<<

. 14
( 19)



>>

where Fj t is the value of factor j at time t (j = 1, 2, . . . , K), βij (sj t ) is the factor loading
of the asset i on factor j , sj t , j = 1, 2, . . . , K, are independent Markov chains, and µj t ,
j = 1, 2, . . . , K, and µj t , j = 1, 2, . . . , N , are independently distributed.
This model is more parsimonious, in fact the introduction of an extra asset implies that
only K + 2 parameters need to be estimated. This approach is valid when the number of
assets in the portfolio is high and the speci¬c risk is easily eliminated by diversi¬cation.
284 Applied Quantitative Methods for Trading and Investment

8.8 CONCLUDING REMARKS
We have tried to develop an introduction to the current literature on stochastic volatility
models. Other than the classical log-normal model introduced by Taylor (1986), we have
also presented the discrete volatility model in which the latent stochastic structure of the
volatility is described by a Markov chain.
Both models (with continuous and discrete volatility) ¬t in the framework of a non-
linear and non-Gaussian state space model, thus the estimation and smoothing problems
are developed along the same lines. Only for the discrete case does the general algorithm
introduced allow us to compute the likelihood function and then to obtain maximum likeli-
hood estimates. In the continuous case, approximations or simulations must be introduced.
Some extensions and multivariate models are also presented, however there is still a
great deal of work to be done.
Finally, the estimation program presented considers the two basic models and allows an
estimation of the latent volatility. Some possible applications are suggested and discussed.


APPENDIX A: APPLICATION OF THE PENTANOMIAL MODEL
The example considers a European call option on the FTSE100 index, with maturity June
2001 and strike price 5900 traded at LIFFE on 22 August 2000. Three-month Libor is
used as an approximation of the risk-free interest rate r. The FTSE100 index quoted
6584.82 and Libor was 6.22%.
The FTSE100 index being a weighted average of the prices of 100 stocks, the dividend
effect must be considered. In the example, this parameter is considered constant48 and
equal to q = 3%. The time to maturity in terms of trading days is 213 and the step size
is the single trading day ( t = 1).
Taking the estimated volatilities of Section 8.7.1.1 (σ0 = 0.0108036 and σ1 =
ˆ ˆ
0.0234427), the parameters of the pentanomial model can be computed as follows:


’ σ1 +(r’q)2
ˆ2
’e
r’q
e
p1,u = √ 2 = 0.4968
√2
e σ1 +(r’q) ’ e’ σ1 +(r’q)
ˆ ˆ
2 2


1√ 2 1√ 2
√2 ’ σ1 +(r’q)2
ˆ
’ pm 1 ’ e 2 σ1 +(r’q)
’ ˆ 2
’e
r’q
e 2
σ1 +(r’q)2
ˆ
u1 = e = 1.0237 p0,u = 1√ 2 1√ 2
1√ 2 σ1 +(r’q)2
ˆ
’ e 2 σ1 +(r’q)
’ ˆ 2
σ1 +(r’q)2
ˆ
u0 = e 2 = 1.0118 e2
= 0.4277
1√ 2
’ σ1 +(r’q)2
ˆ
d0 = e 2 = 0.9883 « 2
√2
¬ σ0 + (r ’ q) ·
ˆ 2 2
d1 = e’ σ1 +(r’q) = 0.9768
ˆ 2

pm = 1 ’ 4   = 0.1504
σ1 + (r ’ q)
ˆ 2 2

p0,d = 1 ’ p0,u ’ pm = 0.4219
p1,d = 1 ’ p1,u = 0.5032


48
During the period of analysis the dividend yield was nearly constant. For details, see
http://www.londonstockexchange.com.
Stochastic Volatility Models 285

Given u1 , u0 , d0 , d1 , the possible one-step-ahead values for the FTSE100 index can be
calculated as follows:

’ u1 St = 6741.01
’ u0 St = 6662.45
St+1 ’ St = 6584.82
’ d0 St = 6508.11
’ d1 St = 6432.25

and the tree is recursively generated.
The payoffs at maturity are calculated with the usual formula:

CT = max(ST ’ K, 0)

and the values of the option in the previous periods are obtained operating backwards.
In particular:

• At time T ’ 1, the conditional values of the option at the ith node, CT ’1 (sT ’1 = 0),
i

CT ’1 (sT ’1 = 1), are given by
i



CT ’1 (sT ’1 = 0) = [(1 ’ p0,u ’ pm )CT + pm CT + p0,u CT ]e’r
i’1 i+1
i i

CT ’1 (sT ’1 = 1) = [(1 ’ p1,u )CT + p1,u CT ]e’r
i’2 i+2
i



Note that the nodes ±1 consider the option values obtained with u0 and d0 , while the
nodes ±2 consider u1 and d1 . The calculation is repeated for all the nodes at time T ’ 1
and we obtain two sets of conditional option values CT ’1 (sT ’1 = 0), CT ’1 (sT ’1 = 1).
i i

• At time T ’ 2
“ for each node i, the values of the option are obtained conditional on the regime in
T ’ 1:

CT ’2/T ’1 (sT ’1 = 0) = [(1 ’ p0,u ’ pm )CT ’1 (sT ’1 = 0) + pm CT ’1 (sT ’1 = 0) + p0,u CT ’1 (sT ’1 = 0)]
i’1 i+1
i i

CT ’2/T ’1 (sT ’1 = 1) = [(1 ’ p0,u ’ pm )CT ’1 (sT ’1 = 1) + pm CT ’1 (sT ’1 = 1) + p0,u CT ’1 (sT ’1 = 1)]
i’1 i+1
i i




“ using the estimated transition probabilities (p00 = 0.992889, p11 = 0.963762), they
ˆ ˆ
are then discounted considering the possibility that a switch occurs between time
T ’ 1 and T ’ 2:

CT ’2 (sT ’2 = 0) = [p00 CT ’2/T ’1 (sT ’1 = 0) + (1 ’ p00 )CT ’2/T ’1 (sT ’1 = 1)]e’r
ˆ ˆ
i i i

CT ’2 (sT ’2 = 1) = [(1 ’ p11 )CT ’2/T ’1 (sT ’1 = 0) + p11 CT ’2/T ’1 (sT ’1 = 1)]e’r
ˆ ˆ
i i i



Again we obtain two sets of conditional option values CT ’2 (sT ’2 = 0), CT ’2 (sT ’2 = 1).
i i

• This computation is iterated for T ’ 3, T ’ 4, . . . , t.

At the evaluation time t, we obtain two conditional values Ct (st = 0), Ct (st = 1), which
are respectively 1047.61 and 1093.54. Finally, the value of the option is calculated as a
286 Applied Quantitative Methods for Trading and Investment

weighted average of these two values, where the weights depend on the knowledge of
the current regime. If the regime is unknown, the estimated unconditional probabilities

1 ’ p11
ˆ 1 ’ p00
ˆ
p0 =
ˆ p1 =
ˆ
2 ’ p00 ’ p11
ˆ ˆ 2 ’ p00 ’ p11
ˆ ˆ

can be used.
The pentanomial option value is therefore 1055.14 while the market value was 1047.5.
Another possibility, probably better from a methodological point of view as it uses all
the available information, is to consider the ¬ltered probabilities obtained as output of the
estimation step, i.e. P (st = 0|Y t ) = 0.98636043 and P (st = 1|Y t ) = 0.01363957. In that
case, the pentanomial option value is 1048.24.
As an exercise, readers may wish to replicate the following examples:

1. European put option quoted on 23/10/2000, strike price: 7000, FTSE100: 6315.9, Libor:
6.1475%, days to maturity: 39, option price: 700.
Results: Conditional option values: 756.15 and 679.89. Option value considering
unconditional probabilities: 692.4.
2. European put option quoted on 03/11/2000, strike price: 5500, FTSE100: 6385.44,
Libor: 6.12625%, days to maturity: 160, option price: 117.
Results: Conditional option values: 143.29 and 101.08. Option value considering
unconditional probabilities: 108.


