For this assignment, please use excel file group_assignment_1_portfolios.xls posted on blackboard under the folder of Excel Files. The file contains the monthly returns of 4 stocks over the 10 year period -- January 1997 -- December 2006. In this file, the expected monthly return for each stock is calculated using excel function AVERAGE (), for each stock, the variance of monthly returns is calculated using Excel function VAR (), and the covariance between the returns of each pair of stocks is calculated using Excel function COVAR ().

Assume that the yearly risk free rate is 2% (A monthly risk free rate of 0.001652).

(a) Plot the minimum variance frontier for an investor who wants to allocate his money to PG, BAC, and the risk-free asset. Find the optimal risky portfolio. What are the mean and s.d. of the returns of this portfolio?

AutoSave OFF group assignment_1_portfolios.xls - Compatibility Mode Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Insert Arial V 10 . Ar = = ab Wrap Text General V WE 48- 0 Ou 3 DX Delete v Paste BIU Av E E Merge & Center $ % ) .00 .00 0 Ideas Conditional Format Formatting as Table Cell Styles Sensitivity Format v Sort & Filter Find & Select Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates E198 fx F G K L M N o 2 R S T U V w H J Expected Monthly Return Expected Monthly Return PG 0.010848 Microsoft 0.014854 BAC 0.011589 Exxon 0.012043 PG Microsoft BAC Exxon Variance Variance 0.004478 0.012820 0.005611 0.002820 Covariance Cov(PG, Microsoft) Cov(PG, BAC) Cov(PG, Exxon) Cov(Microsoft, BAC) Cov(Microsoft, Exxon) Cov(BAC, Exxon) -0.000649 0.000683 0.000433 0.001681 0.000804 0.000757 1 Date 2 3 4 5 6 7 8 9 10 11 12. 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 A B D E PG Microsoft BAC Exxon 2/3/97 0.039059 -0.04394 0.109771 -0.02517 3/3/97 -0.04485 -0.05994 -0.07011 0.074816 4/1/97 0.099732 0.325186 0.088252 0.051471 5/1/97 0.096787 0.02085 -0.0247 0.053613 6/2/97 0.024472 0.018853 0.102423 0.033628 7/1/97 0.080347 0.118736 0.102398 0.048801 8/1/97 -0.12496 -0.06478 -0.1604 -0.04122 9/2/97 0.037519 0.000737 0.041554 0.046828 10/1/97 -0.01181 -0.01767 -0.03368 -0.04067 11/3/97 0.120612 0.088456 0.00429 -0.00042 12/1/97 0.047318 -0.08678 0.018687 0.002969 1/2/98 -0.01495 0.1546 -0.0152 -0.03087 2/2/98 0.08301 0.135859 0.143693 0.082024 3/2/98 -0.00596 0.056354 0.070731 0.060887 4/1/98 -0.0228 0.007077 0.042156 0.080578 5/1/98 0.020264 -0.05892 -0.00667 -0.02955 6/1/98 0.085766 0.277427 0.020991 0.012323 7/1/98 -0.12555 0.014388 0.039885 -0.01575 8/3/98 -0.03613 -0.12722 -0.27916 -0.06293 9/1/98 -0.07037 0.147283 -0.06308 0.079193 10/1/98 0.251503 -0.03807 0.074941 0.014029 11/2/98 -0.01215 0.152324 0.133442 0.05321 12/1/98 0.042346 0.136981 -0.07112 -0.02492 1/4/99 -0.00165 0.261679 0.111743 -0.03938 2/1/99 -0.01512 -0.14226 -0.02327 -0.04711 3/1/99 0.094361 0.194093 0.088614 0.06 4/1/99 -0.03929 -0.03929 -0.09269 0.013567 0.177287 5/3/99 -0.00478 -0.00779 -0.09629 -0.03357 6/1/99 -0.0563 0.117754 0.141424 -0.03442 7/1/99 0.031383 -0.04862 -0.0946 0.029164 8/2/99 0.095943 0.078932 -0.0883 -0.00126 9/1/99 -0.05528 -0.02184 -0.07252 -0.03657 10/1/99 0.122319 0.02233 0.156916 -0.02552 11/1/99 0.029724 -0.01632 -0.09074 0.077569 12/1/99 0.014433 0.282236 -0.13617 0.015581 1/3/00 -0.07498 -0.16169 -0.0349 0.028843 2/1/00 -0.13062 -0.08686 -0.0505 -0.08619 3/1/00 -0.35411 0.18888 0.152988 0.035574 4/3/00 0.057826 -0.34365 -0.06549 -0.00378 5/1/00 0.113029 -0.10304 0.141377 0.07814 6/1/00 -0.1466 0.278816 -0.22424 -0.05781 7/3/00 0.010385 -0.12728 0.101858 0.021489 8/1/00 0.084797 0 0.141786 0.02378 9/1/00 0.083695 -0.13608 -0.02243 0.091423 10/2/00 0.071038 0.141761 -0.08226 0.000819 11/1/00 0.048299 -0.16696 -0.15732 -0.00845 12/1/00 0.047696 -0.24374 0.149059 -0.0121 GA1 + A i + 100% AutoSave OFF group assignment_1_portfolios.xls - Compatibility Mode Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Insert Arial V 10 . Ar = = ab Wrap Text General V WE 48- 0 Ou 3 DX Delete v Paste BIU Av E E Merge & Center $ % ) .00 .00 0 Ideas Conditional Format Formatting as Table Cell Styles Sensitivity Format v Sort & Filter Find & Select Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates E198 fx F G K L M N o 2 R S T U V w H J Expected Monthly Return Expected Monthly Return PG 0.010848 Microsoft 0.014854 BAC 0.011589 Exxon 0.012043 PG Microsoft BAC Exxon Variance Variance 0.004478 0.012820 0.005611 0.002820 Covariance Cov(PG, Microsoft) Cov(PG, BAC) Cov(PG, Exxon) Cov(Microsoft, BAC) Cov(Microsoft, Exxon) Cov(BAC, Exxon) -0.000649 0.000683 0.000433 0.001681 0.000804 0.000757 1 Date 2 3 4 5 6 7 8 9 10 11 12. 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 A B D E PG Microsoft BAC Exxon 2/3/97 0.039059 -0.04394 0.109771 -0.02517 3/3/97 -0.04485 -0.05994 -0.07011 0.074816 4/1/97 0.099732 0.325186 0.088252 0.051471 5/1/97 0.096787 0.02085 -0.0247 0.053613 6/2/97 0.024472 0.018853 0.102423 0.033628 7/1/97 0.080347 0.118736 0.102398 0.048801 8/1/97 -0.12496 -0.06478 -0.1604 -0.04122 9/2/97 0.037519 0.000737 0.041554 0.046828 10/1/97 -0.01181 -0.01767 -0.03368 -0.04067 11/3/97 0.120612 0.088456 0.00429 -0.00042 12/1/97 0.047318 -0.08678 0.018687 0.002969 1/2/98 -0.01495 0.1546 -0.0152 -0.03087 2/2/98 0.08301 0.135859 0.143693 0.082024 3/2/98 -0.00596 0.056354 0.070731 0.060887 4/1/98 -0.0228 0.007077 0.042156 0.080578 5/1/98 0.020264 -0.05892 -0.00667 -0.02955 6/1/98 0.085766 0.277427 0.020991 0.012323 7/1/98 -0.12555 0.014388 0.039885 -0.01575 8/3/98 -0.03613 -0.12722 -0.27916 -0.06293 9/1/98 -0.07037 0.147283 -0.06308 0.079193 10/1/98 0.251503 -0.03807 0.074941 0.014029 11/2/98 -0.01215 0.152324 0.133442 0.05321 12/1/98 0.042346 0.136981 -0.07112 -0.02492 1/4/99 -0.00165 0.261679 0.111743 -0.03938 2/1/99 -0.01512 -0.14226 -0.02327 -0.04711 3/1/99 0.094361 0.194093 0.088614 0.06 4/1/99 -0.03929 -0.03929 -0.09269 0.013567 0.177287 5/3/99 -0.00478 -0.00779 -0.09629 -0.03357 6/1/99 -0.0563 0.117754 0.141424 -0.03442 7/1/99 0.031383 -0.04862 -0.0946 0.029164 8/2/99 0.095943 0.078932 -0.0883 -0.00126 9/1/99 -0.05528 -0.02184 -0.07252 -0.03657 10/1/99 0.122319 0.02233 0.156916 -0.02552 11/1/99 0.029724 -0.01632 -0.09074 0.077569 12/1/99 0.014433 0.282236 -0.13617 0.015581 1/3/00 -0.07498 -0.16169 -0.0349 0.028843 2/1/00 -0.13062 -0.08686 -0.0505 -0.08619 3/1/00 -0.35411 0.18888 0.152988 0.035574 4/3/00 0.057826 -0.34365 -0.06549 -0.00378 5/1/00 0.113029 -0.10304 0.141377 0.07814 6/1/00 -0.1466 0.278816 -0.22424 -0.05781 7/3/00 0.010385 -0.12728 0.101858 0.021489 8/1/00 0.084797 0 0.141786 0.02378 9/1/00 0.083695 -0.13608 -0.02243 0.091423 10/2/00 0.071038 0.141761 -0.08226 0.000819 11/1/00 0.048299 -0.16696 -0.15732 -0.00845 12/1/00 0.047696 -0.24374 0.149059 -0.0121 GA1 + A i + 100%