Pronóstico de Volatilidad de GARCH en Excel

Scene 1:

Welcome to the GARCH volatility forecast mini tutorial. In this video I will use NumXL to analyze, model and forecast the volatility for the SP500 ETF Spider.

To start I've downloaded the monthly closing prices for spider from January 3rd 2000 to the present date. Let's start by computing the log returns for the spider ETF. We'll use the NumXL diff function with the logarithmic closing prices.

Scene 2:

Now select all the cells below. Hit F2 and press control, shift, enter.

Scene 3:

The monthly log returns are now copied in the new range, now format the output range. Notice that the first cell shows N/A or not available. Let's plot the time series in a graph.

First, select the input time series. Now select the returns cell range. Click the insert tab, using the line graph icon select a 2D type of line graph.

Scene 4:

Move the graph to the right and reformat the graph. The monthly returns look centered over the x-axis with no trend over time.

Now let's examine the stationarity assumption of the time series. We'll compute the 12-month moving average and the exponential weighted volatility or the EWMA. Type N/A in the first row, in the second row type WMA in the formula editor.

Now click the FX button to launch the function arguments dialog box.

Scene 5:

For the input range select the returns from the beginning of the series to the current row. Hit F4 to log the starting cell. Set the order equal to 1 for ascending.

For weights, type 12 to use a twelve month equal weighted moving average.

For the forecast horizon, T, type 0 for the current time.

Scene 6:

The function should return N/A since we don't have enough data for a forecast. Copy the formula to the rows below it.

Scene 7:

The WMA is now shown by the dark black curve on the graph. For volatility we'll use NumXL's EWMA function.

First type N/A in the first cell, now type EWMA in the formula bar. Now click the FX button to the left of the bar to make the function arguments dialog box appear.

Scene 8:

Use the beginning of the sample to the current row for the input data set. Lock the starting cell and for order, enter 1.

For the Lamda parameter leave it blank so the function assumes a value of 0.94.

In the T field, type 0, this corresponds to the last step in the input data set.

Scene 9:

Again, the function returns N/A because we don't have enough data points yet. Copy the formula to the row below it then copy the formula to all the following rows.

Scene 10:

The EWMA is shown as the red curve using the vertical axis on the right, note that the 12-month moving average is relatively stable and the EWMA is fairly active over the sample time span.

Scene 11:

Let's compute the summary stats for the monthly log returns. Select the cell where you'd like the summary table to be displayed then switch to the NumXL tab and click the summary stats icon.

Scene 12:

In the input time series select the monthly returns cell range. Accept the default settings for the remaining options and then click OK.

Scene 13:

The sample stats table is now displayed, you should note that the sample average is not significantly different than 0. 

Now we need to compute and plot the autocorrelation and partial autocorrelation in an effort to find the proper model. Select the cell where you'd like the output table to be displayed and then click the correlogram icon. 

Scene 14:

Select the monthly returns time series as the input data. Now change your max lags to 18 for the ACF and for the PACF. Accept the default values for the remaining options and then hit OK.

Scene 15:

The correlogram table and plot are now displayed side by side.

Scene 16:

Now let's reformat the ACF and PACF plots.

Scene 17:

The plot shows no significant autocorrelation in the data, so an ARMA model is not justified. That being said the data does possess fat tails and the EWMA swings widely throughout the sample time, also the returns time plot shows a clustering of volatility. This calls for a GARCH type plot.

Scene 18:

Now select the cell where you'd like the table to be displayed and then click the GARCH icon.

Scene 19:

Select the monthly returns cell range as the input data. In a financial time series, the volatility reacts differently for negative return than it does for positive returns. EGARCH captures this phenomenon so let's go with an EGARCH model. Now click OK.

Scene 20:

The EGARCH models, parameters, its goodness of fit, and the residuals diagnosis are displayed in your worksheet. Notice that the parameter values are initial and by no means optimal. Now we can calculate the models parameter values.

To start, select the cell where the model begins then click the calibration icon in the toolbar. 

Scene 21:

Now the Microsoft Excel solver will pop up. Notice that all the fields in our solver are already initialized with our model formula and parameter cells. Now hit solve.

Scene 22:

The solver found a solution. The optimal parameter values are now copied into your worksheet, notice that all of our residual diagnosis checks worked except the ARCH test.

Now let's compute the long-run conditional volatility. Type the function EGARCH_VL the the long-run conditional volatility. Type EGARCH_VL the formula. Click the function wizard icon FX.

Scene 23:

The EGARCH_VL depends only on the model parameters. Select the cell range for the alphas and for the betas. Notice that we use a Gaussian distribution for the innovations or shocks. This is the default, so we leave the rest blank.

Scene 24:

Now reformat. Now we can reforecast. Select the cell that describes the model or the one that says EGARCH 1:1. Now switch to the NumXL tab. Click on the forecast icon.

Scene 25:

The forecast dialog should appear. The model field is set to the current selected cell and is grayed out.

For the input data select the most current observations from your data set. You can leave the realized volatility blank, moving the standard deviation and volatility values from a third-party source.

For our purposes here we use the EWMA as a proxy for the monthly volatility. Select the cell range for the realize volatility, note that for EGARCH we need at least one realized volatility value. Now we can specify the forecast horizon. We can put 24 for a 2-year forecast, now select the output range.

Scene 26:

We picked the cell immediately below the model table. Now click OK.

Scene 27:

The table is displayed starting from the output cell that we selected. Now we can plot the local conditional volatility and the term structure for the next 12 months. 

Scene 28:

Now reformat the graph, and that's it!

Scene 29:

If you have any questions, comments, or concerns please send them to us at support@numxl.com.