Stock Analysis on Net

Medtronic PLC (NYSE:MDT)

Capital Asset Pricing Model (CAPM) 

Microsoft Excel

Rates of Return

Medtronic PLC, monthly rates of return

Microsoft Excel
Medtronic PLC (MDT) Standard & Poor’s 500 (S&P 500)
t Date PriceMDT,t1 DividendMDT,t1 RMDT,t2 PriceS&P 500,t RS&P 500,t3
May 31, 2019 $92.58 2,752.06
1. Jun 30, 2019 $97.39 5.20% 2,941.76 6.89%
2. Jul 31, 2019 $101.94 $0.54 5.23% 2,980.38 1.31%
3. Aug 31, 2019 $107.89 5.84% 2,926.46 -1.81%
. . . . . . .
. . . . . . .
. . . . . . .
69. Feb 28, 2025 $92.02 1.32% 5,954.50 -1.42%
70. Mar 31, 2025 $89.86 $0.70 -1.59% 5,611.85 -5.75%
Average (R): 0.40% 1.15%
Standard deviation: 6.45% 5.00%
Medtronic PLC (MDT) Standard & Poor’s 500 (S&P 500)
t Date PriceMDT,t1 DividendMDT,t1 RMDT,t2 PriceS&P 500,t RS&P 500,t3
May 31, 2019 $92.58 2,752.06
1. Jun 30, 2019 $97.39 5.20% 2,941.76 6.89%
2. Jul 31, 2019 $101.94 $0.54 5.23% 2,980.38 1.31%
3. Aug 31, 2019 $107.89 5.84% 2,926.46 -1.81%
4. Sep 30, 2019 $108.62 $0.54 1.18% 2,976.74 1.72%
5. Oct 31, 2019 $108.90 0.26% 3,037.56 2.04%
6. Nov 30, 2019 $111.39 2.29% 3,140.98 3.40%
7. Dec 31, 2019 $113.45 $0.54 2.33% 3,230.78 2.86%
8. Jan 31, 2020 $115.44 1.75% 3,225.52 -0.16%
9. Feb 29, 2020 $100.67 -12.79% 2,954.22 -8.41%
10. Mar 31, 2020 $90.18 $0.54 -9.88% 2,584.59 -12.51%
11. Apr 30, 2020 $97.63 8.26% 2,912.43 12.68%
12. May 31, 2020 $98.58 0.97% 3,044.31 4.53%
13. Jun 30, 2020 $91.70 $0.58 -6.39% 3,100.29 1.84%
14. Jul 31, 2020 $96.48 5.21% 3,271.12 5.51%
15. Aug 31, 2020 $107.47 11.39% 3,500.31 7.01%
16. Sep 30, 2020 $103.92 $0.58 -2.76% 3,363.00 -3.92%
17. Oct 31, 2020 $100.57 -3.22% 3,269.96 -2.77%
18. Nov 30, 2020 $113.70 13.06% 3,621.63 10.75%
19. Dec 31, 2020 $117.14 $0.58 3.54% 3,756.07 3.71%
20. Jan 31, 2021 $111.33 -4.96% 3,714.24 -1.11%
21. Feb 28, 2021 $116.97 5.07% 3,811.15 2.61%
22. Mar 31, 2021 $118.13 $0.58 1.49% 3,972.89 4.24%
23. Apr 30, 2021 $130.92 10.83% 4,181.17 5.24%
24. May 31, 2021 $126.59 -3.31% 4,204.11 0.55%
25. Jun 30, 2021 $124.13 $0.63 -1.45% 4,297.50 2.22%
26. Jul 31, 2021 $131.31 5.78% 4,395.26 2.27%
27. Aug 31, 2021 $133.48 1.65% 4,522.68 2.90%
28. Sep 30, 2021 $125.35 $0.63 -5.62% 4,307.54 -4.76%
29. Oct 31, 2021 $119.86 -4.38% 4,605.38 6.91%
30. Nov 30, 2021 $106.70 -10.98% 4,567.00 -0.83%
31. Dec 31, 2021 $103.45 $0.63 -2.46% 4,766.18 4.36%
32. Jan 31, 2022 $103.49 0.04% 4,515.55 -5.26%
33. Feb 28, 2022 $104.99 1.45% 4,373.94 -3.14%
34. Mar 31, 2022 $110.95 $0.63 6.28% 4,530.41 3.58%
35. Apr 30, 2022 $104.36 -5.94% 4,131.93 -8.80%
36. May 31, 2022 $100.15 -4.03% 4,132.15 0.01%
37. Jun 30, 2022 $89.75 $0.68 -9.71% 3,785.38 -8.39%
38. Jul 31, 2022 $92.52 3.09% 4,130.29 9.11%
39. Aug 31, 2022 $87.92 -4.97% 3,955.00 -4.24%
40. Sep 30, 2022 $80.75 $0.68 -7.38% 3,585.62 -9.34%
41. Oct 31, 2022 $87.34 8.16% 3,871.98 7.99%
42. Nov 30, 2022 $79.04 -9.50% 4,080.11 5.38%
43. Dec 31, 2022 $77.72 $0.68 -0.81% 3,839.50 -5.90%
44. Jan 31, 2023 $83.69 7.68% 4,076.60 6.18%
45. Feb 28, 2023 $82.80 -1.06% 3,970.15 -2.61%
46. Mar 31, 2023 $80.62 $0.68 -1.81% 4,109.31 3.51%
47. Apr 30, 2023 $90.95 12.81% 4,169.48 1.46%
48. May 31, 2023 $82.76 -9.00% 4,179.83 0.25%
49. Jun 30, 2023 $88.10 $0.69 7.29% 4,376.86 4.71%
50. Jul 31, 2023 $87.76 -0.39% 4,588.96 4.85%
51. Aug 31, 2023 $81.50 -7.13% 4,507.66 -1.77%
52. Sep 30, 2023 $78.36 $0.69 -3.01% 4,288.05 -4.87%
53. Oct 31, 2023 $70.56 -9.95% 4,193.80 -2.20%
54. Nov 30, 2023 $79.27 12.34% 4,567.80 8.92%
55. Dec 31, 2023 $82.38 $0.69 4.79% 4,769.83 4.42%
56. Jan 31, 2024 $87.54 6.26% 4,845.65 1.59%
57. Feb 29, 2024 $83.36 -4.77% 5,096.27 5.17%
58. Mar 31, 2024 $87.15 $0.69 5.37% 5,254.35 3.10%
59. Apr 30, 2024 $80.24 -7.93% 5,035.69 -4.16%
60. May 31, 2024 $81.37 1.41% 5,277.51 4.80%
61. Jun 30, 2024 $78.71 $0.70 -2.41% 5,460.48 3.47%
62. Jul 31, 2024 $80.32 2.05% 5,522.30 1.13%
63. Aug 31, 2024 $88.58 10.28% 5,648.40 2.28%
64. Sep 30, 2024 $90.03 $0.70 2.43% 5,762.48 2.02%
65. Oct 31, 2024 $89.25 -0.87% 5,705.45 -0.99%
66. Nov 30, 2024 $86.54 -3.04% 6,032.38 5.73%
67. Dec 31, 2024 $79.88 $0.70 -6.89% 5,881.63 -2.50%
68. Jan 31, 2025 $90.82 13.70% 6,040.53 2.70%
69. Feb 28, 2025 $92.02 1.32% 5,954.50 -1.42%
70. Mar 31, 2025 $89.86 $0.70 -1.59% 5,611.85 -5.75%
Average (R): 0.40% 1.15%
Standard deviation: 6.45% 5.00%

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 MDT during period t

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


