DCF Model in Excel: Build a Discounted Cash Flow From Scratch

A 2025 McKinsey survey found that 87% of investment banking analysts still rely on Excel-based DCF models as their primary valuation tool. Yet most tutorials skip the parts that actually trip people up: circular WACC references, mid-year convention adjustments, and terminal value sanity checks. This guide builds a complete DCF model in Excel from a blank workbook, covering every formula, assumption, and cross-check you need to produce a defensible valuation.

Whether you are preparing for investment banking interviews, auditing a client's model, or valuing an acquisition target, this walkthrough gives you a production-ready framework.

What Is a DCF Model and Why Does It Matter?

A discounted cash flow (DCF) model estimates a company's intrinsic value by projecting its future free cash flows and discounting them back to today's dollars using a risk-adjusted rate. It is the most widely used valuation method in investment banking, private equity, and corporate development because it values a business based on fundamentals rather than market sentiment.

The core logic is simple: a dollar earned five years from now is worth less than a dollar today. The DCF quantifies exactly how much less, using the weighted average cost of capital (WACC) as the discount rate.

When Should You Use a DCF?

• M&A transactions — valuing a target company independent of comparable trading multiples
• Equity research — establishing a price target based on intrinsic value
• Capital budgeting — evaluating whether a project's returns exceed the cost of capital
• Fairness opinions — providing an independent valuation for board-level decisions

A DCF works best for companies with predictable, positive cash flows. It is less reliable for early-stage startups with no revenue history or companies undergoing rapid structural change — for those businesses, a purpose-built SaaS startup financial model based on MRR mechanics and unit economics provides a more appropriate analytical framework.

How Do You Build a DCF Model in Excel Step by Step?

Building a DCF model in Excel follows six sequential steps: project free cash flows, calculate WACC, estimate terminal value, discount everything to present value, bridge from enterprise value to equity value, and run sensitivity analysis. Here is each step in detail.

graph TD
    A[Step 1: Historical Financials] --> B[Step 2: Forecast Free Cash Flow]
    B --> C[Step 3: Calculate WACC]
    B --> D[Step 4: Terminal Value]
    C --> E[Discount FCFs to Present Value]
    D --> E
    E --> F[Enterprise Value]
    F --> G[Step 5: Equity Bridge]
    G --> H[Implied Share Price]
    H --> I[Step 6: Sensitivity Analysis]

Step 1: Set Up Your Workbook Structure

Before writing a single formula, organize your workbook with these tabs:

1. Assumptions — all key inputs in one place (revenue growth, margins, CapEx, WACC components)
2. Income Statement — historical (3-5 years) and projected (5-10 years)
3. Balance Sheet — historical and projected
4. Cash Flow — derived from the income statement and balance sheet
5. DCF — the valuation engine
6. Sensitivity — data tables for key variable ranges

Best practice: Color-code inputs (blue font), formulas (black font), and links to other sheets (green font). This convention is standard across Wall Street and makes models auditable.

Step 2: Forecast Unlevered Free Cash Flow (UFCF)

Unlevered Free Cash Flow represents the cash a company generates before any debt payments. It is capital-structure neutral, which is why it pairs with WACC (which blends debt and equity costs).

The formula:

UFCF = EBIT × (1 - Tax Rate) + D&A - CapEx - Change in Net Working Capital

In Excel, assuming your projected data starts in column D (Year 1) with rows for each line item:

=D10*(1-$B$3)+D12-D14-(D16-C16)

Where:

• D10 = EBIT
• $B$3 = Tax rate (locked reference)
• D12 = Depreciation & Amortization
• D14 = Capital Expenditures
• D16 = Net Working Capital (Current Assets minus Current Liabilities)

Key assumptions to get right:

Assumption	What Drives It	Typical Range
Revenue growth	Industry trends, market share, pricing power	3-15% for mature companies
EBIT margin	Operating leverage, cost structure	10-30% depending on industry
Tax rate	Statutory rate, NOLs, jurisdictional mix	20-25% for US companies
CapEx as % of revenue	Capital intensity, growth vs. maintenance	3-12%
D&A as % of revenue	Asset base maturity	2-8%
NWC as % of revenue	Industry working capital norms	5-15%

