GARCH, TGARCH, probit, logit, copula, default correlation, BKKK (Multivariate GARCH) and just what you want in Excel
Maximum like likelihood estimation
One of the most widely used methods of statistical estimation is that of maximum likelihood. The method of maximum likelihood finds the values of the model parameter that maximize the likelihood function. The method is also broadly used in quantitative analysis and financial modeling. When I look back on my professional life as a quantitative analyst, risk manager and consultant, there are a number of issues that would spring to mind: Volatility forecast, rating models, time series analysis, credit portfolio or copula models.
The concept of maximum likelihood is straightforward and is easy to explain:
- Make an assumption about the data generating function.
- Formulate the likelihood function for the data, using the data generating function.
- Find an estimator for the parameter using optimization technique.
Anyone who wants to know more about the concept, I recommend to have regularly a look to a book of your choice.
The two first points – assumption and formulate the likelihood function – require brain or a cookbook. The internet provides solutions for the most common problems. Alternatively, you can read relevant specialist literature.
The final action on the above list can be solved by computer-aided numerical algorithm. Statistical and mathematical packages like R and Matlab provide numerical optimizations routines such as the Berndt–Hall–Hall–Hausman (BHHH), in order to find numerically the maximum likelihood estimator of the parameter. Furthermore Excel’s Solver provides a simple solution. Both approaches have their advantages and disadvantages. On the one hand on some computer an installation of statistical and mathematical packages are not allowed (i.e. insufficient user-rights) on the other hand Excel’s Solver is an all-rounder whose processing is low performance. For that reason I decided to develop my own solution, which satisfies the following conditions:
- Compatibility (Excel/VBA)
- Speed (optimized numerical algorithm and using the WinAPI)
- Simplicity (object oriented programming)
The result is a collection of VBA Classes and Examples. I programmed a linear algebra class, an oop implementation of the BHHH algorithm and following estimation examples:
- GARCH(1,1) model (parameter estimation with different error distributions: Gaussian, Student’s t and the Generalized Error Distribution)
- TGARCH(1,1) model (parameter estimation with different error distributions: Gaussian, Student’s t and the Generalized Error Distribution)
- ARMA(1,1) model
- Multivariate probistic regression (probit)
- Multivariate logistic regression (logit)
- Default/Asset correlation
- Clayton copula estimation
- Multivariate GARCH model (BKKK)
You will see that the structure of the examples will be highly similar. User surely benefit from some prior Excel and VBA literacy. I think the best way to learn is to try it yourself.
Maximum likelihood
Download “[web:reg] MLE” wr_mle_V01.zip – Downloaded 248 times – 2 MB
How to use
I choose the GARCH(1,1) as example:
Assumption about the data generating function and formulate the likelihood function.
GARCH models are used mainly in analysing financial time-series data, in order to ascertain its conditional variances and volatilities. GARCH(1,1) is defined by the following equation.
\varepsilon_t=\sigma_t\ \zeta_t\ \ with\ \sigma_t^2=\omega+\alpha\varepsilon_{t-1}^2+\beta\sigma_{t-1}^2One can demonstrate that the log likelihood function is
llh_t=-\frac{1}{2}\log{\left(2\pi\right)-\frac{1}{2}}\log{\sigma_t^2}-\frac{1}{2}\frac{\varepsilon_t^2}{\sigma_t^2}Coding the log-likelihood function.
The general design of the log likelihood function in the MLE Toolkit is:
Private Function udf(par As WEB_REG_MAT, llh As WEB_REG_MAT, x As WEB_REG_MAT, err As WEB_REG_MLE_ERR) As Double ' declarations ... err.err = False ' Reset Error Handler On Error GoTo ERR_Handler ' your llh ... 'return the sum of llh udf =... Exit Function ' If error ERR_Handler: udf = -1000000000000# err.err = True End Function
WEB_REG_MAT is a linear algebra library which provides an efficient class for vectors, matrices and basic linear algebra calculation. The WEB_REG_MAT class is zero-based. The ten elements of a 10-element vector are numbered from 0 to 9.
Item |
Example |
Declaring and Creating | Dim par As WEB_REG_MAT: Set par = New WEB_REG_MAT |
Create a vector with 3 elements of zeros | par.new_(3) |
Create a 4-by-3 matrix of zeros | x.new(4,3) |
Number of rows | m=Par.m |
Number of columns | n=x.n |
Refer to an element |
k= par.val(1) [k= par.val_A(1) unsafe but fast] par.val(1)=1.5 [par.val_A(1)=1.5 unsafe but fast] l=x.val(1,2) [l=x.val_A(1,2)] |
Creating a vector/matrix from a Range | par.var_data = Range(“A1:A3”).Value |
First of all, we had to code the log likelihood function:
Private Function garchexample_llh(par As WEB_REG_MAT, scores As WEB_REG_MAT, eps As WEB_REG_MAT, err As WEB_REG_MLE_ERR) As Double Dim i As Long, T As Long, sigma_sqr_t As Double, llh As Double T = eps.M ' #observations sigma_sqr_t = sigma_sqr ' initializing the volatility process by the sample variance err.err = False ' Reset Error Handler On Error GoTo ERR_Handler 'Cumulative sum with a for loop For i = 1 To T - 1 ' The GARCH(1,1) Model sigma_sqr_t = par.val_A(0) + par.val_A(1) * eps.val_A(i - 1) * eps.val_A(i - 1) + par.val_A(2) * sigma_sqr_t ' log likelihood scores.val_A(i) = -0.5 * (M_LN2PI + Log(sigma_sqr_t) + (eps.val_A(i) * eps.val_A(i)) / sigma_sqr_t) ' Cumulative sum -> Sum of llh llh = llh + scores.val_A(i) Next i 'return the sum of llh garchexample_llh = llh Exit Function ' If error ERR_Handler: garchexample_llh = -1000000000000# ' If error i.e. negative sigma_sqr_t stepsize will be reduced in optimization routine err.err = True End Function
Four parameters are passed to the function during function call and the sum of log likelihood is re-turned.
The arguments are:
Argument |
Description |
par As WEB_REG_MAT |
parameter vector to be estimated. In this example size of three. |
scores As WEB_REG_MAT |
A vector of log likelihoods. Empty Vector, the WR_MLE Class resized it. |
eps As WEB_REG_MAT | Given observations. In this example mean adjusted log returns |
err As WEB_REG_MLE_ERR | An error control class |
During each run of the loop the conditional variance (sigma_sqr_t) and the log likelihood is scores.val_A(i) calculated.
In the second step we need a driver procedure. Note that sigma_sqr is a private global variable, the given observations are provided in the Range Simple_GARCH_1_1!D4:D997, the sample variance in Simple_GARCH_1_1!G5.
Private Function garchexample_llh(par As WEB_REG_MAT, scores As WEB_REG_MAT, eps As WEB_REG_MAT, err As WEB_REG_MLE_ERR) As Double Dim i As Long, T As Long, sigma_sqr_t As Double, llh As Double T = eps.M ' #observations sigma_sqr_t = sigma_sqr ' initializing the volatility process by the sample variance err.err = False ' Reset Error Handler On Error GoTo ERR_Handler 'Cumulative sum with a for loop For i = 1 To T – 1 ' The GARCH(1,1) Model sigma_sqr_t = par.val_A(0) + par.val_A(1) * eps.val_A(i – 1) * eps.val_A(i – 1) + par.val_A(2) * sigma_sqr_t ' log likelihood scores.val_A(i) = –0.5 * (M_LN2PI + Log(sigma_sqr_t) + (eps.val_A(i) * eps.val_A(i)) / sigma_sqr_t) ' Cumulative sum -> Sum of llh llh = llh + scores.val_A(i) Next i 'return the sum of llh garchexample_llh = llh Exit Function ' If error ERR_Handler: garchexample_llh = –1000000000000# ' If error i.e. negative sigma_sqr_t stepsize will be reduced in optimization routine err.err = True End Function