Modelado de Garch en Excel

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 detail in module 7, so let's skip this one.

Scene 8:

In the output range, the currently 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 model's parameters two, the goodness-of-fit calculations in the center three, the residuals diagnosis analysis on the right.

Scene 10:

Let's look closer at the model's 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 model's order. We like to see this value as small as possible.

Three, the final value is the model's 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 support number at 1 (312) 324-0367 or email us at support@spiderfinancial.com. We look forward to working with you!