Discounted cash flow (DCF) models are the backbone of corporate valuation and capital budgeting. Yet many models are either overcomplicated academic exercises or fragile spreadsheets full of hard-coded numbers. This article gives a practical, repeatable process you can implement in Excel or Google Sheets to produce a defendable valuation in 60–120 minutes.
You’ll get concrete formulas, benchmark inputs, common pitfalls and a worked example that walks from historical financials to a sensitivity table around terminal value and discount rate. No fluff — just the steps professional analysts use to make rapid, reliable DCFs.
What inputs you must gather before you start
Collect three to five years of historical income statements, balance sheets and cash flow statements and a recent quarter if available.
Identify one-off items and normalize operating profit (remove restructuring, asset sales, non-recurring tax credits).
Get management guidance and analyst consensus for revenue and margins if available; if not, use historical compound annual growth rates (CAGR) and industry benchmarks.
- Effective tax rate: use cash taxes paid / pre-tax income averaged over last 3 years as a proxy.
- Net working capital drivers: days sales outstanding (DSO), days inventory outstanding (DIO), days payable outstanding (DPO). Calculate as averages and translate to % of revenue for forecasts.
- Capital expenditure (CapEx) and depreciation: compute CapEx / revenue and depreciation / revenue averages to guide future assumptions.
Free cash flow formula and forecast mechanics
We use Free Cash Flow to the Firm (FCFF): FCFF = EBIT*(1 - tax rate) + Depreciation - Change in Net Working Capital - CapEx.
Step 1: Forecast revenue for 5 years. Use either CAGR, a step-down growth profile (e.g., high growth first 2 years then converge to long-term rate), or build bottoms-up by product/channel.
Step 2: Forecast operating margin (EBIT or EBIT margin). Apply historical margin trends adjusted for known cost drivers.
Step 3: Forecast depreciation and CapEx. Use Depreciation = historical depreciation/revenue * revenue. CapEx can be modelled as CapEx = target CapEx/rev * revenue or as replacement rate = depreciation + incremental growth capex.
Step 4: Model working capital: NWC = (Receivables + Inventory - Payables). Forecast as % of revenue using the DSO/DIO/DPO-derived percentages. Change in NWC = NWC_this_year - NWC_prior_year.
Step 5: Compute FCFF each forecast year and check signs and magnitude. For fast-growing companies FCFF may be negative early — that’s acceptable if explained by heavy CapEx or working capital absorption.
How to calculate WACC correctly
WACC = (E / (E + D)) * Re + (D / (E + D)) * Rd * (1 - Tc). Use market values for equity (market cap) and debt (book is OK if market not available; adjust if interest rates have shifted).
Cost of equity (Re) via CAPM: Re = Rf + Beta * Equity Risk Premium. Use a 10-year government bond yield for Rf, an unlevered beta adjusted for industry and then relevered: Beta_levered = Beta_unlevered * (1 + (1 - Tc) * D/E).
Cost of debt (Rd): use the company’s yield to maturity on debt or recent average interest rate on new debt. If unknown, proxy with credit spread over Rf using credit rating benchmarks.
- Common mistakes: mixing book and market values for capital structure; using historical interest expense / average debt as Rd without checking current market spreads; ignoring tax shield when computing after-tax cost of debt.
Terminal value: choosing between exit multiple and Gordon growth
Two common approaches: terminal multiple (EV/EBITDA, EV/EBIT) or perpetuity growth (Terminal Value = FCFF_last * (1 + g) / (WACC - g)).
Use multiples when there are reliable peer comparables and the business will stabilize with similar scale/profitability; use Gordon growth when the company approaches steady-state with predictable cash flow.
Reasonable terminal growth rate (g) benchmarks: nominal GDP growth or inflation + long-term real GDP (typically 2–3% for developed markets). Avoid using g > WACC - 1% and never greater than expected long-term nominal GDP.
- Exit multiple sanity check: derive implied multiple from your perpetuity g and WACC: Implied EV/EBITDA = (FCFF * (1 + g) / (WACC - g)) / EBITDA. If implied multiple is outside industry 25th–75th percentile, re-evaluate assumptions.
Worked example: 5-year DCF for a mid-size manufacturer
Assumptions: revenue grows from $200m with 6% CAGR to $267.6m in year 5; EBIT margin improves from 10% to 12% by year 3 and holds; tax rate 25%; Depreciation = 3% of revenue; CapEx = 4% of revenue; NWC = 10% of revenue constant (so ΔNWC = growth * 10%).
Year 5 EBIT = $267.6m * 12% = $32.11m. FCFF year 5 = EBIT*(1 - 0.25) + Depreciation - ΔNWC - CapEx. Compute components: after-tax EBIT = $24.08m; Depreciation = $8.03m; CapEx = $10.70m; ΔNWC = (year5 rev - year4 rev)*10% (calculate each year).
Terminal value (Gordon): choose g = 2.5%, WACC = 8.5% (derived from market cap $800m, debt $200m, Re 10%, Rd 4.5%). TV = FCFF5*(1+0.025)/(0.085-0.025) = FCFF5*17.083. Discount TV and FCFFs to present value and sum to get enterprise value.
Check: implied EV/EBITDA = EV / EBITDA (EBITDA = EBIT + Depreciation). If EV/EBITDA is outside industry range, adjust WACC or terminal assumptions and re-run sensitivities.
How to build sensitivity tables and what ranges to test
Create a 2-way sensitivity table with WACC on one axis (±200–500 bps around base) and terminal growth or terminal multiple on the other axis. Typical WACC test range: base ± 2 percentage points; terminal growth: 0% to 4% or multiples ± 20–50%.
Include a valuation bridge showing base-case EV to alternative cases (e.g., low/medium/high revenue growth scenarios) and list the drivers that move value most (margin expansion, CapEx intensity, NWC).
- Common checks: if small changes in WACC or g swing value dramatically (>30–40%), highlight key value drivers to management and include scenario narratives explaining the risk implications.
Common modeling errors and practical checks before you present
Hard-coding: avoid hard-coded links to inputs inside formulas. Use a single 'assumptions' block and reference cells. This prevents invisible breaks when updating models.
Circular references: if you model interest that depends on debt level and debt depends on cash flow, either break the circle (use beginning-of-year debt for interest) or enable iterative calculation deliberately and document it.
Sign errors: ensure cash flow signs are consistent (CapEx as negative outflow, depreciation added back). A quick check: reconcile the model’s cumulative free cash flow over the forecast to the change in cash on the balance sheet (modulo financing activities).
- Audit steps: (1) check formula consistency by scanning for numbers embedded in formulas, (2) run a reasonability test comparing model EBITDA margins and growth to peers, (3) stress test a 10% adverse shock to revenue and inspect liquidity and covenant impacts.
When to use DCF and when to use alternatives
Use DCF when cash flow drivers are predictable and capital structure is stable — typical for mature firms or well-understood projects.
Use relative valuation (multiples) as a cross-check or when you need a quick, market-based sanity test — multiples are market-sensitive and reflect prevailing investor sentiment but can be volatile.
For early-stage or highly uncertain businesses, complement DCF with scenario analysis, option pricing for embedded optionality, or a probability-weighted expected return across discrete scenarios.
Key takeaways
- Gather accurate historical financials and normalize one-offs before forecasting.
- Build FCFF step-by-step: revenue → EBIT → depreciation/capex → ΔNWC → FCFF.
- Calculate WACC using market values; derive cost of equity via CAPM and use realistic credit spreads for cost of debt.
- Choose terminal method carefully and sanity-check implied multiples versus peers.
- Always produce sensitivity tables and stress tests; document assumptions and avoid hard-coded numbers.
- Perform model audits: reconcile to balance sheet movements and test scenario impacts before presenting the valuation.