Stock Analysis on Net

ServiceNow Inc. (NYSE:NOW)

Capital Asset Pricing Model (CAPM) 

Microsoft Excel

Rates of Return

ServiceNow Inc., monthly rates of return

Microsoft Excel
ServiceNow Inc. (NOW) Standard & Poor’s 500 (S&P 500)
t Date PriceNOW,t1 DividendNOW,t1 RNOW,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2017 $90.62 2,278.87
1. Feb 28, 2017 $86.92 -4.08% 2,363.64 3.72%
2. Mar 31, 2017 $87.47 0.63% 2,362.72 -0.04%
3. Apr 30, 2017 $94.48 8.01% 2,384.20 0.91%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 $647.70 -7.17% 4,567.00 -0.83%
59. Dec 31, 2021 $649.11 0.22% 4,766.18 4.36%
Average (R): 3.65% 1.36%
Standard deviation: 7.50% 4.48%
ServiceNow Inc. (NOW) Standard & Poor’s 500 (S&P 500)
t Date PriceNOW,t1 DividendNOW,t1 RNOW,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2017 $90.62 2,278.87
1. Feb 28, 2017 $86.92 -4.08% 2,363.64 3.72%
2. Mar 31, 2017 $87.47 0.63% 2,362.72 -0.04%
3. Apr 30, 2017 $94.48 8.01% 2,384.20 0.91%
4. May 31, 2017 $104.65 10.76% 2,411.80 1.16%
5. Jun 30, 2017 $106.00 1.29% 2,423.41 0.48%
6. Jul 31, 2017 $110.45 4.20% 2,470.30 1.93%
7. Aug 31, 2017 $116.19 5.20% 2,471.65 0.05%
8. Sep 30, 2017 $117.53 1.15% 2,519.36 1.93%
9. Oct 31, 2017 $126.37 7.52% 2,575.26 2.22%
10. Nov 30, 2017 $123.00 -2.67% 2,647.58 2.81%
11. Dec 31, 2017 $130.39 6.01% 2,673.61 0.98%
12. Jan 31, 2018 $148.87 14.17% 2,823.81 5.62%
13. Feb 28, 2018 $161.01 8.15% 2,713.83 -3.89%
14. Mar 31, 2018 $165.45 2.76% 2,640.87 -2.69%
15. Apr 30, 2018 $166.14 0.42% 2,648.05 0.27%
16. May 31, 2018 $177.61 6.90% 2,705.27 2.16%
17. Jun 30, 2018 $172.47 -2.89% 2,718.37 0.48%
18. Jul 31, 2018 $175.96 2.02% 2,816.29 3.60%
19. Aug 31, 2018 $196.36 11.59% 2,901.52 3.03%
20. Sep 30, 2018 $195.63 -0.37% 2,913.98 0.43%
21. Oct 31, 2018 $181.04 -7.46% 2,711.74 -6.94%
22. Nov 30, 2018 $185.27 2.34% 2,760.17 1.79%
23. Dec 31, 2018 $178.05 -3.90% 2,506.85 -9.18%
24. Jan 31, 2019 $220.02 23.57% 2,704.10 7.87%
25. Feb 28, 2019 $239.44 8.83% 2,784.49 2.97%
26. Mar 31, 2019 $246.49 2.94% 2,834.40 1.79%
27. Apr 30, 2019 $271.51 10.15% 2,945.83 3.93%
28. May 31, 2019 $261.93 -3.53% 2,752.06 -6.58%
29. Jun 30, 2019 $274.57 4.83% 2,941.76 6.89%
30. Jul 31, 2019 $277.39 1.03% 2,980.38 1.31%
31. Aug 31, 2019 $261.84 -5.61% 2,926.46 -1.81%
32. Sep 30, 2019 $253.85 -3.05% 2,976.74 1.72%
33. Oct 31, 2019 $247.26 -2.60% 3,037.56 2.04%
34. Nov 30, 2019 $283.04 14.47% 3,140.98 3.40%
35. Dec 31, 2019 $282.32 -0.25% 3,230.78 2.86%
36. Jan 31, 2020 $338.23 19.80% 3,225.52 -0.16%
37. Feb 29, 2020 $326.09 -3.59% 2,954.22 -8.41%
38. Mar 31, 2020 $286.58 -12.12% 2,584.59 -12.51%
39. Apr 30, 2020 $351.54 22.67% 2,912.43 12.68%
40. May 31, 2020 $387.93 10.35% 3,044.31 4.53%
41. Jun 30, 2020 $405.06 4.42% 3,100.29 1.84%
42. Jul 31, 2020 $439.20 8.43% 3,271.12 5.51%
43. Aug 31, 2020 $482.02 9.75% 3,500.31 7.01%
44. Sep 30, 2020 $485.00 0.62% 3,363.00 -3.92%
45. Oct 31, 2020 $497.57 2.59% 3,269.96 -2.77%
46. Nov 30, 2020 $534.55 7.43% 3,621.63 10.75%
47. Dec 31, 2020 $550.43 2.97% 3,756.07 3.71%
48. Jan 31, 2021 $543.16 -1.32% 3,714.24 -1.11%
49. Feb 28, 2021 $533.46 -1.79% 3,811.15 2.61%
50. Mar 31, 2021 $500.11 -6.25% 3,972.89 4.24%
51. Apr 30, 2021 $506.37 1.25% 4,181.17 5.24%
52. May 31, 2021 $473.88 -6.42% 4,204.11 0.55%
53. Jun 30, 2021 $549.55 15.97% 4,297.50 2.22%
54. Jul 31, 2021 $587.89 6.98% 4,395.26 2.27%
55. Aug 31, 2021 $643.64 9.48% 4,522.68 2.90%
56. Sep 30, 2021 $622.27 -3.32% 4,307.54 -4.76%
57. Oct 31, 2021 $697.76 12.13% 4,605.38 6.91%
58. Nov 30, 2021 $647.70 -7.17% 4,567.00 -0.83%
59. Dec 31, 2021 $649.11 0.22% 4,766.18 4.36%
Average (R): 3.65% 1.36%
Standard deviation: 7.50% 4.48%

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 NOW during period t

