Stock Analysis on Net

Charter Communications Inc. (NASDAQ:CHTR)

Capital Asset Pricing Model (CAPM) 

Microsoft Excel

Rates of Return

Charter Communications Inc., monthly rates of return

Microsoft Excel
Charter Communications Inc. (CHTR) Standard & Poor’s 500 (S&P 500)
t Date PriceCHTR,t1 DividendCHTR,t1 RCHTR,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2017 $323.95 2,278.87
1. Feb 28, 2017 $323.06 -0.27% 2,363.64 3.72%
2. Mar 31, 2017 $327.32 1.32% 2,362.72 -0.04%
3. Apr 30, 2017 $345.16 5.45% 2,384.20 0.91%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 $646.28 -4.24% 4,567.00 -0.83%
59. Dec 31, 2021 $651.97 0.88% 4,766.18 4.36%
Average (R): 1.45% 1.36%
Standard deviation: 7.33% 4.48%
Charter Communications Inc. (CHTR) Standard & Poor’s 500 (S&P 500)
t Date PriceCHTR,t1 DividendCHTR,t1 RCHTR,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2017 $323.95 2,278.87
1. Feb 28, 2017 $323.06 -0.27% 2,363.64 3.72%
2. Mar 31, 2017 $327.32 1.32% 2,362.72 -0.04%
3. Apr 30, 2017 $345.16 5.45% 2,384.20 0.91%
4. May 31, 2017 $345.55 0.11% 2,411.80 1.16%
5. Jun 30, 2017 $336.85 -2.52% 2,423.41 0.48%
6. Jul 31, 2017 $391.97 16.36% 2,470.30 1.93%
7. Aug 31, 2017 $398.54 1.68% 2,471.65 0.05%
8. Sep 30, 2017 $363.42 -8.81% 2,519.36 1.93%
9. Oct 31, 2017 $334.17 -8.05% 2,575.26 2.22%
10. Nov 30, 2017 $326.21 -2.38% 2,647.58 2.81%
11. Dec 31, 2017 $335.96 2.99% 2,673.61 0.98%
12. Jan 31, 2018 $377.25 12.29% 2,823.81 5.62%
13. Feb 28, 2018 $341.93 -9.36% 2,713.83 -3.89%
14. Mar 31, 2018 $311.22 -8.98% 2,640.87 -2.69%
15. Apr 30, 2018 $271.29 -12.83% 2,648.05 0.27%
16. May 31, 2018 $261.04 -3.78% 2,705.27 2.16%
17. Jun 30, 2018 $293.21 12.32% 2,718.37 0.48%
18. Jul 31, 2018 $304.58 3.88% 2,816.29 3.60%
19. Aug 31, 2018 $310.40 1.91% 2,901.52 3.03%
20. Sep 30, 2018 $325.88 4.99% 2,913.98 0.43%
21. Oct 31, 2018 $320.37 -1.69% 2,711.74 -6.94%
22. Nov 30, 2018 $329.20 2.76% 2,760.17 1.79%
23. Dec 31, 2018 $284.97 -13.44% 2,506.85 -9.18%
24. Jan 31, 2019 $331.05 16.17% 2,704.10 7.87%
25. Feb 28, 2019 $344.91 4.19% 2,784.49 2.97%
26. Mar 31, 2019 $346.91 0.58% 2,834.40 1.79%
27. Apr 30, 2019 $371.19 7.00% 2,945.83 3.93%
28. May 31, 2019 $376.80 1.51% 2,752.06 -6.58%
29. Jun 30, 2019 $395.18 4.88% 2,941.76 6.89%
30. Jul 31, 2019 $385.38 -2.48% 2,980.38 1.31%
31. Aug 31, 2019 $409.59 6.28% 2,926.46 -1.81%
32. Sep 30, 2019 $412.12 0.62% 2,976.74 1.72%
33. Oct 31, 2019 $467.86 13.53% 3,037.56 2.04%
34. Nov 30, 2019 $470.01 0.46% 3,140.98 3.40%
35. Dec 31, 2019 $485.08 3.21% 3,230.78 2.86%
36. Jan 31, 2020 $517.46 6.68% 3,225.52 -0.16%
37. Feb 29, 2020 $493.17 -4.69% 2,954.22 -8.41%
38. Mar 31, 2020 $436.31 -11.53% 2,584.59 -12.51%
39. Apr 30, 2020 $495.23 13.50% 2,912.43 12.68%
40. May 31, 2020 $544.00 9.85% 3,044.31 4.53%
41. Jun 30, 2020 $510.04 -6.24% 3,100.29 1.84%
42. Jul 31, 2020 $580.00 13.72% 3,271.12 5.51%
43. Aug 31, 2020 $615.61 6.14% 3,500.31 7.01%
44. Sep 30, 2020 $624.34 1.42% 3,363.00 -3.92%
45. Oct 31, 2020 $603.82 -3.29% 3,269.96 -2.77%
46. Nov 30, 2020 $651.99 7.98% 3,621.63 10.75%
47. Dec 31, 2020 $661.55 1.47% 3,756.07 3.71%
48. Jan 31, 2021 $607.56 -8.16% 3,714.24 -1.11%
49. Feb 28, 2021 $613.42 0.96% 3,811.15 2.61%
50. Mar 31, 2021 $617.02 0.59% 3,972.89 4.24%
51. Apr 30, 2021 $673.45 9.15% 4,181.17 5.24%
52. May 31, 2021 $694.53 3.13% 4,204.11 0.55%
53. Jun 30, 2021 $721.45 3.88% 4,297.50 2.22%
54. Jul 31, 2021 $744.05 3.13% 4,395.26 2.27%
55. Aug 31, 2021 $816.66 9.76% 4,522.68 2.90%
56. Sep 30, 2021 $727.56 -10.91% 4,307.54 -4.76%
57. Oct 31, 2021 $674.89 -7.24% 4,605.38 6.91%
58. Nov 30, 2021 $646.28 -4.24% 4,567.00 -0.83%
59. Dec 31, 2021 $651.97 0.88% 4,766.18 4.36%
Average (R): 1.45% 1.36%
Standard deviation: 7.33% 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 CHTR during period t

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


