How to Build a DCF Model in Excel: A Step-by-Step Guide

A discounted cash flow model answers one question: what is the business worth today, given the cash it will generate in the future. Everything else, the tabs, the formatting, the scenario toggles, is in service of that one number. This guide walks through building a full DCF in Excel with a worked example using round numbers, so you can follow the arithmetic by hand and then rebuild it in your own workbook.

Step 1: Gather your inputs from the 10-K

Before opening Excel, pull the following from the company's most recent 10-K (or 10-Q for the trailing quarter):

Management's Discussion and Analysis (Item 7) is where you find the qualitative color: why margins moved, what capex is funding, what management expects for the next year. Use it to sanity-check the numbers you project, not as a substitute for them.

Step 2: Project free cash flow (a worked 5-year example)

Free cash flow to the firm is NOPAT + D&A − Capex − Increase in net working capital. Here is a full five-year projection using clean, round inputs so the mechanics are easy to follow.

Assumptions: revenue starts at $1,000 and grows 10% a year, operating margin holds at 20%, the tax rate is 25%, D&A runs at 5% of revenue, capex runs at 6% of revenue, and working capital equals 10% of revenue (so the cash tied up each year is 10% of the year-over-year revenue increase).

In Excel, build this as five rows (revenue, EBIT, tax, NOPAT, D&A, capex, change in NWC, FCF) across five year columns, with every line as a formula off the assumptions cell, not a typed number. That is what lets the sensitivity table in Step 7 actually work.

Step 3: Calculate WACC

The weighted average cost of capital is the discount rate. It blends the cost of equity and the after-tax cost of debt, weighted by their share of the capital structure.

Cost of equity, using CAPM: Cost of equity = Risk-free rate + Beta × Equity risk premium

With a risk-free rate of 4.0% (the 10-year Treasury yield), a beta of 1.2, and an equity risk premium of 5.5%:

After-tax cost of debt: Pre-tax cost of debt × (1 − Tax rate)

With a pre-tax cost of debt of 5.0% and the same 25% tax rate:

Weights: use market values, not book values. Here, equity is 80% of the capital structure and debt is 20%.

In Excel, build each input as its own labeled cell (risk-free rate, beta, ERP, pre-tax cost of debt, tax rate, weights) and let WACC be a formula that references them. You will want to flex beta and the risk-free rate later without hunting for where they live.

Step 4: Terminal value, two ways

The terminal value captures everything after the explicit forecast period, and it is usually the majority of enterprise value, so build it two ways and compare.

Gordon growth method: TV = FCF_final × (1 + g) / (WACC − g)

Using Year 5 FCF of $211, a long-run growth rate of 2.5%, and WACC of 9.2%:

Exit multiple method: TV = Final-year EBITDA × Exit multiple

Year 5 EBITDA is EBIT plus D&A: 322 + 81 = $403. At a 10x EV/EBITDA exit multiple:

Run both. If they imply wildly different growth rates or multiples once you back into them, that is a signal one of your assumptions is off, not a reason to pick whichever number you like better.

Step 5: Discount back to present value (mid-year convention)

Standard end-of-year discounting assumes every dollar of cash flow arrives on December 31, which understates value since real businesses generate cash all year round. The mid-year convention corrects for this by discounting each cash flow at t − 0.5 instead of t.

Discount factor: 1 / (1 + WACC)^(t − 0.5). At a 9.2% WACC:

Sum of PV of explicit cash flows: $701 (rounded).

Apply the same Year 5 factor (0.673) to the terminal value:

Enterprise value:

Step 6: Bridge from enterprise value to per-share value

Enterprise value belongs to everyone with a claim on the business, debt and equity alike. To get to what a share is worth, subtract net debt.

Say the company carries $400 of total debt and $150 of cash, for net debt of $250, and has 100 million diluted shares outstanding.

That $26 to $32 spread between the two terminal value methods is normal. It is the model telling you where its uncertainty lives, and it is exactly what the sensitivity table in the next step is for.

Step 7: Build a sensitivity table with formulas, not Excel's Data Table

Excel's built-in Data Table feature works, but it is slow to recalculate, opaque to anyone else opening the file, and easy to break by inserting a row. A formula-driven grid is faster and auditable. Five steps:

  1. Lay out WACC values across a row (for example 8.2%, 8.7%, 9.2%, 9.7%, 10.2%) and terminal growth values down a column (1.5%, 2.0%, 2.5%, 3.0%, 3.5%), on a dedicated sensitivity tab.
  2. In the top-left result cell, write one formula that recomputes enterprise value directly from that row's WACC and that column's growth rate, referencing the live FCF cells rather than a single cached output, for example: =SUMPRODUCT($C$10:$G$10,1/(1+C$4)^({1,2,3,4,5}-0.5))+($G$10*(1+$B5)/(C$4-$B5))/(1+C$4)^4.5
  3. Anchor the FCF range and the year exponents with dollar signs so they stay fixed, but leave the WACC cell (C$4) and growth cell ($B5) free so each references its own row and column as you copy the formula.
  4. Fill that formula right and down across the full grid. Because each cell recalculates independently from the underlying FCF, it updates instantly with the rest of the model and survives being copied into a new workbook.
  5. Wrap it in =IFERROR(...,"n/a") to handle the case where WACC equals the growth rate, which divides by zero.

The result is a 5x5 grid of implied enterprise values you can scan in one glance, built entirely from formulas you can trace, cell by cell, back to the assumptions tab.

If you would rather start from a finished version of everything above, complete with the sensitivity grid pre-built, see a finished version of everything above.