Stock Analysis on Net
Stock Analysis on Net
Microsoft Excel LibreOffice Calc

Medtronic PLC (NYSE:MDT)

Capital Asset Pricing Model (CAPM)

Intermediate level

Rates of Return

Medtronic PLC, monthly rates of return

Microsoft Excel LibreOffice Calc
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, 2014 $61.03 1,923.57
1. Jun 30, 2014 $63.76 4.47% 1,960.23 1.91%
2. Jul 31, 2014 $61.74 $0.305 -2.69% 1,930.67 -1.51%
3. Aug 31, 2014 $63.85 3.42% 2,003.37 3.77%
. . . . . . .
. . . . . . .
. . . . . . .
70. Mar 31, 2020 $90.18 $0.54 -9.88% 2,584.59 -12.51%
71. Apr 30, 2020 $97.63 8.26% 2,912.43 12.68%
Average (R): 0.97% 0.67%
Standard deviation: 5.19% 4.03%
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, 2014 $61.03 1,923.57
1. Jun 30, 2014 $63.76 4.47% 1,960.23 1.91%
2. Jul 31, 2014 $61.74 $0.305 -2.69% 1,930.67 -1.51%
3. Aug 31, 2014 $63.85 3.42% 2,003.37 3.77%
4. Sep 30, 2014 $61.95 -2.98% 1,972.29 -1.55%
5. Oct 31, 2014 $68.16 $0.305 10.52% 2,018.05 2.32%
6. Nov 30, 2014 $73.87 8.38% 2,067.56 2.45%
7. Dec 31, 2014 $72.20 $0.305 -1.85% 2,058.90 -0.42%
8. Jan 31, 2015 $71.40 -1.11% 1,994.99 -3.10%
9. Feb 28, 2015 $77.59 8.67% 2,104.50 5.49%
10. Mar 31, 2015 $77.99 $0.305 0.91% 2,067.89 -1.74%
11. Apr 30, 2015 $74.45 -4.54% 2,085.51 0.85%
12. May 31, 2015 $76.32 2.51% 2,107.39 1.05%
13. Jun 30, 2015 $74.10 $0.38 -2.41% 2,063.11 -2.10%
14. Jul 31, 2015 $78.39 5.79% 2,103.84 1.97%
15. Aug 31, 2015 $72.29 -7.78% 1,972.18 -6.26%
16. Sep 30, 2015 $66.94 $0.38 -6.88% 1,920.03 -2.64%
17. Oct 31, 2015 $73.92 10.43% 2,079.36 8.30%
18. Nov 30, 2015 $75.34 1.92% 2,080.41 0.05%
19. Dec 31, 2015 $76.92 $0.38 2.60% 2,043.94 -1.75%
20. Jan 31, 2016 $75.92 -1.30% 1,940.24 -5.07%
21. Feb 29, 2016 $77.39 1.94% 1,932.23 -0.41%
22. Mar 31, 2016 $75.00 $0.38 -2.60% 2,059.74 6.60%
23. Apr 30, 2016 $79.15 5.53% 2,065.30 0.27%
24. May 31, 2016 $80.48 1.68% 2,096.95 1.53%
25. Jun 30, 2016 $86.77 7.82% 2,098.86 0.09%
26. Jul 31, 2016 $87.63 $0.43 1.49% 2,173.60 3.56%
27. Aug 31, 2016 $87.03 -0.68% 2,170.95 -0.12%
28. Sep 30, 2016 $86.40 $0.43 -0.23% 2,168.27 -0.12%
29. Oct 31, 2016 $82.02 -5.07% 2,126.15 -1.94%
30. Nov 30, 2016 $73.01 -10.99% 2,198.81 3.42%
31. Dec 31, 2016 $71.23 $0.43 -1.85% 2,238.83 1.82%
32. Jan 31, 2017 $76.02 6.72% 2,278.87 1.79%
33. Feb 28, 2017 $80.91 6.43% 2,363.64 3.72%
34. Mar 31, 2017 $80.56 $0.43 0.10% 2,362.72 -0.04%
35. Apr 30, 2017 $83.09 3.14% 2,384.20 0.91%
36. May 31, 2017 $84.28 1.43% 2,411.80 1.16%
37. Jun 30, 2017 $88.75 5.30% 2,423.41 0.48%
38. Jul 31, 2017 $83.97 $0.46 -4.87% 2,470.30 1.93%
39. Aug 31, 2017 $80.62 -3.99% 2,471.65 0.05%
40. Sep 30, 2017 $77.77 $0.46 -2.96% 2,519.36 1.93%
41. Oct 31, 2017 $80.52 3.54% 2,575.26 2.22%
42. Nov 30, 2017 $82.13 2.00% 2,647.58 2.81%
43. Dec 31, 2017 $80.75 $0.46 -1.12% 2,673.61 0.98%
44. Jan 31, 2018 $85.89 6.37% 2,823.81 5.62%
45. Feb 28, 2018 $79.89 -6.99% 2,713.83 -3.89%
46. Mar 31, 2018 $80.22 $0.46 0.99% 2,640.87 -2.69%
47. Apr 30, 2018 $80.13 -0.11% 2,648.05 0.27%
48. May 31, 2018 $86.32 7.72% 2,705.27 2.16%
49. Jun 30, 2018 $85.61 -0.82% 2,718.37 0.48%
50. Jul 31, 2018 $90.23 $0.50 5.98% 2,816.29 3.60%
51. Aug 31, 2018 $96.41 6.85% 2,901.52 3.03%
52. Sep 30, 2018 $98.37 $0.50 2.55% 2,913.98 0.43%
53. Oct 31, 2018 $89.82 -8.69% 2,711.74 -6.94%
54. Nov 30, 2018 $97.53 8.58% 2,760.17 1.79%
55. Dec 31, 2018 $90.96 $0.50 -6.22% 2,506.85 -9.18%
56. Jan 31, 2019 $88.39 -2.83% 2,704.10 7.87%
57. Feb 28, 2019 $90.50 2.39% 2,784.49 2.97%
58. Mar 31, 2019 $91.08 $0.50 1.19% 2,834.40 1.79%
59. Apr 30, 2019 $88.81 -2.49% 2,945.83 3.93%
60. May 31, 2019 $92.58 4.25% 2,752.06 -6.58%
61. Jun 30, 2019 $97.39 5.20% 2,941.76 6.89%
62. Jul 31, 2019 $101.94 $0.54 5.23% 2,980.38 1.31%
63. Aug 31, 2019 $107.89 5.84% 2,926.46 -1.81%
64. Sep 30, 2019 $108.62 $0.54 1.18% 2,976.74 1.72%
65. Oct 31, 2019 $108.90 0.26% 3,037.56 2.04%
66. Nov 30, 2019 $111.39 2.29% 3,140.98 3.40%
67. Dec 31, 2019 $113.45 $0.54 2.33% 3,230.78 2.86%
68. Jan 31, 2020 $115.44 1.75% 3,225.52 -0.16%
69. Feb 29, 2020 $100.67 -12.79% 2,954.22 -8.41%
70. Mar 31, 2020 $90.18 $0.54 -9.88% 2,584.59 -12.51%
71. Apr 30, 2020 $97.63 8.26% 2,912.43 12.68%
Average (R): 0.97% 0.67%
Standard deviation: 5.19% 4.03%

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 LibreOffice Calc
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, 2014 4.47% 1.91% 12.24 1.54 4.34
2. Jul 31, 2014 -2.69% -1.51% 13.43 4.73 7.97
3. Aug 31, 2014 3.42% 3.77% 5.97 9.60 7.57
. . . . . . .
. . . . . . .
. . . . . . .
70. Mar 31, 2020 -9.88% -12.51% 117.91 173.67 143.10
71. Apr 30, 2020 8.26% 12.68% 53.09 144.43 87.57
Total (Σ): 1,887.36 1,134.74 838.01
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, 2014 4.47% 1.91% 12.24 1.54 4.34
2. Jul 31, 2014 -2.69% -1.51% 13.43 4.73 7.97
3. Aug 31, 2014 3.42% 3.77% 5.97 9.60 7.57
4. Sep 30, 2014 -2.98% -1.55% 15.61 4.92 8.76
5. Oct 31, 2014 10.52% 2.32% 91.04 2.73 15.78
6. Nov 30, 2014 8.38% 2.45% 54.80 3.19 13.23
7. Dec 31, 2014 -1.85% -0.42% 7.97 1.18 3.06
8. Jan 31, 2015 -1.11% -3.10% 4.34 14.22 7.85
9. Feb 28, 2015 8.67% 5.49% 59.21 23.26 37.11
10. Mar 31, 2015 0.91% -1.74% 0.00 5.79 0.16
11. Apr 30, 2015 -4.54% 0.85% 30.40 0.03 -1.02
12. May 31, 2015 2.51% 1.05% 2.36 0.15 0.59
13. Jun 30, 2015 -2.41% -2.10% 11.46 7.66 9.37
14. Jul 31, 2015 5.79% 1.97% 23.18 1.71 6.30
15. Aug 31, 2015 -7.78% -6.26% 76.68 47.95 60.64
16. Sep 30, 2015 -6.88% -2.64% 61.62 10.96 25.99
17. Oct 31, 2015 10.43% 8.30% 89.35 58.24 72.14
18. Nov 30, 2015 1.92% 0.05% 0.90 0.38 -0.58
19. Dec 31, 2015 2.60% -1.75% 2.65 5.85 -3.94
20. Jan 31, 2016 -1.30% -5.07% 5.18 32.95 13.06
21. Feb 29, 2016 1.94% -0.41% 0.92 1.17 -1.04
22. Mar 31, 2016 -2.60% 6.60% 12.76 35.19 -21.19
23. Apr 30, 2016 5.53% 0.27% 20.78 0.16 -1.81
24. May 31, 2016 1.68% 1.53% 0.50 0.75 0.61
25. Jun 30, 2016 7.82% 0.09% 46.80 0.33 -3.94
26. Jul 31, 2016 1.49% 3.56% 0.26 8.38 1.48
27. Aug 31, 2016 -0.68% -0.12% 2.75 0.62 1.31
28. Sep 30, 2016 -0.23% -0.12% 1.45 0.62 0.95
29. Oct 31, 2016 -5.07% -1.94% 36.53 6.81 15.77
30. Nov 30, 2016 -10.99% 3.42% 143.04 7.57 -32.90
31. Dec 31, 2016 -1.85% 1.82% 7.97 1.33 -3.26
32. Jan 31, 2017 6.72% 1.79% 33.06 1.26 6.45
33. Feb 28, 2017 6.43% 3.72% 29.79 9.32 16.66
34. Mar 31, 2017 0.10% -0.04% 0.77 0.50 0.62
35. Apr 30, 2017 3.14% 0.91% 4.69 0.06 0.53
36. May 31, 2017 1.43% 1.16% 0.21 0.24 0.22
37. Jun 30, 2017 5.30% 0.48% 18.74 0.03 -0.80
38. Jul 31, 2017 -4.87% 1.93% 34.13 1.61 -7.41
39. Aug 31, 2017 -3.99% 0.05% 24.65 0.37 3.04
40. Sep 30, 2017 -2.96% 1.93% 15.52 1.60 -4.98
41. Oct 31, 2017 3.54% 2.22% 6.56 2.41 3.98
42. Nov 30, 2017 2.00% 2.81% 1.05 4.59 2.19
43. Dec 31, 2017 -1.12% 0.98% 4.39 0.10 -0.66
44. Jan 31, 2018 6.37% 5.62% 29.06 24.52 26.69
45. Feb 28, 2018 -6.99% -3.89% 63.37 20.81 36.31
46. Mar 31, 2018 0.99% -2.69% 0.00 11.26 -0.05
47. Apr 30, 2018 -0.11% 0.27% 1.18 0.16 0.43
48. May 31, 2018 7.72% 2.16% 45.56 2.23 10.09
49. Jun 30, 2018 -0.82% 0.48% 3.23 0.03 0.33
50. Jul 31, 2018 5.98% 3.60% 25.06 8.62 14.69
51. Aug 31, 2018 6.85% 3.03% 34.51 5.57 13.86
52. Sep 30, 2018 2.55% 0.43% 2.49 0.06 -0.37
53. Oct 31, 2018 -8.69% -6.94% 93.44 57.87 73.53
54. Nov 30, 2018 8.58% 1.79% 57.90 1.25 8.52
55. Dec 31, 2018 -6.22% -9.18% 51.82 96.91 70.87
56. Jan 31, 2019 -2.83% 7.87% 14.44 51.87 -27.37
57. Feb 28, 2019 2.39% 2.97% 1.99 5.32 3.26
58. Mar 31, 2019 1.19% 1.79% 0.05 1.27 0.25
59. Apr 30, 2019 -2.49% 3.93% 12.02 10.66 -11.32
60. May 31, 2019 4.25% -6.58% 10.69 52.48 -23.69
61. Jun 30, 2019 5.20% 6.89% 17.81 38.77 26.28
62. Jul 31, 2019 5.23% 1.31% 18.08 0.42 2.75
63. Aug 31, 2019 5.84% -1.81% 23.64 6.13 -12.04
64. Sep 30, 2019 1.18% 1.72% 0.04 1.11 0.21
65. Oct 31, 2019 0.26% 2.04% 0.51 1.89 -0.99
66. Nov 30, 2019 2.29% 3.40% 1.72 7.50 3.59
67. Dec 31, 2019 2.33% 2.86% 1.85 4.81 2.98
68. Jan 31, 2020 1.75% -0.16% 0.61 0.69 -0.65
69. Feb 29, 2020 -12.79% -8.41% 189.60 82.40 124.99
70. Mar 31, 2020 -9.88% -12.51% 117.91 173.67 143.10
71. Apr 30, 2020 8.26% 12.68% 53.09 144.43 87.57
Total (Σ): 1,887.36 1,134.74 838.01

