In the attached file there 5 stocks in the porfolio with 60 rows of PriceHistory. Use the solver tool in Microsoft Excel to de?ne the Minimum Variance Frontier. That is, for a portfolio constructed of your assigned securities, ?nd the weights of each asset that would minimize the standard deviation/variance of the portfolio. Do this for each expected annual portfolio return level between 0% and 30% (increments of 5%). Please show your calculations in the Excel.

(PS system does not let me to upload the Excel file, so I can submit as png only. sorry)

Transcribed Image Text:

Date Stock C Stock D Stock F Stock G Stock R 01/31/14 3.81 -12.09 -13.37 -8.20 0.82 02/28/14 3.33 4.63 0.65 9.19 4.41 03/31/14 2.99 -0.09 1.38 -0.93 -10.19 04/30/14 4.44 -8.99 4.93 0.69 -8.17 05/30/14 -1.85 6.87 -0.93 2.00 3.02 06/30/14 4.71 -3.15 7.20 -1.59 10.28 07/31/14 -6.27 15.86 2.80 -7.52 5.16 08/29/14 5.57 2.19 -2.28 5.18 4.82 09/30/14 -2.88 -13.24 -10.23 2.21 -7.83 10/31/14 10.15 -6.14 -11.82 -1.93 4.93 11/28/14 2.14 -2.80 -5.79 -0.03 5.48 12/31/14 4.98 -0.85 -13.00 3.75 11.25 01/30/15 8.58 -17.32 -26.97 -7.47 -7.74 02/27/15 -6.16 9.43 28.67 0.91 8.36 03/31/15 -0.63 -3.39 -12.39 -0.46 9.59 04/30/15 -2.81 2.56 23.13 5.35 -0.65 05/29/15 1.50 4.02 -15.56 -2.80 2.67 06/30/15 -6.74 -1.16 -5.24 -1.53 -1.73 07/31/15 7.60 -2.51 -36.30 -3.36 4.15 08/31/15 -3.50 -16.30 -9.45 -1.80 -8.69 09/30/15 7.75 -4.22 -8.93 -3.77 -0.46 10/30/15 2.12 13.30 21.94 -2.33 10.06 11/30/15 -2.12 7.49 -30.50 -3.97 2.91 12/31/15 3.03 -14.74 -17.24 1.02 1.72 01/29/16 7.76 7.89 -32.05 -2.91 -15.41 02/29/16 2.55 -9.41 65.87 10.89 -6.71 03/31/16 7.28 9.53 35.52 7.62 14.02 04/29/16 -4.15 -0.81 35.40 0.47 -1.53 05/31/16 3.57 -0.04 -20.86 -2.12 5.57 06/30/16 9.66 -10.91 0.54 -0.48 -6.27 07/29/16 -1.48 2.89 16.34 -3.70 3.71 08/31/16 -6.45 1.14 -20.60 5.99 -3.07 09/30/16 0.10 6.00 5.54 -2.52 10.76 10/31/16 0.33 -4.56 2.95 -7.44 -4.18 11/30/16 -3.86 5.30 37.30 11.40 2.14 12/30/16 3.48 1.29 -14.07 0.73 -11.89 01/31/17 2.35 3.47 26.23 8.75 8.87 02/28/17 5.33 1.30 -19.52 -1.35 9.13 03/31/17 0.49 0.86 -0.30 -6.13 4.46 04/28/17 1.48 -1.17 -4.57 -17.21 1.83 05/31/17 5.20 -7.68 -9.88 -9.99 1.69 06/30/17 -2.45 -2.40 4.53 4.79 6.90 07/31/17 -0.02 -8.25 21.73 -7.64 3.26 08/31/17 5.72 -9.17 1.09 -1.74 8.73 09/29/17 -4.57 -3.57 -5.01 10.57 3.13 10/31/17 4.43 -12.09 -0.43 9.99 8.99 11/30/17 3.88 1.52 -0.43 12.65 4.91 12/29/17 -5.21 17.09 36.21 6.75 -5.25 01/31/18 -5.39 12.71 2.85 14.14 9.39 02/28/18 -4.36 -3.69 -4.62 -2.54 12.19 03/29/18 6.69 -15.06 -5.54 7.92 1.43 04/30/18 4.20 13.83 -13.19 -0.33 9.06 05/31/18 -1.49 -11.03 11.11 10.33 -0.39 06/29/18 2.49 28.98 2.13 -0.19 -17.27 07/31/18 2.24 -3.73 -4.13 12.37 5.11 08/31/18 2.62 4.44 -14.85 2.56 4.60 09/28/18 -0.49 15.37 -0.93 0.94 -7.75 10/31/18 1.06 -0.91 -15.98 -20.55 25.95 11/30/18 5.96 -4.71 2.49 11.10 4.03 Date Stock C Stock D Stock F Stock G Stock R 01/31/14 3.81 -12.09 -13.37 -8.20 0.82 02/28/14 3.33 4.63 0.65 9.19 4.41 03/31/14 2.99 -0.09 1.38 -0.93 -10.19 04/30/14 4.44 -8.99 4.93 0.69 -8.17 05/30/14 -1.85 6.87 -0.93 2.00 3.02 06/30/14 4.71 -3.15 7.20 -1.59 10.28 07/31/14 -6.27 15.86 2.80 -7.52 5.16 08/29/14 5.57 2.19 -2.28 5.18 4.82 09/30/14 -2.88 -13.24 -10.23 2.21 -7.83 10/31/14 10.15 -6.14 -11.82 -1.93 4.93 11/28/14 2.14 -2.80 -5.79 -0.03 5.48 12/31/14 4.98 -0.85 -13.00 3.75 11.25 01/30/15 8.58 -17.32 -26.97 -7.47 -7.74 02/27/15 -6.16 9.43 28.67 0.91 8.36 03/31/15 -0.63 -3.39 -12.39 -0.46 9.59 04/30/15 -2.81 2.56 23.13 5.35 -0.65 05/29/15 1.50 4.02 -15.56 -2.80 2.67 06/30/15 -6.74 -1.16 -5.24 -1.53 -1.73 07/31/15 7.60 -2.51 -36.30 -3.36 4.15 08/31/15 -3.50 -16.30 -9.45 -1.80 -8.69 09/30/15 7.75 -4.22 -8.93 -3.77 -0.46 10/30/15 2.12 13.30 21.94 -2.33 10.06 11/30/15 -2.12 7.49 -30.50 -3.97 2.91 12/31/15 3.03 -14.74 -17.24 1.02 1.72 01/29/16 7.76 7.89 -32.05 -2.91 -15.41 02/29/16 2.55 -9.41 65.87 10.89 -6.71 03/31/16 7.28 9.53 35.52 7.62 14.02 04/29/16 -4.15 -0.81 35.40 0.47 -1.53 05/31/16 3.57 -0.04 -20.86 -2.12 5.57 06/30/16 9.66 -10.91 0.54 -0.48 -6.27 07/29/16 -1.48 2.89 16.34 -3.70 3.71 08/31/16 -6.45 1.14 -20.60 5.99 -3.07 09/30/16 0.10 6.00 5.54 -2.52 10.76 10/31/16 0.33 -4.56 2.95 -7.44 -4.18 11/30/16 -3.86 5.30 37.30 11.40 2.14 12/30/16 3.48 1.29 -14.07 0.73 -11.89 01/31/17 2.35 3.47 26.23 8.75 8.87 02/28/17 5.33 1.30 -19.52 -1.35 9.13 03/31/17 0.49 0.86 -0.30 -6.13 4.46 04/28/17 1.48 -1.17 -4.57 -17.21 1.83 05/31/17 5.20 -7.68 -9.88 -9.99 1.69 06/30/17 -2.45 -2.40 4.53 4.79 6.90 07/31/17 -0.02 -8.25 21.73 -7.64 3.26 08/31/17 5.72 -9.17 1.09 -1.74 8.73 09/29/17 -4.57 -3.57 -5.01 10.57 3.13 10/31/17 4.43 -12.09 -0.43 9.99 8.99 11/30/17 3.88 1.52 -0.43 12.65 4.91 12/29/17 -5.21 17.09 36.21 6.75 -5.25 01/31/18 -5.39 12.71 2.85 14.14 9.39 02/28/18 -4.36 -3.69 -4.62 -2.54 12.19 03/29/18 6.69 -15.06 -5.54 7.92 1.43 04/30/18 4.20 13.83 -13.19 -0.33 9.06 05/31/18 -1.49 -11.03 11.11 10.33 -0.39 06/29/18 2.49 28.98 2.13 -0.19 -17.27 07/31/18 2.24 -3.73 -4.13 12.37 5.11 08/31/18 2.62 4.44 -14.85 2.56 4.60 09/28/18 -0.49 15.37 -0.93 0.94 -7.75 10/31/18 1.06 -0.91 -15.98 -20.55 25.95 11/30/18 5.96 -4.71 2.49 11.10 4.03