ńňđ. 14 |

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

â—

jË

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 |