DCF Model Excel: Build a Discounted Cash Flow Valuation Workbook Step by Step

What Is a DCF Model and Why Build It in Excel?

A DCF model in Excel is best built as a workbook of separate sheets (Inputs, Historicals, Projections, WACC, Valuation, Sensitivity, Checks) so assumptions, calculations, and outputs stay apart and auditable. The model projects free cash flow to the firm, discounts it at WACC, adds a discounted terminal value, and bridges enterprise value to value per share. This guide covers that workbook structure step by step.

See it in actionMarketXLS video

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos

A discounted cash flow (DCF) model estimates the intrinsic value of a business by projecting the cash it will generate over a forecast horizon and discounting those future cash flows back to today's dollars using a risk-adjusted rate. The result is a single "fair value" estimate you can compare against the current market price to decide whether a stock appears over- or undervalued.

Excel suits DCF work because its grid maps to multi-year projections, it can iterate circular WACC calculations, and its data tables, named ranges, and conditional formatting handle sensitivity analysis. With MarketXLS, historical fundamentals and market inputs come into cells as functions, so the model updates when you refresh instead of being retyped.

The sections below cover sheet architecture, data sourcing, projection mechanics, discount-rate calculation, terminal value, sensitivity analysis, error checks, and how the MarketXLS MCP connector can support the research phase.

Anatomy of a DCF Workbook: Sheet Structure and Data Flow

A DCF workbook should put inputs, calculations, and outputs on dedicated sheets so that no formula mixes an assumption with a calculation.

Recommended sheet layout:

Sheet Name	Purpose
`Inputs`	Ticker, forecast assumptions, WACC components
`Historicals`	5–10 years of pulled financial data
`Projections`	Forecast income statement and cash flow statement
`WACC`	Cost of equity, cost of debt, capital structure
`Valuation`	DCF bridge, terminal value, equity value per share
`Sensitivity`	Two-variable data tables for WACC and growth rate
`Checks`	Automated error flags and balance-sheet reconciliations

Keep all hard-coded assumptions on the Inputs sheet and reference them everywhere else with named ranges. You can then change one growth-rate cell and the whole model recalculates, with no constants buried inside formulas.

Step 1 – Pull Fundamental Inputs with MarketXLS

The Historicals sheet holds 5 to 10 years of financial statement data. MarketXLS historical fundamental functions (the hf_ family) return one figure per cell for a ticker and fiscal year, so you do not copy numbers from financial websites by hand.

Key data points to retrieve for the Historicals sheet:

Revenue (last 5–10 fiscal years)
Operating income (EBIT)
Depreciation and amortization (D&A)
Capital expenditures (CapEx)
Changes in net working capital
Total debt and cash (for the equity bridge)
Shares outstanding (diluted)
Effective tax rate

Example MarketXLS functions for the Historicals sheet (Symbol, fiscal Year, optional Quarter and TTM arguments):

=hf_revenue("AAPL", 2024)
=hf_Operating_Income("AAPL", 2024)
=hf_Depreciation_Amortization_and_Accretion("AAPL", 2024)
=hf_Capital_Expenditure("AAPL", 2024)
=hf_Total_Debt("AAPL", 2024)

Formula documentation: hf_revenue, hf_Operating_Income, hf_Depreciation_Amortization_and_Accretion, hf_Capital_Expenditure, hf_Total_Debt

Point the ticker and year at cells (for example =hf_Capital_Expenditure($B$1, C$3)) and fill across. Each cell stays linked to the data source, so a refresh updates every historical figure and the projections recalculate. On the Microsoft 365 add-in (Excel for Mac and the web), prefix functions with mxls.; some functions differ by platform, so check /formulas.

Practical tip: Lock the historical range after you confirm the data. Use Excel's IFERROR wrapper around every MarketXLS call so that a temporary data outage returns a blank rather than a #VALUE! error that cascades through your projection formulas.

Step 2 – Project Free Cash Flows

Free cash flow to the firm (FCFF) is the quantity you will discount. The standard formula is:

FCFF = EBIT × (1 – Tax Rate) + D&A – CapEx – ΔNWC

Where ΔNWC is the change in net working capital (an increase in NWC is a cash outflow).

Building the projection engine:

