You have just started working for a financial planning firm and have been asked to analyze the performance and risk of the following 10 stocks to decide on the optimal portfolio for your client:
The stocks (tickers) are:
IBM (IBM)
Caterpillar (CAT)
Autodesk (ADSK)
Apple (AAPL)
Ebay (EBAY)
Goldman Sachs (GS)
J.P. Morgan (JPM)
Boeing (BA)
Walmart (WMT)
Hershey (HSY)
S&P 500 (GSPC)
13 Week Treasury Bill
1) Collect information on the dividend-adjusted price of each stock and the S&P 500 index and the risk-free rate from Yahoo (finance.yahoo.com) as follows:
* Enter the stock symbol, and click “historical data” on the right tab. Enter start data September 1, 2012 and end data September 1, 2017 to get five years of data. Click on show “historical prices” and frequency “monthly”.
* Click on “apply” and “download data”. Save the “date” and the “adj close” column and delete all other columns. Continue repeating step b) until you have downloaded all the tickers and keep pasting the “adj close” in columns next to each other, labeling each column with the ticker, except the S&P500 index should be labeled as themarket “MKT” and the 13-week Treasury bill as the risk free rate “RF”. Label the tab as “DATA”.
* Convert these dividend-adjusted prices to monthly returns in a separate spreadsheet within your Excel file. Name this tab as “RETURNS”. Create return series from September 1, 2012 to September 1, 2017 for each stock and the S&P500 index. Convert the annualized T-bill rate which is quoted in percentage points to monthly return by dividing by 1,200 (there are 12 months per year and 100 percentage points per unit return). Format all the returns as “percentage” with 2 decimal places.
2.) Compute the mean monthly return and standard deviation for each of the 10 stocks and the S&P500 index. Convert the monthly statistics to annual for easier interpretation (multiply the mean monthly return by 12, and the standard deviation by the square root of 12).
* Using the annual statistics, create an Excel plot with the standard deviation (volatility) on the x-axis and average return on the y-axis. Do you see any relation between the volatility and return of the individual stocks? Why (why not)?
* Note: The easy way to create a scatter plot with mean return on the y-axis and SD on the x-axis is to reorder the rows in Excel, putting SD in the row above mean, highlighting the two rows and choosing an “XY scatter plot”.
3.) Copy the “RETURNS” worksheet and rename it as “PORTFOLIO” (right click on the “RETURNS” tab, click move or copy, copy). Keep only the returns for the 10 stocks and delete the other series. Your manager would like you to construct a portfolio from the 10 stocks. He suggests that you first try an equal weighted portfolio, using the weight of 0.1 for each stock, and determine the portfolio’s average return and standard deviation. (Hint: create a column called “portfolio” to the right of your stocks, multiplying the stock return on each day by the weight). Then compute the average portfolio return and standard deviation. As before, your manager would like you to express the portfolio return and standard deviation on an annual basis. Your manager also told you to include a control sum of the weights in your spreadsheet to make sure that they sum to one.
4.) You believe that you can do better than the equal weighted portfolio suggested by your manager. You will surprise your manager with a portfolio using the optimum weights that will provide the best risk and return combinations for the 10 stocks in your portfolio. Use the solver function in Excel to perform this analysis.[1] Compute the efficient frontier by minimizing the standard deviation for each level of return. First, use the solver to solve for the absolute minimum standard deviation you can obtain by combining the 10 stocks as follows: “Set objective” to the cell where you have theportfolio standard deviation, and check “min” in the menu of choices to minimize the standard deviation. Choose “By changing variable cells” by highlighting all the 10 portfolio weights to tell Excel to look for the optimal weights. Add a constraint by clicking the Add button next to “Subject to constraints”. For the moment, the only constraint is that the weights have to sum to one. So point on the cell where you have the sum of weights, and specify that the cell must equal one. Make sure that the box “make unconstrained variables non-negative” is NOT checked because your weights can be negative in case of short-selling. Now compute the portfolio with the lowest standard deviation. Report the annualized minimum return and minimum standard deviation. Copy the result somewhere aside so you can use the same cells for further analysis.
5.) Now compute the minimum standard deviation for each of the following return levels so you can plot the efficient frontier: 0%, 5%, 10%, 15%, 20%…50%. This calculation is performed in the same way as before, except you add one more constraint on the return. Add a constraint by clicking the Add button next to “Subject to constraints”. Point on the cell where you have the portfolio average return, and specify that the cell must equal 0%, 5%, 10%, 15%, 20%…50%. Keep copying and pasting the result of the optimization in a separate table to build the efficient frontier. Plot the efficient frontier by creating and XY scatter plot (similar to the one you did for the return and standard deviation of each stock, except connecting the dot). The standard deviation should be on the X axis and the return on the Y axis. The easiest way to achieve this is to copy the standard deviation in the row just above the returns, highlight the cells, and click on the scatter plot. How does your efficient frontier compare to the equal-weighted portfolio from c) above? Is the equal-weighted portfolio efficient?
6.) Redo the analysis by adding a new risk-free security as security #11 in your portfolio. Begin by setting up a new worksheet in your Excel file by copying the “PORTFOLIO” worksheet and renaming it as “PORTFOLIO with risk free”. The risk free security you consider has a return of 0.1% each month. (Note: Do NOT use the riskfree rate you downloaded for this problem because its standard deviation is not strictly zero). Include a weight for this security when calculating the monthly portfolio return. That is, there will now be 11 weights making up your portfolio. Again, the 11 weights must sum to one. Compute the minimum standard deviation for each of the following return levels so you can plot the efficient frontier: 1.2%, 5%, 10%, 15%, 20%…50%. (note that the minimum return is now 1.2%, corresponding to the risk-free rate). Plot this efficient portfolio along the previous efficient frontier without the risk free rate. How does this efficient frontier compare to the previous one?
7.) Load your data into Stata or similar econometric software for further analysis.
* Adjust all returns by subtracting the risk-free rate. Estimate the CAPM alpha and beta for each of the 10 stocks using the S&P500 as the market index and 13-week T-bill rate you downloaded as the risk-free rate.
* Report the estimated alphas (annualize the alphas by multiplying by 12) and the 95% confidence interval for the alphas (annualize the standard errors by multiplying by the square root of 12 before computing the confidence intervals). According to your estimates, did any of the stocks earn a significant positive alpha? In an efficient market, do you expect that the estimated alphas are informative about future alphas?
* Create a plot with the beta on the x-axis and average return (not the alpha) on the y-axis. (either in Stata or Excel) Do you see any relation between the beta and return of the individual stocks? Did you expect to see any? Why (why not)?
