finance excel

ppz234
Project2ExampleSpring2017-REVISED1.xlsx
Home>Business & Finance homework help>Financial markets homework help>finance excel

data

Prices RETURNS Risk-Free
Date AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV S&P AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV S&P ANNUAL PERCENT MONTHLY DECIMAL
1/4/10 12.7 15.17 51.42 49.63 62.38 28.05 51.61 62.04 59.2 11.33 1073.87 -0.210199005 0.0079734219 0.0645962733 0.0165915608 -0.0240926158 -0.0754779169 -0.0165777439 0.0127326151 -0.019380487 -0.0087489064 -0.0369742624 0.07 0.0000583333
12/1/09 16.08 15.05 48.3 48.82 63.92 30.34 52.48 61.26 60.37 11.43 1115.1 0.287429944 -0.0492735313 0.0378169317 -0.0119409027 0.0250160359 0.0365562009 -0.0210781571 0.0615144689 -0.0159739201 0.2437431991 0.0177705977 0.05 0.0000416667
11/2/09 12.49 15.83 46.54 49.41 62.36 29.27 53.61 57.71 61.35 9.19 1095.63 0.0105177994 0.0872252747 -0.0158595898 0.0334658021 0.0725834193 0.0651382824 0.0702735077 0.0786915888 0.027638191 0.0953516091 0.057363997 0.05 0.0000416667
10/1/09 12.36 14.56 47.29 47.81 58.14 27.48 50.09 53.5 59.7 8.39 1036.19 -0.0535987749 -0.1384615385 -0.0269547325 -0.0376409018 -0.0301918265 0.0780698313 0.0310827501 0.0481974922 0.0321576763 -0.1251303441 -0.0197619858 0.05 0.0000416667
9/1/09 13.06 16.9 48.6 49.68 59.95 25.49 48.58 51.04 57.84 9.59 1057.08 0.0883333333 -0.0375854214 0.1341890315 0.0279329609 0.0073937153 0.0433892755 0.084133006 0.0569476082 0.0434782609 0.1738066095 0.0357233838 0.12 0.0001
8/3/09 12 17.56 42.85 48.33 59.51 24.43 44.81 48.29 55.43 8.17 1020.62 0.0273972603 0.189701897 0.0187826914 0.0694843992 0.000672608 0.0539257981 0.0186406001 -0.0178970917 -0.0014411818 0.0420918367 0.0335601734 0.13 0.0001083333
7/1/09 11.68 14.76 42.06 45.19 59.47 23.18 43.99 49.17 55.51 7.84 987.48 0.1384015595 0.1198786039 0.0281104864 0.158124039 0.0719178082 -0.0102476516 0.1184846173 0.1368786127 0.032744186 0.1666666667 0.074141757 0.17 0.0001416667
6/1/09 10.26 13.18 40.91 39.02 55.48 23.42 39.33 43.25 53.75 6.72 919.32 0.1200873362 0.1725978648 0.0565599174 0.0148244473 0.0298867644 0.1374453618 0.0191759523 -0.0536105033 0.064567241 -0.0014858841 0.0001958352 0.18 0.00015
5/1/09 9.16 11.24 38.72 38.45 53.87 20.59 38.59 45.7 50.49 6.73 919.14 0.0200445434 0.2615039282 -0.0147582697 0.0180037066 0.0627342671 0.0378024194 0.0558139535 0.0295111512 0.0459912989 -0.033045977 0.0530814267 0.13 0.0001083333
4/1/09 8.98 8.91 39.3 37.77 50.69 19.84 36.55 44.39 48.27 6.96 872.81 0.235213205 0.3102941176 0.099608282 -0.0796783626 -0.0045168892 0.1028349083 0.0019188596 0.1834177553 -0.0334401282 0.1012658228 0.0939250755 0.12 0.0001
3/2/09 7.27 6.8 35.74 41.04 50.92 17.99 36.48 37.51 49.94 6.32 797.87 0.1782820097 0.7302798982 -0.0456608812 0.0935251799 0.0518487916 0.137887413 0.043776824 -0.0406649616 0.0788507237 0.074829932 0.0854045083 0.2 0.0001666667
2/2/09 6.17 3.93 37.45 37.53 48.41 15.81 34.95 39.1 46.29 5.88 735.09 -0.182781457 -0.3990825688 -0.0771315919 -0.0830686538 -0.1260155263 -0.0493084787 -0.082436335 -0.1691457714 -0.0416149068 -0.1611982882 -0.1099312249 0.25 0.0002083333
1/2/09 7.55 6.54 40.58 40.93 55.39 16.63 38.09 47.06 48.3 7.01 825.88 -0.3079743355 -0.5328571429 0.0201106083 -0.1652049765 -0.0358572672 -0.1205711264 -0.1708750544 0.1430653388 -0.0829694323 -0.1839348079 -0.0856573485 0.22 0.0001833333
12/1/08 10.91 14 39.78 49.03 57.45 18.91 45.94 41.17 52.67 8.59 903.25 0.046021093 -0.1139240506 0.0305699482 -0.0006114961 0.0213333333 -0.0381485249 0.0607250058 -0.0230185097 -0.0264325323 -0.0034802784 0.0078215657 0.12 0.0001
11/3/08 10.43 15.8 38.6 49.06 56.25 19.66 43.31 42.14 54.1 8.62 896.24 -0.050955414 -0.3276595745 -0.0316106372 -0.0138693467 -0.0369799692 -0.0885489105 -0.0798810283 -0.1533052039 -0.0053318625 -0.265758092 -0.0748490323 0.02 0.0000166667
10/1/08 10.99 23.5 39.86 49.75 58.41 21.57 47.07 49.77 54.39 11.74 968.75 -0.4909680408 -0.3094328534 0.124717833 -0.0512967201 -0.1145975443 -0.1633048875 -0.0350553506 0.1094516273 -0.2001470588 -0.1881051176 -0.1694245344 0.44 0.0003666667
9/2/08 21.59 34.03 35.44 52.44 65.97 25.78 48.78 44.86 68 14.46 1166.36 -0.2972005208 0.146177164 -0.1102184283 0.1505046073 -0.0164007753 -0.0220030349 -0.0502336449 -0.1551789077 0.0471204188 -0.04743083 -0.0907914533 0.9 0.00075
8/1/08 30.72 29.69 39.83 45.58 67.07 26.36 51.36 53.1 64.94 15.18 1282.83 -0.0426924275 -0.053252551 0.2600442898 -0.0006577505 0.0353504168 0.0650505051 -0.0626026647 0.1092542302 0.028833967 -0.0225370251 0.0121905032 1.69 0.0014083333
7/1/08 32.09 31.36 31.61 45.61 64.78 24.75 54.79 47.87 63.12 15.53 1267.38 -0.0525538825 0.3778558875 -0.0146508728 -0.01977219 0.0642352555 -0.0649792218 0.0783310372 -0.0127861415 0.0467661692 0.1955350269 -0.009859375 1.63 0.0013583333
6/2/08 33.87 22.76 32.08 46.53 60.87 26.47 50.81 48.49 60.3 12.99 1280 -0.1225388601 -0.2842767296 0.0375161708 -0.0824295011 -0.035951853 -0.0286238532 0.0513138837 -0.0887051306 -0.06308266 -0.0007692308 -0.0859623816 1.71 0.001425
5/1/08 38.6 31.8 30.92 50.71 63.14 27.25 48.33 53.21 64.36 13 1400.38 0.1672210463 -0.094017094 0.0144356955 0.0148088853 0.0017452007 -0.00329188 0.0402496771 0.0565925338 -0.0032522843 -0.0136570561 0.0106741532 1.85 0.0015416667
4/1/08 33.07 35.1 30.48 49.97 63.03 27.34 46.46 50.36 64.57 13.18 1385.59 -0.0307737397 -0.0098730606 -0.1582435791 0.0707092351 0.0342960289 0.0047776553 -0.0175512793 0.027755102 -0.0508599147 0.0672064777 0.0475466848 1.34 0.0011166667
3/3/08 34.12 35.45 36.21 46.67 60.94 27.21 47.29 49 68.03 12.35 1322.7 -0.0290267501 -0.0298303229 -0.065307176 -0.0215932914 0.0468991582 0.0437284235 0.0423187128 -0.0730230798 0.043564964 0.0114660115 -0.0059595831 1.27 0.0010583333
2/1/08 35.14 36.54 38.74 47.7 58.21 26.07 45.37 52.86 65.19 12.21 1330.63 0.1280898876 -0.1 -0.001288992 -0.017102823 -0.0122178856 -0.1625441696 -0.0032952548 -0.0086271568 0.0221072437 0.0462724936 -0.0347611621 1.78 0.0014833333
1/2/08 31.15 40.6 38.79 48.53 58.93 31.13 45.52 53.32 63.78 11.67 1378.55 -0.0947399012 0.0701107011 -0.1459709379 -0.0512218964 -0.0533333333 -0.0844117647 -0.0647215944 0.0383641675 -0.1033319275 -0.0387149918 -0.0611634749 1.87 0.0015583333
12/3/07 34.41 37.94 45.42 51.15 62.25 34 48.67 51.35 71.13 12.14 1468.36 0.0049649533 -0.0925615881 -0.0375079466 0.005899705 -0.0153432458 0.0595200997 -0.0390918065 0.0180412371 -0.0118088358 -0.1377840909 -0.0086284889 3.14 0.0026166667
11/1/07 34.24 41.81 47.19 50.85 63.22 32.09 50.65 50.44 71.98 14.08 1481.14 -0.0812986316 -0.0445612431 -0.0510758094 0.0225216167 0.0458229942 -0.0839280617 0.0629590766 -0.0450586899 0.0469818182 -0.0042432815 -0.0440434238 3.07 0.0025583333
10/1/07 37.27 43.76 49.73 49.73 60.45 35.03 47.65 52.82 68.75 14.14 1549.38 0.0163621489 -0.0397191135 -0.1076619415 -0.0054 -0.0080406958 0.2492867332 -0.0324873096 0.0766408479 0.006293911 -0.0400543109 0.014822335 3.82 0.0031833333
9/4/07 36.67 45.57 55.73 50 60.94 28.04 49.25 49.06 68.32 14.73 1526.75 0.0709696262 0.0046296296 0.079411195 0.0548523207 0.0633397313 0.0256035113 0.0438745231 -0.0718880061 0.082725832 -0.0199600798 0.0357940013 3.7 0.0030833333
8/1/07 34.24 45.36 51.63 47.4 57.31 27.34 47.18 52.86 63.1 15.03 1473.99 -0.0392817059 0.068803016 0.0483248731 0.0143376846 0.0281664873 -0.0058181818 -0.0241985522 -0.051838565 0.0368057838 -0.0353016688 0.0128635923 3.99 0.003325
7/2/07 35.64 42.44 49.25 46.73 55.74 27.5 48.35 55.75 60.86 15.58 1455.27 -0.0576414595 -0.0301645338 -0.0262949783 -0.0689380355 -0.0181433856 -0.0161001789 -0.0378109453 0.0014370397 0.0118038238 0.0505731625 -0.0319819071 4.81 0.0040083333
6/1/07 37.82 43.76 50.58 50.19 56.77 27.95 50.25 55.67 60.15 14.83 1503.35 -0.018172378 -0.0359109936 0.0144404332 -0.0081027668 -0.026076514 -0.0398488492 -0.0019860973 -0.0044706724 -0.0456925274 0.0421644413 -0.0178163097 4.67 0.0038916667
5/1/07 38.52 45.39 49.86 50.6 58.29 29.11 50.35 55.92 63.03 14.23 1530.62 0.1690440061 0.0073235686 0.0259259259 0.0193392425 -0.0083361688 0.0286219081 -0.0328467153 -0.0310171547 0.0339566929 -0.0028030834 0.0325492286 4.59 0.003825
4/2/07 32.95 45.06 48.6 49.64 58.78 28.3 52.06 57.71 60.96 14.27 1482.37 0.0466963151 -0.0024352446 0.0257492613 0.0417628541 0.0656272661 0.0740037951 0.0633169935 0.0663340724 0.0397407471 -0.0239398085 0.0432906831 4.72 0.0039333333
3/1/07 31.48 45.17 47.38 47.65 55.16 26.35 48.96 54.12 58.63 14.62 1420.86 0.0148291425 0.0037777778 0.0132591959 0.0179448836 -0.0423611111 -0.0105144574 0.0055452865 0.0425736852 0.0113851992 -0.0272787758 0.0099799548 4.89 0.004075
2/1/07 31.02 45 46.76 46.81 57.6 26.63 48.69 51.91 57.97 15.03 1406.82 0.0343447816 -0.0223767108 -0.002559727 -0.0186582809 -0.0524757361 -0.0839353285 -0.0392659826 0.0170454545 -0.0320587744 0.001332445 -0.0218461453 4.99 0.0041583333
1/3/07 29.99 46.03 46.88 47.7 60.79 29.07 50.68 51.04 59.89 15.01 1438.24 0.0818903319 -0.0151904151 -0.0180142438 -0.0164948454 0.0118175766 0.0334162815 0.004359889 0.0205958808 0.0430163706 -0.014445174 0.0140590848 4.97 0.0041416667
12/1/06 27.72 46.74 47.74 48.5 60.08 28.13 50.46 50.01 57.42 15.23 1418.3 -0.0354906054 -0.0086956522 0.0242437245 0.027107158 0.0016672224 0.0169920463 -0.0165659715 0.0479882649 0.0141292829 -0.0243433696 0.0126157515 4.89 0.004075
11/1/06 28.74 47.15 46.61 47.22 59.98 27.66 51.31 47.72 56.62 15.61 1400.63 0.0837104072 0.0100685518 0.0217010083 -0.025990099 -0.0165600918 0.0259643917 -0.0382380506 -0.0035498016 -0.0232879075 0.0448460509 0.0164666096 4.89 0.004075
10/2/06 26.52 46.68 45.62 48.48 60.99 26.96 53.35 47.89 57.97 14.94 1377.94 0.0311041991 0.005601034 0.0277089435 0.0227848101 0.0377743747 0.0498442368 0.0391507596 0.0956302906 -0.0278383364 -0.0972809668 0.0315080286 4.94 0.0041166667
9/1/06 25.72 46.42 44.39 47.4 58.77 25.68 51.34 43.71 59.63 16.55 1335.85 -0.0194433854 0.0408071749 0.0104712042 0.0408432148 0.0044436848 0.0642353916 0.0231167796 0.0113373438 0.0043793162 -0.0383497966 0.0245662745 4.76 0.0039666667
8/1/06 26.23 44.6 43.93 45.54 58.51 24.13 50.18 43.22 59.37 17.21 1303.82 -0.0402488108 0.0097351143 0.033403905 -0.0050251256 0.039623312 0.0719680142 0.0159951407 0.0695372433 0.0300138793 -0.0369334079 0.0212742625 4.91 0.0040916667
7/3/06 27.33 44.17 42.51 45.77 56.28 22.51 49.39 40.41 57.64 17.87 1276.66 -0.074500508 0.0713073005 -0.0718340611 0.0103752759 0.0439621592 0.0325688073 0.042643023 0.0733067729 0.0554843435 0.0990159902 0.0050858133 4.93 0.0041083333
6/1/06 29.53 41.23 45.8 45.3 53.91 21.8 47.37 37.65 54.61 16.26 1270.2 0.0200345423 -0.0062665703 -0.0097297297 -0.0074496056 -0.0049833887 0.0287871638 -0.0281083299 -0.0071202532 -0.0020102339 0.0168855535 0.000086608 4.86 0.00405
5/1/06 28.95 41.49 46.25 45.64 54.18 21.19 48.74 37.92 54.72 15.99 1270.09 -0.0566959922 -0.0203069658 -0.0618661258 -0.0197594502 0.0337721809 -0.0582222222 -0.0352335709 0.0133618386 0.0381331816 -0.0074487896 -0.0309169013 4.72 0.0039333333
4/3/06 30.69 42.35 49.3 46.56 52.41 22.5 50.52 37.42 52.71 16.11 1310.61 0.1051494418 0.0960144928 0.0053017945 0.0802784223 -0.0101983003 -0.1127760252 0.0373716632 0.0420495684 0.0078393881 -0.0984890879 0.0121556604 4.65 0.003875
3/1/06 27.77 38.64 49.04 43.1 52.95 25.36 48.7 35.91 52.3 17.87 1294.87 0.0424174174 0.0044190278 0.0830388693 0.0018596002 0.0271580989 0.0127795527 0.0410431808 -0.0038834951 -0.0180247841 0.0732732733 0.0110958412 4.51 0.0037583333
2/1/06 26.64 38.47 45.28 43.02 51.55 25.04 46.78 36.05 53.26 16.65 1280.66 -0.0649350649 0.0363685345 -0.0339236185 0.0148619958 0.0076231431 -0.0420811018 -0.0186700231 0.0796645702 0.0337732919 0.0189718482 0.0004530967 4.51 0.0037583333
1/3/06 28.49 37.12 46.87 42.39 51.16 26.14 47.67 33.39 51.52 16.34 1280.08 0.0654450262 -0.0415698425 0.0537320144 -0.0339562443 -0.0426646707 0.0761630301 0.0511576626 0.0798835705 -0.0323065364 0.0018393624 0.0254668386 4.37 0.0036416667
12/1/05 26.74 38.73 44.48 43.88 53.44 24.29 45.35 30.92 53.24 16.31 1248.29 0.0786607503 0.0057128019 0.0118289354 0.0129270545 -0.0265938069 -0.0552314275 0.0015459364 -0.0162265352 0.00244775 -0.0042735043 -0.0009523962 3.98 0.0033166667
11/1/05 24.79 38.51 43.96 43.32 54.9 25.71 45.28 31.43 53.11 16.38 1249.48 0.1355932203 0.0605893693 0.0476644423 0.0415965376 -0.0088463622 0.0802521008 -0.0264459256 -0.0799180328 0.0020754717 0.0308370044 0.0351861211 3.86 0.0032166667
10/3/05 21.83 36.31 41.96 41.59 55.39 23.8 46.51 34.16 53 15.89 1207.01 -0.0054669704 0.0389127325 -0.0369520312 0.0381927109 -0.0103626943 -0.0012589173 0.0553664624 -0.0135720474 0.0416666667 0.0780189959 -0.017740741 3.81 0.003175
9/1/05 21.95 34.95 43.57 40.06 55.97 23.83 44.07 34.63 50.88 14.74 1228.81 -0.0884551495 -0.021556551 0.0288075561 0.0348747094 -0.0017834849 -0.0599605523 0.0460479468 0 0.0387913434 0.1149773071 0.00694894 3.47 0.0028916667
8/1/05 24.08 35.72 42.35 38.71 56.07 25.35 42.13 34.63 48.98 13.22 1220.33 -0.0398724083 -0.0016769145 0.0301629774 -0.0209914011 -0.0037313433 0.0723350254 0.0009503445 -0.0551159618 0.0059560485 -0.0610795455 -0.011222026 3.43 0.0028583333
7/1/05 25.08 35.78 41.11 39.54 56.28 23.64 42.09 36.65 48.69 14.08 1234.18 0.073630137 -0.0440822869 -0.0581901489 0.0375229599 -0.0160839161 0.0309638029 0.0268358136 0.0459474886 0.0112149533 0.018813314 0.0359682036 3.33 0.002775
6/1/05 23.36 37.43 43.65 38.11 57.2 22.93 40.99 35.04 48.15 13.82 1191.33 -0.0359059018 -0.0055791711 0.1296583851 0.0214419727 -0.0311653117 -0.03736356 -0.0286729858 0.0333235034 -0.037 -0.0422730423 -0.0001426773 3.06 0.00255
5/2/05 24.23 37.64 38.64 37.31 59.04 23.82 42.2 33.91 50 14.43 1191.5 -0.0612165827 0.0284153005 0.0766230148 0.0300938708 -0.0176372712 0.0231958763 0.0021372596 0.0370030581 0.0111223458 -0.0223577236 0.0299520249 2.88 0.0024
4/1/05 25.81 36.6 35.89 36.22 60.1 23.28 42.11 32.7 49.45 14.76 1156.85 -0.0451350351 0.0214903712 -0.0419113721 0.0316149245 0.0219350451 0.0467625899 0.0418109847 -0.0325443787 0.0492255464 0.045325779 -0.0201085898 2.84 0.0023666667
3/1/05 27.03 35.83 37.46 35.11 58.81 22.24 40.42 33.8 47.13 14.12 1180.59 -0.0539026951 -0.0455514118 0.035092567 0.0074605452 0.0238509749 -0.0393088553 -0.0639184808 -0.0558659218 -0.0111204364 0.0284049527 -0.0191176471 2.72 0.0022666667
2/1/05 28.57 37.54 36.19 34.85 57.44 23.15 43.18 35.8 47.66 13.73 1203.6 0.093797856 0.0058949625 0.0069560378 0.0621761658 0.0182591739 -0.0398175031 0.0437515108 0.0260819719 0.0029461279 -0.0438718663 0.0189033836 2.69 0.0022416667
1/20/05 26.12 37.32 35.94 32.81 56.41 24.11 41.37 34.89 47.52 14.36 1181.27 2.42 0.0020166667

