Stock Analysis on Net
Stock Analysis on Net

Caterpillar Inc. (NYSE:CAT)

Capital Asset Pricing Model (CAPM)

Microsoft Excel LibreOffice Calc

Rates of Return

Caterpillar Inc., monthly rates of return

Microsoft Excel LibreOffice Calc
Caterpillar Inc. (CAT) Standard & Poor’s 500 (S&P 500)
t Date PriceCAT,t1 DividendCAT,t1 RCAT,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2016 $62.24 1,940.24
1. Feb 29, 2016 $67.70 8.77% 1,932.23 -0.41%
2. Mar 31, 2016 $76.54 13.06% 2,059.74 6.60%
3. Apr 30, 2016 $77.72 $0.77 2.55% 2,065.30 0.27%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2020 $173.59 10.53% 3,621.63 10.75%
59. Dec 31, 2020 $182.02 4.86% 3,756.07 3.71%
Average (R): 2.34% 1.22%
Standard deviation: 7.26% 4.36%
Caterpillar Inc. (CAT) Standard & Poor’s 500 (S&P 500)
t Date PriceCAT,t1 DividendCAT,t1 RCAT,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2016 $62.24 1,940.24
1. Feb 29, 2016 $67.70 8.77% 1,932.23 -0.41%
2. Mar 31, 2016 $76.54 13.06% 2,059.74 6.60%
3. Apr 30, 2016 $77.72 $0.77 2.55% 2,065.30 0.27%
4. May 31, 2016 $72.51 -6.70% 2,096.95 1.53%
5. Jun 30, 2016 $75.81 4.55% 2,098.86 0.09%
6. Jul 31, 2016 $82.76 $0.77 10.18% 2,173.60 3.56%
7. Aug 31, 2016 $81.95 -0.98% 2,170.95 -0.12%
8. Sep 30, 2016 $88.77 8.32% 2,168.27 -0.12%
9. Oct 31, 2016 $83.46 $0.77 -5.11% 2,126.15 -1.94%
10. Nov 30, 2016 $95.56 14.50% 2,198.81 3.42%
11. Dec 31, 2016 $92.74 -2.95% 2,238.83 1.82%
12. Jan 31, 2017 $95.66 $0.77 3.98% 2,278.87 1.79%
13. Feb 28, 2017 $96.66 1.05% 2,363.64 3.72%
14. Mar 31, 2017 $92.76 -4.03% 2,362.72 -0.04%
15. Apr 30, 2017 $102.26 $0.77 11.07% 2,384.20 0.91%
16. May 31, 2017 $105.43 3.10% 2,411.80 1.16%
17. Jun 30, 2017 $107.46 1.93% 2,423.41 0.48%
18. Jul 31, 2017 $113.95 $0.78 6.77% 2,470.30 1.93%
19. Aug 31, 2017 $117.49 3.11% 2,471.65 0.05%
20. Sep 30, 2017 $124.71 6.15% 2,519.36 1.93%
21. Oct 31, 2017 $135.80 $0.78 9.52% 2,575.26 2.22%
22. Nov 30, 2017 $141.15 3.94% 2,647.58 2.81%
23. Dec 31, 2017 $157.58 11.64% 2,673.61 0.98%
24. Jan 31, 2018 $162.78 $0.78 3.79% 2,823.81 5.62%
25. Feb 28, 2018 $154.63 -5.01% 2,713.83 -3.89%
26. Mar 31, 2018 $147.38 -4.69% 2,640.87 -2.69%
27. Apr 30, 2018 $144.36 $0.78 -1.52% 2,648.05 0.27%
28. May 31, 2018 $151.91 5.23% 2,705.27 2.16%
29. Jun 30, 2018 $135.67 -10.69% 2,718.37 0.48%
30. Jul 31, 2018 $143.80 $0.86 6.63% 2,816.29 3.60%
31. Aug 31, 2018 $138.85 -3.44% 2,901.52 3.03%
32. Sep 30, 2018 $152.49 9.82% 2,913.98 0.43%
33. Oct 31, 2018 $121.32 $0.86 -19.88% 2,711.74 -6.94%
34. Nov 30, 2018 $135.67 11.83% 2,760.17 1.79%
35. Dec 31, 2018 $127.07 -6.34% 2,506.85 -9.18%
36. Jan 31, 2019 $133.16 $0.86 5.47% 2,704.10 7.87%
37. Feb 28, 2019 $137.34 3.14% 2,784.49 2.97%
38. Mar 31, 2019 $135.49 -1.35% 2,834.40 1.79%
39. Apr 30, 2019 $139.42 $0.86 3.54% 2,945.83 3.93%
40. May 31, 2019 $119.81 -14.07% 2,752.06 -6.58%
41. Jun 30, 2019 $136.29 13.76% 2,941.76 6.89%
42. Jul 31, 2019 $131.67 $1.03 -2.63% 2,980.38 1.31%
43. Aug 31, 2019 $119.00 -9.62% 2,926.46 -1.81%
44. Sep 30, 2019 $126.31 6.14% 2,976.74 1.72%
45. Oct 31, 2019 $137.80 $1.03 9.91% 3,037.56 2.04%
46. Nov 30, 2019 $144.73 5.03% 3,140.98 3.40%
47. Dec 31, 2019 $147.68 2.04% 3,230.78 2.86%
48. Jan 31, 2020 $131.35 $1.03 -10.36% 3,225.52 -0.16%
49. Feb 29, 2020 $124.24 -5.41% 2,954.22 -8.41%
50. Mar 31, 2020 $116.04 -6.60% 2,584.59 -12.51%
51. Apr 30, 2020 $116.38 $1.03 1.18% 2,912.43 12.68%
52. May 31, 2020 $120.13 3.22% 3,044.31 4.53%
53. Jun 30, 2020 $126.50 5.30% 3,100.29 1.84%
54. Jul 31, 2020 $132.88 $1.03 5.86% 3,271.12 5.51%
55. Aug 31, 2020 $142.31 7.10% 3,500.31 7.01%
56. Sep 30, 2020 $149.15 4.81% 3,363.00 -3.92%
57. Oct 31, 2020 $157.05 $1.03 5.99% 3,269.96 -2.77%
58. Nov 30, 2020 $173.59 10.53% 3,621.63 10.75%
59. Dec 31, 2020 $182.02 4.86% 3,756.07 3.71%
Average (R): 2.34% 1.22%
Standard deviation: 7.26% 4.36%

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 CAT during period t

3 Rate of return on S&P 500 (the market portfolio proxy) during period t


Variance and Covariance

Caterpillar Inc., calculation of variance and covariance of returns

Microsoft Excel LibreOffice Calc
t Date RCAT,t RS&P 500,t (RCAT,tRCAT)2 (RS&P 500,tRS&P 500)2 (RCAT,tRCAT)×(RS&P 500,tRS&P 500)
1. Feb 29, 2016 8.77% -0.41% 41.40 2.67 -10.51
2. Mar 31, 2016 13.06% 6.60% 114.91 28.93 57.66
3. Apr 30, 2016 2.55% 0.27% 0.04 0.90 -0.20
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2020 10.53% 10.75% 67.14 90.91 78.12
59. Dec 31, 2020 4.86% 3.71% 6.34 6.21 6.28
Total (Σ): 3,057.07 1,103.45 1,048.96
t Date RCAT,t RS&P 500,t (RCAT,tRCAT)2 (RS&P 500,tRS&P 500)2 (RCAT,tRCAT)×(RS&P 500,tRS&P 500)
1. Feb 29, 2016 8.77% -0.41% 41.40 2.67 -10.51
2. Mar 31, 2016 13.06% 6.60% 114.91 28.93 57.66
3. Apr 30, 2016 2.55% 0.27% 0.04 0.90 -0.20
4. May 31, 2016 -6.70% 1.53% 81.75 0.10 -2.82
5. Jun 30, 2016 4.55% 0.09% 4.90 1.27 -2.50
6. Jul 31, 2016 10.18% 3.56% 61.55 5.48 18.37
7. Aug 31, 2016 -0.98% -0.12% 11.00 1.80 4.45
8. Sep 30, 2016 8.32% -0.12% 35.81 1.81 -8.04
9. Oct 31, 2016 -5.11% -1.94% 55.54 10.00 23.57
10. Nov 30, 2016 14.50% 3.42% 147.86 4.83 26.72
11. Dec 31, 2016 -2.95% 1.82% 27.97 0.36 -3.17
12. Jan 31, 2017 3.98% 1.79% 2.69 0.32 0.93
13. Feb 28, 2017 1.05% 3.72% 1.67 6.25 -3.23
14. Mar 31, 2017 -4.03% -0.04% 40.61 1.59 8.02
15. Apr 30, 2017 11.07% 0.91% 76.27 0.10 -2.72
16. May 31, 2017 3.10% 1.16% 0.58 0.00 -0.05
17. Jun 30, 2017 1.93% 0.48% 0.17 0.55 0.30
18. Jul 31, 2017 6.77% 1.93% 19.60 0.51 3.16
19. Aug 31, 2017 3.11% 0.05% 0.59 1.36 -0.90
20. Sep 30, 2017 6.15% 1.93% 14.49 0.50 2.70
21. Oct 31, 2017 9.52% 2.22% 51.55 1.00 7.17
22. Nov 30, 2017 3.94% 2.81% 2.56 2.52 2.54
23. Dec 31, 2017 11.64% 0.98% 86.53 0.06 -2.20
24. Jan 31, 2018 3.79% 5.62% 2.12 19.34 6.41
25. Feb 28, 2018 -5.01% -3.89% 53.95 26.16 37.57
26. Mar 31, 2018 -4.69% -2.69% 49.37 15.28 27.46
27. Apr 30, 2018 -1.52% 0.27% 14.88 0.90 3.66
28. May 31, 2018 5.23% 2.16% 8.36 0.89 2.72
29. Jun 30, 2018 -10.69% 0.48% 169.74 0.54 9.59
30. Jul 31, 2018 6.63% 3.60% 18.39 5.67 10.22
31. Aug 31, 2018 -3.44% 3.03% 33.41 3.26 -10.44
32. Sep 30, 2018 9.82% 0.43% 56.03 0.63 -5.92
33. Oct 31, 2018 -19.88% -6.94% 493.50 66.59 181.28
34. Nov 30, 2018 11.83% 1.79% 90.06 0.32 5.37
35. Dec 31, 2018 -6.34% -9.18% 75.29 108.11 90.22
36. Jan 31, 2019 5.47% 7.87% 9.81 44.20 20.82
37. Feb 28, 2019 3.14% 2.97% 0.64 3.07 1.40
38. Mar 31, 2019 -1.35% 1.79% 13.58 0.33 -2.11
39. Apr 30, 2019 3.54% 3.93% 1.43 7.35 3.25
40. May 31, 2019 -14.07% -6.58% 269.07 60.81 127.91
41. Jun 30, 2019 13.76% 6.89% 130.35 32.18 64.77
42. Jul 31, 2019 -2.63% 1.31% 24.72 0.01 -0.46
43. Aug 31, 2019 -9.62% -1.81% 143.06 9.18 36.23
44. Sep 30, 2019 6.14% 1.72% 14.48 0.25 1.89
45. Oct 31, 2019 9.91% 2.04% 57.37 0.68 6.23
46. Nov 30, 2019 5.03% 3.40% 7.24 4.77 5.88
47. Dec 31, 2019 2.04% 2.86% 0.09 2.69 -0.49
48. Jan 31, 2020 -10.36% -0.16% 161.25 1.91 17.56
49. Feb 29, 2020 -5.41% -8.41% 60.08 92.76 74.65
50. Mar 31, 2020 -6.60% -12.51% 79.89 188.57 122.74
51. Apr 30, 2020 1.18% 12.68% 1.34 131.43 -13.27
52. May 31, 2020 3.22% 4.53% 0.78 10.94 2.92
53. Jun 30, 2020 5.30% 1.84% 8.79 0.38 1.83
54. Jul 31, 2020 5.86% 5.51% 12.39 18.40 15.10
55. Aug 31, 2020 7.10% 7.01% 22.64 33.48 27.53
56. Sep 30, 2020 4.81% -3.92% 6.09 26.45 -12.69
57. Oct 31, 2020 5.99% -2.77% 13.32 15.89 -14.55
58. Nov 30, 2020 10.53% 10.75% 67.14 90.91 78.12
59. Dec 31, 2020 4.86% 3.71% 6.34 6.21 6.28
Total (Σ): 3,057.07 1,103.45 1,048.96

Show all

VarianceCAT = Σ(RCAT,tRCAT)2 ÷ (59 – 1)
= 3,057.07 ÷ (59 – 1)
= 52.71

VarianceS&P 500 = Σ(RS&P 500,tRS&P 500)2 ÷ (59 – 1)
= 1,103.45 ÷ (59 – 1)
= 19.02

CovarianceCAT, S&P 500 = Σ(RCAT,tRCAT)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 1,048.96 ÷ (59 – 1)
= 18.09


Systematic Risk (β) Estimation

Microsoft Excel LibreOffice Calc
VarianceCAT 52.71
VarianceS&P 500 19.02
CovarianceCAT, S&P 500 18.09
Correlation coefficientCAT, S&P 5001 0.57
βCAT2 0.95
αCAT3 1.18%

Calculations

1 Correlation coefficientCAT, S&P 500
= CovarianceCAT, S&P 500 ÷ (Standard deviationCAT × Standard deviationS&P 500)
= 18.09 ÷ (7.26% × 4.36%)
= 0.57

2 βCAT
= CovarianceCAT, S&P 500 ÷ VarianceS&P 500
= 18.09 ÷ 19.02
= 0.95

3 αCAT
= AverageCAT – βCAT × AverageS&P 500
= 2.34%0.95 × 1.22%
= 1.18%


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.06%
Expected rate of return on market portfolio2 E(RM) 11.65%
Systematic risk (β) of Caterpillar Inc.’s common stock βCAT 0.95
 
Expected rate of return on Caterpillar Inc.’s common stock3 E(RCAT) 11.18%

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(RCAT) = RF + βCAT [E(RM) – RF]
= 2.06% + 0.95 [11.65%2.06%]
= 11.18%