Monthly price data for Apple (AAPL) and the S&P500 (^SPX) are contained in the a

Question

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 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!

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.

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?

There is a spreadsheet attached to this assignment.

Explanation / Answer

Date Price SPX Price AAPL Monthly Return Sep-10 1218.89 374.27 Oct-10 1131.42 370.85 -7.18% -0.91% Nov-10 1253.3 393.67 10.77% 6.15% Dec-10 1246.96 371.71 -0.51% -5.58% Jan-11 1257.6 393.88 0.85% 5.96% Feb-11 1312.41 443.95 4.36% 12.71% Mar-11 1365.68 527.55 4.06% 18.83% Apr-11 1408.47 583.09 3.13% 10.53% May-11 1397.91 567.95 -0.75% -2.60% Jun-11 1310.33 561.87 -6.27% -1.07% Jul-11 1362.16 567.97 3.96% 1.09% Aug-11 1379.32 594 1.26% 4.58% Sep-11 1406.58 649.76 1.98% 9.39% Oct-11 1440.67 651.58 2.42% 0.28% Nov-11 1412.16 581.47 -1.98% -10.76% Dec-11 1416.18 574.27 0.28% -1.24% Jan-12 1426.19 522.16 0.71% -9.07% Feb-12 1498.11 446.92 5.04% -14.41% Mar-12 1514.68 435.62 1.11% -2.53% Apr-12 1569.19 436.86 3.60% 0.28% May-12 1597.57 436.98 1.81% 0.03% Jun-12 1630.74 446.78 2.08% 2.24% Jul-12 1606.28 393.93 -1.50% -11.83% Aug-12 1685.73 449.56 4.95% 14.12% Sep-12 1638.17 491.7 -2.82% 9.37% Variance 0.14% 0.71% COVar P 0.10% COVAR S 0.11% Beta 0.75 ?AAPL2 = ?AAPL2?S&P2 + ?e2 .71 = .75*.14+x 0.105 0.605 x = .60 Systematic Risk 0.11 When the Investment is to be made only in AAPL the investor will look into the systematic risk which is around .11, while when investing as part of portfolio the investor will look into the unsystematic risk and which is .60 The unsystematic risk can be diversified by investing in other business.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.