Build a DCF Valuation Model Step by Step

Create exactly four tabs:

1. Inputs, for valuation assumptions and capital structure
2. Forecast, for the historical base year and five forecast years
3. DCF, for discounting, terminal value, and valuation bridges
4. Sensitivity, for WACC, perpetual-growth, and EBIT-margin cases

This example uses annual year-end discounting. The 12/31/2025 actual year establishes the opening base but isn’t included as a forecast cash flow.

A DCF is appropriate when revenue, margins, taxes, reinvestment, and working capital can be forecast on a supportable basis. It’s less informative when cash flows are highly unpredictable or when the forecast omits material reinvestment requirements.

For alternative spreadsheet layouts, see Build a DCF Valuation Step by Step and How to Build a DCF Model in Google Sheets.

In Inputs, enter the following illustrative values:

Inputs!B2 is a fixed documentation field in this workbook. The model assumes valuation at the end of 2025 and uses manually assigned annual discount periods of 1 through 5. Changing B2 alone won’t change the discounting. If you change the valuation date or use stub periods, update the forecast dates and discount periods together.

Enter 10.0 in Inputs!B6 as a number, not the text 10.0x. To display it as 10.0x, apply the custom number format 0.0x.

For a real company, calculate net debt consistently with the valuation bridge. One possible construction is debt and debt-like items minus cash and cash equivalents, adjusted for the analytical or transaction definition being used. Negative net debt represents net cash.

Set entry validation to reject:

• B3: values below 0% or above 100%
• B4: values less than or equal to 0%
• B5: values less than or equal to -100%, or greater than or equal to B4
• B6: negative values
• B8: values less than or equal to zero

In Google Sheets, use Data > Data validation > Add rule and select Reject the input. In Excel, use Data > Data Validation and configure the equivalent limits.

Entry validation isn’t a substitute for formula controls. The formulas below test source inputs directly, so blank, text, and out-of-range values return #N/A instead of being treated as valid assumptions.

Set up Forecast with the following text labels in row 1:

These headers identify the periods. The DCF uses the numeric discount periods entered later in DCF!B2:F2.

Use rows 2 through 15 as follows:

Enter these illustrative assumptions:

• Forecast!B3: 500.0
• Forecast!C2:G2: 10.0%, 9.0%, 8.0%, 7.0%, 6.0%
• Forecast!B4:G4: 15.0%, 16.0%, 17.0%, 18.0%, 19.0%, 20.0%
• Forecast!B6:G6: 4.0%
• Forecast!B8:G8: 5.0%
• Forecast!B10:G10: 12.0%

In Forecast!B13, enter the following formula and copy it through G13:

=IF(AND(Inputs!$B$3<>"",ISNUMBER(Inputs!$B$3),Inputs!$B$3>=0,Inputs!$B$3<=100%),Inputs!$B$3,NA())

This formula tests Inputs!B3 directly. As a result, a blank source cell returns #N/A instead of being interpreted as a 0% tax rate.

For this example, restrict forecast growth to greater than -100% and no more than 100%. Allow EBIT margins from -100% through 100%, and restrict D&A, capex, NWC, and tax rates to 0% through 100%.

These ranges are workbook policies, not universal economic limits. Revenue growth, investment ratios, and working-capital ratios can fall outside them. Adjust the validation and formula guards when the company’s economics require it.

When valuing a real company, tie the actual-year figures to dated primary filings. Keep historical results, management guidance, and analyst assumptions clearly separated.

Forecast revenue

Enter this formula in Forecast!C3 and copy it through G3:

=IF(AND(ISNUMBER(B3),ISNUMBER(C2),C2>-100%,C2<=100%),B3*(1+C2),NA())

Expected forecast revenue is:

A prior-year revenue value of zero produces zero revenue. A blank or text value, or a growth rate outside this workbook’s range, returns #N/A.

Forecast EBIT, D&A, capex, and NWC

Enter the following formulas in column B and copy each through column G.

Forecast!B5:

=IF(AND(ISNUMBER(B3),ISNUMBER(B4),B4>=-100%,B4<=100%),B3*B4,NA())

Forecast!B7:

