DCF (Discounted Cash Flow)

Key takeaways

DCF says a business is worth the cash it can return to its capital providers, discounted to today.
The two big levers are WACC (the discount rate) and the terminal value (typically 50–70% of total enterprise value).
Use mid-year discounting (periods 0.5, 1.5, 2.5...) — it's the IB standard and it materially changes the answer.
An institutional DCF spans 14 linked sheets, not one tab. Skipping the supporting schedules is the difference between a model and a sketch.
Sensitise via three 5×5 tables (75 full-recalculation formulas), not linear approximations. The center cell of each table must equal your base-case output — that's the sanity check.

The math

A DCF computes enterprise value as the sum of present-valued free cash flows over an explicit forecast horizon plus the present-valued terminal value capturing everything after.

EV = Σ_t=1ⁿ [ FCF_t / (1 + WACC)^{t − 0.5} ] + TV_n / (1 + WACC)^{n − 0.5}

Where:

FCF_t — unlevered free cash flow in year t: EBIT × (1 − tax) + D&A − CapEx − ΔNWC
WACC — weighted-average cost of capital (blended cost of debt and equity at market-value weights)
n — explicit forecast horizon, typically 5–10 years
TV_n — terminal value at year n (Gordon growth or exit-multiple method)
The − 0.5 exponent reflects the mid-year convention — see below

To bridge enterprise value to equity value: subtract net debt, add cash and equivalents, adjust for minority interests and preferred stock. Divide by diluted shares (basic shares overstate per-share value) to get implied price per share.

Mid-year convention — the IB standard most online primers skip

Year-end discounting (periods 1, 2, 3, 4, 5) implicitly assumes every dollar of free cash flow arrives on December 31. In reality, cash flows arrive evenly through the year. The mid-year convention models this:

Discount factor_t = 1 / (1 + WACC)^{t − 0.5}

For a 5-year model, the discount periods become 0.5, 1.5, 2.5, 3.5, 4.5 — and the terminal value (struck at year 5) is also discounted at period 4.5, not 5. Switching from year-end to mid-year typically increases enterprise value by 4–6% at a 10% WACC. Material enough that every banker uses it.

WACC, walked through

The discount rate is the weighted-average return required by the capital providers — debt and equity holders — given the risk of receiving the projected cash flows.

WACC = (E / V) × R_e + (D / V) × R_d × (1 − t)

Cost of equity (CAPM)

R_e = R_f + β × (R_m − R_f)

The risk-free rate is typically the 10-year government bond yield in the company's reporting currency. Beta is regressed against a broad market index over 2–5 years (Bloomberg, Damodaran's databases). The equity risk premium is contested — Damodaran's implied ERP is the most defensible reference, typically 5.0–6.0% in developed markets.

Cost of debt

Pre-tax cost of debt is the YTM on the company's outstanding bonds, or for private companies, a synthetic spread over the risk-free rate based on credit metrics. Multiply by (1 − t) for the after-tax rate, since interest is tax-deductible.

Use market-value weights, not book

Equity weight is market cap, not book equity. Debt weight is the market value of debt — book is an acceptable proxy for non-traded debt. Mixing book and market produces a wrong WACC and a wrong valuation. Typical ranges: large-cap stable companies 7–9%, growth 9–12%, high-risk 12–15%. If your computed WACC is outside 6–14%, double-check before discounting.

The terminal value problem

Terminal value typically accounts for 50–70% of total enterprise value in a DCF. Get it wrong and the rest of the model is a distraction. Two methods, both worth computing:

Gordon growth (perpetual growth)

TV_n = FCF_n+1 / (WACC − g)

Implies the business grows at g forever. The hard constraint: g cannot exceed the long-run growth rate of the economy in the reporting currency. For developed markets, that ceiling is ~2.0–2.5% nominal. Anything above 3% implies the business eventually consumes the entire economy — implausible.

Exit multiple

TV_n = EBITDA_n × Exit multiple

Implies the business sells at year n at a market multiple of EBITDA. The multiple should match what comparable mature businesses currently trade at — by year n, your target should have matured. Cross-check the implied perpetual growth of your exit multiple against Gordon growth: if they disagree by more than ~1%, one of them is wrong.

The 14 sheets of an institutional DCF

A real DCF lives across 14 linked sheets, not one tab. The supporting schedules drive the projection, the projection drives the valuation, and the sensitivity stress-tests the answer. Skipping any of these turns the model into a sketch.

#	Sheet	What it does
1	Assumptions	All driver inputs in one place: growth rates, margins, capex, working-capital days, WACC inputs
2	Revenue Schedule	Top-line build, possibly segment-by-segment
3	COGS Schedule	Margin path, possibly unit-cost driven
4	OpEx Schedule	S&M, R&D, G&A — each as % of Revenue, never gross profit
5	Working Capital	DSO × Revenue / 365, DIO × COGS / 365, DPO × COGS / 365
6	CAPEX & Depreciation	Capex as % of revenue; D&A as % of prior-year PP&E (preferred) or revenue
7	Debt Schedule	Tranches, beginning/ending balances, interest computed on beginning balance
8	Tax Schedule	Effective rate; `=MAX(0, EBT × rate)` so loss years pay no tax
9	Income Statement	Pulled from the schedules — never hardcoded
10	Balance Sheet	Must satisfy `Assets = Liabilities + Equity` exactly every period
11	Cash Flow Statement	Ending cash must tie to balance sheet cash exactly
12	WACC	CAPM build, market-value weights, blended rate
13	DCF Valuation	UFCF build, mid-year discounting, terminal value, equity bridge, share price
14	Sensitivity	Three 5×5 tables — see below

Scenario framework — Bear / Base / Bull

