Stock Analysis on Net

Medtronic PLC (NYSE:MDT)

資本資産価格設定モデル (CAPM) 

Microsoft Excel

資本資産価格モデル(CAPM)は、 Medtronic普通株式などのリスク資産の期待収益率または必要な収益率を示します。


収益率

Medtronic PLC、月間収益率

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

すべて表示

1 普通株式1株当たり米ドルのデータで、分割と株式配当を調整したものです。

2 期間 t中のMDTの普通株式の収益率。

3 期間 tにおけるS&P 500(市場ポートフォリオの代理)の収益率。


分散と共分散

Medtronic PLC、分散の計算、および戻り値の共分散

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

すべて表示

分散MDT = Σ(RMDT,tRMDT)2 ÷ (70 – 1)
= 2,874.69 ÷ (70 – 1)
= 41.66

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

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


システマティックなリスク推定 (β)

Microsoft Excel
分散MDT 41.66
分散S&P 500 25.04
共 分散MDT, S&P 500 20.12
相関係数MDT, S&P 5001 0.62
βMDT2 0.80
αMDT3 -0.53%

計算

1 相関係数MDT, S&P 500
= 共 分散MDT, S&P 500 ÷ (標準偏差MDT × 標準偏差S&P 500)
= 20.12 ÷ (6.45% × 5.00%)
= 0.62

2 βMDT
= 共 分散MDT, S&P 500 ÷ 分散S&P 500
= 20.12 ÷ 25.04
= 0.80

3 αMDT
= 平均MDT – βMDT × 平均S&P 500
= 0.40%0.80 × 1.15%
= -0.53%


期待収益率

Microsoft Excel
仮定
LT国債総合の収益率1 RF 4.91%
市場ポートフォリオの期待収益率2 E(RM) 14.72%
普通株式 Medtronic システマティックリスク βMDT 0.80
 
メドトロニックの普通株式の期待収益率3 E(RMDT) 12.79%

1 10年以内に期限が到来しない、または償還不能となるすべての発行済み固定利回り米国債の入札利回りの加重平均(リスクフリー・リターン・プロキシ)。

2 詳しく見る »

3 E(RMDT) = RF + βMDT [E(RM) – RF]
= 4.91% + 0.80 [14.72%4.91%]
= 12.79%