Question

answer using excel please (all calcultions should be rounded one decimal place)

You work for a small investment management firm. You have been provided with the following historical information for three stocks and the market index. The information is shown in the table below. Stock AAA Inc. Dividend Price 2012 2013 2014 2015 2016 2017 2018 $45 $50 S55 $60 $52 $62 $80 $1.40 $1.40 $1.70 $1.85 $1.85 $1.90 BBB Inc. Stock Dividend Price $130 $140 $1.50 $118 $1.55 $135 $1.65 $160 $1.75 $170 $1.90 $140 $2.00 CCC Inc. Stock Dividend Price $200 $185 $3.50 $195 $3,60 $210 $3.65 $205 $3.65 $225 $3.75 $240 $3.90 Market Index 30,840 33,320 32,220 35,325 34,500 36,100 37,500 Using the data provided you are to calculate the following for each of the stocks and the market index. a) What is the average annual return for the past six years? b) What is the geometric average annual return (effective annual rate of return) for the past six years? c) What is the population standard deviation and the sample standard deviation? d) What is the correlation coefficient between Stocks AAA and BBB? Between Stocks AAA and CCC? Between Stocks BBB and CCC? e) What is the sample covariance between Stocks AAA and BBB, between Stocks AAA and CCC and between stocks BBB and CCC? f) What is the coefficient of variation for each of the stocks and the market index? You should use the sample standard deviation g) What is the beta of each of the stocks? You can use Excel's "slope" function to estimate this h) Assuming that the beta of stock AAA is 0.80, the beta of stock BBB is 1.2 and the beta of stock CCC is 1.6. What is the beta of a portfolio where the weight in stock AAA is 20% the weight in stock BBB is 50% and the weight in stock CCC is 30%. i) Assuming the risk free rate is 3% and the expected return on the market is 7% what is the expected return on the portfolio in part (8) above?

Answer 1

Вв ССС AAА Return Return Return Return Stock Stock Stock Market Year on Year on Year on Year on Dividend Dividend Dividend Index Price Year Price Year Price Year Year 01-01-2012 45 45 130 130 200 200 30840 188.5 -0.0575 01-01-2013 50 1.4 51.4 0.142222 140 1.5 141.5 0.088462 185 3.5 33320 0.080415 01-01-2014 55 1.4 56.4 0.097276 118 1.55 119.55 -0.15512 190 3.6 193.6 0.027056 32220 -0.03301 01-01-2015 60 1.7 61.7 0.093972 135 1.65 136.65 0.143036 210 3.65 213.65 0.103564 35325 0.096369 01-01-2016 1.85 -0.0234 52 53.85 -0.12723 160 1.75 161.75 0.183681 205 3.65 208.65 34500 -0.02335 01-01-2017 1.85 170 62 63.85 0.185701 1.9 171.9 0.062751 225 3.75 228.75 0.096334 36100 0.046377 81.9 0.282694 0.06623 01-01-2018 80 1.9 140 2 142 -0.17394 240 3.9 243.9 37500 0.038781 a) Annual average return 11.2439% 2.481% 3.5380% 3.4262% b) Effective Annual rate of return 10.496% 1.482% 3.3626% 3.3125% c) Population Standard Deviation Sample Standard Deviation 10.96808 16.68386 18.28359 2131.954 11.84689 18.02063 19.74853 2302.773 Between AAA and BBB Between BBB and CCC Between CCC and AAA d) Correlation Coefficient e) Sample Covariance 0.201071 0.47017 0.875502 42.92637 167.3247 204.8312 f) Coefficient of variation 20.026% 12.572% 9.3592% 6.7219% g) Beta 1.193808 0.913798 0.340905

h) Beta of stock AAA = 0.8 and its weight in the portfolio = 0.2

Beta of stock BBB = 1.2 and its weight in the portfolio = 0.5

  Beta of stock CCC = 1.6 and its weight in the portfolio = 0.3

Beta of the portfolio = weighted average of individual betas

= 0.8*0.2 + 1.2*0.5 + 0.3*1.6 = 1.24

i) Risk free rate = 3%

Expected market return = 7%

Expected return from stock AAA = risk free rate + beta * ( expected market return - risk free rate)

= 0.03 + 1.19*(0.07-0.03) = 0.0776

Expected return from stock BBB = risk free rate + beta * ( expected market return - risk free rate)

= 0.03 + 0.91*(0.07-0.03) = 0.0664

Expected return from stock CCC = risk free rate + beta * ( expected market return - risk free rate)

= 0.03 + 0.34*(0.07-0.03) = 0.0436

Expected return from portfolio  = risk free rate + beta * ( expected market return - risk free rate)

= 0.03 + 1.24*(0.07-0.03) = 0.0796