10 Calculating the Variance-Covariance Matrix

10.1 Overview

In order to calculate efficient portfolios, we must be able to compute the

variance-covariance matrix from return data for stocks. In this chapter we
discuss this computation, showing how to do the calculations in Excel. The
most obvious calculation is the sample variance-covariance matrix: This is
the matrix computed directly from the historic returns. We illustrate several
methods for calculating the sample variance-covariance matrix, including a
direct calculation in the spreadsheet using the excess return matrix and an
implementation of this method with VBA.
While the sample variance-covariance matrix may appear to be an obvious
choice, a large literature recognizes that it may not be the best estimate
of variances and covariances. Disappointment with the sample variance-
covariance matrix stems both from its often unrealistic parameters and from
its inability to predict. These issues are discussed briefly in sections 10.5 and
10.6. As an alternative to the sample matrix, sections 10.7–10.10 discuss
so-called “shrinkage” methods for improving the estimate of the variance-
covariance matrix.1
Before starting this chapter, you may want to peruse Chapter 34 which
discusses array functions. These are Excel functions whose arguments are
vectors and matrices; their implementation is slightly different from standard
Excel functions. This chapter makes heavy use of the array functions Trans-
pose( ) and MMult( ) as well as some other “home-grown” array functions.

10.2 Computing the Sample Variance-Covariance Matrix

Suppose we have return data for N assets over M periods. Writing the return
of asset i in period t as rit, we write the mean return of asset i as:

1 M
ri = ∑ rit , i = 1, … , N
M t =1

Then the covariance of the return of asset i and asset j is calculated as:

1 M
σ ij = Cov(i, j ) = ∑ (rit − ri ) ⋅ (rjt − rj ) , i, j = 1, … , N
M − 1 t =1

1. We return to the issue of prediction in Chapter 13, which discusses the Black-Litterman model
of portfolio optimization.
 252 Chapter 10

The matrix of these covariances (which includes, of course, the variances when
i = j) is the sample variance-covariance matrix. Our problem is to calculate
these covariances efficiently. Define the excess return matrix to be:

⎡ r11 − r1  rN 1 − rN ⎤
⎢r −r  r −r ⎥
A = matrix of excess returns = ⎢ ⎥
12 1 N2 N

⎢   ⎥
⎢ ⎥
⎣r1M − r1  rNM − rN ⎦
Columns of matrix A subtract the mean asset return from the individual asset
returns. The transpose of this matrix is:

⎡ r11 − r1 r12 − r1   r1M − r1 ⎤

A =⎢ 
T
  ⎥
⎢ ⎥
⎢⎣rN 1 − rN rN 2 − rN   rNM − rN ⎥⎦

Multiplying AT times A and dividing through by M − 1 gives the sample

variance-covariance matrix:

AT ⋅ A
S = [σ ij ] =
M −1
To consider the computational aspects, we use M = 60 months of return data
for N = 10 stocks. The spreadsheet below shows the price data (adjusted for
dividends) and the computed returns:

A B C D E F G H I J K L
1 FIVE YEARS OF PRICES FOR 10 STOCKS AND THE SP500
Arcelor- General Bank of
2 McDonalds US Steel MiƩal MicrosoŌ Apple Kellogg Electric America Pfizer Exxon S&P500
3 Date MCD X MT MSFT AAPL K GE BAC PFE XOM ^GSPC
4 1-Feb-07 37.57 84.74 44.84 25.53 84.61 43.45 28.97 44.68 19.57 64.20 1406.82
5 1-Mar-07 38.74 94.76 46.63 25.26 92.91 44.83 29.34 44.85 19.80 67.58 1420.86
6 2-Apr-07 41.52 97.03 47.10 27.14 99.80 46.12 30.59 44.74 20.74 71.10 1482.37
57 1-Jul-11 85.26 39.80 30.54 27.02 390.48 54.85 17.57 9.68 18.65 78.37 1292.28
58 1-Aug-11 89.74 30.01 21.74 26.40 384.83 53.84 16.00 8.15 18.60 73.18 1218.89
59 1-Sep-11 87.16 21.94 15.74 24.70 381.32 52.72 15.07 6.11 17.32 71.81 1131.42
60 3-Oct-11 92.16 25.27 20.51 26.43 404.78 53.73 16.55 6.82 18.87 77.20 1253.30
61 1-Nov-11 95.52 27.26 18.89 25.58 382.20 49.16 15.76 5.44 19.86 80.00 1246.96
62 1-Dec-11 100.33 26.42 18.19 25.96 405.00 50.57 17.91 5.56 21.42 84.30 1257.60
63 3-Jan-12 99.05 30.14 20.52 29.53 456.48 49.52 18.71 7.13 21.18 83.28 1312.41
64 1-Feb-12 99.99 31.01 23.30 30.77 493.17 50.21 19.13 8.18 21.14 84.88 1351.95
 253 Calculating the Variance-Covariance Matrix

Using the Excel function Ln(Pt/Pt−1), we compute the monthly returns:

A B C D E F G H I J K L
1 FIVE YEARS OF MONTHLY RETURNS FOR 10 STOCKS AND THE SP500
2 Date MCD X MT MSFT AAPL K GE BAC PFE XOM ^GSPC
3 1-Mar-07 3.07% 11.18% 3.91% -1.06% 9.36% 3.13% 1.27% 0.38% 1.17% 5.13% 0.99%
4 2-Apr-07 6.93% 2.37% 1.00% 7.18% 7.15% 2.84% 4.17% -0.25% 4.64% 5.08% 4.24%
5 1-May-07 4.59% 11.02% 12.16% 2.80% 19.42% 2.55% 1.91% 0.73% 4.89% 5.09% 3.20%
6 1-Jun-07 0.41% -3.98% 3.93% -4.06% 0.70% -4.14% 2.60% -3.66% -7.23% 0.85% -1.80%
7 2-Jul-07 -5.85% -10.11% -2.23% -1.66% 7.66% 0.04% 1.24% -3.06% -8.39% 1.49% -3.25%
8 1-Aug-07 2.83% -3.72% 8.65% -0.53% 4.97% 6.42% 0.28% 6.66% 6.70% 1.09% 1.28%
54 1-Jun-11 3.35% -0.15% 3.86% 3.86% -3.56% -2.97% -3.24% -6.90% -4.07% -2.53% -1.84%
55 1-Jul-11 2.53% -14.09% -10.97% 5.24% 15.12% 0.82% -5.16% -12.05% -6.79% -1.97% -2.17%
56 1-Aug-11 5.12% -28.23% -33.99% -2.32% -1.46% -1.86% -9.36% -17.20% -0.27% -6.85% -5.85%
57 1-Sep-11 -2.92% -31.32% -32.29% -6.66% -0.92% -2.10% -5.99% -28.81% -7.13% -1.89% -7.45%
58 3-Oct-11 5.58% 14.13% 26.47% 6.77% 5.97% 1.90% 9.37% 10.99% 8.57% 7.24% 10.23%
59 1-Nov-11 3.58% 7.58% -8.23% -3.27% -5.74% -8.89% -4.89% -22.61% 5.11% 3.56% -0.51%
60 1-Dec-11 4.91% -3.13% -3.78% 1.47% 5.79% 2.83% 12.79% 2.18% 7.56% 5.24% 0.85%
61 3-Jan-12 -1.28% 13.17% 12.05% 12.88% 11.97% -2.10% 4.37% 24.87% -1.13% -1.22% 4.27%
62 1-Feb-12 0.94% 2.85% 12.71% 4.11% 7.73% 1.38% 2.22% 13.74% -0.19% 1.90% 2.97%
63
64 Mean 1.63% -1.68% -1.09% 0.31% 2.94% 0.24% -0.69% -2.83% 0.13% 0.47% -0.07%

Below we compute the excess returns and the variance-covariance matrix:

A B C D E F G H I J K L
66 Variance-Covariance Matrix
67 MCD X MT MSFT AAPL K GE BAC PFE XOM
68 MCD 0.0020 0.0037 0.0028 0.0015 0.0017 0.0007 0.0020 0.0031 0.0015 0.0011
69 X 0.0037 0.0380 0.0284 0.0076 0.0111 0.0031 0.0127 0.0176 0.0043 0.0043
70 MT 0.0028 0.0284 0.0267 0.0065 0.0097 0.0031 0.0102 0.0133 0.0038 0.0039
71 MSFT 0.0015 0.0076 0.0065 0.0063 0.0049 0.0010 0.0046 0.0079 0.0018 0.0014
72 AAPL 0.0017 0.0111 0.0097 0.0049 0.0126 0.0016 0.0049 0.0049 0.0007 0.0020
73 K 0.0007 0.0031 0.0031 0.0010 0.0016 0.0026 0.0028 0.0046 0.0011 0.0003
74 GE 0.0020 0.0127 0.0102 0.0046 0.0049 0.0028 0.0122 0.0163 0.0041 0.0022
75 BAC 0.0031 0.0176 0.0133 0.0079 0.0049 0.0046 0.0163 0.0393 0.0080 0.0017
76 PFE 0.0015 0.0043 0.0038 0.0018 0.0007 0.0011 0.0041 0.0080 0.0041 0.0011
77 XOM 0.0011 0.0043 0.0039 0.0014 0.0020 0.0003 0.0022 0.0017 0.0011 0.0026
78 <-- {=MMULT(TRANSPOSE(B83:K142),B83:K142)/59}
79
80
81 Excess returns: rij-ri
82 MCD X MT MSFT AAPL K GE BAC PFE XOM
83 1-Mar-07 0.0144 0.1285 0.0501 -0.0137 0.0642 0.0289 0.0196 0.0321 0.0104 0.0467 <-- =K3-K$64
84 2-Apr-07 0.0530 0.0404 0.0209 0.0687 0.0422 0.0260 0.0486 0.0258 0.0451 0.0461 <-- =K4-K$64
85 1-May-07 0.0296 0.1269 0.1325 0.0249 0.1648 0.0231 0.0260 0.0356 0.0476 0.0462 <-- =K5-K$64
86 1-Jun-07 -0.0122 -0.0231 0.0502 -0.0437 -0.0224 -0.0438 0.0329 -0.0083 -0.0736 0.0039 <-- =K6-K$64
87 2-Jul-07 -0.0748 -0.0843 -0.0114 -0.0197 0.0473 -0.0020 0.0193 -0.0023 -0.0852 0.0102
88 1-Aug-07 0.0119 -0.0205 0.0974 -0.0084 0.0204 0.0618 0.0097 0.0949 0.0657 0.0063
89 4-Sep-07 0.0845 0.1313 0.1795 0.0217 0.0734 0.0168 0.0768 0.0327 -0.0180 0.0720
90 1-Oct-07 0.0762 0.0351 0.0310 0.2197 0.1839 -0.0615 0.0009 -0.0121 0.0058 -0.0108
91 1-Nov-07 -0.0120 -0.0806 -0.0644 -0.0911 -0.0709 0.0268 -0.0652 -0.0171 -0.0238 -0.0321
92 3-Dec-07 -0.0088 0.2299 0.0576 0.0547 0.0541 -0.0327 -0.0170 -0.0690 -0.0458 0.0449
93 2-Jan-08 -0.1111 -0.1537 -0.1419 -0.0913 -0.4101 -0.0948 -0.0403 0.0960 0.0260 -0.0937
94 1-Feb-08 0.0003 0.0810 0.1464 -0.1801 -0.1088 0.0629 -0.0489 -0.0770 -0.0345 0.0148
95 3-Mar-08 0.0140 0.1737 0.0892 0.0392 0.1085 0.0332 0.1172 -0.0022 -0.0637 -0.0331
96 1-Apr-08 0.0497 0.2102 0.0963 0.0019 0.1631 -0.0292 -0.1169 0.0186 -0.0416 0.0910
97 1-May-08 -0.0207 0.1332 0.1197 -0.0066 0.0523 0.0163 -0.0553 -0.0705 -0.0233 -0.0476
98 2-Jun-08 -0.0635 0.0843 0.0120 -0.0319 -0.1492 -0.0786 -0.1228 -0.3064 -0.1040 -0.0118
254 Chapter 10

A VBA Function to Compute the Variance-Covariance Matrix

To automate this procedure, we write a VBA function that computes the

variance-covariance matrix using the Excel function Covariance.S. When
Excel functions with periods, such as Covariance.S, are used in VBA, the
period becomes an underscore: Covariance_S:

‘My thanks to Amir Kirsh

‘Revised 2012 by Benjamin Czaczkes and _
Simon Benninga
Function VarCovar(rng As Range) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = rng.Columns.Count
numrows = rng.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = _
Application.WorksheetFunction.
Covariance_S(rng.Columns(i), _
rng.Columns(j))
Next j
Next i
VarCovar = matrix
End Function
 255 Calculating the Variance-Covariance Matrix

The VBA computes Covariance_S for every entry of the variance-

covariance matrix.2 Here’s the result:

A B C D E F G H I J K L
1 PORTFOLIO ANALYSIS FOR MONTHLY DATA
2 Variance-Covariance Matrix
3 MCD X MT MSFT AAPL K GE BAC PFE XOM
4 MCD 0.0020 0.0037 0.0028 0.0015 0.0017 0.0007 0.0020 0.0031 0.0015 0.0011 <-- {=varcovar('Page 253'!B3:K62)}
5 X 0.0037 0.0380 0.0284 0.0076 0.0111 0.0031 0.0127 0.0176 0.0043 0.0043
6 MT 0.0028 0.0284 0.0267 0.0065 0.0097 0.0031 0.0102 0.0133 0.0038 0.0039
7 MSFT 0.0015 0.0076 0.0065 0.0063 0.0049 0.0010 0.0046 0.0079 0.0018 0.0014
8 AAPL 0.0017 0.0111 0.0097 0.0049 0.0126 0.0016 0.0049 0.0049 0.0007 0.0020
9 K 0.0007 0.0031 0.0031 0.0010 0.0016 0.0026 0.0028 0.0046 0.0011 0.0003
10 GE 0.0020 0.0127 0.0102 0.0046 0.0049 0.0028 0.0122 0.0163 0.0041 0.0022
11 BAC 0.0031 0.0176 0.0133 0.0079 0.0049 0.0046 0.0163 0.0393 0.0080 0.0017
12 PFE 0.0015 0.0043 0.0038 0.0018 0.0007 0.0011 0.0041 0.0080 0.0041 0.0011
13 XOM 0.0011 0.0043 0.0039 0.0014 0.0020 0.0003 0.0022 0.0017 0.0011 0.0026

Should We Divide by M – 1 or by M

In the above calculations, we use the sample covariance (in Excel Covariance.S
and in VBA Covariance_S) to divide by M – 1 instead of M in order to get
the unbiased estimate of the variances and covariances. We don’t think this
matters very much, but for reference to a higher authority, we suggest our
discussion of M versus M – 1 in section 8.2.
Starting with Excel 2010, Microsoft has cleared up considerable confusion
that once existed in Excel about whether to divide by M or M – 1. The new
versions of Excel have standardized the nomenclature and computations for
these functions:

Excel 2010 Other (older) Comments When used in VBA

and later versions of
this function
(still work)
Covariance.S Sample covariance, Application.
divides by M–1 WorksheetFunction.
Covariance_S
Covariance.P Covar Population covariance, Application.
divides by M WorksheetFunction.
Covariance_P

2. Since the covariance matrix is symmetric, we’ve actually done too many computations. But
given the speed of our computers, who cares?
256 Chapter 10

Excel 2010 Other (older) Comments When used in VBA

and later versions of
this function
(still work)

Var.S VarS Sample variance Application.

WorksheetFunction.Var_S
Application.
WorksheetFunction.VarS
Var.P VarP Population variance Application.
WorksheetFunction.
Var_P
Application.
WorksheetFunction.VarP

Confused? Don’t worry! As the discussion in Chapter 8 indicates, perhaps

it doesn’t matter very much.

10.3 The Correlation Matrix

Using the Excel function Correl we can compute the correlation matrix of the
returns:

Function CorrMatrix(rng As Range) As Variant

Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = rng.Columns.Count
numrows = rng.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
 257 Calculating the Variance-Covariance Matrix

For i = 1 To numcols
For j = 1 To numcols
matrix(i - 1, j - 1) = _
Application.WorksheetFunction.Correl(rng. _
Columns(i), rng.Columns(j))
Next j
Next i
CorrMatrix = matrix
End Function

A B C D E F G H I J K
1 CORRELATION MATRIX
Arcelor- General Bank of
2 McDonalds US Steel MiƩal MicrosoŌ Apple Kellogg Electric America Pfizer Exxon
3 MCD X MT MSFT AAPL K GE BAC PFE XOM
4 MCD 1.0000 0.4199 0.3859 0.4238 0.3379 0.2920 0.4064 0.3506 0.5411 0.4741
5 X 0.4199 1.0000 0.8898 0.4898 0.5062 0.3078 0.5904 0.4556 0.3491 0.4361
6 MT 0.3859 0.8898 1.0000 0.5044 0.5277 0.3692 0.5659 0.4103 0.3602 0.4620
7 MSFT 0.4238 0.4898 0.5044 1.0000 0.5497 0.2416 0.5312 0.5050 0.3542 0.3581
8 AAPL 0.3379 0.5062 0.5277 0.5497 1.0000 0.2827 0.3964 0.2205 0.0945 0.3425
9 K 0.2920 0.3078 0.3692 0.2416 0.2827 1.0000 0.4846 0.4559 0.3487 0.1234
10 GE 0.4064 0.5904 0.5659 0.5312 0.3964 0.4846 1.0000 0.7461 0.5842 0.3926
11 BAC 0.3506 0.4556 0.4103 0.5050 0.2205 0.4559 0.7461 1.0000 0.6328 0.1723
12 PFE 0.5411 0.3491 0.3602 0.3542 0.0945 0.3487 0.5842 0.6328 1.0000 0.3435
13 XOM 0.4741 0.4361 0.4620 0.3581 0.3425 0.1234 0.3926 0.1723 0.3435 1.0000
14 <-- {=CorrMatrix('Page 253'!B3:K62)}

Here’s another version of the correlation matrix, this time only the upper
half:

’Triangular correlation matrix

Function CorrMatrixTriangular(rng As Range) _
As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = rng.Columns.Count
numrows = rng.Rows.Count
Dim matrix() As Variant
ReDim matrix(numcols - 1, numcols - 1)
 258 Chapter 10

For i = 1 To numcols
For j = 1 To numcols
If i <= j Then
matrix(i - 1, j - 1) = _
Application.WorksheetFunction.Correl(rng. _
Columns(i), rng.Columns(j))
Else
matrix(i - 1, j - 1) = “”
End If
Next j
Next i
CorrMatrixTriangular = matrix
End Function

A B C D E F G H I J K
Arcelor- General Bank of
16 McDonalds US Steel MiƩal MicrosoŌ Apple Kellogg Electric America Pfizer Exxon
17 MCD X MT MSFT AAPL K GE BAC PFE XOM
18 MCD 1.0000 0.4199 0.3859 0.4238 0.3379 0.2920 0.4064 0.3506 0.5411 0.4741
19 X 1.0000 0.8898 0.4898 0.5062 0.3078 0.5904 0.4556 0.3491 0.4361
20 MT 1.0000 0.5044 0.5277 0.3692 0.5659 0.4103 0.3602 0.4620
21 MSFT 1.0000 0.5497 0.2416 0.5312 0.5050 0.3542 0.3581
22 AAPL 1.0000 0.2827 0.3964 0.2205 0.0945 0.3425
23 K 1.0000 0.4846 0.4559 0.3487 0.1234
24 GE 1.0000 0.7461 0.5842 0.3926
25 BAC 1.0000 0.6328 0.1723
26 PFE 1.0000 0.3435
27 XOM 1.0000
28 <-- {=CorrMatrixTriangular('Page 253'!B3:K62)}

Here are some statistics for the correlations. The average correlation for our
sample (0.4226) is a bit high (usually a sample of stocks will give average
correlation of 0.2–0.3). The largest correlations (ρArcelor,US Steel = 0.8898,
ρGE,BankAmerica = 0.7461) look quite high, though perhaps there are economic
explanations.3

3. Arcelor and U.S. Steel are, of course, both steel companies. GE has one of the largest financing
operations in the world; perhaps this explains the high correlation between the returns of Bank
of America and GE? Or perhaps it’s just a fluke of the data?
 259 Calculating the Variance-Covariance Matrix

A B C D E F G H I J
30 Some correlaƟon staƟsƟcs
31 Average 0.4226 <-- =AVERAGEIF(B18:K27,"<1")
32 Largest 0.8898 <-- =LARGE(B18:K27,11) Smallest 0.0945 <-- =SMALL($B$18:$K$27,1)
33 Next largest 0.7461 <-- =LARGE(B18:K27,12) Next small 0.1234 <-- =SMALL($B$18:$K$27,2)
34 etc. 0.6328 <-- =LARGE(B18:K27,13) etc. 0.1723 <-- =SMALL($B$18:$K$27,3)
35 etc. 0.5904 <-- =LARGE(B18:K27,14) etc. 0.2416 <-- =SMALL($B$18:$K$27,5)
36 etc. 0.5842 <-- =LARGE(B18:K27,15) etc. 0.2416 <-- =SMALL($B$18:$K$27,5)

10.4 Computing the Global Minimum Variance Portfolio (GMVP)

The two most prominent uses of the variance-covariance matrix are to find the
global minimum variance portfolio (GMVP) and to find efficient portfolios.
Both uses illustrate the problematics of working with sample data and provide
us with the introduction needed for sections 10.7–10.10, which discuss alterna-
tives to the sample variance-covariance matrix. In this section we discuss
the GMPV.
Suppose there are N assets having a variance-covariance matrix S. The
GMVP is the portfolio x = {x1, x2, … , xN} which has the lowest variance from
among all feasible portfolios. The minimum variance portfolio is defined by
1row ⋅ S −1
xGMVP = { xGMVP ,1, xGMVP ,2, … , xGMVP ,N } = ,
1row ⋅ S −1 ⋅ 1Trow
1row ⋅ S −1
here 1row = {1, 1, … , 1} =
wh
 Sum ( numerator )
↑
N-dimensional
row vector of 1s

⎧ xGMVP ,1 ⎫ ⎧1⎫
⎪x ⎪ S −1 1column ⎪1⎪
⎪ GMVP ,2 ⎪ ⎪ ⎪
xGMVP =⎨ ⎬= T , where 1column = ⎨ ⎬
⎪ 
−1
⎪ 1column ⋅ S ⋅ 1column ⎪ ⎪
⎪⎩ xGMVP ,N ⎪⎭ ⎪⎩1⎭⎪

↑
N-dimensional
column vector of 1s

S −1 1column
=
Sum ( numerator )
260 Chapter 10

This formula is due to Merton.4

The particular fascination of the minimum variance portfolio is that it is the
only portfolio on the efficient frontier whose computation does not require the
asset expected returns. The mean μGMVP and the variance σ GMVP
2
of the minimum
variance portfolio are given by:
μGMVP = xGMVP ⋅ E (r ) , σ GMVP
2
= xGMVP ⋅ S ⋅ xGMVP
T

Here’s an implementation of these formulas for our particular example. We

use two VBA functions for the unit column and row vectors:

‘I thank Priyush Singh and Ayal Itzkovitz

Function UnitrowVector(numcols As Integer) _
As Variant
Dim i As Integer
Dim vector() As Integer
ReDim vector(0, numcols - 1)
For i = 1 To numcols
vector(0, i - 1) = 1
Next i
UnitrowVector = vector
End Function

Function UnitColVector(numrows As Integer) _

As Variant
Dim i As Integer
Dim vector() As Integer
ReDim vector(numrows - 1, 0)
For i = 1 To numrows
vector(i - 1, 0) = 1
Next i
UnitColVector = vector
End Function

4. Robert C. Merton, “An Analytical Derivation of the Efficient Portfolio Frontier,” Journal of
Financial and Quantitative Analysis (1973).
 261 Calculating the Variance-Covariance Matrix

Applying this to our 10-asset example:

A B C D E F G H I J K
1 COMPUTING THE GLOBAL MINIMUM VARIANCE PORTFOLIO
2 Variance-Covariance Matrix
3 MCD X MT MSFT AAPL K GE BAC PFE XOM
4 MCD 0.0020 0.0037 0.0028 0.0015 0.0017 0.0007 0.0020 0.0031 0.0015 0.0011
5 X 0.0037 0.0380 0.0284 0.0076 0.0111 0.0031 0.0127 0.0176 0.0043 0.0043
6 MT 0.0028 0.0284 0.0267 0.0065 0.0097 0.0031 0.0102 0.0133 0.0038 0.0039
7 MSFT 0.0015 0.0076 0.0065 0.0063 0.0049 0.0010 0.0046 0.0079 0.0018 0.0014
8 AAPL 0.0017 0.0111 0.0097 0.0049 0.0126 0.0016 0.0049 0.0049 0.0007 0.0020
9 K 0.0007 0.0031 0.0031 0.0010 0.0016 0.0026 0.0028 0.0046 0.0011 0.0003
10 GE 0.0020 0.0127 0.0102 0.0046 0.0049 0.0028 0.0122 0.0163 0.0041 0.0022
11 BAC 0.0031 0.0176 0.0133 0.0079 0.0049 0.0046 0.0163 0.0393 0.0080 0.0017
12 PFE 0.0015 0.0043 0.0038 0.0018 0.0007 0.0011 0.0041 0.0080 0.0041 0.0011
13 XOM 0.0011 0.0043 0.0039 0.0014 0.0020 0.0003 0.0022 0.0017 0.0011 0.0026
14
15 GMVP as row 0.0326 0.2117 0.1754 0.0705 0.0873 0.0340 0.1166 0.1891 0.0493 0.0335
16 <-- {=MMULT(unitrowvector(10),B4:K13)/SUM(MMULT(unitrowvector(10),B4:K13))}
17
18 0.0326
GMVP as
19 0.2117 <-- {=MMULT(B4:K13,unitcolvector(10))/SUM(MMULT(B4:K13,unitcolvector(10)))}
column
20 0.1754
21 0.0705
22 0.0873
23 0.0340
24 0.1166
25 0.1891
26 0.0493
27 0.0335
28
29 GMVP staƟsƟcs
30 Mean -0.80% T(B15:K15,'Page 253'!B64:K64)
31 Variance 0.0130 <-- {=MMULT(MMULT(B15:K15,B4:K13),B18:B27)}
32 Sigma 11.40% <-- =SQRT(B31)