=IF(AND(ISNUMBER(B3),ISNUMBER(B6),B6>=0,B6<=100%),B3*B6,NA())

Forecast!B9:

=IF(AND(ISNUMBER(B3),ISNUMBER(B8),B8>=0,B8<=100%),B3*B8,NA())

Forecast!B11:

=IF(AND(ISNUMBER(B3),ISNUMBER(B10),B10>=0,B10<=100%),B3*B10,NA())

Spreadsheets store 100% as 1, so these upper-bound guards use 100%, not 100. Zero revenue produces zero operating values, while invalid revenue inputs or rates outside the selected ranges return #N/A.

Calculate NOPAT and UFCF

Enter these formulas in the 2026 forecast column and copy them through 2030.

Forecast!C12:

=IF(AND(ISNUMBER(C11),ISNUMBER(B11)),C11-B11,NA())

Forecast!C14:

=IF(AND(ISNUMBER(C5),ISNUMBER(C13),C13>=0,C13<=100%),C5*(1-C13),NA())

Forecast!C15:

=IF(AND(ISNUMBER(C14),ISNUMBER(C7),ISNUMBER(C9),ISNUMBER(C12)),C14+C7-C9-C12,NA())

The unlevered free cash flow equation is:

UFCF = NOPAT + D&A - Capex - Change in NWC

Interest expense and debt repayments are excluded because UFCF measures cash flow available to debt and equity capital providers.

The NOPAT formula assumes that any tax benefit from negative EBIT is immediately usable. That assumption may not hold when losses create net operating losses, valuation allowances, or cash-tax restrictions. If negative EBIT is material, replace the simplified calculation with a separate cash-tax and NOL schedule.

The completed forecast should produce:

Check the signs carefully. Increasing NWC and capex reduce UFCF, while D&A is added back after taxes are calculated on EBIT.

The example uses a 10.0% WACC as an illustrative input. It isn’t presented as an appropriate rate for a real company.

For an unlevered DCF, calculate WACC as:

Cost of equity = Risk-free rate + Beta × Equity risk premium

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

WACC = Equity weight × Cost of equity
     + Debt weight × After-tax cost of debt

Use capital weights consistent with the company being valued. Document:

• The date and source of the risk-free rate
• The equity risk premium source and measurement date
• The comparable-company set and beta calculation
• The debt-pricing or borrowing-cost source
• The tax rate used for the interest tax shield
• Market values or target capital weights

Don’t combine market inputs from different dates without explaining the mismatch. Also confirm that WACC remains greater than the perpetual-growth rate used in the perpetuity formula below.

In DCF, create this projection-period block:

Link DCF!B1:F1 to Forecast!C1:G1. Enter 1, 2, 3, 4, and 5 in DCF!B2:F2, then link DCF!B3:F3 to Forecast!C15:G15.

These fixed periods correspond to five full year-end cash flows following the documented 12/31/2025 valuation date. They don’t recalculate from Inputs!B2.

In DCF!B4, enter the discount-factor formula and copy it through F4:

=IF(AND(ISNUMBER(Inputs!$B$4),Inputs!$B$4>0,ISNUMBER(B2),B2>0),1/(1+Inputs!$B$4)^B2,NA())

Next, enter this formula in DCF!B5 and copy it through F5:

=IF(AND(ISNUMBER(B3),ISNUMBER(B4)),B3*B4,NA())

Expected results are:

In DCF!B7, sum the forecast-period present values:

=SUM(B5:F5)

The expected present value of forecast-period UFCF is approximately $279.055 million.

This workbook uses year-end discounting. A mid-year convention or stub period requires different discount periods and will produce a different valuation.

Terminal value represents cash flows beyond the explicit five-year forecast. Calculate it using perpetuity growth and an exit multiple, then compare how the terminal assumptions affect the result.

Create these labels in DCF!A9:A13:

1. Terminal value
2. PV of terminal value
3. Enterprise value
4. Equity value
5. Implied share price

Use column B for perpetuity growth and column C for the exit-multiple cross-check.

Perpetuity-growth method

In DCF!B9, enter:

