In this section, we will show how to perform multivariate models to calculate conditional correlation estimation and forecast the term structure using Excel. Several models will be considered, the J.P. Morgan RiskMetrics, the optimal decay model and three GARCH models: the full diagonal GARCH, and its simpler derivative with variance targeting, and the superfast GARCH model (Bourgoin, 2002). For convenience purposes and simplicity, we will consider only a two-variable system, more can be added but with additional complexity in the spreadsheet set-up. Each calculation devoted to a specific asset or cross-term (volatility for asset 1, asset 2 and the covariance) will have its own background colour in the workbook Bourgoin002.xls. In this way it is easier to read and understand the spreadsheet.
Let us consider a two-asset model, where both volatilities and the covariance follow the RiskMetrics (RiskMetrics Technical Document, p. 82) equation:
The initialisation of a10, a2 0, a12,0 is set to the unconditional volatilities and covariance.
The model has very appealing attributes in a multivariate framework: it is very quick to calculate for a large size covariance matrix and the covariance is always positive definite by construction as long as X is the same everywhere in the covariance matrix.9 But the drawbacks are still the same in a multivariate framework: flat 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 French CAC40 and the German DAX30. The volatilities are calculated in exactly the same way 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 the resulting correlation by applying the standard textbook formula:
Was this article helpful?