Monthly price data for Apple (AAPL) and the S&P500 (^SPX) are contained in the attached Excel spreadsheet. These are adjusted end-of-month prices that incorporate any dividend payments.

Calculate monthly returns for AAPL and the S&P using the adjusted end of month prices.

Calculate the beta for AAPL using the S&P 500 returns as your market index. Use the formula in the notes. This will require you to calculate the covariance of AAPLs returns with those of the S&P 500 and the variance of the S&P 500 returns^*. You can use the VAR function in EXCEL to compute return variance. You can also use the COVAR function to compute covariancebut you need to make an adjustment. The COVAR function provides the population covariance while we want the sample covariance. To convert, multiply the Excel stat by n/(n-1), or 24/23 in this example. Now, you have a sample covariance!

Calculate variance for the 24 monthly returns for AAPL You should already have the variance for the returns for the S&P 500. Total risk for a stock can be partitioned into systematic and unsystematic portions using the following equation:

?_AAPL² = ?_AAPL²?_S&P² + ?_e²

The equation says, total risk is equal to systematic risk plus unsystematic risk. The ?_e² term refers to the variance of the error terms when plotting the characteristic line. If all points fall precisely on the characteristic line, then ?_e² will be zero and all risk is systematic. If you have beta and variance for AAPL and the S&P, you can calculate the value for ?_e² using the equation.

Calculate the proportion of total risk (variance) in the returns for AAPL that is systematic and unsystematic. Which measure of risk is most relevant for an investor holding AAPL as his or her only investment? Which measure is most relevant for an investor holding the stock as part of a well diversified portfolio? What proportion of AAPLs total risk can be diversified away?

There is a spreadsheet attached to this assignment.

^*Note: we are using returns for AAPL and the S&P to construct AAPL's beta. A more precise method would be to use excess returns (returns for AAPL minus the T-bill rate and returns for the S&P minus the T-bill rate). Your text employs the excess return approach (see pp. 203-207). Since T-bill rates are so minimal during the period we are examining, there is little loss of precision if we use actual returns rather than excess returns.