For this assignment, please use excel file group_assignment_1_portfolios.xls posted on blackboard...

Question

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

1298e2486f3d6e12248d2c81c0d56718-4jnncthb.png 1298e2486f3d6e12248d2c81c0d56718-t23enpez.png 4eb3ad1356754f37666a79514f1fae4b-y0yqetmj.png 99887123af3d4ea0d1b0eca70034be44-cvxn41fn-ptuims2d.png assignment-wlemxqfi-ukpo4p2h.xls

Answered Same DayMar 05, 2021

Answer To: For this assignment, please use excel file group_assignment_1_portfolios.xls posted on blackboard...

Kushal answered on Mar 06 2021

151 Votes

Portfolio return = w1 * r1 + w2* r2 + w3 * r3
Here, w1, w2, and w3 are the weights of the three asset classes in the portfolio and r1, r2 and r3 are the returns of the respective asset classes.
Standard deviation formula for the portfolio is given by this,
σP = (wA2σA2 + wB2 σB2 + wC2σC2 + 2wAwBσAσBρAB + 2wBwCσBσCρBC + 2wAwCσAσCρAC)1/2
Where,
σP = is the portfolio standard deviation;
ωB = weight of asset B in the portfolio;
σA = standard deviation of asset A;
σB = standard deviation of asset B; and
ρAB = correlation coefficient between returns on asset A and asset B.
ρBC = correlation coefficient between returns on asset B and asset C.
ρCA = correlation coefficient between returns on asset C and asset A.
As per the modern portfolio theory, the efficient frontier finds the locus of the portfolios with the different standard...

SOLUTION.PDF

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

Answer To: For this assignment, please use excel file group_assignment_1_portfolios.xls posted on blackboard...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment

Sun	Mon	Tue	Wed	Thu	Fri	Sat
30	31	1	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	1	2	3