Anchor to the last historical year. Your first forecast year should reference the final column of your Historicals sheet, not a hard-coded number.
Drive revenue with a growth rate. Place annual growth-rate assumptions in the Inputs sheet. A typical model uses a higher near-term rate (years 1–3) that tapers to a long-run rate (years 4–10).
Model margins explicitly. Rather than growing EBIT directly, project EBIT margin as a percentage of revenue. This makes your assumptions transparent and easier to stress-test.
Tie D&A and CapEx to revenue or assets. A common approach is to express CapEx as a percentage of revenue and D&A as a percentage of gross PP&E.
Estimate NWC as a percentage of revenue. The change in NWC each year is then the difference between consecutive NWC balances.

Example projection structure (columns = fiscal years):

Row 10: Revenue          = Prior Year Revenue × (1 + Growth Rate)
Row 11: EBIT Margin      = [Inputs!B5]  ← named range "ebit_margin_yr1"
Row 12: EBIT             = Revenue × EBIT Margin
Row 13: NOPAT            = EBIT × (1 – Tax Rate)
Row 14: D&A              = Revenue × [Inputs!B8]
Row 15: CapEx            = Revenue × [Inputs!B9]
Row 16: ΔNWC             = NWC_t – NWC_(t-1)
Row 17: FCFF             = NOPAT + D&A – CapEx – ΔNWC

Formula documentation: Revenue

Keep every formula in Row 17 identical across columns so you can audit the logic by inspecting a single cell.

Step 3 – Calculate the Discount Rate (WACC)

The weighted average cost of capital (WACC) is the rate at which you discount future cash flows. Errors here have an outsized impact on valuation, so build the WACC sheet carefully.

WACC formula:

WACC = (E/V) × Ke + (D/V) × Kd × (1 – Tax Rate)

Where:

E = market value of equity (shares outstanding × current price)
D = market value of debt (book value is often used as a proxy)
V = E + D
Ke = cost of equity (via CAPM or a build-up method)
Kd = pre-tax cost of debt (yield on outstanding bonds or interest expense / total debt)

Cost of equity via CAPM:

Ke = Risk-Free Rate + Beta × Equity Risk Premium

