How-to17. Nov. 2026 · 9 Min Lesezeit

DCF Model Excel Template: How a Live 5-Year DCF Works

What a working DCF model in Excel contains, sheet by sheet: unlevered FCF, discounting, terminal value and a WACC × growth grid, with a worked example.

PT
PickSkill TeamThe PickSkill research team — building an AI analyst for retail investors.
Diagram of a five-sheet DCF workbook: Assumptions feeding Model, Valuation and a WACC by terminal-growth sensitivity grid, with the Summary sheet on top

A DCF model Excel template is a workbook that turns a handful of assumptions (revenue growth, margins, reinvestment, WACC and terminal growth) into an intrinsic value per share, using formulas rather than typed-in numbers. The test of a good one is simple: change any input and every projection, the valuation and the sensitivity table recalculate. This guide walks through the five sheets such a model needs, the exact formulas behind each, and a worked example you can check by hand.

Key takeaways

  • A usable DCF template has five parts: Summary, Assumptions, Model (projections), Valuation and Sensitivity. Inputs live in one place; everything else is a formula.
  • The cash flow you discount is unlevered free cash flow: EBIT × (1 − tax) + D&A − capex − increase in net working capital.
  • Terminal value usually dominates. In the worked example below it is 76% of enterprise value, which is why a WACC × terminal-growth grid matters more than any single output.
  • The most common template bugs are hard-coded numbers inside projections, terminal growth set at or above WACC, and net debt subtracted twice or not at all.

What sheets should a DCF Excel template have?

A DCF workbook works best split into five sheets, each with one job:

SheetWhat it holdsRule
AssumptionsBase-year revenue, five years of growth and EBIT margin, tax rate, D&A, capex, working capital, WACC, terminal growth, net debt, diluted shares, current priceThe only sheet with typed numbers. Mark inputs visually (the sell-side convention is blue text on a pale-yellow fill)
ModelFive projected years: revenue, EBIT, NOPAT, D&A, capex, ΔNWC, unlevered FCF, discount factor, present valueEvery cell is a formula pointing at Assumptions or the row above
ValuationSum of PV of FCF, terminal value, PV of terminal value, enterprise value, net debt, equity value, value per share, upside vs priceNo inputs; reads Model and Assumptions
SensitivityValue per share across a grid of WACC and terminal growthEach cell re-runs the full DCF at that pair
SummaryValue per share, upside, the key assumptions and a few lines on why you chose themWhat you show someone else

Keeping inputs on one sheet is what makes the model honest: anyone can see every assumption that drives the answer, and nothing is buried in a projection cell.

What formulas does each year of the model use?

Each projected year runs the same chain. With revenue growth g, EBIT margin m and tax rate t:

  1. Revenue = prior-year revenue × (1 + g)
  2. EBIT = revenue × m
  3. NOPAT = EBIT × (1 − t)
  4. D&A = revenue × D&A %
  5. Capex = revenue × capex %
  6. ΔNWC = (revenue − prior-year revenue) × NWC % of the change
  7. Unlevered FCF = NOPAT + D&A − capex − ΔNWC
  8. Discount factor = 1 / (1 + WACC)^n, where n is the year number (1 to 5)
  9. PV of FCF = unlevered FCF × discount factor

Then, on the Valuation sheet:

  • Terminal value (Gordon growth) = FCF in year 5 × (1 + terminal growth) / (WACC − terminal growth)
  • Enterprise value = sum of PV of FCF + terminal value × year-5 discount factor
  • Equity value = enterprise value − net debt (add net cash if net debt is negative)
  • Value per share = equity value / diluted shares

This is end-of-year discounting. Some analysts use a mid-year convention, which raises the value slightly; whichever you choose, say so on the Summary sheet. If you need a refresher on the inputs themselves, see what DCF is and how WACC is built.

What does a worked DCF example look like?

Here is an illustrative company, not a real one, so you can check the arithmetic: base-year revenue 1,000 (in millions), 10% growth for five years, 20% EBIT margin, 21% tax, D&A 4% and capex 5% of revenue, ΔNWC 10% of the revenue increase, WACC 9%, terminal growth 2.5%, net debt 200, 100 million diluted shares, current price 25.

YearRevenueEBITNOPATD&ACapexΔNWCUnlevered FCFPV of FCF
20261,100.0220.0173.844.055.010.0152.8140.2
20271,210.0242.0191.248.460.511.0168.1141.5
20281,331.0266.2210.353.266.512.1184.9142.8
20291,464.1292.8231.358.673.213.3203.4144.1
20301,610.5322.1254.564.480.514.6223.7145.4

