In this article we show how to simulate price movements with Risk Kit: how to choose a suitable price model, calibrate it to historical data, simulate price paths and display them graphically, and finally run scenarios.
We build on an earlier case study. There we loaded data on oil prices, exchange rates and interest rates with Risk Kit Data, from sources such as the Federal Reserve System, the European Central Bank, Eurostat, the World Bank and Apilayer Marketstack.
More on this: Our case study “Live Access to Large International Data Sources with Risk Kit Data” shows how to find the time series, load them into Excel and synchronise them by date.
This first step gave us three time series: the dollar exchange rate, the price of Brent crude oil and the one-year euro interest rate. The three series partly had slightly different dates, and in some cases values were missing. We therefore put them on a common date grid and interpolated the missing values from their neighbours. Then we converted the oil price from US dollars into euros, because here in Germany we pay in euros. The result is two price series: the Brent oil price in euros and the one-year euro interest rate.
What properties do price movements have?
When we look for a model for a price movement and then want to simulate it, it is always a good idea to look at the data first. A line chart in Excel is all we need. The two price movements, however, are of very different orders of magnitude, and on a shared axis the interest rate could not be made out. So we put it on a secondary axis.
Figure 1 shows the result. The left axis shows the oil price in euros (blue), the right axis the one-year euro interest rate in percent (orange).
Figure 1: The Brent oil price in euros (blue, left axis) and the one-year euro interest rate (orange, right axis). The rate is below zero most of the time, the oil price always stays positive (German interface).
Although the two price movements are of very different magnitudes, they follow a similar course. From 2012, where our time series starts, to 2014 both were at a relatively high, stable level. Then both prices fell until 2016 and afterwards ran stationary until the pandemic, which begins in 2020. In 2020 both the oil price and the interest rate reach their minimum for the whole period under review. At the end of the pandemic both recover, and in the very recent past both interest rates and oil prices rise sharply. The reasons are inflation, supply shortages on the international markets, the war in Ukraine and the end of the pandemic.
The sign is also striking. The oil price is positive the whole time. It does fall sharply at times, but even its minimum in April 2020 is still 8.40 euros, well above zero. Ideally, we would therefore need a price model for the oil price that always takes positive values.
Interest rates are quite different. Until 2014 they are positive in some phases, but then they fall far below zero and stay negative for the rest of the time. Even after the sharp rise at the beginning of 2022, the rate for the one-year period is still negative. For interest rates we therefore need a price model that can take both signs. We will now feed these qualitative observations into the choice of price models.
Which price models are available?
In short: Risk Kit is an add-in for Microsoft Excel that lets you build and analyse Monte Carlo simulations directly in the spreadsheet. What we show here applies in general, though: the price models, their calibration to historical data and the simulation of price paths work on the same principle in any simulation tool.
Stochastic processes serve as price models. A stochastic process is basically nothing more than a random function of time: at each point in time it takes a random value. Risk Kit offers several such models for representing price movements. The five most important are:
- the classic Brownian motion,
- the Wiener process, a variant of Brownian motion,
- geometric Brownian motion, a very famous model that inspired the development of the option pricing models for which several Nobel prizes in economics were awarded,
- the Vasicek process, a process with mean reversion, which keeps it close to its trend,
- and the Black-Karasinski process, a variant of the Vasicek process that also has mean reversion but only takes positive values.
All these processes have two things in common. First, they start from a fixed, given starting value. This is typically the current value of the price, on which the simulation builds. Second, they all have a volatility, that is, a strength of the random fluctuations with which they spread up and down from this starting value over time.
The following charts show a sample path for each process, that is, a single random development, together with the trend as the middle line and a lower and an upper quantile. The 99% quantile is the value that the process does not exceed in 99% of cases at a given point in time. It falls below the 1% quantile in only 1% of cases. So the process moves between the two quantiles with high probability. Quantiles whose probability is given in percent are also called percentiles. In the charts they appear as “Bound 1%” and “Bound 99%”.
Three criteria for choosing the price model
To find our way to the price model that suits our market factors, we use three criteria. They form the columns of the overview table on the left of the following figures.
- The sign: can a process represent only one sign or both? Brownian motion, for example, can represent both signs. Geometric Brownian motion and the Black-Karasinski process, on the other hand, take only positive values in principle. You could use them for negative values by simply mirroring them, but even then they would only ever have one sign.
- Mean reversion: does a mechanism always keep the price close to its long-term trend, or can it move arbitrarily far away from it? This is above all a criterion for long-term forecasts. In the short term, mean reversion has only a very small effect. But if we want to forecast how prices develop over a year or longer, it can be an important element.
- The long-term rise of the percentiles: here, too, the point is to keep long-term forecasts within a range that still yields sensible orders of magnitude. Some price movements can explode very strongly, some widen moderately, and others remain stationary. Stationary here means that they do not move arbitrarily far from their long-term trend, no matter how far into the future we forecast.
Brownian motion
Figure 2 shows on the left the overview of the five processes by the three criteria and on the right Brownian motion. The process starts at −1 and then goes either into positive or into negative territory, or switches from one sign to the other in phases. It has no mean reversion and can therefore move arbitrarily far away from its trend over time. The quantiles move away from the trend with the square root of time.
Figure 2: On the left the five processes by sign, mean reversion and long-term rise of the percentiles, on the right Brownian motion with a sample path, trend and the 1% and 99% quantiles (German slide).
The Wiener process
The Wiener process is a variant of it. It consists of a Brownian motion plus a fixed linear increase, which is called drift: over time the process receives fixed absolute amounts. This gives it a fixed, static trend, and around this trend it develops just as randomly as Brownian motion. There is no mean reversion, and both signs are taken. The quantiles grow as with Brownian motion, now supplemented by the linear increase. That is why the table says “Quadratwurzel plus Linear” (square root plus linear).
Geometric Brownian motion
Compared with this, geometric Brownian motion looks completely different. It takes only positive values, so in principle it never goes below zero. It also has no mean reversion, but its quantiles rise exponentially in the long run.
We see this in the upper chart of Figure 3. The process starts at 100, and we look at it over 500 time steps. In the extreme case it can take very large values: at the end the upper quantile is at almost 110,000, more than a thousand times the starting value.
Figure 3: Geometric Brownian motion with the same parameters, at the top over 500 time steps, at the bottom over 100 time steps (German slide).
In the lower chart we shorten the time window from 500 to 100 time steps. It is the same process with the same parameters, but for this shorter-term forecast it behaves much more stably. We see a recognisable geometric trend, a kind of compound interest. That is also why this process is called geometric Brownian motion. The quantiles rise only moderately exponentially here, because the time factor they contain is still very small. In between runs a sample path.
Geometric Brownian motion is therefore usually better suited for short-term forecasts than for very long-term ones. In long-term forecasts it tends to become unstable, at least on some paths.
More on this: The values of a geometric Brownian motion are lognormally distributed at every point in time. How this distribution arises from proportional growth, why it never becomes negative and where its limits lie for real prices is explained in our article “When do I use a lognormal distribution in a Monte Carlo simulation?”.
What does mean reversion mean?
Mean reversion means that a mechanism keeps pulling the price back towards its long-term trend. The further the price moves away from it, the stronger this pull. The last two processes in our overview have this property.
The Vasicek process
The Vasicek process is the first process that adds mean reversion. This brings two new elements. First, the long-term trend has to be specified explicitly. In the example in Figure 4 the process starts at exactly zero and then develops towards a long-term level of −0.5. This −0.5 has to be specified. Second, the strength of the mean reversion has to be quantified, that is, a speed of reversion.
Figure 4: The Vasicek process with the same starting value and the same long-term trend, at the top with moderate, at the bottom with strong mean reversion (German slide).
In the upper chart the speed of reversion is moderate. In the lower chart the long-term trend of −0.5 and the starting value of zero are the same, but the mean reversion is much stronger. This leads to two differences. First, the long-term trend is reached much faster at the bottom, after about 100 time steps, whereas at the top it is only reached after about 300 time steps. Second, the range within which the process moves back and forth is much smaller with strong mean reversion than with weak mean reversion. At the top the interval between the quantiles is considerably wider. In both cases, however, the process does not move arbitrarily far from its long-term trend but stays close to it.
The Black-Karasinski process
The Black-Karasinski process has essentially the same properties. It is a variant of the Vasicek process and therefore shares its mean reversion. The main difference is that it can only take positive values and not both signs. In the example in Figure 5 it starts at 100 and then moves towards its long-term trend of 750.
Figure 5: The Black-Karasinski process. It stays positive and runs from the starting value of 100 towards its long-term trend (German slide).
Which price model fits the oil price and the interest rate?
With the qualitative properties of the process types we can now choose models for the Brent price in euros and for the one-year euro interest rate. Two observations from Figure 1 play a role here.
First, within distinct market phases, for example from 2012 to 2014 and from 2016 to 2020, there are no pronounced growth processes. In both cases we move along stable price levels. A process with mean reversion is therefore the obvious choice. Second, the oil price is always positive, whereas the interest rate is also negative for a long time. For the interest rate we therefore need a model that can take both signs, for the Brent price in euros one that takes only positive values.
Accordingly, we choose a Vasicek process for the interest rate and a Black-Karasinski process for the Brent price. Both provide mean reversion, the Vasicek process can take both signs, and the Black-Karasinski process is always positive.
These processes have a further advantage, which we do not need in the first step, though: the long-term trend can be specified explicitly. This makes both very well suited for scenario analyses. If we have a market expectation that is not derived from history but seems conclusive to us on the basis of current information, we can set these parameters in the processes explicitly. We do this further below.
How do you calibrate a price model to historical data?
Calibration means estimating the parameters of a model from historical data so that the model fits these data. First we write the chosen process types above the two time series: in cell Q3 “BlackKarasinski” for the Brent price and in cell R3 “Vasicek” for the interest rate. These are keywords for Risk Kit, so we write them in exactly this form, as one word and without a hyphen. On screen they appear in capital letters. Cells Q5 and R5 hold the names of the two time series, “Brent in EUR” and “1Y EUR-Zins”, and the data follow from row 6.
To calibrate, we use the function CalibrateMVProcess. It calibrates a multivariate, that is, multidimensional, process, here a two-dimensional one. A one-dimensional process could be calibrated with the function CalibrateProcess. But we also need the dependence between the Brent price and the interest rate so that the model is self-contained. The multidimensional estimation provides this dependence automatically.
Figure 6 shows the dialog of the function. ProcessTypes holds the process types from Q3:R3. So each price can have a model of its own. It is not necessary for all the prices we want to simulate together to follow the same characteristics. ProcessNames holds the names from Q5:R5, Data the data from Q6:R2605. ProcessIsColumn is set to “wahr” (true) because each time series is in a column.
Figure 6: The dialog of the function CalibrateMVProcess. It is inserted via “Einfügen als Matrixformel” (insert as array formula) (German interface).
How long is a time step?
Finally, the length of a time step has to be specified, the delta t from one observation date to the next. Here this is one business day, and we set DeltaT to 1. We could just as well give a business day the value 1/260, because 52 weeks of 5 business days each make 260 business days a year. The length of delta t does not really matter for the calibration and the simulation.
The difference is that the parameters of the process, such as the volatility, are always reported for the delta t specified. If we set 1 for one business day, then later in the simulation we also have to use a time interval of 1 for one business day. The two must always match exactly. With this, the mandatory parameters are complete.
Which data go into the estimation?
With IsDescending we state whether the time series is sorted by date in descending order, that is, whether the most recent day is at the top. That is the case here, so IsDescending is set to “wahr” (true). If the time series were sorted in ascending order, with the oldest day at the top, we would have to enter “falsch” (false).
With DataPointNumber we can limit the estimation to the most recent data records, for example to the last 260 days, that is, one business year, or to 500 or 1000 days. Risk Kit then counts down from the current day. We leave the field empty and take all the data records in the table.
We insert the function as an array formula, because it does not return just a single cell but a whole range. The formula bar then shows:
{=CalibrateMVProcess(Q3:R3,Q5:R5,Q6:R2605,WAHR,1,WAHR)}
What do the estimated parameters mean?
Figure 7 shows the result in columns T to V. To the left, in columns P to R, are the two time series we calibrated to. The result block is divided into two parts: at the top the process parameters in the narrower sense, at the bottom the dependences between the processes in the form of correlations.
Figure 7: The result of the calibration. At the top in rows 6 to 10 type, a, b, mu and sigma for each process, at the bottom in rows 12 to 14 the correlations (German interface).
Row 6 states the type of the process once more, followed by the parameters a, b, mu and sigma. The parameter mu plays no role for either the Black-Karasinski process or the Vasicek process. It is reported nonetheless, here with the value 0. The reason is that CalibrateMVProcess also allows geometric Brownian motions and Wiener processes, and for these two process types mu does play a role. The function therefore forms the union of all potentially relevant parameters and reports those that the chosen process needs.
The parameters are easy to interpret:
- The parameter a is the speed of mean reversion. For the Brent price it is about 0.0037, for the interest rate about 0.0012.
- The parameter b is the long-term trend. For the Vasicek process it is the long-term trend directly, here an interest rate level of −0.54%. For the Black-Karasinski process it is the logarithm of the long-term trend. To see the trend as a price, we take e to the power of this value: b = 4.0526 gives a long-term trend of about 57.5 euros per barrel.
- The parameter mu is, as mentioned, irrelevant for these two processes.
- The parameter sigma is the volatility of the process, here about 0.030 for the Brent price and about 0.013 for the interest rate. For the Black-Karasinski process it is the volatility of the logarithmic, that is, relative changes, for the Vasicek process the volatility of the absolute changes in the value.
The correlation between the processes is defined as the correlation between the residuals, that is, between the random parts of the changes that are not explained by the trend of the model. For the Brent price and the interest rate it is about 0.023 here.
How do you simulate price paths?
To simulate the processes, we first define the points in time for which we want the prices. To do this, we enter points in time in column T from row 19. The 0 is the current point in time, in effect our starting value. After that we want the price for one day per week, that is, every 5 business days, and that for 52 weeks, one year. The points in time therefore run from 0 to 260 business days. They do not have to be evenly spaced at all, they only have to be in ascending order.
The processes are simulated with the function MV_General_path, which we find on the Risk Kit toolbar under “Prozess” (process). It simulates general processes in a multidimensional model. Each individual process has a model of its own, and these models are combined with each other, here the Black-Karasinski model for the Brent price and the Vasicek model for the interest rate.
Figure 8 shows the dialog of the function. The parameters have exactly the same layout as the return values of the calibration function. So all we have to do is transfer the values returned by the calibration into the input form.
Figure 8: The dialog of the function MV_General_path. The parameters come from the result of the calibration, the points in time from column T. The Identifier field is still empty here (German interface).
- ProcessTypes are the process types from row 6 of the result, Black-Karasinski and Vasicek.
- R is the vector of starting values, that is, the two current prices from Q6:R6: the Brent price of 107.70 euros and the interest rate of −0.25%. Here we see how practical it is that the most recent values are at the top. They have a fixed coordinate, and even if we change the time window, the references stay correct.
- A, B, Mu and Sigma are the estimated parameters. We have to specify Mu even though we do not need it, because this function covers the general case. Had we chosen a geometric Brownian motion or a Wiener process, mu would be needed.
- CorrelationMatrix is the correlation matrix from U13:V14.
- Times are the points in time from T19:T71 at which we want to evaluate the processes.
At the bottom of the dialog there are two more optional parameters. We leave RowVector empty, so we get the results as column vectors. The Identifier means that Risk Kit does not reload the parameters from Excel in every simulation run but remembers them itself. If we enter an ID here, for example 1, this improves performance somewhat. It is important to really treat this ID as a unique identifier and not to use it more than once, because otherwise the same parameters are always used.
We insert this function as an array formula too, here over cells U19:V71. The formula is:
{=MV_General_path(U6:V6,Q6:R6,U7:V7,U8:V8,U9:V9,U10:V10,U13:V14,T19:T71,,1)}
Risk Kit returns the simulated prices directly, the Brent price in column U and the interest rate in column V. With F9, or with “Neu berechnen” (calculate now) on the “Formeln” (formulas) tab, the prices are drawn over the whole period as a new random price movement. With that, the simulation is basically done.
How do you display simulated price paths graphically?
To see how the simulated processes run as a whole, we display them graphically. Risk Kit needs two steps for this.
First step: define the processes
First we have to declare the processes as result processes. So we tell Risk Kit that this list of values at these points in time is the simulated Brent price in euros and that list is the euro interest rate. This is done with the function ProcessDefinition, which we find on the Risk Kit toolbar under “Allgemein” (general). It has three arguments: the points in time as x values, the values as y values and the name of the process.
Figure 9 shows the dialog for the Brent price. The points in time are in T19:T71, the values in U19:U71, and the name comes from cell U18, “Brent in EUR”. The values only start in row 19, which we have to keep in mind for the references.
Figure 9: The dialog of the function ProcessDefinition for the Brent price. “Einfügen & als Prozesszelle markieren” (insert and mark as process cell) marks the cell as a process cell straight away (German interface).
When inserting, we can either just insert the function or insert it and mark it as a process cell at the same time. Marking it as a process cell is necessary. It is the counterpart to marking a result as an output cell: it tells Risk Kit that there is a process here that has to be evaluated. For the euro interest rate we do the same with the values from column V. The two process definitions are in cells X18 and Y18.
Second step: plot the processes
We then visualise the paths of the two processes with the function PlotProcess, also under “Allgemein”. It has the same form as the normal Plot function, but refers to process cells instead of output cells. For the Brent price we reference the process cell X18 and specify the title “Brent-Preis-Entwicklung” (Brent price movement) and the axis labels “Zeit” (time) and “Preis pro Barrel (EUR)” (price per barrel). For the interest rate we do the same with the process cell Y18.
If we also specify the cell for the upper left corner and the width and height of the chart, the chart is embedded in the worksheet. The number of visible paths and the quantiles can be set as well. Without these entries we get a window of its own in which the chart is displayed.
What does the chart show?
Now we simulate and look at what the processes look like. Figure 10 shows the window for the Brent price with several simulated paths. The horizontal axis shows the points in time we simulated, up to 260 days. The two outer lines are the 1% quantile and the 99% quantile, the line in the middle is the mean as the trend over time.
Figure 10: The simulated paths of the Brent price in euros over 260 days with the two quantiles and the mean (German interface).
We can see clearly here that the Brent price is capped at the top in its random development. This is the effect of mean reversion, exactly what we estimated before. So the price does not run arbitrarily far away. Based on this estimate, we are 99% certain that at any given point in the coming year it will not be above about 160 euros, and likewise 99% certain that it will not be below 35 euros. The mean falls over the year.
The result for the euro interest rate looks similar. Here, too, a slight decline is expected.
How do you run scenarios?
So far we have taken the parameters of the processes as they were calibrated. But we might take the view that they are not representative of the future for the oil price. Then there are two options. On the one hand, we could select the segment of the historical data that goes into the calibration more carefully and use only a very short history instead of a long one over 10 years. On the other hand, we can assume an expected price movement and partly overwrite the estimated parameters.
The calibrated long-term trend of the oil price is about 57.5 euros per barrel, well below the price of 107.70 euros at the time of recording in May 2022. If we think this is wrong and would rather assume 120 euros per barrel as the long-term trend, we overwrite this parameter. Since the Black-Karasinski process works with the logarithm of the trend, the new parameter b is the logarithm of 120, that is, 4.7875.
We can do the same with the euro interest rate. Perhaps we want to assume that the long-term interest rate level will not be −0.54% but much higher, because, as was expected in May 2022, the European Central Bank will soon raise interest rates, much like the US central bank. Then we set it, for example, to plus 0.5% instead of minus 0.54%.
Figure 11 shows what this looks like in the sheet. The new values are in row 8 next to the calibrated ones: in cell W8 the logarithm of 120 and in cell X8 the value 0.5. In the formula of MV_General_path the parameter B now refers to W8:X8 instead of U8:V8. All other parameters remain as calibrated.
Figure 11: The scenario in the sheet. W8 holds the logarithm of 120 euros, X8 the interest rate level of 0.5%. MV_General_path now uses W8:X8 for the parameter B (German interface).
Then we simulate again. Figure 12 shows the result for the Brent price. In our scenario analysis we now see a rising trend: we start from 107.70 euros and slowly rise to 120 euros. In addition, the upper 99% quantile no longer stops at 160 euros but goes far beyond it, to over 230 euros.
Figure 12: The Brent price in the scenario with a long-term trend of 120 euros. The mean rises, and the upper quantile reaches over 230 euros (German interface).
It is similar for interest rates. They now rise as well. At the end of the simulated year they are still far from reaching the long-term trend, but essentially much higher interest rates are now assumed than in the purely calibrated analysis.
This approach gives us a great deal of room to shape risk analyses. We can build on a pure data analysis and, starting from it, carry out scenario analyses. To do so, we overwrite individual parameters or whole groups of parameters and see what effect this change in the environment has on the risk result.
How do you use the simulated prices further?
We can reference the simulated prices from our risk models. We can take individual prices out of the process and integrate them, for example, into a profit and loss model or place them in an entirely different context. This is basically standard. Once a simulated price is available, it fits naturally into such a model, which is no longer a technical problem.
Incidentally, defining a process with ProcessDefinition is entirely general. We can also use it, for example, to represent a trend in earnings as a stochastic development. So it is not only there for price movements: any kind of development can be represented and plotted this way.