means and standard deviations

AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV S&P ^IRX
Means -0.0027997179 0.0005956312 0.00825645 0.0082298399 0.0024868874 0.0052040632 0.0049561628 0.0120354337 0.0047887131 0.0001167827 -0.0004853512 0.0022288251
Standard deviations 0.1270262541 0.1742458421 0.0686220938 0.0515797956 0.0402187339 0.0740571889 0.0502113428 0.0694969123 0.0464822698 0.089847774 0.046462399 0.002265

correlation

AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
AA 1 0.4524718753 0.0960056812 0.2717069068 0.4177312658 0.4428641657 0.3923823427 0.1214711746 0.423256806 0.4907059412
BAC 0.4524718753 1 -0.0684336961 0.5377158411 0.5392321287 0.4078441925 0.454311805 0.046942189 0.5053002794 0.5283920646
BB.PA 0.0960056812 -0.0684336961 1 -0.0879357169 0.0086831503 0.0192109351 0.0606627506 0.2871919744 -0.1006832174 0.0550751482
CB 0.2717069068 0.5377158411 -0.0879357169 1 0.4606152537 0.1837555749 0.4774138255 -0.1209735528 0.491244995 0.281916548
JNJ 0.4177312658 0.5392321287 0.0086831503 0.4606152537 1 0.3699927926 0.5489207756 0.1348062591 0.6703447998 0.4988487293
MSFT 0.4428641657 0.4078441925 0.0192109351 0.1837555749 0.3699927926 1 0.2444454904 0.1223784892 0.4044219926 0.2052832455
NVS 0.3923823427 0.454311805 0.0606627506 0.4774138255 0.5489207756 0.2444454904 1 0.1125968261 0.443968777 0.5064447657
OKS 0.1214711746 0.046942189 0.2871919744 -0.1209735528 0.1348062591 0.1223784892 0.1125968261 1 -0.2249746011 0.1756362652
PEP 0.423256806 0.5053002794 -0.1006832174 0.491244995 0.6703447998 0.4044219926 0.443968777 -0.2249746011 1 0.3554540048
LUV 0.4907059412 0.5283920646 0.0550751482 0.281916548 0.4988487293 0.2052832455 0.5064447657 0.1756362652 0.3554540048 1

