Excel Implementation In the file “PortfolioAllocationEfficientFrontierExercise.xlsx” you find monthly returns for January 1995 to December 2004 for 8 different investment indices: 1. U.S. equity: the...

Excel Implementation In the file “PortfolioAllocationEfficientFrontierExercise.xlsx” you find monthly returns for January 1995 to December 2004 for 8 different investment indices: 1. U.S. equity: the S&P500 (SPX). 2. Short-maturity corporate bonds (C1C0). 3. Long-maturity corporate bonds (C7C0). 4. International equity: the MSCI index (MXWD). 5. Real estate (RMS). 6. High yield securities (HY). 7. Leveraged loans (DLJLTR). 8. Hedge Funds (HEDGNAV). The objective is to study portfolio allocation using these 8 return series. The exercise is very similar to what we did in class. I describe below a good course of action to tackle this question: ? In the worksheet “Data”, first compute average return and standard deviation for each investment index. The structure of the worksheet “Data” easily allows you to program the average return and standard deviation. You can then transfer the computed mean and standard deviation onto worksheet “Estimation” if that is easier for you. ? For each of the three analyses listed below, perform the portfolio allocation i.e., generate the portfolio frontier. You can use the Sheet “Estimation” for this purpose. First, you should compute the variance-covariance matrix and then find the weights for each level of the return provided in table “Weights” such that you have minimum standard deviation. Each of the return level is indicated in annual terms and is already multiplied by 100. So, you need to be careful and adjust your computations accordingly. ? Once you perform the optimization for each return level, you have to use the scatterplot function to draw the portfolio frontier in (annualized) return/standard deviation space, using the results in the worksheet “Data” under table “Weights”. You can also graph the return/standard deviation combinations for the 8 investment indexes, using the average return and standard deviation computed earlier. Please use the sheet “Results” to present your graphs as well as final estimates. Repeat this for each of the three different cases listed below. You need to complete three different analyses. (Note: In some return level cases, you may get a message that the “solver” did not converge, for those scenarios you can ignore that particular return level and move onto the next level i.e., completely ignore that particular return level when generating the portfolio frontier) A) No constraints on weights i.e., no short-sale constraints. B) Impose Short-sale constraints, i.e., you are not allowed to short-sale any of the security. FINA 4320: Investment Management