Stock Analysis on Net

Bristol-Myers Squibb Co. (NYSE:BMY)

Capital Asset Pricing Model (CAPM)

Microsoft Excel

Rates of Return

Bristol-Myers Squibb Co., monthly rates of return

Microsoft Excel
Bristol-Myers Squibb Co. (BMY) Standard & Poor’s 500 (S&P 500)
t Date PriceBMY,t1 DividendBMY,t1 RBMY,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $49.37 2,704.10
1. Feb 28, 2019 $51.66 4.64% 2,784.49 2.97%
2. Mar 31, 2019 $47.71 -7.65% 2,834.40 1.79%
3. Apr 30, 2019 $46.43 $0.41 -1.82% 2,945.83 3.93%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 $49.38 -4.17% 4,567.80 8.92%
59. Dec 31, 2023 $51.31 3.91% 4,769.83 4.42%
Average (R): 0.49% 1.11%
Standard deviation: 5.87% 5.31%
Bristol-Myers Squibb Co. (BMY) Standard & Poor’s 500 (S&P 500)
t Date PriceBMY,t1 DividendBMY,t1 RBMY,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $49.37 2,704.10
1. Feb 28, 2019 $51.66 4.64% 2,784.49 2.97%
2. Mar 31, 2019 $47.71 -7.65% 2,834.40 1.79%
3. Apr 30, 2019 $46.43 $0.41 -1.82% 2,945.83 3.93%
4. May 31, 2019 $45.37 -2.28% 2,752.06 -6.58%
5. Jun 30, 2019 $45.35 -0.04% 2,941.76 6.89%
6. Jul 31, 2019 $44.41 $0.41 -1.17% 2,980.38 1.31%
7. Aug 31, 2019 $48.07 8.24% 2,926.46 -1.81%
8. Sep 30, 2019 $50.71 5.49% 2,976.74 1.72%
9. Oct 31, 2019 $57.37 $0.41 13.94% 3,037.56 2.04%
10. Nov 30, 2019 $56.94 -0.75% 3,140.98 3.40%
11. Dec 31, 2019 $64.19 12.73% 3,230.78 2.86%
12. Jan 31, 2020 $62.95 $0.45 -1.23% 3,225.52 -0.16%
13. Feb 29, 2020 $59.06 -6.18% 2,954.22 -8.41%
14. Mar 31, 2020 $55.74 -5.62% 2,584.59 -12.51%
15. Apr 30, 2020 $60.81 $0.45 9.90% 2,912.43 12.68%
16. May 31, 2020 $59.72 -1.79% 3,044.31 4.53%
17. Jun 30, 2020 $58.80 -1.54% 3,100.29 1.84%
18. Jul 31, 2020 $58.66 $0.45 0.53% 3,271.12 5.51%
19. Aug 31, 2020 $62.20 6.03% 3,500.31 7.01%
20. Sep 30, 2020 $60.29 -3.07% 3,363.00 -3.92%
21. Oct 31, 2020 $58.45 $0.45 -2.31% 3,269.96 -2.77%
22. Nov 30, 2020 $62.40 6.76% 3,621.63 10.75%
23. Dec 31, 2020 $62.03 $0.49 0.19% 3,756.07 3.71%
24. Jan 31, 2021 $61.43 -0.97% 3,714.24 -1.11%
25. Feb 28, 2021 $61.33 -0.16% 3,811.15 2.61%
26. Mar 31, 2021 $63.13 $0.49 3.73% 3,972.89 4.24%
27. Apr 30, 2021 $62.42 -1.12% 4,181.17 5.24%
28. May 31, 2021 $65.72 5.29% 4,204.11 0.55%
29. Jun 30, 2021 $66.82 1.67% 4,297.50 2.22%
30. Jul 31, 2021 $67.87 $0.49 2.30% 4,395.26 2.27%
31. Aug 31, 2021 $66.86 -1.49% 4,522.68 2.90%
32. Sep 30, 2021 $59.17 $0.49 -10.77% 4,307.54 -4.76%
33. Oct 31, 2021 $58.40 -1.30% 4,605.38 6.91%
34. Nov 30, 2021 $53.63 -8.17% 4,567.00 -0.83%
35. Dec 31, 2021 $62.35 16.26% 4,766.18 4.36%
36. Jan 31, 2022 $64.89 $0.54 4.94% 4,515.55 -5.26%
37. Feb 28, 2022 $68.67 5.83% 4,373.94 -3.14%
38. Mar 31, 2022 $73.03 $0.54 7.14% 4,530.41 3.58%
39. Apr 30, 2022 $75.27 3.07% 4,131.93 -8.80%
40. May 31, 2022 $75.45 0.24% 4,132.15 0.01%
41. Jun 30, 2022 $77.00 $0.54 2.77% 3,785.38 -8.39%
42. Jul 31, 2022 $73.78 -4.18% 4,130.29 9.11%
43. Aug 31, 2022 $67.41 -8.63% 3,955.00 -4.24%
44. Sep 30, 2022 $71.09 5.46% 3,585.62 -9.34%
45. Oct 31, 2022 $77.47 $0.54 9.73% 3,871.98 7.99%
46. Nov 30, 2022 $80.28 3.63% 4,080.11 5.38%
47. Dec 31, 2022 $71.95 -10.38% 3,839.50 -5.90%
48. Jan 31, 2023 $72.65 $0.57 1.77% 4,076.60 6.18%
49. Feb 28, 2023 $68.96 -5.08% 3,970.15 -2.61%
50. Mar 31, 2023 $69.31 0.51% 4,109.31 3.51%
51. Apr 30, 2023 $66.77 $0.57 -2.84% 4,169.48 1.46%
52. May 31, 2023 $64.44 -3.49% 4,179.83 0.25%
53. Jun 30, 2023 $63.95 -0.76% 4,376.86 4.71%
54. Jul 31, 2023 $62.19 $0.57 -1.86% 4,588.96 4.85%
55. Aug 31, 2023 $61.65 -0.87% 4,507.66 -1.77%
56. Sep 30, 2023 $58.04 -5.86% 4,288.05 -4.87%
57. Oct 31, 2023 $51.53 $0.57 -10.23% 4,193.80 -2.20%
58. Nov 30, 2023 $49.38 -4.17% 4,567.80 8.92%
59. Dec 31, 2023 $51.31 3.91% 4,769.83 4.42%
Average (R): 0.49% 1.11%
Standard deviation: 5.87% 5.31%

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

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


