IBLIOGRAPHYBenninga, S. (2000), Financial Modeling, MIT Press, Cambridge, MA.Cuthbertson, K. and D. Nitzsche (2001), Investments: Spot and Derivative Markets, John Wiley,Chichester.Jackson, M. and M. Staunton (2001), Advanced Modelling in Finance Using Excel and VBA, JohnWiley, Chichester.Markovitz, H. (1952), “Portfolio Selection”, Journal of Finance, 7(1), March, 77–91.Markovitz, H. (1959), Portfolio Selection: Efﬁcient Diversiﬁcation of Investments, John Wiley, NewYork.
10
Applied Volatility and Correlation ModellingUsing Excel∗FRÉDÉRICK BOURGOIN
ABSTRACTThis chapter implements a range of univariate and multivariate models within MicrosoftExcel. This is extremely useful as a large proportion of ﬁnance professionals, students andresearchers that are familiar with this package. We show how to generate one-step aheadforecasts of volatility using the JP Morgan RiskMetrics model, JP Morgan RiskMetricsmodel with an optimal decay, a GARCH model with and without a variance reductiontechnique and ﬁnally using the GJR model to account for asymmetric reaction to news. Acomparison of forecasts is made and some useful insights to the efﬁciency of the modelsis highlighted. In the second part of this chapter, we model the time-varying correlationusing different models. As with the univariate approach this includes the JP MorganRiskMetrics model with and without optimal decay, a GARCH model with and withoutvariance reduction and ﬁnally the so-called “Fast GARCH” model of which the authorhas previously made signiﬁcant contributions to the literature.
10.1 INTRODUCTIONThe practicability of multivariate GARCH models has been the subject of several articlesin the past few years, particularly regarding the feasibility of large size problems (Athayde,2001; Bourgoin, 2000, 2002; Engle and Mezrich, 1995, 1996; Ding and Engle, 1994;Ledoit, 2001; Ledoit et al., 2001). Some models work well only on speciﬁc data, like theorthogonal GARCH (Alexander and Chibumba, 1997) with yield curve or term structureof implied volatilities (see Bourgoin (2000) for the reasons). Because all these modelsrequire complex optimisation routines and tricks, GARCH models have always requiredspecial software like Rats (Regression Analysis of Time Series), Eviews, Matlab, SPlus, Gauss, Ox or even special C++ code. In this chapter, we will try to provide anunderstandable way to construct GARCH models, both univariate and multivariate models,using Excel, the most widely used ﬁnancial application in the market place. Two Excelspreadsheets are provided on the CD-Rom, one for the univariate models and another onefor the multivariate models (Bourgoin001.xls, and Bourgoin002.xls, respectively).∗The views expressed herein are those of the author and do not necessarily reﬂect the views of Barclays GlobalInvestors. I retain responsibility for any errors.
Applied Quantitative Methods for Trading and Investment. 2003 John Wiley & Sons, Ltd ISBN: 0-470-84885-5
Edited by C.L. Dunis, J. Laws and P. Naı̈m
314
Applied Quantitative Methods for Trading and Investment
10.2 THE BASICSLet Pt be the price of a security at time t with t = 1, . . . , T and the continuously compounded returns expressed as rt = ln(Pt /Pt−1 ) with µt = E(rt |Ft−1 ) where F (t − 1) isthe information set available at time t − 1.The simplest linear time series model is the ARMA(p, q) process where r(t) followsan autoregressive process, i.e., the past values of r(t) inﬂuence future values of r(t) anda moving average component which is formed by the error term, εt−1 . Mathematicallywe have the following:pqφi rt−i +θi εt−i(10.1)rt = θ0 +i=1
j =1
In the following pages we will not focus our attention on modelling the ﬁrst momentof the distribution, so we will consider that E(rt ) = µt = 0 and that either it has beendone before or that it is statistically insigniﬁcant. The main purpose of this chapter isto consider the following: σt2 = Var(rt |Ft−1 ) and how to account for the time-varyingvolatility observed in the ﬁnancial markets. So the model becomes:rt = σt εt
2222where σt2 = f (εt−1, . . . , εt−i; σt−1, . . . , σt−j)
with
i, j > 0,
εt → N (0, 1)1
(10.2)
We try in this chapter to use only the Excel Solver add-in2 and no additional optimisationadd-in (several packages are available and a lot more ﬂexible and powerful than the ExcelSolver3 ), their usage is quite similar to the Solver, readily available as part of Excel.We use the maximum likelihood estimation technique to evaluate the parameters of themodels shown in the chapter.The likelihood function is the following:L = f (rn+1 , rn+2 , . . . , rT , ) = f (rt |Ft−1 )f (rt−1 |Ft−2 ) . . . f (rn+1 |Fn )
(10.3)
where n is the number of parameters to estimate.If f follows a normal probability distribution with zero mean and variance σt2 , thenL becomes:T
T

