What's new

P1.T1.Elton_Ch13.xlxm - CAPM,SML

Thread starter #1

Under the SML tab in the provided excel sheet for CAPM I could not understand how Covariance (Port, Market) is calculated. Could anyone please help me in understanding the formulae used?

Got the spreadsheet you are referring to, but have to admit that I can't see through what David is doing here on the spot. Why should there be 4 different weights? He computes that 56.8% weight in the market portfolio maximises the Sharpe Ratio, that's ok even if I did not replicate it myself now.

T1.9-Market Portfolio needs some explanation. Why should we minimise the Sharpe Measure as it says in F15?

Under T1.9-Market Portfolio cell H14 it would be easier to reference to cell G14 for the second weight instead of (1- the first weight). Anyway, this is a question of taste.

This is unfortunately one of these spreadsheets which is apparently causing some serious confusion and needs considerable time to entangle certain (usually straightforward) concepts.

It is similar to example 63.3 in David's notes with factor sensitivities - look at the formula there which makes sense, but in the spreadsheet (T1.9 SML) I can't follow the right-hand side of the covariance formula to be honest.

What puzzles me are the 4 different weights. I am afraid this needs some very detailed explanation. I can't find anything similar neither in Elton's book nor in Copeland's 'Financial Theory'.

Anyway, thanks for coming up with this! Really appreciate that you asked this question.
Last edited:

David Harper CFA FRM

David Harper CFA FRM
Staff member
Hi @Manas31 and @emilioalzamora1 I am working on an update to this XLS (I'll post here so you can see a version asap) but some of these calculations are inherently involved; I used to use Formula Names to help, but over the years I've discovered that names create real problems for copied versions (and copying columns examples). But i do agree that better labeling can always help

@emilioalzamora1 It should say "Maximize Sharpe ratio" (my copy does) because we need to find the allocation mix (A versus B) with the highest Sharpe ratio. That's the market portfolio. My updated version actually solves for the maximize sharpe ratio analytically (very cool!) without a need to use solver ( per this derivation https://www.bionicturtle.com/forum/...lio-and-derivative-of-weight.9919/#post-45560)

@Manas31 In regard to covariance(portfolio, market) in this two-asset case, that function applies the covariance property (https://en.wikipedia.org/wiki/Covariance#Properties ) of cov(aX+bY, cW+dV) = a*c*cov(X,W) + a*d*cov(X,V) + b*c*cov(Y,W) + b*d*cov(Y,V); i tend to think of this as covariance's "distributive property."

In this case, the portfolio and the market each have different weights of the same two assets A and B, where we let wp_a = portfolio's weight in asset A and wm_a = market's weight in asset, we have:
cov(p, M) = cov(wp_a*A + wp_b*B, wm_a*A + wm_b*B) = wp_a*wm_a*variance(A) + wp_b*wm_b*variance(B) + (wp_a*wm_b + wp_b*wm_a)*covariance(A,B). I hope that clarifies, I will try to make the update more readable. Thanks!
Last edited:
to cover both forum posts:

Actually this is question is a tricky one and I would like to dig a bit deeper here in order to be crystal-clear. The notation with Asset A and Asset B for the market portfolio and simultaneously being A and B as well for the investors weights is misleading!!

In a sense the most critical part to understand here is the fact that an investor wants to form a portfolio consisting only of the market portfolio (which consits only of two assets in our simplified world!) AND wants to determine the weights how he wants to split his wealth between these two assets which form the so called market (portfolio), call it the S&P500.

Let me explain in detail with a more practical example:

An investor wants to hold a portfolio of two assets; put differently: he wants to hold two assets which comprise the market and he thinks about splitting his wealth in one of the following combinations: e.g. 160% and -60%, 100% and 0% etc.

Then comes the more advanced part here: We are asked to compute the covariance of the investor's portfolio (how much he likes to devote to Asset A and Asset B in the market portolfio) with regard to the market portfolio (which in this simplified case consits of 2 assets; imagine that for simplification the S&P500 consists only of two assets: Exxon Mobile and American Airways.
The weights of these assets are given in % (let's say the % expresses the market cap as a reciprocal of the total market cap of the S&P500). In this case Exxon Mobile accounts for 56,82% and American Airways accounts for 43,18%.

Remember that we two variances (call them X and Y) for Exxon Mobile (Asset A of the market portfolio) and American Airways (Asset B of the market portfolio) are our two random variables.

Variance Exxon Mobile (X): 0,01
Variance American Airways (Y): 0,04

however, we do have four constants (2 weights for the investors portfolio - how he want's to split his wealth - and 2 weights - the so called market caps of Exxon and American Airways in %-terms - these weights add up to 1 and show how much of these two assets contribute to the whole market risk/reward relationship) given as:

a, b, c, d

We can then write the following covariance property:

Cov(aX + bY, cX + dY) >>> which splits up into

a*c*Cov(X,X) + b*d*Cov(Y,Y) + a*d*Cov(X,Y) + b*c*Cov(Y,X)

Remember: The covariance with itself Cov(X,X) and Cov(Y,Y) simplifies to the simple variance (sigma^2) of X and Y!

We write down the following:

a*c*Var(X) + b*d*Var(Y) + (a*d + b*c) * Cov(X,Y)

>>> this right-hand side is a simplification, it can also be written in the extended form given above: a*d*Cov(X,Y) + b*c*Cov(Y,X)

Remember: the covariance is invariant to whether it is written as Cov(X,Y) or Cov(Y,X). The covariance between (Exxon, American Airways) and the covariance between (American Airways, Exxon) yields the same result!

We therefore have the following covariance of the investor portfolio with the market portfolio:

Let's take the 50% wealth devoted to Asset A (Exxon) and 50% devoted to Asset B (American) case where we have:

a (50% wealth devoted to Exxon) = 0,5
b (50% wealth devoted to American) = 0,5

c (% weight of Exxon or call it %-market cap) = 0,5682
d (% weight of American or call it %-market cap)= 0,4318

variance of Exxon (X) = 0,01
variance of American Airways (Y) = 0,04

[Notice: the covariance is already given/computed in the spreadsheet: 0,006]

Following the above equation we compute:

covariance = 0,5*0,5682*0,01 + 0,5*0,4318*0,04 +(0,5*0,4318 + 0,5*0,5682) * 0,006 = 0,01448