Stock Analysis on Net

AT&T Inc. (NYSE:T)

Capital Asset Pricing Model (CAPM) 

Microsoft Excel

Capital asset pricing model (CAPM) indicates what should be the expected or required rate of return on risky assets like AT&T Inc. common stock.


Rates of Return

AT&T Inc., monthly rates of return

Microsoft Excel
AT&T Inc. (T) Standard & Poor’s 500 (S&P 500)
t Date PriceT,t1 DividendT,t1 RT,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $30.06 2,704.10
1. Feb 28, 2019 $31.12 3.53% 2,784.49 2.97%
2. Mar 31, 2019 $31.36 0.77% 2,834.40 1.79%
3. Apr 30, 2019 $30.96 $0.51 0.35% 2,945.83 3.93%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 $16.57 7.60% 4,567.80 8.92%
59. Dec 31, 2023 $16.78 1.27% 4,769.83 4.42%
Average (R): -0.20% 1.11%
Standard deviation: 7.07% 5.31%
AT&T Inc. (T) Standard & Poor’s 500 (S&P 500)
t Date PriceT,t1 DividendT,t1 RT,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $30.06 2,704.10
1. Feb 28, 2019 $31.12 3.53% 2,784.49 2.97%
2. Mar 31, 2019 $31.36 0.77% 2,834.40 1.79%
3. Apr 30, 2019 $30.96 $0.51 0.35% 2,945.83 3.93%
4. May 31, 2019 $30.58 -1.23% 2,752.06 -6.58%
5. Jun 30, 2019 $33.51 9.58% 2,941.76 6.89%
6. Jul 31, 2019 $34.05 $0.51 3.13% 2,980.38 1.31%
7. Aug 31, 2019 $35.26 3.55% 2,926.46 -1.81%
8. Sep 30, 2019 $37.84 7.32% 2,976.74 1.72%
9. Oct 31, 2019 $38.49 $0.51 3.07% 3,037.56 2.04%
10. Nov 30, 2019 $37.38 -2.88% 3,140.98 3.40%
11. Dec 31, 2019 $39.08 4.55% 3,230.78 2.86%
12. Jan 31, 2020 $37.62 $0.52 -2.41% 3,225.52 -0.16%
13. Feb 29, 2020 $35.22 -6.38% 2,954.22 -8.41%
14. Mar 31, 2020 $29.15 -17.23% 2,584.59 -12.51%
15. Apr 30, 2020 $30.47 $0.52 6.31% 2,912.43 12.68%
16. May 31, 2020 $30.86 1.28% 3,044.31 4.53%
17. Jun 30, 2020 $30.23 -2.04% 3,100.29 1.84%
18. Jul 31, 2020 $29.58 $0.52 -0.43% 3,271.12 5.51%
19. Aug 31, 2020 $29.81 0.78% 3,500.31 7.01%
20. Sep 30, 2020 $28.51 -4.36% 3,363.00 -3.92%
21. Oct 31, 2020 $27.02 $0.52 -3.40% 3,269.96 -2.77%
22. Nov 30, 2020 $28.75 6.40% 3,621.63 10.75%
23. Dec 31, 2020 $28.76 0.03% 3,756.07 3.71%
24. Jan 31, 2021 $28.63 $0.52 1.36% 3,714.24 -1.11%
25. Feb 28, 2021 $27.89 -2.58% 3,811.15 2.61%
26. Mar 31, 2021 $30.27 8.53% 3,972.89 4.24%
27. Apr 30, 2021 $31.41 $0.52 5.48% 4,181.17 5.24%
28. May 31, 2021 $29.43 -6.30% 4,204.11 0.55%
29. Jun 30, 2021 $28.78 -2.21% 4,297.50 2.22%
30. Jul 31, 2021 $28.05 $0.52 -0.73% 4,395.26 2.27%
31. Aug 31, 2021 $27.42 -2.25% 4,522.68 2.90%
32. Sep 30, 2021 $27.01 -1.50% 4,307.54 -4.76%
33. Oct 31, 2021 $25.26 $0.52 -4.55% 4,605.38 6.91%
34. Nov 30, 2021 $22.83 -9.62% 4,567.00 -0.83%
35. Dec 31, 2021 $24.60 7.75% 4,766.18 4.36%
36. Jan 31, 2022 $25.50 $0.52 5.77% 4,515.55 -5.26%
37. Feb 28, 2022 $23.69 -7.10% 4,373.94 -3.14%
38. Mar 31, 2022 $23.63 -0.25% 4,530.41 3.58%
39. Apr 30, 2022 $18.86 $0.2775 -19.01% 4,131.93 -8.80%
40. May 31, 2022 $21.29 12.88% 4,132.15 0.01%
41. Jun 30, 2022 $20.96 -1.55% 3,785.38 -8.39%
42. Jul 31, 2022 $18.78 $0.2775 -9.08% 4,130.29 9.11%
43. Aug 31, 2022 $17.54 -6.60% 3,955.00 -4.24%
44. Sep 30, 2022 $15.34 -12.54% 3,585.62 -9.34%
45. Oct 31, 2022 $18.23 $0.2775 20.65% 3,871.98 7.99%
46. Nov 30, 2022 $19.28 5.76% 4,080.11 5.38%
47. Dec 31, 2022 $18.41 -4.51% 3,839.50 -5.90%
48. Jan 31, 2023 $20.37 $0.2775 12.15% 4,076.60 6.18%
49. Feb 28, 2023 $18.91 -7.17% 3,970.15 -2.61%
50. Mar 31, 2023 $19.25 1.80% 4,109.31 3.51%
51. Apr 30, 2023 $17.67 $0.2775 -6.77% 4,169.48 1.46%
52. May 31, 2023 $15.73 -10.98% 4,179.83 0.25%
53. Jun 30, 2023 $15.95 1.40% 4,376.86 4.71%
54. Jul 31, 2023 $14.52 $0.2775 -7.23% 4,588.96 4.85%
55. Aug 31, 2023 $14.79 1.86% 4,507.66 -1.77%
56. Sep 30, 2023 $15.02 1.56% 4,288.05 -4.87%
57. Oct 31, 2023 $15.40 $0.2775 4.38% 4,193.80 -2.20%
58. Nov 30, 2023 $16.57 7.60% 4,567.80 8.92%
59. Dec 31, 2023 $16.78 1.27% 4,769.83 4.42%
Average (R): -0.20% 1.11%
Standard deviation: 7.07% 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 T during period t

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