Single-point projections lie about confidence. Institutional models carry three coherent scenarios — bear, base, bull — and let the user toggle between them with a case selector cell:

Build three assumption blocks (one per scenario), each with all drivers projected horizontally across years.
Add a consolidation row with =INDEX(scenario_block, MATCH(case_selector, ...)) formulas that pull from whichever block is active.
Every projection formula reads from the consolidation row, not from a specific scenario block. Clean, auditable, one place to change the active case.
Case selector cell: 1 = Bear, 2 = Base, 3 = Bull.

Sensitivity — three 5×5 tables, 75 full-recalc formulas

A DCF without sensitivity is a single number with false precision. The institutional standard is three 5×5 tables, each cell containing a full DCF recalculation for that variable combination — not a linear approximation.

Table	Axes	Output
1	WACC × Terminal growth rate	Implied share price
2	WACC × Exit EV/EBITDA multiple	Implied share price
3	Revenue growth × EBITDA margin	Implied EV

Construction rules:

Odd dimensions (5×5) — guarantees a true center cell.
Symmetric axes around the base case: [base − 2×step, base − step, base, base + step, base + 2×step].
Center cell sanity check — the center cell value must equal the model's base-case implied share price. If it doesn't, the table is wrong.
Center cell formatted with medium-blue fill (#BDD7EE) plus bold font.
No approximations. Every cell must be a full DCF recompute, not = base × (1 + ΔWACC).

Worked example (with mid-year discounting)

A company projecting $200M of UFCF in year 5, growing through the projection. WACC of 10%, terminal growth of 2.5%, mid-year convention.

Year	FCF ($M)	Period (mid-year)	Discount factor	PV of FCF ($M)
1	120	0.5	0.953	114
2	140	1.5	0.867	121
3	160	2.5	0.788	126
4	180	3.5	0.716	129
5	200	4.5	0.651	130

Sum of PV(FCF) = $620M. Terminal value at year 5 (Gordon, g = 2.5%): TV = 200 × 1.025 / (0.10 − 0.025) = $2,733M. Discounted at period 4.5: 2,733 × 0.651 = $1,779M.

Enterprise value = $620M + $1,779M = $2,399M. 74% of EV is in the terminal value — typical. Subtract net debt of $400M; equity value is $1,999M. Across 100M diluted shares, implied price is $19.99 per share. Compared to a current market price of $16, that's an implied 25% upside.

When DCF works, when it doesn't

Situation	DCF reliability
Mature business, stable cash flows	Strong — DCF is the canonical method here
Cyclicals at top of cycle	Weak — projecting peak FCF forever overvalues the business; pair with mid-cycle EV/EBITDA
Cyclicals at bottom of cycle	Variable — depends on when the cycle turns; use mid-cycle margins
Early-stage, pre-revenue	Poor — FCF is years away, terminal dominates, too much WACC sensitivity. Use VC scorecard or real options.
Banks and insurance	Use dividend discount or excess returns models — FCFF DCF doesn't apply
Highly distressed	Use APV (adjusted present value), separating tax shields from distress costs

The most common DCF errors

Section-header off-by-one — the most common silent bug. If =Assumptions!C24 points to the "WACC INPUTS" header label instead of the actual rate one row below, the WACC computes to zero, every discount factor breaks, and the implied share price comes out as $0. Always verify formulas trace to data rows, not header rows.
OpEx as % of gross profit, not revenue. S&M, R&D, and G&A scale with revenue, not gross profit. S&M = Revenue × 15% is right; S&M = Gross Profit × 15% is the textbook mistake.
Year-end instead of mid-year discounting. Periods 1, 2, 3, 4, 5 instead of 0.5, 1.5, 2.5, 3.5, 4.5. Costs ~5% of EV for no good reason.
Basic shares instead of diluted. Diluted includes in-the-money options, RSUs, convertibles. Basic overstates per-share value.
Net cash mishandled. If cash > debt, net debt is negative — adds to equity value. Many models get the sign wrong on net-cash names like Apple.
Linear approximations in sensitivity tables. =base × (1 + ΔWACC) instead of a full recompute. The relationship is not linear.
Terminal growth above GDP. 4% perpetual growth implies the company eventually outgrows the economy. Cap at 2.5–3%.
Hardcoded values that should be formulas. If you change a driver and nothing recalculates, the model is decorative.
No source comments on inputs. Every hardcoded value should carry a cell comment: Source: [system], [date], [reference]. Without it, the model isn't audit-ready.

How Smalt AI builds it

Tell your AI coworker the company and the horizon — "build a DCF for ABC Corp, 2031 horizon, US WACC, three scenarios." What you get back:

All 14 sheets generated in one workbook, with consistent cross-sheet naming (='Revenue Schedule'!B4, ='Debt Schedule'!C12, and so on). Sheets are dependency-ordered so they build cleanly.
A row registry tracked while building — section headers and data rows are accounted for so cross-sheet references never point at a label cell. (The off-by-one bug is the most common DCF failure mode; we eliminate it at construction time.)
Bear / Base / Bull scenario blocks wired through an INDEX consolidation row and a single case-selector cell.
Mid-year discounting by default — periods 0.5 through 4.5 — across the FCF and terminal value.
The three 5×5 sensitivity tables (75 cells, every one a full DCF recompute, center cell highlighted and equal to the base case).
Quality checks auto-run before delivery: balance sheet balances exactly each period, cash flow ties to balance-sheet cash, terminal value is 50–70% of EV (flagged otherwise), WACC is 6–14% (flagged otherwise).
Source comments on every hardcoded input — date, source system, page reference.
Institutional formatting: dark-blue headers, blue-font hardcoded inputs, green-font cross-sheet links, single/double underlines for subtotals/totals.