10.5 Four Alternatives to the Sample Variance-Covariance Matrix

In succeeding sections we illustrate four alternatives to the sample variance-

covariance matrix:

• The single-index model assumes that the only sources of variance risk are
the market variance and the betas of the assets.
• The constant correlation model assumes the correlation between all asset
returns is constant, so that σij = ρ σi σj.
262 Chapter 10

• Shrinkage methods assume that the variance-covariance matrix is a convex

combination of the sample variance-covariance and a matrix with variances
on the diagonal and zeros elsewhere.
• Option methods use options to derive the standard deviations of returns for
the assets. We combine this in section 10.9 with the constant correlation
method to compute a variance-covariance matrix.

The first three models arise out of a distrust of return data for generating
the covariances of the data in the future. The fourth method—using option
data—goes further and assumes that even the sample variances are an inac-
curate prediction of future variance.

10.6 Alternatives to the Sample Variance-Covariance: The Single-Index

Model (SIM)

The single-index model (SIM) began as an attempt to simplify some of the

computational complexities of calculating the variance-covariance matrix.5
The basic assumption of the SIM is that the returns of each asset can be linearly
regressed on a market index x:

ri = α i + βi rx + εi

where the correlation between εi and εj is zero. Given this assumption, it is
easy to establish the following two facts:

• E (ri ) = α i + βi E (rx )
⎧βi β j σ x2 when i ≠ j
• σ ij = ⎨
⎩ σi when i = j
2

5. W. M. Sharpe, “A Simplified Model for Portfolio Analysis,” Management Science (1963).

263 Calculating the Variance-Covariance Matrix

Essentially the SIM involves changes in the estimates of the covariances,

but not the sample variance. We can automate the procedure for computing
the SIM by writing some VBA code:

Function sim(assetdata As Range, marketdata As Range) _

As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = assetdata.Columns.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)

For i = 1 To numcols
For j = 1 To numcols
If i = j Then
matrix(i - 1, j - 1) = Application. _
WorksheetFunction.Var_S(assetdata.Columns(i))
Else
matrix(i - 1, j - 1) = _
Application.WorksheetFunction.Slope(assetdata. _
Columns(i), marketdata) * _
Application.WorksheetFunction.Slope(assetdata. _
Columns(j), marketdata) * _
Application.WorksheetFunction.Var_S(marketdata)
End If
Next j
Next i
sim = matrix
End Function
 264 Chapter 10

The two arguments of this function are the asset returns and the market
returns. Applying this code in our example:

A B C D E F G H I J K L
1 COMPUTING THE SINGLE-INDEX VARIANCE-COVARIANCE MATRIX
2 MCD X MT MSFT AAPL K GE BAC PFE XOM
3 MCD 0.0020 0.0036 0.0031 0.0013 0.0016 0.0006 0.0021 0.0032 0.0009 0.0006
4 X 0.0036 0.0380 0.0198 0.0085 0.0105 0.0038 0.0137 0.0204 0.0059 0.0042
5 MT 0.0031 0.0198 0.0267 0.0073 0.0090 0.0033 0.0117 0.0175 0.0051 0.0036
6 MSFT 0.0013 0.0085 0.0073 0.0063 0.0038 0.0014 0.0050 0.0075 0.0022 0.0015
7 AAPL 0.0016 0.0105 0.0090 0.0038 0.0126 0.0017 0.0062 0.0092 0.0027 0.0019
8 K 0.0006 0.0038 0.0033 0.0014 0.0017 0.0026 0.0022 0.0034 0.0010 0.0007
9 GE 0.0021 0.0137 0.0117 0.0050 0.0062 0.0022 0.0122 0.0121 0.0035 0.0025
10 BAC 0.0032 0.0204 0.0175 0.0075 0.0092 0.0034 0.0121 0.0393 0.0052 0.0037
11 PFE 0.0009 0.0059 0.0051 0.0022 0.0027 0.0010 0.0035 0.0052 0.0041 0.0011
12 XOM 0.0006 0.0042 0.0036 0.0015 0.0019 0.0007 0.0025 0.0037 0.0011 0.0026
13 <-- {=sim(B16:K75,L16:L75)}
14
15 Date MCD X MT MSFT AAPL K GE BAC PFE XOM ^GSPC
16 1-Mar-07 3.07% 11.18% 3.91% -1.06% 9.36% 3.13% 1.27% 0.38% 1.17% 5.13% 0.99%
17 2-Apr-07 6.93% 2.37% 1.00% 7.18% 7.15% 2.84% 4.17% -0.25% 4.64% 5.08% 4.24%
18 1-May-07 4.59% 11.02% 12.16% 2.80% 19.42% 2.55% 1.91% 0.73% 4.89% 5.09% 3.20%
19 1-Jun-07 0.41% -3.98% 3.93% -4.06% 0.70% -4.14% 2.60% -3.66% -7.23% 0.85% -1.80%
20 2-Jul-07 -5.85% -10.11% -2.23% -1.66% 7.66% 0.04% 1.24% -3.06% -8.39% 1.49% -3.25%
21 1-Aug-07 2.83% -3.72% 8.65% -0.53% 4.97% 6.42% 0.28% 6.66% 6.70% 1.09% 1.28%
67 1-Jun-11 3.35% -0.15% 3.86% 3.86% -3.56% -2.97% -3.24% -6.90% -4.07% -2.53% -1.84%
68 1-Jul-11 2.53% -14.09% -10.97% 5.24% 15.12% 0.82% -5.16% -12.05% -6.79% -1.97% -2.17%
69 1-Aug-11 5.12% -28.23% -33.99% -2.32% -1.46% -1.86% -9.36% -17.20% -0.27% -6.85% -5.85%
70 1-Sep-11 -2.92% -31.32% -32.29% -6.66% -0.92% -2.10% -5.99% -28.81% -7.13% -1.89% -7.45%
71 3-Oct-11 5.58% 14.13% 26.47% 6.77% 5.97% 1.90% 9.37% 10.99% 8.57% 7.24% 10.23%
72 1-Nov-11 3.58% 7.58% -8.23% -3.27% -5.74% -8.89% -4.89% -22.61% 5.11% 3.56% -0.51%
73 1-Dec-11 4.91% -3.13% -3.78% 1.47% 5.79% 2.83% 12.79% 2.18% 7.56% 5.24% 0.85%
74 3-Jan-12 -1.28% 13.17% 12.05% 12.88% 11.97% -2.10% 4.37% 24.87% -1.13% -1.22% 4.27%
75 1-Feb-12 0.94% 2.85% 12.71% 4.11% 7.73% 1.38% 2.22% 13.74% -0.19% 1.90% 2.97%

10.7 Alternatives to the Sample Variance-Covariance: Constant Correlation

The constant correlation model of Elton and Gruber (1973) computes the
variance-covariance matrix by assuming that the variances of the asset returns
are the sample returns, but that the covariances are all related by the same
correlation coefficient, which is generally taken to be the average correlation
coefficient of the assets in question. Since Cov(ri,rj) = σij = ρijσiσj, this means
that in the constant correlation model:

⎧ σ ii = σ i2 when i = j
σ ij = ⎨
⎩σ ij = ρσ iσ j when i ≠ j
 265 Calculating the Variance-Covariance Matrix

Using our data for the 10 stocks, we can implement the constant correlation
model. We first compute the correlations of all the stocks:

A B C D E F G H I J K
1 ESTIMATING THE CONSTANT CORRELATION VARIANCE-COVARIANCE MATRIX
2 Correlation 0.20
3
4 MCD X MT MSFT AAPL K GE BAC PFE XOM
5 MCD 0.0020 0.0018 0.0015 0.0007 0.0010 0.0005 0.0010 0.0018 0.0006 0.0005
6 X 0.0018 0.0380 0.0064 0.0031 0.0044 0.0020 0.0043 0.0077 0.0025 0.0020
7 MT 0.0015 0.0064 0.0267 0.0026 0.0037 0.0017 0.0036 0.0065 0.0021 0.0017
8 MSFT 0.0007 0.0031 0.0026 0.0063 0.0018 0.0008 0.0017 0.0031 0.0010 0.0008
9 AAPL 0.0010 0.0044 0.0037 0.0018 0.0126 0.0012 0.0025 0.0044 0.0014 0.0011
10 K 0.0005 0.0020 0.0017 0.0008 0.0012 0.0026 0.0011 0.0020 0.0007 0.0005
11 GE 0.0010 0.0043 0.0036 0.0017 0.0025 0.0011 0.0122 0.0044 0.0014 0.0011
12 BAC 0.0018 0.0077 0.0065 0.0031 0.0044 0.0020 0.0044 0.0393 0.0025 0.0020
13 PFE 0.0006 0.0025 0.0021 0.0010 0.0014 0.0007 0.0014 0.0025 0.0041 0.0007
14 XOM 0.0005 0.0020 0.0017 0.0008 0.0011 0.0005 0.0011 0.0020 0.0007 0.0026
15 <-- {=constantcorr('Page 253'!B3:K62,'Page 265'!B2)}

We’ve written a VBA function to compute this matrix from the return data:

Function constantcorr(data As Range, corr As Double) _

As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = data.Columns.Count
numrows = data.Rows.Count
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
If Abs(corr) >= 1 Then GoTo Out
For i = 1 To numcols
For j = 1 To numcols
If i = j Then
matrix(i - 1, j - 1) = Application. _
WorksheetFunction.Var_S(data.Columns(i))
Else
266 Chapter 10

matrix(i - 1, j - 1) = corr * jjunk(data, i) * _

jjunk(data, j)
End If
Next j
Next i
Out:
If Abs(corr) >= 1 Then constantcorr = VarCovar(data) _
Else constantcorr = matrix
End Function

10.8 Alternatives to the Sample Variance-Covariance: Shrinkage Methods

A third class of methods of estimating the variance-covariance matrix has

recently achieved popularity. So-called shrinkage methods assume that the
variance-covariance matrix is a convex combination of the sample covariance
matrix and some other matrix:

Shrinkage variance-covariance matrix =

λ *Sample var -cov + (1 − λ ) *Other matrix
In the example below, the “other” matrix is a diagonal matrix of only vari-
ances, with zeros elsewhere. The shrinkage estimator λ = 0.3 (cell B20).
267 Calculating the Variance-Covariance Matrix

A B C D E F G H

ESTIMATING THE VARIANCE-COVARIANCE MATRIX USING

