Microsoft Excel LibreOffice Calc

AbbVie Inc. (ABBV)


Capital Asset Pricing Model (CAPM)

Intermediate level


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, 2015 60.35 1,994.99
1. Feb 28, 2015 60.50 0.25% 2,104.50 5.49%
2. Mar 31, 2015 58.54 -3.24% 2,067.89 -1.74%
3. Apr 30, 2015 64.66 0.51 11.33% 2,085.51 0.85%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2019 87.73 10.28% 3,140.98 3.40%
59. Dec 31, 2019 88.54 0.92% 3,230.78 2.86%
Average: 1.28% 0.88%
Standard deviation: 7.78% 3.45%
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, 2015 60.35 1,994.99
1. Feb 28, 2015 60.50 0.25% 2,104.50 5.49%
2. Mar 31, 2015 58.54 -3.24% 2,067.89 -1.74%
3. Apr 30, 2015 64.66 0.51 11.33% 2,085.51 0.85%
4. May 31, 2015 66.59 2.98% 2,107.39 1.05%
5. Jun 30, 2015 67.19 0.90% 2,063.11 -2.10%
6. Jul 31, 2015 70.01 0.51 4.96% 2,103.84 1.97%
7. Aug 31, 2015 62.41 -10.86% 1,972.18 -6.26%
8. Sep 30, 2015 54.41 -12.82% 1,920.03 -2.64%
9. Oct 31, 2015 59.55 0.51 10.38% 2,079.36 8.30%
10. Nov 30, 2015 58.15 -2.35% 2,080.41 0.05%
11. Dec 31, 2015 59.24 1.87% 2,043.94 -1.75%
12. Jan 31, 2016 54.90 0.57 -6.36% 1,940.24 -5.07%
13. Feb 29, 2016 54.61 -0.53% 1,932.23 -0.41%
14. Mar 31, 2016 57.12 4.60% 2,059.74 6.60%
15. Apr 30, 2016 61.00 0.57 7.79% 2,065.30 0.27%
16. May 31, 2016 62.93 3.16% 2,096.95 1.53%
17. Jun 30, 2016 61.91 -1.62% 2,098.86 0.09%
18. Jul 31, 2016 66.23 0.57 7.90% 2,173.60 3.56%
19. Aug 31, 2016 64.10 -3.22% 2,170.95 -0.12%
20. Sep 30, 2016 63.07 -1.61% 2,168.27 -0.12%
21. Oct 31, 2016 55.78 0.57 -10.65% 2,126.15 -1.94%
22. Nov 30, 2016 60.80 9.00% 2,198.81 3.42%
23. Dec 31, 2016 62.62 2.99% 2,238.83 1.82%
24. Jan 31, 2017 61.11 0.64 -1.39% 2,278.87 1.79%
25. Feb 28, 2017 61.84 1.19% 2,363.64 3.72%
26. Mar 31, 2017 65.16 5.37% 2,362.72 -0.04%
27. Apr 30, 2017 65.94 0.64 2.18% 2,384.20 0.91%
28. May 31, 2017 66.02 0.12% 2,411.80 1.16%
29. Jun 30, 2017 72.51 9.83% 2,423.41 0.48%
30. Jul 31, 2017 69.91 0.64 -2.70% 2,470.30 1.93%
31. Aug 31, 2017 75.30 7.71% 2,471.65 0.05%
32. Sep 30, 2017 88.86 18.01% 2,519.36 1.93%
33. Oct 31, 2017 90.25 0.64 2.28% 2,575.26 2.22%
34. Nov 30, 2017 96.92 7.39% 2,647.58 2.81%
35. Dec 31, 2017 96.71 -0.22% 2,673.61 0.98%
36. Jan 31, 2018 112.22 0.71 16.77% 2,823.81 5.62%
37. Feb 28, 2018 115.83 3.22% 2,713.83 -3.89%
38. Mar 31, 2018 94.65 -18.29% 2,640.87 -2.69%
39. Apr 30, 2018 96.55 0.96 3.02% 2,648.05 0.27%
40. May 31, 2018 98.94 2.48% 2,705.27 2.16%
41. Jun 30, 2018 92.65 -6.36% 2,718.37 0.48%
42. Jul 31, 2018 92.23 0.96 0.58% 2,816.29 3.60%
43. Aug 31, 2018 95.98 4.07% 2,901.52 3.03%
44. Sep 30, 2018 94.58 -1.46% 2,913.98 0.43%
45. Oct 31, 2018 77.85 0.96 -16.67% 2,711.74 -6.94%
46. Nov 30, 2018 94.27 21.09% 2,760.17 1.79%
47. Dec 31, 2018 92.19 -2.21% 2,506.85 -9.18%
48. Jan 31, 2019 80.29 1.07 -11.75% 2,704.10 7.87%
49. Feb 28, 2019 79.24 -1.31% 2,784.49 2.97%
50. Mar 31, 2019 80.59 1.70% 2,834.40 1.79%
51. Apr 30, 2019 79.39 1.07 -0.16% 2,945.83 3.93%
52. May 31, 2019 76.71 -3.38% 2,752.06 -6.58%
53. Jun 30, 2019 72.72 -5.20% 2,941.76 6.89%
54. Jul 31, 2019 66.62 1.07 -6.92% 2,980.38 1.31%
55. Aug 31, 2019 65.74 -1.32% 2,926.46 -1.81%
56. Sep 30, 2019 75.72 15.18% 2,976.74 1.72%
57. Oct 31, 2019 79.55 1.07 6.47% 3,037.56 2.04%
58. Nov 30, 2019 87.73 10.28% 3,140.98 3.40%
59. Dec 31, 2019 88.54 0.92% 3,230.78 2.86%
Average: 1.28% 0.88%
Standard deviation: 7.78% 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 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 60.56
VarianceS&P 500 11.88
CovarianceABBV, S&P 500 11.20
Correlation coefficientABBV, S&P 5001 0.42
βABBV2 0.94
αABBV3 0.45

Calculations

1 Correlation coefficientABBV, S&P 500
= CovarianceABBV, S&P 500 ÷ (Standard deviationABBV × Standard deviationS&P 500)
= 11.20 ÷ (7.78 × 3.45)

2 βABBV
= CovarianceABBV, S&P 500 ÷ VarianceS&P 500
= 11.20 ÷ 11.88

3 αABBV
= AverageABBV – βABBV × AverageS&P 500
= 1.280.94 × 0.88


Expected Rate of Return

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

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]
= 1.19% + 0.94 [11.91%1.19%]
= 11.30%