I need help with this project, due for submission on Saturday, Feb6th

Portfolio Theory Please single-space and use size 11 font for your report. Please compile the project neatly, with tables and discussion where appropriate. Please be sure to cite sources, where appropriate. Pay close attention to spelling and grammar, to ensure best results. This project will require some work with the Solver Tool within Excel. Your goal is to find the optimal asset allocation between the following five asset classes: US large stocks, US small stocks, US high-grade bond, Developed markets, Emerging markets. Assume that your investment horizon is 10 years. We have learned from the class that to implement modern portfolio theory, we need the following inputs: expected returns and variances/covariance. 1. Describe historical data. Find and download monthly returns of each asset class over the past 2 years. You need to find an index for each asset classes. Below is a list of index choices: US large stocks: S&P 500 index http://us.spindices.com/indices/equity/sp-500 Click additional information for monthly returns US high-grade bond: BOA/Merrill Lynch Domestic Master (D0A0) http://www.mlindex.ml.com/gispublic/default.asp US small stocks: Russell 2000 index http://www.ftse.com/products/russell-index-values FactSheet:http://www.ftse.com/Analytics/FactSheets/temp/c1146f88-6fa1-4ee2-a14ab8c2b53eb6bf.pdf Developed markets: MSCI world ex USA index (in US dollars, Size (standard, including large cap and mid cap)) https://www.msci.com/resources/factsheets/index_fact_sheet/msci-world-ex-usaindex.pdf https://www.msci.com/end-of-day-data-search Emerging markets: MSCI emerging market index (in US dollars) https://www.msci.com/documents/10199/1493b63f-1ce8-418e-a82d-4aae72951f27 Note that every index has three series (price, gross (or total), net). Price series presents index levels without considering distribution. Total return series presents index levels including distributions. Net series presents index levels including distributions and assuming certain tax rate. The index values we typically see in news outlet are the price level series. We should be using the total return series for the projects. You may need to calculate the percentage returns from the month-end index values. You may end up with fewer than 24 monthly returns. That's fine. . Use this data to present the following A. Suppose that you invest $1 two years ago in each asset class, graph (all five on one graph) the month-end value of your investment for the past 24 months. B. Produce a table with the distribution of monthly returns using basic statistics: Mean median, maximum, minimum, and standard deviation. C. Present the correlation matrix and the covariance matrix of the monthly returns with each other. Comment on these relations. Present the annualized numbers, and use annualized numbers for the rest of the project. Annualized return = monthly return * 12 Annualized standard deviation = monthly standard deviation * sqrt(12) Annualized covariance/variance = monthly covariance/variance * 12 Correlation is unitless. No need to multiply by sqrt(12) or 12. Next you are going to find the optimal asset allocations. Note that to implement the modern portfolio theory, you need to have expected returns and risk. Would you can use the historical average in the past two years as your forecasted returns? Suppose that you have done your research and your view is that the expected returns will be (6%, 2.5%, 6.6%, 6%, 6.2%) respectively for the five asset classes and you can use historical correlation/covariance matrices as your forecasted risk. . 2. When short sales are allowed. A. B. C. D. E. Construct the mean-variance frontier and plot it in the mean-standard deviation space. Find the global minimal-variance portfolio, and plot it. Find the tangent portfolio and plot it. What's the Sharpe Ratio? Draw the CAL of the optimal risky portfolio. Suppose that your preference can be represented by the quadratic utility function U = E(r ) - A/2 2 and your risk aversion A = 4. What's your optimal allocation between the risk-free asset and the optimal risky portfolio? What's your complete portfolio expressed in terms of asset classes? 3. When short sales are NOT allowed. A. Construct the mean-variance frontier when short sales are not allowed and plot it in the mean-standard deviation space. Compare this frontier with that when short sales are allowed. Comment. B. Find the global minimal-variance portfolio, and plot it. Compare this portfolio with Portfolio 2B. Comment. C. Find the tangent portfolio and plot it. Draw the CAL of the optimal risky portfolio. What's the Sharpe Ratio? Compare this portfolio with the tangent portfolio when short sales are allowed. Comment. 4. Construct a risk-parity portfolio using the five major asset classes (Short sale is allowed). Use the weights of portfolio 2C as the initial values of weights before you use the Solver to find the optimal risk-parity portfolio. 5. Find the market cap of each index (most recent). Calculate the weight of each asset class in the value-weighted portfolio. What is the variance of the value-weighted portfolio? 6. Suppose that you are 50% confident in your view. You can put 50% of your money in the optimal risky portfolio based on your view (Portfolio 2 C) and the other 50% in the market portfolio. What's your final optimal risky portfolio? A 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 B C D E F Bordered Covariance Matrix for Target Return Portfolio EWD EWH EWI EWJ EWL Weights 0.0000 0.0000 0.0826 0.3805 0.0171 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0826 0.0000 0.0000 4.6261 3.2071 0.5475 0.3805 0.0000 0.0000 3.2071 98.4074 1.8208 0.0171 0.0000 0.0000 0.5475 1.8208 0.1374 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.5198 0.0000 0.0000 7.6932 53.7915 2.0854 1.0000 0.0000 0.0000 16.0739 157.2268 4.5911 Portfolio Variance 321.3580 Portfolio Standard Deviation 17.9265 Portfolio Mean 12.0000 Mean 6.00 9.00 12.00 15.00 18.00 21.00 24.00 27.00 St. Dev 21.8883 19.6644 17.9265 16.8147 16.4614 17.3685 21.1878 26.0514 Weights EWD 0.0165 0.0217 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWH 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWI 0.0000 0.0185 0.0826 0.1372 0.1918 0.4023 0.7225 1.0000 EWJ 0.7075 0.5300 0.3805 0.2232 0.0658 0.0000 0.0000 0.0000 G H I EWP 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWW S&P 500 0.0000 0.5198 0.0000 0.0000 0.0000 0.0000 0.0000 7.6932 0.0000 53.7915 0.0000 2.0854 0.0000 0.0000 0.0000 0.0000 0.0000 79.8960 0.0000 143.4662 EWL 0.0000 0.0176 0.0171 0.0168 0.0164 0.0000 0.0000 0.0000 EWP 0.0188 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 J EWW 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 S&P 500 0.2571 0.4122 0.5198 0.6229 0.7260 0.5977 0.2775 0.0000 A 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 B C D E F Bordered Covariance Matrix for Target Return Portfolio EWD EWH EWI EWJ EWL Weights 0.0000 0.0000 0.0826 0.3805 0.0171 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0826 0.0000 0.0000 4.6261 3.2071 0.5475 0.3805 0.0000 0.0000 3.2071 98.4074 1.8208 0.0171 0.0000 0.0000 0.5475 1.8208 0.1374 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.5198 0.0000 0.0000 7.6932 53.7915 2.0854 1.0000 0.0000 0.0000 16.0739 157.2268 4.5911 Portfolio Variance 321.3580 Portfolio Standard Deviation 17.9265 Portfolio Mean 12.0000 Mean 6.00 9.00 12.00 15.00 18.00 21.00 24.00 27.00 St. Dev 21.8883 19.6644 17.9265 16.8147 16.4614 17.3685 21.1878 26.0514 Weights EWD 0.0165 0.0217 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWH 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWI 0.0000 0.0185 0.0826 0.1372 0.1918 0.4023 0.7225 1.0000 EWJ 0.7075 0.5300 0.3805 0.2232 0.0658 0.0000 0.0000 0.0000 G H I EWP 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 EWW S&P 500 0.0000 0.5198 0.0000 0.0000 0.0000 0.0000 0.0000 7.6932 0.0000 53.7915 0.0000 2.0854 0.0000 0.0000 0.0000 0.0000 0.0000 79.8960 0.0000 143.4662 EWL 0.0000 0.0176 0.0171 0.0168 0.0164 0.0000 0.0000 0.0000 EWP 0.0188 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 J EWW 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 0.0000 S&P 500 0.2571 0.4122 0.5198 0.6229 0.7260 0.5977 0.2775 0.0000