DCF Template Excel: Formulas, Layout, and Worked Model

A DCF template Excel workbook estimates a company's intrinsic value by forecasting unlevered free cash flows, discounting those cash flows and a terminal value at the weighted average cost of capital, and bridging from enterprise value to an implied share price. While SEC Corporation Finance guidance notes that reported free cash flow typically equals GAAP operating cash flow minus capital expenditures, a valuation DCF Excel template builds unlevered free cash flow from EBIT so financing costs sit only in WACC. Across the 443,100 US financial analyst jobs tracked by the Bureau of Labor Statistics in 2025, this model is a standard way to value securities and test growth assumptions.

Updated

How a DCF Excel Template Is Structured

A discounted cash flow model values an operating business as a going concern based on the time value of money. Instead of pricing a company off peer multiples alone, a DCF template Excel sheet projects how much cash the business will generate for all capital providers over an explicit forecast window and in perpetuity, then discounts every dollar back to the valuation date.

You can build your DCF Excel template on one vertical tab for a timed modeling test or split it across six linked tabs for deal work. Format hardcoded historicals and driver inputs in blue font, same-sheet formulas in black font, and cross-sheet links in green font. Never type hardcoded assumptions inside formulas. Reference dedicated input cells instead.

Place a macro-free scenario switch at the top of your assumptions block to toggle between Base, Bull, and Bear cases without VBA. Put the active case index in one blue cell named Case_Select, stack the three case rows for revenue growth, EBIT margin, CapEx, and working capital underneath, and pull the active row with =CHOOSE(Case_Select, Base_Cell, Bull_Cell, Bear_Cell) or =INDEX(Scenario_Block, Case_Select, Column_Num).

Standard Workbook Architecture for a DCF Template in Excel

Standard Workbook Architecture for a DCF Template in Excel
Schedule or TabPrimary InputsCore Formula OutputFont Color Rule
Control and ScenariosValuation date, case selector, driver casesActive operating drivers via CHOOSE or INDEXBlue inputs, black active row
Operating ForecastHistorical 10-K actuals, active growth and margin ratesProjected Revenue, EBIT, D&A, and EBITDABlue actuals, black projections
UFCF BuildEffective tax rate, CapEx % of sales, NWC daysAnnual Unlevered Free Cash Flow (FCFF)Black same-sheet or green links
WACC CalculationRisk-free rate, peer betas, ERP, pretax debt yieldWeighted Average Cost of Capital (%)Blue market data, black WACC
Valuation and BridgeBalance sheet cash, debt, preferreds, option tranchesEnterprise Value, Equity Value, Implied Share PriceBlue balance sheet items, black bridge
Sensitivity and ChecksWACC steps, terminal growth and exit multiple stepsTwo-way share price matrix and error flagsBlue axis headers, black Data Table

Sourcing Clean Historical and Macro Inputs

Pull audited annual statements from Form 10-K, interim stub actuals from Form 10-Q, material events from Form 8-K, and option grants from Form DEF 14A proxy filings through SEC EDGAR Full Text Search, which covers electronic filings since 2001. To populate historical tabs across a peer group without retyping PDFs, use the SEC Financial Statement Data Sets (covering January 2009 through September 2026 and updated quarterly) for flattened XBRL face financials and Standard Industrial Classification codes. Pair them with the SEC Financial Statement and Notes Data Sets (covering January 2009 through October 2026 and updated monthly since November 2020) for footnote debt, lease, and option tables.

To benchmark operating margins and working capital ratios for private companies, check the US Census Bureau Quarterly Financial Report program. Collected for over sixty years, the QFR publishes aggregate income statements, balance sheets, and operating ratios across US manufacturing, mining, wholesale trade, retail trade, and selected service corporations, with database tables from 1947 to present and downloadable Excel tables from 2006 to present.

Macroeconomic price data helps you stress-test margin assumptions. Over the 12 months ended September 2026, the Bureau of Labor Statistics Consumer Price Index rose 3.7% for all items (not seasonally adjusted), while the BLS Producer Price Index for final demand advanced 6.5% not seasonally adjusted for the 12 months ended October 2026. Separating revenue pricing from input cost inflation prevents overstated gross margins when producer costs outpace consumer prices.

Unlevered Free Cash Flow Formulas in Excel

Under SEC Corporation Finance guidance on Non-GAAP Financial Measures (last updated December 13, 2022), companies typically calculate reported free cash flow as GAAP operating cash flows less capital expenditures, which omits mandatory debt service requirements and already deducts cash interest. In a DCF Excel template, build Unlevered Free Cash Flow (Free Cash Flow to Firm, or FCFF) from GAAP operating income (EBIT) so cash flows stay independent of debt before discounting at WACC. Keep SEC definitions straight when pulling historicals: under Exchange Act Release No. 47226, earnings in EBIT and EBITDA means GAAP net income, so non-GAAP performance disclosures reconcile to net income rather than operating income.

