Exercise 5. Do this exercise in Excel or something similar. Write up your results in a few paragraphs, and include all graphs and parameters asked for. Collect five years of monthly data on two stocks, the S&P500 index, and 1-Month Treasury Securities (You can use the 1-Month Treasury Constant Maturity Rate (GS1M) on FRED. Note that the interest rate quoted for this Treasury security is quoted in annual percentage points, so you'll need to divide by 12 to get it into monthly percentage points, and then divide by 100 to get it into a decimal). That is your monthly risk-free rate. We will use the single index model to calculate the optimal risky portfolio. Compute the a, b, and o(e) for each stock using a regression of each stocks excess return on the market excess return. Make a scatterplot the excess return for each stock against the market excess return, along with the security characteristic line (your regression line/line of best fit). Then compute the expected excess return and variance for the market index (just the sample mean and variance). Use the summary of optimization procedure from the slides (also in the book in Chapter 8) to construct the optimal risky portfolio, when you have access to these two stocks and a market index fund. Exercise 5. Do this exercise in Excel or something similar. Write up your results in a few paragraphs, and include all graphs and parameters asked for. Collect five years of monthly data on two stocks, the S&P500 index, and 1-Month Treasury Securities (You can use the 1-Month Treasury Constant Maturity Rate (GS1M) on FRED. Note that the interest rate quoted for this Treasury security is quoted in annual percentage points, so you'll need to divide by 12 to get it into monthly percentage points, and then divide by 100 to get it into a decimal). That is your monthly risk-free rate. We will use the single index model to calculate the optimal risky portfolio. Compute the a, b, and o(e) for each stock using a regression of each stocks excess return on the market excess return. Make a scatterplot the excess return for each stock against the market excess return, along with the security characteristic line (your regression line/line of best fit). Then compute the expected excess return and variance for the market index (just the sample mean and variance). Use the summary of optimization procedure from the slides (also in the book in Chapter 8) to construct the optimal risky portfolio, when you have access to these two stocks and a market index fund
