Microsoft Excel LibreOffice Calc

Home Depot Inc. (NYSE:HD)


Capital Asset Pricing Model (CAPM)

Intermediate level

Capital asset pricing model (CAPM) indicates what should be the expected or required rate of return on risky assets like Home Depot Inc.’s common stock.


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, 2014 82.03 1,859.45
1. Mar 31, 2014 79.13 0.47 -2.96% 1,872.34 0.69%
2. Apr 30, 2014 79.51 0.48% 1,883.95 0.62%
3. May 31, 2014 80.23 0.91% 1,923.57 2.10%
. . . . . . .
. . . . . . .
. . . . . . .
70. Dec 31, 2019 218.38 1.36 -0.35% 3,230.78 2.86%
71. Jan 31, 2020 228.10 4.45% 3,225.52 -0.16%
Average: 1.77% 0.83%
Standard deviation: 5.05% 3.24%
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, 2014 82.03 1,859.45
1. Mar 31, 2014 79.13 0.47 -2.96% 1,872.34 0.69%
2. Apr 30, 2014 79.51 0.48% 1,883.95 0.62%
3. May 31, 2014 80.23 0.91% 1,923.57 2.10%
4. Jun 30, 2014 80.96 0.47 1.50% 1,960.23 1.91%
5. Jul 31, 2014 80.85 -0.14% 1,930.67 -1.51%
6. Aug 31, 2014 93.50 15.65% 2,003.37 3.77%
7. Sep 30, 2014 91.74 0.47 -1.38% 1,972.29 -1.55%
8. Oct 31, 2014 97.52 6.30% 2,018.05 2.32%
9. Nov 30, 2014 99.40 1.93% 2,067.56 2.45%
10. Dec 31, 2014 104.97 0.47 6.08% 2,058.90 -0.42%
11. Jan 31, 2015 104.42 -0.52% 1,994.99 -3.10%
12. Feb 28, 2015 114.75 9.89% 2,104.50 5.49%
13. Mar 31, 2015 113.61 0.59 -0.48% 2,067.89 -1.74%
14. Apr 30, 2015 106.98 -5.84% 2,085.51 0.85%
15. May 31, 2015 111.42 4.15% 2,107.39 1.05%
16. Jun 30, 2015 111.13 0.59 0.27% 2,063.11 -2.10%
17. Jul 31, 2015 117.03 5.31% 2,103.84 1.97%
18. Aug 31, 2015 116.46 -0.49% 1,972.18 -6.26%
19. Sep 30, 2015 115.49 0.59 -0.33% 1,920.03 -2.64%
20. Oct 31, 2015 123.64 7.06% 2,079.36 8.30%
21. Nov 30, 2015 133.88 8.28% 2,080.41 0.05%
22. Dec 31, 2015 132.25 0.59 -0.78% 2,043.94 -1.75%
23. Jan 31, 2016 125.76 -4.91% 1,940.24 -5.07%
24. Feb 29, 2016 124.12 -1.30% 1,932.23 -0.41%
25. Mar 31, 2016 133.43 0.69 8.06% 2,059.74 6.60%
26. Apr 30, 2016 133.89 0.34% 2,065.30 0.27%
27. May 31, 2016 132.12 0.69 -0.81% 2,096.95 1.53%
28. Jun 30, 2016 127.69 -3.35% 2,098.86 0.09%
29. Jul 31, 2016 138.24 8.26% 2,173.60 3.56%
30. Aug 31, 2016 134.12 0.69 -2.48% 2,170.95 -0.12%
31. Sep 30, 2016 128.68 -4.06% 2,168.27 -0.12%
32. Oct 31, 2016 122.01 -5.18% 2,126.15 -1.94%
33. Nov 30, 2016 129.40 0.69 6.62% 2,198.81 3.42%
34. Dec 31, 2016 134.08 3.62% 2,238.83 1.82%
35. Jan 31, 2017 137.58 2.61% 2,278.87 1.79%
36. Feb 28, 2017 144.91 5.33% 2,363.64 3.72%
37. Mar 31, 2017 146.83 0.89 1.94% 2,362.72 -0.04%
38. Apr 30, 2017 156.10 6.31% 2,384.20 0.91%
39. May 31, 2017 153.51 0.89 -1.09% 2,411.80 1.16%
40. Jun 30, 2017 153.40 -0.07% 2,423.41 0.48%
41. Jul 31, 2017 149.60 -2.48% 2,470.30 1.93%
42. Aug 31, 2017 149.87 0.89 0.78% 2,471.65 0.05%
43. Sep 30, 2017 163.56 9.13% 2,519.36 1.93%
44. Oct 31, 2017 165.78 1.36% 2,575.26 2.22%
45. Nov 30, 2017 179.82 0.89 9.01% 2,647.58 2.81%
46. Dec 31, 2017 189.53 5.40% 2,673.61 0.98%
47. Jan 31, 2018 200.90 6.00% 2,823.81 5.62%
48. Feb 28, 2018 182.27 -9.27% 2,713.83 -3.89%
49. Mar 31, 2018 178.24 1.03 -1.65% 2,640.87 -2.69%
50. Apr 30, 2018 184.80 3.68% 2,648.05 0.27%
51. May 31, 2018 186.55 1.03 1.50% 2,705.27 2.16%
52. Jun 30, 2018 195.10 4.58% 2,718.37 0.48%
53. Jul 31, 2018 197.52 1.24% 2,816.29 3.60%
54. Aug 31, 2018 200.77 1.03 2.17% 2,901.52 3.03%
55. Sep 30, 2018 207.15 3.18% 2,913.98 0.43%
56. Oct 31, 2018 175.88 -15.10% 2,711.74 -6.94%
57. Nov 30, 2018 180.32 1.03 3.11% 2,760.17 1.79%
58. Dec 31, 2018 171.82 -4.71% 2,506.85 -9.18%
59. Jan 31, 2019 183.53 6.82% 2,704.10 7.87%
60. Feb 28, 2019 185.14 0.88% 2,784.49 2.97%
61. Mar 31, 2019 191.89 1.36 4.38% 2,834.40 1.79%
62. Apr 30, 2019 203.70 6.15% 2,945.83 3.93%
63. May 31, 2019 189.85 -6.80% 2,752.06 -6.58%
64. Jun 30, 2019 207.97 1.36 10.26% 2,941.76 6.89%
65. Jul 31, 2019 213.69 2.75% 2,980.38 1.31%
66. Aug 31, 2019 227.91 6.65% 2,926.46 -1.81%
67. Sep 30, 2019 232.02 1.36 2.40% 2,976.74 1.72%
68. Oct 31, 2019 234.58 1.10% 3,037.56 2.04%
69. Nov 30, 2019 220.51 -6.00% 3,140.98 3.40%
70. Dec 31, 2019 218.38 1.36 -0.35% 3,230.78 2.86%
71. Jan 31, 2020 228.10 4.45% 3,225.52 -0.16%
Average: 1.77% 0.83%
Standard deviation: 5.05% 3.24%

Show all

1 Data in US$ 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 25.47
VarianceS&P 500 10.49
CovarianceHD, S&P 500 10.79
Correlation coefficientHD, S&P 5001 0.66
βHD2 1.03
αHD3 0.91

Calculations

1 Correlation coefficientHD, S&P 500
= CovarianceHD, S&P 500 ÷ (Standard deviationHD × Standard deviationS&P 500)
= 10.79 ÷ (5.05 × 3.24)

2 βHD
= CovarianceHD, S&P 500 ÷ VarianceS&P 500
= 10.79 ÷ 10.49

3 αHD
= AverageHD – βHD × AverageS&P 500
= 1.771.03 × 0.83


Expected Rate of Return

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

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).

2 See details »

3 E(RHD) = RF + βHD [E(RM) – RF]
= 1.14% + 1.03 [12.13%1.14%]
= 12.44%