Pro tip: For the forecast period, project revenue first, then derive each line item as a percentage of revenue. This creates a clean, assumption-driven model where you can stress-test any variable.

Step 3: Calculate the Weighted Average Cost of Capital (WACC)

WACC blends the cost of debt and cost of equity based on the company's target capital structure. It is the discount rate that converts future cash flows into present value.

WACC = (E/V) × Re + (D/V) × Rd × (1 - T)

Where:

• E/V = Equity weight (market cap / total enterprise value)
• D/V = Debt weight (net debt / total enterprise value)
• Re = Cost of equity (from CAPM)
• Rd = Cost of debt (weighted average interest rate)
• T = Marginal tax rate

Calculating Cost of Equity with CAPM

Re = Rf + β × (Rm - Rf)

In Excel:

=B5+B7*(B6-B5)

Where:

• B5 = Risk-free rate (10-year Treasury yield, ~4.2% in 2026)
• B6 = Expected market return (~10% long-term S&P 500 average)
• B7 = Levered beta (from Bloomberg, Capital IQ, or Yahoo Finance)

Putting WACC Together

=B12*B14+B13*B15*(1-B16)

Where:

• B12 = Equity weight
• B13 = Debt weight
• B14 = Cost of equity
• B15 = Pre-tax cost of debt
• B16 = Tax rate

Typical WACC range: 7-12% for most publicly traded companies. A WACC below 6% or above 15% should trigger a sanity check on your inputs.

How Do You Calculate Terminal Value in a DCF?

Terminal value captures the company's value beyond the explicit forecast period. It typically accounts for 60-80% of total enterprise value, making it the single most important (and most debated) number in your model.

⚠️ Warning: If terminal value exceeds 85% of your total enterprise value, your explicit forecast period is too short or your near-term growth assumptions are too conservative. This is the most common red flag reviewers look for in a DCF.

There are two standard approaches.

Method 1: Gordon Growth Model (Perpetuity Growth)

This assumes free cash flows grow at a constant rate forever:

Terminal Value = FCF(n) × (1 + g) / (WACC - g)

In Excel:

=H20*(1+$B$8)/($B$9-$B$8)

Where:

• H20 = Final year UFCF
• $B$8 = Perpetuity growth rate (typically 2-3%, approximating long-term GDP or inflation)
• $B$9 = WACC

Critical rule: The perpetuity growth rate must be less than WACC and should not exceed long-term nominal GDP growth (~3%). A growth rate above 4% implies the company will eventually become larger than the entire economy.

Method 2: Exit Multiple

This values the company at a multiple of its final-year metric (usually EBITDA):

Terminal Value = EBITDA(n) × Exit Multiple

In Excel:

=H18*$B$10

Where:

• H18 = Final year EBITDA
• $B$10 = Exit EV/EBITDA multiple (sourced from a comparable company analysis in Excel)

Which Method Should You Use?

Factor	Perpetuity Growth	Exit Multiple
Best for	Stable, mature businesses	Companies with clear trading comps
Key input	Long-term growth rate	Comparable EV/EBITDA multiple
Sensitivity	Highly sensitive to growth rate vs. WACC spread	Depends on comp selection
Common in	Academic models, equity research	Investment banking, PE
Weakness	Assumes infinite growth at constant rate	Circular: uses market multiples in an intrinsic value model

Best practice: Calculate both and present a range. If the two methods produce wildly different results, revisit your assumptions.

Step 4: Discount Cash Flows to Present Value

Now bring everything back to today's dollars. Each year's UFCF and the terminal value get divided by (1 + WACC)^n, where n is the number of years from today.

Standard Year-End Convention

PV of FCF = FCF(n) / (1 + WACC)^n

In Excel (for Year 1 in column D):

=D20/(1+$B$9)^D3

Where D3 contains the year number (1, 2, 3, etc.).

Mid-Year Convention (Recommended)

Cash flows don't arrive in a lump sum on December 31. The mid-year convention assumes cash is received evenly throughout the year, so you discount to the midpoint:

💡 Pro Tip: The mid-year convention typically increases your valuation by 3-5% compared to year-end discounting. Most investment banks use mid-year as the default. If comparing your model to a peer's, check which convention they used — this alone can explain a meaningful valuation gap.