Variance and Covariance

Charter Communications Inc., calculation of variance and covariance of returns

Microsoft Excel
t Date RCHTR,t RS&P 500,t (RCHTR,tRCHTR)2 (RS&P 500,tRS&P 500)2 (RCHTR,tRCHTR)×(RS&P 500,tRS&P 500)
1. Feb 28, 2017 -0.27% 3.72% 2.99 5.58 -4.08
2. Mar 31, 2017 1.32% -0.04% 0.02 1.95 0.19
3. Apr 30, 2017 5.45% 0.91% 15.97 0.20 -1.79
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 -4.24% -0.83% 32.41 4.80 12.48
59. Dec 31, 2021 0.88% 4.36% 0.33 9.02 -1.72
Total (Σ): 3,113.06 1,164.17 1,099.41
t Date RCHTR,t RS&P 500,t (RCHTR,tRCHTR)2 (RS&P 500,tRS&P 500)2 (RCHTR,tRCHTR)×(RS&P 500,tRS&P 500)
1. Feb 28, 2017 -0.27% 3.72% 2.99 5.58 -4.08
2. Mar 31, 2017 1.32% -0.04% 0.02 1.95 0.19
3. Apr 30, 2017 5.45% 0.91% 15.97 0.20 -1.79
4. May 31, 2017 0.11% 1.16% 1.80 0.04 0.27
5. Jun 30, 2017 -2.52% 0.48% 15.78 0.77 3.48
6. Jul 31, 2017 16.36% 1.93% 222.28 0.33 8.60
7. Aug 31, 2017 1.68% 0.05% 0.05 1.70 -0.29
8. Sep 30, 2017 -8.81% 1.93% 105.40 0.33 -5.88
9. Oct 31, 2017 -8.05% 2.22% 90.30 0.74 -8.18
10. Nov 30, 2017 -2.38% 2.81% 14.72 2.10 -5.56
11. Dec 31, 2017 2.99% 0.98% 2.36 0.14 -0.58
12. Jan 31, 2018 12.29% 5.62% 117.42 18.15 46.16
13. Feb 28, 2018 -9.36% -3.89% 117.00 27.59 56.82
14. Mar 31, 2018 -8.98% -2.69% 108.90 16.37 42.23
15. Apr 30, 2018 -12.83% 0.27% 204.04 1.18 15.51
16. May 31, 2018 -3.78% 2.16% 27.38 0.64 -4.20
17. Jun 30, 2018 12.32% 0.48% 118.15 0.76 -9.50
18. Jul 31, 2018 3.88% 3.60% 5.87 5.04 5.44
19. Aug 31, 2018 1.91% 3.03% 0.21 2.78 0.76
20. Sep 30, 2018 4.99% 0.43% 12.48 0.86 -3.28
21. Oct 31, 2018 -1.69% -6.94% 9.89 68.86 26.10
22. Nov 30, 2018 2.76% 1.79% 1.70 0.18 0.56
23. Dec 31, 2018 -13.44% -9.18% 221.71 111.00 156.87
24. Jan 31, 2019 16.17% 7.87% 216.56 42.39 95.81
25. Feb 28, 2019 4.19% 2.97% 7.47 2.61 4.41
26. Mar 31, 2019 0.58% 1.79% 0.76 0.19 -0.38
27. Apr 30, 2019 7.00% 3.93% 30.74 6.62 14.27
28. May 31, 2019 1.51% -6.58% 0.00 62.97 -0.45
29. Jun 30, 2019 4.88% 6.89% 11.72 30.64 18.95
30. Jul 31, 2019 -2.48% 1.31% 15.48 0.00 0.18
31. Aug 31, 2019 6.28% -1.81% 23.31 10.03 -15.29
32. Sep 30, 2019 0.62% 1.72% 0.70 0.13 -0.30
33. Oct 31, 2019 13.53% 2.04% 145.71 0.47 8.27
34. Nov 30, 2019 0.46% 3.40% 0.99 4.19 -2.04
35. Dec 31, 2019 3.21% 2.86% 3.07 2.25 2.63
36. Jan 31, 2020 6.68% -0.16% 27.26 2.31 -7.94
37. Feb 29, 2020 -4.69% -8.41% 37.80 95.43 60.06
38. Mar 31, 2020 -11.53% -12.51% 168.58 192.37 180.08
39. Apr 30, 2020 13.50% 12.68% 145.20 128.29 136.48
40. May 31, 2020 9.85% 4.53% 70.46 10.05 26.61
41. Jun 30, 2020 -6.24% 1.84% 59.24 0.23 -3.70
42. Jul 31, 2020 13.72% 5.51% 150.37 17.24 50.92
43. Aug 31, 2020 6.14% 7.01% 21.95 31.91 26.47
44. Sep 30, 2020 1.42% -3.92% 0.00 27.89 0.19
45. Oct 31, 2020 -3.29% -2.77% 22.48 17.01 19.55
46. Nov 30, 2020 7.98% 10.75% 42.55 88.30 61.30
47. Dec 31, 2020 1.47% 3.71% 0.00 5.54 0.03
48. Jan 31, 2021 -8.16% -1.11% 92.45 6.11 23.76
49. Feb 28, 2021 0.96% 2.61% 0.24 1.57 -0.61
50. Mar 31, 2021 0.59% 4.24% 0.75 8.33 -2.50
51. Apr 30, 2021 9.15% 5.24% 59.16 15.09 29.88
52. May 31, 2021 3.13% 0.55% 2.81 0.65 -1.36
53. Jun 30, 2021 3.88% 2.22% 5.87 0.75 2.09
54. Jul 31, 2021 3.13% 2.27% 2.82 0.84 1.54
55. Aug 31, 2021 9.76% 2.90% 68.97 2.38 12.80
56. Sep 30, 2021 -10.91% -4.76% 152.88 37.39 75.61
57. Oct 31, 2021 -7.24% 6.91% 75.58 30.87 -48.30
58. Nov 30, 2021 -4.24% -0.83% 32.41 4.80 12.48
59. Dec 31, 2021 0.88% 4.36% 0.33 9.02 -1.72
Total (Σ): 3,113.06 1,164.17 1,099.41

