Microsoft Excel LibreOffice Calc

Home Depot Inc. (HD)


Capital Asset Pricing Model (CAPM)

Difficulty: Intermediate


Rates of Return

Home Depot Inc., monthly rates of return

Microsoft Excel LibreOffice Calc
Home Depot Inc. (HD) Standard & Poor’s 500 (S&P 500)
t Date PriceHD,t1 DividendHD,t1 RHD,t2 PriceS&P 500,t RS&P 500,t3
Feb 28, 2013 68.50 1,514.68
1. Mar 31, 2013 69.78 0.39 2.44% 1,569.19 3.60%
2. Apr 30, 2013 73.35 5.12% 1,597.57 1.81%
3. May 31, 2013 78.66 7.24% 1,630.74 2.08%
. . . . . . .
. . . . . . .
. . . . . . .
70. Dec 31, 2018 171.82 -4.71% 2,506.85 -9.18%
71. Jan 31, 2019 183.53 6.82% 2,704.10 7.87%
Average: 1.70% 0.87%
Standard Deviation: 4.96% 3.18%
Home Depot Inc. (HD) Standard & Poor’s 500 (S&P 500)
t Date PriceHD,t1 DividendHD,t1 RHD,t2 PriceS&P 500,t RS&P 500,t3
Feb 28, 2013 68.50 1,514.68
1. Mar 31, 2013 69.78 0.39 2.44% 1,569.19 3.60%
2. Apr 30, 2013 73.35 5.12% 1,597.57 1.81%
3. May 31, 2013 78.66 7.24% 1,630.74 2.08%
4. Jun 30, 2013 77.47 0.39 -1.02% 1,606.28 -1.50%
5. Jul 31, 2013 79.03 2.01% 1,685.73 4.95%
6. Aug 31, 2013 74.49 -5.74% 1,632.97 -3.13%
7. Sep 30, 2013 75.85 0.39 2.35% 1,681.55 2.97%
8. Oct 31, 2013 77.89 2.69% 1,756.54 4.46%
9. Nov 30, 2013 80.67 3.57% 1,805.81 2.80%
10. Dec 31, 2013 82.34 0.39 2.55% 1,848.36 2.36%
11. Jan 31, 2014 76.85 -6.67% 1,782.59 -3.56%
12. Feb 28, 2014 82.03 6.74% 1,859.45 4.31%
13. Mar 31, 2014 79.13 0.47 -2.96% 1,872.34 0.69%
14. Apr 30, 2014 79.51 0.48% 1,883.95 0.62%
15. May 31, 2014 80.23 0.91% 1,923.57 2.10%
16. Jun 30, 2014 80.96 0.47 1.50% 1,960.23 1.91%
17. Jul 31, 2014 80.85 -0.14% 1,930.67 -1.51%
18. Aug 31, 2014 93.50 15.65% 2,003.37 3.77%
19. Sep 30, 2014 91.74 0.47 -1.38% 1,972.29 -1.55%
20. Oct 31, 2014 97.52 6.30% 2,018.05 2.32%
21. Nov 30, 2014 99.40 1.93% 2,067.56 2.45%
22. Dec 31, 2014 104.97 0.47 6.08% 2,058.90 -0.42%
23. Jan 31, 2015 104.42 -0.52% 1,994.99 -3.10%
24. Feb 28, 2015 114.75 9.89% 2,104.50 5.49%
25. Mar 31, 2015 113.61 0.59 -0.48% 2,067.89 -1.74%
26. Apr 30, 2015 106.98 -5.84% 2,085.51 0.85%
27. May 31, 2015 111.42 4.15% 2,107.39 1.05%
28. Jun 30, 2015 111.13 0.59 0.27% 2,063.11 -2.10%
29. Jul 31, 2015 117.03 5.31% 2,103.84 1.97%
30. Aug 31, 2015 116.46 -0.49% 1,972.18 -6.26%
31. Sep 30, 2015 115.49 0.59 -0.33% 1,920.03 -2.64%
32. Oct 31, 2015 123.64 7.06% 2,079.36 8.30%
33. Nov 30, 2015 133.88 8.28% 2,080.41 0.05%
34. Dec 31, 2015 132.25 0.59 -0.78% 2,043.94 -1.75%
35. Jan 31, 2016 125.76 -4.91% 1,940.24 -5.07%
36. Feb 29, 2016 124.12 -1.30% 1,932.23 -0.41%
37. Mar 31, 2016 133.43 0.69 8.06% 2,059.74 6.60%
38. Apr 30, 2016 133.89 0.34% 2,065.30 0.27%
39. May 31, 2016 132.12 0.69 -0.81% 2,096.95 1.53%
40. Jun 30, 2016 127.69 -3.35% 2,098.86 0.09%
41. Jul 31, 2016 138.24 8.26% 2,173.60 3.56%
42. Aug 31, 2016 134.12 0.69 -2.48% 2,170.95 -0.12%
43. Sep 30, 2016 128.68 -4.06% 2,168.27 -0.12%
44. Oct 31, 2016 122.01 -5.18% 2,126.15 -1.94%
45. Nov 30, 2016 129.40 0.69 6.62% 2,198.81 3.42%
46. Dec 31, 2016 134.08 3.62% 2,238.83 1.82%
47. Jan 31, 2017 137.58 2.61% 2,278.87 1.79%
48. Feb 28, 2017 144.91 5.33% 2,363.64 3.72%
49. Mar 31, 2017 146.83 0.89 1.94% 2,362.72 -0.04%
50. Apr 30, 2017 156.10 6.31% 2,384.20 0.91%
51. May 31, 2017 153.51 0.89 -1.09% 2,411.80 1.16%
52. Jun 30, 2017 153.40 -0.07% 2,423.41 0.48%
53. Jul 31, 2017 149.60 -2.48% 2,470.30 1.93%
54. Aug 31, 2017 149.87 0.89 0.78% 2,471.65 0.05%
55. Sep 30, 2017 163.56 9.13% 2,519.36 1.93%
56. Oct 31, 2017 165.78 1.36% 2,575.26 2.22%
57. Nov 30, 2017 179.82 0.89 9.01% 2,647.58 2.81%
58. Dec 31, 2017 189.53 5.40% 2,673.61 0.98%
59. Jan 31, 2018 200.90 6.00% 2,823.81 5.62%
60. Feb 28, 2018 182.27 -9.27% 2,713.83 -3.89%
61. Mar 31, 2018 178.24 1.03 -1.65% 2,640.87 -2.69%
62. Apr 30, 2018 184.80 3.68% 2,648.05 0.27%
63. May 31, 2018 186.55 1.03 1.50% 2,705.27 2.16%
64. Jun 30, 2018 195.10 4.58% 2,718.37 0.48%
65. Jul 31, 2018 197.52 1.24% 2,816.29 3.60%
66. Aug 31, 2018 200.77 1.03 2.17% 2,901.52 3.03%
67. Sep 30, 2018 207.15 3.18% 2,913.98 0.43%
68. Oct 31, 2018 175.88 -15.10% 2,711.74 -6.94%
69. Nov 30, 2018 180.32 1.03 3.11% 2,760.17 1.79%
70. Dec 31, 2018 171.82 -4.71% 2,506.85 -9.18%
71. Jan 31, 2019 183.53 6.82% 2,704.10 7.87%
Average: 1.70% 0.87%
Standard Deviation: 4.96% 3.18%

