Microsoft Excel LibreOffice Calc

Bristol-Myers Squibb Co. (BMY)


Capital Asset Pricing Model (CAPM)

Difficulty: Intermediate


Rates of Return

Bristol-Myers Squibb Co., monthly rates of return

Microsoft Excel LibreOffice Calc
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, 2014 49.97 1,782.59
1. Feb 28, 2014 53.77 7.60% 1,859.45 4.31%
2. Mar 31, 2014 51.95 -3.38% 1,872.34 0.69%
3. Apr 30, 2014 50.09 0.36 -2.89% 1,883.95 0.62%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2018 53.46 5.78% 2,760.17 1.79%
59. Dec 31, 2018 51.98 -2.77% 2,506.85 -9.18%
Average: 0.55% 0.63%
Standard Deviation: 7.23% 3.13%
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, 2014 49.97 1,782.59
1. Feb 28, 2014 53.77 7.60% 1,859.45 4.31%
2. Mar 31, 2014 51.95 -3.38% 1,872.34 0.69%
3. Apr 30, 2014 50.09 0.36 -2.89% 1,883.95 0.62%
4. May 31, 2014 49.74 -0.70% 1,923.57 2.10%
5. Jun 30, 2014 48.51 -2.47% 1,960.23 1.91%
6. Jul 31, 2014 50.62 0.36 5.09% 1,930.67 -1.51%
7. Aug 31, 2014 50.65 0.06% 2,003.37 3.77%
8. Sep 30, 2014 51.18 1.05% 1,972.29 -1.55%
9. Oct 31, 2014 58.19 0.36 14.40% 2,018.05 2.32%
10. Nov 30, 2014 59.05 1.48% 2,067.56 2.45%
11. Dec 31, 2014 59.03 0.37 0.59% 2,058.90 -0.42%
12. Jan 31, 2015 60.27 2.10% 1,994.99 -3.10%
13. Feb 28, 2015 60.92 1.08% 2,104.50 5.49%
14. Mar 31, 2015 64.50 5.88% 2,067.89 -1.74%
15. Apr 30, 2015 63.73 0.37 -0.62% 2,085.51 0.85%
16. May 31, 2015 64.60 1.37% 2,107.39 1.05%
17. Jun 30, 2015 66.54 3.00% 2,063.11 -2.10%
18. Jul 31, 2015 65.64 0.37 -0.80% 2,103.84 1.97%
19. Aug 31, 2015 59.47 -9.40% 1,972.18 -6.26%
20. Sep 30, 2015 59.20 0.37 0.17% 1,920.03 -2.64%
21. Oct 31, 2015 65.95 11.40% 2,079.36 8.30%
22. Nov 30, 2015 67.01 1.61% 2,080.41 0.05%
23. Dec 31, 2015 68.79 0.38 3.22% 2,043.94 -1.75%
24. Jan 31, 2016 62.16 -9.64% 1,940.24 -5.07%
25. Feb 29, 2016 61.93 -0.37% 1,932.23 -0.41%
26. Mar 31, 2016 63.88 0.38 3.76% 2,059.74 6.60%
27. Apr 30, 2016 72.18 12.99% 2,065.30 0.27%
28. May 31, 2016 71.70 -0.67% 2,096.95 1.53%
29. Jun 30, 2016 73.55 0.38 3.11% 2,098.86 0.09%
30. Jul 31, 2016 74.81 1.71% 2,173.60 3.56%
31. Aug 31, 2016 57.39 -23.29% 2,170.95 -0.12%
32. Sep 30, 2016 53.92 -6.05% 2,168.27 -0.12%
33. Oct 31, 2016 50.91 0.38 -4.88% 2,126.15 -1.94%
34. Nov 30, 2016 56.44 10.86% 2,198.81 3.42%
35. Dec 31, 2016 58.44 3.54% 2,238.83 1.82%
36. Jan 31, 2017 49.16 0.39 -15.21% 2,278.87 1.79%
37. Feb 28, 2017 56.71 15.36% 2,363.64 3.72%
38. Mar 31, 2017 54.38 -4.11% 2,362.72 -0.04%
39. Apr 30, 2017 56.05 0.39 3.79% 2,384.20 0.91%
40. May 31, 2017 53.95 -3.75% 2,411.80 1.16%
41. Jun 30, 2017 55.72 3.28% 2,423.41 0.48%
42. Jul 31, 2017 56.90 0.39 2.82% 2,470.30 1.93%
43. Aug 31, 2017 60.48 6.29% 2,471.65 0.05%
44. Sep 30, 2017 63.74 5.39% 2,519.36 1.93%
45. Oct 31, 2017 61.66 0.39 -2.65% 2,575.26 2.22%
46. Nov 30, 2017 63.19 2.48% 2,647.58 2.81%
47. Dec 31, 2017 61.28 -3.02% 2,673.61 0.98%
48. Jan 31, 2018 62.60 0.40 2.81% 2,823.81 5.62%
49. Feb 28, 2018 66.20 5.75% 2,713.83 -3.89%
50. Mar 31, 2018 63.25 -4.46% 2,640.87 -2.69%
51. Apr 30, 2018 52.13 0.40 -16.95% 2,648.05 0.27%
52. May 31, 2018 52.62 0.94% 2,705.27 2.16%
53. Jun 30, 2018 55.34 5.17% 2,718.37 0.48%
54. Jul 31, 2018 58.75 0.40 6.88% 2,816.29 3.60%
55. Aug 31, 2018 60.55 3.06% 2,901.52 3.03%
56. Sep 30, 2018 62.08 2.53% 2,913.98 0.43%
57. Oct 31, 2018 50.54 0.40 -17.94% 2,711.74 -6.94%
58. Nov 30, 2018 53.46 5.78% 2,760.17 1.79%
59. Dec 31, 2018 51.98 -2.77% 2,506.85 -9.18%
Average: 0.55% 0.63%
Standard Deviation: 7.23% 3.13%

Show All

1 Data in USD $ 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


Systematic Risk (β) Estimation

Microsoft Excel LibreOffice Calc
VarianceBMY 52.29
VarianceS&P 500 9.77
CovarianceBMY, S&P 500 9.39
Correlation CoefficientBMY, S&P 5001 0.42
βBMY2 0.96
αBMY3 -0.05

Calculations

1 CovarianceBMY, S&P 500 ÷ (Standard DeviationBMY × Standard DeviationS&P 500)
= 9.39 ÷ (7.23 × 3.13)

2 CovarianceBMY, S&P 500 ÷ VarianceS&P 500
= 9.39 ÷ 9.77

3 AverageBMY – βBMY × AverageS&P 500
= 0.550.96 × 0.63


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.43%
Expected rate of return on market portfolio2 E(RM) 11.82%
Systematic risk (β) of Bristol-Myers Squibb Co.’s common stock βBMY 0.96
Expected rate of return on Bristol-Myers Squibb Co.’s common stock3 E(RBMY) 11.46%

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).

Calculations

2 See Details »

3 E(RBMY) = RF + βBMY [E(RM) – RF]
= 2.43% + 0.96 [11.82%2.43%]
= 11.46%