Optimization in the Index Model

Summary: In this homework you will obtain data for two stocks and a market portfolio. You will then construct optimal risky portfolios of these three assets using the Index Model. We will use the data to estimate correlations, volatilities, and betas, but we will make assumptions about the expected returns (and alphas).

Obtaining Data:

You will need to download return data for 2 stocks. They should be relatively large companies with very similar business models (e.g. Home Depot and Lowes), but the choice is up to each group. You should download 5-years of monthly data (can easily be done from yahoo).

Additionally, you can get the monthly excess returns to the aggregate market as well as the monthly risk free rate from the excel file on canvas.

Step 1: Obtain and analyze your two stocks)

Using your five years of monthly data you should construct monthly holding period returns as well as excess returns. To do this you should do the following:

Start with the adjusted close price for each month.

Use the formula (Change in Price) / (Previous Price) to construct monthly returns for each month in the sample.

For each month, subtract off the monthly risk-free rate.

Perform standard market index regressions using the monthly data for the two stocks. You do this by regressing their excess returns (Y-variable) on the monthly excess returns to the Market (X-variable). (Use can use Excels regression package, you may have to install the data analysis add-on).

For each month calculate the residual from the index model regressions for the two stocks. (Note: This is simply R_{i i i} R_M, where R_i is each monthly excess return to the stock, the alpha and beta come from your regression, and R_M is each days excess market return. It is easiest to just ask excel to output these residuals.) Calculate the standard deviation of these monthly residuals. This is a monthly standard deviation, so you can annualize this value by multiplying by sqrt(12).

As a final piece you will need the standard deviation of the market portfolio. Simply take the standard deviation of the markets excess returns over the five year period. Again you can annualize by multiplying by sqrt(12)

Step 2: Construct Optimal Risky Portfolios

In this part we will construct optimal risky portfolios out of the Market and your two stocks. To do this we need to make some assumptions about expected returns. In theory we could use the past returns (and alphas) to do this, but in practice that is likely to lead us astray. So instead we will assume the following.

Alpha of Stock 1 and Stock 2: 1% a year, Expected Excess Return of Market: 6% a year

Construct an optimal risky portfolio

This time you will need: A) Annualized standard deviations of the Market from your analysis in Part 1. B) The betas of the two stocks. C) The firm-specific standard deviations of the two stocks (the annualized standard deviations of the residuals). (Note that we assume the market risk premium (6%) and alphas (1%) above.

Follow the excel examples we have done previously to calculate the sharpe ratio of a hypothetical portfolio of the two stocks and the market. Then use solver to maximize this portfolios sharpe ratio.

Write up:

You should provide a write up (either document or in a single readable excel sheet) that includes the following. (Note explanations can be quite short, a sentence or two).

Report the output from your two regressions. Are the alphas significant? Interpret the betas of your firms. Do they make sense? Are they surprising?

Report the weights and predicted sharpe ratio of your optimal risky portfolio from your optimization. Do they make sense?

Bonus: You can calculate the True standard deviation of your portfolio as follows. First, construct a portfolio return in each month by multiplying your optimal weights times the returns to each of the three assets (market, stock 1 and stock 2). For example if your weights are .5 in the market, .25 in stock 1, and .25 in stock 2. The return in each month to you your portfolio will be .5*R_Mkt + .25*R_Stock1 + .25*R_Stock2. Calculate this return for each of the 60 months and then take the standard deviation of these returns.

Now compare this to the actual standard deviation of your optimal portfolio to the standard deviation predicted by the index model (the denominator in your sharpe ratio calculation). Is it higher or lower? What assumption does the index model make about your two stocks and what potential error does this cause?

How does the assumption of the index model impact your optimal portfolio choice? In other words, if you fixed the error the index model is making, how would that change your weights in stock 1 and stock2?