Microsoft Excel LibreOffice Calc

Colgate-Palmolive Co. (CL)


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 Colgate-Palmolive Co.’s common stock.


Rates of Return

Colgate-Palmolive Co., monthly rates of return

Microsoft Excel LibreOffice Calc
Colgate-Palmolive Co. (CL) Standard & Poor’s 500 (S&P 500)
t Date PriceCL,t1 DividendCL,t1 RCL,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 61.23 1,782.59
1. Feb 28, 2014 62.83 2.61% 1,859.45 4.31%
2. Mar 31, 2014 64.87 3.25% 1,872.34 0.69%
3. Apr 30, 2014 67.30 0.36 4.30% 1,883.95 0.62%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2018 63.52 6.67% 2,760.17 1.79%
59. Dec 31, 2018 59.52 -6.30% 2,506.85 -9.18%
Average: 0.23% 0.63%
Standard deviation: 4.28% 3.13%
Colgate-Palmolive Co. (CL) Standard & Poor’s 500 (S&P 500)
t Date PriceCL,t1 DividendCL,t1 RCL,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 61.23 1,782.59
1. Feb 28, 2014 62.83 2.61% 1,859.45 4.31%
2. Mar 31, 2014 64.87 3.25% 1,872.34 0.69%
3. Apr 30, 2014 67.30 0.36 4.30% 1,883.95 0.62%
4. May 31, 2014 68.40 1.63% 1,923.57 2.10%
5. Jun 30, 2014 68.18 -0.32% 1,960.23 1.91%
6. Jul 31, 2014 63.40 0.36 -6.48% 1,930.67 -1.51%
7. Aug 31, 2014 64.73 2.10% 2,003.37 3.77%
8. Sep 30, 2014 65.22 0.76% 1,972.29 -1.55%
9. Oct 31, 2014 66.88 0.36 3.10% 2,018.05 2.32%
10. Nov 30, 2014 69.59 4.05% 2,067.56 2.45%
11. Dec 31, 2014 69.19 -0.57% 2,058.90 -0.42%
12. Jan 31, 2015 67.52 0.36 -1.89% 1,994.99 -3.10%
13. Feb 28, 2015 70.82 4.89% 2,104.50 5.49%
14. Mar 31, 2015 69.34 -2.09% 2,067.89 -1.74%
15. Apr 30, 2015 67.28 0.38 -2.42% 2,085.51 0.85%
16. May 31, 2015 66.79 -0.73% 2,107.39 1.05%
17. Jun 30, 2015 65.41 -2.07% 2,063.11 -2.10%
18. Jul 31, 2015 68.02 0.38 4.57% 2,103.84 1.97%
19. Aug 31, 2015 62.81 -7.66% 1,972.18 -6.26%
20. Sep 30, 2015 63.46 1.03% 1,920.03 -2.64%
21. Oct 31, 2015 66.35 0.38 5.15% 2,079.36 8.30%
22. Nov 30, 2015 65.68 -1.01% 2,080.41 0.05%
23. Dec 31, 2015 66.62 1.43% 2,043.94 -1.75%
24. Jan 31, 2016 67.53 0.38 1.94% 1,940.24 -5.07%
25. Feb 29, 2016 65.64 -2.80% 1,932.23 -0.41%
26. Mar 31, 2016 70.65 7.63% 2,059.74 6.60%
27. Apr 30, 2016 70.92 0.39 0.93% 2,065.30 0.27%
28. May 31, 2016 70.41 -0.72% 2,096.95 1.53%
29. Jun 30, 2016 73.20 3.96% 2,098.86 0.09%
30. Jul 31, 2016 74.43 0.39 2.21% 2,173.60 3.56%
31. Aug 31, 2016 74.34 -0.12% 2,170.95 -0.12%
32. Sep 30, 2016 74.14 -0.27% 2,168.27 -0.12%
33. Oct 31, 2016 71.36 0.39 -3.22% 2,126.15 -1.94%
34. Nov 30, 2016 65.23 -8.59% 2,198.81 3.42%
35. Dec 31, 2016 65.44 0.32% 2,238.83 1.82%
36. Jan 31, 2017 64.58 0.39 -0.72% 2,278.87 1.79%
37. Feb 28, 2017 72.98 13.01% 2,363.64 3.72%
38. Mar 31, 2017 73.19 0.29% 2,362.72 -0.04%
39. Apr 30, 2017 72.04 0.40 -1.02% 2,384.20 0.91%
40. May 31, 2017 76.36 6.00% 2,411.80 1.16%
41. Jun 30, 2017 74.13 -2.92% 2,423.41 0.48%
42. Jul 31, 2017 72.20 0.40 -2.06% 2,470.30 1.93%
43. Aug 31, 2017 71.64 -0.78% 2,471.65 0.05%
44. Sep 30, 2017 72.85 1.69% 2,519.36 1.93%
45. Oct 31, 2017 70.45 0.40 -2.75% 2,575.26 2.22%
46. Nov 30, 2017 72.45 2.84% 2,647.58 2.81%
47. Dec 31, 2017 75.45 4.14% 2,673.61 0.98%
48. Jan 31, 2018 74.24 0.40 -1.07% 2,823.81 5.62%
49. Feb 28, 2018 68.97 -7.10% 2,713.83 -3.89%
50. Mar 31, 2018 71.68 3.93% 2,640.87 -2.69%
51. Apr 30, 2018 65.23 0.42 -8.41% 2,648.05 0.27%
52. May 31, 2018 63.09 -3.28% 2,705.27 2.16%
53. Jun 30, 2018 64.81 2.73% 2,718.37 0.48%
54. Jul 31, 2018 67.01 0.42 4.04% 2,816.29 3.60%
55. Aug 31, 2018 66.41 -0.90% 2,901.52 3.03%
56. Sep 30, 2018 66.95 0.81% 2,913.98 0.43%
57. Oct 31, 2018 59.55 0.42 -10.43% 2,711.74 -6.94%
58. Nov 30, 2018 63.52 6.67% 2,760.17 1.79%
59. Dec 31, 2018 59.52 -6.30% 2,506.85 -9.18%
Average: 0.23% 0.63%
Standard deviation: 4.28% 3.13%

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 CL 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
VarianceCL 18.28
VarianceS&P 500 9.77
CovarianceCL, S&P 500 7.20
Correlation coefficientCL, S&P 5001 0.54
βCL2 0.74
αCL3 -0.24

Calculations

1 Correlation coefficientCL, S&P 500
= CovarianceCL, S&P 500 ÷ (Standard deviationCL × Standard deviationS&P 500)
= 7.20 ÷ (4.28 × 3.13)

2 βCL
= CovarianceCL, S&P 500 ÷ VarianceS&P 500
= 7.20 ÷ 9.77

3 αCL
= AverageCL – βCL × AverageS&P 500
= 0.230.74 × 0.63


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.07%
Expected rate of return on market portfolio2 E(RM) 11.21%
Systematic risk (β) of Colgate-Palmolive Co.’s common stock βCL 0.74
 
Expected rate of return on Colgate-Palmolive Co.’s common stock3 E(RCL) 8.81%

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(RCL) = RF + βCL [E(RM) – RF]
= 2.07% + 0.74 [11.21%2.07%]
= 8.81%