Define operating Net Working Capital (NWC) as non-cash current assets minus non-debt current liabilities. When receivables and inventory grow faster than payables, NWC increases and consumes cash. Write your Excel formula as -(NWC_Current, NWC_Prior) so an increase in NWC reduces UFCF.

Handle Stock-Based Compensation (SBC) and operating leases consistently so they do not distort value. Adding back SBC in UFCF while holding shares flat treats equity pay as free. Leave SBC expensed inside EBIT without adding it back in UFCF, and include already-granted unvested RSUs and options in diluted shares. Under US GAAP lease accounting, operating lease cost sits entirely inside EBIT as an operating expense, so exclude operating lease liabilities from debt in the equity bridge. Under international lease accounting standards, lease cost splits into right-of-use depreciation and interest: either deduct the full cash lease rent in UFCF (do not add back right-of-use depreciation) and exclude lease liabilities from debt, or add back right-of-use depreciation and subtract the capitalized lease liability as debt.

Unlevered Free Cash Flow (UFCF) Formula Build in Excel

Unlevered Free Cash Flow (UFCF) Formula Build in Excel
Line ItemSignExact Excel Formula SyntaxModeling Check
Operating Income (EBIT)Base=Revenue_Cell, COGS_Cell, OpEx_CellExcludes interest and non-operating items
Taxes on EBITSubtract (-)=-EBIT_Cell * Tax_RateTax rate applied before interest shield
NOPAT (EBIAT)Subtotal (=)=EBIT_Cell + Tax_On_EBIT_CellEquals EBIT, (EBIT * Tax_Rate) when positive
Depreciation and AmortizationAdd (+)=DA_Schedule_CellOmit right-of-use D&A if deducting cash rent
Capital ExpendituresSubtract (-)=-CapEx_Schedule_CellIncludes PP&E and capitalized software
Change in Net Working CapitalSubtract increase (-)=-(NWC_Current, NWC_Prior)Excludes cash and short-term debt
Unlevered Free Cash FlowTotal (=)=SUM(NOPAT_Cell:Change_NWC_Cell)Cash flow available to all capital holders

WACC, Mid-Year Convention, and Stub Discounting

Discount UFCF at the Weighted Average Cost of Capital (WACC). Calculate Cost of Equity via CAPM as =Risk_Free_Rate + (Relevered_Beta * Equity_Risk_Premium). Unlever each peer's beta with =(Peer_Beta * Peer_Equity) / (Peer_Equity + Peer_Debt, (Peer_Debt * Peer_Tax_Rate)), take the peer median, and relever to your target capital structure with =Median_Unlevered_Beta * (Target_Equity + Target_Debt, (Target_Debt * Target_Tax_Rate)) / Target_Equity. Combine Cost of Equity with After-Tax Cost of Debt, =Pretax_Debt_Yield, (Pretax_Debt_Yield * Effective_Tax_Rate), using market or target weights =(Cost_of_Equity * Equity_Weight) + (After_Tax_Debt_Cost * Debt_Weight) so you avoid circularity.

Excel's native =NPV(WACC, Explicit_UFCF_Range) assumes cash arrives on the last day of each annual period, which understates value for businesses collecting cash all year. Add an explicit mid-year Discount Period row that subtracts half a year from each forecast year (starting at half a year in the first forecast column and stepping up by one year per column, as shown in our worked model below), and discount each year's UFCF at WACC over that fractional period.

For an intra-year valuation date (such as a third-quarter close for a calendar-year filer), add a stub column before the first full forecast year. Calculate the remaining year fraction with =YEARFRAC(Valuation_Date, Fiscal_Year_End_Date). Set the stub mid-period exponent to half of Stub_Fraction, and step each full-year mid-period exponent up by adding half a year for the first full year and one full year for each subsequent column. You can also place the valuation date (with a zero cash flow) and exact mid-period calendar dates in a date row and use =XNPV(WACC, Cash_Flow_Range, Date_Range).

Terminal Value, Equity Bridge, and Diluted Shares

Capture cash flows after your final forecast year (Year N) using both Terminal Value methods side by side so each checks the other. Under Gordon Growth, keep perpetual growth g below WACC and long-run nominal GDP growth. Discount both Exit Multiple Terminal Value and standard year-end Gordon Growth Terminal Value using the full exponent N (five full years in a five-year model), because both state value as of the final day of Year N.

Sum the present value of explicit UFCFs and the present value of Terminal Value to get Enterprise Value. Bridge to Implied Equity Value using the latest balance sheet: add cash, marketable securities, and non-operating assets, then subtract total debt, preferred stock, and noncontrolling interest. While SEC Question 102.07 bars non-GAAP free cash flow per share in regulatory filings, in a DCF spreadsheet you never divide annual UFCF by shares anyway. You complete the full equity bridge first, then divide Implied Equity Value by fully diluted shares.

