Microsoft Excel LibreOffice Calc

Home Depot Inc. (HD)


Capital Asset Pricing Model (CAPM)

Medium level of difficulty


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 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 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 Correlation coefficientHD, S&P 500
= CovarianceHD, S&P 500 ÷ (Standard deviationHD × Standard deviationS&P 500)
= 11.13 ÷ (4.96 × 3.18)

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

3 αHD
= 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.23%
Expected rate of return on market portfolio2 E(RM) 11.54%
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.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]
= 2.23% + 1.10 [11.54%2.23%]
= 12.44%