PV of FCF = FCF(n) / (1 + WACC)^(n - 0.5)

In Excel:

=D20/(1+$B$9)^(D3-0.5)

The terminal value gets discounted at the full final year (not mid-year), since it represents value from the end of the projection period onward:

=TV/(1+$B$9)^H3

Summing It All Up

Use SUM to add the present values of all projected FCFs plus the discounted terminal value:

=SUM(D22:H22)+H24

This gives you the Enterprise Value (EV).

Step 5: Bridge from Enterprise Value to Equity Value

Enterprise Value includes all capital providers. To find what equity holders own, you need the equity bridge:

graph LR
    A[Enterprise Value] --> B[Minus: Net Debt]
    B --> C[Minus: Minority Interest]
    C --> D[Minus: Preferred Stock]
    D --> E[Equity Value]
    E --> F[Divide by Diluted Shares]
    F --> G[Implied Share Price]

Equity Value = Enterprise Value - Net Debt - Minority Interest - Preferred Stock + Associates

In Excel:

=B30-B32-B33-B34+B35

Where:

• B30 = Enterprise Value from your DCF
• B32 = Total debt minus cash and equivalents (Net Debt)
• B33 = Minority/non-controlling interest
• B34 = Preferred stock
• B35 = Value of equity method investments

Then calculate the implied share price:

=B37/B38

Where B37 = Equity Value and B38 = Fully diluted shares outstanding (include stock options using the treasury stock method).

Step 6: Run Sensitivity Analysis

A DCF is only as good as its assumptions. Sensitivity tables — for an in-depth walkthrough, see our guide to sensitivity analysis in Excel — show how your valuation changes across a range of inputs, giving stakeholders a valuation range rather than a false sense of precision.

Two-Way Data Table: WACC vs. Terminal Growth Rate

This is the most common sensitivity output in investment banking:

1. Place your implied share price formula in the top-left corner (e.g., cell B42)
2. List WACC values across the top row (e.g., 7.0% to 12.0% in 0.5% increments)
3. List terminal growth rates down the left column (e.g., 1.5% to 3.5% in 0.5% increments)
4. Select the entire table range
5. Go to Data > What-If Analysis > Data Table
6. Set the Row input cell to your WACC cell
7. Set the Column input cell to your terminal growth rate cell

The result: a matrix showing implied share prices across every combination of WACC and growth rate.

Additional Sensitivity Tables Worth Building

Sensitivity Pair	Why It Matters
Revenue growth vs. EBIT margin	Tests operating leverage assumptions
CapEx % vs. revenue growth	Shows how capital intensity affects value
Exit multiple vs. WACC	Combines both terminal value methods
Revenue growth vs. WACC	Isolates the growth-discount tradeoff

Pro tip: Use conditional formatting to highlight the current base case in the sensitivity table. Apply a color scale (green for higher valuations, red for lower) to make the output presentation-ready.

For analysts who need to move beyond two-variable tables to fully probabilistic analysis, a Monte Carlo simulation in Excel runs thousands of randomized draws across all uncertain inputs simultaneously — converting the single-point DCF valuation into a probability distribution of enterprise values.

Common DCF Mistakes (and How to Fix Them)

Even experienced analysts make these errors. Here is what to watch for during model review.

1. Terminal Value Dominates Too Much

If terminal value accounts for more than 85% of your enterprise value, your explicit forecast period isn't capturing enough of the company's near-term economics. Fix: Extend the projection period to 7-10 years, or revisit whether your near-term growth assumptions are too conservative.

2. WACC and Terminal Growth Rate Are Too Close

When the spread between WACC and the perpetuity growth rate narrows below 3%, terminal value explodes. A WACC of 9% with a 3% growth rate is reasonable. A WACC of 8% with a 4% growth rate is dangerous.

3. Ignoring the Circular Reference in WACC

WACC depends on the company's market cap (equity weight), but the DCF is supposed to determine that market cap. The technically correct approach uses iterative calculations:

1. Go to File > Options > Formulas
2. Check Enable iterative calculation
3. Set maximum iterations to 100 and maximum change to 0.0001

This allows Excel to solve the circular reference where WACC feeds into EV, and EV feeds back into the equity weight in WACC.

4. Mixing Levered and Unlevered Cash Flows

