Question

Historical Returns: Expected and Required Rates of Return

You have observed the following returns over time:

Year	Stock X	Stock Y	Market
2009	16%	14%	13%
2010	19	6	9
2011	-15	-2	-13
2012	2	1	1
2013	23	11	18

Assume that the risk-free rate is 3% and the market risk premium is 6%. Do not round intermediate calculations.

1. What is the beta of Stock X? Round your answer to two decimal places.
   
   
2. What is the beta of Stock Y? Round your answer to two decimal places.
   
   
3. What is the required rate of return on Stock X? Round your answer to one decimal place.
   %
   
4. What is the required rate of return on Stock Y? Round your answer to one decimal place.
   %
   
5. What is the required rate of return on a portfolio consisting of 80% of Stock X and 20% of Stock Y? Round your answer to one decimal place.

Answer 1

The formula for beta is covariance of the return of an asset with the return of the benchmark index divided by the variance of the return of the benchmark over a certain period

We can calculate beta directly in excel using the slope function

Year	Stock X	Stock Y	Market
2009	16%	14%	13%
2010	19%	6%	9%
2011	-15%	-2%	-13%
2012	2%	1%	1%
2013	23%	11%	18%
Rf	3%
Rm-Rf	6%
a)	1.256811989	<--SLOPE(C5:C9,E5:E9)
b)	0.498978202	<--SLOPE(D5:D9,E5:E9)
c)	CAPM	Rf+Beta*(Rm-Rf)
		10.54%
d)	CAPM	Rf+Beta*(Rm-Rf)
		5.99%
e)	9.63%	<--80% X & 20% Y

The above table gives the case facts along with the answers

Please reach out for any clarifications