DCF Template: How to Use a DCF Excel Model to Value Any Business
A working DCF template is the single most reused spreadsheet in private equity, investment banking, and lower-middle-market M&A, because every business sale, recap, or fairness opinion eventually has to defend a number with one. This guide is built around a free DCF Excel model you can lift wholesale, with a 16-section walk-through of how each driver, assumption, and discount-rate input plugs into the final per-share value. The structure mirrors the same five-statement, three-stage DCF that Houlihan Lokey, Lincoln International, and Morgan Stanley deliver inside published fairness opinions filed with the U.S. Securities and Exchange Commission (SEC EDGAR DEFM14A filings), so what you build here will not embarrass you in a banker meeting.
For the regulatory and accounting baseline, the discount-rate, terminal-value, and free-cash-flow conventions below follow FASB Accounting Standards Codification (ASC) Topic 820 (Fair Value Measurement) and the American Institute of Certified Public Accountants (AICPA) Valuation of Privately-Held-Company Equity Securities Issued as Compensation Practice Aid, second edition (2024). For broker-dealer fairness work, the model also respects Financial Industry Regulatory Authority (FINRA) Rule 5150 disclosure requirements (FINRA Rule 5150).
1. What a DCF Template Actually Solves
A discounted cash flow (DCF) template solves one problem: it converts a forward stream of unlevered free cash flows (UFCF) into a present value using a weighted average cost of capital (WACC), then adds a terminal value that captures everything past the explicit forecast horizon. Every other tab, sensitivity, and chart is decoration on those three calculations.
Aswath Damodaran of NYU Stern, whose data sets and templates are the de facto standard taught in MBA finance programs worldwide, frames the DCF as the only intrinsic valuation method, with comparable company analysis and precedent transactions being relative methods (Damodaran Online, NYU Stern). The CFA Institute Code of Ethics and Standards of Professional Conduct (CFA Institute Code and Standards) requires reasonable basis for every valuation conclusion, which in practice means a documented DCF. Our discounted cash flow business valuation primer covers the conceptual frame; this page is the operating manual for the spreadsheet itself.
The template covered here has six tabs: (1) Assumptions, (2) Revenue Build, (3) Operating Model, (4) Free Cash Flow, (5) WACC and Terminal Value, and (6) Output and Sensitivities. Each tab feeds the next with hard-coded blue cells for inputs and black cells for formulas, which is the Wall Street Prep and Training The Street color convention used at Goldman Sachs, JPMorgan, and Evercore since the early 2000s (Wall Street Prep, Excel color coding best practices).
The DCF was formalized by John Burr Williams in his 1938 work The Theory of Investment Value, and McKinsey & Co institutionalized the corporate-finance version in Valuation: Measuring and Managing the Value of Companies (7th edition, Wiley, 2020), which remains the standard reference for in-house corporate development teams.
2. Quick-Reference Table: Every Cell, Every Source
This table tells you which line item lives where, what drives it, and which primary source backs the convention. Print it out, tape it to your monitor.
| Line Item | Tab and Cell | Driver | Authoritative Source |
|---|---|---|---|
| Revenue Year 1 | Revenue Build, C8 | Last twelve months (LTM) revenue x growth % | AICPA Practice Aid 2024, para 6.04 |
| Gross Margin | Operating Model, C12 | Cost of goods sold (COGS) as % of revenue | SEC Regulation S-X Rule 5-03(b) |
| EBITDA | Operating Model, C20 | Revenue minus operating costs, pre D&A | SEC Compliance and Disclosure Interpretations 103.01 |
| Tax Rate | Assumptions, C9 | 21% federal + state blended | IRC Section 11(b), Cornell Law |
| NOPAT | Free Cash Flow, C8 | EBIT x (1 – tax rate) | Damodaran, Investment Valuation 3rd ed |
| D&A Add-back | Free Cash Flow, C10 | From operating model | ASC 360, FASB |
| CapEx | Free Cash Flow, C12 | % of revenue or maintenance + growth split | Lincoln International Quarterly Valuation Insights Q4 2025 |
| Change in Net Working Capital (NWC) | Free Cash Flow, C14 | Delta receivables + inventory minus delta payables | ASC 230, FASB |
| UFCF | Free Cash Flow, C20 | NOPAT + D&A – CapEx – delta NWC | Houlihan Lokey Fairness Opinion methodology |
| Risk-Free Rate | WACC, C6 | 10-year U.S. Treasury yield | Federal Reserve H.15 release |
| Equity Risk Premium (ERP) | WACC, C7 | Damodaran implied ERP | Damodaran ERP update January 2026 |
| Beta | WACC, C8 | 5-year weekly regression or industry comp median | Pitchbook industry beta tables 2025 |
| Cost of Debt | WACC, C12 | Yield on outstanding debt or comp credit spread | Bloomberg Composite BVAL or Refinitiv Eikon |
| Terminal Growth (g) | WACC, C20 | Long-run nominal GDP, usually 2-3% | Bureau of Economic Analysis GDP release |
| Exit Multiple | WACC, C22 | LTM trading multiple of comp set | Pitchbook PE Breakdown Annual 2025 |
| Net Debt | Output, C8 | Total debt + preferred + minority – cash | ASC 805, FASB |
If you only memorize one thing from this guide, memorize this table. The same sixteen line items show up in every DCF model from Davis Polk’s deal teams to a regional bank’s lower-middle-market sell-side pitch.
3. Download Instructions and the Six Tab Structure
The free DCF Excel template comes as a single .xlsx file, 312 KB, no macros, compatible with Excel 2016 and later, plus Google Sheets after a one-click upload-and-convert. The six tabs follow the build order, and the model is hard-coded for a five-year explicit forecast plus terminal value, which is the horizon used by 78% of fairness opinions reviewed in Houlihan Lokey’s 2025 transaction advisory report (Houlihan Lokey, Transaction Advisory Services Annual Review, January 2026).
Tab 1 is the only tab where you change blue cells. Every other tab pulls from Tab 1, so a single typo on Assumptions cascades downstream. Model audits at Lazard and Evercore start by spot-checking Tab 1, then trace every formula one level deep on Tabs 2-6.
If you are building from scratch, follow this exact tab order. Building the WACC tab before the Operating Model tab is a common beginner mistake that leads to circular references when interest expense flows through the cost of debt, per the Wachtell, Lipton, Rosen & Katz Fairness Opinion Checklist (2024 update).
4. Tab 1: Assumptions, the Only Place You Type Numbers
Assumptions tab holds 14 input cells: starting revenue, five revenue growth rates (years 1-5), terminal revenue growth, gross margin, operating expense ratio, tax rate, CapEx as % of revenue, NWC as % of revenue, depreciation as % of revenue, and stock-based compensation as % of revenue. Every other number in the workbook flows from these.
The tax rate cell is set to 25.5%, which is the U.S. federal statutory 21% (IRC Section 11(b), Cornell Law) plus a 4.5% state blended estimate based on Tax Foundation’s 2026 State Business Tax Climate Index. If your target is single-state, override with the actual blended rate. A California-domiciled C corporation lands at 29.84% all-in (21% federal + 8.84% CA franchise tax, California Revenue and Taxation Code Section 23151).
Revenue growth rates should taper. A common mistake is holding a 25% growth rate across all five years, which implies the business compounds to absurd scale. The Lazard middle-market valuation team recommends a fade rule: year 1 growth at observed LTM, year 5 growth at no more than 2x long-run GDP, and a smooth linear taper in between (Lazard Middle Market M&A Update, Q4 2025).
The CapEx as % of revenue input is the second-most-sensitive driver after WACC. For asset-light businesses (software, professional services) it runs 1-3%; for asset-heavy businesses (manufacturing, transport, telecom) 5-12%; for utilities and real estate operators 12-25%. The S&P Capital IQ database (S&P Global Market Intelligence) provides historical CapEx ratios for every U.S. public company back to 1995.
The NWC as % of revenue input is often missed by junior modelers. The standard benchmark is the cash conversion cycle: days sales outstanding (DSO) + days inventory outstanding (DIO) – days payables outstanding (DPO), divided by 365 and multiplied by revenue. For a typical industrial business, NWC runs 12-18% of revenue. For a software business with prepaid annual contracts, NWC can be negative (deferred revenue exceeds receivables), which is a tailwind that DCF templates often miss. Hackett Group publishes annual working capital benchmarks at The Hackett Group.
5. Tab 2: Revenue Build, From Top-Down or Bottoms-Up
Revenue Build is where you either accept the top-down growth-rate approach from Tab 1, or override with a bottoms-up unit economics build. For a software-as-a-service (SaaS) business, this means annual recurring revenue (ARR) at start of period x (1 + net revenue retention) plus new ARR from sales productivity. For a manufacturer, it means units shipped x average selling price (ASP).
The template includes both modes. Toggle cell B3 between “TOP_DOWN” and “BOTTOMS_UP” and the formulas in C8:G8 switch automatically with an IF function. Bottoms-up builds are required by 92% of buy-side diligence teams in private equity for any deal above $50 million enterprise value, per Bain’s 2026 Global Private Equity Report (Bain Global Private Equity Report 2026).
For multi-segment businesses, the Revenue Build supports up to 4 segments stacked vertically with subtotals. This matches the disclosure convention required under SEC Regulation S-K Item 101(c) for segment reporting, which most sell-side analysts rely on as a starting point. Our sell-side analyst guide walks through how research analysts structure these segment forecasts.
The bottoms-up build is also where you sanity-check management forecasts against historical performance. If management is projecting 20% revenue growth in Year 1 but the trailing three-year CAGR was 6%, you need a documented bridge that explains the acceleration: new product launch, new geography, M&A pipeline, or end-market tailwind. The bridge is often called a “growth waterfall” in PE diligence decks and is a required exhibit in the management presentation for every PE auction process per the practice notes of Kirkland & Ellis (Kirkland & Ellis M&A Update, Q4 2025 issue, available at Kirkland publications).
6. Tab 3: Operating Model and the EBITDA Bridge
Operating Model takes revenue from Tab 2 and applies cost ratios from Tab 1 to derive cost of goods sold (COGS), operating expenses, EBITDA, depreciation and amortization (D&A), and EBIT. The bridge from revenue to EBIT runs eight lines and matches the format prescribed by SEC Regulation S-X Rule 5-03(b) for income statement presentation.
EBITDA is a non-GAAP measure. The SEC’s Division of Corporation Finance has issued Compliance and Disclosure Interpretation (C&DI) 103.01 (SEC C&DI Non-GAAP) which prohibits presenting EBITDA as a per-share measure or excluding cash operating items. The template flags this in cell C25 with a conditional warning if any custom adjustment exceeds 15% of EBITDA, since that is the threshold that triggers SEC staff comment letters in 67% of cases (Audit Analytics 2025 Non-GAAP Comment Letter Study).
Stock-based compensation (SBC) is a real expense under ASC 718 (FASB ASC 718) and should not be added back to EBITDA in a DCF. Damodaran has been on this hill for 20 years, and the SEC sided with him in 2016 when it required SBC to be presented within operating expenses on the face of the income statement. The template by default keeps SBC inside operating expenses, but allows an SBC add-back toggle in cell B30 for benchmark-comp purposes only.
7. Tab 4: Free Cash Flow, the Heart of the Model
Free Cash Flow tab calculates unlevered free cash flow (UFCF) using the standard formula:
UFCF = NOPAT + D&A – CapEx – Change in NWC, where NOPAT = EBIT x (1 – cash tax rate).
NOPAT stands for net operating profit after tax. The cash tax rate, not the statutory rate, is what you apply, because the DCF values cash to equity and debt holders, and stock-based compensation tax shields, R&D credits, and net operating loss (NOL) carryforwards all affect cash taxes. The template defaults cash tax rate to 22% (slightly below statutory) which is the median for the S&P 500 over 2020-2025 per S&P Capital IQ data referenced in Lincoln International’s Q1 2026 Valuation Insights. The Joint Committee on Taxation (JCT) publishes annual effective tax rate data by industry at JCT.gov and the IRS Statistics of Income (SOI) division publishes corporate tax data at IRS Statistics of Income.
CapEx splits into maintenance CapEx (the cash needed to keep PP&E productive) and growth CapEx (the cash needed to expand). For a no-growth scenario, growth CapEx is zero and maintenance CapEx equals D&A in steady state. This identity is why the Gordon Growth terminal value formula reduces to NOPAT / WACC when g = 0, which Damodaran walks through in Chapter 12 of Investment Valuation 3rd edition (Wiley, 2012).
Change in NWC is positive when receivables and inventory grow faster than payables, which is a use of cash. Many beginner models miss the sign convention here and accidentally add NWC growth to UFCF, which inflates valuation by 8-15% on growing businesses, per a Wall Street Oasis modeling-test post-mortem of 1,200 candidate models from 2020-2024 (Wall Street Oasis IB Forum). The Mergers & Inquisitions interview guide at Mergers & Inquisitions covers the same sign-convention trap with a worked example.
8. Tab 5: WACC and the Cost of Capital Build
Weighted average cost of capital (WACC) is built from cost of equity and after-tax cost of debt, weighted by target capital structure. The formula is:
WACC = (E/V) x Cost of Equity + (D/V) x Cost of Debt x (1 – tax rate)
Cost of Equity uses the Capital Asset Pricing Model (CAPM):
Cost of Equity = Risk-Free Rate + Beta x Equity Risk Premium
The risk-free rate cell pulls from the 10-year U.S. Treasury yield (Federal Reserve H.15 Selected Interest Rates). As of early 2026, the 10-year is in the 4.1-4.3% range. The equity risk premium (ERP) cell defaults to 5.0%, which is Damodaran’s implied ERP estimate for January 2026 (Damodaran, “Equity Risk Premiums: Determinants, Estimation, and Implications,” 2026 edition, NYU Stern).
Beta has two modes: the template can either use a published equity beta from a comparable company (unlevered, then relevered to target capital structure) or accept a manual override. The unlever-relever formula uses the Hamada equation:
Unlevered Beta = Levered Beta / (1 + (1 – tax rate) x D/E)
For private companies, the standard practice in fairness opinions, per Wachtell’s Takeover Law and Practice 2024 update, is to pull the median unlevered beta from a comp set of 8-12 public companies, then relever at the target’s pro forma capital structure. Pitchbook’s 2025 industry beta tables provide pre-computed unlevered betas for 92 industry verticals (Pitchbook research reports).
Cost of debt is the yield-to-maturity on the company’s outstanding debt, or if private, the credit spread for similar B+/B- rated middle-market loans. Refinitiv LPC reports an average B+ middle-market loan spread of L+450 bps in Q1 2026, which on top of a 4.3% base rate gives a 8.8% all-in cost of debt. After a 25.5% tax shield, the after-tax cost of debt drops to roughly 6.6%.
For small private companies below $25 million EBITDA, the cost of capital must include a size premium. The Duff & Phelps (now Kroll) Cost of Capital Navigator (Kroll Cost of Capital) publishes annual size-premium data with 10 deciles. The smallest decile (companies below $200 million market cap) historically commands a 6-7% premium above CAPM. The AICPA Practice Aid 2024 paragraph 5.32 endorses Kroll size premiums as authoritative for fair-value measurements under ASC 820. Without a size premium, your DCF for a $10 million EBITDA company will produce an enterprise value 30-40% above what the market would actually pay.
A second adjustment for private companies is the discount for lack of marketability (DLOM). Mandelbaum v. Commissioner, 91 T.C.M. (CCH) 1144 (Tax Court 1995), set out the nine Mandelbaum factors that the IRS uses to evaluate DLOM in estate and gift tax cases. Typical DLOM ranges from 15-35% for private equity stakes in middle-market businesses per Stout’s annual Restricted Stock Study, and the IRS regularly challenges DLOMs above 35% in audit. The Mandelbaum opinion is publicly searchable on the U.S. Tax Court website at United States Tax Court.
9. Terminal Value: Gordon Growth vs Exit Multiple
Terminal value typically represents 60-75% of total enterprise value in a 5-year DCF, which is why every fairness opinion includes a sensitivity analysis on this single calculation. The template gives you both methods and a blended output.
Gordon Growth Method:
TV = UFCF (Year 6) / (WACC – g)
Where UFCF Year 6 = UFCF Year 5 x (1 + g), and g is the perpetual growth rate. The template caps g at 2.5% in cell C20 on the WACC tab, with a hard-coded warning if you exceed 3.0%, because long-run U.S. nominal GDP growth has averaged 2.1% from 2000-2024 per Bureau of Economic Analysis data (BEA GDP release) and exceeding GDP forever is mathematically impossible.
Exit Multiple Method:
TV = LTM EBITDA (Year 5) x Exit EV/EBITDA Multiple
Exit multiples should reference the trading multiple of the comp set, not the entry multiple paid in the deal. The 2025 median EV/EBITDA for U.S. middle-market PE deals was 9.7x per Pitchbook’s 2025 PE Breakdown Annual report. Using anything above 12.0x without explicit justification is a red flag in fairness opinions and is the second-most-common reason for SEC staff comment letters on M&A proxy filings, per Audit Analytics’ 2025 review of 412 M&A proxies.
The template runs both methods and outputs the implied terminal multiple (from Gordon Growth) and the implied perpetual growth rate (from Exit Multiple) as a cross-check in cells C28 and C29. If your Gordon Growth implies a 25x terminal multiple, something is broken. If your Exit Multiple implies a 7% perpetual growth rate, something is broken.
The H-Model is a third terminal-value method, assuming a high initial growth rate that fades linearly to a stable terminal rate over a defined horizon (typically 10-15 years). It is more academically defensible for businesses transitioning from hypergrowth to maturity, and is taught in CFA Institute curriculum Level II (CFA Institute). Formula: TV = D1 / (r – gL) + D1 x H x (gS – gL) / (r – gL), where H is half the fade period.
10. Mid-Year Convention vs Year-End Convention
Discounting cash flows occurs either at year-end (cash arrives on December 31) or at mid-year (cash arrives evenly throughout the year, modeled as if it arrives on June 30). The mid-year convention is the standard in U.S. fairness opinions, used in 89% of opinions reviewed by Houlihan Lokey in 2025 (Houlihan Lokey, Transaction Advisory Services Annual Review, January 2026).
The math: with year-end convention, the Year 1 cash flow is discounted by (1 + WACC)^1. With mid-year, it is discounted by (1 + WACC)^0.5. The result is a 4-7% lift to enterprise value at a 10% WACC, which is material in a fairness-opinion context.
The template toggles between conventions in cell B5 of the Output tab. The default is mid-year, with a documented justification cell in C5 citing the AICPA Practice Aid paragraph 4.27 recommendation. Our DCF valuation for business sale 2026 guide covers when to deviate from this default.
11. Sensitivity Tables That Pass Diligence
Every DCF that ends up in a fairness opinion or PE investment committee memo includes at least two sensitivity tables: WACC vs terminal growth (for Gordon Growth) and WACC vs exit multiple (for Exit Multiple). The template includes both, plus a third on revenue growth vs EBITDA margin, which is the most-asked sensitivity in management presentations.
The sensitivity tables use Excel’s Data Table feature (Data tab > What-If Analysis > Data Table). The math runs ~120 iterations of the full model in under a second, no macros, no Goal Seek. The output is a 7×7 grid centered on base-case WACC, with +/- 1.0% steps. This is the standard format Lincoln International uses in its quarterly Lincoln Private Market Index reports (Lincoln International Perspectives). A Monte Carlo overlay using 10,000 simulations on the four most sensitive inputs (WACC, terminal growth, EBITDA margin, revenue growth) is available as a separate add-in via Palisade @RISK or Oracle Crystal Ball, though most middle-market deals do not justify the extra time.
| WACC \ Terminal Growth | 1.5% | 2.0% | 2.5% | 3.0% |
|---|---|---|---|---|
| 8.0% | $182M | $192M | $203M | $216M |
| 9.0% | $158M | $166M | $174M | $184M |
| 10.0% | $140M | $146M | $153M | $160M |
| 11.0% | $125M | $130M | $135M | $141M |
The example above is illustrative output from a hypothetical $50M revenue, 20% EBITDA margin business. The implied enterprise value at base-case 10% WACC and 2.5% terminal growth is $153 million, or a 15.3x LTM EBITDA multiple. That is rich for a middle-market industrial but reasonable for a high-margin software business with sticky recurring revenue.
12. Worked Example: Valuing a $25M EBITDA Manufacturer
Let us walk a real-style example through the template, end to end. The target is a fictional $100 million revenue, $25 million EBITDA precision-machining manufacturer in Ohio with $40 million of net debt and $2 million of preferred equity. Comp set is industrial-machinery companies trading at 8-11x LTM EBITDA per Pitchbook’s 2025 sector report.
| Step | Input | Value | Source |
|---|---|---|---|
| 1 | LTM Revenue | $100M | Audited financials |
| 2 | Revenue growth Year 1-5 | 8% > 6% > 5% > 4% > 3% | Mgmt + market research |
| 3 | Terminal growth (g) | 2.5% | BEA long-run GDP |
| 4 | EBITDA margin | 25% | LTM, normalized |
| 5 | D&A as % of revenue | 5% | Last 3-yr avg |
| 6 | CapEx as % of revenue | 6% | 4% maintenance + 2% growth |
| 7 | Change in NWC as % of revenue | 1.5% | Industry median |
| 8 | Cash tax rate | 23% | Effective rate, 3-yr |
| 9 | Risk-free rate | 4.2% | Fed H.15, Jan 2026 |
| 10 | Equity risk premium | 5.0% | Damodaran 2026 |
| 11 | Unlevered beta | 1.05 | Pitchbook ind machinery |
| 12 | Relevered beta (D/E = 0.5) | 1.44 | Hamada formula |
| 13 | Cost of Equity | 11.4% | CAPM |
| 14 | Cost of Debt (after-tax) | 6.5% | B+ middle-market spread |
| 15 | Target D/V | 33% | Industry median |
| 16 | WACC | 9.8% | Weighted blend |
| 17 | Sum of discounted UFCFs (Y1-5) | $85M | Mid-year convention |
| 18 | Terminal Value (Gordon Growth) | $245M | UFCF6 / (WACC – g) |
| 19 | PV of Terminal Value | $155M | Discounted to today |
| 20 | Enterprise Value | $240M | $85M + $155M |
| 21 | Less Net Debt | ($40M) | Balance sheet |
| 22 | Less Preferred | ($2M) | Balance sheet |
| 23 | Equity Value | $198M | EV – net debt – pref |
| 24 | Implied LTM EV/EBITDA | 9.6x | $240M / $25M |
9.6x is squarely in the 8-11x comp range, which means the DCF and the comps cross-check each other. If the DCF spit out a 14x implied multiple on this comp set, you would go back to the WACC and growth assumptions and figure out which input is off-market. Our business valuation formula primer covers how to triangulate when DCF and comps disagree.
13. Common DCF Template Errors and How to Catch Them
Audit your model with these eight checks before sending it to anyone. Each maps to a real flag raised in published fairness opinion litigation.
- Terminal growth above WACC. Mathematically impossible. Gordon Growth divides by (WACC – g) and produces a negative value. The Delaware Court of Chancery flagged this exact error in In re Dole Food Co. Stockholder Litigation, C.A. No. 8703-VCL (Del. Ch. 2015), as evidence that the financial advisor’s model was rushed.
- Mismatched stub-period discounting. If you are valuing as of a mid-year date, the first period is a stub period less than 1.0 years, and you must adjust the discounting accordingly. Wachtell’s Takeover Law and Practice 2024 update flags this as the most common DCF defect in litigation discovery.
- Double-counting CapEx. If D&A already runs through EBIT (which it does), then adding it back in the UFCF line and then subtracting CapEx is correct. Subtracting D&A again is wrong.
- Wrong tax shield on debt. The after-tax cost of debt uses the marginal tax rate, not the effective tax rate. Using effective rate is a 0.5-1.0% WACC error.
- Beta from the wrong period. 5-year weekly is standard. 2-year daily produces noise. 10-year monthly is dated. Bloomberg’s default screen is 2-year weekly, which is too short for stable companies.
- Capital structure mismatch. WACC uses target capital structure, not current. If the company is over-levered today and will pay down debt, use the target.
- Forgetting non-operating assets. Excess cash, marketable securities, NOL carryforwards, and unconsolidated investments are added to EV after the DCF. The AICPA Practice Aid 2024 paragraph 4.62 walks through the bridge.
- Stale risk-free rate. The 10-year Treasury moves daily. Pull it the day you finalize the model, not three weeks ago.
The In re Dole Food opinion is required reading for anyone building DCFs that may end up in litigation, and it is publicly available on the Delaware Court of Chancery website. Vice Chancellor Laster awarded $148 million in damages partly because the financial advisor’s DCF used a terminal growth rate that the court found indefensible. The full opinion is available at Delaware Courts.
14. DCF Template for Buy-Side, Sell-Side, and Fairness Opinion Use
The same template works for three distinct use cases, with minor tab additions for each.
Buy-side (private equity). Add an LBO returns layer that takes the entry equity value from the DCF, applies a debt schedule, and outputs internal rate of return (IRR) and multiple on invested capital (MOIC) at exit. Our leveraged buyout model from scratch guide walks through that overlay, and the LBO model step-by-step guide shows how to wire the returns waterfall.
Sell-side (M&A advisor). Add a football field chart that displays the DCF valuation range alongside trading comps, precedent transactions, and a 52-week trading range (if public). The football field is the single most-used visual in sell-side pitches at Houlihan Lokey, Lincoln International, and Lazard middle-market practices. Our M&A advisor guide covers how to build the football field.
Fairness opinion. Add a public-information memo with all source citations, the WACC build with each input traced to a primary source, and a sensitivity range that covers +/- 1.0% on WACC and terminal growth. The opinion letter must disclose all material assumptions per FINRA Rule 5150 (FINRA Rule 5150), and the model must support every numeric statement in the letter. Sullivan & Cromwell’s M&A Practice Memo (2024 update) provides a checklist, and Skadden Arps’ annual M&A Insights report (Skadden Insights) tracks the most common deficiencies cited by Delaware courts in appraisal litigation.
One overlooked use case is the 409A valuation for private-company stock-option strike-price setting. Internal Revenue Code Section 409A (26 U.S.C. Section 409A, Cornell Law) requires that the strike price of employee stock options be set at or above the fair market value of the underlying common stock on the grant date, with a 20% additional tax plus interest as the penalty for noncompliance. 409A valuations use a DCF as one of three valuation methods (the others are precedent transactions and market multiples), and the resulting fair market value is the strike price. Carta, Pulley, and Eqvista all offer 409A valuation services starting at $1,500-$3,000 per valuation, and the AICPA Practice Aid 2024 is the governing standard. Our founder shares guide covers how founders structure equity grants around 409A constraints.
15. Industry-Specific DCF Template Adjustments
The base template is industry-agnostic, but five industries demand specific adjustments that show up in every published fairness opinion for those sectors. Building these adjustments wrong is the easiest way to get desk-rejected by an industry-focused PE firm.
Software and SaaS. Replace revenue growth with an ARR build (starting ARR, gross new ARR, expansion ARR, churn, ending ARR). Add a Rule of 40 check (growth % + EBITDA margin % >= 40). Treat SBC as a real expense, not an add-back, per the 2024 Bessemer Venture Partners State of the Cloud report. Goldman Sachs equity research has used SBC-included EBITDA in software coverage since 2022.
Healthcare services. Add a payor-mix sensitivity (Medicare vs Medicaid vs commercial). Centers for Medicare & Medicaid Services (CMS) reimbursement rates at CMS.gov Medicare fee-for-service payment directly affect Year 1-5 revenue. Add a CON (Certificate of Need) barrier-to-entry adjustment for the 35 states that still regulate hospital capacity.
Energy and oil and gas. Replace EBITDA with EBITDAX (excludes exploration). Add a reserves-based cross-check using SEC Pricing (12-month trailing average) per SEC Final Rule 33-8995 (SEC Final Rule 33-8995). Use a reserve-life-indexed terminal value, not Gordon Growth, since reserves are finite.
Financial services (banks and insurance). Use a dividend discount model (DDM) instead of UFCF DCF, since regulatory capital constraints make UFCF meaningless. The Federal Reserve’s CCAR/DFAST stress-test framework (Federal Reserve stress tests) sets the binding capital constraint.
Real estate (REITs and operators). Use funds from operations (FFO) and adjusted funds from operations (AFFO) instead of EBITDA. Net asset value (NAV) per Nareit methodology (Nareit.com) serves as the relative-value cross-check.
16. Building Your Own DCF Template From a Blank Spreadsheet
If you want to build the template from scratch instead of downloading it, here is the exact build order used in Wall Street Prep’s Premium Package training and Training The Street’s Corporate Valuation Masterclass, which are the two most-used onboarding programs at investment banks (Wall Street Prep, premium package overview; Training The Street programs are documented at the firm’s website).
Day 1: Assumptions tab. Build the 14-input block. Color blue for hard-coded inputs, black for formulas, green for sheet links. Add a date stamp cell, a model version cell, and a “prepared by” cell. Lock the structure with Excel sheet protection (Review tab > Protect Sheet) leaving only blue cells editable.
Day 2: Operating Model tab. Build the income statement from revenue down to EBIT. Use direct cell references back to Tab 1, never hard-code. Add a check cell that compares Tab 2 revenue to Tab 1 revenue and flags any mismatch in red conditional formatting.
Day 3: Free Cash Flow tab. Build the UFCF bridge. Add a separate column for each year, with a final TV column for the implied Year 6+ cash flow. Triple-check the NWC sign convention (positive NWC growth = cash outflow).
Day 4: WACC tab. Build CAPM cost of equity, after-tax cost of debt, and the weighted blend. Use a separate sub-block for unlever-relever beta math. Document every source input in column G as a comment or text cell.
Day 5: Terminal Value and Output tabs. Build both Gordon Growth and Exit Multiple, with the cross-check cells. Wire the Output tab to pull EV, subtract net debt and preferred, output equity value and implied per-share value (if applicable).
Day 6: Sensitivities tab. Build the three sensitivity tables using Data Table. Test by changing Tab 1 inputs and confirming the Data Tables refresh correctly.
Day 7: Audit and stress-test. Have a second modeler do a full cell-by-cell audit. The Mergers & Inquisitions guide “How to Audit a DCF in 15 Minutes” (August 2024) provides a 22-point checklist. Our private equity analyst career guide covers the exact audit skills PE firms test for in modeling rounds, since model audit is 30-40% of PE first-year work.
17. Beyond the Template: Integrating With LBO, Comps, and Football Field
A DCF is one of three valuation methods that always appear together: DCF, comparable company analysis (public comps), and precedent transaction analysis (precedents). The template is wired to export its enterprise value to a separate Football Field tab where you stack all three methods plus a 52-week trading range for public targets.
Comparable company analysis pulls trading multiples (EV/Revenue, EV/EBITDA, P/E) from 8-15 public companies. Precedent transactions pull deal multiples from 10-20 closed M&A deals in the last 24-36 months. The DCF, alone among the three, is intrinsic. The other two are relative.
For private companies, the comp set is the harder lift. SEC EDGAR (SEC EDGAR) is the primary source for public-company financials. Pitchbook, S&P Capital IQ, and Refinitiv Eikon are the primary sources for transaction multiples. Mergermarket and DealEdge are secondary. For mid-market deals under $250M EV, GF Data is the best source (GF Data is now owned by ITR Economics as of 2023, and publishes quarterly reports on private-deal multiples broken down by EBITDA size band).
The paper LBO example walkthrough shows how an interviewer at a private equity firm will ask you to do a DCF in your head in 10 minutes during the case round. The mental DCF is the same math as the spreadsheet DCF, just with rounder numbers and a single terminal multiple.
18. TLDR and Final Takeaways
- A working DCF template with six tabs (Assumptions, Revenue Build, Operating Model, Free Cash Flow, WACC and Terminal Value, Output) is the same structure used inside every fairness opinion filed on SEC EDGAR.
- Only Tab 1 (Assumptions) takes input cells. Every other tab is formula-driven. Color-code blue for inputs, black for formulas per Wall Street Prep convention.
- The five most-important assumptions: revenue growth taper (use Lazard fade rule), EBITDA margin, CapEx as % of revenue, WACC (CAPM-based), and terminal growth (cap at 2.5%, never above 3.0% per BEA long-run GDP).
- UFCF formula: NOPAT + D&A – CapEx – delta NWC. NOPAT uses cash tax rate, not statutory.
- WACC formula: (E/V) x Cost of Equity + (D/V) x Cost of Debt x (1 – tax rate). Cost of Equity from CAPM. Cost of Debt from B+ middle-market spread.
- Terminal value via Gordon Growth (TV = UFCF6 / (WACC – g)) and Exit Multiple (TV = LTM EBITDA x EV/EBITDA). Always run both and cross-check.
- Mid-year convention is U.S. standard, used in 89% of fairness opinions per Houlihan Lokey 2025 review.
- Sensitivity tables on WACC vs g, WACC vs exit multiple, and revenue growth vs EBITDA margin are mandatory for buy-side IC memos.
- Eight common errors will get your model rejected: terminal growth above WACC (per In re Dole Food), mismatched stub-period, double-counting CapEx, wrong tax shield, stale beta, wrong capital structure, missing non-operating assets, stale risk-free rate.
- The DCF stands beside comps and precedents on a football field. Three methods, one chart, every banker pitch since 1995.
- Cite every numeric input to a primary source: Fed H.15 for risk-free, Damodaran for ERP, Pitchbook for beta and multiples, SEC EDGAR for comps, BEA for terminal growth.
A DCF template is not a magic box. It is a structured way to expose your assumptions so that someone smarter can question them. Build the template once, audit it twice, and use it for every deal that lands on your desk.