Stock Analysis on Net

Eli Lilly & Co. (NYSE:LLY)

Capital Asset Pricing Model (CAPM) 

Microsoft Excel

Rates of Return

Eli Lilly & Co., monthly rates of return

Microsoft Excel
Eli Lilly & Co. (LLY) Standard & Poor’s 500 (S&P 500)
t Date PriceLLY,t1 DividendLLY,t1 RLLY,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $119.86 2,704.10
1. Feb 28, 2019 $126.29 $0.645 5.90% 2,784.49 2.97%
2. Mar 31, 2019 $129.76 2.75% 2,834.40 1.79%
3. Apr 30, 2019 $117.04 -9.80% 2,945.83 3.93%
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 $591.04 $1.13 6.90% 4,567.80 8.92%
59. Dec 31, 2023 $582.92 -1.37% 4,769.83 4.42%
Average (R): 3.19% 1.11%
Standard deviation: 8.33% 5.31%
Eli Lilly & Co. (LLY) Standard & Poor’s 500 (S&P 500)
t Date PriceLLY,t1 DividendLLY,t1 RLLY,t2 PriceS&P 500,t RS&P 500,t3
Jan 31, 2019 $119.86 2,704.10
1. Feb 28, 2019 $126.29 $0.645 5.90% 2,784.49 2.97%
2. Mar 31, 2019 $129.76 2.75% 2,834.40 1.79%
3. Apr 30, 2019 $117.04 -9.80% 2,945.83 3.93%
4. May 31, 2019 $115.94 $0.645 -0.39% 2,752.06 -6.58%
5. Jun 30, 2019 $110.79 -4.44% 2,941.76 6.89%
6. Jul 31, 2019 $108.95 -1.66% 2,980.38 1.31%
7. Aug 31, 2019 $112.97 $0.645 4.28% 2,926.46 -1.81%
8. Sep 30, 2019 $111.83 -1.01% 2,976.74 1.72%
9. Oct 31, 2019 $113.95 1.90% 3,037.56 2.04%
10. Nov 30, 2019 $117.35 $0.645 3.55% 3,140.98 3.40%
11. Dec 31, 2019 $131.43 12.00% 3,230.78 2.86%
12. Jan 31, 2020 $139.64 6.25% 3,225.52 -0.16%
13. Feb 29, 2020 $126.13 $0.74 -9.14% 2,954.22 -8.41%
14. Mar 31, 2020 $138.72 9.98% 2,584.59 -12.51%
15. Apr 30, 2020 $154.64 11.48% 2,912.43 12.68%
16. May 31, 2020 $152.95 $0.74 -0.61% 3,044.31 4.53%
17. Jun 30, 2020 $164.18 7.34% 3,100.29 1.84%
18. Jul 31, 2020 $150.29 -8.46% 3,271.12 5.51%
19. Aug 31, 2020 $148.39 $0.74 -0.77% 3,500.31 7.01%
20. Sep 30, 2020 $148.02 -0.25% 3,363.00 -3.92%
21. Oct 31, 2020 $130.46 -11.86% 3,269.96 -2.77%
22. Nov 30, 2020 $145.65 $0.74 12.21% 3,621.63 10.75%
23. Dec 31, 2020 $168.84 15.92% 3,756.07 3.71%
24. Jan 31, 2021 $207.97 23.18% 3,714.24 -1.11%
25. Feb 28, 2021 $204.89 $0.85 -1.07% 3,811.15 2.61%
26. Mar 31, 2021 $186.82 -8.82% 3,972.89 4.24%
27. Apr 30, 2021 $182.77 -2.17% 4,181.17 5.24%
28. May 31, 2021 $199.74 $0.85 9.75% 4,204.11 0.55%
29. Jun 30, 2021 $229.52 14.91% 4,297.50 2.22%
30. Jul 31, 2021 $243.50 6.09% 4,395.26 2.27%
31. Aug 31, 2021 $258.29 $0.85 6.42% 4,522.68 2.90%
32. Sep 30, 2021 $231.05 -10.55% 4,307.54 -4.76%
33. Oct 31, 2021 $254.76 10.26% 4,605.38 6.91%
34. Nov 30, 2021 $248.04 $0.85 -2.30% 4,567.00 -0.83%
35. Dec 31, 2021 $276.22 11.36% 4,766.18 4.36%
36. Jan 31, 2022 $245.39 -11.16% 4,515.55 -5.26%
37. Feb 28, 2022 $249.95 $0.98 2.26% 4,373.94 -3.14%
38. Mar 31, 2022 $286.37 14.57% 4,530.41 3.58%
39. Apr 30, 2022 $292.13 2.01% 4,131.93 -8.80%
40. May 31, 2022 $313.44 $0.98 7.63% 4,132.15 0.01%
41. Jun 30, 2022 $324.23 3.44% 3,785.38 -8.39%
42. Jul 31, 2022 $329.69 1.68% 4,130.29 9.11%
43. Aug 31, 2022 $301.23 $0.98 -8.34% 3,955.00 -4.24%
44. Sep 30, 2022 $323.35 7.34% 3,585.62 -9.34%
45. Oct 31, 2022 $362.09 11.98% 3,871.98 7.99%
46. Nov 30, 2022 $371.08 $0.98 2.75% 4,080.11 5.38%
47. Dec 31, 2022 $365.84 -1.41% 3,839.50 -5.90%
48. Jan 31, 2023 $344.15 -5.93% 4,076.60 6.18%
49. Feb 28, 2023 $311.22 $1.13 -9.24% 3,970.15 -2.61%
50. Mar 31, 2023 $343.42 10.35% 4,109.31 3.51%
51. Apr 30, 2023 $395.86 15.27% 4,169.48 1.46%
52. May 31, 2023 $429.46 $1.13 8.77% 4,179.83 0.25%
53. Jun 30, 2023 $468.98 9.20% 4,376.86 4.71%
54. Jul 31, 2023 $454.55 -3.08% 4,588.96 4.85%
55. Aug 31, 2023 $554.20 $1.13 22.17% 4,507.66 -1.77%
56. Sep 30, 2023 $537.13 -3.08% 4,288.05 -4.87%
57. Oct 31, 2023 $553.93 3.13% 4,193.80 -2.20%
58. Nov 30, 2023 $591.04 $1.13 6.90% 4,567.80 8.92%
59. Dec 31, 2023 $582.92 -1.37% 4,769.83 4.42%
Average (R): 3.19% 1.11%
Standard deviation: 8.33% 5.31%

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

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