Risk-free rate: Use the current yield on a 10-year government bond. =TreasuryRate10y() returns the 10-year US Treasury rate (sourced from FRED), so your WACC updates when you refresh.
Beta: =Beta("AAPL") returns the stock's beta from MarketXLS. Consider using a 2-year weekly or 5-year monthly beta depending on the company's history.
Equity risk premium (ERP): Use a published estimate (e.g., Damodaran's annual ERP) and store it as a named range in Inputs.

Practical tip: Build a small table on the WACC sheet that shows how WACC changes across a ±1% range of the risk-free rate. This doubles as a quick sanity check and feeds your sensitivity analysis.

Step 4 – Discount Cash Flows and Compute Terminal Value

With projected FCFFs and a WACC in hand, the Valuation sheet performs three tasks: discounting the explicit forecast period, estimating terminal value, and bridging from enterprise value to equity value per share.

Discounting the forecast period:

=FCFF_Year_N / (1 + WACC)^N

Sum the present values of years 1 through 10 (or your chosen horizon) using Excel's NPV function or a manual sum of discounted cash flows. The manual approach is more transparent for auditing.

Terminal value (Gordon Growth Model):

TV = FCFF_(N+1) / (WACC – g)

Where g is the perpetuity growth rate—typically set between long-run GDP growth and inflation (1%–3% for a mature company). The terminal value is then discounted back to today:

PV of TV = TV / (1 + WACC)^N

Enterprise-to-equity bridge:

Enterprise Value  = PV of FCFFs + PV of Terminal Value
Equity Value      = Enterprise Value – Net Debt + Cash
Equity Value/Share = Equity Value / Diluted Shares Outstanding

Pull net debt and diluted shares from your Historicals sheet (sourced via MarketXLS) so this bridge updates automatically when you refresh.

Step 5 – Build a Sensitivity Table

No single DCF output should be trusted in isolation. A two-variable data table on the Sensitivity sheet shows how the implied share price changes across a range of WACC and terminal growth rate combinations.

Setting up the data table in Excel:

In a blank area of the Sensitivity sheet, place your base-case equity value per share in the top-left corner cell (e.g., B2), referencing the formula from Valuation.
List WACC values (e.g., 7%, 8%, 9%, 10%, 11%) down column A starting at A3.
List terminal growth rates (e.g., 1%, 1.5%, 2%, 2.5%, 3%) across row 2 starting at B2.
Select the full table range (A2:F7), go to Data → What-If Analysis → Data Table, set the row input cell to your WACC named range and the column input cell to your terminal growth rate named range.
Apply a color scale (green = above current price, red = below) via conditional formatting.

The sensitivity table shows how much the value per share depends on its two most uncertain inputs, which is usually the page colleagues or clients look at first.

Data Quality Checks and Common Errors

A Checks sheet with automated flags catches the most common DCF errors before anyone reads the output.

Essential checks:

Historical balance sheet: Does Assets = Liabilities + Equity for every year? Flag any year where the difference exceeds a rounding threshold.
FCFF sign: Flag years where FCFF is negative without a clear explanation (e.g., heavy CapEx investment phase). Negative FCFF is not inherently wrong, but it deserves a note.
WACC vs. growth rate: If g ≥ WACC, the Gordon Growth Model produces a negative or infinite terminal value. Use =IF(g>=WACC,"ERROR: g ≥ WACC","OK") to catch this.
Circular references: WACC calculations that use market-cap-based weights can create circularity if the share price feeds back into the model. Use iterative calculation (File → Options → Formulas → Enable Iterative Calculation) or break the loop with a prior-period price.
Data freshness: Add a cell that displays the timestamp of the last MarketXLS data refresh. If the date is stale, flag it in red.

Common modeling errors to avoid:

Discounting terminal value at the wrong exponent (use year N, not year N+1)
Mixing fiscal-year and calendar-year data without adjustment
Using book-value equity weights instead of market-value weights in WACC
Forgetting to subtract minority interest or add equity investments in the bridge
Double-counting cash that is already embedded in NWC

Extending the Model with MarketXLS AI and MCP Workflows

The MarketXLS Model Context Protocol (MCP) connector gives compatible AI assistants access to MarketXLS financial data tools, which helps with the research and assumption-setting that happens before you build the Excel model.

Example AI-assisted workflow:

Ask your AI assistant (connected via the MarketXLS MCP) to summarize a company's revenue growth trajectory, margin trends, and capital-allocation history over the past five years. The assistant pulls structured fundamental data through the connector and returns a summary with supporting figures.
Use the summary to calibrate assumptions. If the AI surfaces that CapEx as a percentage of revenue has averaged 8% over five years with low variance, you have a defensible anchor for your projection.
Request comparables. Ask the assistant to retrieve WACC components—beta, debt yield, capital structure—for a peer group. Use the peer median as a cross-check on your own WACC estimate.
Hand off to Excel. Once assumptions are validated conversationally, enter them into the Inputs sheet. MarketXLS functions then populate Historicals automatically, and the model calculates from there.

In this workflow the AI assistant handles research and narrative, and Excel handles the structured calculation. Check any figure the assistant reports against the MarketXLS functions in your workbook before you rely on it.

You can also ask the assistant to run DCF-style calculations directly, but a structured Excel workbook is easier to audit and to run scenarios on, which matters for formal valuation work.

Frequently Asked Questions

How many years should I forecast explicitly? Most practitioners use a 5- to 10-year explicit forecast period. Use a shorter horizon for stable, mature businesses and a longer one for high-growth companies where near-term cash flows are a small fraction of total value.

Should I use FCFF or FCFE in my DCF? FCFF (free cash flow to the firm) is discounted at WACC and produces enterprise value. FCFE (free cash flow to equity) is discounted at the cost of equity and produces equity value directly. Both are valid; FCFF is more common because it separates operating performance from financing decisions.

What terminal growth rate is appropriate? A perpetuity growth rate above long-run nominal GDP growth is difficult to justify, because it implies the company eventually becomes larger than the entire economy. For most companies, a range of 1%–3% is reasonable. Use your sensitivity table to show how the valuation changes across this range.

How do I handle negative FCFF in early years? Negative FCFF is acceptable for growth-stage companies investing heavily in CapEx or working capital. Discount the negative values normally. They reduce the sum of present values, which is economically correct.

Can I use MarketXLS to refresh the model automatically? Yes. MarketXLS functions recalculate when Excel recalculates or you refresh, which updates historical figures and market-based inputs such as the share price for WACC weights and the risk-free rate. On the Standard plan, US stock quotes are 15-minute delayed; that is fine for a valuation model.

What is the biggest source of error in a DCF? Terminal value usually makes up the majority of enterprise value in a DCF, so the terminal growth rate and WACC are the most consequential assumptions. Small changes in either one produce large swings in the output, which is why the sensitivity table is essential.

Is a DCF model sufficient for investment decisions? A DCF is one input among many. Cross-check your intrinsic value estimate against comparable-company multiples (EV/EBITDA, P/E), precedent transactions, and qualitative factors such as competitive moat, management quality, and industry dynamics. No single model should drive a decision in isolation.