Excel worksheet for calculating returns, standard
deviation and correlation of the given stocks:-
Formula Sheet of the above calculations:-
Formulas for calculating the expected return and
standard deviation of the portfolio:-
Calculating the expected return and standard deviation
of the given portfolio:-
Book1 - Excel nitin arora - 0 X File Home Insert Page Layout Formulas Data Review View Help ? Tell me what you want to do & Share A1 x ✓ fix C A B D E F G H I I J K L M N O P Q R 0 1 3 Year Rio Tinto Returns 2010 48.51 2011 69.48 43.23% 2012 60.46 -12.98% 2013 56.47 -6.60% 2014 53.15 -5.88% 2015 44.13 -16.97% 2016 24.65 -44.14% 2017 44.79 81.70% 2018 56.11 25.27% Avg. Return 7.95% Std. deviation 39.93% Year Amazon Returns 2010 125.41 2011 169.64 35.27% 2012 194.44 14.62% 2013 264.27 35.91% 2014 358.69 35.73% 2015 354.53 -1.16% 2016 587 65.57% 2017 823.48 40.29% 2018 1450.89 76.19% Avg. Return 37.80% Std. deviation 24.84% Correlation 0.118759 Sheet1 Ready 0 0 -- - + 100% + 02:12 19-03-2020 O Type here to search o A 9 ? ^ . 1» (la 4 ENG 6
Book1 - Excel nitin arora - o X File Home Insert Page Layout Formulas Data Review View Help ? Tell me what you want to do & Share A1 X ✓ fix А В G H I Returns Returns 1 2 3 Year 4. 2010 5 2011 6 2012 7 2013 8 2014 9 2015 10 2016 11 2017 12 2018 Rio Tinto 48.51 69.48 60.46 56.47 53.15 44.13 24.65 44.79 56.11 Avg. Return Std. deviation =B5/B4-1 =B6/B5-1 =B7/B6-1 =B8/B7-1 =B9/B8-1 =B10/B9-1 =B11/B10-1 =B12/B11-1 =AVERAGE(C5:012) ESTDEVA(C5:C12) Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 Amazon 125.41 169.64 194.44 264.27 358.69 354.53 587 823.48 1450.89 Avg. Return Std. deviation =F5/F4-1 =F6/F5-1 =F7/F6-1 =F8/F7-1 =F9/F8-1 =F10/F9-1 =F11/F10-1 =F12/F11-1 =AVERAGE(G5:G12) ESTDEVA(G5:612) Correlation =CORREL(C5:C12,65: Sheet1 0 0 -- - + 100% Ready 1 + 02:13 19-03-2020 0 Type here to search lo ? ^ . 1» (la 4 ENG 6
- 09 10 _ Expected Return= WARA + B RB where WA = weight of stock a WB = weight of shock B RA= Retum on stock A RB = Return on stock B 102] _Standard deviation = J was con + cua că + 2x Pas X WAXWB ход хов where Wa= weight of Stock A wg weight of stock B OA = sid. deviation of stock a a B = Std. deviation of stock B Pag z correlation coefficient blaueen Choice Af ShekB cs Scanned with NotesamScanner
27 28 29 30 31 Using Data from 10) _above, Excel worksheet & formulas 11_Ans 3: Expected Return = 0.80 x 0.378 + 0.20% 0.0795 121 2.0.3183 or 31.83° (where weight of Amazon: 80% Return of Amazon = 37.80% weight of Rio Tinto = 20%. Return of Rio Tinto a 7.95% Standard deviakona (0.8)^(0.248)2 + (0.2)*(0.399)? + 2x 0.118 x 0.8x0.2 x 0.248 x 0.399 E 0.0494 = 0.2224 Notes 22.24% where std. deviction of Rio tinto = 39.93% Std. deviation of Amazon z 24.84% Scanned with correlation coefficient = 0:1187 --Eartscanner