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

 
View only
 
 
Still loading...
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Optimize Your Retirement - DCF Calculator
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
OptionHouseI trade stocks and options with OptionsHouse.
20
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.
21
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.
22
23
For more information, you can email me at matt@optimizeyourretirement.com
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
Loading...
 
 
 
DCF Calculator