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, 2017 $78.81 2,278.87
1. Feb 28, 2017 $80.58 2.25% 2,363.64 3.72%
2. Mar 31, 2017 $78.50 -2.58% 2,362.72 -0.04%
3. Apr 30, 2017 $82.44 $0.50 5.66% 2,384.20 0.91%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 $89.06 -0.25% 4,567.00 -0.83%
59. Dec 31, 2021 $103.16 15.83% 4,766.18 4.36%
Average (R): 1.02% 1.36%
Standard deviation: 8.16% 4.48%
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, 2017 $78.81 2,278.87
1. Feb 28, 2017 $80.58 2.25% 2,363.64 3.72%
2. Mar 31, 2017 $78.50 -2.58% 2,362.72 -0.04%
3. Apr 30, 2017 $82.44 $0.50 5.66% 2,384.20 0.91%
4. May 31, 2017 $76.83 -6.80% 2,411.80 1.16%
5. Jun 30, 2017 $80.46 4.72% 2,423.41 0.48%
6. Jul 31, 2017 $79.93 $0.50 -0.04% 2,470.30 1.93%
7. Aug 31, 2017 $77.34 -3.24% 2,471.65 0.05%
8. Sep 30, 2017 $81.32 5.15% 2,519.36 1.93%
9. Oct 31, 2017 $68.53 $0.50 -15.11% 2,575.26 2.22%
10. Nov 30, 2017 $76.60 11.78% 2,647.58 2.81%
11. Dec 31, 2017 $72.50 -5.35% 2,673.61 0.98%
12. Jan 31, 2018 $78.69 $0.50 9.23% 2,823.81 5.62%
13. Feb 28, 2018 $67.73 -13.93% 2,713.83 -3.89%
14. Mar 31, 2018 $62.21 -8.15% 2,640.87 -2.69%
15. Apr 30, 2018 $69.83 $0.50 13.05% 2,648.05 0.27%
16. May 31, 2018 $63.39 -9.22% 2,705.27 2.16%
17. Jun 30, 2018 $64.35 1.51% 2,718.37 0.48%
18. Jul 31, 2018 $64.86 $0.50 1.57% 2,816.29 3.60%
19. Aug 31, 2018 $75.24 16.00% 2,901.52 3.03%
20. Sep 30, 2018 $78.72 4.63% 2,913.98 0.43%
21. Oct 31, 2018 $72.39 $0.50 -7.41% 2,711.74 -6.94%
22. Nov 30, 2018 $80.20 10.79% 2,760.17 1.79%
23. Dec 31, 2018 $65.52 -18.30% 2,506.85 -9.18%
24. Jan 31, 2019 $65.55 $0.50 0.81% 2,704.10 7.87%
25. Feb 28, 2019 $57.83 -11.78% 2,784.49 2.97%
26. Mar 31, 2019 $53.93 -6.74% 2,834.40 1.79%
27. Apr 30, 2019 $54.38 $0.50 1.76% 2,945.83 3.93%
28. May 31, 2019 $52.37 -3.70% 2,752.06 -6.58%
29. Jun 30, 2019 $54.49 4.05% 2,941.76 6.89%
30. Jul 31, 2019 $55.87 $0.50 3.45% 2,980.38 1.31%
31. Aug 31, 2019 $60.92 9.04% 2,926.46 -1.81%
32. Sep 30, 2019 $63.07 3.53% 2,976.74 1.72%
33. Oct 31, 2019 $66.39 $0.50 6.06% 3,037.56 2.04%
34. Nov 30, 2019 $75.27 13.38% 3,140.98 3.40%
35. Dec 31, 2019 $74.29 -1.30% 3,230.78 2.86%
36. Jan 31, 2020 $67.82 $0.50 -8.04% 3,225.52 -0.16%
37. Feb 29, 2020 $59.18 -12.74% 2,954.22 -8.41%
38. Mar 31, 2020 $59.33 0.25% 2,584.59 -12.51%
39. Apr 30, 2020 $61.55 $0.50 4.58% 2,912.43 12.68%
40. May 31, 2020 $65.57 6.53% 3,044.31 4.53%
41. Jun 30, 2020 $64.97 -0.92% 3,100.29 1.84%
42. Jul 31, 2020 $62.94 $0.50 -2.35% 3,271.12 5.51%
43. Aug 31, 2020 $62.12 -1.30% 3,500.31 7.01%
44. Sep 30, 2020 $58.40 -5.99% 3,363.00 -3.92%
45. Oct 31, 2020 $56.09 $0.50 -3.10% 3,269.96 -2.77%
46. Nov 30, 2020 $67.79 20.86% 3,621.63 10.75%
47. Dec 31, 2020 $68.30 0.75% 3,756.07 3.71%
48. Jan 31, 2021 $71.65 $0.50 5.64% 3,714.24 -1.11%
49. Feb 28, 2021 $68.13 -4.91% 3,811.15 2.61%
50. Mar 31, 2021 $75.23 10.42% 3,972.89 4.24%
51. Apr 30, 2021 $76.40 $0.50 2.22% 4,181.17 5.24%
52. May 31, 2021 $86.44 13.14% 4,204.11 0.55%
53. Jun 30, 2021 $83.44 -3.47% 4,297.50 2.22%
54. Jul 31, 2021 $82.36 $0.50 -0.70% 4,395.26 2.27%
55. Aug 31, 2021 $86.39 4.89% 4,522.68 2.90%
56. Sep 30, 2021 $84.86 -1.77% 4,307.54 -4.76%
57. Oct 31, 2021 $89.28 $0.50 5.80% 4,605.38 6.91%
58. Nov 30, 2021 $89.06 -0.25% 4,567.00 -0.83%
59. Dec 31, 2021 $103.16 15.83% 4,766.18 4.36%
Average (R): 1.02% 1.36%
Standard deviation: 8.16% 4.48%

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, 2017 2.25% 3.72% 1.50 5.58 2.90
2. Mar 31, 2017 -2.58% -0.04% 12.96 1.95 5.03
3. Apr 30, 2017 5.66% 0.91% 21.50 0.20 -2.08
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 -0.25% -0.83% 1.60 4.80 2.77
59. Dec 31, 2021 15.83% 4.36% 219.42 9.02 44.49
Total (Σ): 3,860.55 1,164.17 999.28
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, 2017 2.25% 3.72% 1.50 5.58 2.90
2. Mar 31, 2017 -2.58% -0.04% 12.96 1.95 5.03
3. Apr 30, 2017 5.66% 0.91% 21.50 0.20 -2.08
4. May 31, 2017 -6.80% 1.16% 61.22 0.04 1.57
5. Jun 30, 2017 4.72% 0.48% 13.73 0.77 -3.25
6. Jul 31, 2017 -0.04% 1.93% 1.12 0.33 -0.61
7. Aug 31, 2017 -3.24% 0.05% 18.14 1.70 5.55
8. Sep 30, 2017 5.15% 1.93% 17.03 0.33 2.36
9. Oct 31, 2017 -15.11% 2.22% 260.25 0.74 -13.89
10. Nov 30, 2017 11.78% 2.81% 115.71 2.10 15.60
11. Dec 31, 2017 -5.35% 0.98% 40.60 0.14 2.39
12. Jan 31, 2018 9.23% 5.62% 67.38 18.15 34.97
13. Feb 28, 2018 -13.93% -3.89% 223.42 27.59 78.51
14. Mar 31, 2018 -8.15% -2.69% 84.07 16.37 37.10
15. Apr 30, 2018 13.05% 0.27% 144.80 1.18 -13.07
16. May 31, 2018 -9.22% 2.16% 104.89 0.64 -8.22
17. Jun 30, 2018 1.51% 0.48% 0.25 0.76 -0.43
18. Jul 31, 2018 1.57% 3.60% 0.30 5.04 1.24
19. Aug 31, 2018 16.00% 3.03% 224.54 2.78 25.00
20. Sep 30, 2018 4.63% 0.43% 13.00 0.86 -3.35
21. Oct 31, 2018 -7.41% -6.94% 70.98 68.86 69.91
22. Nov 30, 2018 10.79% 1.79% 95.45 0.18 4.18
23. Dec 31, 2018 -18.30% -9.18% 373.39 111.00 203.58
24. Jan 31, 2019 0.81% 7.87% 0.04 42.39 -1.37
25. Feb 28, 2019 -11.78% 2.97% 163.75 2.61 -20.67
26. Mar 31, 2019 -6.74% 1.79% 60.27 0.19 -3.37
27. Apr 30, 2019 1.76% 3.93% 0.55 6.62 1.91
28. May 31, 2019 -3.70% -6.58% 22.23 62.97 37.42
29. Jun 30, 2019 4.05% 6.89% 9.17 30.64 16.77
30. Jul 31, 2019 3.45% 1.31% 5.91 0.00 -0.11
31. Aug 31, 2019 9.04% -1.81% 64.32 10.03 -25.40
32. Sep 30, 2019 3.53% 1.72% 6.30 0.13 0.90
33. Oct 31, 2019 6.06% 2.04% 25.38 0.47 3.45
34. Nov 30, 2019 13.38% 3.40% 152.68 4.19 25.29
35. Dec 31, 2019 -1.30% 2.86% 5.39 2.25 -3.48
36. Jan 31, 2020 -8.04% -0.16% 82.00 2.31 13.77
37. Feb 29, 2020 -12.74% -8.41% 189.30 95.43 134.41
38. Mar 31, 2020 0.25% -12.51% 0.59 192.37 10.62
39. Apr 30, 2020 4.58% 12.68% 12.71 128.29 40.38
40. May 31, 2020 6.53% 4.53% 30.38 10.05 17.47
41. Jun 30, 2020 -0.92% 1.84% 3.74 0.23 -0.93
42. Jul 31, 2020 -2.35% 5.51% 11.38 17.24 -14.01
43. Aug 31, 2020 -1.30% 7.01% 5.39 31.91 -13.12
44. Sep 30, 2020 -5.99% -3.92% 49.11 27.89 37.00
45. Oct 31, 2020 -3.10% -2.77% 16.96 17.01 16.99
46. Nov 30, 2020 20.86% 10.75% 393.63 88.30 186.43
47. Dec 31, 2020 0.75% 3.71% 0.07 5.54 -0.63
48. Jan 31, 2021 5.64% -1.11% 21.32 6.11 -11.41
49. Feb 28, 2021 -4.91% 2.61% 35.19 1.57 -7.42
50. Mar 31, 2021 10.42% 4.24% 88.40 8.33 27.13
51. Apr 30, 2021 2.22% 5.24% 1.44 15.09 4.66
52. May 31, 2021 13.14% 0.55% 146.95 0.65 -9.81
53. Jun 30, 2021 -3.47% 2.22% 20.16 0.75 -3.88
54. Jul 31, 2021 -0.70% 2.27% 2.94 0.84 -1.57
55. Aug 31, 2021 4.89% 2.90% 15.01 2.38 5.97
56. Sep 30, 2021 -1.77% -4.76% 7.79 37.39 17.06
57. Oct 31, 2021 5.80% 6.91% 22.84 30.87 26.55
58. Nov 30, 2021 -0.25% -0.83% 1.60 4.80 2.77
59. Dec 31, 2021 15.83% 4.36% 219.42 9.02 44.49
Total (Σ): 3,860.55 1,164.17 999.28

