Question

show formula for the yellow box

function Formula Auditing Defined Names Fundien Lbrary F41 N M K G D J C B 8 Here are the expected returns and standard deviations for stocks A. B. and C: Erd 7.0% Stock 33.11% 53.8% 10 10.0% 20.0% 11 89.44% c 12 13 14 Here is the correlation matrix: A 15 16 0.1891 0.1661 1.0000 0.15/1 1.0000 1.0000 A 0.1571 17 0.1891 0.1661 C 18 19 20 a. Suppose a portlolio has 30 percent invested in A, 50 percent in B, and 20 percent in 21 C. What are the expected return and standard deviation of the portfolio? 3% 23 w 50% 2% 27 E 28 11.10% 29 30 Hint for the portoflio standard deviation, start by creating a table Ske the one in Section 31 25.1 for the N asset case. In fact, begin by creating a table with the products of the 32 weights and standard deviations for each pair of stocks If you are careful about how 33 you construct the formulas, you can copy them. Then take the resalts from this 34 intermediate table and maltiply them by the comelations above 36 36 31 B A 50% 3% w a9.44% 33.11% 538% 36 Hint put the products of weights and standard devistions for each stock in this row 35 wx 40 w Hint: the values in this box should equal w, KW 33% 41 30% 42 50% 20% 54% 43 C Hist: the values in this box should equal w, x s, xwxxf- 45 Now multiply the products of w, xsx 46sby the corelations ghven above to 47 ceale a lable like the one in Section 3.1 C 48 Hint portfolio variance is the sam of all the valuss in the table immediately above. 49 Portfolio variance 50 Build a Model

show formula for the yellow box

show formula for the red and yellow box

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

A C D E G Н Stock E[ri] 33.11% 10 А 7% 11 B 10% 53.85% 12 C 20% 89.44% 13 correlation matrix 14 15 B C 16 A 1.0000 0.1571 0.1891 0.1661 17 B 0.1571 1.0000 0.1891 0.1661 1,0000 18 C 19 20 21 22 23 wA 30% 24 wB 50% 25 wC 20% 26 27 E[rp] 11.10% 28 29 30 31 32 33 35 36 А B C 50% 37 30% 20% W 38 33.11% 53.85% 89.44% w*a 39 0.09933 0.26925 0.17888 w*a 40 W о 41 A 30% 33.11% 0.09933 0.009866 0.026745 0.017768 50% 53.85% 0.26925 0.026745 42 В 0.072496 0.048163 0.031998 89.44% 0.048163 43 С 20% 0.17888 0.017768 44 0.0033600 0.0079999 45 A 0.0098664 0.0042016 0.0724956 46 B 0.0042016 0.0079999 0.0033600 0.0319981 47 48 0.1455 38.14% Portfolio variance 49 50 op 51 52 53 54

Cell reference -

A C D E G Stock 9 E[ri] a 0.3311 10 A 0.07 11 B 0.1 0.5385 12 C 0.2 0.8944 13 14 correlation matrix: 15 A C 0.1571 0.1891 0.1661 -F17 16 A 1 17 B -F16 1 -G17 -G16 18 C 19 20 21 22 23 WA 24 wB 25 wC 26 0.3 0.5 0.2 27 Elrpl =SUMPRODUCT(B10:B12,B23 : B25) 28 29 30 31 32 33 34 35 -F15 36 -E15 -G15 37 38 TRANSPOSE(B23:825) TRANSPOSE(C10:C12) =TRANSPOSE (B23:825) TRANSPOSE(c10:C12) F37 F38 TRANSPOSE(B23:825) W =TRANSPOSE(C10:C12) -G37 G38 39 w a -E37 E38 TRANSPOSE (D37:G37) 40 D38 D39 =MMULT (D41 : D43,E39:G39)MMULT(D41:D43,E39:G39)MMULT(D41:D43,E39:G39) -MMULT(D41:D43,E39:G39)MMULT(D41:D43, E39:G39) MMULT(D41:D43,E39:G39) -MMULT(D41:D43,E39:G39) -MMULT(D41:D43,E39:G39)-MMULT(D41:D43,E39:G39) 41 TRANSPOSE(E36:G36) TRANSPOSE(D37:G37) 42 -TRANSPOSE(E36:G36) -TRANSPOSE(D37:G37) =TRANSPOSE(E38:G38) 841 C41 -TRANSPOSE(E38:G38) B42*C42 43 -TRANSPOSE(E36:G36) -TRANSPOSE(D37:G37) 44 -TRANSPOSE(E38:G38) -B43 C43 =F16 F41 -F17*F42 45 -E16*E41 -E17 E42 -E18 E43 -G16*G41 A 16 B -G17*G42 -G18*G43 47 -F18 F43 C 48 -SUM(E45:G47) -SQRT(C49) Portfolio variance 49 50 op 51 52 53 54 55 56 57 58 59

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.