Microsoft Excel LibreOffice Calc

AbbVie Inc. (ABBV)


Capital Asset Pricing Model (CAPM)

Medium level of difficulty


Rates of Return

AbbVie Inc., monthly rates of return

Microsoft Excel LibreOffice Calc
AbbVie Inc. (ABBV) Standard & Poor’s 500 (S&P 500)
t Date PriceABBV,t1 DividendABBV,t1 RABBV,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 49.23 1,782.59
1. Feb 28, 2014 50.91 3.41% 1,859.45 4.31%
2. Mar 31, 2014 51.40 0.96% 1,872.34 0.69%
3. Apr 30, 2014 52.08 0.42 2.14% 1,883.95 0.62%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2018 94.27 21.09% 2,760.17 1.79%
59. Dec 31, 2018 92.19 -2.21% 2,506.85 -9.18%
Average: 1.63% 0.63%
Standard deviation: 7.50% 3.13%
AbbVie Inc. (ABBV) Standard & Poor’s 500 (S&P 500)
t Date PriceABBV,t1 DividendABBV,t1 RABBV,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 49.23 1,782.59
1. Feb 28, 2014 50.91 3.41% 1,859.45 4.31%
2. Mar 31, 2014 51.40 0.96% 1,872.34 0.69%
3. Apr 30, 2014 52.08 0.42 2.14% 1,883.95 0.62%
4. May 31, 2014 54.33 4.32% 1,923.57 2.10%
5. Jun 30, 2014 56.44 3.88% 1,960.23 1.91%
6. Jul 31, 2014 52.34 0.42 -6.52% 1,930.67 -1.51%
7. Aug 31, 2014 55.28 5.62% 2,003.37 3.77%
8. Sep 30, 2014 57.76 4.49% 1,972.29 -1.55%
9. Oct 31, 2014 63.46 0.42 10.60% 2,018.05 2.32%
10. Nov 30, 2014 69.20 9.05% 2,067.56 2.45%
11. Dec 31, 2014 65.44 -5.43% 2,058.90 -0.42%
12. Jan 31, 2015 60.35 0.49 -7.03% 1,994.99 -3.10%
13. Feb 28, 2015 60.50 0.25% 2,104.50 5.49%
14. Mar 31, 2015 58.54 -3.24% 2,067.89 -1.74%
15. Apr 30, 2015 64.66 0.51 11.33% 2,085.51 0.85%
16. May 31, 2015 66.59 2.98% 2,107.39 1.05%
17. Jun 30, 2015 67.19 0.90% 2,063.11 -2.10%
18. Jul 31, 2015 70.01 0.51 4.96% 2,103.84 1.97%
19. Aug 31, 2015 62.41 -10.86% 1,972.18 -6.26%
20. Sep 30, 2015 54.41 -12.82% 1,920.03 -2.64%
21. Oct 31, 2015 59.55 0.51 10.38% 2,079.36 8.30%
22. Nov 30, 2015 58.15 -2.35% 2,080.41 0.05%
23. Dec 31, 2015 59.24 1.87% 2,043.94 -1.75%
24. Jan 31, 2016 54.90 0.57 -6.36% 1,940.24 -5.07%
25. Feb 29, 2016 54.61 -0.53% 1,932.23 -0.41%
26. Mar 31, 2016 57.12 4.60% 2,059.74 6.60%
27. Apr 30, 2016 61.00 0.57 7.79% 2,065.30 0.27%
28. May 31, 2016 62.93 3.16% 2,096.95 1.53%
29. Jun 30, 2016 61.91 -1.62% 2,098.86 0.09%
30. Jul 31, 2016 66.23 0.57 7.90% 2,173.60 3.56%
31. Aug 31, 2016 64.10 -3.22% 2,170.95 -0.12%
32. Sep 30, 2016 63.07 -1.61% 2,168.27 -0.12%
33. Oct 31, 2016 55.78 0.57 -10.65% 2,126.15 -1.94%
34. Nov 30, 2016 60.80 9.00% 2,198.81 3.42%
35. Dec 31, 2016 62.62 2.99% 2,238.83 1.82%
36. Jan 31, 2017 61.11 0.64 -1.39% 2,278.87 1.79%
37. Feb 28, 2017 61.84 1.19% 2,363.64 3.72%
38. Mar 31, 2017 65.16 5.37% 2,362.72 -0.04%
39. Apr 30, 2017 65.94 0.64 2.18% 2,384.20 0.91%
40. May 31, 2017 66.02 0.12% 2,411.80 1.16%
41. Jun 30, 2017 72.51 9.83% 2,423.41 0.48%
42. Jul 31, 2017 69.91 0.64 -2.70% 2,470.30 1.93%
43. Aug 31, 2017 75.30 7.71% 2,471.65 0.05%
44. Sep 30, 2017 88.86 18.01% 2,519.36 1.93%
45. Oct 31, 2017 90.25 0.64 2.28% 2,575.26 2.22%
46. Nov 30, 2017 96.92 7.39% 2,647.58 2.81%
47. Dec 31, 2017 96.71 -0.22% 2,673.61 0.98%
48. Jan 31, 2018 112.22 0.71 16.77% 2,823.81 5.62%
49. Feb 28, 2018 115.83 3.22% 2,713.83 -3.89%
50. Mar 31, 2018 94.65 -18.29% 2,640.87 -2.69%
51. Apr 30, 2018 96.55 0.96 3.02% 2,648.05 0.27%
52. May 31, 2018 98.94 2.48% 2,705.27 2.16%
53. Jun 30, 2018 92.65 -6.36% 2,718.37 0.48%
54. Jul 31, 2018 92.23 0.96 0.58% 2,816.29 3.60%
55. Aug 31, 2018 95.98 4.07% 2,901.52 3.03%
56. Sep 30, 2018 94.58 -1.46% 2,913.98 0.43%
57. Oct 31, 2018 77.85 0.96 -16.67% 2,711.74 -6.94%
58. Nov 30, 2018 94.27 21.09% 2,760.17 1.79%
59. Dec 31, 2018 92.19 -2.21% 2,506.85 -9.18%
Average: 1.63% 0.63%
Standard deviation: 7.50% 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 ABBV 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
VarianceABBV 56.20
VarianceS&P 500 9.77
CovarianceABBV, S&P 500 14.15
Correlation coefficientABBV, S&P 5001 0.60
βABBV2 1.45
αABBV3 0.72

Calculations

1 Correlation coefficientABBV, S&P 500
= CovarianceABBV, S&P 500 ÷ (Standard deviationABBV × Standard deviationS&P 500)
= 14.15 ÷ (7.50 × 3.13)

2 βABBV
= CovarianceABBV, S&P 500 ÷ VarianceS&P 500
= 14.15 ÷ 9.77

3 αABBV
= AverageABBV – βABBV × AverageS&P 500
= 1.631.45 × 0.63


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.18%
Expected rate of return on market portfolio2 E(RM) 11.54%
Systematic risk (β) of AbbVie Inc.’s common stock βABBV 1.45
 
Expected rate of return on AbbVie Inc.’s common stock3 E(RABBV) 15.74%

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(RABBV) = RF + βABBV [E(RM) – RF]
= 2.18% + 1.45 [11.54%2.18%]
= 15.74%