Variance and Covariance

Bristol-Myers Squibb Co., calculation of variance and covariance of returns

Microsoft Excel
t Date RBMY,t RS&P 500,t (RBMY,tRBMY)2 (RS&P 500,tRS&P 500)2 (RBMY,tRBMY)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 4.64% 2.97% 17.21 3.49 7.75
2. Mar 31, 2019 -7.65% 1.79% 66.20 0.47 -5.59
3. Apr 30, 2019 -1.82% 3.93% 5.35 7.98 -6.54
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 -4.17% 8.92% 21.74 61.03 -36.42
59. Dec 31, 2023 3.91% 4.42% 11.69 11.00 11.34
Total (Σ): 1,995.90 1,634.30 621.26
t Date RBMY,t RS&P 500,t (RBMY,tRBMY)2 (RS&P 500,tRS&P 500)2 (RBMY,tRBMY)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 4.64% 2.97% 17.21 3.49 7.75
2. Mar 31, 2019 -7.65% 1.79% 66.20 0.47 -5.59
3. Apr 30, 2019 -1.82% 3.93% 5.35 7.98 -6.54
4. May 31, 2019 -2.28% -6.58% 7.69 59.04 21.31
5. Jun 30, 2019 -0.04% 6.89% 0.29 33.49 -3.09
6. Jul 31, 2019 -1.17% 1.31% 2.75 0.04 -0.34
7. Aug 31, 2019 8.24% -1.81% 60.08 8.50 -22.60
8. Sep 30, 2019 5.49% 1.72% 25.02 0.37 3.06
9. Oct 31, 2019 13.94% 2.04% 180.96 0.88 12.61
10. Nov 30, 2019 -0.75% 3.40% 1.54 5.28 -2.85
11. Dec 31, 2019 12.73% 2.86% 149.88 3.07 21.46
12. Jan 31, 2020 -1.23% -0.16% 2.96 1.61 2.18
13. Feb 29, 2020 -6.18% -8.41% 44.48 90.57 63.47
14. Mar 31, 2020 -5.62% -12.51% 37.35 185.44 83.22
15. Apr 30, 2020 9.90% 12.68% 88.61 134.06 108.99
16. May 31, 2020 -1.79% 4.53% 5.21 11.71 -7.81
17. Jun 30, 2020 -1.54% 1.84% 4.12 0.54 -1.49
18. Jul 31, 2020 0.53% 5.51% 0.00 19.40 0.16
19. Aug 31, 2020 6.03% 7.01% 30.74 34.82 32.72
20. Sep 30, 2020 -3.07% -3.92% 12.68 25.29 17.91
21. Oct 31, 2020 -2.31% -2.77% 7.81 15.00 10.83
22. Nov 30, 2020 6.76% 10.75% 39.29 93.10 60.48
23. Dec 31, 2020 0.19% 3.71% 0.09 6.79 -0.78
24. Jan 31, 2021 -0.97% -1.11% 2.12 4.93 3.23
25. Feb 28, 2021 -0.16% 2.61% 0.43 2.26 -0.98
26. Mar 31, 2021 3.73% 4.24% 10.52 9.85 10.18
27. Apr 30, 2021 -1.12% 5.24% 2.61 17.11 -6.68
28. May 31, 2021 5.29% 0.55% 23.01 0.31 -2.67
29. Jun 30, 2021 1.67% 2.22% 1.40 1.24 1.32
30. Jul 31, 2021 2.30% 2.27% 3.29 1.37 2.12
31. Aug 31, 2021 -1.49% 2.90% 3.91 3.22 -3.55
32. Sep 30, 2021 -10.77% -4.76% 126.76 34.37 66.01
33. Oct 31, 2021 -1.30% 6.91% 3.21 33.74 -10.40
34. Nov 30, 2021 -8.17% -0.83% 74.96 3.76 16.79
35. Dec 31, 2021 16.26% 4.36% 248.68 10.60 51.34
36. Jan 31, 2022 4.94% -5.26% 19.80 40.51 -28.32
37. Feb 28, 2022 5.83% -3.14% 28.47 17.99 -22.63
38. Mar 31, 2022 7.14% 3.58% 44.16 6.11 16.42
39. Apr 30, 2022 3.07% -8.80% 6.64 98.04 -25.52
40. May 31, 2022 0.24% 0.01% 0.06 1.21 0.28
41. Jun 30, 2022 2.77% -8.39% 5.20 90.21 -21.66
42. Jul 31, 2022 -4.18% 9.11% 21.83 64.09 -37.40
43. Aug 31, 2022 -8.63% -4.24% 83.24 28.62 48.81
44. Sep 30, 2022 5.46% -9.34% 24.69 109.11 -51.90
45. Oct 31, 2022 9.73% 7.99% 85.45 47.34 63.60
46. Nov 30, 2022 3.63% 5.38% 9.84 18.23 13.39
47. Dec 31, 2022 -10.38% -5.90% 118.07 49.04 76.10
48. Jan 31, 2023 1.77% 6.18% 1.63 25.70 6.46
49. Feb 28, 2023 -5.08% -2.61% 31.02 13.82 20.70
50. Mar 31, 2023 0.51% 3.51% 0.00 5.76 0.04
51. Apr 30, 2023 -2.84% 1.46% 11.10 0.13 -1.19
52. May 31, 2023 -3.49% 0.25% 15.84 0.74 3.41
53. Jun 30, 2023 -0.76% 4.71% 1.56 13.02 -4.51
54. Jul 31, 2023 -1.86% 4.85% 5.53 13.99 -8.79
55. Aug 31, 2023 -0.87% -1.77% 1.84 8.28 3.91
56. Sep 30, 2023 -5.86% -4.87% 40.27 35.73 37.93
57. Oct 31, 2023 -10.23% -2.20% 115.01 10.92 35.43
58. Nov 30, 2023 -4.17% 8.92% 21.74 61.03 -36.42
59. Dec 31, 2023 3.91% 4.42% 11.69 11.00 11.34
Total (Σ): 1,995.90 1,634.30 621.26

