Stock Analysis on Net

CVS Health Corp. (NYSE:CVS)

Capital Asset Pricing Model (CAPM)

Microsoft Excel

Rates of Return

CVS Health Corp., monthly rates of return

Microsoft Excel
CVS Health Corp. (CVS) Standard & Poor’s 500 (S&P 500)
t Date PriceCVS,t1 DividendCVS,t1 RCVS,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $65.55 2,704.10
1. Feb 28, 2019 $57.83 -11.78% 2,784.49 2.97%
2. Mar 31, 2019 $53.93 -6.74% 2,834.40 1.79%
3. Apr 30, 2019 $54.38 $0.50 1.76% 2,945.83 3.93%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 $67.95 -1.54% 4,567.80 8.92%
59. Dec 31, 2023 $78.96 16.20% 4,769.83 4.42%
Average (R): 0.79% 1.11%
Standard deviation: 7.14% 5.31%
CVS Health Corp. (CVS) Standard & Poor’s 500 (S&P 500)
t Date PriceCVS,t1 DividendCVS,t1 RCVS,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $65.55 2,704.10
1. Feb 28, 2019 $57.83 -11.78% 2,784.49 2.97%
2. Mar 31, 2019 $53.93 -6.74% 2,834.40 1.79%
3. Apr 30, 2019 $54.38 $0.50 1.76% 2,945.83 3.93%
4. May 31, 2019 $52.37 -3.70% 2,752.06 -6.58%
5. Jun 30, 2019 $54.49 4.05% 2,941.76 6.89%
6. Jul 31, 2019 $55.87 $0.50 3.45% 2,980.38 1.31%
7. Aug 31, 2019 $60.92 9.04% 2,926.46 -1.81%
8. Sep 30, 2019 $63.07 3.53% 2,976.74 1.72%
9. Oct 31, 2019 $66.39 $0.50 6.06% 3,037.56 2.04%
10. Nov 30, 2019 $75.27 13.38% 3,140.98 3.40%
11. Dec 31, 2019 $74.29 -1.30% 3,230.78 2.86%
12. Jan 31, 2020 $67.82 $0.50 -8.04% 3,225.52 -0.16%
13. Feb 29, 2020 $59.18 -12.74% 2,954.22 -8.41%
14. Mar 31, 2020 $59.33 0.25% 2,584.59 -12.51%
15. Apr 30, 2020 $61.55 $0.50 4.58% 2,912.43 12.68%
16. May 31, 2020 $65.57 6.53% 3,044.31 4.53%
17. Jun 30, 2020 $64.97 -0.92% 3,100.29 1.84%
18. Jul 31, 2020 $62.94 $0.50 -2.35% 3,271.12 5.51%
19. Aug 31, 2020 $62.12 -1.30% 3,500.31 7.01%
20. Sep 30, 2020 $58.40 -5.99% 3,363.00 -3.92%
21. Oct 31, 2020 $56.09 $0.50 -3.10% 3,269.96 -2.77%
22. Nov 30, 2020 $67.79 20.86% 3,621.63 10.75%
23. Dec 31, 2020 $68.30 0.75% 3,756.07 3.71%
24. Jan 31, 2021 $71.65 $0.50 5.64% 3,714.24 -1.11%
25. Feb 28, 2021 $68.13 -4.91% 3,811.15 2.61%
26. Mar 31, 2021 $75.23 10.42% 3,972.89 4.24%
27. Apr 30, 2021 $76.40 $0.50 2.22% 4,181.17 5.24%
28. May 31, 2021 $86.44 13.14% 4,204.11 0.55%
29. Jun 30, 2021 $83.44 -3.47% 4,297.50 2.22%
30. Jul 31, 2021 $82.36 $0.50 -0.70% 4,395.26 2.27%
31. Aug 31, 2021 $86.39 4.89% 4,522.68 2.90%
32. Sep 30, 2021 $84.86 -1.77% 4,307.54 -4.76%
33. Oct 31, 2021 $89.28 $0.50 5.80% 4,605.38 6.91%
34. Nov 30, 2021 $89.06 -0.25% 4,567.00 -0.83%
35. Dec 31, 2021 $103.16 15.83% 4,766.18 4.36%
36. Jan 31, 2022 $106.51 $0.55 3.78% 4,515.55 -5.26%
37. Feb 28, 2022 $103.65 -2.69% 4,373.94 -3.14%
38. Mar 31, 2022 $101.21 -2.35% 4,530.41 3.58%
39. Apr 30, 2022 $96.13 $0.55 -4.48% 4,131.93 -8.80%
40. May 31, 2022 $96.75 0.64% 4,132.15 0.01%
41. Jun 30, 2022 $92.66 -4.23% 3,785.38 -8.39%
42. Jul 31, 2022 $95.68 $0.55 3.85% 4,130.29 9.11%
43. Aug 31, 2022 $98.15 2.58% 3,955.00 -4.24%
44. Sep 30, 2022 $95.37 -2.83% 3,585.62 -9.34%
45. Oct 31, 2022 $94.70 $0.55 -0.13% 3,871.98 7.99%
46. Nov 30, 2022 $101.88 7.58% 4,080.11 5.38%
47. Dec 31, 2022 $93.19 -8.53% 3,839.50 -5.90%
48. Jan 31, 2023 $88.22 $0.605 -4.68% 4,076.60 6.18%
49. Feb 28, 2023 $83.54 -5.30% 3,970.15 -2.61%
50. Mar 31, 2023 $74.31 -11.05% 4,109.31 3.51%
51. Apr 30, 2023 $73.31 $0.605 -0.53% 4,169.48 1.46%
52. May 31, 2023 $68.03 -7.20% 4,179.83 0.25%
53. Jun 30, 2023 $69.13 1.62% 4,376.86 4.71%
54. Jul 31, 2023 $74.69 $0.605 8.92% 4,588.96 4.85%
55. Aug 31, 2023 $65.17 -12.75% 4,507.66 -1.77%
56. Sep 30, 2023 $69.82 7.14% 4,288.05 -4.87%
57. Oct 31, 2023 $69.01 $0.605 -0.29% 4,193.80 -2.20%
58. Nov 30, 2023 $67.95 -1.54% 4,567.80 8.92%
59. Dec 31, 2023 $78.96 16.20% 4,769.83 4.42%
Average (R): 0.79% 1.11%
Standard deviation: 7.14% 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 CVS during period t