THE SHRINKAGE APPROACH
Gives weight 0.30 (the shrinkage factor) to sample var-cov and
weight 0.70 to a diagonal matrix of only variances
1
2 Return data
3 Date GE MSFT JNJ K BA IBM
4 03-Jan-94 56.44% -1.50% 6.01% -9.79% 58.73% 21.51%
5 03-Jan-95 18.23% 33.21% 41.56% 7.46% -0.24% 6.04%
6 02-Jan-96 56.93% 44.28% 57.71% 37.76% 65.55% 27.33%
7 02-Jan-97 42.87% 79.12% 22.94% -5.09% 54.34% 41.08%
8 02-Jan-98 47.11% 38.04% 17.62% 32.04% 37.11% 2.63%
9 04-Jan-99 34.55% 85.25% 26.62% -10.74% 15.05% -2.11%
10 03-Jan-00 28.15% 11.20% 3.41% -48.93% 43.53% 23.76%
11 02-Jan-01 4.61% -47.19% 10.69% 11.67% 28.29% 21.76%
12 02-Jan-02 -19.74% 4.27% 23.11% 19.90% -15.09% 4.55%
13 02-Jan-03 -44.78% -29.47% -5.67% 10.88% -23.23% 15.54%
14 02-Jan-04 35.90% 18.01% -1.27% 15.49% 39.82% 31.80%
15
16 Average 23.66% 21.38% 18.43% 5.51% 27.63% 17.63% <-- =AVERAGE(G4:G14)
17 Standard deviation 32.17% 40.71% 18.97% 23.86% 29.93% 13.56% <-- =STDEV(G4:G14)
18 Variance 0.1035 0.1657 0.0360 0.0570 0.0896 0.0184 <-- =VAR(G4:G14)
19
20 Shrinkage factor λ 0.3 <-- This is the weight put on the sample var-cov
21
Shrinkage matrix
22 Uses the array formula {=B20*B34:G39+(1-B20)*B44:G49} to compute the shrinkage covariance matrix
23 GE MSFT JNJ K BA IBM
24 GE 0.1035 0.0228 0.0066 -0.0013 0.0257 0.0037
25 MSFT 0.0228 0.1657 0.0124 -0.0016 0.0114 -0.0007
26 JNJ 0.0066 0.0124 0.0360 0.0054 0.0030 -0.0012
27 K -0.0013 -0.0016 0.0054 0.0570 -0.0023 -0.0014
28 BA 0.0257 0.0114 0.0030 -0.0023 0.0896 0.0074
29 IBM 0.0037 -0.0007 -0.0012 -0.0014 0.0074 0.0184
30
31
Uses the array formula {=MMULT(TRANSPOSE(B4:G14-B16:G16),B4:G14-B16:G16)/10} to compute the constant
32 sample covariance matrix. In the shrinkage var-cov, this matrix is given weight lambda.
33 GE MSFT JNJ K BA IBM
34 GE 0.1035 0.0758 0.0222 -0.0043 0.0857 0.0123
35 MSFT 0.0758 0.1657 0.0412 -0.0052 0.0379 -0.0022
36 JNJ 0.0222 0.0412 0.0360 0.0181 0.0101 -0.0039
37 K -0.0043 -0.0052 0.0181 0.0570 -0.0076 -0.0046
38 BA 0.0857 0.0379 0.0101 -0.0076 0.0896 0.0248
39 IBM 0.0123 -0.0022 -0.0039 -0.0046 0.0248 0.0184
40
41
Uses the array formula {=MMULT(TRANSPOSE(B4:G14-B16:G16),B4:G14-B16:G16)/10*IF(A44:A49=B43:G43,1,0)} to
compute a matrix with only variances on diagonal and zeros elsewhere. In the shrinkage var-cov this matrix is given
42 weight 1-lambda.
43 GE MSFT JNJ K BA IBM
44 GE 0.1035 0.0000 0.0000 0.0000 0.0000 0.0000
45 MSFT 0.0000 0.1657 0.0000 0.0000 0.0000 0.0000
46 JNJ 0.0000 0.0000 0.0360 0.0000 0.0000 0.0000
47 K 0.0000 0.0000 0.0000 0.0570 0.0000 0.0000
48 BA 0.0000 0.0000 0.0000 0.0000 0.0896 0.0000
49 IBM 0.0000 0.0000 0.0000 0.0000 0.0000 0.0184
268 Chapter 10

There is little theory about choosing the proper shrinkage estimator.6 Our
suggestion is to choose a shrinkage operator λ so that the GMVP is wholly
positive (see next section for details).

10.9 Using Option Information to Compute the Variance Matrix7

Another way to compute the variance matrix is to use the information from
the options market. We use the implied volatility for each of the stocks from
their at-the-money call options and then compute the variance matrix using
constant correlation:

⎧ σ i2,implied if i = j
σ ij = ⎨
⎩ρσ i ,impliedσ j ,implied if i ≠ j

Here’s an example for our 10-stock case. We use data from the options
markets and the function CallVolatility discussed in Chapter 17 to compute
the implied volatility for each of the 10 stocks and the S&P 500. We use our
data set of five years of returns to compute the historical volatility:

6. Three papers by Olivier Ledoit and Michael Wolf may offer some guidance: “Improved Estima-
tion of the Covariance Matrix of Stock Returns with an Application to Portfolio Selection,”
Journal of Empirical Finance, 2003. “A Well-Conditioned Estimator for Large-Dimensional
Covariance Matrices,” Journal of Multivariate Analysis, 2004. “Honey, I Shrunk the Sample
Covariance Matrix,” Journal of Portfolio Management, 2004.
7. This section uses some information from the chapters on options.
269 Calculating the Variance-Covariance Matrix

A B C D E F G
1 COMPARING IMPLIED AND HISTORICAL VOLATILITY
2 Current date 10-Jul-12
3 Expire date 17-Aug-12 =callvolatility(B7,C7,($B$3-$B$2)/365,$B$4,D7)
4 Interest 1.00%
5
Stock Implied Historical
6 price Strike Price volatility volatility
7 MCD 90.25 90.00 2.09 16.50% 15.42% <-- {=TRANSPOSE('Betas etc'!B11:L11)}
8 X 20.28 20.00 1.58 55.05% 67.00%
9 MT 14.67 15.00 0.75 47.12% 56.18%
10 MSFT 29.74 30.00 1.07 30.74% 27.21%
11 AAPL 608.21 610.00 25.30 33.04% 38.53%
12 K 48.98 50.00 0.40 12.44% 17.67%
13 GE 19.62 20.00 0.39 21.63% 37.91%
14 BAC 7.48 8.00 0.22 43.04% 68.10%
15 PFE 22.44 23.00 0.18 13.38% 21.87%
16 XOM 83.11 85.00 0.90 15.23% 17.55%
17 ^GSPC 1,341.47 1,350.00 22.50 14.96% 19.21%
18
19
20 Comparing Implied and Historical VolaƟliƟes
21
22
Historical: 2007 - 2012, Implied: July 10, 2012
23 80%
24 70%
25 60%
26
50%
27
28 40%
Implied volaƟlity
29 30%
Historical volaƟlity
30 20%
31 10%
32 0%
33
MCD

MT

MSFT

AAPL

GE

BAC

PFE

XOM

^GSPC

34
35

We can now use the implied volatilities as the basis for a constant correla-
tion variance-covariance matrix:
 270 Chapter 10

A B C D E F G H I J K
1 CONSTANT CORRELATION MATRIX WITH IMPLIED VOLATILITIES
2 Correlation 0.20
3
4 MCD X MT MSFT AAPL K GE BAC PFE XOM
5 MCD 0.0272 0.0182 0.0156 0.0101 0.0109 0.0041 0.0071 0.0142 0.0044 0.0050
6 X 0.0182 0.3031 0.0519 0.0338 0.0364 0.0137 0.0238 0.0474 0.0147 0.0168
7 MT 0.0156 0.0519 0.2221 0.0290 0.0311 0.0117 0.0204 0.0406 0.0126 0.0143
8 MSFT 0.0101 0.0338 0.0290 0.0945 0.0203 0.0076 0.0133 0.0265 0.0082 0.0094
9 AAPL 0.0109 0.0364 0.0311 0.0203 0.1091 0.0082 0.0143 0.0284 0.0088 0.0101
10 K 0.0041 0.0137 0.0117 0.0076 0.0082 0.0155 0.0054 0.0107 0.0033 0.0038
11 GE 0.0071 0.0238 0.0204 0.0133 0.0143 0.0054 0.0468 0.0186 0.0058 0.0066
12 BAC 0.0142 0.0474 0.0406 0.0265 0.0284 0.0107 0.0186 0.1852 0.0115 0.0131
13 PFE 0.0044 0.0147 0.0126 0.0082 0.0088 0.0033 0.0058 0.0115 0.0179 0.0041
14 XOM 0.0050 0.0168 0.0143 0.0094 0.0101 0.0038 0.0066 0.0131 0.0041 0.0232
15 <-- {=ImpliedVol(B5:K14,E22:E31,B2)}
16
17 Current date 10-Jul-12
18 Expire date 17-Aug-12
19 Interest 1.00%
20
Stock Implied
21 price Strike Price volatility
22 MCD 90.25 90.00 2.09 16.50% <-- =callvolatility(B22,C22,($B$18-$B$17)/365,$B$19,D22)
23 X 20.28 20.00 1.58 55.05%
24 MT 14.67 15.00 0.75 47.12%
25 MSFT 29.74 30.00 1.07 30.74%
26 AAPL 608.21 610.00 25.30 33.04%
27 K 48.98 50.00 0.40 12.44%
28 GE 19.62 20.00 0.39 21.63%
29 BAC 7.48 8.00 0.22 43.04%
30 PFE 22.44 23.00 0.18 13.38%
31 XOM 83.11 85.00 0.90 15.23%

The programming of the ImpliedVolVarCov is similar to previous VBAs

in this chapter:

Function ImpliedVolVarCov(varcovarmatrix As _
Range, volatilities As Range, corr As Double)
As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = varcovarmatrix.Columns.Count
numrows = numcols
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
If Abs(corr) >= 1 Then GoTo Out
271 Calculating the Variance-Covariance Matrix

For i = 1 To numcols
For j = 1 To numcols
If i = j Then
matrix(i - 1, j - 1) = volatilities(i) ∧ 2
Else
matrix(i - 1, j - 1) = corr * _
volatilities(i) * volatilities(j)
End If
Next j
Next i
Out:
If Abs(corr) >= 1 Then ImpliedVolVarCov = _
“ERR” Else ImpliedVolVarCov = matrix
End Function

10.10 Which Method to Compute the Variance-Covariance Matrix?

This chapter gives five alternatives to computing the variance-covariance

matrix:

• The sample variance-covariance

• The single-index model
• The constant correlation approach
• Shrinkage methods
• Implied volatility-based variance-covariance matrices

How do we compare these alternatives? Which one should we choose? We

could compare the technical outcomes of using each of these methods—for
example, show the alternative values of the GMVP using different methods—
but this largely misses the point.
The choice of how to compute the variance-covariance matrix is largely a
question of how you view capital markets. If you strongly believe that the past
predicts the future, then perhaps your choice should be to use the sample
varcov matrix. This author prefers to get away from history … our preference
is to use an option-based volatility model with a changing correlation:
In “normal” times we would use a “normal” correlation of between 0.2 and
0.3; in times of crisis, we would use a much higher correlation, say, ρ =
0.5 − 0.6.
272 Chapter 10

10.11 Summary

In this chapter we considered how to compute the variance-covariance matrix

which is central to all portfolio optimization. Starting with the standard sample
variance-covariance matrix, we also showed how to compute several alterna-
tives that have appeared in the literature as perhaps improving portfolio
computations.

Exercises

1. Below you will find annual return data for six furniture companies for the years 1982–
1992. Use these data to calculate the variance-covariance matrix of the returns.

A B C D E F G H
1 DATA FOR 6 FURNITURE COMPANIES
Leggett Herman Shaw
La-Z-Boy Kimball Flexsteel
2 & Platt Miller Industries
3 1982 36.67% 0.20% 41.54% 21.92% 26.13% 22.50%
4 1983 122.82% 61.43% 195.09% 62.27% 73.38% 117.89%
5 1984 14.44% 63.51% -38.38% -1.27% 45.15% 7.80%
6 1985 21.39% 28.42% 1.30% 81.17% 24.27% 38.14%
7 1986 45.36% -7.44% 21.89% 19.83% 10.73% 54.48%
8 1987 20.19% 48.27% 9.11% -10.21% -11.92% 26.82%
9 1988 -8.94% -11.28% 12.65% 13.77% 7.06% -6.24%
10 1989 27.02% 12.85% 12.08% 32.55% -7.55% 123.03%
11 1990 -11.64% 2.42% -17.13% -6.48% 1.31% 15.48%
12 1991 20.29% 6.90% 3.62% 50.12% -5.54% 19.92%
13 1992 34.08% 22.21% 33.46% 84.40% 5.71% 62.76%
14
15 Beta 0.80 0.95 0.65 0.85 0.85 1.40
16 Mean returns 29.24% 20.68% 25.02% 31.64% 15.34% 43.87% <-- =AVERAGE(G3:G13)

The remaining exercises refer to the data in the tab Price data on the exercise spreadsheet
that accompanies this book. This tab gives three years of price data for six stocks and the
S&P 500 as a surrogate for the market.

2. Compute the returns of the data and the statistics for each of the assets (mean return,
variance and standard deviation of return, beta).

3. Compute the sample variance-covariance matrix and the correlation matrix for the six
stocks.

4. Use the function SIM defined in the chapter to compute the single-index variance-cova-
riance matrix.

5. Compute the global minimum variance portfolio (GMVP) using the sample variance-
covariance matrix.

6. Compute the GMVP using the constant correlation covariance matrix.

11 Estimating Betas and the Security Market Line

11.1 Overview

The capital asset pricing model (CAPM) is one of the two most influential
innovations in financial theory in the latter half of the twentieth century.1 By
integrating the portfolio decision with utility theory and the statistical behavior
of asset prices, the formulators of the CAPM defined the paradigm which is
now generally used for the analysis of stock prices.
What does the CAPM actually say? What are its empirical implications?
Roughly speaking, we can differentiate between two kinds of implications of
the CAPM. First, the capital market line (CML) defines the individual optimal
portfolios for an investor interested in the mean and variance of her optimal
portfolio. Second, given agreement between investors on the statistical proper-
ties of asset returns and on the importance of mean-variance optimization, the
security market line (SML) defines the risk-return relation for each individual
asset.
It is useful to differentiate between the case wherein a risk-free asset exists
and the case wherein there is no risk-free asset.2

Case 1: A Risk-Free Asset Exists

