Stock Analysis on Net

AbbVie Inc. (NYSE:ABBV)

Capital Asset Pricing Model (CAPM)

Microsoft Excel

Rates of Return

AbbVie Inc., monthly rates of return

Microsoft Excel
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, 2019 $80.29 2,704.10
1. Feb 28, 2019 $79.24 -1.31% 2,784.49 2.97%
2. Mar 31, 2019 $80.59 1.70% 2,834.40 1.79%
3. Apr 30, 2019 $79.39 $1.07 -0.16% 2,945.83 3.93%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 $142.39 0.86% 4,567.80 8.92%
59. Dec 31, 2023 $154.97 8.83% 4,769.83 4.42%
Average (R): 1.74% 1.11%
Standard deviation: 7.10% 5.31%
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, 2019 $80.29 2,704.10
1. Feb 28, 2019 $79.24 -1.31% 2,784.49 2.97%
2. Mar 31, 2019 $80.59 1.70% 2,834.40 1.79%
3. Apr 30, 2019 $79.39 $1.07 -0.16% 2,945.83 3.93%
4. May 31, 2019 $76.71 -3.38% 2,752.06 -6.58%
5. Jun 30, 2019 $72.72 -5.20% 2,941.76 6.89%
6. Jul 31, 2019 $66.62 $1.07 -6.92% 2,980.38 1.31%
7. Aug 31, 2019 $65.74 -1.32% 2,926.46 -1.81%
8. Sep 30, 2019 $75.72 15.18% 2,976.74 1.72%
9. Oct 31, 2019 $79.55 $1.07 6.47% 3,037.56 2.04%
10. Nov 30, 2019 $87.73 10.28% 3,140.98 3.40%
11. Dec 31, 2019 $88.54 0.92% 3,230.78 2.86%
12. Jan 31, 2020 $81.02 $1.18 -7.16% 3,225.52 -0.16%
13. Feb 29, 2020 $85.71 5.79% 2,954.22 -8.41%
14. Mar 31, 2020 $76.19 -11.11% 2,584.59 -12.51%
15. Apr 30, 2020 $82.20 $1.18 9.44% 2,912.43 12.68%
16. May 31, 2020 $92.67 12.74% 3,044.31 4.53%
17. Jun 30, 2020 $98.18 5.95% 3,100.29 1.84%
18. Jul 31, 2020 $94.91 $1.18 -2.13% 3,271.12 5.51%
19. Aug 31, 2020 $95.77 0.91% 3,500.31 7.01%
20. Sep 30, 2020 $87.59 -8.54% 3,363.00 -3.92%
21. Oct 31, 2020 $85.10 $1.18 -1.50% 3,269.96 -2.77%
22. Nov 30, 2020 $104.58 22.89% 3,621.63 10.75%
23. Dec 31, 2020 $107.15 2.46% 3,756.07 3.71%
24. Jan 31, 2021 $102.48 $1.30 -3.15% 3,714.24 -1.11%
25. Feb 28, 2021 $107.74 5.13% 3,811.15 2.61%
26. Mar 31, 2021 $108.22 0.45% 3,972.89 4.24%
27. Apr 30, 2021 $111.50 $1.30 4.23% 4,181.17 5.24%
28. May 31, 2021 $113.20 1.52% 4,204.11 0.55%
29. Jun 30, 2021 $112.64 -0.49% 4,297.50 2.22%
30. Jul 31, 2021 $116.30 $1.30 4.40% 4,395.26 2.27%
31. Aug 31, 2021 $120.78 3.85% 4,522.68 2.90%
32. Sep 30, 2021 $107.87 -10.69% 4,307.54 -4.76%
33. Oct 31, 2021 $114.67 $1.30 7.51% 4,605.38 6.91%
34. Nov 30, 2021 $115.28 0.53% 4,567.00 -0.83%
35. Dec 31, 2021 $135.40 17.45% 4,766.18 4.36%
36. Jan 31, 2022 $136.89 $1.41 2.14% 4,515.55 -5.26%
37. Feb 28, 2022 $147.77 7.95% 4,373.94 -3.14%
38. Mar 31, 2022 $162.11 9.70% 4,530.41 3.58%
39. Apr 30, 2022 $146.88 $1.41 -8.53% 4,131.93 -8.80%
40. May 31, 2022 $147.37 0.33% 4,132.15 0.01%
41. Jun 30, 2022 $153.16 3.93% 3,785.38 -8.39%
42. Jul 31, 2022 $143.51 $1.41 -5.38% 4,130.29 9.11%
43. Aug 31, 2022 $134.46 -6.31% 3,955.00 -4.24%
44. Sep 30, 2022 $134.21 -0.19% 3,585.62 -9.34%
45. Oct 31, 2022 $146.40 $1.41 10.13% 3,871.98 7.99%
46. Nov 30, 2022 $161.18 10.10% 4,080.11 5.38%
47. Dec 31, 2022 $161.61 0.27% 3,839.50 -5.90%
48. Jan 31, 2023 $147.75 $1.48 -7.66% 4,076.60 6.18%
49. Feb 28, 2023 $153.90 4.16% 3,970.15 -2.61%
50. Mar 31, 2023 $159.37 3.55% 4,109.31 3.51%
51. Apr 30, 2023 $151.12 $1.48 -4.25% 4,169.48 1.46%
52. May 31, 2023 $137.96 -8.71% 4,179.83 0.25%
53. Jun 30, 2023 $134.73 -2.34% 4,376.86 4.71%
54. Jul 31, 2023 $149.58 $1.48 12.12% 4,588.96 4.85%
55. Aug 31, 2023 $146.96 -1.75% 4,507.66 -1.77%
56. Sep 30, 2023 $149.06 1.43% 4,288.05 -4.87%
57. Oct 31, 2023 $141.18 $1.48 -4.29% 4,193.80 -2.20%
58. Nov 30, 2023 $142.39 0.86% 4,567.80 8.92%
59. Dec 31, 2023 $154.97 8.83% 4,769.83 4.42%
Average (R): 1.74% 1.11%
Standard deviation: 7.10% 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 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
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, 2019 -1.31% 2.97% 9.31 3.49 -5.70
2. Mar 31, 2019 1.70% 1.79% 0.00 0.47 -0.03
3. Apr 30, 2019 -0.16% 3.93% 3.63 7.98 -5.38
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 0.86% 8.92% 0.79 61.03 -6.93
59. Dec 31, 2023 8.83% 4.42% 50.29 11.00 23.52
Total (Σ): 2,925.10 1,634.30 948.59
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, 2019 -1.31% 2.97% 9.31 3.49 -5.70
2. Mar 31, 2019 1.70% 1.79% 0.00 0.47 -0.03
3. Apr 30, 2019 -0.16% 3.93% 3.63 7.98 -5.38
4. May 31, 2019 -3.38% -6.58% 26.21 59.04 39.34
5. Jun 30, 2019 -5.20% 6.89% 48.23 33.49 -40.19
6. Jul 31, 2019 -6.92% 1.31% 75.01 0.04 -1.79
7. Aug 31, 2019 -1.32% -1.81% 9.39 8.50 8.93
8. Sep 30, 2019 15.18% 1.72% 180.56 0.37 8.23
9. Oct 31, 2019 6.47% 2.04% 22.35 0.88 4.43
10. Nov 30, 2019 10.28% 3.40% 72.92 5.28 19.63
11. Dec 31, 2019 0.92% 2.86% 0.67 3.07 -1.44
12. Jan 31, 2020 -7.16% -0.16% 79.29 1.61 11.30
13. Feb 29, 2020 5.79% -8.41% 16.36 90.57 -38.50
14. Mar 31, 2020 -11.11% -12.51% 165.14 185.44 175.00
15. Apr 30, 2020 9.44% 12.68% 59.19 134.06 89.08
16. May 31, 2020 12.74% 4.53% 120.86 11.71 37.62
17. Jun 30, 2020 5.95% 1.84% 17.66 0.54 3.08
18. Jul 31, 2020 -2.13% 5.51% 15.00 19.40 -17.05
19. Aug 31, 2020 0.91% 7.01% 0.70 34.82 -4.94
20. Sep 30, 2020 -8.54% -3.92% 105.78 25.29 51.72
21. Oct 31, 2020 -1.50% -2.77% 10.49 15.00 12.54
22. Nov 30, 2020 22.89% 10.75% 447.20 93.10 204.04
23. Dec 31, 2020 2.46% 3.71% 0.51 6.79 1.86
24. Jan 31, 2021 -3.15% -1.11% 23.90 4.93 10.85
25. Feb 28, 2021 5.13% 2.61% 11.49 2.26 5.09
26. Mar 31, 2021 0.45% 4.24% 1.69 9.85 -4.07
27. Apr 30, 2021 4.23% 5.24% 6.19 17.11 10.29
28. May 31, 2021 1.52% 0.55% 0.05 0.31 0.12
29. Jun 30, 2021 -0.49% 2.22% 5.01 1.24 -2.50
30. Jul 31, 2021 4.40% 2.27% 7.07 1.37 3.11
31. Aug 31, 2021 3.85% 2.90% 4.45 3.22 3.78
32. Sep 30, 2021 -10.69% -4.76% 154.57 34.37 72.89
33. Oct 31, 2021 7.51% 6.91% 33.24 33.74 33.49
34. Nov 30, 2021 0.53% -0.83% 1.47 3.76 2.35
35. Dec 31, 2021 17.45% 4.36% 246.79 10.60 51.14
36. Jan 31, 2022 2.14% -5.26% 0.16 40.51 -2.53
37. Feb 28, 2022 7.95% -3.14% 38.49 17.99 -26.32
38. Mar 31, 2022 9.70% 3.58% 63.37 6.11 19.67
39. Apr 30, 2022 -8.53% -8.80% 105.45 98.04 101.68
40. May 31, 2022 0.33% 0.01% 1.99 1.21 1.55
41. Jun 30, 2022 3.93% -8.39% 4.78 90.21 -20.76
42. Jul 31, 2022 -5.38% 9.11% 50.75 64.09 -57.03
43. Aug 31, 2022 -6.31% -4.24% 64.80 28.62 43.07
44. Sep 30, 2022 -0.19% -9.34% 3.72 109.11 20.15
45. Oct 31, 2022 10.13% 7.99% 70.39 47.34 57.73
46. Nov 30, 2022 10.10% 5.38% 69.76 18.23 35.66
47. Dec 31, 2022 0.27% -5.90% 2.18 49.04 10.34
48. Jan 31, 2023 -7.66% 6.18% 88.44 25.70 -47.67
49. Feb 28, 2023 4.16% -2.61% 5.85 13.82 -8.99
50. Mar 31, 2023 3.55% 3.51% 3.28 5.76 4.34
51. Apr 30, 2023 -4.25% 1.46% 35.90 0.13 -2.15
52. May 31, 2023 -8.71% 0.25% 109.24 0.74 8.96
53. Jun 30, 2023 -2.34% 4.71% 16.69 13.02 -14.74
54. Jul 31, 2023 12.12% 4.85% 107.68 13.99 38.81
55. Aug 31, 2023 -1.75% -1.77% 12.22 8.28 10.06
56. Sep 30, 2023 1.43% -4.87% 0.10 35.73 1.88
57. Oct 31, 2023 -4.29% -2.20% 36.45 10.92 19.95
58. Nov 30, 2023 0.86% 8.92% 0.79 61.03 -6.93
59. Dec 31, 2023 8.83% 4.42% 50.29 11.00 23.52
Total (Σ): 2,925.10 1,634.30 948.59

Show all

VarianceABBV = Σ(RABBV,tRABBV)2 ÷ (59 – 1)
= 2,925.10 ÷ (59 – 1)
= 50.43

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

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


Systematic Risk (β) Estimation

Microsoft Excel
VarianceABBV 50.43
VarianceS&P 500 28.18
CovarianceABBV, S&P 500 16.35
Correlation coefficientABBV, S&P 5001 0.43
βABBV2 0.58
αABBV3 1.10%

Calculations

1 Correlation coefficientABBV, S&P 500
= CovarianceABBV, S&P 500 ÷ (Standard deviationABBV × Standard deviationS&P 500)
= 16.35 ÷ (7.10% × 5.31%)
= 0.43

2 βABBV
= CovarianceABBV, S&P 500 ÷ VarianceS&P 500
= 16.35 ÷ 28.18
= 0.58

3 αABBV
= AverageABBV – βABBV × AverageS&P 500
= 1.74%0.58 × 1.11%
= 1.10%


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 AbbVie Inc. common stock βABBV 0.58
 
Expected rate of return on AbbVie Inc. common stock3 E(RABBV) 9.75%

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]
= 4.42% + 0.58 [13.61%4.42%]
= 9.75%