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

Microsoft Corp. (NASDAQ:MSFT)

Capital Asset Pricing Model (CAPM)

Intermediate level

Rates of Return

Microsoft Corp., monthly rates of return

Microsoft Excel LibreOffice Calc
Microsoft Corp. (MSFT) Standard & Poor’s 500 (S&P 500)
t Date PriceMSFT,t1 DividendMSFT,t1 RMSFT,t2 PriceS&P 500,t RS&P 500,t3
Jul 31, 2014 43.16 1,930.67
1. Aug 31, 2014 45.43 0.28 5.91% 2,003.37 3.77%
2. Sep 30, 2014 46.36 2.05% 1,972.29 -1.55%
3. Oct 31, 2014 46.95 1.27% 2,018.05 2.32%
. . . . . . .
. . . . . . .
. . . . . . .
70. May 31, 2020 183.25 0.51 2.54% 3,044.31 4.53%
71. Jun 30, 2020 203.51 11.06% 3,100.29 1.84%
Average: 2.57% 0.75%
Standard deviation: 6.12% 4.04%
Microsoft Corp. (MSFT) Standard & Poor’s 500 (S&P 500)
t Date PriceMSFT,t1 DividendMSFT,t1 RMSFT,t2 PriceS&P 500,t RS&P 500,t3
Jul 31, 2014 43.16 1,930.67
1. Aug 31, 2014 45.43 0.28 5.91% 2,003.37 3.77%
2. Sep 30, 2014 46.36 2.05% 1,972.29 -1.55%
3. Oct 31, 2014 46.95 1.27% 2,018.05 2.32%
4. Nov 30, 2014 47.81 0.31 2.49% 2,067.56 2.45%
5. Dec 31, 2014 46.45 -2.84% 2,058.90 -0.42%
6. Jan 31, 2015 40.40 -13.02% 1,994.99 -3.10%
7. Feb 28, 2015 43.85 0.31 9.31% 2,104.50 5.49%
8. Mar 31, 2015 40.66 -7.27% 2,067.89 -1.74%
9. Apr 30, 2015 48.64 19.63% 2,085.51 0.85%
10. May 31, 2015 46.86 0.31 -3.02% 2,107.39 1.05%
11. Jun 30, 2015 44.15 -5.78% 2,063.11 -2.10%
12. Jul 31, 2015 46.70 5.78% 2,103.84 1.97%
13. Aug 31, 2015 43.52 0.31 -6.15% 1,972.18 -6.26%
14. Sep 30, 2015 44.26 1.70% 1,920.03 -2.64%
15. Oct 31, 2015 52.64 18.93% 2,079.36 8.30%
16. Nov 30, 2015 54.35 0.36 3.93% 2,080.41 0.05%
17. Dec 31, 2015 55.48 2.08% 2,043.94 -1.75%
18. Jan 31, 2016 55.09 -0.70% 1,940.24 -5.07%
19. Feb 29, 2016 50.88 0.36 -6.99% 1,932.23 -0.41%
20. Mar 31, 2016 55.23 8.55% 2,059.74 6.60%
21. Apr 30, 2016 49.87 -9.70% 2,065.30 0.27%
22. May 31, 2016 53.00 0.36 7.00% 2,096.95 1.53%
23. Jun 30, 2016 51.17 -3.45% 2,098.86 0.09%
24. Jul 31, 2016 56.68 10.77% 2,173.60 3.56%
25. Aug 31, 2016 57.46 0.36 2.01% 2,170.95 -0.12%
26. Sep 30, 2016 57.60 0.24% 2,168.27 -0.12%
27. Oct 31, 2016 59.92 4.03% 2,126.15 -1.94%
28. Nov 30, 2016 60.26 0.39 1.22% 2,198.81 3.42%
29. Dec 31, 2016 62.14 3.12% 2,238.83 1.82%
30. Jan 31, 2017 64.65 4.04% 2,278.87 1.79%
31. Feb 28, 2017 63.98 0.39 -0.43% 2,363.64 3.72%
32. Mar 31, 2017 65.86 2.94% 2,362.72 -0.04%
33. Apr 30, 2017 68.46 3.95% 2,384.20 0.91%
34. May 31, 2017 69.84 0.39 2.59% 2,411.80 1.16%
35. Jun 30, 2017 68.93 -1.30% 2,423.41 0.48%
36. Jul 31, 2017 72.70 5.47% 2,470.30 1.93%
37. Aug 31, 2017 74.77 0.39 3.38% 2,471.65 0.05%
38. Sep 30, 2017 74.49 -0.37% 2,519.36 1.93%
39. Oct 31, 2017 83.18 11.67% 2,575.26 2.22%
40. Nov 30, 2017 84.17 0.42 1.70% 2,647.58 2.81%
41. Dec 31, 2017 85.54 1.63% 2,673.61 0.98%
42. Jan 31, 2018 95.01 11.07% 2,823.81 5.62%
43. Feb 28, 2018 93.77 0.42 -0.86% 2,713.83 -3.89%
44. Mar 31, 2018 91.27 -2.67% 2,640.87 -2.69%
45. Apr 30, 2018 93.52 2.47% 2,648.05 0.27%
46. May 31, 2018 98.84 0.42 6.14% 2,705.27 2.16%
47. Jun 30, 2018 98.61 -0.23% 2,718.37 0.48%
48. Jul 31, 2018 106.08 7.58% 2,816.29 3.60%
49. Aug 31, 2018 112.33 0.42 6.29% 2,901.52 3.03%
50. Sep 30, 2018 114.37 1.82% 2,913.98 0.43%
51. Oct 31, 2018 106.81 -6.61% 2,711.74 -6.94%
52. Nov 30, 2018 110.89 0.46 4.25% 2,760.17 1.79%
53. Dec 31, 2018 101.57 -8.40% 2,506.85 -9.18%
54. Jan 31, 2019 104.43 2.82% 2,704.10 7.87%
55. Feb 28, 2019 112.03 0.46 7.72% 2,784.49 2.97%
56. Mar 31, 2019 117.94 5.28% 2,834.40 1.79%
57. Apr 30, 2019 130.60 10.73% 2,945.83 3.93%
58. May 31, 2019 123.68 0.46 -4.95% 2,752.06 -6.58%
59. Jun 30, 2019 133.96 8.31% 2,941.76 6.89%
60. Jul 31, 2019 136.27 1.72% 2,980.38 1.31%
61. Aug 31, 2019 137.86 0.46 1.50% 2,926.46 -1.81%
62. Sep 30, 2019 139.03 0.85% 2,976.74 1.72%
63. Oct 31, 2019 143.37 3.12% 3,037.56 2.04%
64. Nov 30, 2019 151.38 0.51 5.94% 3,140.98 3.40%
65. Dec 31, 2019 157.70 4.17% 3,230.78 2.86%
66. Jan 31, 2020 170.23 7.95% 3,225.52 -0.16%
67. Feb 29, 2020 162.01 0.51 -4.53% 2,954.22 -8.41%
68. Mar 31, 2020 157.71 -2.65% 2,584.59 -12.51%
69. Apr 30, 2020 179.21 13.63% 2,912.43 12.68%
70. May 31, 2020 183.25 0.51 2.54% 3,044.31 4.53%
71. Jun 30, 2020 203.51 11.06% 3,100.29 1.84%
Average: 2.57% 0.75%
Standard deviation: 6.12% 4.04%

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 MSFT 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
VarianceMSFT 37.40
VarianceS&P 500 16.35
CovarianceMSFT, S&P 500 16.44
Correlation coefficientMSFT, S&P 5001 0.67
βMSFT2 1.01
αMSFT3 1.81

Calculations

1 Correlation coefficientMSFT, S&P 500
= CovarianceMSFT, S&P 500 ÷ (Standard deviationMSFT × Standard deviationS&P 500)
= 16.44 ÷ (6.12 × 4.04)

2 βMSFT
= CovarianceMSFT, S&P 500 ÷ VarianceS&P 500
= 16.44 ÷ 16.35

3 αMSFT
= AverageMSFT – βMSFT × AverageS&P 500
= 2.571.01 × 0.75


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 1.41%
Expected rate of return on market portfolio2 E(RM) 12.56%
Systematic risk (β) of Microsoft Corp.’s common stock βMSFT 1.01
 
Expected rate of return on Microsoft Corp.’s common stock3 E(RMSFT) 12.63%

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(RMSFT) = RF + βMSFT [E(RM) – RF]
= 1.41% + 1.01 [12.56%1.41%]
= 12.63%