Show All

1 Data in USD $ per share of common stock, adjusted for splits and stock dividends.

2 Rate of return on common stock of HD during period t

3 Rate of return on S&P 500 (the market portfolio proxy) during period t


Systematic Risk (β) Estimation

Microsoft Excel LibreOffice Calc
VarianceHD 24.56
VarianceS&P 500 10.14
CovarianceHD, S&P 500 11.13
Correlation CoefficientHD, S&P 5001 0.71
βHD2 1.10
αHD3 0.75

Calculations

1 CovarianceHD, S&P 500 ÷ (Standard DeviationHD × Standard DeviationS&P 500)
= 11.13 ÷ (4.96 × 3.18)

2 CovarianceHD, S&P 500 ÷ VarianceS&P 500
= 11.13 ÷ 10.14

3 AverageHD – βHD × AverageS&P 500
= 1.701.10 × 0.87


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.72%
Expected rate of return on market portfolio2 E(RM) 11.98%
Systematic risk (β) of Home Depot Inc.’s common stock βHD 1.10
Expected rate of return on Home Depot Inc.’s common stock3 E(RHD) 12.89%

1 Unweighted average of bid yields on all outstanding fixed-coupon U.S. Treasury bonds neither due or callable in less than 10 years (risk-free rate of return proxy).

Calculations

2 See Details »

3 E(RHD) = RF + βHD [E(RM) – RF]
= 2.72% + 1.10 [11.98%2.72%]
= 12.89%