How do you simulate an AR1 process using Microsoft Excel?

Answer 1

This can be done fairly easily using Excel's random number generator and a couple of simple equations.

First, you need to specify the auto correlation coefficient you wan to use and the mean value and error variance of the AR1 process. For example, you could say that the auto correlation coefficient is 0.5, and that the mean = 100 and the errors are ~ N ( 0, 102 ); i.e., the errors are normally distributed with a mean of 0 and a standard deviation of 10. Keep in mind that for the AR1 series to be stationary, the absolute value of the auto correlation coefficient must be less than 1.

Then using Excel's random number generator you would generate an array of error terms equal in length to the AR1 series you want to create. This is done as follows:

1) Type the following formula into the first cell of the error array, say cell B11

= NORMINV(RAND(), 0, 10)

[Note: The values 0 and 10 in the above equation represent the mean and standard deviation of the error distribution. Ordinarily errors have a mean of 0, but you should substitute a relative cell reference for the standard deviation so that it can be changed without re-entering the formula.]

2) Copy the formula to as many cells as you want to have terms in the AR1 series

3) Now, start the AR1 series in a separate array, say cell C11. Begin by making the first term equal to the mean value; for example, = 100. Then in cell C12 type

= 100 + 0.5*C11 + B11

[Note: The values 100 and 0.5 in the above equation represent the mean and auto correlation values. As before you should substitute a relative cell reference so that they can be changed without re-entering the formula. Be careful to make sure that the cell containing the mean value is the same in both formulas.]

4) You should then copy that formula down the entire array for the AR1 series.

To make sure you have done everything correctly you can check your AR1 series as follows:

The mean of an AR1 process = mean/(1 - auto correlation coefficient). In this case, with a mean of 100 and an auto correlation coefficient of 0.5 the mean value = 200. Thus, if you take the average of the AR1 array, it should be approximately = 200.

The variance of an AR1 process = error variance/(1 - autocorr2 ). In this case with a variance of 100 and an auto correlation coefficient of 0.5 we would get 133.33 for a variance or about 11.55 for a standard deviation. Thus if you take the standard deviation of the AR1 series you should get 11.55.

Hope this helps.

This can be done fairly easily using Excel's random number generator and a couple of simple equations.

First, you need to specify the auto correlation coefficient you wan to use and the mean value and error variance of the AR1 process. For example, you could say that the auto correlation coefficient is 0.5, and that the mean = 100 and the errors are ~ N ( 0, 102 ); i.e., the errors are normally distributed with a mean of 0 and a standard deviation of 10. Keep in mind that for the AR1 series to be stationary, the absolute value of the auto correlation coefficient must be less than 1.

Then using Excel's random number generator you would generate an array of error terms equal in length to the AR1 series you want to create. This is done as follows:

1) Type the following formula into the first cell of the error array, say cell B11

= NORMINV(RAND(), 0, 10)

[Note: The values 0 and 10 in the above equation represent the mean and standard deviation of the error distribution. Ordinarily errors have a mean of 0, but you should substitute a relative cell reference for the standard deviation so that it can be changed without re-entering the formula.]

2) Copy the formula to as many cells as you want to have terms in the AR1 series

3) Now, start the AR1 series in a separate array, say cell C11. Begin by making the first term equal to the mean value; for example, = 100. Then in cell C12 type

= 100 + 0.5*C11 + B11

[Note: The values 100 and 0.5 in the above equation represent the mean and auto correlation values. As before you should substitute a relative cell reference so that they can be changed without re-entering the formula. Be careful to make sure that the cell containing the mean value is the same in both formulas.]

4) You should then copy that formula down the entire array for the AR1 series.

To make sure you have done everything correctly you can check your AR1 series as follows:

The mean of an AR1 process = mean/(1 - auto correlation coefficient). In this case, with a mean of 100 and an auto correlation coefficient of 0.5 the mean value = 200. Thus, if you take the average of the AR1 array, it should be approximately = 200.

The variance of an AR1 process = error variance/(1 - autocorr2 ). In this case with a variance of 100 and an auto correlation coefficient of 0.5 we would get 133.33 for a variance or about 11.55 for a standard deviation. Thus if you take the standard deviation of the AR1 series you should get 11.55.

Hope this helps.