If you use UFCF, discount with WACC. If you use levered free cash flow (FCFE), discount with the cost of equity only. Mixing them produces a meaningless number.

5. Forgetting the Treasury Stock Method for Diluted Shares

Stock options and RSUs dilute existing shareholders. Use the treasury stock method:

Diluted Shares = Basic Shares + In-the-Money Options - Shares Repurchased with Option Proceeds

In Excel:

=B38+MAX(0,(B39-B40)*B41/B42)

Where B39 = options outstanding, B40 = average exercise price, B41 = options count, B42 = current share price.

DCF Model Validation Checklist

Before presenting your model, run through these checks:

1. Balance sheet balances — Total Assets = Total Liabilities + Equity in every projected year
2. Cash flow reconciliation — Change in cash on the balance sheet equals net cash flow from the CF statement
3. UFCF cross-check — Calculate UFCF from both the income statement and the cash flow statement; they should match
4. Terminal value reasonableness — Implied exit multiple from the perpetuity method should be within range of trading comps (and vice versa)
5. Implied share price vs. current price — If your DCF implies a price 3x the current market price, your assumptions are likely too aggressive
6. WACC range — Should fall between 6-14% for most companies; outside this range, double-check beta and capital structure inputs
7. Revenue growth convergence — Projected growth rates should gradually converge toward the terminal growth rate by the final forecast year

These validations are designed for corporate DCF models. For capital-intensive infrastructure or energy projects financed with non-recourse debt, a project finance model uses different lender metrics — DSCR, LLCR, and PLCR — rather than share price implied multiples as its primary validation framework.

Frequently Asked Questions

What Excel functions are essential for a DCF model?

The most critical functions — many covered in our essential Excel formulas guide for analysts — are XNPV (for present value calculations with specific dates), XIRR (for implied return analysis), SUM, IF (for toggle switches between scenarios), and Data Tables for sensitivity analysis. Use XNPV over the standard NPV function because it handles irregular time periods correctly.

How many years should a DCF forecast cover?

Most DCF models project 5-10 years of explicit free cash flows. Use 5 years for stable, mature businesses and 7-10 years for high-growth companies where near-term cash flows differ materially from steady-state economics. The forecast period should be long enough that the company reaches a normalized growth rate by the terminal year.

Is the DCF model still relevant in 2026?

Absolutely. While comparable company analysis and precedent transactions provide useful market context, the DCF remains the only intrinsic valuation method that values a company based on its own fundamentals. Every major investment bank, PE firm, and equity research shop still builds DCFs as a core part of their valuation work.

How do you handle negative free cash flow in a DCF?

Negative FCF in early projection years is fine — simply discount those negative values the same way you would positive ones. The model still works as long as the company generates positive cash flow by the terminal year. If FCF is negative at the terminal year, a perpetuity growth model does not work and you should use an exit multiple instead.

What is a reasonable WACC for a DCF model?

For large-cap US companies, WACC typically ranges from 7-10%. Smaller companies or those in emerging markets may have a WACC of 10-15% due to higher beta and equity risk premiums. The risk-free rate component should match the currency of your cash flow projections (e.g., use the US 10-year Treasury for USD-denominated models).

Putting It All Together

The DCF model is the cornerstone of fundamental valuation — built on top of a solid three-statement financial model — and mastering it in Excel gives you a skill that translates directly to investment banking, equity research, private equity, and corporate finance roles. In M&A transactions, the standalone DCF valuation pairs directly with a merger model to give deal teams both an intrinsic value estimate and a pro forma EPS impact. In private equity, the same DCF output feeds directly into an LBO model in Excel, where the sponsor layers leverage onto the intrinsic enterprise value to test whether a leveraged buyout achieves their target IRR and MOIC. The key is not just knowing the formulas — it is understanding why each assumption matters and how sensitive your output is to each input.

Start with a simple model and layer in complexity as needed. A clean, well-structured DCF that uses reasonable assumptions will always beat an over-engineered model with questionable inputs.

If you want to accelerate your DCF workflow, tools like VeloraAI can help generate formula structures from natural language descriptions, catch common modeling errors, and automate sensitivity table formatting — letting you focus on the assumptions and analysis rather than the mechanical spreadsheet work.