This Excel project is a great practice of the Markowitz Portfolio Theory. 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 wont 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 Office 2010, 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 ETFs. One example to find available ETFs is to go to Morningstar > ETFs> ETF Performance Table> show complete. 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 2014, then choose: December, 2003 to December, 2013; if it is currently 2015, then choose: December, 2004 to December, 2014). 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. Remember Returnt=(Pt-Pt-1)/ Pt-1, so you need 121 monthly prices to calculate 120 monthly returns. Download the historical prices to Excel by clicking Download to Spreadsheet underneath the table, 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 securitys 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, we are using the ex post approach to accomplish it here.

(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?

(3) Report the variance-covariance matrix for the risky securities based on their monthly returns. The variance-covariance matrix is calculated from Data>>Data Analysis >> Covariance.

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 variance-covariance matrix as a full matrix by filling up the upper triangle with the corresponding values in the lower triangle before setting up the boarded variance-covariance matrix below.

(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 in each security for the minimum variance portfolio (MVP) corresponding to each 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 12 expected portfolio returns. This time, you need to check the box titled Make Unconstrained Variables Non-Negative in the Solver window. 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. According to Bloomberg, the most recently issued 10-year T-Note had a rate of 0.2204% per month. We will use this number as the risk free rate. 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 Ps 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 and the Tangent CAL on the graph as well.

Note for (9): to draw the graph, you can click: Insert, Scatter, 2nd graph, right click, Select data, Add.

(10) What is the relation between the two frontiers and the underlying assets? 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.