2: Portfolio Optimization In the accompanying data (Portfoliodata.txt) file, you are given monthly returns of four stocks: JCPenney, Amoco, GM and JPMorgan for 240 months during the 20 year period from 1975 to 1994. We are interested in developing optimal portfolios (in Markowitz' sense) using this data. In so doing, you will go through the following steps and present your results in a report. (a) Using EXCEL analyze each of the four stock returns and estimate the mean, variance and standard deviation of the monthly returns. In so doing, identify the stocks with the maximum expected return and minimum variance (or standard deviation). (b) Using EXCEL estimate the correlation between each pair of stocks and identify whether any of the stocks are negatively correlated. (c) Using the results from parts (a) and (b), develop a spreadsheet model where you obtain the covariance for each pair of stocks (as we did in class). (d) Now assume that a particular investor will allocate his/her monthly funds equally between these four stocks. Obtain the monthly portfolio mean and variance (or standard deviation) for this investor. (e) Consider another investor who allocates funds equally between Amoco and JPMorgan (that is, nothing is invested in JCPenney and GM). Compare this portfolio's mean and variance (or standard deviation) with that portfolio in part (d). That is, discuss which one has a higher average return and which one has a higher standard deviation (or variance). (f) Using EXCEL solver find the minimum variance portfolio that guarantees at least an average (mean) monthly return of 0.0125. How are the funds allocated in this portfolio ? Compare the average return and standard deviation of this portfolio with that of the investor in part (d). (g) Find the minimum variance portfolios (separately) with at least an average return of 0.013, an average return of 0.0135, and an average return of 0.014. Compare the means and standard deviations of these portfolios as well as their optimal allocations (including the one in part (f)). What do you observe as you are increasing the minimum average return requirement in the portfolio ? Penney Amoco GM JPMorgan 0.0831 0.0147 0.1041 0.1402 0.0162 0.0046 0.0448 -0.0869 0.082 0.105 0.0645 0.0837 -0.0515 0.0264 0.0125 -0.0125 -0.058 0.0375 -0.018 -0.0169 -0.0095 0.0125 -0.0018 0.0873 -0.0537 0.0272 -0.0037 -0.0677 0.0585 -0.0106 0.007 -0.0641 0.0072 0.0936 0.081 -0.0169 -0.013 -0.0158 0.0068 0.0328 0.0633 -0.0078 0.0068 -0.0408 -0.0366 0.1119 0.0998 0.0733 -0.1602 -0.0629 -0.0494 -0.06 -0.0712 -0.0532 -0.0405 -0.0071 -0.0429 -0.0263 -0.0565 -0.0286 -0.0354 0.0541 0.0243 -0.0371 -0.0973 -0.0135 0.0015 -0.0051 0.0372 0.0287 0.0413 0.0517 0.0142 -0.007 -0.018 0.0174 0.0714 -0.0815 0.007 -0.0415 -0.04 -0.0155 0.0424 -0.0102 -0.0522 0.0026 -0.0478 -0.0883 0.0667 -0.0204 0 0.0256 -0.0139 0.0732 -0.0176 -0.0322 -0.0459 -0.0707 -0.0736 -0.0523 -0.0037 -0.0228 0.0193 -0.0245 0.0712 0.0339 0.0535 0.0799 0.1801 0.123 0.065 0.1239 -0.1078 0.0063 -0.0458 0.0315 -0.0201 -0.0539 -0.0266 -0.0829 0.0771 0.0363 0.0505 0.0786 -0.0289 -0.006 0.014 0.0365 -0.0298 0.0842 0.0141 -0.0317 -0.1143 -0.0588 -0.0733 -0.0341 -0.0273 0.0765 -0.0128 0 -0.0241 0.0659 -0.0272 0 0.068 0.011 0.0488 0.0523 -0.0742 -0.0437 -0.031 -0.0759 -0.0042 0.0972 0.0606 0.0453 0.0192 0.0675 0.0264 0.0495 0.0042 0.0138 0.0176 -0.0497 0.0042 0.0375 0.0281 0.0771 -0.0271 0.0057 -0.0253 0.0596 0.1441 0.0378 0.0544 0.022 -0.0763 0.0571 0.0438 -0.0239 -0.1342 0.0836 -0.116 -0.0769 -0.0146 0.0779 -0.0566 -0.0161 0.0296 -0.0071 -0.1422 0.04 0.1081 0.0505 -0.0289 0.1024 -0.0594 -0.1165 0.0092 -0.0778 0.1325 -0.0336 0.0674 0.2158 0.1198 0.0824 -0.0217 -0.0123 -0.0494 -0.02 -0.0625 0.0044 0.0088 0.0859 0.0408 0.0784 0.0473 -0.0176 0.0753 0.0567 0.0895 0.0733 0.082 0.0561 -0.0652 0.0853 0.0649 0.0925 0.0957 -0.0932 -0.0021 -0.0077 -0.0491 0.0449 0.0502 0.0083 -0.0497 0.1224 0.321 -0.1274 0.02 -0.0086 0.1309 0.038 -0.0106 0.1438 0.1039 0.3271 -0.1755 -0.1487 0.0059 0.0886 -0.2254 -0.0871 0.0655 0.1002 -0.0534 -0.0752 -0.021 0.099 -0.0631 -0.1178 -0.1537 -0.0426 0.1507 0.0554 -0.0745 -0.1207 0.143 0.0157 0.0154 -0.0407 0.0258 0.0157 -0.0167 0.1154 0.1035 -0.0219 0.1135 -0.0316 0.0541 -0.0554 0.0026 0.0066 -0.0123 0.0975 -0.0565 -0.0889 -0.0135 -0.0033 0.0391 0.1263 0.0329 -0.014 -0.0687 -0.0845 0.0141 0.0222 0.1027 0.0549 0.0213 0.0412 -0.0472 -0.0307 -0.0856 -0.0377 -0.1821 0.0301 0.0405 -0.013 -0.0105 0.1081 0.0183 0.0413 0.0408 -0.042 0.1456 -0.0362 0.2172 0.0656 0.0418 0.014 -0.0182 -0.0345 0.1537 -0.0332 0.1086 0.012 -0.0377 0.048 0.0526 -0.0484 0.0262 0.0135 -0.0853 0.0076 0.1088 -0.008 0.0524 -0.0541 -0.0218 -0.0345 0.0498 -0.014 0.1875 -0.0169 0.0837 -0.0677 0.0569 0.0867 0.0435 -0.0879 -0.0023 0.051 0.0244 -0.0238 -0.0315 0.0605 -0.0285 -0.014 0.0535 -0.1126 0.0468 -0.0483 0.132 0.0646 0.1603 -0.0166 0.0251 -0.0167 0.0226 0.1204 0.1048 -0.0723 -0.0578 -0.0087 -0.0123 -0.0046 -0.0799 0.1223 -0.0717 -0.0721 -0.0168 -0.0024 0.0606 -0.0607 0.01 0.0092 0.0025 0.032 0.0256 -0.0682 -0.0631 0.0666 -0.0281 -0.0026 -0.0086 0.1078 0.0024 -0.0322 -0.0127 0 0.0173 0.0946 0.0374 0.0827 0.084 0.0659 0.0345 0.1578 0.0444 -0.0675 -0.0063 -0.11 0.129 0.0528 -0.1216 0.1193 0.1885 0.038 -0.0089 0.0552 0.0219 0.1499 0.0417 -0.072 -0.2436 -0.0402 0.041 0.0419 0.0355 0.0278 0.009 0.029 0.0044 -0.0613 0.0769 0.0526 -0.0333 -0.0808 0.0047 0.1007 0.0719 0.002 0.071 -0.0386 0.0464 -0.0039 0.0305 0.0096 0.0411 -0.0056 -0.0782 -0.0675 0.0198 0.0108 0.0085 0.0943 -0.0568 -0.0299 0.1952 -0.0581 0.0204 0.0382 -0.0387 0.1035 0.0097 0.1096 0.011 0.0443 0.0495 -0.0314 0.0068 -0.034 -0.1363 -0.0699 0.0223 -0.0252 -0.0328 -0.0597 0.0289 -0.0505 0.0502 0.0574 0.0814 0.0493 0.0389 -0.0308 0.0503 0.0606 -0.0286 -0.0818 -0.0736 0.0776 0.0052 -0.0174 -0.03 -0.0056 -0.0037 0.0693 0.0308 0.0515 0.0676 0.1093 -0.0725 0.0125 -0.0267 -0.1224 0.0752 -0.059 0.0498 0.0422 -0.0943 0.1401 0.0083 0.0469 0.152 -0.0389 -0.0308 0.0756 0.0492 -0.0978 -0.2952 -0.015 -0.0387 0.1391 -0.0766 -0.0406 0.0392 -0.0808 -0.0137 -0.006 0.1134 0.0709 0.0327 -0.0083 0.0703 0.1147 0.0371 0.0066 0.0637 0.0547 0.0282 -0.0558 -0.0154 -0.059 0.177 0.0883 0.1358 0.0019 0.1206 0.0988 0.0446 0.0915 -0.0113 -0.0157 0.1012 -0.1419 0.0385 0.0119 -0.0243 0.1303 -0.0161 -0.0525 0.029 -0.0084 0.0958 -0.0313 0.0647 -0.0589 -0.1762 -0.1842 0.0389 -0.0001 0.1337 -0.0026 -0.0051 -0.0209 0.0373 -0.0283 -0.0107 0.0892 0.0868 -0.0161 -0.0515 0.0914 -0.0571 0.0484 0.0404 0.0404 -0.0431 0.0979 0.1304 -0.011 -0.038 0.0369 0.0899 -0.0731 -0.0135 0.0398 -0.0579 0.0294 -0.078 -0.0407 -0.1717 -0.1312 -0.0143 0.0935 0.0086 0.1703 0.0416 0.007 -0.0483 0.1514 -0.0862 -0.0607 0.0534 -0.0145 -0.0067 -0.0275 0.0656 0.0833 0.01 -0.0168 0.0647 -0.0304 -0.0284 0.0653 -0.0177 -0.0199 0.0152 0 0.0135 0.0367 -0.0087 0.0805 0.0486 0.0429 -0.0084 0.051 0.0237 0.0372 -0.0051 0.0175 0.1177 -0.0183 0.0328 -0.0251 -0.0514 0.0519 -0.0331 0.088 -0.0021 0.0318 -0.0286 -0.0316 -0.0095 -0.0501 0.0588 0.0072 0.0238 -0.02 -0.0288 0.0519 0.0033 -0.0378 0.0147 -0.0717 0.0887 0.0713 0.1179 -0.0121 0.0508 0.0183 0.0666 0 -0.0749 0.0292 0.1117 0.0255 -0.0089 0.0943 -0.0493 -0.0278 0 -0.009 0.0341 0.0748 0.1086 -0.0485 -0.0483 0.0056 -0.037 -0.0148 0.0961 0.0223 -0.0354 0.1158 -0.0206 -0.021 -0.1314 -0.0881 0.0138 0.0136 -0.0582 0.0546 0.1007 -0.0443 -0.053 0.2175 -0.0551 -0.0245 -0.034 -0.0164 -0.0602 -0.1132 0.1815 0.0241 0.0101 -0.1033 -0.0602 0.152 0.0903 -0.0418 -0.0235 0.0447 -0.0465 -0.0244 0.0083 0.0466 -0.0343 0.1394 -0.0566 0.0833 -0.0513 0.0787 0.003 0.0979 -0.0585 -0.0059 0.0585 -0.1818 0.0347 -0.0113 -0.0859 0.1316 -0.0377 0.0629 -0.1382 0.0177 0.1141 0.1092 0.1045 0.0254 0.0137 0.0243 0.1043 0.0654 -0.0433 -0.0048 0.0747 0.0739 0.0905 -0.0734 0.1311 0.0075 0.1233 0.0447 0.0453 -0.0113 0.0741 0.0341 -0.0518 0.0273 0.0803 0.0497 -0.0189 -0.0703 0.1291 0.0958 -0.0201 0.1118 -0.0574 0.0135 -0.0413 0.0776 0.117 0.0264 -0.014 0.0021 0.0454 -0.0343 0.034 -0.0576 0.0611 -0.0798 0.0631 -0.019 -0.0088 -0.0936 -0.0299 -0.0606 0.0331 0.0466 -0.0144 0.0771 0.0186 0.0178 -0.0646 0.0967 -0.1415 0.1187 0.0369 -0.0662 -0.0098 -0.0446 0.1312 0.0242 -0.028 0.055 0.0382 0.0193 0.0024 -0.0676 0 0.0846 0.0742 0.0222 -0.0457 0.0305 -0.0201 -0.0023 0.0306 0.0336 -0.0325 -0.0349 -0.0094 0.0165 -0.0177 0.0143 0.059 0.0588 -0.034 0.0527 -0.0242 0.0238 0.0696 -0.0308 -0.0288 -0.019 0.034 0.0716 0.0314 0.051 -0.0256 0.0094 -0.0431 0.0059 -0.0039 0.0701 -0.061 0.1212 0.1707 -0.0233 0.1331 -0.0392 0.1135 -0.054 -0.1586 -0.0722 -0.0428 0.0553 0 0.1705 -0.008 0.0101 0.0864 -0.0135 0.109 0.0899 -0.0294 -0.1093 0.1407 0.1118 0.0403 0.1162 -0.0457 -0.0751 0.0534 -0.0493 -0.0651 0.0224 -0.018 -0.0672 -0.1573 -0.0297 0.1049 -0.0771 0.1016 0.0323 0.0256 0.0704 -0.0234 0.04 -0.0272 -0.0053 -0.0667 0.1154 0.1528 -0.1184 -0.0062 -0.0304 -0.0476 0.0432 -0.0123 0.0668 -0.0063 0.0554 0.012 -0.0139 0.068 -0.0912 0.0983 0.0434 -0.0092 0.0019 0.0163 0.0718 0.0309 0.053 -0.0893 -0.007 -0.0116 0.0342 -0.0505 -0.0689 -0.0199 0.0752 -0.0521 0.0161 0.0456 -0.0675 0.0206 -0.0524 -0.0319 0.1225 0.0238 -0.0426 0.0758 0.08 0 0.0446 -0.0051 0.072 -0.0032 0.0178 0.0106 0.0554 0.0902 0.0326