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

Sherwin-Williams Co. (NYSE:SHW)

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 Sherwin-Williams Co.’s common stock.


Rates of Return

Sherwin-Williams Co., monthly rates of return

Microsoft Excel LibreOffice Calc
Sherwin-Williams Co. (SHW) Standard & Poor’s 500 (S&P 500)
t Date PriceSHW,t1 DividendSHW,t1 RSHW,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2015 271.27 1,994.99
1. Feb 28, 2015 285.20 0.67 5.38% 2,104.50 5.49%
2. Mar 31, 2015 284.50 -0.25% 2,067.89 -1.74%
3. Apr 30, 2015 278.00 -2.28% 2,085.51 0.85%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2019 583.13 1.13 2.09% 3,140.98 3.40%
59. Dec 31, 2019 583.54 0.07% 3,230.78 2.86%
Average: 1.58% 0.88%
Standard deviation: 6.29% 3.45%
Sherwin-Williams Co. (SHW) Standard & Poor’s 500 (S&P 500)
t Date PriceSHW,t1 DividendSHW,t1 RSHW,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2015 271.27 1,994.99
1. Feb 28, 2015 285.20 0.67 5.38% 2,104.50 5.49%
2. Mar 31, 2015 284.50 -0.25% 2,067.89 -1.74%
3. Apr 30, 2015 278.00 -2.28% 2,085.51 0.85%
4. May 31, 2015 288.18 0.67 3.90% 2,107.39 1.05%
5. Jun 30, 2015 275.02 -4.57% 2,063.11 -2.10%
6. Jul 31, 2015 277.76 1.00% 2,103.84 1.97%
7. Aug 31, 2015 255.81 0.67 -7.66% 1,972.18 -6.26%
8. Sep 30, 2015 222.78 -12.91% 1,920.03 -2.64%
9. Oct 31, 2015 266.83 19.77% 2,079.36 8.30%
10. Nov 30, 2015 276.07 0.67 3.71% 2,080.41 0.05%
11. Dec 31, 2015 259.60 -5.97% 2,043.94 -1.75%
12. Jan 31, 2016 255.67 -1.51% 1,940.24 -5.07%
13. Feb 29, 2016 270.50 0.84 6.13% 1,932.23 -0.41%
14. Mar 31, 2016 284.67 5.24% 2,059.74 6.60%
15. Apr 30, 2016 287.31 0.93% 2,065.30 0.27%
16. May 31, 2016 291.09 0.84 1.61% 2,096.95 1.53%
17. Jun 30, 2016 293.67 0.89% 2,098.86 0.09%
18. Jul 31, 2016 299.73 2.06% 2,173.60 3.56%
19. Aug 31, 2016 283.71 0.84 -5.06% 2,170.95 -0.12%
20. Sep 30, 2016 276.66 -2.48% 2,168.27 -0.12%
21. Oct 31, 2016 244.86 -11.49% 2,126.15 -1.94%
22. Nov 30, 2016 268.67 0.84 10.07% 2,198.81 3.42%
23. Dec 31, 2016 268.74 0.03% 2,238.83 1.82%
24. Jan 31, 2017 303.81 13.05% 2,278.87 1.79%
25. Feb 28, 2017 308.54 0.85 1.84% 2,363.64 3.72%
26. Mar 31, 2017 310.19 0.53% 2,362.72 -0.04%
27. Apr 30, 2017 334.68 7.90% 2,384.20 0.91%
28. May 31, 2017 331.77 0.85 -0.62% 2,411.80 1.16%
29. Jun 30, 2017 350.96 5.78% 2,423.41 0.48%
30. Jul 31, 2017 337.27 -3.90% 2,470.30 1.93%
31. Aug 31, 2017 339.27 0.85 0.85% 2,471.65 0.05%
32. Sep 30, 2017 358.04 5.53% 2,519.36 1.93%
33. Oct 31, 2017 395.15 10.36% 2,575.26 2.22%
34. Nov 30, 2017 399.42 0.85 1.30% 2,647.58 2.81%
35. Dec 31, 2017 410.04 2.66% 2,673.61 0.98%
36. Jan 31, 2018 417.11 1.72% 2,823.81 5.62%
37. Feb 28, 2018 401.58 0.86 -3.52% 2,713.83 -3.89%
38. Mar 31, 2018 392.12 -2.36% 2,640.87 -2.69%
39. Apr 30, 2018 367.66 -6.24% 2,648.05 0.27%
40. May 31, 2018 379.25 0.86 3.39% 2,705.27 2.16%
41. Jun 30, 2018 407.57 7.47% 2,718.37 0.48%
42. Jul 31, 2018 440.73 8.14% 2,816.29 3.60%
43. Aug 31, 2018 455.58 0.86 3.56% 2,901.52 3.03%
44. Sep 30, 2018 455.21 -0.08% 2,913.98 0.43%
45. Oct 31, 2018 393.47 -13.56% 2,711.74 -6.94%
46. Nov 30, 2018 424.07 0.86 8.00% 2,760.17 1.79%
47. Dec 31, 2018 393.46 -7.22% 2,506.85 -9.18%
48. Jan 31, 2019 421.52 7.13% 2,704.10 7.87%
49. Feb 28, 2019 433.20 1.13 3.04% 2,784.49 2.97%
50. Mar 31, 2019 430.71 -0.57% 2,834.40 1.79%
51. Apr 30, 2019 454.83 5.60% 2,945.83 3.93%
52. May 31, 2019 419.45 1.13 -7.53% 2,752.06 -6.58%
53. Jun 30, 2019 458.29 9.26% 2,941.76 6.89%
54. Jul 31, 2019 513.04 11.95% 2,980.38 1.31%
55. Aug 31, 2019 526.75 1.13 2.89% 2,926.46 -1.81%
56. Sep 30, 2019 549.87 4.39% 2,976.74 1.72%
57. Oct 31, 2019 572.32 4.08% 3,037.56 2.04%
58. Nov 30, 2019 583.13 1.13 2.09% 3,140.98 3.40%
59. Dec 31, 2019 583.54 0.07% 3,230.78 2.86%
Average: 1.58% 0.88%
Standard deviation: 6.29% 3.45%

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 SHW 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
VarianceSHW 39.56
VarianceS&P 500 11.88
CovarianceSHW, S&P 500 15.25
Correlation coefficientSHW, S&P 5001 0.70
βSHW2 1.28
αSHW3 0.46

Calculations

1 Correlation coefficientSHW, S&P 500
= CovarianceSHW, S&P 500 ÷ (Standard deviationSHW × Standard deviationS&P 500)
= 15.25 ÷ (6.29 × 3.45)

2 βSHW
= CovarianceSHW, S&P 500 ÷ VarianceS&P 500
= 15.25 ÷ 11.88

3 αSHW
= AverageSHW – βSHW × AverageS&P 500
= 1.581.28 × 0.88


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 1.37%
Expected rate of return on market portfolio2 E(RM) 12.48%
Systematic risk (β) of Sherwin-Williams Co.’s common stock βSHW 1.28
 
Expected rate of return on Sherwin-Williams Co.’s common stock3 E(RSHW) 15.63%

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(RSHW) = RF + βSHW [E(RM) – RF]
= 1.37% + 1.28 [12.48%1.37%]
= 15.63%