Calculate optimal portfolio weights excel

The Point of the Project

You are a portfolio manager, and you are trying to put together a portfolio that is designed to beat the market (represented here by the S&P 500 index). To do this you will first pick ten stocks, and then you will figure out how much of each of them to buy, using monthly data from the last five years to make your decisions. You have 100 million dollars to play with and you will pick stocks before the start of trading on August 8th.

You will decide if you have beaten the S&P 500 by looking at the performance of your portfolio over the period August 8th–November 8th. To do this you will compare the risk-adjusted returns of your portfolio with the risk-adjusted return of the S&P. The project has three parts.

Project Part II

We are now going to find the optimal portfolio of risky stocks using historical information. You need to answer the following questions:

1. How did your stocks perform over the past five years? How volatile were your stocks? 2. How did the market do over the past 5 years? How volatile was the market? 3. Calculate the correlation matrix between all the stocks over the past five-years. Are any

of the correlations high (above .6) or low (below 0.1)? What does this tell you? 4. Find your optimal portfolio using five years of historical information. What are the

optimal portfolio weights? 5. Graph the minimum variance frontier. 6. Do your portfolio weights seem reasonable? Would you feel comfortable recommending

this investment portfolio to a client? 7. Why does it make sense to use historical information (returns, standard deviations and

covariances) as inputs to portfolio theory? Also give at least one argument against using historical information.

8. What is your optimal portfolio if you do not allow short sales? Use historical inputs. Are the weights more reasonable? Would you restrict short sales? Why or why not? Graph the MVF in this case. How does it compare to #5? Why?

9. You are an investor with an ‘ethical agenda.’ Eliminate two stocks from your portfolio. Explain why you found these companies to be ‘bad’ or ‘objectionable.’ What is the optimal portfolio now? Use historical information. How does this portfolio compare? What is the cost to you as an investor?

Make a title page that includes your name and section number and anything else you deem helpful. You should hand in a hard-copy at the beginning of class. Include answers to all the questions and any supporting material you think is helpful. Make it presentable to a “client,” or a prospective employer. It should be user friendly, concise, well-written, neat and convincing. Print only the relevant parts of the spreadsheet. The answers should be brief, but convincing. Make it

easy to find your answers. Include supporting material in the appendix. Practice good printing etiquette. Write well. The assignment should be maximum eight pages (including any appendix, but excluding the title page). Longer is not always better.

DETAILED INSTRUCTIONS

Try to follow these instructions at closely as possible.

1. How did your stocks perform over the past five years? How volatile were your stocks?

a. The last five years is from the start of trading August 8th, 2012 to the end of trading on August 7th, 2017.

b. You want to calculate the return and standard deviation for each stock over the past five years.

c. To do this, follow the instruction in Project Part I (Question 2). The only difference is that (1) the date range is different and (2) you want monthly prices (select monthly instead of daily which is the default). You should end up with 60 prices for each stock (5 years times 12 months)

2. How did the market do over the past 5 years? How volatile was the market?

Follow the instruction for Question 1 above. As discussed in Project Part I (Question 3) the market is represented by ticker ^SPX

3. Calculate the correlation matrix between all the stocks over the past five-years. Are any of the correlations very high (above .6) or very low (below 0.1)? What does this tell you?

a. You are now in a position for an initial informal inspection as to whether the assets within your risky portfolio are prudently diversified with respect to each other. To compute the Correlation Matrix of your selections, open the sheet containing your returns. You can calculate a correlation matrix using the “Correlation” option in “Data Analysis,” which can be found under the “Tools” menu. The rest is self-explanatory, but note that your data are “Grouped By” columns. The pair-wise Correlation Matrix will be computed on a separate sheet. High pair-wise correlation coefficients (approximately greater than .6) may indicate poor diversification choices. Naturally, the correlation coefficient of an asset with itself is 1. Thus, the “diagonal” of the matrix should be a bunch of ones. You are not done yet. Notice that the upper half of the matrix is all blanks. You must fix this before you can use it to figure out your optimal portfolio. There are a couple of ways to do this, but here is one I like the best. Highlight the matrix (including the upper blank spots) copy it, move your cursor somewhere below it, and from the Edit menu choose “Paste Special.” Next click the “transpose” box, and “OK.” Now highlight the new “up- side-down” matrix, copy it, move your cursor to the upper left-hand corner of your original matrix, and from the Edit menu choose “Paste Special.” Next click the “skip

blanks” box, and “OK.” Now you can delete the intermediate step. Save the sheet containing the correlation matrix.

4. Find your optimal portfolio using 5 years of historical information. What are the optimal portfolio weights?

a. Download the 3-month T-bill rates from finance.yahoo.com. Use the ticker ^IRX and follow the instruction in Question 1 above.

Note!!! T-bills are expressed differently than stocks. They are NOT prices, but expressed as an annual percentage. You have to convert the T-bill rate to monthly decimals (all other inputs are in monthly decimals). The t-bill rates are expressed as annual percentages. You must convert them into monthly decimals. For example, 5.25 might be an annual percent, and (5.25/100)/12=0.004375 would be the monthly decimal.

b. Calculate the covariance matrix. To do this, follow the earlier instructions for calculating the correlation matrix. It is exactly the same procedure except for one thing. Instead of selecting ‘Correlation’ in Data Analysis, select ‘Covariance.’ Everything else is the same.

c. The worksheet called ‘Short Sales’ contains the portfolio optimization program. It will calculate the optimal portfolio weights for you. To do this you have to copy in your inputs into the YELLOW areas.

