Stock Analysis on Net
Stock Analysis on Net
Microsoft Excel LibreOffice Calc

Home Depot Inc. (NYSE:HD)

Capital Asset Pricing Model (CAPM)

Intermediate level

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 (R): 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 (R): 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


Variance and Covariance

Home Depot Inc., calculation of variance and covariance of returns

Microsoft Excel LibreOffice Calc
t Date RHD,t RS&P 500,t (RHD,tRHD)2 (RS&P 500,tRS&P 500)2 (RHD,tRHD)×(RS&P 500,tRS&P 500)
1. Mar 31, 2014 -2.96% 0.69% 22.35 0.02 0.65
2. Apr 30, 2014 0.48% 0.62% 1.65 0.04 0.27
3. May 31, 2014 0.91% 2.10% 0.74 1.62 -1.09
. . . . . . .
. . . . . . .
. . . . . . .
70. Dec 31, 2019 -0.35% 2.86% 4.47 4.11 -4.29
71. Jan 31, 2020 4.45% -0.16% 7.21 0.99 -2.67
Total (Σ): 1,782.85 734.39 755.47
t Date RHD,t RS&P 500,t (RHD,tRHD)2 (RS&P 500,tRS&P 500)2 (RHD,tRHD)×(RS&P 500,tRS&P 500)
1. Mar 31, 2014 -2.96% 0.69% 22.35 0.02 0.65
2. Apr 30, 2014 0.48% 0.62% 1.65 0.04 0.27
3. May 31, 2014 0.91% 2.10% 0.74 1.62 -1.09
4. Jun 30, 2014 1.50% 1.91% 0.07 1.16 -0.29
5. Jul 31, 2014 -0.14% -1.51% 3.62 5.47 4.45
6. Aug 31, 2014 15.65% 3.77% 192.67 8.61 40.74
7. Sep 30, 2014 -1.38% -1.55% 9.89 5.67 7.49
8. Oct 31, 2014 6.30% 2.32% 20.56 2.22 6.75
9. Nov 30, 2014 1.93% 2.45% 0.03 2.63 0.26
10. Dec 31, 2014 6.08% -0.42% 18.58 1.56 -5.39
11. Jan 31, 2015 -0.52% -3.10% 5.24 15.48 9.01
12. Feb 28, 2015 9.89% 5.49% 66.05 21.70 37.86
13. Mar 31, 2015 -0.48% -1.74% 5.04 6.61 5.77
14. Apr 30, 2015 -5.84% 0.85% 57.78 0.00 -0.16
15. May 31, 2015 4.15% 1.05% 5.69 0.05 0.52
16. Jun 30, 2015 0.27% -2.10% 2.24 8.60 4.39
17. Jul 31, 2015 5.31% 1.97% 12.56 1.31 4.05
18. Aug 31, 2015 -0.49% -6.26% 5.07 50.25 15.97
19. Sep 30, 2015 -0.33% -2.64% 4.38 12.08 7.27
20. Oct 31, 2015 7.06% 8.30% 28.00 55.76 39.51
21. Nov 30, 2015 8.28% 0.05% 42.46 0.61 -5.08
22. Dec 31, 2015 -0.78% -1.75% 6.46 6.68 6.57
23. Jan 31, 2016 -4.91% -5.07% 44.53 34.86 39.40
24. Feb 29, 2016 -1.30% -0.41% 9.42 1.55 3.82
25. Mar 31, 2016 8.06% 6.60% 39.58 33.27 36.29
26. Apr 30, 2016 0.34% 0.27% 2.02 0.31 0.80
27. May 31, 2016 -0.81% 1.53% 6.62 0.49 -1.80
28. Jun 30, 2016 -3.35% 0.09% 26.20 0.55 3.79
29. Jul 31, 2016 8.26% 3.56% 42.21 7.45 17.74
30. Aug 31, 2016 -2.48% -0.12% 18.04 0.91 4.05
31. Sep 30, 2016 -4.06% -0.12% 33.89 0.91 5.56
32. Oct 31, 2016 -5.18% -1.94% 48.29 7.69 19.27
33. Nov 30, 2016 6.62% 3.42% 23.59 6.69 12.56
34. Dec 31, 2016 3.62% 1.82% 3.43 0.98 1.83
35. Jan 31, 2017 2.61% 1.79% 0.71 0.92 0.81
36. Feb 28, 2017 5.33% 3.72% 12.69 8.35 10.29
37. Mar 31, 2017 1.94% -0.04% 0.03 0.76 -0.15
38. Apr 30, 2017 6.31% 0.91% 20.68 0.01 0.36
39. May 31, 2017 -1.09% 1.16% 8.15 0.11 -0.93
40. Jun 30, 2017 -0.07% 0.48% 3.38 0.12 0.64
41. Jul 31, 2017 -2.48% 1.93% 18.00 1.22 -4.68
42. Aug 31, 2017 0.78% 0.05% 0.98 0.60 0.77
43. Sep 30, 2017 9.13% 1.93% 54.30 1.21 8.10
44. Oct 31, 2017 1.36% 2.22% 0.17 1.93 -0.57
45. Nov 30, 2017 9.01% 2.81% 52.42 3.91 14.32
46. Dec 31, 2017 5.40% 0.98% 13.21 0.02 0.55
47. Jan 31, 2018 6.00% 5.62% 17.92 22.92 20.27
48. Feb 28, 2018 -9.27% -3.89% 121.86 22.33 52.16
49. Mar 31, 2018 -1.65% -2.69% 11.64 12.38 12.01
50. Apr 30, 2018 3.68% 0.27% 3.67 0.31 -1.07
51. May 31, 2018 1.50% 2.16% 0.07 1.77 -0.35
52. Jun 30, 2018 4.58% 0.48% 7.94 0.12 -0.98
53. Jul 31, 2018 1.24% 3.60% 0.28 7.68 -1.46
54. Aug 31, 2018 2.17% 3.03% 0.16 4.82 0.88
55. Sep 30, 2018 3.18% 0.43% 1.99 0.16 -0.57
56. Oct 31, 2018 -15.10% -6.94% 284.29 60.39 131.03
57. Nov 30, 2018 3.11% 1.79% 1.81 0.91 1.28
58. Dec 31, 2018 -4.71% -9.18% 41.98 100.17 64.85
59. Jan 31, 2019 6.82% 7.87% 25.50 49.53 35.54
60. Feb 28, 2019 0.88% 2.97% 0.79 4.59 -1.90
61. Mar 31, 2019 4.38% 1.79% 6.84 0.92 2.51
62. Apr 30, 2019 6.15% 3.93% 19.26 9.61 13.61
63. May 31, 2019 -6.80% -6.58% 73.36 54.89 63.45
64. Jun 30, 2019 10.26% 6.89% 72.17 36.75 51.50
65. Jul 31, 2019 2.75% 1.31% 0.97 0.23 0.47
66. Aug 31, 2019 6.65% -1.81% 23.90 6.97 -12.91
67. Sep 30, 2019 2.40% 1.72% 0.40 0.79 0.56
68. Oct 31, 2019 1.10% 2.04% 0.44 1.47 -0.80
69. Nov 30, 2019 -6.00% 3.40% 60.27 6.63 -19.98
70. Dec 31, 2019 -0.35% 2.86% 4.47 4.11 -4.29
71. Jan 31, 2020 4.45% -0.16% 7.21 0.99 -2.67
Total (Σ): 1,782.85 734.39 755.47

Show all

VarianceHD = Σ(RHD,tRHD)2 ÷ (71 – 1)
= 1,782.85 ÷ (71 – 1)
= 25.47

VarianceS&P 500 = Σ(RS&P 500,tRS&P 500)2 ÷ (71 – 1)
= 734.39 ÷ (71 – 1)
= 10.49

CovarianceHD, S&P 500 = Σ(RHD,tRHD)×(RS&P 500,tRS&P 500) ÷ (71 – 1)
= 755.47 ÷ (71 – 1)
= 10.79


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%)
= 0.66

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

3 αHD
= AverageHD – βHD × AverageS&P 500
= 1.77%1.03 × 0.83%
= 0.91%


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 1.66%
Expected rate of return on market portfolio2 E(RM) 12.36%
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.67%

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.66% + 1.03 [12.36%1.66%]
= 12.67%