Show all

VarianceCHTR = Σ(RCHTR,tRCHTR)2 ÷ (59 – 1)
= 3,113.06 ÷ (59 – 1)
= 53.67

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

CovarianceCHTR, S&P 500 = Σ(RCHTR,tRCHTR)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 1,099.41 ÷ (59 – 1)
= 18.96


Systematic Risk (β) Estimation

Microsoft Excel
VarianceCHTR 53.67
VarianceS&P 500 20.07
CovarianceCHTR, S&P 500 18.96
Correlation coefficientCHTR, S&P 5001 0.58
βCHTR2 0.94
αCHTR3 0.17%

Calculations

1 Correlation coefficientCHTR, S&P 500
= CovarianceCHTR, S&P 500 ÷ (Standard deviationCHTR × Standard deviationS&P 500)
= 18.96 ÷ (7.33% × 4.48%)
= 0.58

2 βCHTR
= CovarianceCHTR, S&P 500 ÷ VarianceS&P 500
= 18.96 ÷ 20.07
= 0.94

3 αCHTR
= AverageCHTR – βCHTR × AverageS&P 500
= 1.45%0.94 × 1.36%
= 0.17%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 3.24%
Expected rate of return on market portfolio2 E(RM) 12.24%
Systematic risk (β) of Charter Communications Inc. common stock βCHTR 0.94
 
Expected rate of return on Charter Communications Inc. common stock3 E(RCHTR) 11.74%

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(RCHTR) = RF + βCHTR [E(RM) – RF]
= 3.24% + 0.94 [12.24%3.24%]
= 11.74%