d. Select your historical covariance matrix (NOT the correlation matrix). Then go to the top left corner of the first yellow box (Covariance Matrix) and paste it in.

e. The expected returns and standard deviations (the two yellow columns) are a little trickier. You need to get the means and standard deviations for your stocks into the these two columns. You can do this by either: (1) using the existing setup or (2) simply pasting in your individual values.

If you choose (1) then you have to understand array functions in excel or set up your spreadsheet exactly how the example is set up. The array function that is used is =TRANSPOSE(). To enter the transpose function you have to select CRTL+SHIFT+ENTER (you press all three keys simultaneously).

If you chose (2) then select the whole yellow column with the means in the example (if you don’t select all it will say arrays cannot be changed). Then press ‘delete’ and everything will vanish. Then type in all your historical returns (our best guess of

future returns i.e. expected returns). Make sure you include enough decimals. Do the same for the standard deviations.

f. We now need a risk-free rate. There are many proxies we can use for the risk-free rate. My example uses the average over the past five years as the best guess of what the risk-free rate (T-bill rate) will be in the future.

g. You can replace the tickers by pasting in your own (the examples has the ticker symbols for my ten stocks).

h. To find the optimal portfolio follow the instructions at the bottom of the spreadsheet.

5. Graph the minimum variance frontier.

a. To graph the MVF you need to select a range of expected returns. It is useful for this range to start above the stock that had the highest historical return and end below your lowest historical return. Split this range into about 10-15 intervals. This means you will have 10-15 data points (expected return, standard deviation pairs) to plot your MVF.

For example, if your best stock historically has a return of 3.5% and the worst had a return of -1.3%, you may choose a range of -2% to 4%. You can then select an appropriate interval i.e. -0.02, -0.015, -0.01…0.03, 0.035, .04.

b. For each data point you have to run solver to minimize the portfolio variance for each expected return. Specifically, solver should minimize the portfolio variance by changing the portfolio weights subject to the following constraints: (i) the portfolio weights sum to one and (ii) the expected return is equal to the specified data point (i.e. -0.2 for the first data point in the example above). Record the expected return and the minimized standard deviation. Repeat this for all 10-15 data points.

c. Graph the 10-15 data points using the tools in excel. A straightforward way is to use the chart tools in excel. To the the ‘Insert’ tab and select ‘Scatter.’ Both the second and third choices will serve you well. Then right click your chart and choose ‘Select Data’ Next click on add and select the standard deviations as your x-variable and the expected returns as your y-variable.

6. Do your portfolio weights seem reasonable? Would you feel comfortable recommending this investment portfolio to a client?

Remember that it is important to (1) diversify among the ten stocks and (2) not have any weights that are too extreme (either very large positive weights and/or negative weights).

7. Why does it make sense to use historical information (returns, standard deviations and covariances) as inputs to portfolio theory? Also give at least one argument against using historical information.

See lecture notes and the class discussion

8. What is your optimal portfolio if you do not allow short sales? Use historical inputs. Are the weights more reasonable? Would you restrict short sales? Why or why not?

Graph the MVF with short sales constraints and compare it to the MVF from Question 5. Intuitively why are they different? Which one is better?

Start by pasting your historical inputs into the yellow cells in the spreadsheet. Then use ‘Solver’ (Data Analysis) to solve for the optimal portfolio weights. Intuitively the optimal portfolio is on the CAL with the highest slope (Sharpe Ratio). So you want Solver to maximize the cell that calculates the Sharpe Ratio (this is your ‘Target Cell’). You want to select the best possible portfolio by picking portfolio weights. Therefore ‘by changing the cells’ should refer to the column of portfolio weights. Then we need to add two constraints. First, the portfolio weights must all sum to one (to make sure it is a portfolio). Second, the portfolio weights must all be nonnegative (as we do not allow short sales). Follow the detailed step-by-step instruction in the worksheet ‘Short Sales’ to implement Solver correctly.

Solver will find a solution (if you did everything right!). The optimal weights will be automatically filled into A34-A43. It will also fill in the optimized Sharpe Ratio.

Would you restrict short sales? Explain the pros and cons of shorting as discussed in class.

To graph the MVF follow the instructions #5.

9. You are an investor with a ‘moral agenda.’ Eliminate two stocks from your portfolio. Explain why you found these companies to be ‘bad’ or ‘objectionable.’ What is the optimal portfolio now? Use historical information. How does this portfolio compare? What is the cost to you as an investor?

Ethical investing is also known as ‘green investing.’

Everything is the same as in 6 above expect that (i) the short sale constraints need to be deleted and (ii) a constraint needs to be added for each stock you eliminate. (ii) is a constraint that sets the portfolio weight in the ‘bad’ stock to be zero. See the worksheet ‘Green Investing’ for details.

Compare the weights and Sharpe Ratio to the optimal portfolios obtained previously. Discuss how ‘green investing’ impacts diversification. Explain the pros and cons of green or ethical investing. 


Troubleshooting tips for #4, 8 and #9

If you get crazy weights for #4 and/or 8 and/or 9 and/or a negative Sharpe ratio, then something IS wrong.

1. Check all your inputs. All they all annual or all monthly? Is your covariance correct? Check the diagonal of your covariance matrix. Is the diagonal equal to the variance?

2. If (1) fails, introduce a lower bound to eliminate crazy weights i.e. put a short sale constraint of -1 on all your stocks. You can also place a upper bound to eliminate large positive weights if you choose i.e. 1 or 1.5 or more.

3. If you use the troubleshooting tip in (2) above, explain what you did and why you choose to do so in the project write-up.