covariance

AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
AA 0.0158667414 0.0098480051 0.0008229153 0.0017505501 0.0020985515 0.0040966798 0.0024609656 0.001054467 0.0024574547 0.0055070991
BAC 0.0098480051 0.0298555866 -0.0008046317 0.0047522101 0.0037159284 0.0051751708 0.0039085803 0.0005589745 0.0040243899 0.0081344238
BB.PA 0.0008229153 -0.0008046317 0.0046305086 -0.0003060622 0.0000235652 0.000096002 0.0002055364 0.0013467982 -0.0003157978 0.0003339087
CB 0.0017505501 0.0047522101 -0.0003060622 0.0026161341 0.0009396088 0.0006902209 0.0012158422 -0.0004264188 0.0011581528 0.0012847194
JNJ 0.0020985515 0.0037159284 0.0000235652 0.0009396088 0.0015905874 0.0010836515 0.0010900355 0.0003705141 0.0012322951 0.001772578
MSFT 0.0040966798 0.0051751708 0.000096002 0.0006902209 0.0010836515 0.0053930594 0.0008938237 0.0006193535 0.0013689578 0.0013431633
NVS 0.0024609656 0.0039085803 0.0002055364 0.0012158422 0.0010900355 0.0008938237 0.0024791593 0.0003863619 0.0010189253 0.0022466841
OKS 0.001054467 0.0005589745 0.0013467982 -0.0004264188 0.0003705141 0.0006193535 0.0003863619 0.0047493238 -0.0007146396 0.0010784196
PEP 0.0024574547 0.0040243899 -0.0003157978 0.0011581528 0.0012322951 0.0013689578 0.0010189253 -0.0007146396 0.0021245914 0.0014597511
LUV 0.0055070991 0.0081344238 0.0003339087 0.0012847194 0.001772578 0.0013431633 0.0022466841 0.0010784196 0.0014597511 0.0079380788

