<< стр. 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
в€—
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
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)СОДЕРЖАНИЕ >>