For this exercise you would need to use the spreadsheet ps$2.$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 H$122$ and T$123$ in

order to control the portfolio weights and the stock$/$portfolio chosen for the regression calculation.

$($a$)$ Calculate the Beta and

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 T$123.$ 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.

Page $4$ of $4$

These portfolios can be chosen by changing the value of cell H$122($change also the value of T$123$ 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?