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: 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: 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


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)

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

3 αMDT
= AverageMDT – βMDT × AverageS&P 500
= 0.970.74 × 0.67


Expected Rate of Return

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

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.22% + 0.74 [12.07%1.22%]
= 9.24%