On this page I demonstrate how to use Monte Carlo simulation to demonstrate some ideas in time series analysis. I begin with a non-mean reverting series and show how you can verify volatility measures and the increase in standard deviation of the rate of return over time by using Monte Carlo simulation. Unlike the simple simulations of in the initial page, I show how to make a more interesting and much faster and more flexible simulation. With the fundamental case established, I illustrate the testing measures in volatility output (for example using population or sample variance) given the volatility input and demonstrate how to measure the change in standard deviation as time progresses with no mean reversion and with full mean reversion. My philosophy in understanding time series is to demonstrate how the statistics really work by using Monte Carlo proofs. These kinds of proofs are perhaps a more useful application of Monte Carlo simulation than attempting to put Monte Carlo simulation into your financial models. At the end you can hopefully think about mean reversion and volatility and use the notions in a conceptual way to derive debt terms and risk analysis.

You can download the file that includes simulations of brownian motion and mean reversion. This file includes discrete and continuous equations with random (stochastic) variation. It also includes VBA code that you can use to make more efficient simulations.

## Setting Up Brownian Motion and Mean Reverting Time Series

I will use some time series equations to illustrate the various points relating to Brownian motion time series and mean reverting time series. Begin by understanding that the measured volatility is the standard deviation of the rate of return or the rate of change in price. When I first started applying Monte Carlo simulation I did it in a blind and mechanical way by just copying the method where you first compute the percent change using the formula change = Pt/Pt-1 – 1 or percent change = LN(Pt/Pt-1). The first formula assumes that the change occurs at the end of the period and the second assumes that changes occur at infinitely small increments over the period. The standard deviation of the rate of return is the volatility. If the time increments are annual, the volatility can be expressed as annual volatility. In the screenshot below, I assume that the annual volatility is 10% and the expected return is 4%. If you assume the returns are normally distributed (not the absolute prices) then there should be a 68% chance that the return will be between one standard deviation above the mean and one standard deviation below the mean. If you are like me and you need to remind yourself of this, you can compute the cumulative normal distribution for a series of returns as shown in the screenshot below. At the end of the day, the volatility gives you probabilities of returns above or below the expected return. In the case below, there is a 68% chance that the return will be between 14% and -6%. You can hopefully see like me that this only works if the returns (not the stock prices) come from a normal distribution.

To allow the return to change, you can apply a volatility factor to the return. This can be done as shown below by using the RAND() function in excel and then applying a distribution assumptions to the random number. For this case I assume that numbers come from a normal distribution (I will discuss this later a lot.) To apply the normal distribution, I use the inverse of the normal distribution the produces a value instead of a probability. The inverse of the normal distribution can be used with a mean of zero and a standard deviation of 1.0. When you put a standard deviation into the standard normal distribution, you will get a probability between zero and 1. Conversely, you can use the NORMSINV function to give you a standard normal value with a mean of zero and a standard deviation of 1.0 by inputting the RAND function using NORMSINV(RAND()). This is illustrated below.

I use discrete and continuous time series equations in my Monte Carlo simulation tests. The time series equations with stochastic variation are illustrated in the screenshot below. As shown, I start only with a volatility statistic and a mean reversion statistic. I use a discrete time series where the change is all assumed to occur at the end of the period. When you use something like Price t = Price t – 1 * (1 + return), you are assuming that the return you input occurs at the end of the period. If you want to assume that the return occurs at tiny increments or incrementally over the time period, you can use the formula Price t = Price t – t x exp(return).

In this sheet, when you press the F9 key in excel you will get a different scenario. My idea with Monte Carlo simulation is to test various things about a mean reverting series and a non-mean reverting time series with this simple Model

## More Efficient VBA Code for Monte Carlo Simulation

The first Monte Carlo simulation I made was to test whether the volatility that you input is the same as the volatility that results from the scenarios. To do this you can make a whole bunch of simulations and than compute the average or median volatility across the different scenarios. I don’t know whether to use the population standard deviation or should I compute the variance for the different scenarios and then take the square root. Further, should I compute the volatility with the LN function on the continuous time series and the discrete percent change with the discrete function. I could try to look around my old statistics books for this, but I think it is better to prove things with a Monte Carlo simulation where you can compute the percent change (the growth rate) from period to period and then compute the standard deviation or the variance of the growth rate.

When running a simulation to test things, I want to be able to run a whole lot of simulations but I don’t want to sit around and wait for hours. If I can compute tens of thousands of simulations then I can run it again and test if I get the same answers. To make a more efficient simulation in VBA you can try a couple of things. The first is to use the APPLICATION.SCREENUPDATING = FALSE. Then you can get a little more complicated and save the scenarios into an array rather than writing each scenario out separately. To do this you can make a flexible array as shown in the code below where you could make an array named output with DIM Output() as variant. Then you can use the REDIM statement with the number of simulations and output variables that you want.

Sub simulate() On Error Resume Next Range("output").ClearContents ' Clear out the prior scenario ' ' Now make a range name from the number of scenarios and the number of outputs ' sheet_name = ActiveSheet.Name range_name = "=" & sheet_name & "!R" & Range("row_start") & "C4:R" & Range("row_start") + Range("simulations") & "C12"`ActiveWorkbook.Names("output").Delete ActiveWorkbook.Names.Add`