Show all

VarianceMDT = Σ(RMDT,tRMDT)2 ÷ (71 – 1)
= 1,887.36 ÷ (71 – 1)
= 26.96

VarianceS&P 500 = Σ(RS&P 500,tRS&P 500)2 ÷ (71 – 1)
= 1,134.74 ÷ (71 – 1)
= 16.21

CovarianceMDT, S&P 500 = Σ(RMDT,tRMDT)×(RS&P 500,tRS&P 500) ÷ (71 – 1)
= 838.01 ÷ (71 – 1)
= 11.97


Systematic Risk (β) Estimation

Microsoft Excel LibreOffice Calc
VarianceMDT 26.96
VarianceS&P 500 16.21
CovarianceMDT, S&P 500 11.97
Correlation coefficientMDT, S&P 5001 0.57
βMDT2 0.74
αMDT3 0.48%

Calculations

1 Correlation coefficientMDT, S&P 500
= CovarianceMDT, S&P 500 ÷ (Standard deviationMDT × Standard deviationS&P 500)
= 11.97 ÷ (5.19% × 4.03%)
= 0.57

2 βMDT
= CovarianceMDT, S&P 500 ÷ VarianceS&P 500
= 11.97 ÷ 16.21
= 0.74

3 αMDT
= AverageMDT – βMDT × AverageS&P 500
= 0.97%0.74 × 0.67%
= 0.48%


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 1.66%
Expected rate of return on market portfolio2 E(RM) 12.36%
Systematic risk (β) of Medtronic PLC’s common stock βMDT 0.74
 
Expected rate of return on Medtronic PLC’s common stock3 E(RMDT) 9.56%

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]
= 1.66% + 0.74 [12.36%1.66%]
= 9.56%