Suppose a risk-free asset exists and has return rf. We can differentiate between
the individual optimization of investors and the general equilibrium implica-
tions of the CAPM:

• Individual optimization: Assuming that investors optimize based on the

expected return and standard deviation of their portfolio returns (in the jargon
of finance—they have “mean-variance” preferences), the CAPM states
that each individual investor’s optimal portfolio falls on the line
E ( rp ) = rf + σ p [ E (rx ) − rf ], where portfolio x is a portfolio which maximizes
E ( ry ) − rf
for all feasible portfolios y. Proposition 1 of Chapter 9 shows
σy
S −1 [ E (r ) − rf ]
that x can be computed by x = {x1 , x2, … , xN } = , where
∑ S −1 [E (r ) − rf ]
1. The other remarkable innovation is option pricing theory, which is discussed in Chapters
15–19. These two innovations together have accounted for a number of Nobel Prizes in Econom-
ics: Harry Markowitz (1990), William Sharpe (1990), Myron Scholes (1997), and Robert Merton
(1997). But for their untimely demise, others associated with these theories—Jan Mossin (1936–
1987), and Fischer Black (1938–1995)—would doubtless also have received the Nobel.
2. The existence (or non-existence) of a risk-free asset is closely related to the investment horizon.
Assets which are risk-free over a short term may not be riskless over a longer term.
274 Chapter 11

S is the variance-covariance matrix of risky asset returns and

E (r ) = {E (r1 ) , E (r2 ) , … , E (rN )} is the vector of expected asset returns.
• General equilibrium: If all investors agree about the statistical assumptions
of the model—the variance-covariance matrix S and the vector of expected
asset returns E(r)—and if a risk-free asset exists, then individual asset returns
are defined by the security market line (SML):
Cov (ri, rM )
E (ri ) = rf + [E (rM ) − rf ]
σM 2

where M denotes the market portfolio—the value-weighted portfolio of all

Cov (ri, rM )
risky assets. The expression is generally termed the asset’s beta:
σM 2

Cov (ri, rM ) .
βi =
σM2

Case 2: No Risk-Free Asset Exists

If there is no risk-free asset, then the implications of the CAPM both for
individual optimization and for general equilibrium are defined by Black’s
(1972) zero-beta model (Proposition 3 of Chapter 9):

• Individual optimization: In the absence of a risk-free asset, individual optimal

portfolios will fall along the efficient frontier. As shown in Proposition 2 of
Chapter 9, this frontier is the upward-sloping portion of the mean-
sigma combinations created by the convex combination of any two optimizing
S −1 [ E (r ) − c1 ] S −1 [ E (r ) − c2 ]
portfolios x = and y = , where c1 and c2
∑ S [E (r ) − c1 ]
−1
∑ S −1 [E (r ) − c2 ]
are two arbitrary constants.
• General equilibrium: In the absence of a risk-free asset, if all investors agree
about the statistical assumptions of the model—the variance-covariance matrix
S and the vector of expected asset returns E(r)—then individual asset returns
are defined by the security market line (SML):

Cov ( ri, ry )
E (ri ) = E (rz ) + [ E (ry ) − E (rz )]
σ y2
where y is any efficient portfolio and z is a portfolio which has zero covariance
with y (the so-called “zero beta portfolio”).
275 Estimating Betas and the Security Market Line

The case of no risk-free asset is obviously weaker than the case of a risk-
free asset. If there is a risk-free asset, the general equilibrium version of the
CAPM says that all portfolios are situated on a single, agreed-upon line. If
there is no risk-free asset, then all optimal portfolios are on the same frontier;
but in this case asset betas can differ, since there are many portfolios y which
Cov ( ri, ry )
fulfill the equation E (ri ) = E (rz ) +
σ y2
[ E (ry ) − E (rz )].

The CAPM as a Prescriptive and a Descriptive Tool

As you can see from the discussion above, the CAPM is both prescriptive and
descriptive.
As a prescriptive tool, the CAPM tells a mean-variance investor how
to choose his optimal portfolio. By finding a portfolio of the form
S −1 [ E (r ) − c1 ]
, the investor can identify an optimal portfolio from the
∑ S −1 [E (r ) − c1 ]
data set.
As a descriptive tool, the CAPM gives conditions under which we can
generalize about the structure of expected returns in the market. Whether or
not a risk-free asset exists, these conditions assume that investors agree on the
statistical structure of asset returns—the variance-covariance matrix and the
expected returns. In this case all the returns are expected to lie on a security
Cov (ri, rM )
market line (SML) of the form E (ri ) = rf + [E (rM ) − rf ] (if there is
σM2

Cov ( ri, ry )
a risk-free asset) or of the form E (ri ) = E (rz ) + [ E (ry ) − E (rz )] if
σ y2
there is no risk-free asset.

This Chapter

In this chapter we look at some typical capital market data and replicate a
simple test of the descriptive part of the CAPM. This means that we have to
calculate the betas for a set of assets, and we then have to determine the equa-
tion of the security market line (SML). The test in this chapter is the simplest
possible test of the CAPM. There is an enormous literature in which the pos-
sible statistical and methodological pitfalls of CAPM tests are discussed. Good
276 Chapter 11

places to begin are textbooks by Elton, Gruber, Brown, and Goetzmann (2009),
and Bodie, Kane, and Marcus (2010).3

11.2 Testing the SML

Typical tests of the security market line (SML) start with return data on a set
of risky assets. The steps in the test are as follows:

• Determine a candidate for the market portfolio M. In our example we will

use the Standard & Poor’s 500 Index (S&P 500) as a candidate for M. This is
a critical step: In principle, the “true” market portfolio should—as pointed
out in Chapter 9—contain all the market’s risky assets in proportion to their
value. It is clearly impossible to calculate this theoretical market portfolio,
and we must therefore make do with a surrogate. As you will see in the next
two sections, the propositions of Chapter 9 can shed much light on how the
choice of the market surrogate affects the r-squared of our regression test of
the CAPM.
Cov (ri, rM )
• For each of the assets in question, determine the asset beta, βi = .
σM 2

This is often called the first-pass regression.

• Regress the mean returns of the assets on their respective betas (the second-
pass regression):
ri = γ 0 + γ 1βi

If the CAPM in its descriptive format holds, then the second-pass regression
should be the security market line.4
We illustrate the tests of the CAPM with a simple numerical example that
uses data for the 30 stocks in the Dow-Jones Industrials. We start with the
prices of the S&P 500 (symbol ∧GSPC) and the stocks in the DJ30 (some of
the rows and columns are not shown):

3. For further references, see the Selected References at the end of this book. Our personal
expositional favorite is a paper by Roll, “Ambiguity When Performance Is Measured by the
Securities Market Line,” Journal of Finance (1978).
4. This is a direct consequence of Propositions 3 and 4 of Chapter 9.
 277 Estimating Betas and the Security Market Line

A B C D E F G H I J K L M N

PRICE DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
1
July 2001 - July 2006
American
S&P500 American General General
Alcoa International Boeing Citigroup Caterpillar DuPont Disney Home Honeywell
Date Index Express Electric Motors
AA Group BA C CAT DD DIS Depot HD HON
^GSPC AXP GE GM
2 AIG
3 03-Jul-01 1211.23 35.37 81.32 33.73 53.64 40.97 24.73 36.29 25.02 38.33 49.72 48.26 32.75
4 01-Aug-01 1133.58 34.50 76.43 30.46 47.06 37.49 22.45 35.01 24.14 36.04 43.14 44.06 33.27
5 04-Sep-01 1040.94 28.06 76.24 24.30 30.79 33.15 20.11 32.07 17.68 32.93 33.81 36.79 23.57
6 01-Oct-01 1059.78 29.34 76.82 24.68 29.97 37.26 20.23 34.18 17.65 32.23 32.56 36.66 26.38
7 01-Nov-01 1139.45 35.09 80.54 27.60 32.43 39.34 21.45 38.21 19.43 34.08 39.63 44.78 29.77
8 03-Dec-01 1148.08 32.32 77.64 29.93 35.83 41.46 23.63 36.63 19.88 35.63 38.75 48.97 30.38
9 02-Jan-02 1130.20 32.59 72.51 30.13 37.83 39.06 22.91 38.06 20.21 33.03 40.77 48.09 30.19
10 01-Feb-02 1106.73 34.31 72.38 30.64 42.64 37.29 25.29 40.68 22.07 34.39 42.67 48.00 34.43
51 01-Jul-05 1234.18 27.48 59.76 47.68 65.02 42.62 53.28 41.22 25.37 33.53 34.89 42.97 38.43
52 01-Aug-05 1220.33 26.38 58.92 47.89 66.26 42.89 54.85 38.55 24.93 32.66 32.86 39.92 37.66
53 01-Sep-05 1228.81 24.05 61.67 49.79 67.18 44.60 58.07 38.16 23.88 32.94 29.42 37.76 36.89
54 03-Oct-05 1207.01 23.92 64.49 49.41 63.91 44.86 52.22 40.62 24.11 33.17 26.33 40.63 33.65
55 01-Nov-05 1249.48 27.16 66.82 51.04 67.67 48.04 57.37 42.02 24.67 34.94 21.44 41.46 36.15
56 01-Dec-05 1248.29 29.30 67.91 51.08 69.71 48.02 57.36 41.77 23.97 34.53 19.01 40.17 36.85
57 03-Jan-06 1280.08 31.21 65.15 52.19 67.79 46.09 67.69 38.48 25.31 32.27 23.56 40.24 38.01

We first transform these price data to returns:

A B C D E F G H I J K L M N
RETURN DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
1 July 2001 - July 2006
American
S&P 500 American General General
Alcoa International Boeing Citigroup Caterpillar DuPont Disney Home Honeywell
Date Index Express Electric Motors
AA Group BA C CAT DD DIS Depot HD HON
^GSPC AXP GE GM
2 AIG
3
=AVERAGE(C9:C68)
4 Average return 0.07% -0.09% -0.54% 0.72% 0.67% 0.30% 1.79% 0.18% 0.29% -0.23% -0.87% -0.52% 0.29%
5 Beta 1.00 1.90 0.99 1.38 1.15
=SLOPE(C9:C68,$B$9:$B$68) 1.30 1.39 1.00 1.28 0.84 1.41 1.55 1.66
6 Alpha 0 -0.23% -0.61% 0.62% 0.58% 0.21% 1.69% 0.11% 0.20% -0.30% -0.97% -0.63% 0.17%
7 R-squared 1 0.6085 0.3518 0.7052 0.2487 0.5972
=INTERCEPT(C9:C68,$B$9:$B$68) 0.5158 0.4362 0.3845 0.3221 0.2607 0.5288 0.5473
8
9 01-Aug-01 -6.63% -2.49% -6.20% -10.20% -13.09% -8.88% -9.67% -3.59% -3.58% -6.16% -14.20% -9.11% 1.58%
10 04-Sep-01 -8.53% -20.66% =RSQ(C9:C68,$B$9:$B$68)
-0.25% -22.59% -42.42% -12.30% -11.01% -8.77% -31.14% -9.02% -24.37% -18.03% -34.47%
11 01-Oct-01 1.79% 4.46% 0.76% 1.55% -2.70% 11.69% 0.59% 6.37% -0.17% -2.15% -3.77% -0.35% 11.26%
12 01-Nov-01 7.25% 17.90% 4.73% 11.18% 7.89% 5.43% 5.86% 11.15% 9.61% 5.58% 19.65% 20.01% 12.09%
13 03-Dec-01 0.75% -8.22% -3.67% 8.10% 9.97% 5.25% 9.68% -4.22% 2.29% 4.45% -2.25% 8.94% 2.03%
14 02-Jan-02 -1.57% 0.83% -6.84% 0.67% 5.43% -5.96% -3.09% 3.83% 1.65% -7.58% 5.08% -1.81% -0.63%
15 01-Feb-02 -2.10% 5.14% -0.18% 1.68% 11.97% -4.64% 9.88% 6.66% 8.80% 4.03% 4.55% -0.19% 13.14%
16 01-Mar-02 3.61% 0.47% -2.52% 11.66% 4.85% 9.02% 2.38% 0.66% 0.36% -2.89% 13.20% -2.72% 0.41%

The First-Pass Regression

Row 4 gives each asset’s average monthly return over the 60-month period
(to annualize these returns, we would multiply by 12). Rows 5–7 report the
results of the first-pass regression. For each asset i we report the regression
rit = αi + βirSP,t. We use the Excel function Slope to compute the β of each
asset, and the functions Intercept and Rsq to compute the α and R2 for each
regression.
 278 Chapter 11

As a check, we also compute the α, β, and R2 for the S&P 500 index (column
B). Not surprisingly, αSP = 0, βSP = 1, R2 = 1.

The Second-Pass Regression

The SML postulates that the mean return of each security should be linearly
related to its beta. Assuming that the historic data provide an accurate descrip-
tion of the distribution of future returns, we postulate that E(Ri) = α + βiΠ +
εi, where the definitions of α and Π depend on whether we are in Case 1 or
Case 2 of section 11.1:

