How to Build a DCF (Discounted Cash Flow) Model in Excel

A DCF (Discounted Cash Flow) model is the gold standard for company valuation. By projecting free cash flows over a forecast period, calculating a terminal value, and discounting everything back to present value using your company's cost of capital, you arrive at its intrinsic equity value. This guide walks you through the full model structure, the math, a worked example, and the sensitivity analysis that separates a credible DCF from a numbers-game fiction.

The DCF model is the most theoretically rigorous valuation method in finance. Unlike relative valuation (comparing a company to trading multiples of its peers), a DCF builds a bottom-up forecast of the company's ability to generate cash and anchors valuation in that fundamental economic reality. For investment professionals, DCF is the default tool for merger analysis, private equity underwriting, and long-term equity research.

But the DCF is also deceptively sensitive to assumptions. A 1% change in the discount rate or a 0.5% change in the terminal growth rate can swing your valuation by 20-30%. This is why the best DCF models are built with flexibility, transparency, and sensitivity analysis baked in from the start.

The DCF Valuation Framework: From Free Cash Flow to Equity Value

Structuring the Model

A professional DCF in Excel follows a clear sheet architecture:

1. Assumptions: All input drivers (revenue growth, tax rate, WACC, terminal growth rate, etc.).
2. Income Statement Forecast: Revenue, operating margins, EBIT, and net income for the forecast period.
3. Balance Sheet Forecast: Working capital, CapEx, and depreciation schedules.
4. Free Cash Flow: Calculation of unlevered free cash flow.
5. DCF Valuation: Discount factors, present values, terminal value, and final equity value.
6. Sensitivity Analysis: Tables showing valuation across WACC and terminal growth scenarios.

The golden rule: never hardcode an assumption. Every input must live on the Assumptions sheet, and every formula on the other sheets must reference it. This allows you to run scenarios in seconds and makes the model auditable.

Forecasting Free Cash Flow (UFCF)

Unlevered Free Cash Flow (UFCF) represents the cash available to all investors—both debt and equity holders—before financing costs. The formula is:

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

Let's break each component:

EBIT (Earnings Before Interest and Taxes)

Your income statement forecast must project revenue, gross margin, and operating expenses (S&M, R&D, G&A) for the forecast period (typically 5 years).

Key assumptions:

