Name: <your name> Leave all answers to 2-decimal places.
Group: <your group number> Show work: you answer cells should have the embedded algebra or Excel function.
The following data applies to all questions in this assignment:
Rate of Return
Year Asset A Asset B Market
1 20.00% 19.00% 10.00%
2 -10.00% 15.00% 12.00%
3 10.00% -5.00% 8.00%
4 -8.00% -13.00% -5.00%
5 15.00% 25.00% 10.00%
2015 Spring2
1) Calculate the expected returns for Asset A, Asset B and the Market (use AVERAGE function in Excel).
Asset A =
Asset B =
Market =
2) Calculate the standard deviation of returns, ?, for each asset (use STDEVP function).
Asset A =
Asset B =
Market =
3) Calculate the coefficient of correlation, ?, between Asset A and Asset B (use CORREL function).
?A,B =
4) Using the following asset weight combinations between Asset A and B [0%-100%, 25%-75%, etc.] calculate the expected returns and their corresponding portfolio standard deviation of the portfolio with these weight combos.
Use the following formula to calculate the portfolio standard deviation:
= [(wA*?A)^2 + (wB*?B)^2 + (2 *wA * wB *?A * ?B *?(A,B))] ^0.5
where wA and wB are the % of assets in Asset A and B respectively [i.e. A-B weight combo],
?A and ?B are the respective standard deviations of return [calculated from (2)] and
?(A,B) is the [coefficient of] correlation of returns between asset A and B
[calculated from (3)].
Portfolio Portfolio
%Asset A %Asset B Expected Return Standard Deviation
0% 100%
25% 75%
50% 50%
75% 25%
100% 0%
5) Using Portfolio Expected Returns on the Y axis and Portfolio Standard Deviation in the X axis, draw the efficient frontier for possible portfolio combinations of Asset A and B [from previous question]. (include 100% A and 100% B as two possibilities).
Hint: Use the Excel Chart Wizard and select the XY (scatter) plot option.
Yes, I am helping you auto-plot the Efficient Frontier below once you calculate the Return-Stdev in Question4.
When you calculate and fill in the portfolio’s Expected Returns and Standard Deviations in (4), it will automatically plot below. It’s probably a good idea to learn how to do it yourself, but for now it will save you some time …
6) Calculate Beta for Asset A (relative to the Market) and Asset B (relative to the Market). (use the SLOPE function).
[PS: this function is also available on your TI BA II Plus calculator, and Excel facility may or may not be available (it is semester- and instructor-specific) during the Exam].
Beta Asset A =
Beta Asset B =
7) Assume that for next year the Risk Free Rate is expected to be 1% and that the overall Market will realize a return of 6%. Using the CAPM / SML methodology, calculate the required returns for Asset A and Asset B.
CAPM: ki = kRF + ?i (kM – kRF)
kRF = <risk-free rate>
kM = <Market return>
Required Return for Asset A =
Required Return for Asset B =
