In: Finance
Market Return Aggressive Stock Defensive Stock
4% -5% 6%
20% 30% 15%
Please show in excel with formulas. thank you!
a) Beta can be found by finding the slope of Market returns and the stocks

| Aggressive stock Beta | 2.1875 | 
| Defensive stock Beta | 0.5625 | 
b) Expected return on aggressive stock in % = 0.5*(-5) + 0.5*(30) = 12.5%
Expected return on defensive stock in % = 0.5*(6) + 0.5*(15) = 10.5%
c) Expected return on the market in % = 0.5*(4) + 0.5*(20) = 12%
The required rate of return on aggressive stock R(A) is calculated by CAPM model
R(A) = r(f) + Beta*(R(m) - r(f))
R(m) is the market return =12%
r(f) is the risk-free rate = 5%
Beta of aggressive stock = 2.1875
R(A) = 0.05+2.1875*(0.12-0.05)
R(A) = 0.203125= 20.31%
The required rate of return on defensive stock R(D) is calculated by CAPM model
R(D) = r(f) + Beta*(R(m) - r(f))
R(m) is the market return =12%
r(f) is the risk-free rate = 5%
Beta of defensive stock = 0.5625
R(D) = 0.05+0.5625*(0.12-0.05)
R(D) = 0.089375= 8.94%
Expected return on the market in % = 0.5*(4) + 0.5*(20) = 12%
SML = r(f) + Beta*(R(m) - r(f))
SML = 0.05 + Beta*(0.12 - 0.05)
SML = 0.05 + Beta*0.07
| Beta | SML | 
| 0.1 | 5.70% | 
| 0.2 | 6.40% | 
| 0.3 | 7.10% | 
| 0.4 | 7.80% | 
| 0.5 | 8.50% | 
| 0.6 | 9.20% | 
| 0.7 | 9.90% | 
| 0.8 | 10.60% | 
| 0.9 | 11.30% | 
| 1 | 12.00% | 
| 1.1 | 12.70% | 
| 1.2 | 13.40% | 
| 1.3 | 14.10% | 
| 1.4 | 14.80% | 
| 1.5 | 15.50% | 
| 1.6 | 16.20% | 
| 1.7 | 16.90% | 
| 1.8 | 17.60% | 
| 1.9 | 18.30% | 
| 2 | 19.00% | 
| 2.1 | 19.70% | 
| 2.2 | 20.40% | 
| 2.3 | 21.10% | 
| 2.4 | 21.80% | 
| 2.5 | 22.50% | 
| 2.6 | 23.20% | 
| 2.7 | 23.90% | 
| 2.8 | 24.60% | 
| 2.9 | 25.30% | 
| 3 | 26.00% | 
| 3.1 | 26.70% | 
| 3.2 | 27.40% | 
| 3.3 | 28.10% | 
| 3.4 | 28.80% | 
| 3.5 | 29.50% | 
| 3.6 | 30.20% | 
| 3.7 | 30.90% | 
| 3.8 | 31.60% | 
| 3.9 | 32.30% | 
| 4 | 33.00% | 