• Revenue growth rate (often declining over time, e.g., 25% Year 1 → 15% Year 2 → 10% Year 3 → 5% Year 4–5).
• Gross margin % (typically stable unless there's a structural shift in the business).
• Operating expense ratios as a % of revenue (or fixed growth rates for mature costs).

// Year 1 Revenue (from Assumptions sheet)
= Assumptions!$B$3

// Year 2 Revenue (assuming growth rate in Assumptions B4)
= Year1_Revenue * (1 + Assumptions!$B$4)

// Gross Profit
= Revenue - (Revenue * Assumptions!$B$5)

// EBIT
= Gross_Profit - SandM - RandD - GA

Tax Rate

Apply the statutory or normalized tax rate to EBIT. For multi-jurisdictional companies, use a blended rate.

= EBIT * (1 - Assumptions!$B$10)

Depreciation & Amortization

Depreciation is a non-cash expense. It reduces taxable income but does not reduce cash. For simplicity, you can assume depreciation as a percentage of opening PP&E (e.g., 10% per year) or link it to a detailed PP&E schedule.

// Simple approach: Depreciation as % of opening PP&E
= PP&E_Opening * Assumptions!$B$15

CapEx (Capital Expenditures)

CapEx is cash spent on acquiring or maintaining fixed assets. It's typically driven as a percentage of revenue (e.g., 8% for a capital-intensive manufacturing business, 3% for a software company).

// CapEx as % of Revenue
= Revenue * Assumptions!$B$16

Change in Net Working Capital (NWC)

Working capital includes Accounts Receivable (AR), Inventory, and Accounts Payable (AP). As revenue grows, a company typically needs more AR and inventory to support sales, which ties up cash. Conversely, an increase in AP generates cash.

// Net Working Capital
= (AR + Inventory) - AP

// Change in NWC (use of cash if positive)
= NWC_Current - NWC_Prior

If the company is growing, increases in NWC are typically positive (a use of cash). In mature or declining businesses, NWC can decrease (a source of cash).

Putting It Together: UFCF

Here's a worked numerical example. Assume we're valuing a SaaS company with the following assumptions:

Forecast:

Calculating WACC

WACC (Weighted Average Cost of Capital) is the blended return required by all investors. It is the discount rate in the DCF.

WACC = (E / V) × Cost of Equity + (D / V) × Cost of Debt × (1 - Tax Rate)

Where:

• E / V: Market value of equity as a % of total firm value.
• Cost of Equity: Return required by shareholders (typically 8-12% for public companies, higher for private or early-stage).
• D / V: Market value of debt as a % of total firm value.
• Cost of Debt: Interest rate paid on the company's debt (e.g., 4% for an investment-grade company).
• Tax Rate: Reduces the effective cost of debt because interest is tax-deductible.

Cost of Equity (Using CAPM)

The Capital Asset Pricing Model (CAPM) is the standard method:

Cost of Equity = Risk-Free Rate + Beta × Market Risk Premium

• Risk-Free Rate: Yield on a 10-year government bond (e.g., 4.5% in 2026).
• Beta: Measure of the stock's sensitivity to market movements (1.0 = moves with the market; 1.5 = 50% more volatile).
• Market Risk Premium: Expected return above the risk-free rate (typically 5-6% for equities).

Example: If the risk-free rate is 4.5%, Beta is 1.2, and the market risk premium is 5.5%:

Cost of Equity = 4.5% + 1.2 × 5.5% = 4.5% + 6.6% = 11.1%

Cost of Debt

Use the weighted average interest rate on the company's debt, or for a private company, estimate based on credit rating or comparable company yields.

Example: If a company has $200M of debt with an average interest rate of 4.2%:

Cost of Debt = 4.2%

After tax (25% tax rate):

After-Tax Cost of Debt = 4.2% × (1 - 0.25) = 3.15%

Putting It Together

Assuming the SaaS company has $500M in equity and $100M in debt:

Terminal Value (Gordon Growth vs. Exit Multiple)

The terminal value represents the company's value at the end of the forecast period, extending to infinity. This is often 60-80% of the total DCF value, so getting it right is critical.

Gordon Growth Method

The Gordon Growth formula assumes the company grows at a constant rate forever:

Terminal Value = UFCF_Year5 × (1 + Terminal Growth Rate) / (WACC - Terminal Growth Rate)

For the SaaS example (Year 5 UFCF = $23.2M, WACC = 9.6%, terminal growth = 2.5%):

Terminal Value = $23.2M × (1 + 0.025) / (0.096 - 0.025) = $23.2M × 1.025 / 0.071 = $336.6M

Key insight: The terminal growth rate must be realistic. For mature companies, 2-3% (roughly GDP growth) is standard. Do not use high terminal growth rates (>5%) unless there's a compelling structural reason.

Exit Multiple Method

Alternatively, assume the company is sold at a future date at a market multiple:

Terminal Value = UFCF_Year5 × Exit Multiple

Or using a more common metric (EBITDA):

Terminal Value = EBITDA_Year5 × EV/EBITDA Multiple

If the SaaS company is projected to have $35.5M EBITDA in Year 5 (EBIT + Depreciation) and you assume a 12x EV/EBITDA exit multiple (typical for SaaS):

Terminal Value = $35.5M × 12 = $426M

Which Method to Use?

Both are defensible. The Gordon Growth method is more conservative and theoretically sound for mature companies. The Exit Multiple method is useful if you have strong market comparables. The best practice is to calculate both and present a range.

Discounting to Present Value

Each year's free cash flow is discounted back to present value using the WACC:

Present Value of UFCF = UFCF_Year / (1 + WACC) ^ Year

The discount factor for Year 5 at 9.6% WACC is:

Discount Factor Year 5 = 1 / (1.096) ^ 5 = 0.6568
PV of Year 5 UFCF = $23.2M × 0.6568 = $15.2M

Complete Discounting Schedule:

Terminal Value at End of Year 5 (Gordon Growth): $336.6M

PV of Terminal Value = $336.6M × 0.6321 = $212.8M

Enterprise Value:

Enterprise Value = PV of UFCF (Years 1-5) + PV of Terminal Value
Enterprise Value = $66.0M + $212.8M = $278.8M

Alternatively, using the Exit Multiple terminal value:

PV of Terminal Value = $426M × 0.6321 = $269.3M
Enterprise Value = $66.0M + $269.3M = $335.3M

Bridge from Enterprise Value to Equity Value

Enterprise Value is the value of the entire firm, available to all investors. To arrive at equity value (per shareholders), adjust for net debt:

Equity Value = Enterprise Value - Net Debt

Where:

Net Debt = Total Debt - Cash and Cash Equivalents

For the SaaS company, assume:

• Total Debt: $100M
• Cash: $25M
• Net Debt: $100M - $25M = $75M

Gordon Growth Scenario:

Equity Value = $278.8M - $75M = $203.8M

Exit Multiple Scenario:

Equity Value = $335.3M - $75M = $260.3M

Valuation Range: $203.8M–$260.3M (or a mid-point of ~$232M).

If the company has 10M fully diluted shares outstanding:

Implied Valuation per Share (Gordon Growth) = $203.8M / 10M = $20.38
Implied Valuation per Share (Exit Multiple) = $260.3M / 10M = $26.03

Sensitivity Analysis

A single DCF valuation is not credible without sensitivity analysis. Create a two-way sensitivity table that shows how the valuation changes with different WACC and terminal growth rate assumptions.

The WACC–Terminal Growth Sensitivity Matrix

Each cell shows the enterprise value at that intersection. The highlighted row/column represents your base-case WACC (9.6%) and terminal growth (2.5%), yielding ~$294M enterprise value.

Interpreting the Sensitivity Table

• High WACC (10.6%), Low Terminal Growth (1.5%): Conservative scenario, $195M valuation.
• Low WACC (8.6%), High Terminal Growth (3.5%): Optimistic scenario, $561M valuation.
• Base Case (9.6% WACC, 2.5% terminal growth): $294M valuation.

The range from conservative to optimistic is nearly 3x, which illustrates why sensitivity analysis is essential. Always present your valuation as a range, not a single point estimate.

Common Mistakes to Avoid

1. Over-Optimistic Revenue Growth: Projecting 40% revenue growth indefinitely. Real companies eventually mature. Use declining growth rates (e.g., 25% → 15% → 10% → 5%).
2. Inconsistent Margin Assumptions: Growing revenue 20% but keeping operating margins at 30% in perpetuity. As companies scale, some costs become fixed (G&A), so margins typically expand or stabilize, not balloon indefinitely.
3. Forgetting Changes in Working Capital: If your company is growing, AR and inventory grow proportionally, tying up cash. Failing to account for this understates CapEx and overstates UFCF.
4. Circular Reasoning on WACC: Using an excessively low WACC (e.g., 6%) based on a hypothetical capital structure the company doesn't actually have. Estimate WACC based on real, current market conditions or the target capital structure post-acquisition.
5. Unrealistic Terminal Growth Rates: Assuming 4-5% terminal growth in a developed market (GDP growth is 2-3%). High terminal growth rates overstate valuation significantly.
6. Ignoring Comparable Valuations: Getting a $500M DCF valuation for a company trading at $100M EV/Revenue when peers trade at 5x revenue. Sanity-check your DCF against trading multiples. If you get a significantly different answer, investigate why.
7. Static Terminal Value: Using only the Gordon Growth method without testing an Exit Multiple scenario. Both methods are valid; showing a range is more credible.