⎧ rf Case 1: there exists a risk-free asset

⎪
α=⎨ Case 2: no risk-free asset. z has zero
⎪⎩E (rz ) correlation with efficient portfolio y
⎧ E (rM ) − rf Case 1
Π=⎨
⎩E ( ry ) − E (rz ) Case 2

In the second step of our test of the CAPM, we examine this hypothesis by
regressing the mean returns on the β’s.

A B C D E F G H
1 THE SECOND-PASS REGRESSION
Average
monthly
2 Stock return Beta Alpha
3 Alcoa AA -0.09% 1.9028 -0.0023 Second-pass regression, regressing monthly returns on Beta
4 American International Group AIG -0.54% 0.9936 -0.0061 Intercept 0.0036 <-- =INTERCEPT(B3:B32,C3:C32)
5 American Express AXP 0.72% 1.3784 0.0062 Slope -0.0020 <-- =SLOPE(B3:B32,C3:C32)
6 Boeing BA 0.67% 1.1515 0.0058 R-squared 0.0238 <-- =RSQ(B3:B32,C3:C32)
7 Citigroup C 0.30% 1.2952 0.0021
8 Caterpillar CAT 1.79% 1.3903 0.0169 t-statistic, intercept 1.2381 <-- =tintercept(B3:B32,C3:C32)
9 DuPont DD 0.18% 1.0009 0.0011 t-statistic, slope -0.8254 <-- =tslope(B3:B32,C3:C32)
10 Disney DIS 0.29% 1.2805 0.0020
11 General Electric GE -0.23% 0.8420 -0.0030
12 General Motors GM -0.87% 1.4060 -0.0097
13 Home Depot HD -0.52% 1.5528 -0.0063
14 Honeywell HON 0.29% 1.6640 0.0017
15 Hewlett Packard HPQ 0.61% 1.9594 0.0046
16 IBM -0.47% 1.5764 -0.0058
17 Intel INTC -0.73% 2.2648 -0.0089
18 Johnson & Johnson JNJ 0.34% 0.2471 0.0032
19 JP Morgan JPM 0.18% 1.7917 0.0005
20 Coca Cola KO 0.12% 0.3590 0.0009
21 McDonalds MCD 0.35% 1.2646 0.0025
22 3M MMM 0.64% 0.6504 0.0059
23 AltriaMO 1.30% 0.6633 0.0125
24 Merck MRK -0.63% 0.6099 -0.0068
25 Microsoft MSFT -0.35% 1.1219 -0.0043
26 Pfizer PFE -0.74% 0.5572 -0.0078
27 Proctor Gamble PG 0.94% 0.1687 0.0093
28 AT&T T -0.41% 1.1275 -0.0050
29 United Technologies UTX 1.03% 1.0659 0.0095
30 Verizon VZ -0.49% 1.0231 -0.0057
31 Walmart W MT -0.25% 0.6000 -0.0030
32 Exxon Mobil XOM 0.88% 0.6455 0.0083
279 Estimating Betas and the Security Market Line

The results (cells F4:G6) are very disappointing. Our test yields the follow-
ing SML:
E (ri ) = 0 .0020 βi , R 2 = 0.0238
.0036 − 0
↑ ↑
γ0 γ1

There is nothing about these numbers which inspires confidence:

• γ0 should correspond to the risk-free rate over the period. In section 11.9 we
discuss this rate, which changed wildly over the 60 months surveyed. At this
point it is enough to point out that the average monthly risk-free interest rate
was 0.18% (or 0.0018, exactly half of γ0).
• γ1 should correspond to E(rM) − rf. The average monthly return of the
S&P 500 over the period was −0.10% and the average monthly risk-free inter-
est rate was 0.18%, so that γ1 should be approximated by −0.28% (or 0.0028).
• Both the t-statistics for the i (cell G8) and the slope (cell G9) indicate that
they are not statistically different from zero.5

Our test of the SML has failed. The CAPM may have prescriptive validity,
but it does not describe our data.

Why Are the Results So Bad?

The experiment we did—checking the CAPM by plotting the security market

line—does not appear to have worked out very well. There does not appear to
be much evidence in favor of the SML: Neither the R2 of the regression nor
the t-statistics give much evidence that there is a relation between expected
return and portfolio β.
There are a number of reasons why these disappointing results may hold:

• One reason is that perhaps the CAPM itself does not hold. This could be
true for a variety of reasons:
Ⴛ Perhaps in the market short sales of assets are restricted. Our derivation
of the CAPM (see Chapter 9 on efficient portfolios) assumes that there
are no short-sale restrictions. Clearly this is an unrealistic assumption.
The computation of efficient portfolios when short sales are restricted is

5. The functions TIntercept and TSlope were created by the author. They are attached to the
spreadsheet for this chapter and are discussed in Chapter 3.
280 Chapter 11

considered in Chapter 12. In this case, however, there are no simple relations
(such as those proved in Chapter 9) between the returns of assets and their
betas. In particular, if short sales are restricted, there is no reason to expect
the SML to hold.
Ⴛ Perhaps individuals do not have homogeneous probability assessments,
or perhaps they do not have the same expectations of asset returns, variances,
and covariances.
• Perhaps the CAPM holds only for portfolios and not for single assets.
• Perhaps our set of assets isn’t large enough: After all, the CAPM talks about
all risky assets, whereas we have chosen—for illustrative purposes—to do our
test on a very small subset of these assets. The literature on CAPM testing
records tests in which the set of risky assets has been expanded to include
bonds, real estate, and even non-diversifiable assets such as human capital.
• Perhaps the “market portfolio” isn’t efficient. This possibility is suggested
by the mathematics of Chapter 9 on efficient portfolios, and it is this sugges-
tion which we further explore in the next section.
• Perhaps the CAPM holds only if the market returns are positive (in the
period surveyed they were, on average, negative).

11.3 Did We Learn Something?

The results of our exercise in section 11.1 are quite disappointing. Did we
learn anything positive from this exercise? Absolutely. For example, the regres-
sion model does a pretty good job of describing individual asset returns in
relation to the S&P 500:
281 Estimating Betas and the Security Market Line

Beta and R2 for Each Stock

2.50 as Regressed on SP500

2.00

Beta
1.50 R-squared

1.00

0.50

0.00

On average the S&P 500 describes about 35% of the variability of the DJ30
stocks, which have an average beta of 1.12. If we exclude the seven stocks
with the lowest R2, the S&P describes almost 43% of the variation in the
stocks’ returns:

A B C D E F G H I
1 OUR SML EXERCISE: WHAT DID WE LEARN?
2 Average alpha 0.06% <-- =AVERAGE('Page 277 bottom'!C6:AF6)
3 Average beta 1.12 <-- =AVERAGE('Page 277 bottom'!C5:AF5)
4 Average r-squared 0.3510 <-- =AVERAGE('Page 277 bottom'!C7:AF7)
5
6 Average R2 for best regressions
7 Cutoff for R2 0.2 <-- Below we count all R2 which are greater than this number
8 9.8258 <-- =SUMIF('Page 277 bottom'!C7:AF7,">"&TEXT(B7,"0.00"))
9 23 <-- =COUNTIF('Page 277 bottom'!C7:AF7,">"&TEXT(B7,"0.00"))
10 Average R2 0.4272 <-- =B8/B9
11
12 T-statistics for intercept and slope
American
American
Alcoa International Boeing Citigroup Caterpillar DuPont Disney
Express
AA Group BA C CAT DD DIS
AXP
13 AIG
14 t-stat for intercept 0.3144 0.6324 -1.0525 -0.1584 -0.2013 -1.6120 -0.0192 -0.0371
15 t-stat for slope 9.4942 5.6112 11.7783 4.3815 9.2729 7.8607 6.6993 6.0199
16
Average absolute
17 t-stat for intercept 0.3998 <-- {=AVERAGE(ABS(B14:AE14))}
Average t-stat
18 for slope 5.7866 <-- =AVERAGE(B15:AE15)
282 Chapter 11

Cell B10 above computes the average R2 for those regressions which had
an R2 > 0.2. This is 23 of the Dow-Jones 30. So—on average the first-pass
regressions are very significant. The average R2 of 35% that we got for our
first-pass regressions of the basic SML is actually a respectable number in
finance. Students—influenced by over-enthusiastic statistics instructors and an
overly linear view of the world—often feel that the R2 of any convincing
regression should be at least 90%. Finance does not appear to be a highly linear
profession: A good rule of thumb is that any financial regression that gives an
R2 greater than 80% is possibly misspecified and misleading.6
Another way to look at the significance of our results is to compute the
t-statistics for the intercept and slope of the first-pass regressions (rows 14–15
above). While the intercepts are not significantly different from zero (since
their t-statistic is less than 2), the slopes are very significant.

An Excel Note: Computing the Absolute Value of an Array of Numbers

In the computations above we use a neat Excel trick related to array functions
(see Chapter 34). By using Abs as an array function (that is, by entering the
function using [Ctrl]+[Shift]+[Enter]), we can compute the average of the
absolute values of a vector of numbers. A simple example is shown below:

A B C D E F

USING ABS FUNCTION IN ARRAY

The Excel "Abs" function computes the absolute value
If we use it as an array function, it can be applied to a range
1 of numbers
2
3 Numbers 1 -2 -3 -6 8
4
5 Average number -0.4000 <-- =AVERAGE(B3:F3)
Average absolute
6 number 4.0000 <-- {=AVERAGE(ABS(B3:F3))}
The above, but not
7 as array function 1.0000 <-- =AVERAGE(ABS(B3:F3))

Notice cell B7: Using the same function as a regular function does not
produce the correct answer.

6. An exception to this useful rule relates to diversified portfolios—here the R2 increases

dramatically.
 283 Estimating Betas and the Security Market Line

11.4 The Non-Efficiency of the “Market Portfolio”

When we calculated the SML in section 11.1, we regressed the mean return
of each asset on the returns of the market portfolio. The propositions of
Chapter 9 on efficient portfolios suggest that our failure to find adequate
results may stem from the fact that the S&P 500 portfolio is not efficient rela-
tive to the set of the six assets which we have chosen. Proposition 3 of Chapter
9 states that if we had chosen to regress our asset returns on a portfolio that
is efficient with respect to the asset set itself, we would get an r-squared of
100%. Proposition 4 of Chapter 9 shows that if we get an r-squared of 100%
then the portfolio on which we regress the asset returns is necessarily efficient
with respect to the set of assets. In this section we give a numerical illustration
of these propositions.
In the spreadsheet below we create a “mysterious portfolio” in column B.
This portfolio (its construction is described in the next subsection) is efficient
with respect to the Dow-Jones 30. As you can see in cells A10:B12, when we
perform the second-pass regression—regressing the individual average returns
of the assets on their betas computed with respect to the mysterious portfo-
lio—the results are perfect. The resulting regression has an intercept of 0.0030
and a slope of 0.0425. Most important—it has an R2 of 100%.

A B C D E F G H I J K L M N
RETURN DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
1 July 2001 - July 2006
American
American General General Home
Mysterious Alcoa International Boeing Citigroup Caterpillar DuPont Disney Honeywell
Date Express Electric Motors Depot
portfolio AA Group BA C CAT DD DIS HON
AXP GE GM HD
2 AIG
3
4 Average return 4.55% -0.09% -0.54% 0.72% 0.67% 0.30% 1.79% 0.18% 0.29% -0.23% -0.87% -0.52% 0.29%
5 Beta -0.09 -0.20 0.10 0.09 0.00 0.35 -0.03 0.00 -0.13 -0.28 -0.19 0.00
6 Alpha 0.33% 0.36% 0.27% 0.27% 0.30% 0.19% 0.31% 0.30% 0.34% 0.38% 0.36% 0.30%
7 R-squared 0.0025 0.0242 0.0064 0.0024 0.0000 0.0579 0.0006 0.0000 0.0126 0.0176 0.0143 0.0000
8
9 SML--regressing the average returns on the betas
10 Intercept 0.0030 <-- =INTERCEPT(C4:AF4,C5:AF5)
11 Slope 0.0425 <-- =SLOPE(C4:AF4,C5:AF5)
12 R-squared 1.0000 <-- =RSQ(C4:AF4,C5:AF5)
13
14
15 01-Aug-01 -1.01% -2.49% -6.20% -10.20% -13.09% -8.88% -9.67% -3.59% -3.58% -6.16% -14.20% -9.11% 1.58%
16 04-Sep-01 0.40% -20.66% -0.25% -22.59% -42.42% -12.30% -11.01% -8.77% -31.14% -9.02% -24.37% -18.03% -34.47%
17 01-Oct-01 4.71% 4.46% 0.76% 1.55% -2.70% 11.69% 0.59% 6.37% -0.17% -2.15% -3.77% -0.35% 11.26%
18 01-Nov-01 -1.33% 17.90% 4.73% 11.18% 7.89% 5.43% 5.86% 11.15% 9.61% 5.58% 19.65% 20.01% 12.09%
19 03-Dec-01 8.11% -8.22% -3.67% 8.10% 9.97% 5.25% 9.68% -4.22% 2.29% 4.45% -2.25% 8.94% 2.03%