Variance and Covariance

Eli Lilly & Co., calculation of variance and covariance of returns

Microsoft Excel
t Date RLLY,t RS&P 500,t (RLLY,tRLLY)2 (RS&P 500,tRS&P 500)2 (RLLY,tRLLY)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 5.90% 2.97% 7.37 3.49 5.07
2. Mar 31, 2019 2.75% 1.79% 0.19 0.47 -0.30
3. Apr 30, 2019 -9.80% 3.93% 168.75 7.98 -36.70
. . . . . . .
. . . . . . .
. . . . . . .
58. Nov 30, 2023 6.90% 8.92% 13.81 61.03 29.03
59. Dec 31, 2023 -1.37% 4.42% 20.81 11.00 -15.13
Total (Σ): 4,020.05 1,634.30 491.40
t Date RLLY,t RS&P 500,t (RLLY,tRLLY)2 (RS&P 500,tRS&P 500)2 (RLLY,tRLLY)×(RS&P 500,tRS&P 500)
1. Feb 28, 2019 5.90% 2.97% 7.37 3.49 5.07
2. Mar 31, 2019 2.75% 1.79% 0.19 0.47 -0.30
3. Apr 30, 2019 -9.80% 3.93% 168.75 7.98 -36.70
4. May 31, 2019 -0.39% -6.58% 12.79 59.04 27.48
5. Jun 30, 2019 -4.44% 6.89% 58.21 33.49 -44.15
6. Jul 31, 2019 -1.66% 1.31% 23.51 0.04 -1.00
7. Aug 31, 2019 4.28% -1.81% 1.20 8.50 -3.19
8. Sep 30, 2019 -1.01% 1.72% 17.61 0.37 -2.57
9. Oct 31, 2019 1.90% 2.04% 1.67 0.88 -1.21
10. Nov 30, 2019 3.55% 3.40% 0.13 5.28 0.83
11. Dec 31, 2019 12.00% 2.86% 77.63 3.07 15.45
12. Jan 31, 2020 6.25% -0.16% 9.36 1.61 -3.88
13. Feb 29, 2020 -9.14% -8.41% 152.09 90.57 117.37
14. Mar 31, 2020 9.98% -12.51% 46.16 185.44 -92.52
15. Apr 30, 2020 11.48% 12.68% 68.70 134.06 95.97
16. May 31, 2020 -0.61% 4.53% 14.45 11.71 -13.01
17. Jun 30, 2020 7.34% 1.84% 17.26 0.54 3.05
18. Jul 31, 2020 -8.46% 5.51% 135.67 19.40 -51.30
19. Aug 31, 2020 -0.77% 7.01% 15.68 34.82 -23.36
20. Sep 30, 2020 -0.25% -3.92% 11.81 25.29 17.28
21. Oct 31, 2020 -11.86% -2.77% 226.53 15.00 58.28
22. Nov 30, 2020 12.21% 10.75% 81.41 93.10 87.06
23. Dec 31, 2020 15.92% 3.71% 162.16 6.79 33.19
24. Jan 31, 2021 23.18% -1.11% 399.53 4.93 -44.36
25. Feb 28, 2021 -1.07% 2.61% 18.15 2.26 -6.40
26. Mar 31, 2021 -8.82% 4.24% 144.17 9.85 -37.68
27. Apr 30, 2021 -2.17% 5.24% 28.68 17.11 -22.15
28. May 31, 2021 9.75% 0.55% 43.06 0.31 -3.66
29. Jun 30, 2021 14.91% 2.22% 137.40 1.24 13.08
30. Jul 31, 2021 6.09% 2.27% 8.43 1.37 3.39
31. Aug 31, 2021 6.42% 2.90% 10.47 3.22 5.80
32. Sep 30, 2021 -10.55% -4.76% 188.62 34.37 80.52
33. Oct 31, 2021 10.26% 6.91% 50.04 33.74 41.09
34. Nov 30, 2021 -2.30% -0.83% 30.16 3.76 10.65
35. Dec 31, 2021 11.36% 4.36% 66.81 10.60 26.61
36. Jan 31, 2022 -11.16% -5.26% 205.89 40.51 91.32
37. Feb 28, 2022 2.26% -3.14% 0.86 17.99 3.94
38. Mar 31, 2022 14.57% 3.58% 129.58 6.11 28.13
39. Apr 30, 2022 2.01% -8.80% 1.38 98.04 11.65
40. May 31, 2022 7.63% 0.01% 19.74 1.21 -4.89
41. Jun 30, 2022 3.44% -8.39% 0.06 90.21 -2.42
42. Jul 31, 2022 1.68% 9.11% 2.26 64.09 -12.04
43. Aug 31, 2022 -8.34% -4.24% 132.77 28.62 61.64
44. Sep 30, 2022 7.34% -9.34% 17.27 109.11 -43.41
45. Oct 31, 2022 11.98% 7.99% 77.32 47.34 60.50
46. Nov 30, 2022 2.75% 5.38% 0.19 18.23 -1.85
47. Dec 31, 2022 -1.41% -5.90% 21.16 49.04 32.21
48. Jan 31, 2023 -5.93% 6.18% 83.11 25.70 -46.22
49. Feb 28, 2023 -9.24% -2.61% 154.45 13.82 46.20
50. Mar 31, 2023 10.35% 3.51% 51.25 5.76 17.18
51. Apr 30, 2023 15.27% 1.46% 145.98 0.13 4.33
52. May 31, 2023 8.77% 0.25% 31.20 0.74 -4.79
53. Jun 30, 2023 9.20% 4.71% 36.18 13.02 21.70
54. Jul 31, 2023 -3.08% 4.85% 39.24 13.99 -23.43
55. Aug 31, 2023 22.17% -1.77% 360.38 8.28 -54.63
56. Sep 30, 2023 -3.08% -4.87% 39.28 35.73 37.47
57. Oct 31, 2023 3.13% -2.20% 0.00 10.92 0.20
58. Nov 30, 2023 6.90% 8.92% 13.81 61.03 29.03
59. Dec 31, 2023 -1.37% 4.42% 20.81 11.00 -15.13
Total (Σ): 4,020.05 1,634.30 491.40