Valuation:

  • Sum of PV of FCF: 713.9
  • Terminal value: 223.7 × 1.025 / (0.09 − 0.025) = 3,527.8, worth 2,292.8 today
  • Enterprise value: 3,006.7; equity value after net debt: 2,806.7
  • Value per share: 28.07, or 12.3% above the 25 share price

Notice that the terminal value supplies 76% of enterprise value. Five years of explicit forecasting decide only a quarter of the answer; the long-run assumptions decide the rest.

How do you read the sensitivity table?

Because terminal value dominates, a single value per share is less useful than the range around it. The Sensitivity sheet re-runs the whole DCF for each pair of WACC (rows) and terminal growth (columns):

WACC ↓ / g →2.00%2.25%2.50%2.75%3.00%
8.0%31.2232.4233.7235.1436.71
8.5%28.5929.5830.6631.8333.10
9.0%26.3327.1628.0729.0430.10
9.5%24.3725.0825.8526.6727.56
10.0%22.6623.2723.9324.6325.38

A one-point move in WACC swings the value from about 24 to 34 per share at the same growth rate. If the share price (25) sits inside the plausible part of the grid, the DCF is not telling you the stock is mispriced; it is telling you which assumption you would have to be confident about. That is the honest way to present a DCF.

What mistakes break DCF templates?

  1. Hard-coded numbers inside the Model sheet. One typed-over cell silently breaks the link to Assumptions, and the sensitivity table stops meaning anything. Keep every number in Assumptions.
  2. Terminal growth at or above WACC. The Gordon formula divides by (WACC − g); as they converge, value explodes. Long-run growth above nominal GDP growth (roughly 2–4%) needs a reason.
  3. Mixing levered and unlevered. Unlevered FCF is discounted at WACC to get enterprise value, then net debt is subtracted. Discounting levered FCF at WACC, or forgetting net debt, double-counts or drops the debt.
  4. Basic instead of diluted shares. Use diluted shares, in the same unit as the money inputs (millions with millions).
  5. Margins that never converge. If EBIT margin rises every year to the end of the forecast, the terminal value capitalises that peak forever. Let margins settle before year 5.

How do you get this template for a real company?

You can build the workbook above from scratch in about an hour once the formulas are clear. PickSkill's AI analyst can also generate it for you: ask in plain language, for example "Build a 5-year DCF for Apple in Excel, WACC 8.5%, terminal growth 2.5%", and it pulls the base-year figures from the latest filings and returns an .xlsx with the five sheets described here. Every projected figure is a live formula tied to one input cell, so you can overwrite any assumption and the valuation and sensitivity grid recompute.

Treat the AI's starting assumptions as a first draft, not a forecast: the point of the template is that you can see and change every one of them. For a walkthrough of the workflow, read build a DCF in 60 seconds; to sanity-check the answer against peers, see DCF vs comparable company analysis. Generated files count toward your plan's monthly file quota; see pricing.

FAQ

Is there a free DCF model template in Excel? Yes. You can build one from the formulas on this page in a blank workbook, and PickSkill's free plan includes one generated file a month, enough to try a company-specific DCF workbook; Pro includes 30. Whatever template you use, check that the projections are formulas tied to one assumptions sheet.

What discount rate should a DCF use? Unlevered free cash flow is discounted at the weighted average cost of capital (WACC): the after-tax cost of debt and the cost of equity, weighted by their share of capital. For large, stable companies WACC often lands between about 7% and 10%; the sensitivity grid shows how much the answer depends on it.

Why is terminal value such a large part of a DCF? Because it captures every year after the explicit forecast. With a 9% WACC and 2.5% terminal growth, the example on this page gets 76% of its enterprise value from the terminal value. That is normal, and it is why a DCF should be presented as a range, not a single price target.

How many years should a DCF forecast? Five years is the common default and what this template uses. Use more (seven to ten) when a company is still far from a steady state, for example a fast grower whose margins have not converged; the explicit forecast should end once growth and margins have settled.

Can I use this template for Hong Kong or A-share companies? Yes. The method is the same; set the currency and unit on the Assumptions sheet (for example HKD millions or 亿元) and keep shares in the same scale. PickSkill covers US, Hong Kong and A-share tickers.

Soll PickSkill das für Sie bauen?

Chat öffnen und beauftragen. Die KI holt sich Echtzeitdaten, rechnet durch und liefert das Ergebnis als herunterladbares PPT, Word oder Excel.

Kostenlosen Chat ausprobieren
dcfexcelvaluationtemplatewacc