Variance and Covariance

Medtronic PLC, calculation of variance and covariance of returns

Microsoft Excel
t Date RMDT,t RS&P 500,t (RMDT,tRMDT)2 (RS&P 500,tRS&P 500)2 (RMDT,tRMDT)×(RS&P 500,tRS&P 500)
1. Jun 30, 2019 5.20% 6.89% 23.04 33.02 27.58
2. Jul 31, 2019 5.23% 1.31% 23.34 0.03 0.80
3. Aug 31, 2019 5.84% -1.81% 29.61 8.74 -16.09
. . . . . . .
. . . . . . .
. . . . . . .
69. Feb 28, 2025 1.32% -1.42% 0.86 6.61 -2.38
70. Mar 31, 2025 -1.59% -5.75% 3.93 47.63 13.68
Total (Σ): 2,874.69 1,727.94 1,387.95
t Date RMDT,t RS&P 500,t (RMDT,tRMDT)2 (RS&P 500,tRS&P 500)2 (RMDT,tRMDT)×(RS&P 500,tRS&P 500)
1. Jun 30, 2019 5.20% 6.89% 23.04 33.02 27.58
2. Jul 31, 2019 5.23% 1.31% 23.34 0.03 0.80
3. Aug 31, 2019 5.84% -1.81% 29.61 8.74 -16.09
4. Sep 30, 2019 1.18% 1.72% 0.61 0.33 0.45
5. Oct 31, 2019 0.26% 2.04% 0.02 0.80 -0.12
6. Nov 30, 2019 2.29% 3.40% 3.58 5.10 4.27
7. Dec 31, 2019 2.33% 2.86% 3.76 2.93 3.32
8. Jan 31, 2020 1.75% -0.16% 1.85 1.72 -1.78
9. Feb 29, 2020 -12.79% -8.41% 173.97 91.36 126.07
10. Mar 31, 2020 -9.88% -12.51% 105.66 186.57 140.40
11. Apr 30, 2020 8.26% 12.68% 61.87 133.11 90.75
12. May 31, 2020 0.97% 4.53% 0.33 11.43 1.95
13. Jun 30, 2020 -6.39% 1.84% 46.05 0.48 -4.69
14. Jul 31, 2020 5.21% 5.51% 23.21 19.04 21.02
15. Aug 31, 2020 11.39% 7.01% 120.91 34.33 64.43
16. Sep 30, 2020 -2.76% -3.92% 9.98 25.70 16.01
17. Oct 31, 2020 -3.22% -2.77% 13.10 15.32 14.16
18. Nov 30, 2020 13.06% 10.75% 160.28 92.30 121.63
19. Dec 31, 2020 3.54% 3.71% 9.86 6.58 8.06
20. Jan 31, 2021 -4.96% -1.11% 28.68 5.11 12.11
21. Feb 28, 2021 5.07% 2.61% 21.82 2.14 6.83
22. Mar 31, 2021 1.49% 4.24% 1.19 9.59 3.38
23. Apr 30, 2021 10.83% 5.24% 108.82 16.77 42.72
24. May 31, 2021 -3.31% 0.55% 13.71 0.36 2.22
25. Jun 30, 2021 -1.45% 2.22% 3.39 1.15 -1.98
26. Jul 31, 2021 5.78% 2.27% 29.04 1.27 6.08
27. Aug 31, 2021 1.65% 2.90% 1.58 3.07 2.20
28. Sep 30, 2021 -5.62% -4.76% 36.17 34.86 35.51
29. Oct 31, 2021 -4.38% 6.91% 22.80 33.26 -27.54
30. Nov 30, 2021 -10.98% -0.83% 129.38 3.92 22.53
31. Dec 31, 2021 -2.46% 4.36% 8.13 10.33 -9.16
32. Jan 31, 2022 0.04% -5.26% 0.13 41.03 2.28
33. Feb 28, 2022 1.45% -3.14% 1.11 18.35 -4.52
34. Mar 31, 2022 6.28% 3.58% 34.59 5.91 14.29
35. Apr 30, 2022 -5.94% -8.80% 40.13 98.86 62.99
36. May 31, 2022 -4.03% 0.01% 19.62 1.30 5.06
37. Jun 30, 2022 -9.71% -8.39% 102.02 90.99 96.35
38. Jul 31, 2022 3.09% 9.11% 7.24 63.43 21.43
39. Aug 31, 2022 -4.97% -4.24% 28.81 29.06 28.93
40. Sep 30, 2022 -7.38% -9.34% 60.48 109.97 81.55
41. Oct 31, 2022 8.16% 7.99% 60.31 46.78 53.11
42. Nov 30, 2022 -9.50% 5.38% 97.98 17.88 -41.85
43. Dec 31, 2022 -0.81% -5.90% 1.45 49.62 8.49
44. Jan 31, 2023 7.68% 6.18% 53.09 25.28 36.64
45. Feb 28, 2023 -1.06% -2.61% 2.13 14.13 5.48
46. Mar 31, 2023 -1.81% 3.51% 4.87 5.56 -5.20
47. Apr 30, 2023 12.81% 1.46% 154.20 0.10 3.94
48. May 31, 2023 -9.00% 0.25% 88.36 0.81 8.45
49. Jun 30, 2023 7.29% 4.71% 47.48 12.72 24.58
50. Jul 31, 2023 -0.39% 4.85% 0.61 13.68 -2.89
51. Aug 31, 2023 -7.13% -1.77% 56.68 8.52 21.97
52. Sep 30, 2023 -3.01% -4.87% 11.57 36.23 20.47
53. Oct 31, 2023 -9.95% -2.20% 107.11 11.19 34.62
54. Nov 30, 2023 12.34% 8.92% 142.77 60.39 92.85
55. Dec 31, 2023 4.79% 4.42% 19.35 10.73 14.41
56. Jan 31, 2024 6.26% 1.59% 34.44 0.20 2.60
57. Feb 29, 2024 -4.77% 5.17% 26.73 16.20 -20.81
58. Mar 31, 2024 5.37% 3.10% 24.79 3.82 9.73
59. Apr 30, 2024 -7.93% -4.16% 69.29 28.18 44.19
60. May 31, 2024 1.41% 4.80% 1.03 13.36 3.70
61. Jun 30, 2024 -2.41% 3.47% 7.86 5.38 -6.50
62. Jul 31, 2024 2.05% 1.13% 2.72 0.00 -0.02
63. Aug 31, 2024 10.28% 2.28% 97.78 1.29 11.24
64. Sep 30, 2024 2.43% 2.02% 4.13 0.76 1.77
65. Oct 31, 2024 -0.87% -0.99% 1.59 4.57 2.70
66. Nov 30, 2024 -3.04% 5.73% 11.78 21.00 -15.73
67. Dec 31, 2024 -6.89% -2.50% 53.03 13.29 26.55
68. Jan 31, 2025 13.70% 2.70% 176.90 2.42 20.68
69. Feb 28, 2025 1.32% -1.42% 0.86 6.61 -2.38
70. Mar 31, 2025 -1.59% -5.75% 3.93 47.63 13.68
Total (Σ): 2,874.69 1,727.94 1,387.95

