Questions are in the attached file. Please include the formulas for my reference.

TEMPLATE Chapter: 25 EXCEL ASSIGNMENT SUMMER 2016 Following is information for the required returns and standard deviations of returns for A, B, and C. Here are the expected returns and standard deviations for stocks A, B, and C: ri si Stock A 7.0% 33.11% B 10.0% 53.85% C 20.0% 89.44% Here is the correlation matrix: A B C A 1.0000 0.1571 0.1891 B 0.1571 1.0000 0.1661 C 0.1891 0.1661 1.0000 a. Suppose a portfolio has the distribution percentages shown below. What are the expected return and standard deviation of the portfolio? wA = wB = wC = 30% 50% 20% rp = Hint: for the portoflio standard deviation, start by creating a table like the one in Section 25.1 for the N-asset case. In fact, begin by creating a table with the products of the weights and standard deviations for each pair of stocks. If you are careful about how you construct the formulas, you can copy them. Then take the results from this intermediate table and multiply them by the correlations above. wi = si = wi x si = A wi si 30% 33.11% w i x si A B C 30% 33.11% 50% 53.85% 20% 89.44% Hint: put the products of weights and standard deviations for each stock in this row. Hint: the values in this box should equal wi x si x wj x sj. B C 50% 20% 53.85% 89.44% Hint: the values in this box should equal wi x si x wj x sj. Now multiply the products of wi x si x wj x sj by the correlations given above to create a table like the one in Section 3.1. Portfolio variance = sp = Hint: the values in this box should equal wi x si x wj x sj x rij. A B C Hint: portfolio variance is the sum of all the values in the table immediately above. b. The partial model lists 66 different combinations of portfolio weights. For each combination of weights, find the required return and standard deviation. Hint: Use the formula to calculate the variance for each portfolio and then copy it down. This formula should have six values in it: 1 for Stock A, 1 for Stock B, 1 for Stock C, one for the crossterm of A and B, 1 for the cross-term of A and C, and 1 for the cross term of B and C. Portoflio # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 wA 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.1 0.2 0.2 0.2 wB 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 0.0 0.1 0.2 wC 1.0 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0.0 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0.0 0.8 0.7 0.6 Variance sp rp 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 0.2 0.2 0.2 0.2 0.2 0.2 0.3 0.3 0.3 0.3 0.3 0.3 0.3 0.3 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.5 0.5 0.5 0.5 0.5 0.5 0.6 0.6 0.6 0.6 0.6 0.7 0.7 0.7 0.7 0.8 0.8 0.8 0.9 0.9 0.3 0.4 0.5 0.6 0.7 0.8 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.0 0.1 0.2 0.3 0.4 0.5 0.0 0.1 0.2 0.3 0.4 0.0 0.1 0.2 0.3 0.0 0.1 0.2 0.0 0.1 0.5 0.4 0.3 0.2 0.1 0.0 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0.0 0.6 0.5 0.4 0.3 0.2 0.1 0.0 0.5 0.4 0.3 0.2 0.1 0.0 0.4 0.3 0.2 0.1 0.0 0.3 0.2 0.1 0.0 0.2 0.1 0.0 0.1 0.0 66 1.0 0.0 0.0 c. The partial model provides a scatter diagram (shown below) showing the required returns and standard deviations calculated above. This provides a visual indicator of the feasible set. What is the standard deviation and expected return of the lowest risk portfolio? Minimum standard deviation Expected return of minimum risk portfolio 1200.00% 1000.00% Portfolio Required Return 800.00% 600.00% 400.00% 200.00% 0.00% 0.00% 200.00% 400.00% 600.00% 800.00% Portfolio Standard Deviation 1000.00% 1200.00% Hint: you could sort the data above by rp and sp