7. For this exercise you would need to use the spreadsheet ps2.xls posted on the Blackboard. The file contains information about monthly returns of twelve stocks, AMGN, AMZN, STX, MSFT, BA, WMT, PFE, ABB, YHOO, AAPL, VZ, GOOG, as well as the value-weighted market index excess return (MKT-RF), and the risk-free rate (RF). The sample period begins on January 2004 and ends December 2012. The spreadsheet is set in a way that you would only need to change the values in cells H122 and T123 in order to control the portfolio weights and the stock/portfolio chosen for the regression calculation.

(a) Calculate the Beta and R2 of each stock by running a regression of the monthly returns of the stock (excess of the risk-free rate) on the monthly returns of the value-weighted market index (excess of the risk-free rate ). The latter are denoted MKT-RF and they are located on Column P (notice the risk-free rate has already been subtracted from the index). You can choose different stocks by changing the values in cell T123. This would immediately provide you with the excess returns of each stock in Column T. These returns should be used for the regression.

(b) Graph a scattered plot of the Security Market Line (SML), i.e. each point on this plot is a pair of the average returns of a stock and its Beta. Calculate the risk premium of the market index as the slope of the regression line in this graph. Is the premium statistically significant? Are these results consistent with the CAPM?

(c) Calculate the amount of idiosyncratic risk as a fraction of total risk (1- ) for five portfolios. The first portfolio includes only AMGN. The second portfolio includes the first three stocks, i.e. AMGN, AMZN, and STX. Similarly, portfolios 3 through 5 include the first six, nine, and twelve stocks, respectively. These portfolios can be chosen by changing the value of cell H122 (change also the value of T123 to 14 to run the regressions for this part).

(d) Plot the fraction of idiosyncratic risk as a function of the number of stocks in the portfolio. What is your conclusion?