Finance Project

FINANCE 310

INVESTMENTS

Fall 2022

PROJECT

PART II

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 4^th 2022.

You will decide if you have beaten the S&P 500 by looking at the performance of your portfolio over the period August 4^th to November 4^th in 2022. 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

Pick ten stocks. The stocks must all be part of the S&P 500 index. Make sure the stocks have at least five years of pricing data.

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

• (1.5 points) How did your stocks perform over the past five years? How volatile were your stocks?
• (1.5 points) How did the market do over the past 5 years? How volatile was the market?
• (2 points) 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?
• (2.5 points) Find your optimal portfolio using five years of historical information. What are the optimal portfolio weights?
• (2.5 points) Graph the minimum variance frontier.
• (2 points) Do your portfolio weights seem reasonable? Would you feel comfortable recommending this investment portfolio to a client?
• (1.5 points) 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.
• (2.5 points) 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?
• (2 points) 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?

P. (2 points) Presentation (i.e., see the next page).

Make a title page that includes your name and section number and anything else you deem helpful. You should hand the project in on Blackboard. You should upload a PDF file only. 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.

The peer evaluation form must be submitted by the deadline of the Project; failure to submit the Peer Evaluation Form will results in a penalty of 25% of your individual grade. Peer Evaluations are submitted on BB. If I have convincing evidence that one (or more) members did not adequately participate in the case assignment, I will make appropriate adjustments to that member(s) score.

DETAILED INSTRUCTIONS

Try to follow these instructions at closely as possible.

• How did your stocks perform over the past five years? How volatile were your stocks?
• How did the market do over the past 5 years? How volatile was the market?
• 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?
• Find your optimal portfolio using 5 years of historical information. What are the optimal portfolio weights?
• Graph the minimum variance frontier.
• Do your portfolio weights seem reasonable? Would you feel comfortable recommending this investment portfolio to a client?
• 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.
• 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?
• 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?

• The last five years is from the start of trading August 3rd, 2017 to the end of trading on August 3rd, 2022. In practice the data is monthly so the exact day will not matter.
• You want to calculate the return and standard deviation for each stock over the past five years.
• To do this, first download historical data for each stock. Go to finance.yahoo.com and type in the stock’s ticker symbol (or name). In the left column click on ‘Historical Prices.’ In ‘Set Date Range’ select the relevant range. Click ‘Get Prices.’ Select ‘Download to Spreadsheet.’ Excel will now open with the date you selected. Repeat these steps for all ten stocks.
• In total you should now have ten spreadsheets with historical prices for the past month. It may be helpful to consolidate these ten spreadsheets into one sheet by copying the ‘Adjusted Close’ columns for each stock into a new spreadsheet. You are interested in the ‘Adjusted Close’ column. Yahoo! downloads price data that have been adjusted for dividends and stock splits. So you won’t have to worry about either of these events. You should end up with 60 prices for each stock (5 years times 12 months).
• You now have prices for each, but you are interested in returns. So we have to calculate daily holding period returns (HPR)! To do this you want to use the formula for HPR from the slides. Note that we can disregard dividends as we are using the adjusted close prices (see above). The daily HPR for a stock is the price today minus the price yesterday. To find the average (the mean) HPR over the last five years use the AVERAGE function in excel. You implement this by selecting a cell and typing: =AVERAGE(<select the cell range with the individual HPRs you calculated!>). Then click ENTER.
• To assess how volatile each stock was standard deviation for each stock using monthly stock returns as inputs. Use the STDEV.S function (sample standard deviation).

Follow the instruction for Question 1 above. The market is represented by ticker ^SPX.

Unfortunately, finance.yahoo no longer allows you to conveniently download futures prices and therefore the ‘download’ option is not available for ^SPX. A workaround is to instead down the prices for an ETF (exchange traded fund) that tracks the S&P 500 index. Examples of such an ETF are Vanguard S&P 500 (ticker: VOO), iShare Core S&P 500 ETF (ticker: IVV) etc.

• 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.

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(<cells with your historical means in a different spreadsheet>). 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.

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.

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).

See lecture notes and the class discussion

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.

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.

• Check all your inputs. Are they all annual or all monthly? Is your covariance correct? Check the diagonal of your covariance matrix. Is the diagonal equal to the variance? It is often useful to use ‘paste special’ and ‘values’ when moving your covariance matrix around (this gets rid of the formulas on the diagonal).
• Download the example file again, paste in your inputs and run solver again.
• If (1) and (2) fail, 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 an upper bound to eliminate large positive weights if you choose i.e. 1 or 1.5 or more.
• 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.