`Name:="output", RefersToR1C1:=range_name`

Application.ScreenUpdating = False Dim output_range() As Variant ' Note that you should make an array without any dimension ReDim output_range(Range("simulations"), 9) ' Use the input to put in the dimensions of the output For I = 0 To Range("simulations") Range("volatility_output").Calculate ' This is to make a very little calculation like pressing the F9 output_range(I, 1) = Range("vol_1") ' Now put the outputs into the array output_range(I, 2) = Range("vol_1a") output_range(I, 3) = Range("vol_2") output_range(I, 4) = Range("vol_2a") output_range(I, 5) = Range("vol_3") output_range(I, 6) = Range("vol_3a") output_range(I, 7) = Range("vol_4") output_range(I, 8) = Range("vol_4a") Next I Range("output").Value = output_range End Sub

The screenshot below demonstrates a simulation with 40,000 draws that only takes seconds. I have tried a couple of things in computing the volatility output where the input volatility was 20%. As shown on the screenshot, the volatility output is 20% when you compute the growth rate as LN(price t/price t-1) and then take the variance. When you average all of the 40,000 scenarios and use the sample variance rather than the population variance, then the square root of the average variance is the same as the input volatility.

Note also that when you use a discrete distribution rather than the EXP function, the volatility output that gives you the same as the volatility input is the sample variance with the volatility computed from discrete growth rates. With this base, you can start looking at a lot of stuff. You can test whether the Black-Scholes model produces the same results with Monte Carlo simulation; you can test what happens when you use mean reversion; you can evaluate correlations; you can make equations with upper and lower boundaries to represent surplus and excess capacity and then evaluate whether your model is producing volatility results consistent with the observed historic volatility ….

## Testing the Increase in Volatility Over Time in Brownian Motion

When you apply volatility in a model, I was told a long time ago that you should multiply the volatility (the standard deviation of growth or the standard deviation of return) by the square root of the number of periods per year to annualise the volatility:

Volatility = Standard Deviation of Returns x Square Root (Periods per Year)

For example, if you have monthly data, you can compute the standard deviation of returns on a month by month basis. Then, once you have the monthly volatility, you multiply the number by the square root of 12. This is the central idea I will use in evaluating whether the series is a mean reverting series. The idea that volatility increases over time in a non-mean reverting series is intuitive. The idea of a square root seem reasonable as volatility is a standard deviation. But for me it stops there. I could go to a statistics book and maybe find a proof of the formula. But that would be a real pain and I would probably not understand all of the math.

Instead of doing this, you can compute the growth rate from the initial period with either LN or with the discrete percent change for each of the different time periods. Then I bring the data labeled Chg_1, Chg_2 and Chg_3 … into the Monte Carlo simulation.

In the above screenshot I compute the change in the price relative to the initial price. As there is no trend in the price, the expected change in price over multiple scenarios is zero (you can test this with the Monte Carlo simulation). If you computed the percent change in price relative to the initial price you can compute the variance as the square of the percent change in price. You can then compute this change in price squared relative to the initial price for different periods. The variance should increase over time as a Brownian motion series keeps moving up or down and does not come back to the mean. Indeed, as discussed above, the standard deviation should of returns should increase with the square root of time. This means in the screenshot above, the standard deviation of the change in price for the second period should be the square root of 2 relative to the first period. I want to demonstrate this in a rigorous manner because I will use this idea to discuss mean reversion.

The screenshot below shows the results of the simulations using 40,000 simulations. The output of the 40,000 simulations is the square of the change in price relative to the first price — LN(Pt/Po)^2. After computing the variance, I compute the average variance and the median variance across the 40,000 simulations. This is shown in the screenshot below. Then I compute the standard deviation of the variance — both the average and the median. You can see that the variance increases each period as expected. Then, finally, I compute the standard deviation relative to the first period. I compare this to the standard deviation of 2, 3, 4 etc. Note that the increase in the standard deviation is just about the same as the square root of the period. The average for the first period is just about the same as the volatility. Then the second period standard deviation is 1.42 while the standard deviation of the 2 is 1.42. You can also see that in terms of variance, the variance increases in direct proportion to the the time period where the variance in the second period is just about double the variance of the first period.

This whole thing with Monte Carlo simulation is a really big deal to me. We now have a tool to evaluate what happens not only to the volatility output from our equations, but we also have a tool to test when we change mean reversion parameters or input lower or upper boundaries, what in theory should happen to the change in standard deviation over time.

## Testing for Mean Reversion

The manner in which statistical tests are constructed for mean reversion illustrates the problem for me. When you try to test for the presence of mean reversion or better yet, derive the mean reversion parameter, the tests are not very useful for me at all. The excerpt below illustrates a test that apparently is what I use to test for autocorrelation in a series. I suggest a much more intuitive way to derive mean reversion is to use the above principle that without mean reversion the standard deviation increases over time.