The Mysterious Portfolio Is Efficient

The propositions of Chapter 9 leave us with only one conclusion: The “mys-
terious portfolio” must be efficient with respect to the DJ30. And so it is. In
 284 Chapter 11

the spreadsheet below we show the construction of this efficient portfolio,

which follows the propositions of Chapter 9.

• We first construct the variance-covariance matrix S using the function Var-

covar defined in Chapter 10.
S −1 [ E (r ) − c ]
• We then compute the efficient portfolio by solving . In the
∑ S −1 [E (r ) − c ]
spreadsheet below we use c = 0.0030, which then turns out to be the intercept
of the second-pass regression.

A B C D E F G H I J K L M N
RETURN DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
1 July 2001 - July 2006
American
SP 500 General
Alcoa International American Boeing Citigroup Caterpillar DuPont Disney General Home Honeywell
Date Index Motors
AA Group Express AXP BA C CAT DD DIS Electric GE Depot HD HON
^SPX GM
2 AIG
3
=AVERAGE(C9:C68)
4 Average return 0.07% -0.09% -0.54% 0.72% 0.67% 0.30% 1.79% 0.18% 0.29% -0.23% -0.87% -0.52% 0.29%
5 Beta 1.00 1.90 0.99 1.38 1.15
=SLOPE(C9:C68,$B$9:$B$68) 1.30 1.39 1.00 1.28 0.84 1.41 1.55 1.66
6 Alpha 0 -0.23% -0.61% 0.62% 0.58% 0.21% 1.69% 0.11% 0.20% -0.30% -0.97% -0.63% 0.17%
7 R-squared 1 0.6085 0.3518 0.7052 0.2487
=INTERCEPT(C9:C68,$B$9:$B$68) 0.5972 0.5158 0.4362 0.3845 0.3221 0.2607 0.5288 0.5473
8
9 01-Aug-01 -6.63% -2.49% -6.20% -10.20% -13.09% -8.88% -9.67% -3.59% -3.58% -6.16% -14.20% -9.11% 1.58%
10 04-Sep-01 -8.53% -20.66% =RSQ(C9:C68,$B$9:$B$68)
-0.25% -22.59% -42.42% -12.30% -11.01% -8.77% -31.14% -9.02% -24.37% -18.03% -34.47%
11 01-Oct-01 1.79% 4.46% 0.76% 1.55% -2.70% 11.69% 0.59% 6.37% -0.17% -2.15% -3.77% -0.35% 11.26%
12 01-Nov-01 7.25% 17.90% 4.73% 11.18% 7.89% 5.43% 5.86% 11.15% 9.61% 5.58% 19.65% 20.01% 12.09%
13 03-Dec-01 0.75% -8.22% -3.67% 8.10% 9.97% 5.25% 9.68% -4.22% 2.29% 4.45% -2.25% 8.94% 2.03%
14 02-Jan-02 -1.57% 0.83% -6.84% 0.67% 5.43% -5.96% -3.09% 3.83% 1.65% -7.58% 5.08% -1.81% -0.63%
15 01-Feb-02 -2.10% 5.14% -0.18% 1.68% 11.97% -4.64% 9.88% 6.66% 8.80% 4.03% 4.55% -0.19% 13.14%
67 01-Jun-06 0.01% 2.00% -2.92% -2.13% -1.62% -2.15% 2.08% -2.21% -1.65% -3.14% 10.09% -5.91% -2.16%
68 03-Jul-06 -0.37% 3.61% -0.14% -2.08% -2.37% 1.71% -2.58% -2.58% -0.57% 1.03% -1.05% -1.18% -3.38%
69
70
71 The cells below compute the variance-covariance matrix for the DJ30 by using the formula {=varcovar(C9:AF68)}
72 AA AIG AXP BA C CAT DD DIS GE GM HD HON HPQ
73 AA 0.0093 0.0032 0.0038 0.0045 0.0033 0.0046 0.0040 0.0042 0.0024 0.0046 0.0046 0.0061 0.0060
74 AIG 0.0032 0.0044 0.0020 0.0010 0.0023 0.0022 0.0022 0.0015 0.0015 0.0019 0.0021 0.0027 0.0024
75 AXP 0.0038 0.0020 0.0042 0.0030 0.0031 0.0029 0.0020 0.0037 0.0019 0.0033 0.0033 0.0042 0.0050
76 BA 0.0045 0.0010 0.0030 0.0083 0.0014 0.0029 0.0021 0.0037 0.0020 0.0043 0.0026 0.0053 0.0042
99 UTX 0.0038 0.0012 0.0031 0.0050 0.0022 0.0026 0.0021 0.0038 0.0018 0.0030 0.0023 0.0049 0.0048
100 VZ 0.0028 0.0018 0.0019 0.0007 0.0023 0.0022 0.0018 0.0014 0.0013 0.0006 0.0016 0.0024 0.0030
101 WMT 0.0018 0.0014 0.0011 0.0003 0.0015 0.0011 0.0014 0.0009 0.0009 0.0012 0.0024 0.0008 0.0008
102 XOM 0.0024 0.0010 0.0010 0.0017 0.0009 0.0021 0.0012 0.0004 0.0007 0.0020 0.0007 0.0020 0.0012
103
104 Finding an efficient portfolio
105 Constant 0.30%
106
107 AA 5.45% <-- {=MMULT(MINVERSE(varcov),TRANSPOSE(C4:AF4)-B105)/SUM(MMULT(MINVERSE(varcov),TRANSPOSE(C4:AF4)-B105))}
108 AIG -11.77%
109 AXP -5.78%
110 BA -13.94%
111 C -36.60%
112 CAT 76.26%
113 DD -22.55%
114 DIS -17.05%
115 GE -8.80%
116 GM -37.73%
117 HD -37.21%
118 HON -17.40%
119 HPQ 39.79%
120 IBM -26.38%
121 INTC -18.62%
122 JNJ 65.08%
123 JPM 53.61%
124 KO -12.95%
125 MCD -12.18%
126 MMM -2.07%
127 MP 42.15%
128 MRK 8.29%
129 MSFT 3.61%
130 PFE -61.19%
131 PG 54.67%
132 T -8.38%
133 UTX 44.05%
134 VZ -36.55%
135 WMT 64.78%
136 XOM 29.40%
137 Sum 100.00%
 285 Estimating Betas and the Security Market Line

The “mysterious portfolio” is not unique. Following we show results using

another constant c, which gives another version of the SML.

A B C D E F G H I J K L M N
RETURN DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
1 July 2001 - July 2006
American
American General General Home
Mysterious Alcoa International Boeing Citigroup Caterpillar DuPont Disney Honeywell
Date Express Electric Motors Depot
portfolio AA Group BA C CAT DD DIS HON
AXP GE GM HD
2 AIG
3
4 Average return 8.88% -0.09% -0.54% 0.72% 0.67% 0.30% 1.79% 0.18% 0.29% -0.23% -0.87% -0.52% 0.29%
5 Beta -0.07 -0.12 0.03 0.02 -0.02 0.15 -0.04 -0.02 -0.09 -0.16 -0.12 -0.03
6 Alpha 0.54% 0.56% 0.49% 0.49% 0.51% 0.42% 0.52% 0.51% 0.54% 0.58% 0.56% 0.51%
7 R-squared 0.0061 0.0399 0.0019 0.0005 0.0015 0.0467 0.0045 0.0010 0.0256 0.0259 0.0237 0.0009
8
9 SML--regressing the average returns on the betas
10 Intercept 0.0050 <-- =INTERCEPT(C4:AF4,C5:AF5)
11 Slope 0.0838 <-- =SLOPE(C4:AF4,C5:AF5)
12 R-squared 1.0000 <-- =RSQ(C4:AF4,C5:AF5)

Note also that even though the R2 of the second pass regression is 100%
(since the “mysterious portfolio” is efficient), the R2’s of the individual first-
pass regressions are far from notable.

11.5 So What’s the Real Market Portfolio? How Can We Test the CAPM?

A little reflection will reveal that although the “mysterious” portfolio of the
previous section may be efficient with respect to the 30 stocks of the Dow-
Jones, it could not be the true market portfolio, even if the DJ30 stocks rep-
resented the whole universe of risky securities. This is because many of the
stocks appear in the “mysterious portfolio” with negative weights. Surely a
minimal characteristic of the market portfolio must be that all shares appear
in it with positive proportions.
Roll (1977, 1978) suggests that the only test of the CAPM is to answer the
question: Is the true market portfolio mean-variance efficient? If the answer
to this question is “yes,” then it follows from Proposition 3 of Chapter 9 that
a linear relation holds between the mean of each portfolio and its β. In our
example, we can shed some light on this question by building a table of the
asset proportions of portfolios on the efficient frontier.
In the table below we give some evidence that all efficient portfolios for the
DJ30 contain significant short positions. Using the wonders of Excel’s Data
Table, we compute the largest short and long positions for a series of efficient
portfolios, each defined by its own constant c. All of these portfolios contain
large short positions (and, as you can see, also large long positions):
286 Chapter 11

A B C D E F G H I
Data table: computing the largest short and
105 An efficient portfolio long position for a given constant c
Largest Largest
106 Constant 0.30% short long
107 Constant c <-- Data table hidden: =B141
108 AA 5.5% 0.00% -32.64% 52.33%
109 AIG -11.8% 0.05% -35.51% 53.58%
110 AXP -5.8% 0.10% -38.87% 55.05%
111 BA -13.9% 0.15% -42.86% 56.79%
112 C -36.6% 0.20% -47.69% 59.70%
113 CAT 76.3% 0.25% -53.65% 67.01%
114 DD -22.6% 0.30% -61.19% 76.26%
115 DIS -17.0% 0.35% -71.01% 88.32%
116 GE -8.8% 0.40% -84.36% 104.71%
117 GM -37.7% 0.45% -103.56% 128.28%
118 HD -37.2% 0.50% -133.51% 165.05%
119 HON -17.4% 0.55% -186.77% 230.42%
120 HPQ 39.8% 0.60% -307.86% 379.08%
121 IBM -26.4% 0.65% -853.66% 1049.09%
122 INTC -18.6% 0.70% -1398.93% 1140.50%
123 JNJ 65.1% 0.75% -422.59% 345.18%
124 JPM 53.6% 0.80% -249.90% 204.50%
125 KO -13.0%
126 MCD -12.2%
127 MMM -2.1%
128 MP 42.1%
129 MRK 8.3%
130 MSFT 3.6%
131 PFE -61.2%
132 PG 54.7%
133 T -8.4%
134 UTX 44.1%
135 VZ -36.6%
136 WMT 64.8%
137 XOM 29.4%
138 Sum 100.0%
139
140 Largest short -61.2% <-- =MIN(B108:B137)
141 Largest long 76.3% <-- =MAX(B108:B137)

Our depressing conclusion: If the data for the DJ30 and the S&P 500 are
representative, the CAPM as a descriptive theory of capital markets appears
not to work.7

11.6 Using Excess Returns

Perhaps we should have conducted our experiment on the CAPM in terms of

excess returns—the difference between the stocks’ monthly returns and the
risk-free rates? In this section we perform this variation on the experiment and
show that it does little to improve our analysis.

7. All is not lost! In Chapter 13 we examine the Black-Litterman model, which is a more positivist
approach to portfolio choice.
 287 Estimating Betas and the Security Market Line

Below we show the same Dow-Jones data, with an additional column

appended for Treasury bill returns; these varied wildly over the period:

