$1$ Diversification Classics $(100$ points$)$

The file Project $2.$xls in the Projects folder contains monthly returns to Amazon $($AMZN$)$

and Wells Fargo $($WFC$)$ in $2005-2009,$ as well as the excess market return $($MKT$-$RF$)$ and

the risk$-$free rate $($RF$)$ in the same time period. Use these data to answer the questions

below:

i$.$ Compute the average, the standard deviation, and the correlation for Amazon and

Wells Fargo. $(10$ points$)$

ii$.$ If you can only invest in the risk$-$free asset and either Amazon or Wells Fargo, which

one do you pick? $(5$ points$)$

iii. If you can only invest in the risk$-$free asset and either Amazon or Wells Fargo and

your target standard deviation is $25\%$ per annum, what will be your best possible

expected monthly return? What weight does the risk$-$free asset take in your portfolio?

$(10$ points$)$

iv$.$ Compute the average return, standard deviation, and the Sharpe ratio of the portfolio

that invests $70\%$ in Amazon and $30\%$ in Wells Fargo $(5$ points$)$

v$.$ Does the existence of Amazon and WFC violate the CAPM? $($Hint: You will need to

use the data on the market excess returns. You may assume that the risk$-$free rate

is constant$).(5$ points$)$

vi$.$ Redo $($iv$)$ assuming that the correlation between Amazon and Wells Fargo is $0\mathrm{.}7.$

What does it tell you about the benefits of diversification? $(5$ points$)$

vii. What combination of Amazon and Wells Fargo creates the minimum variance port$-$

folio $($MVP$)?$ What are the average return, standard deviation and the Sharpe ratio

of this portfolio? $($Hint: use the Solver add$-$in$)(10$ points$)$

viii. What combination of Amazon and Wells Fargo creates the mean$-$variance efficient

portfolio $($MVE$)?$ What are the average return, standard deviation and the Sharpe

ratio of this portfolio? $($Hint: use the Solver add$-$in$)(10$ points$)$

ix$.$ Bonus question: Estimate the market model for Amazon and the market model for

Wells Fargo. Explain why the market betas of Amazon and Wells Fargo seem to

contradict the correlation between Amazon and Wells Fargo. Now explain how the

market betas and the correlation may in fact be consistent with each other. Also,

use the market model to see if Amazon and Wells Fargo violate the CAPM by a

statistically significant amount $(20$ points$)$

x$.$ Assume that Amazon and Wells Fargo are the only two stocks in the economy.

Under the CAPM, what are the average return and standard deviation of the market

portfolio? What are the average return and standard deviation of the zero$-$beta

portfolio? $($Hint: Use your solution to $($viii$)$ to answer this question$)(10$ points$)$

xi$.$ Redo $($iii$)$ assuming that now you can invest in Amazon, Wells Fargo, and the risk$-$

free asset simultaneously. What are the weights of Amazon, Wells Fargo, and the

risk$-$free asset in the portfolio that delivers the best possible return for the target

standard deviation of $25\%$ per annum? $(10$ points$)$

xii. Assume that Amazon and Wells Fargo are the only two stocks in the economy and

there is no risk$-$free asset. For each of the following three points $-(1\mathrm{.}4\%,9\mathrm{.}23\%),$

$(2\mathrm{.}03\%,9\mathrm{.}3\%),(3\mathrm{.}1\%,16\%)-$ answer two questions: $($a$)$ Will you want to choose it$?$

$($b$)$ Can you have it$?$ The first number of the pair is always expected return. $($Hint

$1$: use Goal Seek. Hint $2$: do not forget comparing the point with MVP$).(15$ points$)$

xiii. How would your answer to $($xii$)$ change if the risk$-$free asset is available? $(5$ points$)$

Date AMZN WFC MKT$-$RF RF

$03\mathrm{.}01\mathrm{.}2005-18,6025-2,344352,110,16$

$01\mathrm{.}02\mathrm{.}2005-2,58670,669028-1,90,21$

$01\mathrm{.}03\mathrm{.}2005-5,573390,234558-2,730,21$

$01\mathrm{.}04\mathrm{.}20059,734241,5990643,550,24$

$02\mathrm{.}05\mathrm{.}2005-6,814981,9577740,920,23$

$01\mathrm{.}06\mathrm{.}200536,44606-0,414164,090,24$

$01\mathrm{.}07\mathrm{.}2005-5,42636-1,96597-0,890,3$

$01\mathrm{.}08\mathrm{.}20056,088993-1,774010,770,29$

$01\mathrm{.}09\mathrm{.}2005-12,00882,787593-2,350,27$

$03\mathrm{.}10\mathrm{.}200521,575515,3475943,730,31$

$01\mathrm{.}11\mathrm{.}2005-2,70326-0,036260,030,32$

$01\mathrm{.}12\mathrm{.}2005-4,94168-0,76173,660,35$

$03\mathrm{.}01\mathrm{.}2006-16,46593,80117-0,50,34$

$01\mathrm{.}02\mathrm{.}2006-2,43056-0,492961,540,37$

$01\mathrm{.}03\mathrm{.}2006-3,613477,5371550,940,36$

$03\mathrm{.}04\mathrm{.}2006-1,70406-2,63244-3,530,43$

$01\mathrm{.}05\mathrm{.}200611,759611,081446-0,440,4$

$01\mathrm{.}06\mathrm{.}2006-30,48097,82347-0,590,4$