e. Let us now compute an envelope portfolio with constant c = 4%, where the vector z solves the system of simultaneous linear equations E(r) - c= Sz. Then, this solution produces a portfolio x and y on the envelope of the feasible set. Write down and explain the Excel formula used to calculate the values in Cells A20, F20, F24 (portfolio weights and sum of portfolio weights), F26:F28 (mean, variance and standard deviation), and B30:B31 (covariance, correlation), in the Excel spreadsheet below: H B C D E CALCULATING THE FRONTIER Variance-covariance, s 1 ET) Mean minus returns constan 2 Er) t 3 0.10 0.01 0.03 0.05 6% 2.00% 4 0.01 0.30 0.06 -0.04 896 4.00% 5 0.03 0.06 0.40 0.02 10% 6.00% 6 0.05 -0.04 0.02 0.50 15% 11.00% 7 8 Constant, 4.00% 9 10 Computing an envelope portfolio with constant - 0 Envelope Z portfolio 11 X 12 0.3861 0.3553 13 0.2567 0.2362 14 0.1688 0.1553 15 0.2752 0.2532 16 Sum 1.0000 17 18 Computing an envelope portfolio with constant = 4.00% Envelope Z portfolio 19 y 20 0.0404 ? 0.0782 ? 21 0.1386 0.2684 22 0.1151 0.2227 23 0.2224 0.4307 24 Sum 1.0000 ? 25 26 E(X) 9.37% EV 11.30% ? 27 Var(x) 0.0862 Vary) 0.1414 ? 28 Sigmax) 29.37% Sigma 37.60% ? 29 30 Covix. 0.1040 ? 31 Comix 0.9419 ? 32 ? e. Let us now compute an envelope portfolio with constant c = 4%, where the vector z solves the system of simultaneous linear equations E(r) - c= Sz. Then, this solution produces a portfolio x and y on the envelope of the feasible set. Write down and explain the Excel formula used to calculate the values in Cells A20, F20, F24 (portfolio weights and sum of portfolio weights), F26:F28 (mean, variance and standard deviation), and B30:B31 (covariance, correlation), in the Excel spreadsheet below: H B C D E CALCULATING THE FRONTIER Variance-covariance, s 1 ET) Mean minus returns constan 2 Er) t 3 0.10 0.01 0.03 0.05 6% 2.00% 4 0.01 0.30 0.06 -0.04 896 4.00% 5 0.03 0.06 0.40 0.02 10% 6.00% 6 0.05 -0.04 0.02 0.50 15% 11.00% 7 8 Constant, 4.00% 9 10 Computing an envelope portfolio with constant - 0 Envelope Z portfolio 11 X 12 0.3861 0.3553 13 0.2567 0.2362 14 0.1688 0.1553 15 0.2752 0.2532 16 Sum 1.0000 17 18 Computing an envelope portfolio with constant = 4.00% Envelope Z portfolio 19 y 20 0.0404 ? 0.0782 ? 21 0.1386 0.2684 22 0.1151 0.2227 23 0.2224 0.4307 24 Sum 1.0000 ? 25 26 E(X) 9.37% EV 11.30% ? 27 Var(x) 0.0862 Vary) 0.1414 ? 28 Sigmax) 29.37% Sigma 37.60% ? 29 30 Covix. 0.1040 ? 31 Comix 0.9419 ? 32
