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:

σ_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 AAPL’s 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). 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.

Date	Price SPX	Price AAPL
10/1/2016	2126.149902	109.91008
11/1/2016	2198.810059	106.986641
12/1/2016	2238.830078	112.692825
1/1/2017	2278.870117	118.073517
2/1/2017	2363.639893	133.291245
3/1/2017	2362.719971	140.387177
4/1/2017	2384.199951	140.37738
5/1/2017	2411.800049	149.279861
6/1/2017	2423.409912	141.319901
7/1/2017	2470.300049	145.941574
8/1/2017	2471.649902	160.925293
9/1/2017	2519.360107	151.824387
10/1/2017	2575.26001	166.522171
11/1/2017	2584.840088	169.290329
12/1/2017	2673.610107	167.30864
1/1/2018	2823.810059	165.529068
2/1/2018	2713.830078	176.097702
3/1/2018	2640.870117	166.551392
4/1/2018	2648.050049	164.049835
5/1/2018	2705.27002	185.501602
6/1/2018	2718.370117	184.463074
7/1/2018	2816.290039	189.624954
8/1/2018	2901.52002	226.834473
9/1/2018	2913.97998	225.740005
10/1/2018	2711.73999	218.860001

Answer 1

The complete solution looks like this:

Price SPX Price AAPL 109.91008 106.986641 112.692825 118.073517 133.291245 12.89% 140.387177 140.37738 149.279861 141.319901 145.941574 160.925293 10.27% 151.824387 166.522171 169.290329 167.30864 165.529068-1.06% 176.097702 166.551392 164.049835-1.50% 185.501602 184.463074 189.624954 226.834473 225.740005 218.860001 Returns Returns 10/1/2016 2126.149902 11/1/2016 2198.810059 7 12/1/2016 2238.830078 1/1/2017 2278.870117 2/1/2017 2363.639893 1.82% 1.79% 3.72% 2362.719971-0.04% 0.91% 2.66% 5.33% 4.77% Variance SPX Population Covariance AAPL, SPX Sample Covariance 0.0007 0.00041 0.00043 0.61251 9 10| 3/1/2017 5.32% -0.01% 6.34% 11 4/1/2017 2384.199951 5/1/2017 2411.800049 13 6/1/20172423.409912 14 7/1/2017 2470.300049 15 8/1/2017 2471.649902 16 9/1/2017 2519.360107 17 10/1/2017 2575.26001 18 11/1/2017 2584.840088 19 12/1/2017 2673.610107 20 1/1/2018 2823.810059 21 2/1/2018 2713.830078 3/1/2018 2640.870117 23 4/1/2018 2648.050049 5/1/2018 2705.27002 25 6/1/2018 2718.370117 26 7/1/2018 2816.290039 27 8/1/2018 2901.52002 28 9/1/2018 2913.97998 29 10/1/2018 2711.73999 Proportion of total Variance AAPL (Total risk) Systematic risk Unsystematic risk 12 0.00427 0.00026 0.00401 0.48% 1.93% 0.05% 1.93% 2.22% 0.37% 6.17% 93.83% 5.66% 9.68% 1.66% 3.89% 2.69% 6.38% 13.08% -0.56% 0.48% 3.03% 19.62% -0.48% 3,05% -6.94%

First step is to calculate monthly returns for both AAPL and S&P prices. This is done simply by using the formula:

$Monthly\: return = \frac{Price\: in\: current\: month - Price\: in\: previous\: month}{Price\: in\: previous\: month}$

The excel implementation looks like below:

Once, monthly returns for both are calculated, next step is to calculate beta. For this we need to calculate covariance between the two stocks and the variance of S&P (index). Both can easily be calculated using excel functions as is shown below. Finally beta is calculated using the formula:

$Beta=\frac{Covariance (AAPL, SnP)}{Variance (SnP)}$

Covariance calculation:

The covariance calculated above has to be adjusted (as mentioned in the question) by a factor of n/(n-1) which is 24/23 is this case. This is done in order to get a sample covariance as we currently working with a sample of data.

Variance calculation:

To be calculated simply by using excel function called VAR

Beta calculation:

Simply by dividing the above two figures.

The next ask of the question is to calculate the unsystematic risk. This can be calculated using the formula:

$Total\: risk=systematic\:risk +unsystematic\:risk$

or, $Variance (AAPL)=\beta ^{2}Variance(SnP)^{2} + unsystematic\:risk$

Variance of AAPL can be calculated similarly as was calculated for S&P. B has already been calculated. The excel implementation looks like below:

Systematic risk:

Unsystematic risk:

For an investor holding only AAPL stock, total risk is the measure that he should be concerned with as that figure best represents the total risk of this investment. On the other hand, a diversified investor should be concerned only with the systematic risk and the unsystematic part has already been diversified away. Unsystematic risk can be diversified away which is 93.8% of the total in this case.