Question

Consider the following information about 2 stocks: Year Stock Price Stock Price 2010 136.07 517.56 2011 138.97 525.17 2012 139.57 515.05 2013 142.13 532.76 2014 140.26 540.12 2015 143.89 544.32 2016 144.07 527.05 2017 142.67 557.43 2018 145.46 558.09 Calculate: -expected return and standard deviation for stock A; -expected return and standard deviation for stock B; -correlation, covariance, and beta between stocks A and B; -portfolio return and standard deviation, assuming 40% is invested in stock A

Answer 1

-0.22 Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 40% 60% Stock A Stock B 136.07 517.56 138.97 525.17 139.57 515.05 142.13 532.76 140.26 540.12 143.89 544.32 144.07 527.05 142.67 557.43 145.46 558.09 Expected Return Standard deviation Portfolio return Portfolio Standard deviation Return Stock A Return Stock B Correlation Covariance Beta 2.11% 1.46% -0.117 -0.0048% 0.43% -1.95% 1.82% 3.38% -1.32% 1.37% 2.56% 0.77% 0.13% -3.22% -0.98% 5.60% 1.94% 0.12% 0.94% 2.79% 0.83% 1.48% 0.90% 1.71%

Formulae

Ε H B C D F 1547 0.4 =1-C1547 1548 Year Stock A Stock B Return Stock A Return Stock B Correlation Covariance Beta 1549 2010 136.07 517.56 =LN(C1550/C1549) 1=LN(D1550/D1549) =CORREL(E1549:E1556,F1549:F1556) =COVARIANCE.S(E1549:E1556,F1549:F1556) =SLOPE(F1549:F1556,E1549:E1556) 1550 =B1549+1 138.97 525.17 =LN(C1551/C1550) 1=LN(D1551/D1550) 1551 =B1550+1 139.57 515.05 =LN(C1552/C1551) =LN(D1552/D1551) 1552 =B1551+1 142.13 532.76 =LN(C1553/C1552) 1=LN(D1553/D1552) 1553 =B1552+1 140.26 540.12 =LN(C1554/C1553) ELN(D1554/D1553) 1554 =B1553+1 143.89 544.32 =LN(C1555/C1554) =LN(D1555/D1554) 1555 =B1554+1 144.07 527.05 =LN(C1556/C1555) 1=LN(D1556/D1555) 1556 =B1555+1 142.67 557.43 =LN(C1557/C1556) ELN(D1557/D1556) 1557 =B1556+1145.46 558.09 1558 Expected Return =AVERAGE(E1549:E1556) =AVERAGE(F1549:F1556) 1559 Standard deviation =STDEV.S(E1549:E1556) =STDEV.S(F1549:F1556) 1560 Portfolio return =E1558*C1547+F1558*D1547 1561 tfolio Standard deviat] =(E1559426C154742+F155912*0154742+2*C1547*D1547*H1549)^0.5