II Efficient Frontier and the Minimum Variance Portfolio ILI Portfolio: [MRNA, BND] 1. Efficient Frontier Complete the risk-return table for this portfolio and CORREL (MRNA,BND) Find the portfolio return, variance and volatility for the varying stock weights (see excel template) plot the Efficient Frontier (x axis is the op, y axis is the returne) Highlight the lowest risk (or) weight combination in green . . o CORREL(MRNA,BND) 0.0000000 0.000% 0.000% 0.000% P #NUM! 1 Efficient Frontier and the MVP 2 Portfolio : MRNA, BND 3 Stock A Stock B 4 MRNA BND 5. ER #NUM! 6 Variance #NUM! 7 sd #NUM! 8 9 Stock weight A % Stock weight B % returne 10 0% 100% #NUM! 11 10% 90% 12 20% 80% 13 30% 70% 14 40% 60% 15 50% 50% 16 60% 40% 17 70% 30% 18 80% 20% 19 90% 10% 20 100% 0% 21 22 Highlight the lowest risk combo 23 op #NUM! 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.0000% 0.000096 0.000% 100.00000% 90.00000% 80.00000% 70,00000% 60.00000% ERP 50.00000% Series1 40.00000% 30.00000% 20.00000% 10.00000% 0.00000 0.000% 20.000% 80.000 100.000% 40,00096 50.000% Risk (sd) Portfolio stock Iweight stock 2 weight squared 0.56 0.44 0.3136 0.1936 stock 1 stock 2 returnp 62p op MRNA MRNA MRNA MRNA MRNA |SBUX v |FLATX BND SP500 0.422% 0.414% 0.347% 0.403% 0.419% 0.229% 0.313% 0.200% 0.225% 0.232% 4.781% 5.593% 4.476% 4.742% 4.814% Choose the portfolio with lowest risk MRNA, FLATX Look at all the correlation coefficient for each pair of portfolios which provides most diversification opportunity MRNA SBUX -0.05300 V -0.01100 FLATX -0.11900 BND 0.01100 SP500 0.02100 Efficient Frontier and the MVP Portfolio: MRNA, BND Stock A Stock B MRNA BND ER 0.705% Variance 0.391% sd 6.252% CORREL(MRNA,BND) 0.0110000 0.019% 0.003% 0.592% 62p op 0.01873% 0.003% Stock weight A% Stock weight B% returnp 0% 100% 10% 90% 20% 80% 30% 70% 40% 60% 50% 50% 60% 40% 70% 30% 80% 20% 90% 10% 100% 0% 0.592% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.0000% 0.0000% 0.000% Highlight the lowest risk combo II Efficient Frontier and the Minimum Variance Portfolio ILI Portfolio: [MRNA, BND] 1. Efficient Frontier Complete the risk-return table for this portfolio and CORREL (MRNA,BND) Find the portfolio return, variance and volatility for the varying stock weights (see excel template) plot the Efficient Frontier (x axis is the op, y axis is the returne) Highlight the lowest risk (or) weight combination in green . . o CORREL(MRNA,BND) 0.0000000 0.000% 0.000% 0.000% P #NUM! 1 Efficient Frontier and the MVP 2 Portfolio : MRNA, BND 3 Stock A Stock B 4 MRNA BND 5. ER #NUM! 6 Variance #NUM! 7 sd #NUM! 8 9 Stock weight A % Stock weight B % returne 10 0% 100% #NUM! 11 10% 90% 12 20% 80% 13 30% 70% 14 40% 60% 15 50% 50% 16 60% 40% 17 70% 30% 18 80% 20% 19 90% 10% 20 100% 0% 21 22 Highlight the lowest risk combo 23 op #NUM! 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.0000% 0.000096 0.000% 100.00000% 90.00000% 80.00000% 70,00000% 60.00000% ERP 50.00000% Series1 40.00000% 30.00000% 20.00000% 10.00000% 0.00000 0.000% 20.000% 80.000 100.000% 40,00096 50.000% Risk (sd) Portfolio stock Iweight stock 2 weight squared 0.56 0.44 0.3136 0.1936 stock 1 stock 2 returnp 62p op MRNA MRNA MRNA MRNA MRNA |SBUX v |FLATX BND SP500 0.422% 0.414% 0.347% 0.403% 0.419% 0.229% 0.313% 0.200% 0.225% 0.232% 4.781% 5.593% 4.476% 4.742% 4.814% Choose the portfolio with lowest risk MRNA, FLATX Look at all the correlation coefficient for each pair of portfolios which provides most diversification opportunity MRNA SBUX -0.05300 V -0.01100 FLATX -0.11900 BND 0.01100 SP500 0.02100 Efficient Frontier and the MVP Portfolio: MRNA, BND Stock A Stock B MRNA BND ER 0.705% Variance 0.391% sd 6.252% CORREL(MRNA,BND) 0.0110000 0.019% 0.003% 0.592% 62p op 0.01873% 0.003% Stock weight A% Stock weight B% returnp 0% 100% 10% 90% 20% 80% 30% 70% 40% 60% 50% 50% 60% 40% 70% 30% 80% 20% 90% 10% 100% 0% 0.592% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.0000% 0.0000% 0.000% Highlight the lowest risk combo