Microsoft Excel LibreOffice Calc

Gilead Sciences Inc. (GILD)


Capital Asset Pricing Model (CAPM)

Intermediate level


Rates of Return

Gilead Sciences Inc., monthly rates of return

Microsoft Excel LibreOffice Calc
Gilead Sciences Inc. (GILD) Standard & Poor’s 500 (S&P 500)
t Date PriceGILD,t1 DividendGILD,t1 RGILD,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 80.65 1,782.59
1. Feb 28, 2014 82.79 2.65% 1,859.45 4.31%
2. Mar 31, 2014 70.86 -14.41% 1,872.34 0.69%
3. Apr 30, 2014 78.49 10.77% 1,883.95 0.62%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2018 71.94 5.51% 2,760.17 1.79%
59. Dec 31, 2018 62.55 0.57 -12.26% 2,506.85 -9.18%
Average: 0.01% 0.63%
Standard deviation: 7.50% 3.13%
Gilead Sciences Inc. (GILD) Standard & Poor’s 500 (S&P 500)
t Date PriceGILD,t1 DividendGILD,t1 RGILD,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2014 80.65 1,782.59
1. Feb 28, 2014 82.79 2.65% 1,859.45 4.31%
2. Mar 31, 2014 70.86 -14.41% 1,872.34 0.69%
3. Apr 30, 2014 78.49 10.77% 1,883.95 0.62%
4. May 31, 2014 81.21 3.47% 1,923.57 2.10%
5. Jun 30, 2014 82.91 2.09% 1,960.23 1.91%
6. Jul 31, 2014 91.55 10.42% 1,930.67 -1.51%
7. Aug 31, 2014 107.56 17.49% 2,003.37 3.77%
8. Sep 30, 2014 106.45 -1.03% 1,972.29 -1.55%
9. Oct 31, 2014 112.00 5.21% 2,018.05 2.32%
10. Nov 30, 2014 100.32 -10.43% 2,067.56 2.45%
11. Dec 31, 2014 94.26 -6.04% 2,058.90 -0.42%
12. Jan 31, 2015 104.83 11.21% 1,994.99 -3.10%
13. Feb 28, 2015 103.53 -1.24% 2,104.50 5.49%
14. Mar 31, 2015 98.13 -5.22% 2,067.89 -1.74%
15. Apr 30, 2015 100.51 2.43% 2,085.51 0.85%
16. May 31, 2015 112.27 11.70% 2,107.39 1.05%
17. Jun 30, 2015 117.08 0.43 4.67% 2,063.11 -2.10%
18. Jul 31, 2015 117.86 0.67% 2,103.84 1.97%
19. Aug 31, 2015 105.07 -10.85% 1,972.18 -6.26%
20. Sep 30, 2015 98.19 0.43 -6.14% 1,920.03 -2.64%
21. Oct 31, 2015 108.13 10.12% 2,079.36 8.30%
22. Nov 30, 2015 105.96 -2.01% 2,080.41 0.05%
23. Dec 31, 2015 101.19 0.43 -4.10% 2,043.94 -1.75%
24. Jan 31, 2016 83.00 -17.98% 1,940.24 -5.07%
25. Feb 29, 2016 87.25 5.12% 1,932.23 -0.41%
26. Mar 31, 2016 91.86 0.43 5.78% 2,059.74 6.60%
27. Apr 30, 2016 88.21 -3.97% 2,065.30 0.27%
28. May 31, 2016 87.06 -1.30% 2,096.95 1.53%
29. Jun 30, 2016 83.42 0.47 -3.64% 2,098.86 0.09%
30. Jul 31, 2016 79.47 -4.74% 2,173.60 3.56%
31. Aug 31, 2016 78.38 -1.37% 2,170.95 -0.12%
32. Sep 30, 2016 79.12 0.47 1.54% 2,168.27 -0.12%
33. Oct 31, 2016 73.63 -6.94% 2,126.15 -1.94%
34. Nov 30, 2016 73.70 0.10% 2,198.81 3.42%
35. Dec 31, 2016 71.61 0.47 -2.20% 2,238.83 1.82%
36. Jan 31, 2017 72.45 1.17% 2,278.87 1.79%
37. Feb 28, 2017 70.48 -2.72% 2,363.64 3.72%
38. Mar 31, 2017 67.92 0.52 -2.89% 2,362.72 -0.04%
39. Apr 30, 2017 68.55 0.93% 2,384.20 0.91%
40. May 31, 2017 64.89 -5.34% 2,411.80 1.16%
41. Jun 30, 2017 70.78 0.52 9.88% 2,423.41 0.48%
42. Jul 31, 2017 76.09 7.50% 2,470.30 1.93%
43. Aug 31, 2017 83.71 10.01% 2,471.65 0.05%
44. Sep 30, 2017 81.02 0.52 -2.59% 2,519.36 1.93%
45. Oct 31, 2017 74.96 -7.48% 2,575.26 2.22%
46. Nov 30, 2017 74.78 -0.24% 2,647.58 2.81%
47. Dec 31, 2017 71.64 0.52 -3.50% 2,673.61 0.98%
48. Jan 31, 2018 83.80 16.97% 2,823.81 5.62%
49. Feb 28, 2018 78.73 -6.05% 2,713.83 -3.89%
50. Mar 31, 2018 75.39 0.57 -3.52% 2,640.87 -2.69%
51. Apr 30, 2018 72.23 -4.19% 2,648.05 0.27%
52. May 31, 2018 67.40 -6.69% 2,705.27 2.16%
53. Jun 30, 2018 70.84 0.57 5.95% 2,718.37 0.48%
54. Jul 31, 2018 77.83 9.87% 2,816.29 3.60%
55. Aug 31, 2018 75.73 -2.70% 2,901.52 3.03%
56. Sep 30, 2018 77.21 0.57 2.71% 2,913.98 0.43%
57. Oct 31, 2018 68.18 -11.70% 2,711.74 -6.94%
58. Nov 30, 2018 71.94 5.51% 2,760.17 1.79%
59. Dec 31, 2018 62.55 0.57 -12.26% 2,506.85 -9.18%
Average: 0.01% 0.63%
Standard deviation: 7.50% 3.13%

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 GILD 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
VarianceGILD 56.31
VarianceS&P 500 9.77
CovarianceGILD, S&P 500 11.27
Correlation coefficientGILD, S&P 5001 0.48
βGILD2 1.15
αGILD3 -0.72

Calculations

1 Correlation coefficientGILD, S&P 500
= CovarianceGILD, S&P 500 ÷ (Standard deviationGILD × Standard deviationS&P 500)
= 11.27 ÷ (7.50 × 3.13)

2 βGILD
= CovarianceGILD, S&P 500 ÷ VarianceS&P 500
= 11.27 ÷ 9.77

3 αGILD
= AverageGILD – βGILD × AverageS&P 500
= 0.011.15 × 0.63


Expected Rate of Return

Microsoft Excel LibreOffice Calc
Assumptions
Rate of return on LT Treasury Composite1 RF 2.23%
Expected rate of return on market portfolio2 E(RM) 11.40%
Systematic risk (β) of Gilead Sciences Inc.’s common stock βGILD 1.15
 
Expected rate of return on Gilead Sciences Inc.’s common stock3 E(RGILD) 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(RGILD) = RF + βGILD [E(RM) – RF]
= 2.23% + 1.15 [11.40%2.23%]
= 12.81%