A discounted cash flow values a business by projecting future cash flows and discounting them back to today. In practice, an unlevered DCF uses free cash flow before financing decisions and discounts those flows at the weighted average cost of capital. Done well, a DCF in Excel is fast to review, easy to audit, and strong enough to anchor investment debate.
When a DCF fits and how long to forecast
A DCF is best for operating companies where enterprise value stems from pre-financing cash flows. For banks and insurers, dividend discount or residual income methods fit better, because leverage is an operating choice. For pre-revenue biotech or binary outcomes, consider scenario trees or real options. Picking the right approach preserves credibility and avoids false precision.
Work in nominal terms and keep discount rates and cash flows in the same units. Mixing real and nominal inputs quietly breaks valuation math. Forecast five to ten years and keep the terminal value under roughly 80 percent of enterprise value. If most of the value sits in the terminal, extend the horizon or simplify assumptions until the split looks reasonable.
Set up a clean workbook
A compact architecture speeds review and limits errors. Use a small number of clearly labeled tabs and consistent formatting so anyone can follow the flow in minutes.
- Tabs that matter: 00_Cover, 01_Assumptions, 02_IS, 03_CF_UFCF, 04_WACC, 05_DCF, 06_Sens, 07_Output. Fewer tabs reduce review time.
- Colors and checks: Blue inputs, black formulas, green external links, red checks. Only hardcode 1s and 0s in formulas to improve error control.
- One anchor date: Put the valuation date in Assumptions!B3 and drive all period-end dates and discount factors from that cell to prevent timing drift.
- Clear units: State currency and units at the top of each sheet and keep them consistent across tabs.
Build years across columns and items down rows. Add explicit period-end dates in the header row, such as 12/31/2025 onward. Dates enable XNPV, XIRR, and stub periods without hacks. Use the mid-year convention unless seasonality or fiscal-year cash spikes argue against it.
Build operating forecasts that tie to cash
Keep the income path simple and transparent
Start with a light income statement: Revenue, COGS, SG&A, R&D if material, D&A, and EBIT. Tax EBIT to get NOPAT. Then compute unlevered free cash flow as NOPAT plus D&A minus capex minus the change in net working capital. Keep non-operating items out of the unlevered path so you signal value drivers, not noise.
Use a simple revenue formula driven by growth rates and let margins glide to a realistic terminal level over several years. Margin-based costs keep the first build fast and still realistic. For stock-based compensation, pick a policy and stay consistent. Either treat it as a cash expense in free cash flow or add it back and reflect dilution or a higher required return in the cost of equity. Consistency avoids double counting.
Cash conversion that holds up in diligence
- Depreciation and amortization: Link D&A to the income statement. If there is no PP&E roll-forward, model D&A as a percent of revenue or a lagged percent of gross PP&E. Flag long stretches where D&A diverges from capex.
- Working capital: Define operating NWC as AR + Inventory + Other operating current assets minus AP and other operating current liabilities. Exclude cash, debt, and financing items. Model days on revenue and COGS. Static days are acceptable if openings are not available.
- Capex intensity: Drive capex as percent of revenue or relative to revenue or PP&E. Split maintenance versus growth only when you have defensible evidence.
Model cash taxes with a steady cash tax rate that converges if effective and statutory differ. If net operating losses matter, track an NOL balance and switch to cash taxes once the NOLs are consumed. Do not try to value NOLs beyond actual forecast tax relief.
Keep signs consistent. Inflows are positive and outflows negative. Avoid flipping signs mid-model, especially where functions such as XNPV expect clear directionality.
If you later graduate to a full three-statement model, ensure the unlevered free cash flow roll-up agrees with changes in the cash balance after financing items are stripped out.
Price capital correctly with a disciplined WACC
Compute the cost of equity with CAPM: risk-free rate plus beta times the equity risk premium. Add specific premia only with evidence and apply them consistently across peers. For beta, use a service estimate or unlever and relever peer betas. Unlever using βU = βE / [1 + (1 – tax) × D/E] and relever at the target capital structure, not the current point-in-time debt mix.
Use the current marginal pre-tax borrowing rate or a rating curve for the cost of debt, then tax-effect it. Set weights to the long-run target capital structure or peer medians and exclude non-interest-bearing liabilities. The WACC sums the weighted costs across equity, debt, and any preferred stock.
If you want a deeper dive on choosing a defensible discount rate, this guide is a useful complement: How to Calculate the Right Discount Rate for Your DCF Analysis.
Choose and sanity check terminal value
Pick one of two primary methods and reconcile them for credibility.
- Perpetuity growth: Set terminal value to UFCF in n+1 divided by WACC minus g. Keep g aligned with long-run nominal growth for the company’s footprint and maintain a clear spread to WACC.
- Exit multiple: Tie the multiple to enterprise metrics such as EBITDA. Anchor on peer medians and adjust for your terminal margin and growth. Back-solve the implied g to ensure the multiple does not assume implausible growth.
Glide growth down in the last forecast years to avoid a cliff. For Gordon, link reinvestment to growth with terminal ROIC so that capex and working capital remain honest in the steady state.
Discounting and timing that avoid errors
Use XNPV with dated cash flows. Provide a valuation date and period-end dates. XNPV handles stubs and avoids common timing mistakes. For mid-year convention, discount each period by 1 divided by (1 + WACC) to the power of t – 0.5. If you use dates with XNPV, you can approximate mid-year by shifting dates to mid-period. For the terminal under mid-year, treat the terminal cash flow as landing halfway through the year after the final forecast period and discount accordingly.
Put a date row at the top of the DCF sheet and the cash flows below. XNPV expects positive inflows and negative outflows. Use XIRR to reconcile sponsor IRR with entry and exit flows and to connect a DCF to target MOIC. XNPV is safer than manual factors when the valuation date is off-cycle.
Bridge enterprise value to equity cleanly
Start with EV from the DCF, add non-operating assets, and subtract net debt, preferred, and minority interest to get to equity. Treat associates consistently. Net debt equals interest-bearing debt at par minus cash available to equity. Adjust for restricted or trapped cash. Subtract unfunded pensions, environmental provisions, and other quasi-debt not in the cash flows. Add tax assets not used in the forecast.
If leverage and amortization matter, build a simple debt schedule on a separate sheet instead of burying financing math inside the DCF tab.
Sensitivities, scenarios, and review discipline
Keep decision focus by limiting the grid to the two or three drivers that move value. A two-way table on WACC and terminal growth or exit multiple is standard. Show it prominently on the Output tab with clear highlights on the base case. For structured pulls, consider sensitivity tables driven by INDEX or XLOOKUP and a Base/Upside/Downside toggle.
Quality checks save time. Monitor that D&A does not exceed capex for years unless the asset base is shrinking. Ensure working capital as a percent of revenue stabilizes into terminal. Rebuild free cash flow from EBITDA minus tax on EBIT plus or minus working capital and capex as an independent test. Perform kill tests: if terminal growth exceeds long-run nominal GDP of the footprint, revise. If value is over 90 percent terminal, extend the forecast or normalize earlier years. If the DCF implies a multiple far from peers while assuming superior growth and margins, revisit discount rate and reinvestment.
For common pitfalls and how to avoid them, see this concise checklist: Avoiding Common Mistakes in DCF Valuation Models.
Excel implementation that survives diligence
Organize blocks with fixed row anchors and dedicated headers to prevent cell drift. Name core inputs and ranges so formulas read like sentences. Use Data Validation for drop-downs to reduce typos. Keep formulas short and break multi-step logic across rows. SUMPRODUCT works well for vector discounting. Add a Checks area with TRUE or FALSE flags such as WACC greater than g, no negative denominators, terminal share under threshold, and no error values. Small touches like Excel shortcuts and consistent print setup speed reviews.
Governance matters. Stamp the file with version, date, and author. Track assumption changes and approvals. Protect formulas on core tabs after base-case sign-off and leave a sandbox for ad hoc sensitivities. Store market input sources in the workbook and keep dated PDFs in the deal folder. On closeout, archive the model with index and change log and follow your retention and legal hold process.
Field-tested build sequence and a tiny example
Follow a sequence that front-loads assumptions, then flows to value with minimal rework.
- Step 1: Fill the Assumptions tab with valuation date, currency, units, scenarios, risk-free, equity risk premium, tax, target capital structure, and terminal method.
- Step 2: Enter three to five years of historical revenue, margins, D&A, capex, and working capital days and freeze them.
- Step 3: Project revenue and margin paths and converge by years five to eight.
- Step 4: Build working capital and capex. Ensure days and capex intensity converge and asset turnover stays reasonable.
- Step 5: Compute unlevered free cash flow and sum across years to catch sign slips.
- Step 6: Build WACC and lock market inputs with sources and as-of dates.
- Step 7: XNPV the free cash flows and terminal value, then bridge to equity.
- Step 8: Add sensitivities on WACC versus g or exit multiple and a Base/Upside/Downside scenario set.
- Step 9: Populate the Output tab with EV, equity value, per share, implied multiples, WACC and g, and the sensitivity panels.
- Step 10: Run checks and print preview. Fit to two or three pages with readable fonts and clear headers.
Minimal illustration: Revenue grows from 1,000 to 1,100 in Year 1. COGS at 60 percent yields gross profit of 440. SG&A at 20 percent yields EBIT of 240. Tax at 25 percent gives NOPAT of 180. D&A of 50, capex of 60, and a 10 increase in working capital produce free cash flow of 160. With WACC at 9 percent and mid-year, the Year 1 discount factor is about 0.957 and the present value is about 153. For a terminal with g at 2.5 percent and UFCF in Year 6 of 200, the terminal at the end of Year 5 is 3,154 and should be discounted with exponent 5 – 0.5 to align timing.
Cross-checks, peers, and market alignment
Reconcile your DCF enterprise value to forward trading multiples. Large gaps demand a clear reason: growth, returns on capital, or risk. For deals, compare to precedent transactions after adjusting for control, synergies, and cost of capital differences. Run a reverse DCF from the current price to infer the growth and margin path the market embeds and compare that to your forecast. When leverage interacts with reinvestment or covenants, consider a full debt schedule or a focused working capital schedule to tighten the operating path.
Fresh idea: the Reviewer-in-10 test
Before sharing the model, pretend you have 10 minutes to explain it to a critical reviewer. Can they find the valuation date in one click, trace revenue to free cash flow in three clicks, and locate the WACC inputs and sources in one tab? If not, rename tabs, surface inputs on the Assumptions page, add a summary block to the Output tab, and tighten labels. This tiny ritual often saves an hour of Q&A later.
Closing Thoughts
A simple, investment-banking-style DCF is a disciplined forecast of unlevered cash flows discounted with a transparent cost of capital and bridged cleanly to equity. It trades ornamentation for auditability and speed. Built this way, the model becomes a steady platform for investment debate when the answer matters more than the architecture.