Question

The following​ table

Year   A Returns   B Returns
2005   -4.7%   17.7%
2006   1.4%   -8.1%
2007   -31.4%   -25.4%
2008   -11.3%   -3.6%
2009   31.4%   10.4%
2010   26.7%   9.2%
2011   22.6%   5.4%
2012   51.5%   42.6%
2013   35.7%   41.5%
2014   29.3%   39.4%
2015   26.2%   12.1%
2016   5.5%   -0.2%
2017   43.3%   26.2%

contains annual returns for the stocks of Company Upper A ​(Upper A​) and Company Upper B ​(Upper B​).

The returns are calculated using​ end-of-year prices​ (adjusted for dividends and stock​ splits). Use the information for Company Upper A ​(Upper A​) and Company Upper B ​(Upper B​) to create an Excel spreadsheet that calculates the average returns over the​ 10-year period for portfolios comprised of Upper A and Upper B

using the​ following, respective,​ weightings: (1.0,​ 0.0), (0.9,​ 0.1), (0.8,​ 0.2), (0.7,​ 0.3), (0.6,​ 0.4), (0.5,​ 0.5), (0.4,​ 0.6), (0.3,​ 0.7), (0.2,​ 0.8), (0.1,​ 0.9), and​ (0.0, 1.0). The average annual returns over the​ 10-year period for Upper A and Upper B are 17.40​% and 12.86​% respectively.​ Also, calculate the portfolio standard deviation over the​ 10-year period associated with each portfolio composition. The standard deviation over the​ 10-year period for Company Upper A and Company Upper B and their correlation coefficient are 23.78​%, 20.47​%, and 0.82148 respectively. ​(Hint​: Review Table​)

Enter the average return and standard deviation for a portfolio with​ 100% Company Upper A and​ 0% Company Upper B in the table below. (round to 2 decimal places)

Portfolio Average return_______% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 90% Company Upper A and​ 10% Company Upper B in the table below.  ​(Round to two decimal​ places)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 80% Company Upper A and​ 20% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 70% Company Upper A and​ 30% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 60% Company Upper A and​ 40% Company Upper B in the table below. ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 50% Company Upper A and​ 50% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 60% Company Upper A and​ 50% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 70% Company Upper A and​ 50% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 80% Company Upper A and​ 50% Company Upper B in the table below.  ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 90% Company Upper A and​ 50% Company Upper B in the table below. ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Enter the average return and standard deviation for a portfolio with​ 100% Company Upper A and​ 50% Company Upper B in the table below. ​(Round to two decimal​ places.)

Portfolio Average return________% Portfolio Standard Deviation_________%

Answer 1

Expected return of two-asset portfolio R_p = w₁R₁ + w₂R_2,

where R_p = expected return

w₁ = weight of Asset 1

R₁ = expected return of Asset 1

w₂ = weight of Asset 2

R₂ = expected return of Asset 2

Standard deviation for a two-asset portfolio σ_p = (w₁²σ₁² + w₂²σ₂² + 2w₁w₂Cov_1,2)^0.5

where σ_p = standard deviation of the portfolio

w₁ = weight of Asset 1

w₂ = weight of Asset 2

σ₁² = variance of Asset 1

σ₂² = variance of Asset 2

Cov_1,2 = covariance of returns between Asset 1 and Asset 2

Cov_1,2 = ρ_1,2 * σ₁ * σ_2, where ρ_1,2 = correlation of returns between Asset 1 and Asset 2

B17 fax =CORREL(B2:B14,C2:C14) _ А 1 Year | 2 2005 3 2006 4 2007 5 2008 6 2009 7 2010 8 2011 9 2012 2013 11 2014 12 2015 13 2016 14 2017 15 average 16 Std. Dev. 17 Correlation A Returns B Returns -4.70% 17.70% 1.40% -8.10% -31.40% -25.40% -11.30% -3.60% 31.40% 10.40% 26.70% 9.20% 22.60% 5.40% 51.50% 42.60% 35.70% 41.50% 29.30% 39.40% 26.20% 12.10% 5.50% -0.20% 43.30% 26.20% 17.40% 12.86% 23.78% 20.48% 0.82291