Short Sales

INPUTS
Ticker E(r) StDev Company Name
AA -0.0028 0.1270 Alcoa
BAC 0.0006 0.1742 Bank of America
BB.PA 0.0083 0.0686 Bic
CB 0.0082 0.0516 Chubb
JNJ 0.0025 0.0402 Johnson and Johnson
MSFT 0.0052 0.0741 Microsoft
NVS 0.0050 0.0502 Novartis
OKS 0.0120 0.0695 Oneok
PEP 0.0048 0.0465 Pepsi
LUV 0.0001 0.0898 Southwest Airlines
Covariance Matrix
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
AA 0.0159 0.0098 0.0008 0.0018 0.0021 0.0041 0.0025 0.0011 0.0025 0.0055
BAC 0.0098 0.0299 -0.0008 0.0048 0.0037 0.0052 0.0039 0.0006 0.0040 0.0081
BB.PA 0.0008 -0.0008 0.0046 -0.0003 0.0000 0.0001 0.0002 0.0013 -0.0003 0.0003
CB 0.0018 0.0048 -0.0003 0.0026 0.0009 0.0007 0.0012 -0.0004 0.0012 0.0013
JNJ 0.0021 0.0037 0.0000 0.0009 0.0016 0.0011 0.0011 0.0004 0.0012 0.0018
MSFT 0.0041 0.0052 0.0001 0.0007 0.0011 0.0054 0.0009 0.0006 0.0014 0.0013
NVS 0.0025 0.0039 0.0002 0.0012 0.0011 0.0009 0.0025 0.0004 0.0010 0.0022
OKS 0.0011 0.0006 0.0013 -0.0004 0.0004 0.0006 0.0004 0.0047 -0.0007 0.0011
PEP 0.0025 0.0040 -0.0003 0.0012 0.0012 0.0014 0.0010 -0.0007 0.0021 0.0015
LUV 0.0055 0.0081 0.0003 0.0013 0.0018 0.0013 0.0022 0.0011 0.0015 0.0079
Compute Variance
Richard D. Johnson: Each cell in the bordered covariance matrix is computed by multiplying the covariance between the two stocks (from the table above) by the weight in each stock (i.e., weight in row * weight in column * cov(row, column)). Note that the weights that appear across the top of the table contain references to the cells along the first column (column A).
Bordered Covariance Matrix for Target Returns
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
Weights 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000
0.1000 0.0002 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 AA MVF Data Point w/Short Sales allowed
0.1000 0.0001 0.0003 -0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 BAC
0.1000 0.0000 -0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 BB.PA Returns SD
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 CB -0.006 0.0711
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 JNJ -0.004 0.062
0.1000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0000 MSFT -0.002 0.0533
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 NVS 0 0.045
0.1000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 OKS 0.002 0.0374
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 PEP 0.004 0.0311
0.1000 0.0001 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 LUV 0.006 0.027
1.0000 0.0005 0.0007 0.0001 0.0001 0.0001 0.0002 0.0002 0.0001 0.0001 0.0003 0.008 0.0261
0.01 0.0287
Bordered 0.012 0.034
Port Variance 0.0024 0.014 0.0411
Port S.D. 0.0489 0.016 0.0491
Port Mean 0.0044 0.018 0.0576
0.02 0.0665
0.025 0.0896
Risk free rate 0.00223
Monthly Sharpe Ratio 0.0441
Annual Sharpe Ratio 0.1528
How to find the optimal portfolio?
The optimal portfolio is on the CAL with the highest slope (Sharpe Ratio). We therefore want to maximize the Sharpe Ratio.
We will do constrained optimization using solver. Do the following:
1. Open Solver (Data Analysis)
2. Select the target cell to be the Sharpe Ratio below (cell D57 or D58)
3. Select maximize (we want the highest Sharpe Ratio)
4. Select 'by changing cells' to be the portfolio weights (cells A34 to A43)
5. Add constraints. See 6 and 7.
6. First constraint: Make sure the weights sum to one. To do this make cell A44 equal one.
7. Click Solve. Solver should find a solution. It will fill the optimal weights in A34-A43, the expected return and standard deviation of the
optimal portfolio in A47-A48 and the Sharpe Ratio of the optimal portfolio below in D57 (monthly) and D58 (annual)
How to impose Short Sales Constraints?
Same as described in 1-7 above, but also include a second constraint.
Second constraint: Make sure all the weights are nonnegative. To do this make all the cell A34 to A43 greater than or equal to zero.
MVF 7.1099999999999997E-2 6.2E-2 5.33E-2 4.4999999999999998E-2 3.7400000000000003E-2 3.1099999999999999E-2 2.7E-2 2.6100000000000002E-2 2.87E-2 3.4000000000000002E-2 4.1099999999999998E-2 4.9099999999999998E-2 5.7599999999999998E-2 6.6500000000000004E-2 8.9599999999999999E-2 -6.0000000000000001E-3 -4.0000000000000001E-3 -2E-3 0 2E-3 4.0000000000000001E-3 6.0000000000000001E-3 8.0000000000000002E-3 0.01 1.2E-2 1.4E-2 1.6E-2 1.7999999999999999E-2 0.02 2.5000000000000001E-2

