Chap04_The_Value_Line_Johnson_n_Johnson

	A	B	C	D	E	F	G	H	I	J
1	Johnson & Johnson - 13 February 2024
2	Year	Enter Historical Dividends per Share	Yearly Dividend Growth Rates		Enter data in shaded cells. Unshaded cells contains are formulas. (Do not enter data in the unshaded cells. Those are formulas.)
3	2024	$4.94	5.11%			Enter Current Price:	$ 148.61	Source: Google Finance
4	2023	$4.70	5.62%			Enter Predicted Price:	$ 217.50	Source: The Value Line (midpoint of $195 & $240)
5	2022	4.45	6.21%			Enter Predicted Dividend Growth Rate:	6.00%	Source: The Value Line
6	2021	4.19	5.28%
7	2020	3.98	6.13%			Average Dividend Growth Rate:	5.71%	(average of the yearly dividend growth rates)
8	2019	3.75	5.93%					(computed from values at left)
9	2018	3.54
10
11		Gordon Growth Model = (Annual Dividends * (1+Dividend Growth Rate)) / (Required Rate of Return - Dividend Growth Rate)
12			Average Growth Rate	Predicted Growth Rate
13			5.71%	6.00%
14		Prediction:	$83.05	$87.27	12%	12% Required Rate of Return			Required rates of return must be
15			$121.78	$130.91	10%	10% Required Rate of Return			greater than dividend growth rates
16			$228.23	$261.82	8%	8% Required Rate of Return
17
18		Discounted Cash Flow Model = present value(future dividends) + present value(predicted price of stock at end of last year)
19		Years	Future Cash Flows	PVM10%	Discounted Cash Flows		PVM8%	Discounted Cash Flows
20		2024	$ 4.94	0.909	4.49046		0.926	4.57444		Using Our Average
21		2025	$ 5.22	0.826	4.31351		0.857	4.47540		Dividend Growth Rate
22		2026	$ 5.52	0.751	4.14586		0.796	4.39428
23		2027	$ 223.34	0.683	152.53834		0.735	164.15180
24				Present value (PV) at 10%:	165.48817	$165.49	PV at 8%:	177.59592	$177.60
25
26		Discounted Cash Flow Model = present value(future dividends) + present value(predicted price of stock at end of last year)
27		Years	Future Cash Flows	PVM10%	Discounted Cash Flows		PVM8%	Discounted Cash Flows
28		2024	$ 4.94	0.909	4.49046		0.926	4.57444		Using The Value Line's
29		2025	$ 5.24	0.826	4.32527		0.857	4.48759		Predicted Dividend
30		2026	$ 5.55	0.751	4.16849		0.796	4.41826		Growth Rate
31		2027	$ 223.38	0.683	152.57101		0.735	164.18696
32				Present value (PV) at 10%:	165.55523	$165.56	PV at 8%:	177.66726	$177.67
33
34		Internal Rate of Return	Average Growth Rate	Predicted Growth Rate
35			$ (148.61)	$ (148.61)	Current price of Stock (outflow)
36		2024	$ 4.94	$ 4.94	Future cash flows (inflows)
37		2025	$ 5.22	$ 5.24	Dividends and predicted
38		2026	$ 5.52	$ 5.55	price of stock at end of
39		2027	$ 223.34	$ 223.38	last year
40			13.13%	13.14%	Internal Rate of Return
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100