3 Rate of return on S&P 500 (the market portfolio proxy) during period t


Variance and Covariance

ServiceNow Inc., calculation of variance and covariance of returns

Microsoft Excel
t Date RNOW,t RS&P 500,t (RNOW,tRNOW)2 (RS&P 500,tRS&P 500)2 (RNOW,tRNOW)×(RS&P 500,tRS&P 500)
1. Feb 28, 2017 -4.08% 3.72% 59.87 5.58 -18.28
2. Mar 31, 2017 0.63% -0.04% 9.13 1.95 4.22
3. Apr 30, 2017 8.01% 0.91% 19.00 0.20 -1.96
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2021 -7.17% -0.83% 117.27 4.80 23.73
59. Dec 31, 2021 0.22% 4.36% 11.81 9.02 -10.32
Total (Σ): 3,261.20 1,164.17 1,164.88
t Date RNOW,t RS&P 500,t (RNOW,tRNOW)2 (RS&P 500,tRS&P 500)2 (RNOW,tRNOW)×(RS&P 500,tRS&P 500)
1. Feb 28, 2017 -4.08% 3.72% 59.87 5.58 -18.28
2. Mar 31, 2017 0.63% -0.04% 9.13 1.95 4.22
3. Apr 30, 2017 8.01% 0.91% 19.00 0.20 -1.96
4. May 31, 2017 10.76% 1.16% 50.54 0.04 -1.42
5. Jun 30, 2017 1.29% 0.48% 5.59 0.77 2.07
6. Jul 31, 2017 4.20% 1.93% 0.30 0.33 0.31
7. Aug 31, 2017 5.20% 0.05% 2.38 1.70 -2.01
8. Sep 30, 2017 1.15% 1.93% 6.26 0.33 -1.43
9. Oct 31, 2017 7.52% 2.22% 14.95 0.74 3.33
10. Nov 30, 2017 -2.67% 2.81% 39.96 2.10 -9.17
11. Dec 31, 2017 6.01% 0.98% 5.54 0.14 -0.88
12. Jan 31, 2018 14.17% 5.62% 110.63 18.15 44.81
13. Feb 28, 2018 8.15% -3.89% 20.25 27.59 -23.64
14. Mar 31, 2018 2.76% -2.69% 0.80 16.37 3.63
15. Apr 30, 2018 0.42% 0.27% 10.48 1.18 3.52
16. May 31, 2018 6.90% 2.16% 10.56 0.64 2.61
17. Jun 30, 2018 -2.89% 0.48% 42.89 0.76 5.72
18. Jul 31, 2018 2.02% 3.60% 2.66 5.04 -3.66
19. Aug 31, 2018 11.59% 3.03% 63.02 2.78 13.25
20. Sep 30, 2018 -0.37% 0.43% 16.21 0.86 3.74
21. Oct 31, 2018 -7.46% -6.94% 123.49 68.86 92.22
22. Nov 30, 2018 2.34% 1.79% 1.74 0.18 -0.56
23. Dec 31, 2018 -3.90% -9.18% 57.03 111.00 79.56
24. Jan 31, 2019 23.57% 7.87% 396.70 42.39 129.67
25. Feb 28, 2019 8.83% 2.97% 26.75 2.61 8.35
26. Mar 31, 2019 2.94% 1.79% 0.50 0.19 -0.31
27. Apr 30, 2019 10.15% 3.93% 42.19 6.62 16.72
28. May 31, 2019 -3.53% -6.58% 51.60 62.97 57.00
29. Jun 30, 2019 4.83% 6.89% 1.37 30.64 6.48
30. Jul 31, 2019 1.03% 1.31% 6.90 0.00 0.12
31. Aug 31, 2019 -5.61% -1.81% 85.76 10.03 29.33
32. Sep 30, 2019 -3.05% 1.72% 44.97 0.13 -2.42
33. Oct 31, 2019 -2.60% 2.04% 39.07 0.47 -4.28
34. Nov 30, 2019 14.47% 3.40% 116.98 4.19 22.14
35. Dec 31, 2019 -0.25% 2.86% 15.28 2.25 -5.87
36. Jan 31, 2020 19.80% -0.16% 260.79 2.31 -24.56
37. Feb 29, 2020 -3.59% -8.41% 52.48 95.43 70.77
38. Mar 31, 2020 -12.12% -12.51% 248.73 192.37 218.74
39. Apr 30, 2020 22.67% 12.68% 361.48 128.29 215.35
40. May 31, 2020 10.35% 4.53% 44.85 10.05 21.23
41. Jun 30, 2020 4.42% 1.84% 0.58 0.23 0.37
42. Jul 31, 2020 8.43% 5.51% 22.79 17.24 19.82
43. Aug 31, 2020 9.75% 7.01% 37.15 31.91 34.43
44. Sep 30, 2020 0.62% -3.92% 9.22 27.89 16.04
45. Oct 31, 2020 2.59% -2.77% 1.13 17.01 4.38
46. Nov 30, 2020 7.43% 10.75% 14.27 88.30 35.49
47. Dec 31, 2020 2.97% 3.71% 0.47 5.54 -1.61
48. Jan 31, 2021 -1.32% -1.11% 24.76 6.11 12.30
49. Feb 28, 2021 -1.79% 2.61% 29.60 1.57 -6.81
50. Mar 31, 2021 -6.25% 4.24% 98.14 8.33 -28.59
51. Apr 30, 2021 1.25% 5.24% 5.77 15.09 -9.33
52. May 31, 2021 -6.42% 0.55% 101.43 0.65 8.15
53. Jun 30, 2021 15.97% 2.22% 151.62 0.75 10.63
54. Jul 31, 2021 6.98% 2.27% 11.03 0.84 3.05
55. Aug 31, 2021 9.48% 2.90% 33.97 2.38 8.98
56. Sep 30, 2021 -3.32% -4.76% 48.65 37.39 42.65
57. Oct 31, 2021 12.13% 6.91% 71.85 30.87 47.10
58. Nov 30, 2021 -7.17% -0.83% 117.27 4.80 23.73
59. Dec 31, 2021 0.22% 4.36% 11.81 9.02 -10.32
Total (Σ): 3,261.20 1,164.17 1,164.88