Show all

VarianceMDT = Σ(RMDT,tRMDT)2 ÷ (70 – 1)
= 2,874.69 ÷ (70 – 1)
= 41.66

VarianceS&P 500 = Σ(RS&P 500,tRS&P 500)2 ÷ (70 – 1)
= 1,727.94 ÷ (70 – 1)
= 25.04

CovarianceMDT, S&P 500 = Σ(RMDT,tRMDT)×(RS&P 500,tRS&P 500) ÷ (70 – 1)
= 1,387.95 ÷ (70 – 1)
= 20.12


Systematic Risk (β) Estimation

Microsoft Excel
VarianceMDT 41.66
VarianceS&P 500 25.04
CovarianceMDT, S&P 500 20.12
Correlation coefficientMDT, S&P 5001 0.62
βMDT2 0.80
αMDT3 -0.53%

Calculations

1 Correlation coefficientMDT, S&P 500
= CovarianceMDT, S&P 500 ÷ (Standard deviationMDT × Standard deviationS&P 500)
= 20.12 ÷ (6.45% × 5.00%)
= 0.62

2 βMDT
= CovarianceMDT, S&P 500 ÷ VarianceS&P 500
= 20.12 ÷ 25.04
= 0.80

3 αMDT
= AverageMDT – βMDT × AverageS&P 500
= 0.40%0.80 × 1.15%
= -0.53%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 4.85%
Expected rate of return on market portfolio2 E(RM) 14.71%
Systematic risk (β) of Medtronic PLC common stock βMDT 0.80
 
Expected rate of return on Medtronic PLC common stock3 E(RMDT) 12.77%

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(RMDT) = RF + βMDT [E(RM) – RF]
= 4.85% + 0.80 [14.71%4.85%]
= 12.77%