Compute diluted shares via the Treasury Stock Method (TSM) across basic shares, unvested RSUs, in-the-money options and warrants, plus any dilutive convertible instruments. For each in-the-money option or warrant tranche, net new shares equal =Options, ((Options * Strike) / Share_Price). To avoid circular iteration when using Implied Share Price as the repurchase price, use the exact closed-form equation for in-the-money tranches: =(Implied_Equity_Value + SUMPRODUCT(ITM_Options, ITM_Strikes)) / (Basic_Shares + RSUs + SUM(ITM_Options)).

  • Gordon Growth Terminal Value (at end of Year N): =(Final_UFCF + (Final_UFCF * g)) / (WACC, g).
  • Exit Multiple Terminal Value (at end of Year N): =Final_EBITDA * Exit_Multiple.
  • Implied Exit EV/EBITDA Multiple from Gordon Growth: =Gordon_Terminal_Value / Final_EBITDA.
  • Implied Perpetual Growth Rate from Exit Multiple: =((Multiple_Terminal_Value * WACC), Final_UFCF) / (Multiple_Terminal_Value + Final_UFCF).

Worked Example: 5-Year DCF Model in Excel

Below is a worked 5-year DCF for Northline Industrial Corp., an invented company created by our team ($ in millions except per-share data). Northline's tax rate is 25.0%. With a 4.50% risk-free rate, 1.30 relevered beta, and 5.00% equity risk premium, Cost of Equity is 4.50% + (1.30 * 5.00%) = 11.00%. An 8.00% pretax debt yield gives a 6.00% after-tax cost of debt. At 80.0% equity and 20.0% debt, WACC equals (0.80 * 11.00%) + (0.20 * 6.00%) = 10.00%.

Applying a 2.50% perpetual growth rate to Year 5 UFCF of $105.00 million gives a Gordon Growth Terminal Value of $105.00 * 1.025 / (0.100, 0.025) = $1,435.00 million, which implies an 8.15x exit multiple on Year 5 EBITDA of $176.00 million ($148.00 million EBIT + $28.00 million D&A). Discounting $1,435.00 million back 5.0 full years at 10.00% yields $891.02 million.

Adding the $327.24 million mid-year present value of Year 1 to Year 5 UFCFs produces an Enterprise Value of $1,218.26 million. Adding $85.00 million cash and subtracting $240.00 million debt, $15.00 million preferred stock, and $10.00 million noncontrolling interest gives $1,038.26 million in Equity Value. Across 49.00 million basic shares, 1.00 million unvested RSUs, and 2.00 million options at a $10.00 strike, closed-form TSM Implied Share Price equals ($1,038.26 + (2.00 * $10.00)) / (49.00 + 1.00 + 2.00) = $20.35 per share (51.02 million diluted shares).

Worked 5-Year UFCF Schedule: Northline Industrial Corp. ($ in Millions, Invented Example by Our Team)

Worked 5-Year UFCF Schedule: Northline Industrial Corp. ($ in Millions, Invented Example by Our Team)
Line ItemYear 1Year 2Year 3Year 4Year 5
Operating Income (EBIT)$100.00$112.00$124.00$136.00$148.00
Less: Taxes at 25.0%-$25.00-$28.00-$31.00-$34.00-$37.00
NOPAT$75.00$84.00$93.00$102.00$111.00
Plus: Depreciation and Amortization$20.00$22.00$24.00$26.00$28.00
Less: Capital Expenditures-$25.00-$26.00-$28.00-$29.00-$30.00
Less: Increase in Net Working Capital-$5.00-$6.00-$6.00-$5.00-$4.00
Unlevered Free Cash Flow (UFCF)$65.00$74.00$83.00$94.00$105.00
Mid-Year Discount Period (t, 0.5)0.501.502.503.504.50
PV of UFCF at 10.00% WACC$61.98$64.14$65.40$67.34$68.38
Sensitivity Data Tables and Model Audit Checklist

Build two two-variable Excel Data Tables on the same tab as your WACC and terminal inputs: one flexing WACC against Perpetual Growth Rate, and another flexing WACC against Exit EV/EBITDA Multiple. Link the top-left corner cell of the grid to Implied Share Price, put WACC steps down the left column and terminal steps across the top row as blue inputs, highlight the full grid, press Alt + A + W + T, and map Row input cell to your terminal driver and Column input cell to WACC.