Variance and Covariance

AT&T Inc., calculation of variance and covariance of returns

Microsoft Excel
t Date RT,t RS&P 500,t (RT,tRT)2 (RS&P 500,tRS&P 500)2 (RT,tRT)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 3.53% 2.97% 13.92 3.49 6.96
2. Mar 31, 2019 0.77% 1.79% 0.95 0.47 0.67
3. Apr 30, 2019 0.35% 3.93% 0.31 7.98 1.57
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 7.60% 8.92% 60.86 61.03 60.95
59. Dec 31, 2023 1.27% 4.42% 2.17 11.00 4.88
Total (Σ): 2,901.17 1,634.30 1,196.51
t Date RT,t RS&P 500,t (RT,tRT)2 (RS&P 500,tRS&P 500)2 (RT,tRT)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 3.53% 2.97% 13.92 3.49 6.96
2. Mar 31, 2019 0.77% 1.79% 0.95 0.47 0.67
3. Apr 30, 2019 0.35% 3.93% 0.31 7.98 1.57
4. May 31, 2019 -1.23% -6.58% 1.05 59.04 7.86
5. Jun 30, 2019 9.58% 6.89% 95.76 33.49 56.63
6. Jul 31, 2019 3.13% 1.31% 11.14 0.04 0.69
7. Aug 31, 2019 3.55% -1.81% 14.12 8.50 -10.95
8. Sep 30, 2019 7.32% 1.72% 56.57 0.37 4.60
9. Oct 31, 2019 3.07% 2.04% 10.69 0.88 3.06
10. Nov 30, 2019 -2.88% 3.40% 7.18 5.28 -6.16
11. Dec 31, 2019 4.55% 2.86% 22.58 3.07 8.33
12. Jan 31, 2020 -2.41% -0.16% 4.85 1.61 2.79
13. Feb 29, 2020 -6.38% -8.41% 38.14 90.57 58.77
14. Mar 31, 2020 -17.23% -12.51% 290.04 185.44 231.92
15. Apr 30, 2020 6.31% 12.68% 42.46 134.06 75.45
16. May 31, 2020 1.28% 4.53% 2.20 11.71 5.08
17. Jun 30, 2020 -2.04% 1.84% 3.38 0.54 -1.35
18. Jul 31, 2020 -0.43% 5.51% 0.05 19.40 -1.00
19. Aug 31, 2020 0.78% 7.01% 0.96 34.82 5.79
20. Sep 30, 2020 -4.36% -3.92% 17.28 25.29 20.90
21. Oct 31, 2020 -3.40% -2.77% 10.23 15.00 12.38
22. Nov 30, 2020 6.40% 10.75% 43.65 93.10 63.75
23. Dec 31, 2020 0.03% 3.71% 0.06 6.79 0.62
24. Jan 31, 2021 1.36% -1.11% 2.43 4.93 -3.46
25. Feb 28, 2021 -2.58% 2.61% 5.67 2.26 -3.58
26. Mar 31, 2021 8.53% 4.24% 76.35 9.85 27.42
27. Apr 30, 2021 5.48% 5.24% 32.35 17.11 23.53
28. May 31, 2021 -6.30% 0.55% 37.21 0.31 3.40
29. Jun 30, 2021 -2.21% 2.22% 4.02 1.24 -2.24
30. Jul 31, 2021 -0.73% 2.27% 0.28 1.37 -0.61
31. Aug 31, 2021 -2.25% 2.90% 4.17 3.22 -3.66
32. Sep 30, 2021 -1.50% -4.76% 1.67 34.37 7.57
33. Oct 31, 2021 -4.55% 6.91% 18.92 33.74 -25.27
34. Nov 30, 2021 -9.62% -0.83% 88.66 3.76 18.26
35. Dec 31, 2021 7.75% 4.36% 63.31 10.60 25.90
36. Jan 31, 2022 5.77% -5.26% 35.72 40.51 -38.04
37. Feb 28, 2022 -7.10% -3.14% 47.53 17.99 29.24
38. Mar 31, 2022 -0.25% 3.58% 0.00 6.11 -0.12
39. Apr 30, 2022 -19.01% -8.80% 353.73 98.04 186.23
40. May 31, 2022 12.88% 0.01% 171.31 1.21 -14.40
41. Jun 30, 2022 -1.55% -8.39% 1.81 90.21 12.78
42. Jul 31, 2022 -9.08% 9.11% 78.73 64.09 -71.03
43. Aug 31, 2022 -6.60% -4.24% 40.94 28.62 34.23
44. Sep 30, 2022 -12.54% -9.34% 152.24 109.11 128.88
45. Oct 31, 2022 20.65% 7.99% 434.84 47.34 143.48
46. Nov 30, 2022 5.76% 5.38% 35.57 18.23 25.46
47. Dec 31, 2022 -4.51% -5.90% 18.56 49.04 30.17
48. Jan 31, 2023 12.15% 6.18% 152.72 25.70 62.65
49. Feb 28, 2023 -7.17% -2.61% 48.49 13.82 25.88
50. Mar 31, 2023 1.80% 3.51% 4.01 5.76 4.80
51. Apr 30, 2023 -6.77% 1.46% 43.06 0.13 -2.35
52. May 31, 2023 -10.98% 0.25% 116.10 0.74 9.24
53. Jun 30, 2023 1.40% 4.71% 2.57 13.02 5.78
54. Jul 31, 2023 -7.23% 4.85% 49.30 13.99 -26.26
55. Aug 31, 2023 1.86% -1.77% 4.26 8.28 -5.94
56. Sep 30, 2023 1.56% -4.87% 3.09 35.73 -10.52
57. Oct 31, 2023 4.38% -2.20% 20.99 10.92 -15.14
58. Nov 30, 2023 7.60% 8.92% 60.86 61.03 60.95
59. Dec 31, 2023 1.27% 4.42% 2.17 11.00 4.88
Total (Σ): 2,901.17 1,634.30 1,196.51