3 Rate of return on S&P 500 (the market portfolio proxy) during period t


Variance and Covariance

CVS Health Corp., calculation of variance and covariance of returns

Microsoft Excel
t Date RCVS,t RS&P 500,t (RCVS,tRCVS)2 (RS&P 500,tRS&P 500)2 (RCVS,tRCVS)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 -11.78% 2.97% 158.05 3.49 -23.47
2. Mar 31, 2019 -6.74% 1.79% 56.83 0.47 -5.18
3. Apr 30, 2019 1.76% 3.93% 0.94 7.98 2.73
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 -1.54% 8.92% 5.43 61.03 -18.21
59. Dec 31, 2023 16.20% 4.42% 237.43 11.00 51.11
Total (Σ): 2,959.32 1,634.30 840.23
t Date RCVS,t RS&P 500,t (RCVS,tRCVS)2 (RS&P 500,tRS&P 500)2 (RCVS,tRCVS)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 -11.78% 2.97% 158.05 3.49 -23.47
2. Mar 31, 2019 -6.74% 1.79% 56.83 0.47 -5.18
3. Apr 30, 2019 1.76% 3.93% 0.94 7.98 2.73
4. May 31, 2019 -3.70% -6.58% 20.17 59.04 34.50
5. Jun 30, 2019 4.05% 6.89% 10.59 33.49 18.83
6. Jul 31, 2019 3.45% 1.31% 7.05 0.04 0.55
7. Aug 31, 2019 9.04% -1.81% 67.97 8.50 -24.03
8. Sep 30, 2019 3.53% 1.72% 7.48 0.37 1.67
9. Oct 31, 2019 6.06% 2.04% 27.69 0.88 4.93
10. Nov 30, 2019 13.38% 3.40% 158.28 5.28 28.92
11. Dec 31, 2019 -1.30% 2.86% 4.39 3.07 -3.68
12. Jan 31, 2020 -8.04% -0.16% 77.98 1.61 11.20
13. Feb 29, 2020 -12.74% -8.41% 183.17 90.57 128.80
14. Mar 31, 2020 0.25% -12.51% 0.29 185.44 7.37
15. Apr 30, 2020 4.58% 12.68% 14.37 134.06 43.88
16. May 31, 2020 6.53% 4.53% 32.91 11.71 19.63
17. Jun 30, 2020 -0.92% 1.84% 2.92 0.54 -1.25
18. Jul 31, 2020 -2.35% 5.51% 9.92 19.40 -13.87
19. Aug 31, 2020 -1.30% 7.01% 4.40 34.82 -12.37
20. Sep 30, 2020 -5.99% -3.92% 46.01 25.29 34.11
21. Oct 31, 2020 -3.10% -2.77% 15.16 15.00 15.08
22. Nov 30, 2020 20.86% 10.75% 402.60 93.10 193.60
23. Dec 31, 2020 0.75% 3.71% 0.00 6.79 -0.11
24. Jan 31, 2021 5.64% -1.11% 23.45 4.93 -10.75
25. Feb 28, 2021 -4.91% 2.61% 32.57 2.26 -8.58
26. Mar 31, 2021 10.42% 4.24% 92.68 9.85 30.21
27. Apr 30, 2021 2.22% 5.24% 2.03 17.11 5.90
28. May 31, 2021 13.14% 0.55% 152.45 0.31 -6.88
29. Jun 30, 2021 -3.47% 2.22% 18.19 1.24 -4.76
30. Jul 31, 2021 -0.70% 2.27% 2.22 1.37 -1.74
31. Aug 31, 2021 4.89% 2.90% 16.80 3.22 7.35
32. Sep 30, 2021 -1.77% -4.76% 6.58 34.37 15.04
33. Oct 31, 2021 5.80% 6.91% 25.03 33.74 29.06
34. Nov 30, 2021 -0.25% -0.83% 1.08 3.76 2.02
35. Dec 31, 2021 15.83% 4.36% 226.13 10.60 48.95
36. Jan 31, 2022 3.78% -5.26% 8.92 40.51 -19.00
37. Feb 28, 2022 -2.69% -3.14% 12.11 17.99 14.76
38. Mar 31, 2022 -2.35% 3.58% 9.91 6.11 -7.78
39. Apr 30, 2022 -4.48% -8.80% 27.78 98.04 52.18
40. May 31, 2022 0.64% 0.01% 0.02 1.21 0.16
41. Jun 30, 2022 -4.23% -8.39% 25.22 90.21 47.70
42. Jul 31, 2022 3.85% 9.11% 9.35 64.09 24.48
43. Aug 31, 2022 2.58% -4.24% 3.19 28.62 -9.56
44. Sep 30, 2022 -2.83% -9.34% 13.15 109.11 37.88
45. Oct 31, 2022 -0.13% 7.99% 0.85 47.34 -6.33
46. Nov 30, 2022 7.58% 5.38% 46.07 18.23 28.98
47. Dec 31, 2022 -8.53% -5.90% 86.94 49.04 65.30
48. Jan 31, 2023 -4.68% 6.18% 30.01 25.70 -27.77
49. Feb 28, 2023 -5.30% -2.61% 37.20 13.82 22.67
50. Mar 31, 2023 -11.05% 3.51% 140.26 5.76 -28.42
51. Apr 30, 2023 -0.53% 1.46% 1.76 0.13 -0.48
52. May 31, 2023 -7.20% 0.25% 63.95 0.74 6.86
53. Jun 30, 2023 1.62% 4.71% 0.68 13.02 2.97
54. Jul 31, 2023 8.92% 4.85% 65.99 13.99 30.38
55. Aug 31, 2023 -12.75% -1.77% 183.34 8.28 38.96
56. Sep 30, 2023 7.14% -4.87% 40.21 35.73 -37.90
57. Oct 31, 2023 -0.29% -2.20% 1.18 10.92 3.59
58. Nov 30, 2023 -1.54% 8.92% 5.43 61.03 -18.21
59. Dec 31, 2023 16.20% 4.42% 237.43 11.00 51.11
Total (Σ): 2,959.32 1,634.30 840.23

