DCF Calculator.xlsx
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Optimize Your Retirement - DCF Calculator
Go To File - Make a Copy to create an editable copy you can use.
2
3
Input data in yellow cells only.
You can hard key over formulas here to further refine analysis.
4
AssumptionsInputYear
Cash Flow
Growth Rate
Discount Rate
Present Value
5
Year 1 Free Cash Flow
\$ 100 1 \$ 100 7%10% \$ 91
6
Initial Growth Rate7%2 107 7%10% 88
7
Terminal Growth Rate
3%3 114 7%10% 86
8
Discount Rate10%4 123 7%10% 84
9
5 131 7%10% 81
10
Terminal Value 1,887 5 1,887 3%10% 1,172
This year is the terminal year.
11
Net Present Value \$ 1,602
12
13
Instructions
14
This spreadsheet calculates the Net Present Value of estimated cash flows to determine the intrinsic value of the company. I use the numbers in millions. For instance, if a company's next year cash flows are estimated to be \$100 million, I input 100 into cell B5. Then I fill in the initial growth rate of cash flows, the terminal rate of cash flows, and the WACC or discount rate. The net present value calculates in cell B11 highlighted in cyan.
15
16
The terminal value is calculated by taking the cash flow times the terminal rate divided by the WACC minus terminal rateÂ (terminal year cash flow * 1+terminal rate) / (WACC â€“ terminal rate).
17
18
Resources
19
Personal CapitalThe first step in securing your financial future is to sign up with Personal Capital. It's a completely free tool that I use and recommend to everyone.
20
LendingTreeI believe that retirees SHOULD carry a mortgage in retirement to optimize their finances. Make banks compete for your business so that you can get the best rate.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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