Show all

VarianceBMY = Σ(RBMY,tRBMY)2 ÷ (59 – 1)
= 1,995.90 ÷ (59 – 1)
= 34.41

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

CovarianceBMY, S&P 500 = Σ(RBMY,tRBMY)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 621.26 ÷ (59 – 1)
= 10.71


Systematic Risk (β) Estimation

Microsoft Excel
VarianceBMY 34.41
VarianceS&P 500 28.18
CovarianceBMY, S&P 500 10.71
Correlation coefficientBMY, S&P 5001 0.34
βBMY2 0.38
αBMY3 0.07%

Calculations

1 Correlation coefficientBMY, S&P 500
= CovarianceBMY, S&P 500 ÷ (Standard deviationBMY × Standard deviationS&P 500)
= 10.71 ÷ (5.87% × 5.31%)
= 0.34

2 βBMY
= CovarianceBMY, S&P 500 ÷ VarianceS&P 500
= 10.71 ÷ 28.18
= 0.38

3 αBMY
= AverageBMY – βBMY × AverageS&P 500
= 0.49%0.38 × 1.11%
= 0.07%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 4.42%
Expected rate of return on market portfolio2 E(RM) 13.61%
Systematic risk (β) of Bristol-Myers Squibb Co. common stock βBMY 0.38
 
Expected rate of return on Bristol-Myers Squibb Co. common stock3 E(RBMY) 7.91%

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(RBMY) = RF + βBMY [E(RM) – RF]
= 4.42% + 0.38 [13.61%4.42%]
= 7.91%