Discounted cash flow model
Unlevered free cash flow to a share price. Value a company from its cash flows, reconcile the two terminal methods, and say which assumption the answer really depends on.
By Surojit Chakraverti, founder of L3VLUP and an investor running a long-short healthcare and technology equities strategy.
On this subject: discounted cash flow model.Updated 30 September 2026
Who builds it, and for whatThe intrinsic valuation in every banking pitch and fairness opinion, the method an equity analyst uses to defend a target price, and the cross-check a private equity team runs against the multiple they are paying. It is the model in which the assumptions matter more than the arithmetic, which is why the arithmetic has to be beyond argument.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | ||||||||
| 2 | Blue cells only. FY0A is the last reported year; the forecast runs FY1E to FY5E. | ||||||||
| 4 | Driver | Unit | FY0A | FY1E | FY2E | FY3E | FY4E | FY5E | |
| 5 | Operating drivers | ||||||||
| 6 | Revenue, last reported year | $m | 1,200.0 | ||||||
| 7 | Revenue growth | % | 10.0% | 9.0% | 8.0% | 6.0% | 5.0% | ||
| 8 | EBITDA margin | % | 22.0% | 23.0% | 24.0% | 24.0% | 25.0% | ||
| 9 | Depreciation and amortisation, % of revenue | % | 4.0% | ||||||
| 10 | Capital expenditure, % of revenue | % | 5.0% | ||||||
| 11 | Net working capital, % of revenue | % | 10.0% | ||||||
| 12 | Net working capital, last reported year | $m | 120.0 | ||||||
| 13 | Tax rate on operating profit | % | 25.0% | ||||||
| 15 | Cost of capital | ||||||||
| 16 | Risk-free rate | % | 4.2% | ||||||
| 17 | Equity risk premium | % | 5.5% | ||||||
| 18 | Levered beta | β | 1.15 | ||||||
| 19 | Pre-tax cost of debt | % | 6.0% | ||||||
| 20 | Target debt, % of capital | % | 25.0% | ||||||
| 22 | Terminal value | ||||||||
| 23 | Terminal growth rate | % | 2.0% | ||||||
| 24 | Exit multiple, EV / EBITDA | x | 10.0x | ||||||
| 25 | Mid-year convention (1 on, 0 off) | # | 1 | ||||||
| 27 | Equity bridge | ||||||||
| 28 | Debt | $m | 400.0 | ||||||
| 29 | Cash | $m | 150.0 | ||||||
| 30 | Minority interest | $m | - | ||||||
| 31 | Diluted shares outstanding | m | 100 | ||||||
| 32 | Current share price | $ | 34.00 |
Click any cell. The inspector names the line, the schedule it belongs to and what kind of cell it is; blue on cream is an input, black a calculation, green a value from another sheet. Scroll sideways to see every year.
Download
Discounted cash flow model: the workbook
Native Excel, formulas live, no macros, no external links. Inspect it above first; the file is the same model with the formulas in it.
A free account chooses one worked model to keep, and downloads every starter workbook. L3VLUP Pro ($25/month) opens the whole library. Signing in takes one email and no password.
What the base case says
- WACC
- 9.0%
- Enterprise value, perpetuity method
- $3,130.2m
- Enterprise value, exit multiple
- $3,608.3m
- Implied share price
- 28.80
- Terminal value share of EV
- 74.4%
- Implied exit multiple
- 8.3x
Read from the workbook as served, every input at its default. Periods: FY0A, FY1E, FY2E, FY3E, FY4E, FY5E. The figures are invented and move with whatever you type in.
What this model is
A five-year unlevered discounted cash flow valuation: operating forecast to free cash flow, a cost of capital built from its parts, terminal value by perpetuity growth and by exit multiple, and a bridge to an implied share price.
The two terminal methods are reconciled: the perpetuity value is restated as the multiple it implies, and the multiple as the growth it implies, so a reader sees at once whether the two assumptions agree.
The sensitivity grids are live formulas. Trace any cell and you will find the same cash flows re-priced at that cell’s discount rate and terminal assumption.
Seats: Investment banking, Equity research and hedge funds, Private equity.
How the schedules connect
Every row in the workbook is tagged with the schedule it belongs to; the inspector above shows the tag when you click a row. These are the schedules this model is made of and where each one lives.
Revenue build
Where the top line comes from
Assumptions, rows 6–8 · Forecast, rows 6–12
Capex and depreciation (PP&E roll-forward)
Opening, plus capex, less depreciation, closing
Assumptions, rows 9–10 · Forecast, row 9 · Forecast, row 16
Working capital schedule
Receivables, inventory and payables in days
Assumptions, rows 11–12 · Forecast, rows 17–18
Tax
Tax on profit, and tax on operating profit
Assumptions, row 13 · Forecast, row 11
Cash flow statement
Indirect method, closing on balance-sheet cash
Forecast, row 15 · Forecast, rows 19–20
Cost of capital (WACC)
CAPM, after-tax debt, target weights
Assumptions, rows 16–20 · WACC, row 5 · WACC, row 8 · WACC, rows 11–13
Discounting and terminal value
Discount factors, two terminal methods, reconciled
Assumptions, rows 23–25 · Valuation, rows 6–11 · Valuation, rows 14–19 · Valuation, rows 22–26
Enterprise value to equity value
Debt off, cash on, per share
Assumptions, rows 28–32 · Valuation, rows 29–36
Sensitivity tables
Two assumptions at once, without a data table
Sensitivity, rows 5–7 · Sensitivity, rows 10–15 · Sensitivity, rows 18–25
What you should be able to explain
- Why free cash flow is unlevered: tax on EBIT, and interest nowhere above the bridge.
- How the cost of equity is built and why the debt weight uses a target structure rather than today’s.
- What the perpetuity growth formula assumes, and why the implied exit multiple is the sanity check on it.
- Why mid-year discounting exists and what it does to the answer.
- How enterprise value becomes equity value, and what belongs in the bridge.
What a reviewer looks for
- A terminal growth rate above the economy’s nominal growth, or above the discount rate.
- Discounting the terminal value by one period too many or too few.
- Deducting interest from free cash flow and then discounting at WACC, which double-counts the cost of debt.
- A terminal value that is nearly all of the enterprise value, presented without comment.
Conventions this workbook uses
Stated on the cover sheet too. A model is only as trustworthy as the decisions it tells you it made.
- Free cash flow is unlevered: tax is charged on EBIT, and interest appears nowhere above the equity bridge.
- Discounting uses a mid-year convention by default. Cash flows are discounted from the middle of each forecast year; the terminal value is discounted from the end of the final year under both methods. Some practitioners discount the perpetuity-growth value from mid-year too, which raises it by half a year of discounting; the switch here keeps the two methods on the same footing.
- Net working capital is a percentage of revenue, so its increase is a use of cash in a growing company.
- The equity bridge subtracts debt and minority interest and adds cash. Preferred stock and pension deficits would sit on the same lines and are left at zero.
- The perpetuity method is the headline; the exit-multiple value is the cross-check, not the other way round.
Build it yourself
The starter workbook
The Valuation sheet has been cleared from the discount factors to the exit-multiple terminal value. Build the discounting, both terminal values and the reconciliation lines so that the equity bridge and the sensitivity grid come back to life. The Checks sheet tells you when the grid centre ties.
Blanks: Discounting and terminal value. Free with any account. Compare with the worked model when you are done: download above.
The path around this model
Understand it, drill it, read the build, then apply it to a real company.
Understand · Primer
DCF Valuation
Intermediate · a curated reel with a quiz
Build · Lab · ~15 min
DCF Builder
Build a DCF by hand from revenue to a share price, know how much of it sits in the terminal value, and name the one assumption the answer hinges on.
Build · Lab · ~12 min
WACC Builder
Build a discount rate you can defend line by line: beta, equity risk premium and cost of debt, each from a source you can name.
Build · Lab · ~10 min
Reverse DCF
Read off the growth a share price already assumes, and say whether that is plausible before anyone asks.
Build · Lab · ~10 min
Beta Refresher
Estimate a beta, see how far one regression lands from the truth, and know when to use raw, adjusted, unlevered or bottom-up.
Build · Lab · ~12 min
EV to Equity Bridge
Walk from enterprise value to a price per share without dropping a claim: net debt, pensions, minorities, leases and options.
Read · Guide · 12 min
How to Build a DCF Model: Build Order, Terminal Value and the Sanity Checks
Read · Guide · 8 min
DCF Interview Questions: The Ones That Actually Come Up
Read · Guide · 7 min
Enterprise Value vs. Equity Value, Once and For All
Apply · Skill
Discounted Cash Flow
Value the business on its own projections, and show what the answer depends on.
Apply · Skill
Weighted Average Cost of Capital
Build the discount rate from peers rather than choosing it.
Apply · Skill
Sensitivity Analysis
Show how the value moves with the two assumptions that carry it.
Apply · Skill
Model Audit
Find the errors in a financial model before someone senior does.
Vocabulary: DCF, WACC, Terminal Value, Equity Value, Net Debt.
Questions about this model
Why does the sensitivity grid not use a data table?
Because every cell can be written in closed form: the present value of the same five cash flows at that row’s discount rate, plus a terminal value at that column’s growth or multiple. A data table hides the arithmetic; a formula shows it, and it recalculates without the What-If engine.
Which terminal method should I trust?
Neither on its own. The model restates each as the other: the perpetuity value as the multiple it implies, and the multiple as the growth it implies. If the two disagree badly, one of the assumptions is wrong for this business, and that is the conversation to have.
What if the terminal value is more than three quarters of the enterprise value?
The Checks sheet flags it. It is not wrong in itself: a young, growing company will always have most of its value beyond year five. It means the answer rests on the terminal assumptions, so those are the ones to defend.
Why is the mid-year convention a switch?
Because interviewers ask about it and analysts disagree about it. With the switch on, cash flows are discounted from the middle of each year, which raises the value by roughly half a year of discounting. The terminal value is discounted from the end of the final year under both methods so that they stay comparable.
What does it cost?
Nothing to inspect: the whole workbook is on this page. A free account chooses one worked model to keep, and downloads every starter workbook. L3VLUP Pro ($25/month) downloads the whole library and opens every model’s formulas in the inspector.