-0.113 0.054 1 Year 2 2005 3 2006 4 2007 5 2008 6 2009 7 2010 8 2011 9 2012 10 2013 11 2014 12 2015 13 2016 14 2017 15 average 16 Std. Dev. 17 Correlation A Returns B Returns B Returns -0.047 0.177 0.014 -0.081 -0.314 -0.254 -0.036 0.314 0.104 0.267 0.092 0.226 0.426 0.357 0.293 0.262 0.121 0.055 -0.002 0.433 10.262 -AVERAGE(B2:B14) =AVERAGE(C2:C14) =STDEV(B2:B14) =STDEV(C2:C14) =CORREL(B2:B14,C2:C14) 0.515 0.415 0.394 18

D24 ， B J& =(A24*2)*($$16*2)+(B24*2)*($c$16*2)+(2*A24*B24*$$17*$$16*$c$16))A0.5 D E F G H 12.86% 20.48% 15 aver average Std. Dev. 17 Correlation 16 17.40% 23.78% 0.82291 21 Weight - A 0.0 0,7 བཟོn a - ༉ མཉེན་ Portfolio Portfolio Standard Weight-B Return deviation 1.0 17.40% 23.78% 0.9 0.1 16.95% 23.12 0.8 0.2 16.49%L 22.52%I 0.3 16.04% 21.98% 0.6 0.4 15.58% 21.52% 15.13% 21.13 14.68% 20.83% 0.7 14.22% 20.61 0.8 13,7720.47% 0.3 13.32% 20.43 1.0 12.86% 20.48% 0.5 0.3

А 15 average -AVERAGE(B2:B14) 16 Std. Dev. -STDEV(B2:B14) 17 Correlation =CORREL(B2:B14,02:C14) 18 -AVERAGE(C2:C14) =STDEV(C2:C14) 20 Weight - B 21 Weight - A 22 1 23 0.9 0.1 Portfolio Return Portfolio Standard deviation =(A22*$B$15)+(B22*$C$15) =((A22^2)*($B$16^2)+(B2212)*($C$16^2)+(2*A22*B22*$B$17*$B$16*$C$16))^0.5 =(A23*$B$15)+(B23*$C$15) =((A23^2)*($B$16^2)+(B23^2)*($C$16^2)+(2*A23*B23*$B$17*$B$16*$C$16))^0.5 =(A24*$B$15)+(B24*$C$15) =((A24^2) *($B$16^2)+(B24^2)*($C$16^2)+(2*A24*B24*$B$17*$B$16*$C$16))^0.5 =(A25*$B$15)+(B25*$C$15) =((A25^2) *($B$16^2)+(B25^2) *($C$16^2)+(2*A25*B25*$B$17*$B$16*$C$16))^0.5 =(A26*$B$15)+(B26*$C$15) =((A26^2)*($B$16^2)+(B26^2)*($C$16^2)+(2*A26*B26*$B$17*$B$16*$C$16))^0.5 =(A27*$B$15)+(B27*$C$15) =((A2742)*($B$16^2)+(B2712)*($C$16^2)+(2*A27*B27*$B$17*$B$16*$C$16))^0.5 =(A28*$B$15)+(B28*$C$15) =((A28^2)*($B$16^2)+(B28^2) *($C$16^2)+(2*A28*B28*$B$17*$B$16*$C$16))^0.5 =(A29*$B$15)+(B29*$C$15) =((A29^2) *($B$16^2)+(B2942)*($C$16^2)+(2*A29*B29*$B$17*$B$16*$C$16))^0.5 =(A30*$B$15)+(B30*$C$15) =((A30^2)*($B$16^2)+(B3042)*($C$16^2)+(2*A30*B30"$B$17"$B$16"$C$16))^0.5 =(A31*$B$15)+(B31*$C$15) =((A31^2)*($B$16^2)+(B31^2)*($C$16^2)+(2*A31*B31*$B$17*$B$16*$C$16))^0.5 =(A32*$B$15)+(B32*$C$15) =((A3242)*($B$16^2)+(B3242)*($C$16^2)+(2*A32*B32*$B$17*$B$16*$C$16))^0.5