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 (all dollar figures in this guide are in millions): 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).

Each line above is rounded for display, but the FCF figures come from the unrounded rows. Year 2 is 181.5 + 60.5 − 72.6 − 11.0 = 158.4, not the 159 you get by adding the rounded line items. Let Excel carry full precision and only format the display, or small rounding errors will compound through the rest of the model.

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 $210.8, 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.1 + 80.5 = $402.6. 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.

Here is that check on the example. The Gordon value of $3,225 divided by Year 5 EBITDA of $402.6 implies an exit multiple of about 8.0x. Going the other way, the exit multiple value implies a perpetual growth rate of g = (TV × WACC − FCF_final) / (TV + FCF_final) = (4,026 × 0.092 − 210.8) / (4,026 + 210.8) = 3.8%. So the 10x multiple assumes the business grows at 3.8% forever, which is faster than most long-run growth assumptions. In this example the exit multiple is the more aggressive of the two, and the gap between them is a question about that multiple.

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: $699. The five rounded rows add to $700, and the unrounded sum is 699.2.

The terminal value does not get the mid-year factor. It is a value measured as of the end of year 5, so it discounts at the full t = 5: factor 1 / 1.092^5 = 0.644.

Using the Year 5 mid-year factor (0.673) on the terminal value is a common slip. Here it would overstate the exit multiple PV by about 4.5%, which is $117 of enterprise value. The Gordon value is a judgment call. Many banks discount it at t − 0.5 because the perpetuity is built from cash flows that also arrive mid-year, which here would give $2,171 instead of $2,077. This guide uses the full year for both so the two methods compare on one basis. Whichever you pick, label it.

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 $25 to $30 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(Model!$C$10:$G$10,1/(1+C$4)^({1,2,3,4,5}-0.5))+(Model!$G$10*(1+$B5)/(C$4-$B5))/(1+C$4)^5. Here Model!$C$10:$G$10 is the FCF row on your model tab, and C$4 and $B5 are the WACC and growth headers on the sensitivity tab.
  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. Check it with the center cell. At 9.2% and 2.5% it should show $2,776, the Gordon growth enterprise value from Step 5. If it does not, the grid is pointing at the wrong row or using a different discount convention than the model.

Built with AI assistance and formula-checked. For education and planning only, not financial advice.

If you would rather start from a finished version of everything above, complete with the sensitivity grid pre-built, see the DCF Valuation Model. One known issue is open in that model: with the mid-year toggle on, it discounts the exit multiple terminal value at year 4.5 instead of year 5, the slip described in Step 5. The product page explains it and the one-cell fix.