Chapter 5 MPT/CAPM Spreadsheet
- Thread starter finhoe
- Start date
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
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!
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!
Similar threads