Green Investing

INPUTS
Ticker E(r) StDev Company Name
AA -0.0028 0.1270 Alcoa
BAC 0.0006 0.1742 Bank of America
BB.PA 0.0083 0.0686 Bic
CB 0.0082 0.0516 Chubb
JNJ 0.0025 0.0402 Johnson and Johnson
MSFT 0.0052 0.0741 Microsoft
NVS 0.0050 0.0502 Novartis
OKS 0.0120 0.0695 Oneok
PEP 0.0048 0.0465 Pepsi
LUV 0.0001 0.0898 Southwest Airlines
Covariance Matrix
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
AA 0.0159 0.0098 0.0008 0.0018 0.0021 0.0041 0.0025 0.0011 0.0025 0.0055
BAC 0.0098 0.0299 -0.0008 0.0048 0.0037 0.0052 0.0039 0.0006 0.0040 0.0081
BB.PA 0.0008 -0.0008 0.0046 -0.0003 0.0000 0.0001 0.0002 0.0013 -0.0003 0.0003
CB 0.0018 0.0048 -0.0003 0.0026 0.0009 0.0007 0.0012 -0.0004 0.0012 0.0013
JNJ 0.0021 0.0037 0.0000 0.0009 0.0016 0.0011 0.0011 0.0004 0.0012 0.0018
MSFT 0.0041 0.0052 0.0001 0.0007 0.0011 0.0054 0.0009 0.0006 0.0014 0.0013
NVS 0.0025 0.0039 0.0002 0.0012 0.0011 0.0009 0.0025 0.0004 0.0010 0.0022
OKS 0.0011 0.0006 0.0013 -0.0004 0.0004 0.0006 0.0004 0.0047 -0.0007 0.0011
PEP 0.0025 0.0040 -0.0003 0.0012 0.0012 0.0014 0.0010 -0.0007 0.0021 0.0015
LUV 0.0055 0.0081 0.0003 0.0013 0.0018 0.0013 0.0022 0.0011 0.0015 0.0079
Compute Variance: Technique I
Richard D. Johnson: Each cell in the bordered covariance matrix is computed by multiplying the covariance between the two stocks (from the table above) by the weight in each stock (i.e., weight in row * weight in column * cov(row, column)). Note that the weights that appear across the top of the table contain references to the cells along the first column (column A).
Bordered Covariance Matrix for Target Returns
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
Weights 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000
0.1000 0.0002 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 AA
0.1000 0.0001 0.0003 -0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 BAC
0.1000 0.0000 -0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 BB.PA
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 CB
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 JNJ
0.1000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0000 MSFT
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 NVS
0.1000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 OKS
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 PEP
0.1000 0.0001 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 LUV
1.0000 0.0005 0.0007 0.0001 0.0001 0.0001 0.0002 0.0002 0.0001 0.0001 0.0003
Bordered
Port Variance 0.0024
Port S.D. 0.0489
Port Mean 0.0044
How do we find the optimal portfolio?
Risk free rate 0.00223
The optimal portfolio is on the CAL with the highest slope (Sharpe Ratio). We therefore want to maximize the Sharpe Ratio.
We will do constrained optimization using solver. Do the following:
1. Open Solver (Data Analysis)
2. Select the target cell to be the Sharpe Ratio below (cell D57 or D58)
3. Select maximize (we want the highest Sharpe Ratio)
4. Select 'by changing cells' to be the portfolio weights (cells A34 to A43)
5. Add constraints. See 6 and 7.
6. First constraint: Make sure the weights sum to one. To do this make cell A44 equal one.
7. Second constraint: Make the weights on the companies you 'do not like' equal zero. I decided not to like drug companies as I am 'outraged'
bakk0201: This is the only thing that is new here. It replaces the short sale constraints we had before.
that they 'overcharge developing countries. I added TWO constraints. The first one set the portfolio weight in Novartis to zero;
the second set the weight in J&J to zero.
8. Click Solve. Solver should find a solution. It will fill the optimal weights in A34-A43, the expected return and standard deviation of the
optimal portfolio in A47-A48 and the Sharpe Ratio of the optimal portfolio below in D57 (monthly) or D58 (annual)
Monthly Sharpe Ratio 0.0441
Annual Sharpe Ratio 0.1528
Extra Notes for 7.
To delete the short sales constraint simply select it and click 'delete'
To add a constraint (to force the allocation to the company you 'do not like' to be zero) do the following:
1. Select 'Add'
2. Select the portfolio weight for the company (i.e. A38 for J&J here)
3. Select '=' in the second box
4. Select '0'. This will force A38 to be zero when finding the optimal

