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, 2020 $37.62 3,225.52
1. Feb 29, 2020 $35.22 -6.38% 2,954.22 -8.41%
2. Mar 31, 2020 $29.15 -17.23% 2,584.59 -12.51%
3. Apr 30, 2020 $30.47 $0.52 6.31% 2,912.43 12.68%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2024 $23.16 2.75% 6,032.38 5.73%
59. Dec 31, 2024 $22.77 -1.68% 5,881.63 -2.50%
Average (R): -0.06% 1.16%
Standard deviation: 7.20% 5.28%
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, 2020 $37.62 3,225.52
1. Feb 29, 2020 $35.22 -6.38% 2,954.22 -8.41%
2. Mar 31, 2020 $29.15 -17.23% 2,584.59 -12.51%
3. Apr 30, 2020 $30.47 $0.52 6.31% 2,912.43 12.68%
4. May 31, 2020 $30.86 1.28% 3,044.31 4.53%
5. Jun 30, 2020 $30.23 -2.04% 3,100.29 1.84%
6. Jul 31, 2020 $29.58 $0.52 -0.43% 3,271.12 5.51%
7. Aug 31, 2020 $29.81 0.78% 3,500.31 7.01%
8. Sep 30, 2020 $28.51 -4.36% 3,363.00 -3.92%
9. Oct 31, 2020 $27.02 $0.52 -3.40% 3,269.96 -2.77%
10. Nov 30, 2020 $28.75 6.40% 3,621.63 10.75%
11. Dec 31, 2020 $28.76 0.03% 3,756.07 3.71%
12. Jan 31, 2021 $28.63 $0.52 1.36% 3,714.24 -1.11%
13. Feb 28, 2021 $27.89 -2.58% 3,811.15 2.61%
14. Mar 31, 2021 $30.27 8.53% 3,972.89 4.24%
15. Apr 30, 2021 $31.41 $0.52 5.48% 4,181.17 5.24%
16. May 31, 2021 $29.43 -6.30% 4,204.11 0.55%
17. Jun 30, 2021 $28.78 -2.21% 4,297.50 2.22%
18. Jul 31, 2021 $28.05 $0.52 -0.73% 4,395.26 2.27%
19. Aug 31, 2021 $27.42 -2.25% 4,522.68 2.90%
20. Sep 30, 2021 $27.01 -1.50% 4,307.54 -4.76%
21. Oct 31, 2021 $25.26 $0.52 -4.55% 4,605.38 6.91%
22. Nov 30, 2021 $22.83 -9.62% 4,567.00 -0.83%
23. Dec 31, 2021 $24.60 7.75% 4,766.18 4.36%
24. Jan 31, 2022 $25.50 $0.52 5.77% 4,515.55 -5.26%
25. Feb 28, 2022 $23.69 -7.10% 4,373.94 -3.14%
26. Mar 31, 2022 $23.63 -0.25% 4,530.41 3.58%
27. Apr 30, 2022 $18.86 $0.2775 -19.01% 4,131.93 -8.80%
28. May 31, 2022 $21.29 12.88% 4,132.15 0.01%
29. Jun 30, 2022 $20.96 -1.55% 3,785.38 -8.39%
30. Jul 31, 2022 $18.78 $0.2775 -9.08% 4,130.29 9.11%
31. Aug 31, 2022 $17.54 -6.60% 3,955.00 -4.24%
32. Sep 30, 2022 $15.34 -12.54% 3,585.62 -9.34%
33. Oct 31, 2022 $18.23 $0.2775 20.65% 3,871.98 7.99%
34. Nov 30, 2022 $19.28 5.76% 4,080.11 5.38%
35. Dec 31, 2022 $18.41 -4.51% 3,839.50 -5.90%
36. Jan 31, 2023 $20.37 $0.2775 12.15% 4,076.60 6.18%
37. Feb 28, 2023 $18.91 -7.17% 3,970.15 -2.61%
38. Mar 31, 2023 $19.25 1.80% 4,109.31 3.51%
39. Apr 30, 2023 $17.67 $0.2775 -6.77% 4,169.48 1.46%
40. May 31, 2023 $15.73 -10.98% 4,179.83 0.25%
41. Jun 30, 2023 $15.95 1.40% 4,376.86 4.71%
42. Jul 31, 2023 $14.52 $0.2775 -7.23% 4,588.96 4.85%
43. Aug 31, 2023 $14.79 1.86% 4,507.66 -1.77%
44. Sep 30, 2023 $15.02 1.56% 4,288.05 -4.87%
45. Oct 31, 2023 $15.40 $0.2775 4.38% 4,193.80 -2.20%
46. Nov 30, 2023 $16.57 7.60% 4,567.80 8.92%
47. Dec 31, 2023 $16.78 1.27% 4,769.83 4.42%
48. Jan 31, 2024 $17.69 $0.2775 7.08% 4,845.65 1.59%
49. Feb 29, 2024 $16.93 -4.30% 5,096.27 5.17%
50. Mar 31, 2024 $17.60 3.96% 5,254.35 3.10%
51. Apr 30, 2024 $16.89 $0.2775 -2.46% 5,035.69 -4.16%
52. May 31, 2024 $18.22 7.87% 5,277.51 4.80%
53. Jun 30, 2024 $19.11 4.88% 5,460.48 3.47%
54. Jul 31, 2024 $19.25 $0.2775 2.18% 5,522.30 1.13%
55. Aug 31, 2024 $19.90 3.38% 5,648.40 2.28%
56. Sep 30, 2024 $22.00 10.55% 5,762.48 2.02%
57. Oct 31, 2024 $22.54 $0.2775 3.72% 5,705.45 -0.99%
58. Nov 30, 2024 $23.16 2.75% 6,032.38 5.73%
59. Dec 31, 2024 $22.77 -1.68% 5,881.63 -2.50%
Average (R): -0.06% 1.16%
Standard deviation: 7.20% 5.28%

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 29, 2020 -6.38% -8.41% 39.96 91.63 60.51
2. Mar 31, 2020 -17.23% -12.51% 295.03 186.96 234.86
3. Apr 30, 2020 6.31% 12.68% 40.58 132.78 73.41
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2024 2.75% 5.73% 7.89 20.87 12.83
59. Dec 31, 2024 -1.68% -2.50% 2.64 13.40 5.95
Total (Σ): 3,004.65 1,618.62 1,193.80
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 29, 2020 -6.38% -8.41% 39.96 91.63 60.51
2. Mar 31, 2020 -17.23% -12.51% 295.03 186.96 234.86
3. Apr 30, 2020 6.31% 12.68% 40.58 132.78 73.41
4. May 31, 2020 1.28% 4.53% 1.79 11.34 4.51
5. Jun 30, 2020 -2.04% 1.84% 3.93 0.46 -1.34
6. Jul 31, 2020 -0.43% 5.51% 0.14 18.91 -1.62
7. Aug 31, 2020 0.78% 7.01% 0.70 34.17 4.89
8. Sep 30, 2020 -4.36% -3.92% 18.51 25.85 21.88
9. Oct 31, 2020 -3.40% -2.77% 11.18 15.43 13.14
10. Nov 30, 2020 6.40% 10.75% 41.74 92.03 61.98
11. Dec 31, 2020 0.03% 3.71% 0.01 6.51 0.24
12. Jan 31, 2021 1.36% -1.11% 2.00 5.18 -3.22
13. Feb 28, 2021 -2.58% 2.61% 6.38 2.10 -3.66
14. Mar 31, 2021 8.53% 4.24% 73.82 9.50 26.48
15. Apr 30, 2021 5.48% 5.24% 30.72 16.66 22.62
16. May 31, 2021 -6.30% 0.55% 39.01 0.38 3.83
17. Jun 30, 2021 -2.21% 2.22% 4.62 1.12 -2.28
18. Jul 31, 2021 -0.73% 2.27% 0.45 1.24 -0.75
19. Aug 31, 2021 -2.25% 2.90% 4.79 3.02 -3.80
20. Sep 30, 2021 -1.50% -4.76% 2.07 35.02 8.50
21. Oct 31, 2021 -4.55% 6.91% 20.21 33.10 -25.86
22. Nov 30, 2021 -9.62% -0.83% 91.43 3.98 19.07
23. Dec 31, 2021 7.75% 4.36% 61.01 10.24 25.00
24. Jan 31, 2022 5.77% -5.26% 34.00 41.21 -37.43
25. Feb 28, 2022 -7.10% -3.14% 49.56 18.47 30.25
26. Mar 31, 2022 -0.25% 3.58% 0.04 5.84 -0.47
27. Apr 30, 2022 -19.01% -8.80% 359.24 99.14 188.72
28. May 31, 2022 12.88% 0.01% 167.51 1.34 -14.96
29. Jun 30, 2022 -1.55% -8.39% 2.23 91.26 14.25
30. Jul 31, 2022 -9.08% 9.11% 81.34 63.21 -71.70
31. Aug 31, 2022 -6.60% -4.24% 42.83 29.22 35.38
32. Sep 30, 2022 -12.54% -9.34% 155.86 110.27 131.10
33. Oct 31, 2022 20.65% 7.99% 428.77 46.58 141.33
34. Nov 30, 2022 5.76% 5.38% 33.85 17.76 24.52
35. Dec 31, 2022 -4.51% -5.90% 19.84 49.82 31.44
36. Jan 31, 2023 12.15% 6.18% 149.13 25.14 61.23
37. Feb 28, 2023 -7.17% -2.61% 50.54 14.23 26.82
38. Mar 31, 2023 1.80% 3.51% 3.45 5.49 4.35
39. Apr 30, 2023 -6.77% 1.46% 45.00 0.09 -2.03
40. May 31, 2023 -10.98% 0.25% 119.26 0.83 9.97
41. Jun 30, 2023 1.40% 4.71% 2.12 12.62 5.18
42. Jul 31, 2023 -7.23% 4.85% 51.37 13.58 -26.41
43. Aug 31, 2023 1.86% -1.77% 3.68 8.60 -5.62
44. Sep 30, 2023 1.56% -4.87% 2.60 36.40 -9.73
45. Oct 31, 2023 4.38% -2.20% 19.68 11.28 -14.90
46. Nov 30, 2023 7.60% 8.92% 58.61 60.17 59.38
47. Dec 31, 2023 1.27% 4.42% 1.76 10.64 4.32
48. Jan 31, 2024 7.08% 1.59% 50.91 0.18 3.06
49. Feb 29, 2024 -4.30% 5.17% 17.96 16.09 -17.00
50. Mar 31, 2024 3.96% 3.10% 16.13 3.77 7.79
51. Apr 30, 2024 -2.46% -4.16% 5.76 28.33 12.77
52. May 31, 2024 7.87% 4.80% 62.93 13.26 28.88
53. Jun 30, 2024 4.88% 3.47% 24.43 5.32 11.40
54. Jul 31, 2024 2.18% 1.13% 5.03 0.00 -0.07
55. Aug 31, 2024 3.38% 2.28% 11.80 1.26 3.85
56. Sep 30, 2024 10.55% 2.02% 112.59 0.74 9.11
57. Oct 31, 2024 3.72% -0.99% 14.24 4.63 -8.12
58. Nov 30, 2024 2.75% 5.73% 7.89 20.87 12.83
59. Dec 31, 2024 -1.68% -2.50% 2.64 13.40 5.95
Total (Σ): 3,004.65 1,618.62 1,193.80

Show all

VarianceT = Σ(RT,tRT)2 ÷ (59 – 1)
= 3,004.65 ÷ (59 – 1)
= 51.80

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

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


Systematic Risk (β) Estimation

Microsoft Excel
VarianceT 51.80
VarianceS&P 500 27.91
CovarianceT, S&P 500 20.58
Correlation coefficientT, S&P 5001 0.54
βT2 0.74
αT3 -0.91%

Calculations

1 Correlation coefficientT, S&P 500
= CovarianceT, S&P 500 ÷ (Standard deviationT × Standard deviationS&P 500)
= 20.58 ÷ (7.20% × 5.28%)
= 0.54

2 βT
= CovarianceT, S&P 500 ÷ VarianceS&P 500
= 20.58 ÷ 27.91
= 0.74

3 αT
= AverageT – βT × AverageS&P 500
= -0.06%0.74 × 1.16%
= -0.91%


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) 14.89%
Systematic risk (β) of AT&T Inc. common stock βT 0.74
 
Expected rate of return on AT&T Inc. common stock3 E(RT) 12.20%

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.74 [14.89%4.64%]
= 12.20%