r2L(rn+1 , rn+2 , . . . , rT , ) =f (rt |Ft−1 ) =exp − t2σt2πσt2t=n+1t=n+11
(10.4)
For the purpose of the optimisation, we often ﬁnd it more convenient to work with thenatural log of this function, ln(L), because of the following property: ln(ab) = ln(a)+ (b).1Obviously other probability distributions can be considered like the Student(t), GED(v) or the skewedStudent(k, t) distribution but this is beyond the scope of this chapter.2In order to set up the Solver add-in in Excel, start Excel ⇒ Tools ⇒ Add-ins ⇒ Solver Add-in should bechecked and then press <OK>, if it doesn’t appear in the list, use your MS Ofﬁce CD-Rom to install therequired component.3Palisade Corporation sells the @RiskOptimizer, a more powerful version of the Excel Solver with geneticalgorithm and Monte Carlo simulation capabilities, see http://www.palisade.com.
Applied Volatility and Correlation Modelling
315
The likelihood function becomes:ln(L, ) =
T
111 rt2− ln(2π ) − ln(σt2 ) −222 σt2t=n+1
(10.5)
where we can drop ln(2π ) as well.
10.3 UNIVARIATE MODELSThe Excel spreadsheets “Bourgoin001.xls” and “Bourgoin002.xls” both have a commonset-up:• Column A and B should have the dates and the security used for analysis, here, theS&P 500 index.• Column C calculates the log returns for the index.• Column D speciﬁes the volatility equation.• Column E calculates the log-likelihood function for each date (when we do an optimisation, which is not the case for the RiskMetrics Volatility model).• Column F calculates the annualised volatility using the square-root rule.4• The other columns show details on how to set up the Solver in order to perform theoptimisation.
10.3.1 The RiskMetrics modelIn the RiskMetrics model (J.P. Morgan, 1995), the volatility process speciﬁcation isthe following:22σt2 = (1 − λ)εt−1+ λσt−1(10.6)The variance at time t depends on the previous squared returns and the previous variance.If we want to forecast the term structure of volatility going forward, we can show that itis constant:2E(σt+1|Ft ) = E((1 − λ)εt2 + λσt2 ) = (1 − λ)E(εt2 ) + λE(σt2 )2= (1 − λ)εt2 + λσt|t−122222E(σt+2|Ft ) = E((1 − λ)εt+1+ λσt+1|t) = (1 − λ)E(εt+1|t) + λE(σt+1|t)
(10.7)
222= (1 − λ)σt+1|t+ λσt+1|t= σt+1|t22E(σt+h|Ft ) = σt+1|t
4Only statistically applicable to the random walk and the RiskMetrics model, it is industry practice to use itregardless of the volatility model. See Tsay (2002) and Diebold et al. (1997) for more details.
316
Applied Quantitative Methods for Trading and Investment
Figure 10.1 The RiskMetrics model
The worksheet “RiskMetrics” in the spreadsheet “Bourgoin001.xls” (see screenshot inFigure 10.1) implements this model on the S&P500 data from April 1998 to August 2001.The initialisation of the algorithm is quite important regardless of the volatility modelused (here RiskMetrics), one should always default the ﬁrst observation of the volatilityto the unconditional standard deviation of the time series (σ1 = σ ). This is particularlycrucial when complex models are used; bad starting values can lead to a non-convergenceof the optimisation process.Note that the spreadsheet allows λ, the decay factor, to be varied. In practice though,it is set to 0.94 for daily data and 0.97 for monthly data.510.3.2 The optimal decay factor modelIn the previous spreadsheet (RiskMetrics), we didn’t use any optimisation as the usergives λ, the decay factor. If we want to ﬁnd the optimal decay factor for the time series,maximum likelihood estimation is required to ﬁnd the only parameter necessary, λ. Theworksheet “OptimalDecay” in Bourgoin001.xls (see Figure 10.2) shows how it is done.Remarks:• The grey background identiﬁes the optimised values or changing cells in every spreadsheet.• No standard errors are calculated for any models, this is beyond the scope of this chapter.• The Solver options are shown in Figure 10.3.65In practice, we can observe less heteroskedasticity in monthly data than in daily data, that is the reason whythe decay has been set higher (standard set by J.P. Morgan (1995)).6These options are accessed from the main Solver window by clicking on “Options”.
Applied Volatility and Correlation Modelling
317
Figure 10.2 Optimal decay
Figure 10.3
Solver settings
• The higher the decay factor (less than 1), the longer the half-life, for example with adecay factor of 0.94, 50% of the weight is in the previous 11 observations, if the decayincreases to 0.98, the half-life increases to 34 observations (see RiskMetrics TechnicalDocument, p. 93). When the decay equals 1, we mean revert to the unconditionalstandard deviation calculation if we initialised the process with σ0 = σ .
318
Applied Quantitative Methods for Trading and Investment
45.00%FAST moving volatility estimationwith a fast decay (0.94)
40.00%35.00%
SLOW moving volatility estimationwith a fast decay (0.97)
30.00%25.00%20.00%15.00%10.00%
Long-term volatility calculatedusing the Excel function Stdev
5.00%
16-Apr-9816-May-9816-Jun-9816-Jul-9816-Aug-9816-Sep-9816-Oct-9816-Nov-9816-Dec-9816-Jan-9916-Feb-9916-Mar-9916-Apr-9916-May-9916-Jun-9916-Jul-9916-Aug-9916-Sep-9916-Oct-9916-Nov-9916-Dec-9916-Jan-0016-Feb-0016-Mar-0016-Apr-0016-May-0016-Jun-0016-Jul-0016-Aug-0016-Sep-0016-Oct-0016-Nov-0016-Dec-0016-Jan-0116-Feb-0116-Mar-0116-Apr-0116-May-0116-Jun-0116-Jul-01
0.00%
Figure 10.4 Annualised RiskMetrics volatility with varying decay factors
A comparison of different decay factors can be found in the sheet named “DifferentDecayChart” in Bourgoin001.xls (Figure 10.4).As we will see in the next section, the RiskMetrics and optimal decay models are, infact, simpler versions of the GARCH model.10.3.3 The GARCH modelThe general form of the GARCH(p, q) model is:σt2 = ω +
pi=1
2αi εt−i+
q
2βj σt−j
(10.8)
j =1
max(p,q)(αi + βi ) < 1, thisWhere all the parameters are positive or equal to zero and i=1implies that the unconditional volatility of the process exists (we will show this later on)and provides the necessary stationary condition for the stochastic process.The GARCH model allows more ﬂexibility to be built into the volatility process thanRiskMetrics but the extra complexity comes at a price, in that the optimisation is morecomplex (maximum likelihood functions are usually known to be very ﬂat functions oftheir parameters, which makes it difﬁcult for optimisers).We will restrict our analysis to the ﬁnancial industry standard (Engle and Mezrich, 1995),the GARCH(1,1) with just one lag for the residual and one lag for the conditional variance.As we can see from the above equation, the RiskMetrics equation is embedded in it.7 Wehave shown previously that the term structure of volatility forecast is ﬂat for RiskMetrics,here however the GARCH(1,1) allows for a mean-reversion process to take place for the227In a GARCH(1,1), the volatility equation becomes: σt2 = ω + αεt−1+ βσt−1. If we replace ω by 0 and α by1 − β, then the model falls back to the RiskMetrics equation.
Applied Volatility and Correlation Modelling
319
volatility. That is, the volatility mean reverts to a long-term average, the unconditionalvolatility of the process.To forecast the volatility, we have to iterate equation (10.8) forward (with p = 1 andq = 1):2σt2 (1) = E(σt+1/t) = ω + αεt2 + βσt222σt2 (2) = E(ω + αεt+1+ βσt2 (1)) = ω + αE(εt+1) + βσt2 (1)
= ω + αE(σt2 (1)) + βσt2 (1)= ω + (α + β)σt2 (1)σt2 (3) = ω + (α + β)σt2 (2) = ω(1 + (α + β)) + (α + β)2 σt2 (1)Hence for a forecasting horizon h, we have:σt2 (h) = ω
1 − (α + β)h−1+ (α + β)h−1 σt2 (1)1 − (α + β)
(10.9)
When h tends to inﬁnity, we have the following long-term variance of the GARCH(1,1)process:ωlim σt2 (h) = σ 2 =(10.10)h→∞1−α−βwhere σ is the unconditional variance.The spreadsheet-based application (“DataGARCH” sheet in Bourgoin001.xls) is notvery different from the optimal decay sheet; only now omega and alpha are part of theoptimisation routine (Figure 10.5).
Figure 10.5 The GARCH model
320
Applied Quantitative Methods for Trading and Investment
However because we know the long-term volatility σ 2 using historical data and thestandard time series approach:1 (rt − r)2N t=1N
σ2 =
or
=
1E(RR  )T
(10.11)
We can replace ω by using equations (10.10) and (10.11) together:ω = σ 2 (1 − α − β)
(10.12)
ω is then NOT required in the optimisation process! The technique has been pioneeredby Engle and Mezrich (1995) and is called the variance targeting technique, so we canmake the optimisation routine simpler by reducing the number of parameters from threeto two. One should note that on the spreadsheet in Figure 10.6 (“DataGARCH VT” inBourgoin001.xls) only alpha and beta are greyed out.As we can see the likelihood function $I$8 is identical to the normal GARCH modelin the previous example, so there seems to be no loss of likelihood in the model and wewere able to reduce the number of parameters at the same time.
Figure 10.6 The GARCH model with variance targeting
10.3.4 THE GJR modelSince Black (1976) it is well accepted that stocks have an asymmetric response to news.In order to account for these asymmetries encountered in the market, Glosten, Jagannathan
Applied Volatility and Correlation Modelling
321
and Runkle (GJR; Glosten et al., 1993) extended the framework of the GARCH(1,1) totake this into account. The augmented model can be written as:0 if εt > 0222σt2 = ω + αεt−1+ βσt−1+ γ St−1 εt−1St =(10.13)rt = σt εt1 if εt < 02with α > 0, β > 0, γ > 0 and α + β + 12 γ < 1. When returns are positive, γ St−1 εt−1is equal to zero and the volatility equation collapses to a GARCH(1,1) equation; onthe contrary, when returns are negative, the volatility equation is augmented by γ , apositive number, meaning that the impact of this particular negative return is bigger onthe estimation of volatility, as shown below:
if εt < 0,
22σt2 = ω + (α + γ )εt−1+ βσt−1
We can forecast the volatility term structure:2σGJR,t(h) = ω
h−11 − α + β + 12 γh−1 2+ α + β + 12 γσGJR,t (1)11 − α + β + 2γ
(10.14)
2with σGJR,t(1) = ω + αεt2 + βσt2 + γ St εt2 /2. We can derive the long-term variance process from this model as:2lim σGJR,t(h) = σ 2 =
h→∞
ω1 − α − β − γ /2
(10.15)
The spreadsheet-based application (Figure 10.7) is located in “DataGARCHGJR VT”(Bourgoin001.xls).
Figure 10.7 The GJR model with variance targeting
322
Applied Quantitative Methods for Trading and Investment
Remarks:• Here we use again the variance targeting process (note that $I$4, omega is not greyedout) in order to facilitate the optimisation process.• One good way to ensure that the optimisation has good starting values is to give alphaand beta the values of a GARCH(1,1) process with gamma equal to a very low positivevalue (say 0.01 for example as long as alpha+beta+0.5∗ gamma is strictly less than 1).• One should also note that the likelihood function is slightly higher in GJR(1,1) with2548.37 against 2543.64 for the GARCH(1,1). The likelihood ratio test8 performedin $I$10 allows us to conclude that this model is capturing some of the unaccountedvolatility dynamics of the simple GARCH model.10.3.5 Model comparisonSince we’ve calculated several types of volatility model, we can now look at the onestep-ahead volatility forecast over time (Figure 10.8).Several remarks can be made:• The GJR model shows clearly that it is more sensitive to the big downward movementsof the S&P500. For example, in August 1998, it shows a substantial increase in volatilitycompared to the GARCH model or any of the weighted-decay models.• There are clearly two distinct types of model here: the weighted-decay on the one sideand the GARCH-type model on the other. This can be shown from the high degreeof persistence of the volatility with the weighted-decay models (after a shock theirLTCM melt-down generatessubstantially more volatilitywith an asymmetric model
45.00%40.00%
16001500
Volatility
1300
30.00%
1200
25.00%
11001000
20.00%90015.00%
800
Weighted-decay modelsunder-estimate volatility
70016-Apr-9816-May-9816-Jun-9816-Jul-9816-Aug-9816-Sep-9816-Oct-9816-Nov-9816-Dec-9816-Jan-9916-Feb-9916-Mar-9916-Apr-9916-May-9916-Jun-9916-Jul-9916-Aug-9916-Sep-9916-Oct-9916-Nov-9916-Dec-9916-Jan-0016-Feb-0016-Mar-0016-Apr-0016-May-0016-Jun-0016-Jul-0016-Aug-0016-Sep-0016-Oct-0016-Nov-0016-Dec-0016-Jan-0116-Feb-0116-Mar-0116-Apr-0116-May-0116-Jun-0116-Jul-01
10.00%
GARCH Volatility VT
Volatility RiskMetricsGJR Ann. Volatility VTS&P500 Index
Optimal Decay Ann. Vol
Figure 10.8 Comparison between different volatility models8
For the likelihood ratio test, refer to Gallant (1997, p. 181).
S&P500 Index
140035.00%
Applied Volatility and Correlation Modelling
323
volatility is consistently higher than the GARCH-type models), because the persistenceparameters α + β = 1 = λ compared to α + β < 1 for the GARCH model.• We can also see that during calm periods, the weighted-decay volatilities tend todecrease much more than the GARCH models, this is due to the fact that there isno mean reversion embedded in these models. If we take the extreme case, the volatility will progressively tend to zero if the asset returns are zero in the near future; thishas been one of the main criticisms of the RiskMetrics model.The difference in the volatility estimation not only differs historically, but also when wetry to forecast the term structure of volatility in the future (Figure 10.9). As we’ve shownearlier, the weighted-decay methodology does not provide any insight into the futurebehaviour of the term structure of volatility since there is no information in its termstructure (the term structure of forecast is ﬂat and equal to the one-step-ahead volatilityforecast). On the other hand, the GARCH-type model shows us the mean-reversion processto the long-term volatility (the unconditional volatility) happening over time. Here, wecan see that the GARCH model mean reverts slightly quicker than the GJR model, butboth models show that the volatility is likely to increase in the next 5 months (horizonof forecasting).In this section, we’ve seen that it is possible to estimate and forecast volatility usingcomplex volatility models within the Excel framework. However if one wants, for example,to do complicated statistical tests for diagnostic checking, Excel will not be a suitableframework for a proper econometric analysis of ﬁnancial time series. But for commonday-to-day analysis, the practicality and user friendliness is unrivalled.The next section will introduce the modelling of conditional correlation using the sameframework utilised above.
22.00%
LT Volatility
20.00%GJR-VT(1,1)
18.00%GARCH(1,1)16.00%
14.00%
RiskMetrics & optimal decay models
12.00%
7-Aug-0112-Aug-0117-Aug-0122-Aug-0127-Aug-011-Sep-016-Sep-0111-Sep-0116-Sep-0121-Sep-0126-Sep-011-Oct-016-Oct-0111-Oct-0116-Oct-0121-Oct-0126-Oct-0131-Oct-015-Nov-0110-Nov-0115-Nov-0120-Nov-0125-Nov-0130-Nov-015-Dec-0110-Dec-0115-Dec-0120-Dec-0125-Dec-0130-Dec-01
10.00%
Figure 10.9 S&P500 term structure of volatility forecast for different models
324
Applied Quantitative Methods for Trading and Investment
10.4 MULTIVARIATE MODELSIn this section, we will show how to perform multivariate models to calculate conditionalcorrelation estimation and forecast the term structure using Excel. Several models willbe considered, the J.P. Morgan RiskMetrics, the optimal decay model and three GARCHmodels: the full diagonal GARCH, and its simpler derivative with variance targeting, andthe superfast GARCH model (Bourgoin, 2002). For convenience purposes and simplicity,we will consider only a two-variable system, more can be added but with additionalcomplexity in the spreadsheet set-up. Each calculation devoted to a speciﬁc asset orcross-term (volatility for asset 1, asset 2 and the covariance) will have its own backgroundcolour in the workbook Bourgoin002.xls. In this way it is easier to read and understandthe spreadsheet.10.4.1 The RiskMetrics modelLet us consider a two-asset model, where both volatilities and the covariance follow theRiskMetrics (RiskMetrics Technical Document, p. 82) equation:222σ1,t= (1 − λ)ε1,t−1+ λσ1,t−1222σ2,t= (1 − λ)ε2,t−1+ λσ2,t−1
(10.16)
22σ12,t= (1 − λ)ε1,t−1 ε2,t−1 + λσ12,t−1
The initialisation of σ1,0 , σ2,0 , σ12,0 is set to the unconditional volatilities and covariance.The model has very appealing attributes in a multivariate framework: it is very quick tocalculate for a large size covariance matrix and the covariance is always positive deﬁniteby construction as long as λ is the same everywhere in the covariance matrix.9 But thedrawbacks are still the same in a multivariate framework: ﬂat term structure of forecast,too much persistence of shocks and too low volatilities during calm periods.The spreadsheet of Figure 10.10 (“RiskMetrics” in Bourgoin002.xls) shows the implementation of RiskMetrics for volatilities and correlations for two stock indexes, the FrenchCAC40 and the German DAX30. The volatilities are calculated in exactly the sameway as in Bourgoin001.xls, but here we add the cross-term to calculate the covariance.Equation (10.16) is calculated in columns F, G and H respectively. Column I is theresulting correlation by applying the standard textbook formula:ρi,j = σi,j /(σi σj )10.4.2 The optimal decay modelThe optimal decay model is a generalisation of the RiskMetrics model where the optimaldecay is not predetermined at 0.94. The objective function is the likelihood function. In9A matrix is positive deﬁnite as long as its minimum eigenvalue is strictly positive. This is crucial for riskmanagement purposes because otherwise a linear combination of assets in the portfolio can yield to a negativeportfolio variance. For a more detailed explanation, refer to J.P. Morgan (1997).
Applied Volatility and Correlation Modelling
Figure 10.10
325
The RiskMetrics model bivariate
order to write the likelihood function, we need to deﬁne the probability distribution, herewe will use the multivariate normal distribution:f (x, µ, ) =
1(2π )n/2 ||1/2
exp
−1(x − µ) −1 (x − µ)2
(10.17)
where µ