Extra Graph

INPUTS
Ticker E(r) StDev Company Name
AA -0.0028 0.1270 Alcoa -0.0395866434
BAC 0.0006 0.1742 Bank of America 0.003418338
BB.PA 0.0083 0.0686 Bic 0.1203176638
CB 0.0082 0.0516 Chubb 0.1595554959
JNJ 0.0025 0.0402 Johnson and Johnson 0.0618340548
MSFT 0.0052 0.0741 Microsoft 0.0702708711
NVS 0.0050 0.0502 Novartis 0.0987060405
OKS 0.0120 0.0695 Oneok 0.1731794015
PEP 0.0048 0.0465 Pepsi 0.1030223599
LUV 0.0001 0.0898 Southwest Airlines 0.0012997841
Covariance Matrix
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
AA 0.0159 0.0098 0.0008 0.0018 0.0021 0.0041 0.0025 0.0011 0.0025 0.0055
BAC 0.0098 0.0299 -0.0008 0.0048 0.0037 0.0052 0.0039 0.0006 0.0040 0.0081
BB.PA 0.0008 -0.0008 0.0046 -0.0003 0.0000 0.0001 0.0002 0.0013 -0.0003 0.0003
CB 0.0018 0.0048 -0.0003 0.0026 0.0009 0.0007 0.0012 -0.0004 0.0012 0.0013
JNJ 0.0021 0.0037 0.0000 0.0009 0.0016 0.0011 0.0011 0.0004 0.0012 0.0018
MSFT 0.0041 0.0052 0.0001 0.0007 0.0011 0.0054 0.0009 0.0006 0.0014 0.0013
NVS 0.0025 0.0039 0.0002 0.0012 0.0011 0.0009 0.0025 0.0004 0.0010 0.0022
OKS 0.0011 0.0006 0.0013 -0.0004 0.0004 0.0006 0.0004 0.0047 -0.0007 0.0011
PEP 0.0025 0.0040 -0.0003 0.0012 0.0012 0.0014 0.0010 -0.0007 0.0021 0.0015
LUV 0.0055 0.0081 0.0003 0.0013 0.0018 0.0013 0.0022 0.0011 0.0015 0.0079
Compute Variance
Richard D. Johnson: Each cell in the bordered covariance matrix is computed by multiplying the covariance between the two stocks (from the table above) by the weight in each stock (i.e., weight in row * weight in column * cov(row, column)). Note that the weights that appear across the top of the table contain references to the cells along the first column (column A).
Bordered Covariance Matrix for Target Returns
AA BAC BB.PA CB JNJ MSFT NVS OKS PEP LUV
Weights 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000 0.1000
0.1000 0.0002 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 AA MVF Data Point w/Short Sales allowed
0.1000 0.0001 0.0003 -0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 BAC
0.1000 0.0000 -0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 BB.PA Returns SD
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 CB -0.006 0.0711
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 JNJ -0.004 0.062
0.1000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0001 0.0000 0.0000 0.0000 0.0000 MSFT -0.002 0.0533
0.1000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 NVS 0 0.045
0.1000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 OKS 0.002 0.0374
0.1000 0.0000 0.0000 -0.0000 0.0000 0.0000 0.0000 0.0000 -0.0000 0.0000 0.0000 PEP 0.004 0.0311
0.1000 0.0001 0.0001 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0001 LUV 0.006 0.027
1.0000 0.0005 0.0007 0.0001 0.0001 0.0001 0.0002 0.0002 0.0001 0.0001 0.0003 0.008 0.0261
0.01 0.0287
Bordered 0.012 0.034
Port Variance 0.0024 0.014 0.0411
Port S.D. 0.0489 0.016 0.0491
Port Mean 0.0044 0.018 0.0576
0.02 0.0665
0.025 0.0896
Risk free rate 0.00223 CAL
Risk_free 0.00223 0
MVP 0.0128 0.0368
Monthly Sharpe Ratio 0.0441 2.5 0.0286567623 0.092
Annual Sharpe Ratio 0.1528
How to find the optimal portfolio?
The optimal portfolio is on the CAL with the highest slope (Sharpe Ratio). We therefore want to maximize the Sharpe Ratio.
We will do constrained optimization using solver. Do the following:
1. Open Solver (Data Analysis)
2. Select the target cell to be the Sharpe Ratio below (cell D70)
3. Select maximize (we want the highest Sharpe Ratio)
4. Select 'by changing cells' to be the portfolio weights (cells A34 to A43)
5. Add constraints. See 6 and 7.
6. First constraint: Make sure the weights sum to one. To do this make cell A44 equal one.
7. Click Solve. Solver should find a solution. It will fill the optimal weights in A34-A43, the expected return and standard deviation of the
optimal portfolio in A47-A48 and the Sharpe Ratio of the optimal portfolio below in D69
How to impose Short Sales Constraints?
Same as described in 1-7 above, but also include a second constraint.
Second constraint: Make sure all the weights are nonnegative. To do this make all the cell A34 to A43 greater than or equal to zero.
MVF 7.1099999999999997E-2 6.2E-2 5.33E-2 4.4999999999999998E-2 3.7400000000000003E-2 3.1099999999999999E-2 2.7E-2 2.6100000000000002E-2 2.87E-2 3.4000000000000002E-2 4.1099999999999998E-2 4.9099999999999998E-2 5.7599999999999998E-2 6.6500000000000004E-2 8.9599999999999999E-2 -6.0000000000000001E-3 -4.0000000000000001E-3 -2E-3 0 2E-3 4.000000 0000000001E-3 6.0000000000000001E-3 8.0000000000000002E-3 0.01 1.2E-2 1.4E-2 1.6E-2 1.7999999999999999E-2 0.02 2.5000000000000001E-2 Alcoa 0.12702625409928806 -2.7997178843390492E-3 Bank of America 0.17424584208791866 5.956311917462557E-4 Bic 6.8622093828793793E-2 8.2564500119576573E-3 Chubb 5.157979562377555E-2 8.2298398675247266E-3 J & J 4.0218733903803525E-2 2.4868873954260831E-3 Microsoft 7.4057188890381834E-2 5.2040631723075702E-3 Novartis 5.0211342808482141E-2 4.9561628347285867E-3 Oneok 6.9496912327563748E-2 1.2035433683908522E-2 Pepsi 4.6482269750187877E-2 4.7887131229471126E-3 Southwest Airlines 8.984777398821539E-2 1.1678271037500835E-4 MVP 4.8924343734118705E-2 4.3870246106582477E-3 CAL 0 3.6799999999999999E-2 9.1999999999999998E-2 2.2300000000000002E-3 1.2800000000000001E-2 2.8656762295081968E-2