=IF(AND(ISNUMBER(Forecast!G15),ISNUMBER(Inputs!B4),Inputs!B4>0,ISNUMBER(Inputs!B5),Inputs!B5>-100%,Inputs!B5<Inputs!B4),Forecast!G15*(1+Inputs!B5)/(Inputs!B4-Inputs!B5),NA())

This applies:

Terminal value = Final-year UFCF × (1 + Perpetual growth)
                 ÷ (WACC - Perpetual growth)

The formula returns #N/A when perpetual growth is greater than or equal to WACC.

Exit-multiple method

Terminal EBITDA equals EBIT plus D&A. In the example:

$146.870 + $29.374 = $176.244 million

Enter this formula in DCF!C9:

=IF(AND(ISNUMBER(Forecast!G5),ISNUMBER(Forecast!G7),ISNUMBER(Inputs!B6),Inputs!B6>=0),(Forecast!G5+Forecast!G7)*Inputs!B6,NA())

This formula applies the numeric 10.0 multiple in Inputs!B6 to 2030 EBITDA, not EBIT or UFCF.

Discount both terminal values

Enter the following formulas.

DCF!B10:

=IF(AND(ISNUMBER(B9),ISNUMBER(F4)),B9*F4,NA())

DCF!C10:

=IF(AND(ISNUMBER(C9),ISNUMBER(F4)),C9*F4,NA())

The expected terminal-value results are:

The terminal assumptions account for the difference. That difference alone doesn’t indicate a calculation error or establish which method is suitable for a real company.

Enterprise value equals the present value of forecast UFCF plus the present value of terminal value.

For the perpetuity-growth method, enter these formulas.

DCF!B11:

=IF(AND(ISNUMBER(B7),ISNUMBER(B10)),B7+B10,NA())

DCF!B12:

=IF(AND(ISNUMBER(B11),ISNUMBER(Inputs!B7)),B11-Inputs!B7,NA())

DCF!B13:

=IF(AND(ISNUMBER(B12),ISNUMBER(Inputs!B8),Inputs!B8>0),B12/Inputs!B8,NA())

For the exit-multiple method, enter:

DCF!C11:

=IF(AND(ISNUMBER($B$7),ISNUMBER(C10)),$B$7+C10,NA())

DCF!C12:

=IF(AND(ISNUMBER(C11),ISNUMBER(Inputs!B7)),C11-Inputs!B7,NA())

DCF!C13:

=IF(AND(ISNUMBER(C12),ISNUMBER(Inputs!B8),Inputs!B8>0),C12/Inputs!B8,NA())

The completed valuation bridge should read:

Check that the stated inputs produce approximately $20.46 in DCF!B13 and $24.47 in DCF!C13. These are illustrative arithmetic outputs, not price targets or investment recommendations.

Sensitivity analysis shows how valuation changes when specified assumptions change. It doesn’t establish which assumptions are appropriate.

WACC and perpetual-growth sensitivity

In Sensitivity, enter:

• B2:D2: 2.0%, 3.0%, 4.0%
• A3:A5: 9.0%, 10.0%, 11.0%

In Sensitivity!B3, enter this formula and copy it across B3:D5:

=IF(AND(ISNUMBER($A3),$A3>0,ISNUMBER(B$2),B$2>-100%,B$2<$A3,ISNUMBER(Inputs!$B$7),ISNUMBER(Inputs!$B$8),Inputs!$B$8>0),(SUMPRODUCT(Forecast!$C$15:$G$15,1/(1+$A3)^DCF!$B$2:$F$2)+Forecast!$G$15*(1+B$2)/($A3-B$2)/(1+$A3)^DCF!$F$2-Inputs!$B$7)/Inputs!$B$8,NA())

The formula uses DCF!B2:F2 as the discount-period array. In a semicolon-based locale, change the function-argument commas to semicolons.

Expected implied share prices are:

Holding cash flows constant, a higher WACC lowers value. Higher perpetual growth raises value as long as growth remains below WACC.

EBIT-margin sensitivity

Enter EBIT-margin shifts of -2.0%, 0.0%, and 2.0% in Sensitivity!F2:F4.

In Sensitivity!G2, enter this formula and copy it through G4:

=IFERROR(IF(AND(ISNUMBER(F2),F2>=-100%,F2<=100%,COUNT(Forecast!$C$4:$G$4)=COLUMNS(Forecast!$C$4:$G$4),MIN(Forecast!$C$4:$G$4)+F2>=-100%,MAX(Forecast!$C$4:$G$4)+F2<=100%,Inputs!$B$3<>"",ISNUMBER(Inputs!$B$3),Inputs!$B$3>=0,Inputs!$B$3<=100%,ISNUMBER(Inputs!$B$4),Inputs!$B$4>0,ISNUMBER(Inputs!$B$5),Inputs!$B$5>-100%,Inputs!$B$5<Inputs!$B$4,ISNUMBER(Inputs!$B$7),ISNUMBER(Inputs!$B$8),Inputs!$B$8>0),(SUMPRODUCT(Forecast!$C$15:$G$15+Forecast!$C$3:$G$3*F2*(1-Inputs!$B$3),1/(1+Inputs!$B$4)^DCF!$B$2:$F$2)+(Forecast!$G$15+Forecast!$G$3*F2*(1-Inputs!$B$3))*(1+Inputs!$B$5)/(Inputs!$B$4-Inputs!$B$5)/(1+Inputs!$B$4)^DCF!$F$2-Inputs!$B$7)/Inputs!$B$8,NA()),NA())

The guard checks the shift, every resulting forecast EBIT margin, and the source tax-rate input. The outer IFERROR ensures that a text or otherwise malformed margin shift returns #N/A rather than #VALUE!.

Under this workbook’s policy, the formula also returns #N/A if a shift moves any 2026-2030 EBIT margin outside the -100% to 100% range. A blank, malformed, or above-100% tax rate produces the same result.

Expected results are:

This sensitivity changes after-tax EBIT while holding revenue, D&A, capex, NWC, tax, WACC, and perpetual growth constant. It also retains the simplified assumption that changes in EBIT create an immediate tax effect at the stated rate.

Before relying on the output, use the following review procedures. These are proposed tests; they haven’t been executed or audited as part of this guide.

Reconcile the base-case arithmetic

Using the stated inputs, compare every forecast and valuation output against the expected values above. Small display differences may result from rounding, but the formulas should retain unrounded values.

Confirm these identities:

UFCF = NOPAT + D&A - Capex - Change in NWC

Enterprise value = PV of forecast UFCF + PV of terminal value

Equity value = Enterprise value - Net debt

Implied share price = Equity value ÷ Diluted shares

Test WACC and perpetual-growth boundaries

Try these temporary inputs:

• WACC of 10% and perpetual growth of 10%: DCF!B9 should return #N/A.
• Perpetual growth above WACC: DCF!B9 should return #N/A.
• Blank, text, or zero WACC: dependent valuation formulas should return #N/A.

Restore the base assumptions after each check.

Test the share-count guard

With 50 million diluted shares, the base perpetuity case should produce $20.46 per share.

Zero, blank, negative, or text share counts should return #N/A, not a divide-by-zero result or an implied value of zero.

Test malformed inputs separately from validation

In an unvalidated copy of the workbook, paste text into revenue, growth, margin, tax, D&A, capex, or NWC assumptions. The dependent formulas should return #N/A.

Clear Inputs!B3 completely. Forecast!B13:G13, the forecast NOPAT cells, and Sensitivity!G2:G4 should return #N/A rather than treating the blank as a 0% tax rate.

Enter text in one of Sensitivity!F2:F4. Its corresponding cell in column G should return #N/A, not #VALUE!.

Next, paste rates above 100% into the growth, EBIT margin, D&A, capex, NWC, or tax rows. Because those values breach the example’s selected ranges, the formulas should return #N/A. Change the guards if the business can legitimately support values outside those limits.

This process checks formula behavior independently of entry validation.

Test zero values and opening working capital

Confirm that:

• Zero revenue produces zero EBIT, D&A, capex, and calculated NWC.
• UFCF becomes zero only if prior-period NWC is also zero.
• To test a complete revenue decline while preserving the $60 million opening NWC, temporarily overwrite the formula in Forecast!C3 with numeric 0. Keep Forecast!B11 at $60 million. C11 should equal zero, C12 should equal negative $60 million, and C15 should equal positive $60 million because releasing opening NWC creates a cash inflow. After the test, restore the documented revenue formula in C3 and copy it through G3.
• Equity value can be negative when enterprise value is below net debt.
• A numeric 0% tax rate makes NOPAT equal EBIT.
• A numeric zero exit multiple produces a zero exit terminal value rather than an error.

The $60 million opening NWC is calculated once as 12% of the $500 million actual-year revenue. The first forecast change is $66 million minus $60 million, so opening NWC isn’t counted twice.

Test negative EBIT and tax treatment

Enter a negative EBIT margin within the permitted range and inspect NOPAT. The formula multiplies negative EBIT by one minus the tax rate, recognizing an immediate tax benefit.

That behavior is intentional for this simplified example. If tax losses can’t be used immediately, build a separate cash-tax schedule that tracks taxable income, NOL creation, NOL use, and relevant limitations.

Test signs and units

Confirm that:

• Increasing NWC reduces UFCF.
• A reduction in NWC increases UFCF.
• Capex reduces UFCF.
• D&A is added back after tax.
• Financial statements and valuation values use USD millions.
• Diluted shares use millions of shares.
• The exit multiple is stored as a number and displayed as x.
• Only the per-share output is USD per share.

Test the date boundary

Only the five forecast cash flows from 12/31/2026 through 12/31/2030 should be discounted. The 12/31/2025 actual year is the opening base and isn’t valued as a forecast cash flow.

The valuation date and period headers are documentation fields, so changing Inputs!B2 alone won’t update the discount periods. If the valuation date changes, manually rebuild the timing assumptions or replace the fixed periods with a date-based discounting schedule.

Finally, check manually for duplicate forecast columns. A duplicated year would double-count cash flow.

Test sensitivity direction and policy limits

Holding all other inputs constant:

• Raising WACC should reduce implied value.
• Raising perpetual growth should increase value while growth remains below WACC.
• Raising EBIT margin should increase UFCF and implied value.
• A margin shift that takes any forecast EBIT margin below -100% or above 100% should return #N/A under the selected policy.
• A blank, text, or otherwise malformed EBIT-margin shift should return #N/A.

If the model behaves differently, inspect the discount periods, tax treatment, formula guards, and cash-flow signs.

Check the terminal multiple

Recalculate terminal EBITDA independently:

2030 EBIT + 2030 D&A
= $146.870 + $29.374
= $176.244 million

Confirm that the numeric 10.0 multiple is applied to this amount, not to EBIT or UFCF.

Review source support before using real data

For a real valuation, tie historical figures to dated primary filings and document:

• Debt and cash construction
• Diluted share count
• Market-input dates
• Peer selection and beta calculations
• Exit-multiple construction
• Management guidance versus analyst assumptions

Entry controls and reconciliations can expose certain problems, but they don’t prove that the forecast or valuation assumptions are economically appropriate.

Present both terminal methods instead of selecting the higher value without explanation. The completed example produces this summary:

• Five-year PV of UFCF: $279.055 million
• Perpetuity-growth enterprise value: $1,172.786 million
• Perpetuity-growth implied share price: $20.46
• Exit-multiple enterprise value: $1,373.390 million
• Exit-multiple implied share price: $24.47
• WACC and growth sensitivity range: $15.59 to $29.19 per share
• EBIT-margin sensitivity range: $17.72 to $23.19 per share

Explain the assumptions driving the difference between methods, including terminal growth, WACC, terminal EBITDA, and the selected multiple. Neither result should be presented as a precise prediction.

ModelMonkey can inspect spreadsheet context in Google Sheets and Excel and assist with formula analysis, cell formatting, and chart creation or adjustment. For this workbook, you could ask:

Review the Forecast and DCF tabs for formula errors, verify the enterprise-to-equity valuation bridges, and format the sensitivity tables.

Where the workflow presents a preview, review the proposed changes before proceeding. Use ModelMonkey to review the DCF workbook, then complete the manual controls in Step 9.