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...

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? For questions (b), (c), and (d), we assume that investors invest in the risk-free asset and 4 risky assets (PG, Microsoft, BAC, and Exxon).(b) Find the optimal investment portfolio in the risky assets. What are the mean and s.d. of the returns of this portfolio?


problem 3 DatePGMicrosoftBACExxonExpected Monthly Return 2/3/970.0390590324-0.04393505250.1097708082-0.0251669235Expected Monthly Return 3/3/97-0.0448526271-0.0599400599-0.07010869570.0748155954PG0.010848 4/1/970.09973166370.32518597240.08825248390.0514705882Microsoft0.014854 5/1/970.09678731190.0208500401-0.02470461870.0536130536BAC0.011589 6/2/970.02447163520.01885310290.10242290750.0336283186Exxon0.012043 7/1/970.08034744840.11873554360.10239760240.0488013699 8/1/97-0.124958124-0.0647829083-0.1603987313-0.0412244898Variance 9/2/970.03751914240.00073691970.04155423640.0468284376Variance 10/1/97-0.0118081181-0.0176730486-0.0336787565-0.0406669378PG0.004478 11/3/970.12061239730.08845577210.0042895442-0.0004239084Microsoft0.012820 12/1/970.0473175608-0.08677685950.0186865990.0029686175BAC0.005611 1/2/98-0.01495386570.1546003017-0.0151991614-0.0308668076Exxon0.002820 2/2/980.08301033590.13585891570.1436934540.0820244328 3/2/98-0.00596480760.05635422660.07073057240.0608870968Covariance 4/1/98-0.02280228020.00707675560.04215558450.0805777271Cov(PG, Microsoft)-0.000649 5/1/980.0202640467-0.0589189189-0.0066722269-0.029546254Cov(PG, BAC)0.000683 6/1/980.08576587420.27742676620.02099076410.0123233055Cov(PG, Exxon)0.000433 7/1/98-0.12555432370.01438848920.0398848684-0.0157536699Cov(Microsoft, BAC)0.001681 8/3/98-0.036133122-0.1272163121-0.279161724-0.0629319753Cov(Microsoft, Exxon)0.000804 9/1/98-0.07037158830.1472828847-0.06308283050.0791925466Cov(BAC, Exxon)0.000757 10/1/980.2515033605-0.03806994250.0749414520.014028777 11/2/98-0.01215375920.15232397610.13344226580.0532103583 12/1/980.04234620890.1369808307-0.071119654-0.0249242169 1/4/99-0.00164699420.26167896030.111743404-0.0393782383 2/1/99-0.0151223536-0.1422605791-0.0232666356-0.0471053578 3/1/990.09436069240.1940928270.08861362550.06 4/1/99-0.0392857143-0.09268823050.01356673960.1772872909 5/3/99-0.004779607-0.0077890953-0.0962867012-0.0335651648 6/1/99-0.05629669160.11775362320.1414237936-0.0344180225 7/1/990.0313825276-0.0486223663-0.09460025120.0291639663 8/2/990.09594298250.0789324248-0.0883032825-0.0012594458 9/1/99-0.0552776388-0.0218421053-0.072515213-0.0365699874 10/1/990.1223193010.02232983590.1569163477-0.0255235602 11/1/990.0297239915-0.0163157895-0.09073724010.0775688381 12/1/990.01443298970.2822364901-0.13617463620.0155811779 1/3/00-0.0749774164-0.1616941373-0.03489771360.0288432034 2/1/00-0.1306152344-0.0868591339-0.0504987531-0.0861914703 3/1/00-0.35411401290.18887980380.15298752460.0355744125 4/3/000.057826087-0.343649702-0.0654897494-0.0037819099 5/1/000.1130291821-0.10303877050.1413772090.0781398292 6/1/00-0.14660265880.2788161994-0.2242391885-0.0578051643 7/3/000.0103851147-0.12728380020.10185822440.0214886328 8/1/000.084796573900.14178638350.0237804878 9/1/000.0836952231-0.1360781577-0.0224288840.0914234663 10/2/000.07103825140.1417609047-0.08226077220.0008185539 11/1/000.0482993197-0.1669614432-0.1573170732-0.0084514722 12/1/000.0476963011-0.24373673040.1490593343-0.0120978829 1/2/01-0.07990089810.40707467710.1725440806-0.0320066797 2/1/01-0.0185122854-0.0335195531-0.0472610097-0.0319148936 3/1/01-0.1121399177-0.07308009910.079481398-0.0005940006 4/2/01-0.03476245650.2387527840.02297650130.0939078752 5/1/010.06962785110.02121539010.06789178150.0065199674 6/1/01-0.00673400670.05528169010.0133843212-0.0153846154 7/2/010.1193973635-0.09342676010.0599056604-0.0438596491 8/1/010.0440780619-0.1380198749-0.0333778371-0.0332568807 9/4/01-0.01836932-0.1029035013-0.0418968692-0.018683274 10/1/010.01904136570.13612565450.01009130230.0012088244 11/1/010.0499355670.10431503980.0404376784-0.0464835497 12/3/010.02147898130.03186646430.03566529490.0509654954 1/2/020.0372484229-0.03860294120.0013245033-0.0063253012 2/1/020.0379380249-0.08413001910.02425044090.0639587754 3/1/020.06250.03382045930.06371071890.0612535613 4/1/020.006039916-0.13368336030.0655605018-0.0834899329 5/1/02-0.0078308536-0.02564102560.0459551842-0.0002929115 6/3/020.00499868460.0741626794-0.06427015250.0246117785 7/1/020.0015706806-0.1224944321-0.0551028328-0.1015155848 8/1/02-0.00392054360.02284263960.053798768-0.0295989815 9/3/020.0083967463-0.1086848635-0.0810600156-0.1000327976 10/1/02-0.00598490760.22216035630.09372349450.0550291545 11/1/02-0.04424083770.07881548970.00426521910.0407599309 12/2/020.0167077513-0.10388513510.00193050190.0039827415 1/2/030.0002693966-0.0819981150.0069364162-0.0224793388 2/3/03-0.04336116350.0020533881-0.01186375810.0030436253 3/3/030.08783783780.0215163934-0.02517428350.0273095078 4/1/030.01371635610.05616850550.10766785860.0072202166 5/1/030.0219555782-0.03751187080.00215208030.0413815575 6/2/03-0.02872845370.04193389250.07408733-0.0134543179 7/1/03-0.00977366260.02982954550.0446517827-0.0091975896 8/1/03-0.00649350650.004137931-0.04019138760.0669014085 9/2/030.06326797390.0485347985-0.0053173812-0.0294029403 10/1/030.0641750676-0.0545851528-0.0294019379-0.000309119 11/3/03-0.021025878-0.0166281755-0.004130809-0.0034013605 12/1/030.03799858390.06481916390.07777393710.1324852622 1/2/040.01659845380.01014556680.0128287364-0.0052054795 2/2/040.0140908074-0.04061135370.00538315390.0402093087 3/1/040.0231583591-0.0600819299-0.0015748031-0.0137675404 4/1/040.0135805130.0479418886-0.00599369090.0230872483 5/3/040.01914079120.00415896490.03268803550.0230910522 6/1/040.00980801340.088357110.02765826670.026929982 7/1/04-0.0376110767-0.00211416490.0047846890.0424575425 8/2/040.073223105-0.03898305080.05833333330.0016770484 9/1/04-0.03301320530.0127865961-0.02699662540.0483138005 10/1/04-0.0498655080.01131911190.03352601160.0184804928 11/1/040.04486062720.0684459750.03299776290.0468189964 12/1/040.0300125052-0.00322320710.02571737950.0002139953 1/3/05-0.029340348-0.0165723525-0.0131960940.0066324347 2/1/05-0.0025015635-0.03945745990.00588392620.2329436769 3/1/05-0.0016718913-0.0393667095-0.0454666312-0.0586105844 4/1/050.02679505970.04677060130.021448468-0.0430324116 5/2/050.01855249750.02297872340.0283610581-0.0095675469 6/1/05-0.0434347478-0.037437604-0.00583399630.0226043277 7/1/050.05984515590.0311149525-0.04401173650.0222935953 8/1/05-0.00256663380.0720871752-0.00139508930.0245795602 9/1/050.0716547902-0.0602032838-0.02179379720.0606060606 10/3/05-0.0535648319-0.00124792010.0391316767-0.1163265306 11/1/050.02146760340.08038317370.06047278720.0388760585 12/1/050.0120366832-0.05512721670.0057024365-0.032048907 1/3/060.02831791580.0762953896-0.04175257730.1171291866 2/1/060.0117495869-0.04207733130.0368477676-0.0488264519 3/1/06-0.03846851750.0126632370.0041504540.0250360231 4/3/060.0158520476-0.11254396250.09609919920.0365489369 5/1/06-0.0681775961-0.058564509-0.0205043601-0.0294965248 6/1/060.02492025520.0285313377-0.00601539940.0071615721 7/3/060.01653374830.03274215550.07116920840.1042317031 8/1/060.10141599690.07221488330.00971751410.0034553165 9/1/060.00121612230.06406570840.0407341092-0.0082955079 10/2/060.02793683850.04978772670.00559139780.0643939394 11/1/06-0.00945307220.02610294120.01005132590.0800711744 12/1/060.02351738240.0171981369-0.0084691933-0.0023338825 1/3/070.03330003330.04191616770.0038436899-0.0404568598