How to build a DCF model in Excel, step by step

The structure of every DCF

1. Assumptions: growth, margins, tax, investment needs, WACC and long-term growth, each in its own input cell.
2. Forecast: revenue, operating profit and unlevered free cash flow for five (sometimes ten) years.
3. Discounting: the present value of each year's cash flow.
4. Terminal value: everything after the last forecast year, in one number.
5. Bridge: from enterprise value to equity value and value per share.

Keep assumptions and formulas strictly separate. Every number in the forecast should be a formula that points back to an input, never a value you typed in. That is what lets you change the WACC and watch the share price move.

Step 1: forecast revenue and operating profit

Start from the last actual year (2025A: $120.0m revenue) and grow it with a growth rate per year: =B12*(1+C11). Growth slows from 8% to 4%, which is typical. Few companies grow fast forever. EBIT is revenue × the EBIT margin, written as =C12*$B$2. The dollar signs keep the reference fixed on the margin input when you copy the formula to the right.

Worked example: a five-year DCF

Every green cell is a formula you can copy across. The formulas list shows the 2026 column; the other years are the same formula moved one column to the right.

Worked example: a five-year DCF

	A	B	C	D	E	F	G
1	Assumptions	Value
2	EBIT margin	15.0%
3	Tax rate	25%
4	D&A (% of revenue)	4.0%
5	CapEx (% of revenue)	5.0%
6	Net working capital (% of revenue)	10.0%
7	WACC	9.0%
8	Terminal growth g	2.5%
9
10	$m	2025A	2026E	2027E	2028E	2029E	2030E
11	Revenue growth		8.0%	7.0%	6.0%	5.0%	4.0%
12	Revenue	120.0	129.6	138.7	147.0	154.3	160.5
13	EBIT		19.4	20.8	22.0	23.2	24.1
14	NOPAT = EBIT × (1 − t)		14.6	15.6	16.5	17.4	18.1
15	+ D&A		5.2	5.5	5.9	6.2	6.4
16	− CapEx		6.5	6.9	7.3	7.7	8.0
17	− Increase in NWC		1.0	0.9	0.8	0.7	0.6
18	Free cash flow		12.3	13.3	14.2	15.1	15.8
19	Year n		1	2	3	4	5
20	Discount factor 1/(1+WACC)^n		0.9174	0.8417	0.7722	0.7084	0.6499
21	PV of free cash flow		11.3	11.2	11.0	10.7	10.3
22
23	Sum of PV of FCF	54.5
24	Terminal value (end of 2030)	249.7
25	PV of terminal value	162.3
26	Enterprise value	216.8
27	Net debt	60.0
28	Equity value	156.8
29	Shares (m)	10.0
30	Value per share ($)	15.68
31	Check: NPV of FCF	54.5

Step 2: from EBIT to free cash flow

A DCF discounts unlevered free cash flow: the cash the business produces for all investors, before any interest is paid.

FCF = EBIT × (1 − t) + D&A − CapEx − increase in net working capital

EBIT × (1 − t) is NOPAT, operating profit after tax. Depreciation and amortisation are added back because they are not cash payments. CapEx is subtracted because it is. A growing company also needs more receivables and inventory, and that extra working capital ties up cash. Here it is 10% of each year's revenue increase. In 2026 the result is $12.3m of free cash flow.

Step 3: discount at the WACC

A dollar in 2030 is worth less than a dollar today. Each cash flow is multiplied by a discount factor =1/(1+$B$7)^C19, where row 19 counts the years. At a 9% WACC, a dollar received in year 5 is worth 0.6499 today. The model assumes cash arrives at the end of each year. Excel's NPV function makes the same assumption, so =NPV(B7,C18:G18) reproduces the sum of the present values ($54.5m) in one cell. Use it as a check.

Step 4: the terminal value

The company does not stop in 2030. The Gordon growth formula values all later cash flows, assuming they grow at a steady rate g forever: TV = FCF2030 × (1 + g) / (WACC − g). With g = 2.5% that gives $249.7m at the end of 2030. That is why it is discounted with the 2030 factor, not today's. Its present value is $162.3m, which is 75% of enterprise value. That share is normal, and it shows how much the result depends on two inputs: WACC and g.

Step 5: from enterprise value to value per share

Enterprise value (EV) = sum of the discounted cash flows + discounted terminal value = $216.8m. EV belongs to lenders and shareholders together, so subtract net debt (debt minus cash, $60m here) to get the equity value of $156.8m. Divide by 10m shares for $15.68 per share.

The last step in a real model is a sensitivity table: value per share for a grid of WACC and growth rates. That keeps everyone honest about how uncertain the single number really is.

Common mistakes

• Discounting the terminal value one year too few or too many. The Gordon value sits at the end of the last forecast year, so use that year's discount factor.
• Putting year 0 inside NPV. NPV discounts its first value by one full period. Anything that happens today goes outside the function.
• A growth rate close to the WACC. As g approaches the WACC, the terminal value explodes. Long-term growth should be modest, roughly in line with long-run inflation and economic growth.
• Discounting levered cash flow at the WACC. If interest has already been deducted, you are mixing equity cash flows with a company-wide rate.
• Hard-coded numbers in the forecast. One typed value breaks the link to the assumptions, and the model stops reacting to changes.
• Forgetting net debt. EV is not equity value. A company with large debts can have a big EV and a small share price.