Show all

VarianceCVS = Σ(RCVS,tRCVS)2 ÷ (59 – 1)
= 2,959.32 ÷ (59 – 1)
= 51.02

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

CovarianceCVS, S&P 500 = Σ(RCVS,tRCVS)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 840.23 ÷ (59 – 1)
= 14.49


Systematic Risk (β) Estimation

Microsoft Excel
VarianceCVS 51.02
VarianceS&P 500 28.18
CovarianceCVS, S&P 500 14.49
Correlation coefficientCVS, S&P 5001 0.38
βCVS2 0.51
αCVS3 0.23%

Calculations

1 Correlation coefficientCVS, S&P 500
= CovarianceCVS, S&P 500 ÷ (Standard deviationCVS × Standard deviationS&P 500)
= 14.49 ÷ (7.14% × 5.31%)
= 0.38

2 βCVS
= CovarianceCVS, S&P 500 ÷ VarianceS&P 500
= 14.49 ÷ 28.18
= 0.51

3 αCVS
= AverageCVS – βCVS × AverageS&P 500
= 0.79%0.51 × 1.11%
= 0.23%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 4.68%
Expected rate of return on market portfolio2 E(RM) 13.78%
Systematic risk (β) of CVS Health Corp. common stock βCVS 0.51
 
Expected rate of return on CVS Health Corp. common stock3 E(RCVS) 9.36%

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(RCVS) = RF + βCVS [E(RM) – RF]
= 4.68% + 0.51 [13.78%4.68%]
= 9.36%