The purpose of this assignment is for you to practice the Markowitz Portfolio Theory in constructing the efficient frontier and the optimal risky portfolio. follow step by step via the instructions, and formatted to look exactly like the sample. attached some of the tasks
1 Fin331 Excel Project Xiaoxin W. Beardsley Please read and follow the instructions ONE NY ONE, CAREFULLY! The purpose of this assignment is for you to practice the Markowitz Portfolio Theory in constructing the efficient frontier and the optimal risky portfolio. Enjoy and Have Fun! Format: Please follow the exact layout and format in project331sample.xlsx. One worksheet in one Excel file is all what is needed. In Excel, keep the formulas for each value you calculate (i.e., by clicking on a cell, I should be able to see the formula). Excel won’t work right if you only have numbers in the supposed-to-be-formula-embedded cells. Excel Add-in: You will need to enable Excel add-ins. In MS Excel, enabling these add-ins is done through the File | Options | Add-Ins. Select "Solver Add-ins" and click "Go". Choose ‘Solver’ and ‘Analysis Tool Pak’, and click “OK”. Once enabled, you will see both ‘Data Analysis’ and ‘Solver’ under the ‘Data’ tab in Excel. Data gathering: Each student is required to choose five securities that include two stocks and three plain vanilla (passive, index, no leverage or reverse (bull or bear, ultra)) ETFs. Write down the ETF tickers you have picked. Please make sure the three ETFs cover different industries, different geographical locations, different styles or different asset groups for better diversification effects. In Yahoo! Finance, for each of the five securities (three ETFs and two stocks), under ‘Historical Prices’, find the monthly adjusted close prices from previous 10 years (so if it is currently 2021, then choose: December, 2010 to December, 2020; if it is currently 2022, then choose: December, 2011 to December, 2021). Yahoo! Finance tells you the start date of each security, so if your security does not have enough data for the 121 monthly prices from previous 10 years, then you need to change to a different security that goes back that far. Note that Yahoo! Finance tends to mark the monthly date as the beginning of each month, while its adj. close price is actually for the end of the month, so please change the date to the end of the month and remember Returnt=(Pt-Pt-1)/ Pt-1, that is, for example, ReturnApril=(P4/30-P3/31)/P3/31, so you will need 121 monthly prices to calculate 120 monthly returns. Download the historical prices to Excel and save the ‘Date’ column and ‘Adj close’ column as an Excel worksheet. Move on to download the next security. Once you have all five securities downloaded, you can combine them into one Excel file. (1) For each security, based on the monthly adjusted close price, calculate the monthly returns. Then, based on the calculated monthly return series, calculate the average monthly return (Excel built-in function ‘average’) and standard deviation (Excel built-in function ‘stdev.s or ‘stdev’) for each security. If your security’s average monthly return is lower than the risk-free rate (see Question 8 below for its value), then you may consider switching to a different security. Though there are other methods to estimate the mean return and standard deviation for a security as we covered in class, we are using the ex post approach to accomplish it here. 2 (2) Report the correlation matrix for the securities based on their monthly return series. The correlation matrix is calculated from ‘Data’>>‘Data Analysis’ >> ‘Correlation’. Note: see if your securities are highly correlated or not, or maybe you picked a hedge asset? It is always ideal to have a hedge asset in your portfolio, so if your five securities have high correlations, I would suggest you try something else. (3) Report the variance-covariance matrix for the risky securities based on their monthly returns. Note: in this matrix, covariance (and variance), should be calculated using: stdev1*stdev2*correlation12, when security 1 is security 2, it is the variance of the security; when security 1 is not security 2, then it is the covariance between the two securities. The matrix calculated from “Data’>>‘Data Analysis’ >> ‘Covariance’ is slightly different due to a mistake in degree of freedom adjustment in Excel thus shall NOT be used. Notes for (2) and (3): The correlation matrix and covariance matrix are based on monthly returns, not monthly prices. It is helpful to have the correlation matrix as a full matrix by filling up the upper triangle with the corresponding values in the lower triangle before setting up the variance-covariance matrix and the boarded variance-covariance matrix. (4) Set up the boarded variance-covariance matrix, followed by the portfolio mean return, portfolio variance, portfolio standard deviation, and Sharpe ratio (reward-to-variability ratio, and you will need the T-bill rate in (8) for this). Notes for (4): For the left border (highlighted in blue), you can initialize the five cells with some random numbers. For the upper border, each cell needs to be set equal to the corresponding cell in the left border by ‘=cell xyz’ function. For (4), I should be able to see your formula for each green cell (as marked in the project331sample file). (5) Using Excel Solver, find the global minimum variance portfolio (Global MVP, G). Copy and Paste (use paste special>>values) the weights, portfolio mean return and portfolio standard deviation to the designated row in Table1. (6) Assume short sale is allowed. Starting from Global MVP (G), try 10 different expected portfolio return levels above G, and 2 below G, using Excel ‘Solver’, find the weight on each security for the minimum variance portfolio (MVP) corresponding to each expected portfolio return level. Find also the risk of the portfolio (as measured by portfolio standard deviation). Copy and Paste (use paste special >> values) the weights, portfolio mean return and portfolio standard deviation to the designated rows in Table 1. (7) Assume short sale is NOT allowed. Repeat (6) and re-run Solver for each level of the same 12 expected portfolio returns as in (6). This time, you need to check the box titled ‘Make Unconstrained Variables Non-Negative’ in the Solver window (think why?) Copy and Paste (use paste special>>values) the weights, portfolio mean return and portfolio standard deviation to the designated rows in Table 2. 3 It is possible not to be able to find a solution this time, and possible to have the same answers as in Table 1 for some expected return levels. Think about why? Answer them in the designated area in the Excel file. Note for (6) and (7): It is good practice to choose some portfolio expected return levels in the neighborhood of G because the frontier has the greatest curvature in that region. It is also good practice to choose a couple portfolio expected return levels that are higher than your highest security. You can play around with the different levels of expected portfolio mean returns and come up with the entire frontier. (8) Now add the risk-free asset into the picture. Find the current 10-year US T-Note annual rate, and remember to convert it into a monthly rate (since your data is monthly). Assume short sale is allowed as in (6), what is the optimal risky portfolio you shall choose? Call the optimal risky portfolio P*, what is P*’s expected return and risk? Copy and Paste (use paste special>>values) the weights, portfolio mean return and portfolio standard deviation for P to the designated row in Table 1. (9) From the results of (6), (7) and (8), in Excel, draw the unrestricted and the restricted minimum variance frontier onto ONE graph. Mark which one is the unrestricted frontier when short is allowed, and which one is the restricted frontier when short is not allowed. Mark the underlying five securities, the Tangent CAL and the optimal risky portfolio (P*) on the graph as well. Note for (9): to draw the graph, you need to choose the ‘Scatter’ graph to insert and then select data for your ‘series’ of no short allowed, short allowed and every security. Remember X is stdev and Y is return. (10) What is the relation between the two frontiers and the underlying securities? Explain the reason for such a relation under the graph. (11) How should an investor form his/her complete portfolio that includes the risk free asset and the five risky securities? Type your answer under Q11. Fin331 Excel Project Portfolio Variance Portfolio Std. Dev. Portfolio Mean Return Sharpe Ratio Risk free rate TABLE 1 Solutions for Targeted Returns when short sale is allowed Portfolio Std.Dev. wa wb wc wd we portfolio -2 portfolio -1 Global MVP (G) portfolio 1 portfolio 2 portfolio 3 portfolio 4 portfolio 5 portfolio 6 portfolio 7 portfolio 8 portfolio 9 portfolio 10 B C D E F G H I 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 MVF