Stock Analysis on Net
Stock Analysis on Net
Microsoft Excel LibreOffice Calc

AbbVie Inc. (NYSE: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 (R): 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 (R): 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


Variance and Covariance

AbbVie Inc., calculation of variance and covariance of returns

Microsoft Excel LibreOffice Calc
t Date RABBV,t RS&P 500,t (RABBV,tRABBV)2 (RS&P 500,tRS&P 500)2 (RABBV,tRABBV)×(RS&P 500,tRS&P 500)
1. Feb 28, 2015 0.25% 5.49% 1.06 21.25 -4.75
2. Mar 31, 2015 -3.24% -1.74% 20.41 6.86 11.83
3. Apr 30, 2015 11.33% 0.85% 100.95 0.00 -0.27
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2019 10.28% 3.40% 81.08 6.38 22.74
59. Dec 31, 2019 0.92% 2.86% 0.13 3.92 -0.70
Total (Σ): 3,512.30 688.77 649.47
t Date RABBV,t RS&P 500,t (RABBV,tRABBV)2 (RS&P 500,tRS&P 500)2 (RABBV,tRABBV)×(RS&P 500,tRS&P 500)
1. Feb 28, 2015 0.25% 5.49% 1.06 21.25 -4.75
2. Mar 31, 2015 -3.24% -1.74% 20.41 6.86 11.83
3. Apr 30, 2015 11.33% 0.85% 100.95 0.00 -0.27
4. May 31, 2015 2.98% 1.05% 2.91 0.03 0.29
5. Jun 30, 2015 0.90% -2.10% 0.14 8.88 1.12
6. Jul 31, 2015 4.96% 1.97% 13.53 1.20 4.03
7. Aug 31, 2015 -10.86% -6.26% 147.23 50.94 86.60
8. Sep 30, 2015 -12.82% -2.64% 198.72 12.41 49.67
9. Oct 31, 2015 10.38% 8.30% 82.92 55.04 67.56
10. Nov 30, 2015 -2.35% 0.05% 13.17 0.69 3.01
11. Dec 31, 2015 1.87% -1.75% 0.36 6.93 -1.57
12. Jan 31, 2016 -6.36% -5.07% 58.40 35.43 45.49
13. Feb 29, 2016 -0.53% -0.41% 3.26 1.67 2.33
14. Mar 31, 2016 4.60% 6.60% 11.01 32.72 18.98
15. Apr 30, 2016 7.79% 0.27% 42.41 0.37 -3.97
16. May 31, 2016 3.16% 1.53% 3.56 0.43 1.23
17. Jun 30, 2016 -1.62% 0.09% 8.40 0.62 2.28
18. Jul 31, 2016 7.90% 3.56% 43.83 7.19 17.75
19. Aug 31, 2016 -3.22% -0.12% 20.20 1.00 4.50
20. Sep 30, 2016 -1.61% -0.12% 8.32 1.01 2.89
21. Oct 31, 2016 -10.65% -1.94% 142.40 7.96 33.67
22. Nov 30, 2016 9.00% 3.42% 59.62 6.44 19.60
23. Dec 31, 2016 2.99% 1.82% 2.94 0.89 1.61
24. Jan 31, 2017 -1.39% 1.79% 7.12 0.83 -2.43
25. Feb 28, 2017 1.19% 3.72% 0.01 8.07 -0.24
26. Mar 31, 2017 5.37% -0.04% 16.73 0.84 -3.76
27. Apr 30, 2017 2.18% 0.91% 0.81 0.00 0.03
28. May 31, 2017 0.12% 1.16% 1.34 0.08 -0.32
29. Jun 30, 2017 9.83% 0.48% 73.14 0.16 -3.40
30. Jul 31, 2017 -2.70% 1.93% 15.85 1.11 -4.20
31. Aug 31, 2017 7.71% 0.05% 41.37 0.68 -5.30
32. Sep 30, 2017 18.01% 1.93% 279.88 1.10 17.59
33. Oct 31, 2017 2.28% 2.22% 1.01 1.79 1.35
34. Nov 30, 2017 7.39% 2.81% 37.36 3.72 11.79
35. Dec 31, 2017 -0.22% 0.98% 2.23 0.01 -0.16
36. Jan 31, 2018 16.77% 5.62% 240.05 22.46 73.42
37. Feb 28, 2018 3.22% -3.89% 3.76 22.79 -9.25
38. Mar 31, 2018 -18.29% -2.69% 382.74 12.73 69.79
39. Apr 30, 2018 3.02% 0.27% 3.04 0.37 -1.06
40. May 31, 2018 2.48% 2.16% 1.43 1.64 1.53
41. Jun 30, 2018 -6.36% 0.48% 58.30 0.16 3.02
42. Jul 31, 2018 0.58% 3.60% 0.48 7.41 -1.89
43. Aug 31, 2018 4.07% 3.03% 7.77 4.61 5.99
44. Sep 30, 2018 -1.46% 0.43% 7.49 0.20 1.23
45. Oct 31, 2018 -16.67% -6.94% 322.27 61.14 140.37
46. Nov 30, 2018 21.09% 1.79% 392.58 0.82 17.97
47. Dec 31, 2018 -2.21% -9.18% 12.14 101.14 35.04
48. Jan 31, 2019 -11.75% 7.87% 169.67 48.85 -91.04
49. Feb 28, 2019 -1.31% 2.97% 6.69 4.38 -5.41
50. Mar 31, 2019 1.70% 1.79% 0.18 0.83 0.39
51. Apr 30, 2019 -0.16% 3.93% 2.07 9.32 -4.39
52. May 31, 2019 -3.38% -6.58% 21.66 55.61 34.70
53. Jun 30, 2019 -5.20% 6.89% 41.99 36.17 -38.97
54. Jul 31, 2019 -6.92% 1.31% 67.16 0.19 -3.55
55. Aug 31, 2019 -1.32% -1.81% 6.76 7.23 6.99
56. Sep 30, 2019 15.18% 1.72% 193.29 0.70 11.66
57. Oct 31, 2019 6.47% 2.04% 26.97 1.36 6.04
58. Nov 30, 2019 10.28% 3.40% 81.08 6.38 22.74
59. Dec 31, 2019 0.92% 2.86% 0.13 3.92 -0.70
Total (Σ): 3,512.30 688.77 649.47

Show all

VarianceABBV = Σ(RABBV,tRABBV)2 ÷ (59 – 1)
= 3,512.30 ÷ (59 – 1)
= 60.56

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

CovarianceABBV, S&P 500 = Σ(RABBV,tRABBV)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 649.47 ÷ (59 – 1)
= 11.20


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%)
= 0.42

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

3 αABBV
= AverageABBV – βABBV × AverageS&P 500
= 1.28%0.94 × 0.88%
= 0.45%


Expected Rate of Return

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

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.68% + 0.94 [12.11%1.68%]
= 11.52%