Show all

VarianceCVS = Σ(RCVS,tRCVS)2 ÷ (59 – 1)
= 3,860.55 ÷ (59 – 1)
= 66.56

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

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


Systematic Risk (β) Estimation

Microsoft Excel
VarianceCVS 66.56
VarianceS&P 500 20.07
CovarianceCVS, S&P 500 17.23
Correlation coefficientCVS, S&P 5001 0.47
βCVS2 0.86
αCVS3 -0.15%

Calculations

1 Correlation coefficientCVS, S&P 500
= CovarianceCVS, S&P 500 ÷ (Standard deviationCVS × Standard deviationS&P 500)
= 17.23 ÷ (8.16% × 4.48%)
= 0.47

2 βCVS
= CovarianceCVS, S&P 500 ÷ VarianceS&P 500
= 17.23 ÷ 20.07
= 0.86

3 αCVS
= AverageCVS – βCVS × AverageS&P 500
= 1.02%0.86 × 1.36%
= -0.15%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 3.27%
Expected rate of return on market portfolio2 E(RM) 12.25%
Systematic risk (β) of CVS Health Corp. common stock βCVS 0.86
 
Expected rate of return on CVS Health Corp. common stock3 E(RCVS) 10.98%

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]
= 3.27% + 0.86 [12.25%3.27%]
= 10.98%