A DCF model in Excel remains one of the most widely used and defensible approaches to business valuation available to analysts, founders, and investors. It forces you to state every assumption explicitly: how fast revenue grows, what margins normalise to, how much capital the business consumes, and what the long-run growth rate looks like once the forecast period ends. That discipline is exactly why investors and lenders take a well-built DCF seriously, and why a sloppy one gets torn apart in the first review meeting.
The challenge is structural. The logic behind a discounted cash flow model is clean, but building one from scratch in Excel is where most analysts lose hours and introduce formula errors, ending up with a model that looks right but breaks when you change a single input. This guide walks through the five steps that produce a reliable, auditable DCF model in Excel: structuring the workbook, forecasting unlevered free cash flows, calculating WACC, computing terminal value, and running sensitivity analysis. Readers who want to skip the build entirely can download a professionally structured, fully unlocked DCF Excel template at Best Financial Models, where every step covered here is already wired up and ready to customise.
Two DCF model Excel templates that are readily available from our library:
A DCF model in Excel works by pulling future free cash flows back through time, risk and WACC to determine present value. A credible DCF model in Excel exposes the assumptions, cash flows and discount rates driving the valuation rather than hiding them inside formulas.
– Dr. Jozef T Burger, Founder, BestFinancialModels.com
Clean structure prevents debugging nightmares later. Before any formulas go in, the workbook needs a logical tab layout that separates inputs from calculations, and calculations from outputs. Investors and lenders who open your model will navigate by tab, and an auditable layout signals professional-grade work before they read a single number.
A standard DCF workbook uses six tabs: Assumptions, Historicals, Forecast/FCFF, WACC, Valuation, and Sensitivity. Keeping inputs on their own tab and calculations on separate tabs means a reviewer can trace any output back to its source assumption without hunting through formula bars across dozens of worksheets. That separation is what makes a model board-ready rather than analyst-only.
Time flows left to right: historical periods in the earlier columns, forecast years moving right, and a terminal value column at the far end. Rows should follow a consistent order: Revenue, EBIT, NOPAT, D&A, CapEx, change in NWC, FCFF, then the valuation block below. Consistent ordering means anyone reviewing the model can orient themselves instantly without a guide.
Every projection driver belongs in one clearly labelled input area: revenue growth rates, EBIT margins, tax rate, CapEx-to-revenue ratios, working capital intensity, WACC components, and terminal growth rate. Hardcoded numbers buried inside formulas are a major cause of model errors and among the fastest ways to lose credibility with a sophisticated reviewer. Centralising all inputs in one place improves auditability, speeds up scenario testing, and makes it clear that the assumptions themselves still require careful judgment and research; the model can only be as sound as the numbers you put into it.
The explicit forecast period is where the model earns its credibility. Vague assumptions produce vague results, and investors know the difference. A revenue-driven, assumption-by-assumption build is the standard approach because it makes every driver visible and challengeable.
Start with a revenue growth assumption, either a top-down growth rate or a bottom-up driver such as units times price, then apply an EBIT margin that either holds steady or fades toward a normalised industry level over the five-year period. One explicit growth rate per year and one margin assumption per year gives you a model you can stress-test in seconds. A ramp from current margin toward a normalised steady-state is more credible than a flat margin if the business is still scaling.
The standard formula is: FCFF = EBIT Γ (1 β Tax Rate) + D&A β CapEx β ΞNWC. Tax EBIT to get NOPAT, add back the non-cash D&A charge, subtract capital expenditures, and adjust for the annual change in net working capital. Working capital is most cleanly modelled as a percentage of revenue using DSO, DIO, and DPO ratios, which ties the cash conversion cycle directly to the operating assumptions rather than leaving it as an arbitrary input.
A robust DCF is backed by linked financial statements, not a standalone FCFF schedule. Net income flows from the income statement into retained earnings on the balance sheet and into the top of the cash flow statement. Working capital movements on the balance sheet feed the operating section of the cash flow statement, while CapEx flows through investing activities to PP&E. The one balance that must hold: ending cash on the cash flow statement must always tie to cash on the balance sheet. When that balance holds, the FCFF numbers the model produces are trustworthy.
WACC is the rate at which you discount every future cash flow, so a calculation error here flows into every number in the model. In practice, WACC for established U.S. businesses tends to fall somewhere in the high single digits to low double digits, though the right number depends entirely on the company’s risk profile, capital structure, and the current rate environment. Get the components right, use the correct weights, and keep every input in a named cell so the formula is readable and auditable.
The CAPM formula is: Re = Rf + Ξ² Γ (Rm β Rf). In Excel, set up named input cells for the risk-free rate, beta, and expected market return, then reference them in the cost of equity formula. The risk-free rate is typically the current 10-year Treasury yield, and the equity risk premium is most commonly benchmarked to Damodaran’s annually updated estimates, which are freely available and widely referenced by institutional investors and buy-side analysts.
The after-tax cost of debt is simply Rd Γ (1 β Tax Rate), which captures the interest tax shield. Weight each component by its share of total capital using market values, not book values, and the final Excel formula reads: = (E / E + D)) Γ Re + (D / (E + D)) Γ Rd Γ (1 – T). Market value weights reflect what it actually costs to raise capital today, which is what the DCF needs to produce a meaningful valuation.
Several pitfalls consistently trip up analysts building WACC from scratch. Using book value weights instead of market value weights is the most common structural error. Forgetting the tax shield on debt understates the benefit of leverage. Applying a single static WACC to a company whose capital structure is expected to change materially over the forecast period is subtler but equally distorting; the clean fix is to use the target capital structure rather than the current snapshot, which produces a WACC that reflects where the business is heading rather than where it stands today.
Terminal value represents everything that happens after year five. Because it typically accounts for 60-80% of a DCF’s total enterprise value, a range documented across standard corporate finance literature and practitioner guides, the assumptions that drive it carry disproportionate weight. That single reality makes WACC and the terminal growth rate the two most consequential inputs in the entire model, and both deserve more scrutiny than any single year of the explicit forecast.
The formula is: TV = FCFFn Γ (1 + g) / (WACC β g), where g is the long-run nominal growth rate (i.e., inclusive of inflation). For a U.S. business, setting g at 2-3%, at or below long-run nominal GDP growth, is a widely used practitioner convention supported by sources such as Damodaran’s valuation work and standard corporate finance texts. Any company growing faster than the economy indefinitely would eventually consume the entire economy, which is a useful reality check when a model produces an unusually high terminal value. Discount the result back to present value using: PV of TV = TV / (1 + WACC)^N.
The exit multiple approach calculates terminal value as EBITDAn Γ Exit Multiple, where the multiple is benchmarked to comparable public company trading multiples or recent transaction multiples. This method is common in private equity models because it grounds terminal value in observable market data rather than a perpetuity assumption. Running both methods and comparing the implied values is a useful internal consistency check.
With the majority of enterprise value locked in the terminal value, WACC and the terminal growth rate are not peripheral assumptions; they are the model. That reality makes sensitivity testing these two inputs essential for any valuation you plan to put in front of an investor or lender. A model without sensitivity analysis on WACC and terminal growth is incomplete for rigorous decision-making, and most sophisticated reviewers will ask for it immediately.
Once the FCFF schedule and terminal value are discounted back, the final valuation block bridges from enterprise value to an implied share price. This is the section that goes into the pitch deck or the funding memo, and it needs to hold up against comparables.
Enterprise Value equals the sum of the present values of forecast FCFFs plus the present value of terminal value. Keep the layout clean: a discount factor row using 1/(1+WACC)^t multiplied by each period’s FCFF, a separate row for the PV of terminal value, and a summed enterprise value at the bottom. This row-by-row layout makes the model easy to audit because every discounted cash flow is visible.
The equity bridge is: Enterprise Value β Net Debt β Preferred Stock β Minority Interest = Equity Value. Net debt is gross debt minus cash and cash equivalents, and it should match the balance sheet date of the last historical period, not a projected figure. Dividing equity value by diluted shares outstanding produces an implied share price you can defend in an investor meeting.
Cross-check the implied EV/EBITDA and EV/Revenue multiples against comparable public companies and recent transactions. If the DCF implies a 20Γ EV/EBITDA multiple for a company in a sector where peers trade at 8Γ or 10Γ, the assumptions need revisiting, not just the terminal value. This step separates a credible, presentation-grade valuation from a number engineered to support whatever answer you wanted before you opened Excel.
A complete DCF model includes a sensitivity table showing how the valuation moves as WACC and the terminal growth rate change. Seeing that range of outcomes is what gives investors and lenders confidence that you understand the assumptions driving the number, not just the number itself. For an experienced modeller working with a correctly structured model, building this table can often take just a few minutes.
Set up a grid with WACC values across the top row and terminal growth rates down the left column. In the top-left corner cell of the grid, enter a link to your enterprise value or implied share price output. Select the entire grid, including the corner cell, top row, and left column, then go to Data, What-If Analysis, Data Table. Set WACC as the row input cell and the terminal growth rate as the column input cell, then click OK. Excel populates every WACC-and-growth combination automatically, giving you a complete picture of valuation sensitivity without writing a single additional formula.
Building a fully linked, three-statement-backed DCF model in Excel from scratch takes an experienced analyst 10- 20 hours and still carries the risk of formula errors in the discount factor row or broken links between statements. Best Financial Models offers a fully unlocked DCF valuation Excel template designed for immediate use, no subscription required, no locked cells. The template includes the full FCFF build, a WACC calculator, both terminal value methods, the EV-to-equity bridge, and a pre-built sensitivity table: everything covered in this guide, structured and ready to customise for your business. For founders, CFOs, and consultants who need a polished, lender-ready output quickly, it is a faster, lower-risk starting point than building every formula from scratch.
The five-step process covered here produces a DCF model in Excel that holds up to scrutiny. Structure the workbook before writing formulas. Forecast FCFF from revenue-driven assumptions. Calculate WACC using CAPM and after-tax debt cost. Compute terminal value using one or both methods. Bridge to equity value and overlay a sensitivity table. Every number in the output is only as reliable as the assumptions and structural integrity behind it; a DCF built on hardcoded inputs, book-value WACC weights, and no sensitivity analysis is not a valuation; it is a number in a spreadsheet.
If you want a professionally structured, fully editable starting point for your next valuation, the Best Financial Models DCF model in Excel delivers the complete framework described in this guide, ready to download and customise for your specific business, sector, and funding requirements. Visit Best Financial Models to access the template and take your valuation analysis from first principles to a finished, credible output without the full build time. Alternatively, if you require a comprehensive business plan that includes detailed market research and industry-specific projections, contact JTB Consulting, South Africaβs leading business plan consultancy.
We don’t spam, and you can always opt out at any time.