Show all

VarianceT = Σ(RT,tRT)2 ÷ (59 – 1)
= 2,901.17 ÷ (59 – 1)
= 50.02

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

CovarianceT, S&P 500 = Σ(RT,tRT)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 1,196.51 ÷ (59 – 1)
= 20.63


Systematic Risk (β) Estimation

Microsoft Excel
VarianceT 50.02
VarianceS&P 500 28.18
CovarianceT, S&P 500 20.63
Correlation coefficientT, S&P 5001 0.55
βT2 0.73
αT3 -1.01%

Calculations

1 Correlation coefficientT, S&P 500
= CovarianceT, S&P 500 ÷ (Standard deviationT × Standard deviationS&P 500)
= 20.63 ÷ (7.07% × 5.31%)
= 0.55

2 βT
= CovarianceT, S&P 500 ÷ VarianceS&P 500
= 20.63 ÷ 28.18
= 0.73

3 αT
= AverageT – βT × AverageS&P 500
= -0.20%0.73 × 1.11%
= -1.01%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 4.64%
Expected rate of return on market portfolio2 E(RM) 13.82%
Systematic risk (β) of AT&T Inc. common stock βT 0.73
 
Expected rate of return on AT&T Inc. common stock3 E(RT) 11.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(RT) = RF + βT [E(RM) – RF]
= 4.64% + 0.73 [13.82%4.64%]
= 11.36%