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, 2013 31.84 1,685.73
1. Aug 31, 2013 33.40 0.23 5.62% 1,632.97 -3.13%
2. Sep 30, 2013 33.28 -0.36% 1,681.55 2.97%
3. Oct 31, 2013 35.41 6.40% 1,756.54 4.46%
. . . . . . .
. . . . . . .
. . . . . . .
70. May 31, 2019 123.68 0.46 -4.95% 2,752.06 -6.58%
71. Jun 30, 2019 133.96 8.31% 2,941.76 6.89%
Average: 2.41% 0.84%
Standard deviation: 5.88% 3.35%
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, 2013 31.84 1,685.73
1. Aug 31, 2013 33.40 0.23 5.62% 1,632.97 -3.13%
2. Sep 30, 2013 33.28 -0.36% 1,681.55 2.97%
3. Oct 31, 2013 35.41 6.40% 1,756.54 4.46%
4. Nov 30, 2013 38.13 0.28 8.47% 1,805.81 2.80%
5. Dec 31, 2013 37.41 -1.89% 1,848.36 2.36%
6. Jan 31, 2014 37.84 1.15% 1,782.59 -3.56%
7. Feb 28, 2014 38.31 0.28 1.98% 1,859.45 4.31%
8. Mar 31, 2014 40.99 7.00% 1,872.34 0.69%
9. Apr 30, 2014 40.40 -1.44% 1,883.95 0.62%
10. May 31, 2014 40.94 0.28 2.03% 1,923.57 2.10%
11. Jun 30, 2014 41.70 1.86% 1,960.23 1.91%
12. Jul 31, 2014 43.16 3.50% 1,930.67 -1.51%
13. Aug 31, 2014 45.43 0.28 5.91% 2,003.37 3.77%
14. Sep 30, 2014 46.36 2.05% 1,972.29 -1.55%
15. Oct 31, 2014 46.95 1.27% 2,018.05 2.32%
16. Nov 30, 2014 47.81 0.31 2.49% 2,067.56 2.45%
17. Dec 31, 2014 46.45 -2.84% 2,058.90 -0.42%
18. Jan 31, 2015 40.40 -13.02% 1,994.99 -3.10%
19. Feb 28, 2015 43.85 0.31 9.31% 2,104.50 5.49%
20. Mar 31, 2015 40.66 -7.27% 2,067.89 -1.74%
21. Apr 30, 2015 48.64 19.63% 2,085.51 0.85%
22. May 31, 2015 46.86 0.31 -3.02% 2,107.39 1.05%
23. Jun 30, 2015 44.15 -5.78% 2,063.11 -2.10%
24. Jul 31, 2015 46.70 5.78% 2,103.84 1.97%
25. Aug 31, 2015 43.52 0.31 -6.15% 1,972.18 -6.26%
26. Sep 30, 2015 44.26 1.70% 1,920.03 -2.64%
27. Oct 31, 2015 52.64 18.93% 2,079.36 8.30%
28. Nov 30, 2015 54.35 0.36 3.93% 2,080.41 0.05%
29. Dec 31, 2015 55.48 2.08% 2,043.94 -1.75%
30. Jan 31, 2016 55.09 -0.70% 1,940.24 -5.07%
31. Feb 29, 2016 50.88 0.36 -6.99% 1,932.23 -0.41%
32. Mar 31, 2016 55.23 8.55% 2,059.74 6.60%
33. Apr 30, 2016 49.87 -9.70% 2,065.30 0.27%
34. May 31, 2016 53.00 0.36 7.00% 2,096.95 1.53%
35. Jun 30, 2016 51.17 -3.45% 2,098.86 0.09%
36. Jul 31, 2016 56.68 10.77% 2,173.60 3.56%
37. Aug 31, 2016 57.46 0.36 2.01% 2,170.95 -0.12%
38. Sep 30, 2016 57.60 0.24% 2,168.27 -0.12%
39. Oct 31, 2016 59.92 4.03% 2,126.15 -1.94%
40. Nov 30, 2016 60.26 0.39 1.22% 2,198.81 3.42%
41. Dec 31, 2016 62.14 3.12% 2,238.83 1.82%
42. Jan 31, 2017 64.65 4.04% 2,278.87 1.79%
43. Feb 28, 2017 63.98 0.39 -0.43% 2,363.64 3.72%
44. Mar 31, 2017 65.86 2.94% 2,362.72 -0.04%
45. Apr 30, 2017 68.46 3.95% 2,384.20 0.91%
46. May 31, 2017 69.84 0.39 2.59% 2,411.80 1.16%
47. Jun 30, 2017 68.93 -1.30% 2,423.41 0.48%
48. Jul 31, 2017 72.70 5.47% 2,470.30 1.93%
49. Aug 31, 2017 74.77 0.39 3.38% 2,471.65 0.05%
50. Sep 30, 2017 74.49 -0.37% 2,519.36 1.93%
51. Oct 31, 2017 83.18 11.67% 2,575.26 2.22%
52. Nov 30, 2017 84.17 0.42 1.70% 2,647.58 2.81%
53. Dec 31, 2017 85.54 1.63% 2,673.61 0.98%
54. Jan 31, 2018 95.01 11.07% 2,823.81 5.62%
55. Feb 28, 2018 93.77 0.42 -0.86% 2,713.83 -3.89%
56. Mar 31, 2018 91.27 -2.67% 2,640.87 -2.69%
57. Apr 30, 2018 93.52 2.47% 2,648.05 0.27%
58. May 31, 2018 98.84 0.42 6.14% 2,705.27 2.16%
59. Jun 30, 2018 98.61 -0.23% 2,718.37 0.48%
60. Jul 31, 2018 106.08 7.58% 2,816.29 3.60%
61. Aug 31, 2018 112.33 0.42 6.29% 2,901.52 3.03%
62. Sep 30, 2018 114.37 1.82% 2,913.98 0.43%
63. Oct 31, 2018 106.81 -6.61% 2,711.74 -6.94%
64. Nov 30, 2018 110.89 0.46 4.25% 2,760.17 1.79%
65. Dec 31, 2018 101.57 -8.40% 2,506.85 -9.18%
66. Jan 31, 2019 104.43 2.82% 2,704.10 7.87%
67. Feb 28, 2019 112.03 0.46 7.72% 2,784.49 2.97%
68. Mar 31, 2019 117.94 5.28% 2,834.40 1.79%
69. Apr 30, 2019 130.60 10.73% 2,945.83 3.93%
70. May 31, 2019 123.68 0.46 -4.95% 2,752.06 -6.58%
71. Jun 30, 2019 133.96 8.31% 2,941.76 6.89%
Average: 2.41% 0.84%
Standard deviation: 5.88% 3.35%

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 34.61
VarianceS&P 500 11.20
CovarianceMSFT, S&P 500 12.42
Correlation coefficientMSFT, S&P 5001 0.63
βMSFT2 1.11
αMSFT3 1.48

Calculations

1 Correlation coefficientMSFT, S&P 500
= CovarianceMSFT, S&P 500 ÷ (Standard deviationMSFT × Standard deviationS&P 500)
= 12.42 ÷ (5.88 × 3.35)

2 βMSFT
= CovarianceMSFT, S&P 500 ÷ VarianceS&P 500
= 12.42 ÷ 11.20

3 αMSFT
= AverageMSFT – βMSFT × AverageS&P 500
= 2.411.11 × 0.84


Expected Rate of Return

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

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.31% + 1.11 [11.87%1.31%]
= 13.02%