APPENDIX B: APPLICATION TO VALUE AT RISK
Consider a portfolio which perfectly replicates the composition of the FTSE100 index.
Given the estimated volatility of the stochastic volatility models, the VaR of this portfolio
can be obtained following the procedure proposed in Barone-Adesi et al. (1998).
The historical portfolio returns are rescaled by the estimated volatility series to obtain
the standardised residuals ut = yt /σt , t = 1, . . . , T (in our case T = 898, see footnote 33).
The historical simulation can be performed by bootstrapping the standardised returns to
obtain the desired number of residuals u— , j = 1, . . . , M, where M can be arbitrarily large.
j
To calculate the next period returns, it is suf¬cient to multiply the simulated residuals by
ˆ
the forecasted volatility σT +1/T :

yj = u— σT +1/T




The VaR for the next day, at the desired level of con¬dence h, is then calculated as the
Mhth element of these returns sorted in ascending order.


REFERENCES
Alexander, C. (1996), The Handbook of Risk Management and Analysis, John Wiley, New York.
Andersen, T. G. and T. Bollerslev (1997), “Heterogeneous information arrivals and return volatility
dynamics: uncovering the long run in high frequency returns”, Journal of Finance, 52, 975“1005.
Andersen, T. G. and B. E. Sørensen (1996), “GMM estimation of a stochastic volatility model: a
Monte Carlo study”, Journal of Business and Economic Statistics, 14, 328“352.
Stochastic Volatility Models 287
Andersen, T. G. and B. E. Sørensen (1997), “GMM and QML asymptotic standard deviations in
stochastic volatility models: a comment on Ruiz (1994)”, Journal of Econometrics, 76, 397“403.
Andersen, T. G., H. Chung and B. E. Sørensen. (1999), “Ef¬cient method of moments estimation
of a stochastic volatility model: a Monte Carlo study”, Journal of Econometrics, 91, 61“87.
Anderson, B. and J. Moore (1979), Optimal Filtering, Prentice Hall, New York.
Ball, C. and W. Torous (1985), “On jumps in common stock prices and their impact on call option
pricing”, Journal of Finance, 40, 155“173.
Barone-Adesi, G., F. Burgoin and K. Giannopoulos (1998), “Don™t look back”, Risk, 11, 100“104.
Bates, D. S. (1996), “Jumps and stochastic volatility: exchange rate processes implicit in Deutsche
mark options”, The Review of Financial Studies, 9, 69“107.
Bera, A. and M. Higgins (1993), “Arch models: properties, estimation and testing”, Journal of
Economic Surveys, 7, 305“366.
Best, P. (1998), Implementing Value at Risk, John Wiley & Sons, New York.
Billio, M. (1999), Simulation based methods for non linear state space models, Ph.D. thesis, Paris
Dauphine.
Billio, M. (2002a), “Correlated Markov chain”, Working Paper, GRETA, Venice.
Billio, M. (2002b), “Simulation based methods for ¬nancial time series”, Atti della XLI Riunione
Scienti¬ca of Italian Statistical Society, Universit` di Milano-Bicocca, 5“7 June.
a
Billio, M. and A. Monfort (1998), “Switching state space models: likelihood, ¬ltering and smooth-
ing”, Journal of Statistical Planning and Inference, 68/1, 65“103.
Billio, M. and L. Pelizzon (1997), “Pricing options with switching volatility”, in Money, Finance,
Banking and Insurance, C. Hipp (ed.), Verlang.
Billio, M. and L. Pelizzon (2000), “Value-at-Risk: a multivariate switching regime approach”, Jour-
nal of Empirical Finance, 7, 531“554.
Billio, M., A. Monfort and C. P. Robert (1998), “The simulated likelihood ratio (SLR) method”,
Document de Travail du CREST 9828, Paris.
Black, F. and M. Scholes (1973), “The pricing of options and corporate liabilities”, Journal of
Political Economy, 81, 637“654.
Bollen, N. (1998), “Valuating option in regime switching models”, Journal of Derivatives, 6, 38“49.
Bollen, N., S. Gray and R. Whaley (2000), “Regime-switching in foreign exchange rates: evidence
from currency options”, Journal of Econometrics, 94, 239“276.
Bollerslev, T. (1990), “Modelling the coherence in short-run nominal exchange rates: a multivariate
generalized ARCH approach”, Review of Economics and Statistics, 72, 498“505.
Bollerslev, T. and D. Jubinski (1999), “Equity trading volume and volatility: latent information
arrivals and common long-run dependencies”, Journal of Business and Economic Statistics, 17,
9“21.
Bollerslev, T. and H. O. Mikkelsen (1999), “Long-term equity anticipation securities and stock
market volatility dynamics”, Journal of Econometrics, 92, 75“99.
Bollerslev, T. and J. Wright (2000), “Semiparametric estimation of long-memory volatility depen-
dencies: the role of high-frequency data”, Journal of Econometrics, 98, 81“106.
Bollerslev, T., R. Chow and K. Kroner (1992), “Arch modelling in ¬nance: a review of the theory
and empirical evidence”, Journal of Econometrics, 52, 5“59.
Bollerslev, T., R. F. Engle and D. Nelson (1993), “Arch models”, in Handbook of Econometrics,
R. Engle and D. McFadden (eds), North-Holland, Amsterdam, vol. IV.
Breidt, F. J., N. Crato and P. de Lima (1998), “On the detection and estimation of long memory
in stochastic volatility”, Journal of Econometrics, 83, 325“348.
Carraro, C. and D. Sartore (1987), “Square root iterative Kalman ¬lter: theory and applications to
regression models”, Annales d™Economie et de Statistique, 6/7.
Chan, N. H. and G. Petris (1999), “Bayesian analysis of long memory stochastic volatility models”,
Technical Report, Department of Statistics, Carnegie Mellon University.
Chib, S., F. Nardari and N. Shephard (2002), “Markov Chain Monte Carlo methods for stochastic
volatility models”, Journal of Econometrics, 108/2, 281“316.
Clark, P. K. (1973), “A subordinated stochastic process model with ¬xed variance for speculative
prices”, Econometrica, 41, 135“156.
Cox, D. R. (1981), “Statistical analysis of time series: some recent developments”, Scandinavian
Journal of Statistics, 8, 93“115.
288 Applied Quantitative Methods for Trading and Investment
Cox, J. C., S. A. Ross and M. Rubinstein (1979), “Option pricing: a simpli¬ed approach”, Journal
of Financial Economics, 7, 229“263.
Danielsson, J. (1994), “Stochastic volatility in asset prices: estimation with simulated maximum
likelihood”, Journal of Econometrics, 61, 375“400.
Danielsson, J. and J. Richard (1993), “Accelerated Gaussian importance sampler with application
to dynamic latent variable models”, Journal of Applied Econometrics, 8, 153“173.
Davies, R. B. (1977), “Hypothesis testing when a nuisance parameter is present only under the
alternative”, Biometrika, 64, 247“254.
Davies, R. B. (1987), “Hypothesis testing when a nuisance parameter is present only under the
alternative”, Biometrika, 74, 33“43.
de Jong, P. (1989), “Smoothing and interpolation with the state space model”, Journal of the
American Statistical Association, 84, 1085“1088.
Deo, R. S. and C. M. Hurvich (2001), “On the log periodogram regression estimator of the memory
parameter in long memory stochastic volatility models”, Econometric Theory, 17, 686“710.
Doornik, J. A. (2001), Ox: An Object-Oriented Matrix Language (4th edition), Timberlake Consul-
tants Press, London.
Dridi, R. (2000), “Simulated asymptotic least squares theory”, Working Paper 396, London School
of Economics“Suntory Toyota, Econometrics.
Dridi, R. and E. Renault (2000), “Semi-parametric indirect inference”, Working Paper 392, London
School of Economics“Suntory Toyota, Econometrics.
Duf¬e, D. and K. Singleton (1993), “Simulated moments estimation of Markov models of asset
prices”, Econometrica, 61, 929“952.
Duf¬e, D., J. Pan and K. Singleton (2000), “Transform analysis and asset pricing for af¬ne jump-
diffusions”, Econometrica, 68, 1343“1376.
Durbin, J. and S. Koopman (1997), “Monte Carlo maximum likelihood estimation for non Gaussian
state space models”, Biometrika, 84, 669“684.
Eberlein, E. (2001), “Application of generalized hyperbolic L´ vy motion to ¬nance”, in L´ vy
e e
Processes“Theory and Applications, O. E. Barndorff-Nielsen, T. Mikosch and S. Resnick (eds),
Birkhauser Boston, MA, pp. 319“337.
Eberlein, E. and K. Prause (2001), “The generalized hyperbolic model: ¬nancial derivatives and
risk measures”, in Proceedings of the Year 2000 Congress of the European Mathematical Society.
Eberlein, E., J. Kallsen and J. Kristen (2001), “Risk management based on stochastic volatility”,
FDM Preprint 72, University of Freiburg.
Engle, R. F. (1982), “Autoregressive conditional heteroskedasticity with estimates of the variance
of the United Kingdom in¬‚ation”, Econometrica, 50, 987“1007.
Engle, R. F. and G. G. J. Lee (1992), “A permanent and transitory component model of stock return
volatility”, UCSD Economics Discussion Papers 92-44, University of California at San Diego.
Engle, R. F., D. M. Lilien and R. P. Robins (1987), “Estimating time-varying risk premium in the
term structure: the ARCH-M model”, Econometrica, 55, 391“407.
Francq, C. and J. M. Zako¨an (2001), “Stationarity of multivariate Markov-switching ARMA mod-
±
els”, Journal of Econometrics, 102, 339“364.
Francq, C., M. Roussignol and J. M. Zako¨an (2001), “Conditional heteroskedasticity driven by
±
hidden Markov chains”, Journal of Time Series Analysis, 22/2, 197“220.
Fridman, M. and L. Harris (1998), “A maximum likelihood approach for non-Gaussian stochastic
volatility models”, Journal of Business and Economic Statistics, 16, 284“291.
Gallant, A. and J. Long (1997), “Estimating stochastic differential equations ef¬ciently by minimum
chi-square”, Biometrika, 84, 125“141.
Gallant, A. and G. Tauchen (1996), “Which moments to match?”, Econometric Theory, 12, 657“681.
Gallant, A., D. Hsieh and G. Tauchen (1997), “Estimation of stochastic volatility models with
diagnostics”, Journal of Econometrics, 81, 159“192.
Gelfand, A. and A. Smith (1990), “Sampling based approaches to calculating marginal densities”,
Journal of the American Statistical Association, 85, 398“409.
Geweke, J. and S. Porter-Hudak (1983), “The estimation and application of long-memory time
series models”, Journal of Time Series Analysis, 4, 221“238.
Geyer, C. (1994), “On the convergence of Monte Carlo maximum likelihood calculations”, Journal
of the Royal Statistical Society, B 65, 261“274.
Stochastic Volatility Models 289
Geyer, C. (1996), “Estimation and optimization of functions”, in Markov Chain Monte Carlo
in Practice, W. Gilks, S. Richardson and D. Spiegelhalter (eds), Chapman and Hall, London,
pp. 241“258.
Ghysels, E., A. Harvey and E. Renault (1996), “Stochastic volatility”, in: Statistical Methods in
Finance, C. Rao and G. Maddala (eds), North-Holland, Amsterdam.
Gordon, N., D. Salmond and A. Smith (1993), “A novel approach to non-linear and non Gaussian
Bayesian state estimation”, IEE Proceedings, F140, 107“133.
Gouri´ roux, C., A. Monfort and E. Renault (1993), “Indirect inference”, Journal of Applied Econo-
e
metrics, 8, 85“118.
Hamilton, J. D. (1989), “A new approach to the economic analysis of nonstationary time series and
the business cycle”, Econometrica, 57/2, 357“384.
Hamilton, J. D. (1994), Time Series Analysis, Princeton University Press, Princeton, NJ.
Hansen, B. E. (1992), “The likelihood ratio test under nonstandard conditions: testing the Markov
switching model of GNP”, Journal of Applied Econometrics, 7, S61“S82.
Hansen, B. E. (1996), “Erratum: The likelihood ratio test under nonstandard conditions: testing the
Markov switching model of GNP”, Journal of Applied Econometrics, 11/2, 195“198.
Harvey, A. C. (1989), Forecasting, Structural Time Series Models and the Kalman Filter, Cambridge
University Press, Cambridge.
Harvey, A. C. (1998), “Long-memory in stochastic volatility”, in Forecasting Volatility in the Finan-
cial Market, J. Knight and S. Sachell (eds), Butterworth-Heinemann, London.
Harvey, A. C. and N. Shephard (1993), “Estimation and testing stochastic variance models”, Lon-
don School of Economics“Suntory Toyota, Econometrics, no. 93“268.
Harvey, A. C. and N. Shephard (1996), “Estimation of an asymmetric model of asset prices”,
Journal of Business and Economic Statistics, 14/4, 429“434.
Harvey, A. C., E. Ruiz and N. Shephard (1994), “Multivariate stochastic variance models”, Review
of Economic Studies, 61, 247“264.
Heston, S. (1993), “A closed-form solution for option with stochastic volatility with application to
bond and currency options”, The Review of Financial Studies, 6, 327“343.
Hull, J. and A. White (1987), “The pricing of options on assets with stochastic volatilities”, Journal
of Finance, 42, 281“300.
Hull, J. and A. White (1998), “Incorporating volatility updating into the historical simulation
method for Value-at-Risk”, The Journal of Risk, 1, 5“19.
Hurvich, C. M. and B. K. Ray (2001), “The local Whittle estimator of long memory stochastic
signal volatility”, Working Paper, Department of Statistics and Operations Research, New York
University, http://www.stern.nyu.edu/sor/research/wp01.html.
Hurvich, C. M., E. Moulines and P. Soulier (2001), “Estimation of long memory in stochastic
volatility”, Preprint 153, Universit´ d™Evry-Val d™Essonne, http://www.tsi.enst.fr/∼soulier/
e
paper.html.
Jacquier, E., N. G. Polson and P. Rossi (1994), “Bayesian analysis of stochastic volatility models”,
Journal of Business and Economic Statistics, 12, 371“417.
Jacquier, E., N. G. Polson and P. Rossi (1995), “Models and Priors for Multivariate Stochastic
Volatility”, CIRANO Working Papers 95-18.
Jensen, M. J. (1999), “An approximate wavelet MLE of short and long memory parameters”, Studies
in Nonlinear Dynamics and Econometrics, 3, 239“353.
Jensen, M. J. (2000), “An alternative maximum likelihood estimator of long-memory processes
using compactly supported wavelets”, Journal of Economic Dynamics and Control, 24, 361“387.
Jensen, M. J. (2001), “Bayesian inference of long-memory stochastic volatility via wavelets”, Work-
ing Paper 01“02, Department of Economics, University of Missouri-Columbia.
Kalman, R. E. (1960), “A new approach to linear ¬ltering and prediction problems”, Journal of
Basic Engineering, Transactions ASMA, Series D 82, 35“45.
Kim, C. J. (1994), “Dynamic linear models with Markov-switching”, Journal of Econometrics, 60,
1“22.
Kim, C. J. and C. R. Nelson (1999), State-Space Models with Regime Switching, MIT Press, Cam-
bridge, MA.
Kim, S., N. Shephard and S. Chib (1998), “Stochastic volatility: likelihood inference and com-
parison with arch models”, Review of Economic Studies, 65, 361“393.
290 Applied Quantitative Methods for Trading and Investment
Kitagawa, G. (1987), “Non-Gaussian state space modeling of nonstationary time series” (with dis-
cussion), Journal of the American Statistical Association, 82, 400, 1032“1063.
Kitagawa, G. (1996), “Monte Carlo ¬lter and smoother for non-Gaussian non linear state space
models”, Journal of Computational and Graphical Statistics, 5, 1“25.
Kitagawa, G. and W. Gersch (1984), “A smoothness priors-state space modeling of time series with
trend and seasonality”, Journal of the American Statistical Association, 79, 378“389.
Lehar, A., M. Scheicher and C. Schittenkopf (2002), “Garch vs stochastic volatility: option pricing
and risk management™, Journal of Banking and Finance, 26(2& 3), 323“345.
Lobato, I. and N. E. Savin (1998), “Real and spurious long-memory properties of stock-market
data”, Journal of Business and Economic Statistics, 16, 261“268.
Mahieu, R. and P. Schotman (1998), “An empirical application of stochastic volatility models”,
Journal of Applied Econometrics, 16, 333“359.
McNeil, A. J. and R. Frey (2000), “Estimation of tail-related risk measures for heteroskedastic
¬nancial time series: an extreme value approach”, Journal of Empirical Finance, 7, 271“300.
Melino, A. and S. M. Turnbull (1990), “Pricing foreign currency options with stochastic volatility”,
Journal of Econometrics, 45, 239“265.
Merton, R. C. (1976), “Option pricing when underlying stock returns are discontinuous”, Journal
of Financial Economics, 3, 125“144.
Monfardini, C. (1998), “Estimating stochastic volatility models through indirect inference”, Econo-
metrics Journal, 1, 113“128.
Naik, V. (1993), “Option valuation and hedging strategies with jumps in the volatility of asset
returns”, Journal of Finance, 48, 1969“1984.
Pitt, M. and N. Shephard (1999a), “Filtering via simulation: auxiliary particle ¬lter”, Journal of the
American Statistical Association, 94, 590“599.
Pitt, M. and N. Shephard (1999b), “Time varying covariances: a factor stochastic volatility
approach” with discussion, in Bayesian Statistics, J. Bernardo, J. O. Berger, A. P. Dawid and
A. F. M. Smith (eds), Oxford University Press, Oxford, Vol. 6, pp. 547“570.
Ray, B. K. and R. Tsay (2000), “Long-range dependence in daily stock volatilities”, Journal of
Business and Economic Statistics, 18, 254“262.
Robinson, P. M. (1995), “Log-periodogram regression of time series with long range dependence”,
Annals of Statistics, 23, 1043“1072.
Rubinstein, M. (1985), “Nonparametric tests of alternative option pricing models using all reported
trades and quotes on the 30 most active CBOE options classes from August 23, 1976 through
August 21, 1978”, The Journal of Finance, 40, 455“480.
Ruiz, E. (1994), “Quasi maximum likelihood estimation of stochastic volatility models”, Journal
of Econometrics, 63, 289“306.
Sandmann, G. and S. Koopman (1998), “Estimation of stochastic volatility models via Monte Carlo
maximum likelihood”, Journal of Econometrics, 87, 271“301.
Scott, L. O. (1987), “Option pricing when the variance changes randomly: theory, estimation and
an application”, Journal of Financial and Quantitative Analysis, 22, 419“438.
Scott, L. (1991), “Random variance option pricing”, Advances in Future and Options Research, 5,
113“135.
Shephard, N. (1993), “Fitting non-linear time series models, with applications to stochastic variance
models”, Journal of Applied Econometrics, 8, 135“152.
Shephard, N. (1994), “Local scale model: state space alternative to integrated GARCH processes”,
Journal of Econometrics, 60, 181“202.
Shephard, N. (1996), “Statistical aspects of ARCH and stochastic volatility”, in Time Series
Models with Econometric, Finance and Other Applications, D. R. Cox, D. V. Hinkley and
O. E. Barndorff-Nielsen (eds), Chapman and Hall, London, pp. 1“677.
Smith, A. A. (1993), “Estimating non linear time series models using simulated vector autoregres-
sions”, Journal of Applied Econometrics, 8, S63“S84.
So, M. K. P., K. Lam and W. K. Lee (1998), “A stochastic volatility model with Markov switch-
ing”, Journal of Business and Economic Statistics, 16, 244“253.
Sorenson, H. and D. Alspach (1971), “Recursive Bayesian estimation using Gaussian sums”, Auto-
matica, 7, 465“479.
Tanizaki, H. and R. Mariano (1994), “Prediction, ¬ltering and smoothing in non-linear and non-
normal cases using Monte Carlo integration”, Journal of Applied Econometrics, 9, 163“179.
Stochastic Volatility Models 291
Tanizaki, H. and R. Mariano (1998), “Nonlinear and nonnormal state-space modeling with Monte
Carlo stochastic simulation”, Journal of Econometrics, 83(1&2), 263“290.
Tauchen, G. (1997), “New minimum chi-square methods in empirical ¬nance”, in Advances in
Economics and Econometrics: Theory and Applications, Seventh World Congress, D. Kreps and
K. Wallis (eds), Cambridge University Press, Cambridge.
Taylor, S. J. (1986), Modelling Financial Time Series, John Wiley, Chichester.
Taylor, S. J. (1994), “Modelling stochastic volatility: a review and comparative study”, Mathemat-
ical Finance, 4, 183“204.
Tierney, L. (1994), “Markov chains for exploring posterior distributions” (with discussion), Annals
of Statistics, 22, 1701“1786.
Wright, J. H. (1999), “A new estimator of the fractionally integrated stochastic volatility model”,
Economics Letter, 63, 295“303.
Wright, J. H. (2000), “Log-periodogram estimation of long memory volatility dependencies with
conditionally heavy tailed returns”, Board of Governors of the Federal Reserve System, Inter-
national Finance Discussion Papers no. 685, http://www.bog.frb.fed.us.
9
Portfolio Analysis Using Excel

JASON LAWS


ABSTRACT
This chapter analyses the familiar Markovitz model using Excel. This topic is taught on
¬nance degrees and Masters programmes at Universities all over the world increasingly
through the use of Excel. We take time out to explain how the spreadsheet is set up and
how simple short cuts can make analysis of this type of problem quick and straightforward.
In the ¬rst section of the chapter we use a 2 variable example to show how portfolio risk
and return vary with the input weights then he goes onto show how to determine the
optimal weights, in a risk minimisation sense, using both linear expressions and also
using matrix algebra. In the second part of the chapter we extend the number of assets
to seven and illustrate using matrix algebra within Excel that Markovitz analysis of an
n-asset portfolio is as straightforward as the analysis of a two asset portfolio. We take
special care in showing how the correlation matrix can be generated most ef¬ciently and
how within the same framework the optimisation objective can be modi¬ed without fuss.

9.1 INTRODUCTION
The motivation for this chapter is to show how the Markovitz model of portfolio risk can
be modelled in Excel. The goal of this paper is not to provide an in-depth discussion of this
model but instead to show how it can be implemented within Excel. Excellent discussions
of this model can be found in a wide range of corporate ¬nance or investment text books,
including Cuthbertson and Nitzsche (2001). However, it is worthwhile to provide a brief
overview of this theory. In essence the underlying rationale is based on the twin facets of
expected risk and return, with risk being measured by the variance or standard deviation
of expected returns. The ef¬cient frontier of the portfolio is given by:

1. Those assets which offer a higher return for the same risk, or equivalently
2. Those assets which offer a lower risk for the same return.

The precise position an investor takes on the ef¬cient frontier depends on his/her utility
function based on the two motives of expected return and risk; in other words the util-
ity function is positively sloped in the return/risk domain. Initially I assume that this utility
function is very steep (i.e. the individual is highly risk averse) so his/her desired position
is a corner maximum where risk is minimised with respect to just two assets. Later the

Applied Quantitative Methods for Trading and Investment. Edited by C.L. Dunis, J. Laws and P. Na¨m
±
™ 2003 John Wiley & Sons, Ltd ISBN: 0-470-84885-5
294 Applied Quantitative Methods for Trading and Investment

analysis is further extended to incorporate a large number of assets and/or objectives other
than portfolio risk minimisation.


9.2 THE SIMPLE MARKOVITZ MODEL
In the simple Markovitz model portfolio returns, rp , in a two-asset model are given by:

rp = w1 r1 + w2 r2 (9.1)

2
and the corresponding variance of portfolio returns σp , by:

σp = w1 σ1 + w2 σ2 + 2w1 w2 σ12
2 22 22
(9.2)

where wi represents the weight allocated to asset i in the portfolio (i = {1, 2}), σi2 repre-
sents the variance of the returns on asset i (i = {1, 2}), ri represents the historical returns
of asset i (i = {1, 2}) and σ12 represents the covariance between the returns on assets 1
and 2. Using the relationship that the correlation coef¬cient (ρ12 ) between two assets can
be calculated as:
σ12
ρ12 =
σ1 σ2

then equation (9.2) can be rewritten as:

σp = w1 σ1 + w2 σ2 + 2w1 w2 ρ12 σ1 σ2
2 22 22
(9.3)

This is a much more convenient version of the portfolio variance equation and one that
students are often more at ease with as here we use the correlation coef¬cient which
takes the range ’1 to +1 whereas previously we incorporated the covariance whose size
is relative to the individual variances.
As an illustration of this model we will now apply it using two indices of stock prices,
namely, the FT World equity index for the UK (WIUTDK$) and the FT World index for
European equities which excludes the UK (WIEXUK$). The frequency of the data for
this example was chosen as weekly and covers the period January 1996 to end June 2002.
The raw data and subsequent analysis for this example in this and the next section are
included in the ¬le “Laws001.xls”. This ¬le includes several worksheets, the titles of
which are self-explanatory.
As highlighted above the Markovitz model is based around asset returns and not “raw
prices”. The ¬rst task therefore is to generate a return series where the weekly return is
given by:
Pt
(9.4)
100 loge
Pt’1

where Pt is the current price level, Pt’1 is the price level in the previous period and loge
is the natural log transformation. A screen shot of this initial transformation (contained
in the worksheet “Returns”) is given in Figure 9.1.
Portfolio Analysis Using Excel 295




Figure 9.1 Worksheet “Returns” in the ¬le “Laws001.xls”


The Excel instruction necessary to carry out this calculation based on the raw data
contained in the worksheet “Raw Data” is shown in the fourth line of the fourth column
of this screen shot. Note also that throughout this chapter I have rounded the data correct
to two decimal places.
In order to implement the Markovitz model we must ¬rst of all ¬nd the mean and
standard deviation of each of our series. This is easily achieved in Excel using the
“AVERAGE” and “STDEV” functions respectively. For completeness we have also cal-
culated the measures of kurtosis and skewness using the “KURT” and “SKEW” functions
respectively. These measures are shown in the worksheet “Summary Statistics” and are
reproduced in Figure 9.2.
Here we can see that the distributions of both returns series are “peaked” relative to
the normal distribution.1 The negative skewness also indicates that the distribution has
an asymmetric tail extending more towards negative values. More importantly, in terms
of Markovitz analysis, we can see that the European data has a higher average weekly
return over the period than the UK data but a higher standard deviation of returns. The
correlation between returns is 0.76, which provides opportunities for risk diversi¬cation.
The workbook “Portfolio Risk” implements equation (9.3)2 above using the WIUTDK$
and WIEXUK$ data. This is achieved by substituting a range of values for w1 from 0
to 1, where 0 implies that 0% of wealth is invested in the WIUTDK$ index (with 100%
being invested in the WIEXUK$ index) and 1 implies that 100% of wealth is invested

1
Since the measure of excess kurtosis (i.e. observed kurtosis minus the normal curve value of 3) is positive.
If the kurtosis measure were negative then we would say that the distribution is ¬‚at.
2
In fact it implements the square root of equation (9.3).
296 Applied Quantitative Methods for Trading and Investment




Figure 9.2 Worksheet “Summary Statistics” in the ¬le “Laws001.xls”


in the WIUTDK$ index (with 0% invested in the WIEXUK$ index). The result of this
analysis is shown in Figure 9.3.
Note in the explanation of the contents of cells C2 and D2 that use has been made of
the “$” sign. The reason for this is that when we copy the formula in cells C2 and D2
down the columns we would like to ¬x the formula on the cells which contain the returns,
standard deviations and correlations in the “Summary Statistics” worksheet,3 varying only
the portfolio weights.
It is evident from these results that as we decrease the proportion of wealth invested
in the WIUTDK$ index the portfolio risk declines (as return rises) then begins to rise
(as return rises). There is therefore some bene¬t to be gained from incorporating the
WIEXUK$ index into the portfolio.
This is best illustrated using an “XY” chart in Excel. To do this highlight cells C2
to D12 (inclusive) and then click the “Chart Wizard” button. Select the chart type “XY
(Scatter)” and any of the subtypes which include a ¬tted line (Figure 9.4).4
You may add a chart title and titles for each axis if you wish. Finally you end up with
the graph in Figure 9.5, as depicted in the worksheet “Ef¬cient Frontier”. Here we can
see that as we decrease the proportion invested in the WIUTDK$ index the portfolio risk

3
A short-cut to locking a calculation on a cell address is to use the F4 key. Pressing the F4 key once places a $
sign before both the letter and the number. Pressing twice places a $ sign just before the number and pressing
three times places a $ sign before the letter.
4
It is more than likely (depending upon your set-up) that Excel will have drawn a chart with risk on the vertical
axis and return on the horizontal axis (because our return data was in the ¬rst column Excel has assumed this
for the y-axis). It is however standard in this type of analysis to plot the data with returns on the vertical axis
and risk on the horizontal axis. To correct this simply select the “Series” tab and change the X and Y value
locations accordingly.
Portfolio Analysis Using Excel 297




Figure 9.3 Worksheet “Portfolio Risk” in the ¬le “Laws001.xls”




Figure 9.4 The Chart Wizard dialogue box
298 Applied Quantitative Methods for Trading and Investment




Figure 9.5 Worksheet “Ef¬cient Frontier” in the ¬le “Laws001.xls”


drops and returns rise (initially). If our objective is to minimise portfolio risk then there
exists some optimal investment in the WIUTDK$ index. It is possible to ¬nd this optimal
investment using calculus as follows.
We wish to minimise:

σp = w1 σ1 + w2 σ2 + 2w1 w2 ρ12 σ1 σ2 = w1 σ1 + (1 ’ w1 )2 σ2 + 2w1 (1 ’ w1 )ρ12 σ1 σ2
2 22 22 22 2


= w1 σ1 + σ2 + w1 σ2 ’ 2w1 σ2 + 2w1 ρ12 σ1 σ2 ’ 2w1 ρ12 σ1 σ2
22 2 22 2 2



by varying w1 , noting that w2 = 1 ’ w1 .
2
This can be achieved by setting the derivative of σp (portfolio risk) with respect to w1
(the proportion invested in asset 1) equal to zero, i.e.
2
δσp
= 2w1 σ1 + 2(1 ’ w1 )(’1)σ2 + 2ρ12 σ1 σ2 ’ 4w1 ρ12 σ1 σ2 = 0
2 2
δw1
’ w1 σ1 + (1 ’ w1 )(’1)σ2 + ρ12 σ1 σ2 ’ 2w1 ρ12 σ1 σ2 = 0
2 2


’ w1 (σ1 + σ2 ’ 2ρ12 σ1 σ2 ) = σ2 ’ ρ12 σ1 σ2
2 2 2


σ2 ’ ρ12 σ1 σ2
2
’ w1 = 2 and w2 = 1 ’ w1 (9.5)
σ1 + σ2 ’ 2ρ12 σ1 σ2
2


Applying this formula to our dataset we ¬nd the results of Figure 9.6, as shown in the
worksheet “Minimum Variance Using Eqn”. Here we can see that there is some optimal
mix of the WIUTDK$ (82%) and the WIEXUK$ (18%) indexes that minimises risk at
Portfolio Analysis Using Excel 299




Figure 9.6 Worksheet “Minimum Variance using eqn” in the ¬le “Laws001.xls”


2.07% per week with a return of 0.06% per week.5 Note the minimum risk is less than
that of either of the individual securities.
An alternative way to ¬nd this result which doesn™t use calculus is to use Excel™s in-built
optimisation tool “ “Solver”. To illustrate how this works we have created a duplicate
spreadsheet in the worksheet “Min Var Using Solver” and have added an additional line
of calculations to allow us to alter the weights (Figure 9.7).
As you can see we have set the initial weights to 0.5 (i.e. 50%) for the WIUTDK$
index which automatically implies 50% for the WIEXUK$ index. Any arbitrary values for
the initial weights will be all right. It is important in this respect to remember to set cell
B19 equal to 1’A19. Also the values in cells C19 and D19 must be shown according to
the formulae determining them rather than just their actual values. To begin using Solver
we choose “Tools” then “Solver” from the Excel menu. This produces the dialogue box
in Figure 9.8.
Here we set the “Target Cell” as D19 as it contains the calculation for the portfolio
standard deviation. We aim to minimise this value (hence we check the radio button
“Min”) by changing the weight in the WIUTDK$ index (which is located in cell A19).
There is no need to impose the constraint that w1 + w2 = 1 (i.e. A19 + B19 = 1) because,
as noted above, w2 is set automatically to equal 1 ’ w1 . We click the “Solve” button to
begin the optimisation and get the results of Figure 9.9, as shown in the worksheet “Min
Var Using Solver”, which as expected is identical to the outcome using the minimum
variance formula. Therefore, again we ¬nd that there are some bene¬ts, in terms of risk
reduction, by changing a portfolio from 100% UK equities to a mix of 82% UK equities
and 18% European equities.

5
Note that we can convert these weekly risk and returns to annual risk and returns by multiplying the risk
(measured by standard deviation) by the square root of 52 and the returns by 52.
300 Applied Quantitative Methods for Trading and Investment




Figure 9.7 Worksheet “Min Var using solver” in the ¬le “Laws001.xls” (pre-Solver)




Figure 9.8 The Solver dialogue box
Portfolio Analysis Using Excel 301




Figure 9.9 Worksheet “Min Var using solver” in the ¬le “Laws001.xls” (post-Solver)


9.3 THE MATRIX APPROACH TO PORTFOLIO RISK6
Equation (9.2) can be rewritten using matrix notation as:

σ11 σ12 w1
σp = w1 w2 — —
2
(9.6)
σ21 σ22 w2

Note that if you multiply the matrices out you end up with equation (9.2). The bene¬t of
using matrix notation is that we are able to generalise the formula to n-assets as follows:

σp = w
2
(9.7)
w

where w = (w1 , w2 , . . . , wN ) and is a covariance matrix with the variance terms on
the diagonal and the covariance terms on the off-diagonal. The returns equation can be
summarised by:
rp = w µ (9.8)

where µ is a vector of assets historical returns.
To extend the linear version of the Markovitz equation (9.2) to incorporate more assets
becomes particularly cumbersome as in addition to adding a term that incorporates the
variance we must also include a term that incorporates the covariance/correlation.7

6
For this and the following sections, the reader must be acquainted with the rudiments of matrix algebra.
7
For a two-asset portfolio there exists just one unique covariance (σ12 ), for a three-asset portfolio there are
three unique covariances (σ12 , σ13 , σ23 ) and so on. It follows that for an n-asset portfolio there are N (N ’ 1)/2
unique covariances.
302 Applied Quantitative Methods for Trading and Investment

In Figure 9.10 we have applied the matrix version of the portfolio risk equation when the
mix of assets is 50:50. You will see that the outcome is the same as we found previously.
Just as we have done previously with the linear version of the risk equation we can
also use Solver in this environment to ¬nd the minimum variance portfolio which we




Figure 9.10 Worksheet “Portfolio Risk using Matrices” in the ¬le “Laws002.xls” (pre-Solver)




Figure 9.11 Worksheet “Portfolio Risk using Matrices” in the ¬le “Laws002.xls” (post-Solver)
Portfolio Analysis Using Excel 303

again ¬nd is an 82:18 split between UK and European equities.8 Note that the screen
shots of Figures 9.10 and 9.11 are taken from the same worksheet “Portfolio Risk Using
Matrices”, the only difference is that Figure 9.11 has had Solver applied to ¬nd the
minimum variance portfolio.


9.4 MATRIX ALGEBRA IN EXCEL WHEN THE NUMBER
OF ASSETS INCREASES
From the previous section we can see that modelling portfolio risk within Excel using
matrices rather than the linear version of the risk equation appears far more convenient
and ¬‚exible. The ¬le “Laws002.xls” includes data on equity indices from seven European
countries, including the UK and forms the basis of all operations in this section. As before,
this ¬le contains a number of worksheets, the titles of which are self-explanatory. We are
going to utilise this dataset to show how portfolio analysis for portfolios with more than
two assets can be modelled within Excel.
The dataset comprises weekly European equity price indexes data over the period
January 1996 to end June 2002. The list of data used is:

WIFIND$ “ FTSE WORLD INDEX “ FINLAND
WIEIRE$ “ FTSE WORLD INDEX “ IRELAND
WIITAL$ “ FTSE WORLD INDEX “ ITALY
WINETH$ “ FTSE WORD INDEX “ NETHERLANDS
WISPAN$ “ FTSE WORLD INDEX “ PRICE INDEX
WISWIT$ “ FTSE WORLD INDEX “ SWITZERLAND
WIUTDK$ “ FTSE WORLD INDEX “ UNITED KINGDOM

To avoid issues of exchange rate risk all indices are expressed in US dollar terms. The
two worksheets “Raw Data” and “Returns” include the original data and the weekly log
returns respectively.
As we did previously with the two-asset portfolio example we again calculate the
summary statistics of the returns datasets. These are then used as the inputs into the
portfolio analysis examples which follow. The summary statistics shown in Figure 9.12
can be found in the “Summary Statistics” worksheet. Here we can see that there is a
positive trade-off between risk and return with the historical returns of Finland (in US

8
As an aside it should be noted that the minimum variance formula derived above can also be derived using
matrix notation. Should we do this we ¬nd that the optimal weight, w— , in asset 1 is given by:

’1
e
w— = ’1 e
e

where e is a unit vector. An application of this approach to the current dataset can be found in the worksheet
“Min Var Using Matrices” where again we ¬nd the optimal weights are 82% and 18% for WIUTDK$ and
WIEXUK$ respectively. Notice here we utilised two new functions, “MINVERSE” and “TRANSPOSE”. As
with the “MMULT” function, when we use these functions we must ¬rst of all highlight the cells where the
output is to be displayed and after completing the necessary ¬elds in the function wizard we must remember
to press “CTRL + SHIFT + ENTER” rather than simply clicking “OK”.
304 Applied Quantitative Methods for Trading and Investment




Figure 9.12 Worksheet “Summary Statistics” in the ¬le “Laws002.xls”


dollar terms) being the highest but with these returns also having the highest historical
standard deviation of returns.9
Note that it can be quite cumbersome to compute the covariance matrix when the
number of assets becomes large as particular care has to be taken when utilising the
“COVAR” function to ensure that the correct cells are referred to. When the number of
assets gets larger it is advisable to use the following result to construct the covariance
matrix.
EE
σij = (9.9)
N

where E is a vector of excess returns,10 E is its transpose and N is the number of data
observations.
It is an easy task to construct a vector of excess returns. The spreadsheet in Figure 9.13
(worksheet “Covariance”) shows how it is done for this dataset.
At the bottom of this worksheet is the covariance calculation of Figure 9.14. Note
that the calculated covariances are identical to those found above using the “COVAR”
function.11

9
The data in this example has been selected so that it exhibits a positive relationship between risk and return.
Quite often data may be selected that does not conform to this expectation as risk and return data is very
sensitive to the choice of sample and to the choice of frequency.
10
De¬ned as that period™s return minus the mean return.
11
An alternative method of obtaining the covariance matrix is to use Excel™s “OFFSET” function. This function
allows you to de¬ne a block of cells relative to some initial cell. Its arguments are (initial cells, rows and
Portfolio Analysis Using Excel 305




Figure 9.13 Worksheet “Covariance” in the ¬le “Laws002.xls” (Excess Return Calculation)


In the worksheet “Portfolio Analysis” we use the matrix approach to portfolio risk:

σp = w
2
(9.10)
w

to ¬nd the variance of an equally weighted portfolio.
To do this we construct a column vector of weights (see cells B5 to B11 in the “Portfolio
Analysis” worksheet) which represents the w vector above. We then take the transpose of
this vector12 to obtain the w vector above. Our covariance matrix ( ) is drawn from the
“Summary Statistics” worksheet. The matrix algebra involved in calculating the portfolio
risk can be broken down into the following matrix multiplications:

(1 — N ) vector — (N — N ) matrix — (N — 1) matrix

where here N = 7.
Taking the last two operations ¬rst the result we obtain is an (N — 1) matrix. This is
the result found in cells B26 to H26 of the “Portfolio Analysis” worksheet. If we then

columns) where rows and columns are the row or column shifts from the original cells. In the spreadsheet
“Laws002.xls” there are two worksheets that utilise this function. One is an introductory example entitled
“Using Offset” whilst the other is an application of this function to the return databank. Once you are familiar
with this function you will ¬nd that it is the most convenient way to ¬nd the covariance matrix of large
asset portfolios.
12
Using the “TRANSPOSE” function within Excel. This is implemented in the same way as all matrix functions.
306 Applied Quantitative Methods for Trading and Investment




Figure 9.14 Worksheet “Covariance” in the ¬le “Laws002.xls” (Covariance Calculation)



multiply the ¬rst (1 — N ) vector by the result above (an (N — 1) matrix) we obtain a
scalar as a result. That scalar is the portfolio variance and can be found in cell B28
(Figure 9.15). Using this dataset we ¬nd that the risk of an equally weighted portfolio is
2.46% per week with a return of 0.12% per week.
Fortunately within Excel it is not necessary to break this calculation down into these
individual parts. Cell 34 includes the formulae necessary to combine the two matrix
multiplications into one cell, whilst cell C37 includes the formulae necessary to eliminate
both the matrix multiplication and also the vector transposition. It is therefore possible
to ¬nd a portfolio variance in one cell given only a vector of portfolio weights and a
covariance matrix. We are now going to utilise this framework and together with Excel™s
“Solver” tool ¬nd the portfolio composition that minimises portfolio variance.
Previously when we used “Solver” in a two-asset environment the weight assigned to
the second asset adjusted automatically to changes in the weight of the ¬rst asset. Within
an N -asset environment this is not possible and instead we have to add a number of
constraints within the “Solver” dialogue box. The screen shot in Figure 9.16 includes the
required constraints. The signi¬cance of the constraints are:

• $B$5:$B$11<=1 “ we cannot invest more than 100% of our wealth in any asset.
• $B$5:$B$11>=0 “ we cannot be “short” in any asset.
• $E$5=1 “ we must invest all of our wealth in these assets.

Again we are minimising portfolio variance (cell B22) but this time rather than varying
just one single cell we are varying the contents of the vector of weights (B5:B11). The
result shown in the worksheet “Min Variance Portfolio” is given in Figure 9.17.
Portfolio Analysis Using Excel 307




Figure 9.15 Worksheet “Portfolio Analysis” in the ¬le “Laws002.xls”




Figure 9.16 Worksheet “Min Variance Portfolio” in the ¬le “Laws002.xls” (pre-Solver)
308 Applied Quantitative Methods for Trading and Investment




Figure 9.17 Worksheet “Min Variance Portfolio” in the ¬le “Laws002.xls” (post-Solver)

This analysis shows that in order to obtain the minimum variance portfolio we should
assign 65% of our wealth to UK equities, 28% to Swiss equities and 7% to Irish equities.
Moreover, based on this dataset we should not invest any wealth in Finnish, Italian, Dutch
or Spanish equities.

9.5 ALTERNATIVE OPTIMISATION TARGETS
So far we have concentrated our efforts on ¬nding the minimum variance portfolio that
¬ts our dataset. However it can easily be shown that a variety of differing objectives can
be modelled within Excel.
As an example, consider the scenario above where we found that a portfolio comprising
a 65% investment in UK equities together with a 28% investment in Swiss equities and
a 7% investment in Irish equities produced a portfolio with a weekly return of 0.07%
and a standard deviation of 1.98% per week. It may be the case that we would like to
limit our investment in UK equities to some predetermined ¬gure, say 30%. In order to
incorporate this into our analysis we simply add this constraint:

$B$11=<0.3

where $B$11 is the cell that includes the proportion of wealth invested in UK equities.
The worksheet “Risk min with 30% max in UK” shows the results of this analysis where
we ¬nd the optimal portfolio consists of 17% Irish equities, 3% Italian equities, 10%
Dutch equities, 39% Swiss equities and 30% UK equities. The return of this portfolio is
0.08% per week with a standard deviation of 2.06% per week.
We could also construct the optimisation problem such that we minimise portfolio risk
subject to investing at least 5% in each index. This requires us to change the constraint
Portfolio Analysis Using Excel 309




Figure 9.18 Worksheet “Risk min with 30% max in the UK” in the ¬le “Laws002.xls”




Figure 9.19 Worksheet “Return Max” in the ¬le “Laws002.xls” (pre-Solver)
310 Applied Quantitative Methods for Trading and Investment




Figure 9.20 Worksheet “Return Max” in the ¬le “Laws002.xls” (post-Solver)

$B$5:$B$11>=0 to $B$5:$B$11>=0.05, i.e. we must invest at least 5% in each index.
The set-up (and results) for such a problem are shown in Figure 9.18.
It is also possible to utilise “Solver” to reverse the problem to one of return maxi-
misation subject to a given level of risk tolerance. The worksheet “Return Max” takes the
current dataset and attempts to ¬nd a portfolio that maximises returns subject to a risk
tolerance level of 2.93%.13 The solver dialogue box is shown completed in Figure 9.19.
Here you can see that the focus of attention is now on cell B25 (where the portfolio
return result is calculated) and that an additional constraint:

$B$23<=2.93 “ portfolio risk is less than 2.93%

has been added. The results are shown in Figure 9.20.
Here we can see that a mix of investments of Finnish equities (30%), Irish equities
(27%), Italian equities (28%) and Swiss equities (15%) produces a portfolio offering a
return of 0.16% per week with a target maximum standard deviation of 2.93% per week.

9.6 CONCLUSION
As stated at the outset the aim of this chapter was to illustrate how Markovitz-type
portfolio analysis can be implemented within Excel. This is best achieved by looking at
the problem in its matrix format rather than its linear format and then implementing the

13
Note that an investment in Irish equities alone produces a risk of 2.93% per week with a return of 0.10%
per week.
Portfolio Analysis Using Excel 311

calculations using Excel™s matrix algebra functions. If the problem is set up correctly it
is then possible to use Excel™s “Solver” function to optimise the portfolio subject to a
variety of constraints and targets.

BIBLIOGRAPHY
Benninga, 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, John
Wiley, 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, New
York.
10
Applied Volatility and Correlation Modelling
Using Excel—

´´
FREDERICK BOURGOIN



ABSTRACT
This chapter implements a range of univariate and multivariate models within Microsoft
Excel. This is extremely useful as a large proportion of ¬nance professionals, students and
researchers that are familiar with this package. We show how to generate one-step ahead
forecasts of volatility using the JP Morgan RiskMetrics model, JP Morgan RiskMetrics
model with an optimal decay, a GARCH model with and without a variance reduction
technique and ¬nally using the GJR model to account for asymmetric reaction to news. A
comparison of forecasts is made and some useful insights to the ef¬ciency of the models
is highlighted. In the second part of this chapter, we model the time-varying correlation
using different models. As with the univariate approach this includes the JP Morgan
RiskMetrics model with and without optimal decay, a GARCH model with and without
variance reduction and ¬nally the so-called “Fast GARCH” model of which the author
has previously made signi¬cant contributions to the literature.

10.1 INTRODUCTION
The practicability of multivariate GARCH models has been the subject of several articles
in 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 the
orthogonal GARCH (Alexander and Chibumba, 1997) with yield curve or term structure
of implied volatilities (see Bourgoin (2000) for the reasons). Because all these models
require complex optimisation routines and tricks, GARCH models have always required
special software like Rats (Regression Analysis of Time Series), Eviews, Matlab, S-
Plus, Gauss, Ox or even special C++ code. In this chapter, we will try to provide an
understandable way to construct GARCH models, both univariate and multivariate models,
using Excel, the most widely used ¬nancial application in the market place. Two Excel
spreadsheets are provided on the CD-Rom, one for the univariate models and another one
for 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 Global
Investors. I retain responsibility for any errors.

Applied Quantitative Methods for Trading and Investment. Edited by C.L. Dunis, J. Laws and P. Na¨m
±
™ 2003 John Wiley & Sons, Ltd ISBN: 0-470-84885-5
314 Applied Quantitative Methods for Trading and Investment

10.2 THE BASICS
Let Pt be the price of a security at time t with t = 1, . . . , T and the continuously com-
pounded returns expressed as rt = ln(Pt /Pt’1 ) with µt = E(rt |Ft’1 ) where F (t ’ 1) is
the information set available at time t ’ 1.
The simplest linear time series model is the ARMA(p, q) process where r(t) follows
an autoregressive process, i.e., the past values of r(t) in¬‚uence future values of r(t) and
a moving average component which is formed by the error term, µt’1 . Mathematically
we have the following:
p q
rt = θ0 + φi rt’i + θi µt’i (10.1)
j =1
i=1

In the following pages we will not focus our attention on modelling the ¬rst moment
of the distribution, so we will consider that E(rt ) = µt = 0 and that either it has been
done before or that it is statistically insigni¬cant. The main purpose of this chapter is
to consider the following: σt2 = Var(rt |Ft’1 ) and how to account for the time-varying
volatility observed in the ¬nancial markets. So the model becomes:

rt = σt µt where σt2 = f (µt’1 , . . . , µt’i ; σt’1 , . . . , σt’j )
2 2 2 2
i, j > 0,
with
µt ’ N (0, 1)1 (10.2)

We try in this chapter to use only the Excel Solver add-in2 and no additional optimisation
add-in (several packages are available and a lot more ¬‚exible and powerful than the Excel
Solver3 ), 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 the
models 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 , then
L becomes:
T T
rt2
1
L(rn+1 , rn+2 , . . . , rT , ) = f (rt |Ft’1 ) = exp ’ 2 (10.4)
σt
2πσt2
t=n+1 t=n+1

For the purpose of the optimisation, we often ¬nd it more convenient to work with the
natural log of this function, ln(L), because of the following property: ln(ab) = ln(a)
+ (b).

1
Obviously other probability distributions can be considered like the Student(t), GED(v) or the skewed
Student(k, t) distribution but this is beyond the scope of this chapter.
In order to set up the Solver add-in in Excel, start Excel ’ Tools ’ Add-ins ’ Solver Add-in should be
2

checked and then press <OK>, if it doesn™t appear in the list, use your MS Of¬ce CD-Rom to install the
required component.
3
Palisade Corporation sells the @RiskOptimizer, a more powerful version of the Excel Solver with genetic
algorithm and Monte Carlo simulation capabilities, see http://www.palisade.com.
Applied Volatility and Correlation Modelling 315

The likelihood function becomes:

T
1 rt2
1 1
ln(L, ) = ’ ln(2π ) ’ ln(σt ) ’
2
(10.5)
2 σt2
2 2
t=n+1


where we can drop ln(2π ) as well.


10.3 UNIVARIATE MODELS
The Excel spreadsheets “Bourgoin001.xls” and “Bourgoin002.xls” both have a common
set-up:

• Column A and B should have the dates and the security used for analysis, here, the
S&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 opti-
misation, 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 the
optimisation.


10.3.1 The RiskMetrics model

In the RiskMetrics model (J.P. Morgan, 1995), the volatility process speci¬cation is
the following:
σt2 = (1 ’ »)µt’1 + »σt’1
2 2
(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 it
is constant:

E(σt+1 |Ft ) = E((1 ’ »)µt2 + »σt2 ) = (1 ’ »)E(µt2 ) + »E(σt2 )
2


= (1 ’ »)µt2 + »σt|t’1
2


E(σt+2 |Ft ) = E((1 ’ »)µt+1 + »σt+1|t ) = (1 ’ »)E(µt+1|t ) + »E(σt+1|t )
2 2 2 2 2
(10.7)
= (1 ’ »)σt+1|t + »σt+1|t = σt+1|t
2 2 2


E(σt+h |Ft ) = σt+1|t
2 2



4
Only statistically applicable to the random walk and the RiskMetrics model, it is industry practice to use it
regardless 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 in
Figure 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 model
used (here RiskMetrics), one should always default the ¬rst observation of the volatility
to the unconditional standard deviation of the time series (σ1 = σ ). This is particularly
crucial when complex models are used; bad starting values can lead to a non-convergence
of 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.5

10.3.2 The optimal decay factor model
In the previous spreadsheet (RiskMetrics), we didn™t use any optimisation as the user
gives », 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, ». The
worksheet “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 spread-
sheet.
• 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.6

5
In practice, we can observe less heteroskedasticity in monthly data than in daily data, that is the reason why
the decay has been set higher (standard set by J.P. Morgan (1995)).
6
These 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 a
decay factor of 0.94, 50% of the weight is in the previous 11 observations, if the decay
increases to 0.98, the half-life increases to 34 observations (see RiskMetrics Technical
Document, p. 93). When the decay equals 1, we mean revert to the unconditional
standard deviation calculation if we initialised the process with σ0 = σ .
318 Applied Quantitative Methods for Trading and Investment
45.00%

FAST moving volatility estimation
40.00%
with a fast decay (0.94)
SLOW moving volatility estimation
35.00% with a fast decay (0.97)

30.00%

25.00%

20.00%

15.00%

10.00% Long-term volatility calculated
using the Excel function Stdev
5.00%

0.00%
16-Apr-98




16-Oct-98




16-Apr-99




16-Oct-99




16-Apr-00




16-Oct-00




16-Apr-01
16-Mar-99




16-Mar-00




16-Mar-01
16-Jun-98




16-Jan-99




16-Jun-99




16-Jan-00




16-Jun-00




16-Jan-01




16-Jun-01
16-Feb-99




16-Feb-00




16-Feb-01

<<

. 14
( 19)



>>