Show all

VarianceLLY = Σ(RLLY,tRLLY)2 ÷ (59 – 1)
= 4,020.05 ÷ (59 – 1)
= 69.31

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

CovarianceLLY, S&P 500 = Σ(RLLY,tRLLY)×(RS&P 500,tRS&P 500) ÷ (59 – 1)
= 491.40 ÷ (59 – 1)
= 8.47


Systematic Risk (β) Estimation

Microsoft Excel
VarianceLLY 69.31
VarianceS&P 500 28.18
CovarianceLLY, S&P 500 8.47
Correlation coefficientLLY, S&P 5001 0.19
βLLY2 0.30
αLLY3 2.86%

Calculations

1 Correlation coefficientLLY, S&P 500
= CovarianceLLY, S&P 500 ÷ (Standard deviationLLY × Standard deviationS&P 500)
= 8.47 ÷ (8.33% × 5.31%)
= 0.19

2 βLLY
= CovarianceLLY, S&P 500 ÷ VarianceS&P 500
= 8.47 ÷ 28.18
= 0.30

3 αLLY
= AverageLLY – βLLY × AverageS&P 500
= 3.19%0.30 × 1.11%
= 2.86%


Expected Rate of Return

Microsoft Excel
Assumptions
Rate of return on LT Treasury Composite1 RF 4.55%
Expected rate of return on market portfolio2 E(RM) 13.62%
Systematic risk (β) of Eli Lilly & Co. common stock βLLY 0.30
 
Expected rate of return on Eli Lilly & Co. common stock3 E(RLLY) 7.28%

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(RLLY) = RF + βLLY [E(RM) – RF]
= 4.55% + 0.30 [13.62%4.55%]
= 7.28%