Before sending a workbook or defending your valuation in investment banking technical interviews, wire an error-check block at the top of the tab. Pairing clean formula checks with out-loud technical drills and behavioral interview practice prepares you to explain every assumption under follow-up questioning.

  • Growth vs. WACC Flag: =IF(Perpetual_g >= WACC, "ERROR", "OK") blocks negative Gordon Growth denominators.
  • Terminal CapEx vs. D&A Flag: =IF(Final_CapEx < Final_DA, "CHECK", "OK") warns if perpetual growth assumes shrinking fixed assets.
  • Enterprise Value Sign Check: =IF(ABS(Enterprise_Value) <> Enterprise_Value, "ERROR", "OK") catches negative enterprise value sign flips.
  • NWC Exclusion Check: Confirm cash and short-term debt are excluded from NWC so they are not double-counted in the equity bridge.
  • Lease and SBC Check: Confirm US GAAP or international lease debt matches UFCF rent treatment, and SBC is not added back without share dilution.
  • Terminal Exponent and Case Check: Confirm terminal value discounts by full period N and Case_Select is set to the Base Case.

From U.S. Securities and Exchange Commission: Non-GAAP Financial Measures Guidance, U.S. Securities and Exchange Commission: EDGAR Full Text Search, U.S. Securities and Exchange Commission: Financial Statement Data Sets, U.S. Securities and Exchange Commission: Financial Statement and Notes Data Sets, U.S. Bureau of Labor Statistics: Occupational Outlook Handbook, Financial Analysts, U.S. Bureau of Labor Statistics: Consumer Price Index, U.S. Bureau of Labor Statistics: Producer Price Indexes, U.S. Census Bureau: Quarterly Financial Report (QFR) and U.S. Census Bureau: Historical QFR Data and Excel Tables.

1 practice question and sample answer

Say your answer out loud first, then read the sample answer.

Practice question

  1. Walk me through how you set up a DCF model in Excel, including how you handle mid-year discounting and bridge to implied share price.

    Sample answer. I build the sheet in five clean blocks with blue hardcoded inputs and black formulas. First, I project five years of unlevered free cash flow starting from EBIT, subtracting unlevered taxes to get NOPAT, adding back depreciation and amortization, and deducting CapEx and increases in non-cash net working capital. I leave stock-based compensation expensed in EBIT so I do not overstate cash flow. Second, I calculate WACC from CAPM cost of equity using relevered peer betas and after-tax cost of debt at target capital weights. Third, because cash arrives throughout the year rather than on December 31, I add a mid-year discount row set to 0.5 through 4.5 and discount each year's cash flow at WACC. Fourth, I calculate terminal value using both Gordon Growth and an EV/EBITDA exit multiple, cross-check the implied growth rate, and discount terminal value back five full years. Finally, I add cash and subtract total debt, preferred stock, and noncontrolling interest to get equity value, then divide by diluted shares using the Treasury Stock Method, followed by a two-way WACC sensitivity table. Example answer written by our team. The applicant is invented.

    It names every formula step in spreadsheet order, separates mid-year exponents (0.5 to 4.5) on explicit cash flows from the full year-end exponent (5.0) on terminal value, covers stock-based compensation and the Treasury Stock Method, and finishes in under two minutes.

Practice your interview answers out loud

Soreno's AI interviewer asks behavioral and case questions out loud, follows up like a real interviewer, and scores every answer, quoting your own words.

Questions people ask

Should I use NPV or XNPV in a DCF Excel template?

Use XNPV or an explicit discount period row whenever you use a mid-year convention or an intra-year valuation date. Standard Excel NPV assumes equally spaced cash flows arriving on the last day of each full period, while XNPV discounts by exact calendar dates.

How does free cash flow in a DCF differ from free cash flow in SEC filings?

Under SEC Corporation Finance guidance, reported free cash flow is typically GAAP operating cash flow minus capital expenditures, which is after interest and excludes mandatory debt repayments. A DCF Excel template builds unlevered free cash flow from EBIT after unlevered taxes so financing sits only in WACC.

How do you build the mid-year convention in Excel?

Add a discount period row that subtracts half a year from each forecast year (starting at half a year in the first projected column and stepping up by one year per column across a five-year forecast) and discount each year's UFCF at WACC over that fractional period, or apply =XNPV(WACC, Cash_Flow_Range, Mid_Year_Date_Range) to mid-year calendar dates. Keep the terminal value exponent at the full final year N for exit multiples and standard year-end Gordon Growth.

Where can I find free historical financial data for a DCF spreadsheet template?

Pull Form 10-K, 10-Q, and DEF 14A filings from SEC EDGAR Full Text Search, or download structured XBRL tables from the SEC Financial Statement Data Sets and Notes Data Sets. For private-company industry margin and ratio benchmarks, download the US Census Bureau Quarterly Financial Report Excel tables.

How should stock-based compensation and operating leases be treated in UFCF?

Leave stock-based compensation expensed inside EBIT without adding it back in UFCF, and count existing unvested RSUs, options, and warrants via the Treasury Stock Method. Under US GAAP lease rules, keep operating lease cost inside EBIT and exclude operating lease liabilities from total debt.

Keep going