What's new

Chapter 5 MPT/CAPM Spreadsheet

finhoe

New Member
Subscriber
Thread starter #1
1597885455987.png
Under the MPYT/CAPM notes, there are several illustrated examples. Is there a workbook resource that is available for me to see how these tables are actually generated.

Im having trouble figuring out how to generate the eqtn for covariance(port, market)
 

Nicole Seaman

Chief Admin Officer
Staff member
Subscriber
#2
View attachment 2834
Under the MPYT/CAPM notes, there are several illustrated examples. Is there a workbook resource that is available for me to see how these tables are actually generated.

Im having trouble figuring out how to generate the eqtn for covariance(port, market)
Hello @finhoe

The spreadsheets are available in our Professional study packages. We do offer upgrades if you would like access to the spreadsheets. The cost to upgrade is the difference in price between the study package that you purchased and the Professional study package. You can email me at [email protected] if you would like to upgrade. I would be happy to help.

Nicole
 

David Harper CFA FRM

David Harper CFA FRM
Staff member
Subscriber
#3
Hi @finhoe Here is that page from our XLS: https://www.dropbox.com/s/g1m993l6jyvusj6/081920-capm-sml.xlsx?dl=0

Please note the Cov(Port, Market) employs covariance property (see https://en.wikipedia.org/wiki/Covariance): cov(aX, bY) = ac*cov(X,W) + ad*cov(X,V) + bc*cov(Y,W) + bd*cov(Y,V). In this case,

Port = w_pa*A + w_pb*B, where w_pa is weight of A in portfolio, and
Market = w_ma*A + w_mb*B, which w_ma is weight of A in market portfolio (which is actually the solution, in another page, that dynamically solves for the A/B mix with the highest Sharpe ratio, so these are hard-coded in the linked XLS but they are solved in my XLS)

Then cov(w_pa*A + w_pb*B, w_ma*A + w_mb*B)
= w_pa*w_ma*cov(A,A) + w_pa*w_mb*cov(A,B) + w_pb*w_ma*cov(B,A) + w_pb*w_mb*cov(B,B)
= w_pa*w_ma*variance(A) + w_pa*w_mb*cov(A,B) + w_pb*w_ma*cov(B,A) + w_pb*w_mb*variance(B)
=w_pa*w_ma*variance(A) + (w_pa*w_mb + w_pb*w_ma)*cov(B,A) + w_pb*w_mb*variance(B) ... is the covariance(Port, Market) in my XLS. I hope that's helpful!
 
Top