Garch Modeling in Excel

Brief tutorial on constructing a GARCH type of model in Microsoft Excel using NumXL functions and wizards.

Video script

Scene 1:

Module 6: GARCH modeling. In this module. You will learn how NumXL can model a GARCH process type.

Scene 2:

For this module we will be using the same spider log returns as in module two. To begin with our GARCH modeling example, select the NumXL tab or in Excel 2000-2003 click on the menu over the toolbar.

Scene 3:

Next, select the cell where you wish the output model to start and click on the GARCH icon.

Scene 4:

The GARCH model form dialog box will pop up on your screen. In the data section, select the range of cells where the sample data is stored.

You will also need to specify how your data has been chronologically ordered. Since our sample data is sorted so that the oldest entries are at the top, I can leave the ascending checkbox checked.

Scene 5:

Next, specify the order of the ARCH component in the order of the GARCH component. NumXL supports three modules of the GARCH family, plain GARCH, exponential GARCH, and GARCH in the mean. For this demo, we will select GARCH. 

Scene 6:

NumXL also supports three probability distributions for the innovations or the residuals, Gaussian students T and the generalized error distribution aka GED. For this demo we will choose Gaussian.

Scene 7:

Now specify what output you wish to generate for this model. Look under options.

One, the guessed parameters checkbox instructs NumXL to initialize the values of the model parameters.

Two, the goodness of fit option instructs NumXL to print output for measuring the goodness of fit of the sample data, for example LLF, AIC, etc.

Three, the residual diagnosis checkbox will be covered in details in module 7, so let's skip this one.

Scene 8:

In the output range, the current active cell will be shown by default. This works for our example, so we press OK.

Scene 9:

The form generates the following outputs, one, on the left the initial values of the models parameters two, the goodness-of-fit calculations in the center three, the residuals diagnosis analysis on the right.

Scene 10:

Let's look closely to the models parameters output.

The mu is the time and variable conditional mean of the process, the alphas are two the coefficients of the GARCH component model, the betas are the coefficients of the ARCH component model. Note that number of alphas and betas are related to the model order, P and Q.

The parameters values are set with a quick yet valid guess. For instance, the mu is initialized with the sample mean and alpha zero is initialized with the sample standard variance and all other parameters are left zero. Please note the model is not yet calibrated, so the parameters are not optimal.

Scene 11:

Next let's examine the goodness of fit output.

One, the log-likelihood function LLF this function computes a probabilistic measure of the sample data being generated from the GARCH model. Obviously we like this value to be as large as possible.

Two, via cake information criterion AIC. AIC is another probabilistic measure of the sample data fit. Unlike the LLF, AIC favors simpler models and it applies a penalty proportional to the models order. We like to see this value as small as possible.

Three, the final value is the models check. Here NumXL examines whether the parameters values model yields a stable process that is stationary and that the variance is always positive. 

Scene 12:

This concludes module 6. If you have any questions, concerns, or feedback please call our toll free number at 1 (888) 427-9486 or email us at support@spiderfinancial.com. We look forward to working with you!

 

Comments

Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful