Microsoft Excel LibreOffice Calc

Honeywell International Inc. (HON)


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 Honeywell International Inc.’s common stock.


Rates of Return

Honeywell International Inc., monthly rates of return

Microsoft Excel LibreOffice Calc
Honeywell International Inc. (HON) Standard & Poor’s 500 (S&P 500)
t Date PriceHON,t1 DividendHON,t1 RHON,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2015 97.76 1,994.99
1. Feb 28, 2015 102.78 0.5175 5.66% 2,104.50 5.49%
2. Mar 31, 2015 104.31 1.49% 2,067.89 -1.74%
3. Apr 30, 2015 100.92 -3.25% 2,085.51 0.85%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2019 178.55 0.90 3.89% 3,140.98 3.40%
59. Dec 31, 2019 177.00 -0.87% 3,230.78 2.86%
Average: 1.37% 0.88%
Standard deviation: 4.57% 3.45%
Honeywell International Inc. (HON) Standard & Poor’s 500 (S&P 500)
t Date PriceHON,t1 DividendHON,t1 RHON,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2015 97.76 1,994.99
1. Feb 28, 2015 102.78 0.5175 5.66% 2,104.50 5.49%
2. Mar 31, 2015 104.31 1.49% 2,067.89 -1.74%
3. Apr 30, 2015 100.92 -3.25% 2,085.51 0.85%
4. May 31, 2015 104.20 0.5175 3.76% 2,107.39 1.05%
5. Jun 30, 2015 101.97 -2.14% 2,063.11 -2.10%
6. Jul 31, 2015 105.05 3.02% 2,103.84 1.97%
7. Aug 31, 2015 99.27 0.5175 -5.01% 1,972.18 -6.26%
8. Sep 30, 2015 94.69 -4.61% 1,920.03 -2.64%
9. Oct 31, 2015 103.28 9.07% 2,079.36 8.30%
10. Nov 30, 2015 103.95 0.595 1.22% 2,080.41 0.05%
11. Dec 31, 2015 103.57 -0.37% 2,043.94 -1.75%
12. Jan 31, 2016 103.20 -0.36% 1,940.24 -5.07%
13. Feb 29, 2016 101.35 0.595 -1.22% 1,932.23 -0.41%
14. Mar 31, 2016 112.05 10.56% 2,059.74 6.60%
15. Apr 30, 2016 114.27 1.98% 2,065.30 0.27%
16. May 31, 2016 113.83 0.595 0.14% 2,096.95 1.53%
17. Jun 30, 2016 116.32 2.19% 2,098.86 0.09%
18. Jul 31, 2016 116.33 0.01% 2,173.60 3.56%
19. Aug 31, 2016 116.71 0.595 0.84% 2,170.95 -0.12%
20. Sep 30, 2016 116.59 -0.10% 2,168.27 -0.12%
21. Oct 31, 2016 109.68 -5.93% 2,126.15 -1.94%
22. Nov 30, 2016 113.94 0.665 4.49% 2,198.81 3.42%
23. Dec 31, 2016 115.85 1.68% 2,238.83 1.82%
24. Jan 31, 2017 118.32 2.13% 2,278.87 1.79%
25. Feb 28, 2017 124.50 0.665 5.79% 2,363.64 3.72%
26. Mar 31, 2017 124.87 0.30% 2,362.72 -0.04%
27. Apr 30, 2017 131.14 5.02% 2,384.20 0.91%
28. May 31, 2017 132.99 0.665 1.92% 2,411.80 1.16%
29. Jun 30, 2017 133.29 0.23% 2,423.41 0.48%
30. Jul 31, 2017 136.12 2.12% 2,470.30 1.93%
31. Aug 31, 2017 138.27 0.665 2.07% 2,471.65 0.05%
32. Sep 30, 2017 141.74 2.51% 2,519.36 1.93%
33. Oct 31, 2017 144.16 1.71% 2,575.26 2.22%
34. Nov 30, 2017 155.96 0.745 8.70% 2,647.58 2.81%
35. Dec 31, 2017 153.36 -1.67% 2,673.61 0.98%
36. Jan 31, 2018 159.67 4.11% 2,823.81 5.62%
37. Feb 28, 2018 151.11 0.745 -4.89% 2,713.83 -3.89%
38. Mar 31, 2018 144.51 -4.37% 2,640.87 -2.69%
39. Apr 30, 2018 144.68 0.12% 2,648.05 0.27%
40. May 31, 2018 147.91 0.745 2.75% 2,705.27 2.16%
41. Jun 30, 2018 144.05 -2.61% 2,718.37 0.48%
42. Jul 31, 2018 159.65 10.83% 2,816.29 3.60%
43. Aug 31, 2018 159.06 0.745 0.10% 2,901.52 3.03%
44. Sep 30, 2018 166.40 2.20 6.00% 2,913.98 0.43%
45. Oct 31, 2018 144.82 4.67 -10.16% 2,711.74 -6.94%
46. Nov 30, 2018 146.75 0.82 1.90% 2,760.17 1.79%
47. Dec 31, 2018 132.12 -9.97% 2,506.85 -9.18%
48. Jan 31, 2019 143.63 8.71% 2,704.10 7.87%
49. Feb 28, 2019 154.07 0.82 7.84% 2,784.49 2.97%
50. Mar 31, 2019 158.92 3.15% 2,834.40 1.79%
51. Apr 30, 2019 173.63 9.26% 2,945.83 3.93%
52. May 31, 2019 164.31 0.82 -4.90% 2,752.06 -6.58%
53. Jun 30, 2019 174.59 6.26% 2,941.76 6.89%
54. Jul 31, 2019 172.46 -1.22% 2,980.38 1.31%
55. Aug 31, 2019 164.62 0.82 -4.07% 2,926.46 -1.81%
56. Sep 30, 2019 169.20 2.78% 2,976.74 1.72%
57. Oct 31, 2019 172.73 2.09% 3,037.56 2.04%
58. Nov 30, 2019 178.55 0.90 3.89% 3,140.98 3.40%
59. Dec 31, 2019 177.00 -0.87% 3,230.78 2.86%
Average: 1.37% 0.88%
Standard deviation: 4.57% 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 HON 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
VarianceHON 20.91
VarianceS&P 500 11.88
CovarianceHON, S&P 500 13.11
Correlation coefficientHON, S&P 5001 0.83
βHON2 1.10
αHON3 0.40

Calculations

1 Correlation coefficientHON, S&P 500
= CovarianceHON, S&P 500 ÷ (Standard deviationHON × Standard deviationS&P 500)
= 13.11 ÷ (4.57 × 3.45)

2 βHON
= CovarianceHON, S&P 500 ÷ VarianceS&P 500
= 13.11 ÷ 11.88

3 αHON
= AverageHON – βHON × AverageS&P 500
= 1.371.10 × 0.88


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 1.93%
Expected rate of return on market portfolio2 E(RM) 11.17%
Systematic risk (β) of Honeywell International Inc.’s common stock βHON 1.10
 
Expected rate of return on Honeywell International Inc.’s common stock3 E(RHON) 12.12%

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(RHON) = RF + βHON [E(RM) – RF]
= 1.93% + 1.10 [11.17%1.93%]
= 12.12%