Question

Risk and Return

Use the following data to explore the risk-return relation and the concept of beta for Apple stock, Kroger stock, and the S&P 500 market index:

Year	Apple Stock Price	Kroger Stock Price	S&P 500 Market Index
2020	$252.92	$30.71	3,234.85
2019	$156.23	$27.66	2,531.94
2018	$169.23	$27.32	2,753.15
2017	$125.17	$29.52	2,276.98
2016	$108.41	$31.80	2,043.94
2015	$112.98	$33.37	2,058.20

Part 1: Risk and Beta

1. Calculate the return each year for Apple, Kroger, and the Market using the equation:

Return = (Value this year – Value last year) / Value last year. In addition, use the Excel function “Average” to find the average of the returns. (10 Points)

Year	Apple Returns	Kroger Returns	S&P 500 Market Index Returns
2020
2019
2018
2017
2016

Average Returns

2. Calculate the standard deviation of rates of return for Apple, Kroger, and the Market using the “Stdev” Excel function. (10 Points)

Apple Standard Deviation of Returns	Kroger Standard Deviation of Returns	S&P 500 Market Standard Deviation of Returns

3. Make a scatter plot of stock returns (y-axis) against market returns (x-axis) for both Apple and Kroger stock in one plot. Add a linear trend line to the scatter plot for each stock and include the equation on the chart (Select data point and right click then select "add trendline" and choose a "linear" trend and select "display equation on chart"). Label the y-axis, x-axis, legend, and chart title. (10 Points)

4. For each stock, use the Excel function “Correl” to calculate the correlation between the stock returns and market returns. Furthermore, copy the standard deviations from above (by cell reference) and calculate the beta according to the equation: Beta = (Standard deviation of stock / Standard deviation of market) (Correlation between stock and market). (10 Points)

	Correlation	Stock Standard Deviation (in decimal form)	Market Standard Deviation (in decimal form)	Beta
Apple
Kroger

Answer 1

Solution:

The standard deviation measures the volatility of a data in relation to its average/mean and is calculated as the square root of the variance. In case the data is further from the mean, it will have higher standard deviation, hence more volatility. Hence, greater the standard deviation of a stock return, the greater would be the volatility of the stock.

Year	Stock prices	Stock return
Apple stock price	Kroger stock price	S&P 500 Market index	Apple stock return	Kroger stock return	S&P 500 Market return
2020	252.92	30.71	3,234.85	61.89%	11.03%	27.76%
2019	156.23	27.66	2,531.94	-7.68%	1.24%	-8.03%
2018	169.23	27.32	2,753.15	35.20%	-7.45%	20.91%
2017	125.17	29.52	2,276.98	15.46%	-7.17%	11.40%
2016	108.41	31.80	2,043.94	-4.04%	-4.70%	-0.69%
2015	112.98	33.37	2,058.20
		Average return	20.16%	-1.41%	10.27%
		Standard deviation	28.94%	7.78%	14.80%

Another method to calculate standard deviation using formula is as follows:

Year	Stock return	Variance	Variance^2
Apple stock return	Kroger stock return	S&P 500 Market return	Apple stock return	Kroger stock return	S&P 500 Market return	Apple stock return	Kroger stock return	S&P 500 Market return
2020	61.89%	11.03%	27.76%	41.72%	12.44%	17.49%	17.41%	1.55%	3.06%
2019	-7.68%	1.24%	-8.03%	-27.85%	2.66%	-18.30%	7.75%	0.07%	3.35%
2018	35.20%	-7.45%	20.91%	15.04%	-6.04%	10.64%	2.26%	0.36%	1.13%
2017	15.46%	-7.17%	11.40%	-4.70%	-5.76%	1.13%	0.22%	0.33%	0.01%
2016	-4.04%	-4.70%	-0.69%	-24.21%	-3.29%	-10.96%	5.86%	0.11%	1.20%
2015
Average return	20.16%	-1.41%	10.27%		Variance	8.38%	0.61%	2.19%
Standard deviation	28.94%	7.78%	14.80%	Standard deviation	28.94%	7.78%	14.80%

The variance is determined by subtracting the value of the mean from each data point. Each of those values is then squared. The square values are then added together, resulting in a total of 33.51% for Apple, which is then divided by the value of N minus 1, which is 4, resulting in a variance approximately of 8.38% for Apple stock and so on.

The square root of the variance is then calculated, which results in a standard deviation measure of approximately 28.94% for Apple stock and so on.

Year	Stock prices	Stock return
Apple stock price	Kroger stock price	S&P 500 Market index	Apple stock return	Kroger stock return	S&P 500 Market return
2020	252.92	30.71	3,234.85	61.89%	11.03%	27.76%
2019	156.23	27.66	2,531.94	-7.68%	1.24%	-8.03%
2018	169.23	27.32	2,753.15	35.20%	-7.45%	20.91%
2017	125.17	29.52	2,276.98	15.46%	-7.17%	11.40%
2016	108.41	31.80	2,043.94	-4.04%	-4.70%	-0.69%
2015	112.98	33.37	2,058.20
		Average return	20.16%	-1.41%	10.27%
		Standard deviation	28.94%	7.78%	14.80%
		Correlation	0.97	0.29
		Beta	1.96	0.53

	Correlation	Stock standard deviation
Apple	0.97	28.94%
Korger	0.29	7.78%

Apple stock has correlation near to 1 and stock deviation of 28.94% and Beta of 1.96. This indicates stock volatility is higher as compare with Korger.