A B C D E F G H I J K L M N O
EXCESS RETURN DATA FOR THE DOW-JONES INDUSTRIAL STOCKS AND THE STANDARD AND POORS 500
Monthly returns minus monthly Treasury bill return
1 July 2001 - July 2006
Treasury American
S&P 500 American General General
bill return Alcoa International Boeing Citigroup Caterpillar DuPont Disney Home Honeywell
Date Index Express Electric Motors
risk-free AA Group BA C CAT DD DIS Depot HD HON
^GSPC AXP GE GM
2 rate AIG
3
4 Average return -0.22% -0.38% -0.83% 0.43% 0.37% 0.01% 1.50% -0.11% 0.00% -0.53% -1.16% -0.81% 0.00%
5 Beta 1.00 1.90 0.99 1.38 1.15 1.30 1.39 1.00 1.28 0.84 1.41 1.55 1.66
6 Alpha 0 0.04% -0.61% 0.73% 0.63% 0.29% 1.81% 0.11% 0.28% -0.34% -0.86% -0.47% 0.36%
7 R-squared 1 0.6085 0.3518 0.7052 0.2487 0.5972 0.5158 0.4362 0.3845 0.3221 0.2607 0.5288 0.5473
8
9 01-Aug-01 0.29% -6.92% -2.78% -6.49% -10.49% -13.38% -9.17% -9.97% -3.88% -3.87% -6.45% -14.49% -9.40% 1.28%
10 04-Sep-01 0.28% -8.82% -20.95% -0.54% -22.89% -42.72% -12.60% -11.30% -9.06% -31.44% -9.32% -24.66% -18.33% -34.76%
11 01-Oct-01 0.22% 1.50% 4.17% 0.47% 1.26% -2.99% 11.40% 0.30% 6.08% -0.46% -2.44% -4.06% -0.65% 10.97%
12 01-Nov-01 0.18% 6.96% 17.60% 4.44%
Monthly
10.89%
Treasury
7.60%
Bill5.14%
Rates, Aug2001-
5.56%
July2006
10.85% 9.32% 5.29% 19.36% 19.72% 11.80%
13 03-Dec-01 0.16% 0.46% -8.52% -3.96% 7.81% 9.68% 4.96% 9.39% -4.52% 2.00% 4.16% -2.54% 8.65% 1.74%
14 02-Jan-02 0.14% -1.86% 0.45%
0.54% -7.13% 0.37% 5.14% -6.26% -3.39% 3.54% 1.35% -7.87% 4.79% -2.11% -0.92%
15 01-Feb-02 0.14% -2.39% 4.85% -0.47% 1.39% 11.68% -4.93% 9.59% 6.36% 8.51% 3.74% 4.26% -0.48% 12.85%
0.40%
16 01-Mar-02 0.14% 3.32% 0.17% -2.81% 11.37% 4.56% 8.73% 2.09% 0.37% 0.07% -3.18% 12.91% -3.02% 0.11%
17 01-Apr-02 0.15% -6.63% -10.64%
0.35% -4.56% 0.03% -8.14% -13.70% -3.67% -6.07% 0.11% -17.32% 5.65% -5.00% -4.55%
18 01-May-02 0.14% -1.20% 2.91% -3.45% 3.29% -4.39% -0.18% -4.70% 3.81% -1.47% -1.58% -2.72% -10.94% 6.84%
19 03-Jun-02 0.14% -7.81% 0.30%
-5.67% 1.63% -16.02% 5.07% -11.10% -6.85% -3.86% -19.55% -6.64% -15.37% -12.83% -10.96%
20 01-Jul-02 0.14% -8.52% -20.04%
0.25% -6.82% -3.04% -8.36% -14.72% -8.56% -6.04% -6.67% 10.00% -14.12% -17.65% -8.81%
21 01-Aug-02 0.14% 0.19% -7.82% -2.05% 1.97% -11.16% 4.83% -2.69% -3.36% -12.53% -6.86% 3.63% 6.16% -7.45%
22 03-Sep-02 0.14% -11.95% 0.20%
-26.52% -14.03% -14.84% -8.55% -10.25% -16.22% -11.40% -3.81% -19.74% -21.01% -23.41% -32.66%
23 01-Oct-02 0.14% 8.00% 13.04% 13.11% 15.33% -14.04% 22.23% 9.92% 13.12% 9.47% 2.10% -16.00% 9.83% 9.71%
0.15%
24 01-Nov-02 0.13% 5.26% 15.03% 3.78% 6.52% 13.76% 4.80% 19.76% 8.38% 16.87% 6.88% 18.89% -9.27% 8.80%
25 02-Dec-02 0.10% -6.52% -11.76%
0.10% -12.10% -9.71% -3.46% -10.27% -9.04% -5.38% -18.59% -10.31% -7.70% -9.51% -8.34%
26 02-Jan-03 0.10% -3.07% -14.44% -6.96% 0.21% -4.64% -2.15% -3.44% -11.59% 6.77% -5.42% -1.74% -14.19% 1.50%
27 03-Feb-03 0.10% -2.01% 0.05%
4.08% -9.65% -5.92% -13.40% -3.27% 6.35% -2.57% -2.85% 4.38% -6.21% 11.20% -6.01%
28 03-Mar-03 0.10% 0.54% -5.87%
0.00% 0.14% -1.35% -9.79% 2.98% 4.26% 5.51% -0.53% 5.55% -0.75% 3.77% -7.21%
29 01-Apr-03 0.09% 7.50% 17.16% 15.56% 12.98% 8.20% 12.76% 7.04% 8.74% 8.95% 14.11% 6.71% 14.09% 9.69%
Aug-01
Oct-01
Dec-01
Feb-02
Apr-02
Jun-02
Aug-02
Oct-02
Dec-02
Feb-03
Apr-03
Jun-03
Aug-03
Oct-03
Dec-03
Feb-04
Apr-04
Jun-04
Aug-04
Oct-04
Dec-04
Feb-05
Apr-05
Jun-05
Aug-05
Oct-05
Dec-05
Mar-06
May-06
Jul-06
30 01-May-03 0.09% 4.67% 6.77% -0.42% 9.27% 11.98% 4.61% -1.14% -0.40% 4.87% -2.87% -0.92% 14.14% 10.89%
31 02-Jun-03 0.09% 0.83% 3.27% -4.98% 0.07% 10.95% 3.97% 6.22% -1.50% 0.23% 0.31% 1.57% 1.81% 2.17%
32 01-Jul-03 0.08% 1.32% 8.24% 14.85% 5.45% -3.85% 5.05% 19.55% 5.10% 10.09% -1.16% 3.59% -6.29% 4.91%
33 01-Aug-03 0.08% 1.48% 3.09% -7.78% 1.68% 12.36% -3.60% 5.95% 2.31% -6.94% 3.62% 10.41% 2.73% 2.84%
34 02-Sep-03 0.08% -1.49% -9.09% -3.36% -0.29% -8.84% 4.56% -4.54% -11.47% -1.96% 1.12% -0.71% -1.03% -9.82%

Running the second-pass regression shows only minor changes from the
results of section 11.2:

A B C D E F G H
1 THE SECOND-PASS REGRESSION FOR EXCESS RETURNS
Average
monthly
Stock Beta Alpha
excess
2 return
3 Alcoa AA -0.38% 1.9028 0.0004 Second-pass regression, regressing monthly returns on Beta
4 American International Group AIG -0.83% 0.9936 -0.0061 Intercept 0.0007 <-- =INTERCEPT(B3:B32,C3:C32)
5 American Express AXP 0.43% 1.3784 0.0073 Slope -0.0020 <-- =SLOPE(B3:B32,C3:C32)
6 Boeing BA 0.37% 1.1515 0.0063 R-squared 0.0238 <-- =RSQ(B3:B32,C3:C32)
7 Citigroup C 0.01% 1.2952 0.0029
8 Caterpillar CAT 1.50% 1.3903 0.0181 t-statistic, intercept 0.2439 <-- =tintercept(B3:B32,C3:C32)
9 DuPont DD -0.11% 1.0009 0.0011 t-statistic, slope -0.8254 <-- =tslope(B3:B32,C3:C32)
10 Disney DIS 0.00% 1.2805 0.0028
11 General Electric GE -0.53% 0.8420 -0.0034
12 General Motors GM -1.16% 1.4060 -0.0086
288 Chapter 11

11.7 Summary: Does the CAPM Have Any Uses?

Is the game lost? Do we have to give up on the CAPM? Not totally:

• First of all, it could be that the mean returns are approximately described
by their regression on a market portfolio. In this alternative description of the
CAPM, we claim (with some justification) that the β of an asset (which mea-
sures the dependence of the asset’s returns on the market returns) is an impor-
tant measure of the asset’s risk.
• Second, the CAPM might be a good normative description of how to
choose portfolios. As we showed in the appendix of Chapter 3, larger diversi-
fied portfolios are quite well described by their betas, so that the average
beta of a well-diversified portfolio may be a reasonable description of the
portfolio’s risk.

Exercises

1. In a well-known paper, Roll (1978) discusses tests of the SML in a four-asset context:

Variance-covariance matrix Returns

0.10 0.02 0.04 0.05 0.06
0.02 0.20 0.04 0.01 0.07
0.04 0.04 0.40 0.10 0.08
0.05 0.01 0.10 0.60 0.09

a. Derive two efficient portfolios in this 4-asset model and draw a graph of the efficient
frontier.
b. Show that the following four portfolios are efficient by proving that each is a convex
combination of the two portfolios you derived in part a above:

Security 1 0.59600 0.40700 -0.04400 -0.49600

Security 2 0.27621 0.31909 0.42140 0.52395
Security 3 0.07695 0.13992 0.29017 0.44076
Security 4 0.05083 0.13399 0.33242 0.53129

c. Suppose that the market portfolio is composed of equal proportions of each asset (i.e.,
the market portfolio has proportions (0.25,0.25,0.25,0.25)). Calculate the resulting
SML. Is the portfolio (0.25,0.25,0.25,0.25) efficient?
d. Repeat this exercise, but substitute one of the four portfolios of part b above as the
candidate for the market portfolio.
 289 Estimating Betas and the Security Market Line

The remaining questions relate to a data set for 10 stocks. The data are given on the exercise
file with this chapter.

A B C D E F G H I J K L
PRICE DATA: 10 STOCKS AND S&P 500, Jul2007 - Jul2012
1 S&P 500 represented by Vanguard's Index 500 fund (includes dividends)
2 1 2 3 4 5 6 7 8 9 10 11
Whole Johnson- General Hewlett Goldman
3 Apple Google Foods Seagate Comcast Merck Johnson Electric Packard Sachs S&P 500
4
5 Date AAPL GOOG WFM STX CMCSA MRK JNJ GE HPQ GS S&P 500
6 2-Jul-07 131.76 510.00 35.75 21.18 24.17 39.78 51.39 31.84 43.69 178.93 125.27
7 1-Aug-07 138.48 515.25 42.72 23.37 24.00 40.20 52.84 31.93 46.85 167.21 121.40
8 4-Sep-07 153.47 567.27 47.26 23.15 22.25 41.73 56.18 34.24 47.34 205.91 123.22
9 1-Oct-07 189.95 707.00 47.98 25.29 19.37 47.04 55.73 34.04 49.14 235.90 127.81
10 1-Nov-07 182.22 693.00 41.66 23.43 18.90 47.93 58.29 31.67 48.64 215.65 129.83
11 3-Dec-07 198.08 691.48 39.52 23.16 16.80 47.23 57.39 30.92 48.07 204.62 124.39
12 2-Jan-08 135.36 564.30 38.41 18.50 16.71 37.47 54.33 29.49 41.63 190.21 123.53
13 1-Feb-08 125.02 471.18 34.23 19.69 17.98 36.00 53.67 27.89 45.49 161.69 116.10
14 3-Mar-08 143.50 440.47 32.11 19.11 17.85 31.11 56.19 31.15 43.56 157.65 112.33

2. Fill in the template file below.

A B C D E F G H I J K L
1 RETURN DATA: 10 STOCKS AND SP500
2 1 2 3 4 5 6 7 8 9 10 11
Whole Johnson- General Hewlett Goldman
3 Apple Google Foods Seagate Comcast Merck Johnson Electric Packard Sachs S&P 500
Monthly
4 statistics
5 Mean
6 Variance
7 Sigma
8
Annual
9 statistics
10 Mean
11 Variance
12 Sigma
13
14 Regressing individual assets on the S&P 500
15 Alpha
16 Beta
17 Rsq
18 T-test, intercept
19 T-test, slope
20
21
22 RETURN DATA
23 Date AAPL GOOG WFM STX CMCSA MRK JNJ GE HPQ GS SP500
24 1-Aug-07
25 4-Sep-07
26 1-Oct-07
27 1-Nov-07
28 3-Dec-07
29 2-Jan-08
30 1-Feb-08
31 3-Mar-08
32 1-Apr-08
33 1-May-08
34 2-Jun-08
35 1-Jul-08
 290 Chapter 11

3. Perform the second-pass regression: Regress the monthly average returns on the betas of
the assets. Does this confirm that the S&P 500 is efficient?

4. Compute the variance-covariance matrix for the 10 stocks. Using the monthly average
returns and a monthly risk-free interest rate of 0.20%, compute an efficient portfolio.
Here’s the template:

A B C D E F G H I J K L M
1 COMPUTING AN EFFICIENT PORTFOLIO OF THE 10 STOCKS
2
3 Variance-covariance matrix
Average
4 AAPL GOOG WFM STX CMCSA MRK JNJ GE HPQ GS returns
5 AAPL
6 GOOG
7 WFM
8 STX
9 CMCSA
10 MRK
11 JNJ
12 GE
13 HPQ
14 GS
15
16
17 Risk-free 0.20%
18
Efficient
19 portfolio
20 AAPL
21 GOOG
22 WFM
23 STX
24 CMCSA
25 MRK
26 JNJ
27 GE
28 HPQ
29 GS

5. Using the efficient portfolio instead of the S&P 500:

a. Compute the monthly returns on the efficient portfolio.

b. Regress the average monthly returns of the stocks on their betas with respect to the
efficient portfolio.
c. Explain your results in light of Propositions 3 and 4 from Chapter 9.