Question

REALIZED RATES OF RETURN

NEED TO BE DONE USING EXCEL!!!!

Stocks A and B have the following historical returns:

Year	Stock A's Returns, rA	Stock B's Returns, rB
2011	- 24.00%	- 12.10%
2012	20.50	23.70
2013	17.75	31.90
2014	- 5.25	- 11.70
2015	25.75	2.95

1. Calculate the average rate of return for stock A during the period 2011 through 2015. Round your answer to two decimal places.
   %
   
   Calculate the average rate of return for stock B during the period 2011 through 2015. Round your answer to two decimal places.
   %
2. Assume that someone held a portfolio consisting of 50% of Stock A and 50% of Stock B. What would the realized rate of return on the portfolio have been each year? Round your answers to two decimal places. Enter a negative answer with a minus sign.

   Year	Portfolio
   2011	%
   2012
   2013
   2014
   2015

   
   
   What would the average return on the portfolio have been during this period? Round your answer to two decimal places.
   %
3. Calculate the standard deviation of returns for each stock and for the portfolio. Round your answers to two decimal places.

   	Stock A	Stock B	Portfolio
   Standard Deviation	%	%	%

4. Calculate the coefficient of variation for each stock and for the portfolio. Round your answers to two decimal places.

   	Stock A	Stock B	Portfolio
   CV

5. Assuming you are a risk-averse investor, would you prefer to hold Stock A, Stock B, or the portfolio?

NEED TO BE DONE USING EXCEL!!!!

Answer 1

In all cases, I have attached the excel sheet with 2 images, one showing final value obtained and second showing formulas used

a.

A B Year Stock A's Return (%) Stock B's Return (%) 2011 -24.00% -12.10% 2012 20.50% 23.70% 2013 17.75% 31.90% 2014 -5.25% -11.70% 2015 25.75% 2.95% AVERAGE 6.95% 6.95%

1 в 2011 2012 2013 2014 2015 AVERAGE Year Stock A's Return (%) Stock B's Return (%) -0.24 -0.121 0.205 0.237 0.1775 0.319 -0.0525 -0.117 0.2575 0.0295 =AVERAGE(C3:07) -AVERAGE(D3:07)

Average return of stock A = 6.95%

Average return of stock B = 6.95%

b

AB D Year 2011 2012 2013 2014 2015 AVERAGE Stock A's Return (%) -24.00% 20.50% 17.75% -5.25% 25.75% 6.95% Stock B's Return (%) Portfolio's Return (%) -12.10% -18.05% 23.70% 22.10% 31.90% 24.83% -11.70% -8.48% 2.95% 14.35% 6.95% 6.95% Weight of Stock A in Portfolio Weight of Stock B in Portfolio 50.00% 50.00%

AB Year 2011 2012 2013 Stock A's Return (%) Stock B's Return (%) Portfolio's Return (%) -0.24 -0.121 =($C$11*C3)+($D$11*D3) 0.205 0.237 =($C$11*04)+($D$11*D4) 0.1775 0.319 =($C$11*C5)+($D$11*D5) -0.0525 -0.117 =($C$11*C6)+($D$11*D6) 0.2575 0.0295 =($C$11*C7)+($D$11*D7) =AVERAGE(C3:07) =AVERAGE(D3:07) -AVERAGE(E3:E7) 2014 2015 AVERAGE Weight of Stock A in Portfolio Weight of Stock B in Portfolio 0.5 0.5 12

Individual returns for each year has been shown in the calculations.

Average return on the portfolio during this period = 6.95%

c.

Variance is calculated by taking the differences between each number in the data set and the mean, then squaring the differences and finally dividing the "sum of the squares" by the number of values in the data set.

Standard deviation is calculated as the square root of the variance.

B F Year 2011 2012 2013 2014 2015 Stock A's Return (%) -24.00% 20.50% 17.75% -5.25% 25.75% 6.95% Variance of Stock A 0.09579025 0.01836025 0.011664 0.014884 0.035344 0.0352085 Stock B's Return (%) Variance of Stock B Portfolio's Return (%) -12.10% 0.03629025 23.70% 0.02805625 31.90% 0.06225025 -11.70% 0.03478225 2.95% 0.0016 6.95% 0.0325958 -18.05% 22.10% 24.83% -8.48% 14.35% 6.95% Variance of Portfolio 0.0625 0.02295225 0.031951563 0.023793063 0.005476 0.029334575 Weight of Stock A in Portfolio 50.00% Weight of Stock B in Portfolio 50.00% Variance of Stock A Standard deviation of Stock A 0.0352085 18.8% Variance of Stock B Standard deviation of Stock B 0.0325958 18.1% Variance of Portfolio Standard deviation of Portfolio 0.029334575 17.1%

A B D F H Year 2011 2012 2013 2014 2015 Stock A's Return (%) Variance of Stock A Stock B's Return (%) Variance of Stock B Portfolio's Return (%) -0.24 =(C3-$C$8)^2 -0.121 =(E3-$E$8)^2 =($C$11*C3)+($E$11*E3) 0.205 =(C4-$C$8)^2 0.237 =(E4-$E$8)^2 =($C$11*04)+($E$11*E4) 0.1775 =(C5-$C$8)^2 0.319 =(E5-$E$8)^2 =($C$11*C5)+($E$11*E5) -0.0525 = (C6-$C$8)^2 -0.117 =(E6-$E$8)^2 =($C$11*C6)+($E$11*E6) 0.2575 =(C7-$C$8)^2 0.0295 =(E7-$E$8)^2 =($C$11*C7)+($E$11*E7) =AVERAGE(C3:07) =SUM(D3:07)/5 =AVERAGE(E3:E7) =SUM(F3:F7)/5 =AVERAGE(G3:47) Variance of Portfolio =(G3-$G$8)^2 =(G4-$G$8)^2 =(G5-$G$8)^2 =(G6-$G$8)^2 =(G7-$G$8)^2 =SUM(H3:H7)/5 Weight of Stock A in Portfolio 0.5 Weight of Stock B in Portfolio 0.5 Variance of Stock A =SUM(D3:07)/5 Standard deviation of Stock A =SQRT(D13) Variance of Stock B Standard deviation of Stock B =SUM(F3:F7)/5 =SQRT(F13) Variance of Portfolio =SUM(H3:H7)/5 Standard deviation of Portfolio =SQRT(H13)

D.

The coefficient of variation (CV) is defined as the ratio of the standard deviation to the mean.

CV for Stock A = 18.8 / 6.95 = 2.7

CV for Stock B = 18.1 / 6.95 = 2.6

CV for Portfolio = 17.1 / 6.95 = 2.46

Standard deviation of Stock A Mean of Stock A CV of Stock A 18.8% Standard deviation of Stock B 6.95% Mean of Stock B 2.70 CV of Stock B 18.1% Standard deviation of Portfolio 6.95% Mean of Portfolio 2.60 CV of Portfolio 17.1% 6.95% 2.46

E

Since, the Portfolio has the lowest Standard Deviation (Variability) of 17.1% and offers the same expected return of 6.95% as the other stocks, a risk averse investor should hold the Portfolio