Discounted cash flow model
Unlevered free cash flow to a share price. Value a company from its cash flows, compare the two terminal methods, and identify which assumptions drive the result.
Built in: Investment banking, Equity research and hedge funds, Private equity
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 banking pitches and fairness opinions, a method equity analysts use to support a target price, and a cross-check a private equity team runs against the multiple it is paying. The assumptions matter more than the arithmetic, so the arithmetic needs to be correct and traceable.
Every sheet below, in your browser. Sign in free to change the inputs and watch it recalculate
Starter workbook free with an account. Worked model: one a month with Plus, every one with Pro. Downloads
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Assumptions | ||||||||
| 2 | $m unless stated. 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% | Debt / (debt + equity) at the target capital structure. | |||||
| 22 | Terminal value | ||||||||
| 23 | Terminal growth rate | % | 2.0% | Annual growth in UFCF after FY5E. Must be below WACC. | |||||
| 24 | Exit multiple, EV / FY5E EBITDA | x | 10.0x | ||||||
| 25 | Discounting convention | # | 1 | 1 = mid-year, 0 = end-year. Applies to forecast cash flows only. | |||||
| 27 | Equity bridge | ||||||||
| 28 | Debt | $m | 400.0 | At the valuation date (end of FY0A). | |||||
| 29 | Cash | $m | 150.0 | At the valuation date (end of FY0A). | |||||
| 30 | Minority interest | $m | - | ||||||
| 31 | Diluted shares outstanding | m shares | 100 | ||||||
| 32 | Share price (illustrative input) | $ per share | 34.00 | For comparison only. Not used in the valuation. | |||||
Select a cell to see its row, its schedule and the type of cell. Font colours: blue for inputs, black for formulas, green for links to other sheets. Scroll sideways to see every period.
Download
Discounted cash flow model: the workbook
Excel workbook with working formulas, no macros and no external links. Work with it above first; it is the same model as the preview.
Have your build graded
Build the starter, save it, and upload it here. It is marked the way a reviewer would mark the discounted cash flow model, on five things:
- Correct outputsyour figures against the model’s, line by line, and the headline outputs
- Correct linkageseach formula checked against the cells it reads, so a mistake counts once, where it starts
- Built with formulasno hardcoded numbers where a formula belongs
- Holds when assumptions changeyour workbook recalculated with assumptions changed, and every line checked again
- Workbook integritythe checks close, the status reads OK, and the rest of the model is as given
Checking your account…
What the base case says
- WACC
- 9.0%
- Enterprise value, perpetuity growth method (headline)
- $3,130.2m
- Enterprise value, exit multiple method
- $3,608.3m
- Implied value per share, perpetuity growth method
- 28.80
- Terminal value, % of EV, perpetuity growth method
- 74.4%
- Implied exit multiple, perpetuity growth method
- 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 (DCF) valuation of one company. It forecasts free cash flow, builds the weighted average cost of capital (WACC), calculates terminal value by two methods and bridges enterprise value to equity value and implied value per share.
The perpetuity growth method drives the headline valuation. The exit multiple method is a cross-check, and each method is restated in terms of the other as an implied exit multiple and an implied perpetuity growth rate.
Uses: learning DCF mechanics, testing which assumptions drive value, and comparing an intrinsic valuation with market multiples.
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.
- The valuation date is the end of FY0A. Enterprise value, equity value and value per share are all measured at that date.
- Free cash flow is unlevered: tax is charged on EBIT, and interest does not appear above the equity bridge.
- Discounting convention: 1 (mid-year, the default) discounts each forecast year’s cash flow from the middle of that year, at 0.5, 1.5 to 4.5 years. 0 (end-year) discounts from the end of each year. The setting applies to forecast cash flows only.
- Terminal value is measured at the end of FY5E and discounted five full years under both methods and either convention. Some practitioners discount a perpetuity growth value from mid-year; this model does not.
- The two terminal methods rest on separate assumptions and will not generally give the same value. The implied exit multiple and implied perpetuity growth show how far apart those assumptions are.
- Net working capital is a percentage of revenue, so its increase is a use of cash when revenue grows.
- The equity bridge deducts debt and minority interest and adds cash. Preferred stock, pension deficits and other claims are not modelled.
- The sensitivity grids recalculate value per share in each cell with a formula rather than an Excel data table.
Build it yourself
The starter workbook
The Valuation sheet has been cleared from the discount factors to the exit multiple terminal value. Rebuild the discounting, both terminal values, the two enterprise values and the implied multiple and growth rate. The Checks sheet shows when the sensitivity grid centre ties to the Valuation sheet.
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 uses the perpetuity growth value as the headline and restates each method in terms of the other: the perpetuity value as the exit multiple it implies, and the exit multiple as the growth it implies. The two rarely match exactly. A wide gap means the growth rate and the multiple imply different views of the business, and that difference needs explaining.
What if the terminal value is more than three quarters of the enterprise value?
The Checks sheet raises a warning, not an error. A large share is common for a growing company, which usually has 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 discounting convention an input?
Because interviewers ask about it and practice varies. Set to 1 (mid-year), forecast cash flows are discounted from the middle of each year, which raises their present value by roughly half a year of discounting. Set to 0, they are discounted from each year end. The terminal value is discounted from the end of FY5E under both methods so that they stay comparable.
What does it cost?
Nothing to preview: the whole workbook is on this page, and a free account works with it online, every formula included. Every starter workbook downloads with a free account. L3VLUP Plus ($4.99/month) keeps one worked model each billing month, yours for good, and L3VLUP Pro ($25/month) keeps every one.