Show all

VarianceNOW = Σ(RNOW,tRNOW)2 ÷ (59 – 1)
= 3,261.20 ÷ (59 – 1)
= 56.23

VarianceS&P 500 = Σ(RS&P 500,tRS&P 500)2 ÷ (59 – 1)
= 1,164.17 ÷ (59 – 1)
= 20.07

CovarianceNOW, S&P 500 = Σ(RNOW,tRNOW)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 1,164.88 ÷ (59 – 1)
= 20.08


Systematic Risk (β) Estimation

Microsoft Excel
VarianceNOW 56.23
VarianceS&P 500 20.07
CovarianceNOW, S&P 500 20.08
Correlation coefficientNOW, S&P 5001 0.60
βNOW2 1.00
αNOW3 2.30%

Calculations

1 Correlation coefficientNOW, S&P 500
= CovarianceNOW, S&P 500 ÷ (Standard deviationNOW × Standard deviationS&P 500)
= 20.08 ÷ (7.50% × 4.48%)
= 0.60

2 βNOW
= CovarianceNOW, S&P 500 ÷ VarianceS&P 500
= 20.08 ÷ 20.07
= 1.00

3 αNOW
= AverageNOW – βNOW × AverageS&P 500
= 3.65%1.00 × 1.36%
= 2.30%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 3.94%
Expected rate of return on market portfolio2 E(RM) 12.80%
Systematic risk (β) of ServiceNow Inc. common stock βNOW 1.00
 
Expected rate of return on ServiceNow Inc. common stock3 E(RNOW) 12.81%

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(RNOW) = RF + βNOW [E(RM) – RF]
= 3.94% + 1.00 [12.80%3.94%]
= 12.81%