Microsoft Excel LibreOffice Calc

Microsoft Corp. (MSFT)


Capital Asset Pricing Model (CAPM)

Difficulty: Intermediate

Capital asset pricing model (CAPM) indicates what should be the expected or required rate of return on risky assets like Microsoft Corp.’s common stock.


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, 2012 29.47 1,379.32
1. Aug 31, 2012 30.82 0.20 5.26% 1,406.58 1.98%
2. Sep 30, 2012 29.76 -3.44% 1,440.67 2.42%
3. Oct 31, 2012 28.54 -4.10% 1,412.16 -1.98%
. . . . . . .
. . . . . . .
. . . . . . .
70. May 31, 2018 98.84 0.42 6.14% 2,705.27 2.16%
71. Jun 30, 2018 98.61 -0.23% 2,718.37 0.48%
Average: 2.10% 1.00%
Standard Deviation: 5.91% 2.71%
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, 2012 29.47 1,379.32
1. Aug 31, 2012 30.82 0.20 5.26% 1,406.58 1.98%
2. Sep 30, 2012 29.76 -3.44% 1,440.67 2.42%
3. Oct 31, 2012 28.54 -4.10% 1,412.16 -1.98%
4. Nov 30, 2012 26.62 0.23 -5.92% 1,416.18 0.28%
5. Dec 31, 2012 26.71 0.34% 1,426.19 0.71%
6. Jan 31, 2013 27.45 2.77% 1,498.11 5.04%
7. Feb 28, 2013 27.80 0.23 2.11% 1,514.68 1.11%
8. Mar 31, 2013 28.61 2.91% 1,569.19 3.60%
9. Apr 30, 2013 33.10 15.69% 1,597.57 1.81%
10. May 31, 2013 34.90 0.23 6.13% 1,630.74 2.08%
11. Jun 30, 2013 34.54 -1.03% 1,606.28 -1.50%
12. Jul 31, 2013 31.84 -7.82% 1,685.73 4.95%
13. Aug 31, 2013 33.40 0.23 5.62% 1,632.97 -3.13%
14. Sep 30, 2013 33.28 -0.36% 1,681.55 2.97%
15. Oct 31, 2013 35.41 6.40% 1,756.54 4.46%
16. Nov 30, 2013 38.13 0.28 8.47% 1,805.81 2.80%
17. Dec 31, 2013 37.41 -1.89% 1,848.36 2.36%
18. Jan 31, 2014 37.84 1.15% 1,782.59 -3.56%
19. Feb 28, 2014 38.31 0.28 1.98% 1,859.45 4.31%
20. Mar 31, 2014 40.99 7.00% 1,872.34 0.69%
21. Apr 30, 2014 40.40 -1.44% 1,883.95 0.62%
22. May 31, 2014 40.94 0.28 2.03% 1,923.57 2.10%
23. Jun 30, 2014 41.70 1.86% 1,960.23 1.91%
24. Jul 31, 2014 43.16 3.50% 1,930.67 -1.51%
25. Aug 31, 2014 45.43 0.28 5.91% 2,003.37 3.77%
26. Sep 30, 2014 46.36 2.05% 1,972.29 -1.55%
27. Oct 31, 2014 46.95 1.27% 2,018.05 2.32%
28. Nov 30, 2014 47.81 0.31 2.49% 2,067.56 2.45%
29. Dec 31, 2014 46.45 -2.84% 2,058.90 -0.42%
30. Jan 31, 2015 40.40 -13.02% 1,994.99 -3.10%
31. Feb 28, 2015 43.85 0.31 9.31% 2,104.50 5.49%
32. Mar 31, 2015 40.66 -7.27% 2,067.89 -1.74%
33. Apr 30, 2015 48.64 19.63% 2,085.51 0.85%
34. May 31, 2015 46.86 0.31 -3.02% 2,107.39 1.05%
35. Jun 30, 2015 44.15 -5.78% 2,063.11 -2.10%
36. Jul 31, 2015 46.70 5.78% 2,103.84 1.97%
37. Aug 31, 2015 43.52 0.31 -6.15% 1,972.18 -6.26%
38. Sep 30, 2015 44.26 1.70% 1,920.03 -2.64%
39. Oct 31, 2015 52.64 18.93% 2,079.36 8.30%
40. Nov 30, 2015 54.35 0.36 3.93% 2,080.41 0.05%
41. Dec 31, 2015 55.48 2.08% 2,043.94 -1.75%
42. Jan 31, 2016 55.09 -0.70% 1,940.24 -5.07%
43. Feb 29, 2016 50.88 0.36 -6.99% 1,932.23 -0.41%
44. Mar 31, 2016 55.23 8.55% 2,059.74 6.60%
45. Apr 30, 2016 49.87 -9.70% 2,065.30 0.27%
46. May 31, 2016 53.00 0.36 7.00% 2,096.95 1.53%
47. Jun 30, 2016 51.17 -3.45% 2,098.86 0.09%
48. Jul 31, 2016 56.68 10.77% 2,173.60 3.56%
49. Aug 31, 2016 57.46 0.36 2.01% 2,170.95 -0.12%
50. Sep 30, 2016 57.60 0.24% 2,168.27 -0.12%
51. Oct 31, 2016 59.92 4.03% 2,126.15 -1.94%
52. Nov 30, 2016 60.26 0.39 1.22% 2,198.81 3.42%
53. Dec 31, 2016 62.14 3.12% 2,238.83 1.82%
54. Jan 31, 2017 64.65 4.04% 2,278.87 1.79%
55. Feb 28, 2017 63.98 0.39 -0.43% 2,363.64 3.72%
56. Mar 31, 2017 65.86 2.94% 2,362.72 -0.04%
57. Apr 30, 2017 68.46 3.95% 2,384.20 0.91%
58. May 31, 2017 69.84 0.39 2.59% 2,411.80 1.16%
59. Jun 30, 2017 68.93 -1.30% 2,423.41 0.48%
60. Jul 31, 2017 72.70 5.47% 2,470.30 1.93%
61. Aug 31, 2017 74.77 0.39 3.38% 2,471.65 0.05%
62. Sep 30, 2017 74.49 -0.37% 2,519.36 1.93%
63. Oct 31, 2017 83.18 11.67% 2,575.26 2.22%
64. Nov 30, 2017 84.17 0.42 1.70% 2,647.58 2.81%
65. Dec 31, 2017 85.54 1.63% 2,673.61 0.98%
66. Jan 31, 2018 95.01 11.07% 2,823.81 5.62%
67. Feb 28, 2018 93.77 0.42 -0.86% 2,713.83 -3.89%
68. Mar 31, 2018 91.27 -2.67% 2,640.87 -2.69%
69. Apr 30, 2018 93.52 2.47% 2,648.05 0.27%
70. May 31, 2018 98.84 0.42 6.14% 2,705.27 2.16%
71. Jun 30, 2018 98.61 -0.23% 2,718.37 0.48%
Average: 2.10% 1.00%
Standard Deviation: 5.91% 2.71%

Show All

1 Data in USD $ 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.91
VarianceS&P 500 7.35
CovarianceMSFT, S&P 500 7.72
Correlation CoefficientMSFT, S&P 5001 0.48
βMSFT2 1.05
αMSFT3 1.06

Calculations

1 CovarianceMSFT, S&P 500 ÷ (Standard DeviationMSFT × Standard DeviationS&P 500)
= 7.72 ÷ (5.91 × 2.71)

2 CovarianceMSFT, S&P 500 ÷ VarianceS&P 500
= 7.72 ÷ 7.35

3 AverageMSFT – βMSFT × AverageS&P 500
= 2.101.05 × 1.00


Expected Rate of Return

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

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).

Calculations

2 See Details »

3 E(RMSFT) = RF + βMSFT [E(RM) – RF]
= 2.44% + 1.05 [11.78%2.44%]
= 12.25%