Task: Monthly price and return data for Apple (AAPL) and the S&P500 (^SPX) are contained in the attached Excel spreadsheet. These are end-of-month prices that include any dividend payments (although Apple stock hasn’t paid one for many years anyway).
| Date |
Price SPX |
Price AAPL |
| Dec-08 |
903.25 |
85.35 |
| Jan-09 |
825.88 |
90.13 |
| Feb-09 |
735.09 |
89.31 |
| Mar-09 |
797.87 |
105.12 |
| Apr-09 |
872.81 |
125.83 |
| May-09 |
919.14 |
135.81 |
| Jun-09 |
919.32 |
142.43 |
| Jul-09 |
987.48 |
163.39 |
| Aug-09 |
1020.62 |
168.21 |
| Sep-09 |
1057.08 |
185.35 |
| Oct-09 |
1036.19 |
188.5 |
| Nov-09 |
1095.63 |
199.91 |
| Dec-09 |
1115.1 |
210.73 |
| Jan-10 |
1073.87 |
192.06 |
| Feb-10 |
1104.49 |
204.62 |
| Mar-10 |
1169.43 |
235 |
| Apr-10 |
1186.69 |
261.09 |
| May-10 |
1089.41 |
256.88 |
| Jun-10 |
1030.71 |
251.53 |
| Jul-10 |
1101.6 |
257.25 |
| Aug-10 |
1049.33 |
243.1 |
| Sep-10 |
1141.2 |
283.75 |
| Oct-10 |
1183.26 |
300.98 |
| Nov-10 |
1180.55 |
311.15 |
| Dec-10 |
1259.78 |
325.29 |
Problem 1. Calculate the beta for AAPL using the S&P 500 returns as your market index. This will require you to calculate the covariance of AAPL’s 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 covariance…but 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!
Problem 2. 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:
σAAPL2 = βAAPL2 σS&P2 + σe2
The equation says, total risk is equal to systematic risk plus unsystematic risk. The σe2 term refers to the variance of the error terms when plotting the characteristic line. If all points fall precisely on the characteristic line, then σe2 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 σe2 using the equation.
Problem 3. 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 AAPL’s total risk can be diversified away?