E-GARCH volatility forecast tutorial in Excel

Scene 1:

Hello and welcome to the exponential GARCH modeling tutorial. In this video we'll construct an EGARCH model and derive a volatility forecast. For the sample data we will use the log weekly returns for the S&P 500 between January 2009 and July 2012.

Scene 2:

Before we start the modeling phase let's take a look at the descriptive statistics table. The weekly returns do not exhibit any significant mean serial correlation and follow almost normal distribution. Nevertheless, the series have a significant ARCH effect which explains the excess kurtosis.

Scene 3:

Now select an empty cell in your worksheet and click on the GARCH icon in an NumXL toolbar.

Scene 4:

The GARCH modeling wizard pops up. For the input data, select the cells range of the log weekly returns. Under model change the setting to EGARCH. For innovation, keep the Gaussian distribution selected.

Scene 5:

The output range is preset to the address of the selected cell in your worksheet, click OK.

Scene 6:

Now the EGARCH model table is shown in your worksheet, note that the values of the models parameters are not optimal. Select the cell with the EGARCH 1 1 text in it and click on the calibrate icon in the NumXL toolbar.

Scene 7:

The Excel solver pops up with all its parameters initialized with our model values. We don't need to change anything, so just click solve.

Scene 8:

Once the solver finds a good solution, a dialog box pops up. Click OK to keep the solution.

Scene 9:

Now our model is initialized with optimal values. Next let's compute the log run volatility implied by this model. Using the EGARCH_VL function and the models parameters values of alphas and betas, the implied long run volatility is 2.31% or 16.66% per year.

Scene 10:

Let's now compute the next 15 weeks via the volatility forecast. Again select the top cell in the EGARCH model table and click on the forecast icon.

Scene 11:

The forecast wizard pops up. For the input data select the most recent observations. We'll leave the realize volatility blank. Change the forecast horizon to 15 weeks.

Scene 12:

For the output range select an empty cell in your worksheet.

Scene 13:

The forecast table is now printed in our sheet. The standard error corresponds to the EGARCH volatility and TS stands for term structure.

Scene 14:

Let's add a few more forecast points to the table.

Scene 15:

Copy the last rows formulas to the newly added rows.

Scene 16:

Now let's plot the local and term structure volatility forecast.

Scene 17:

Here we go, the EGARCH model recognizes July 2012 as a low volatility period and forecast the volatility to rise to its historical long-run levels of 2.31% per week.

Scene 18:

That is it for now, thank you for watching!