DCF Simulator

Discounted Cash Flow Engine

EBITDA-driven projections, unlevered free cash flow, mid-year convention discounting, EV/EBITDA exit multiple & Gordon Growth Model terminal value — built from first principles, no Excel functions.

How This Model Works This simulator builds a Discounted Cash Flow valuation from first principles, following Prof. de Groot's methodology (no Excel financial functions). Terminal value is computed two ways: (1) Exit Multiple (EV/EBITDA × Terminal Year EBITDA) and (2) Gordon Growth Model (Terminal Year FCF × (1+g) / (WACC − g)). Both are discounted and summed with projected UFCF to derive Enterprise Value. Subtracting Net Debt yields Implied Equity, which divides by diluted shares to produce an Implied Share Price comparable against an M&A offer.

How to Use — Step by Step Tab 1 — WACC: Set your CAPM inputs (risk-free rate, ERP, beta), build cost of debt as Rf + Credit Spread, set the effective tax rate, and define capital structure weights. If valuing a division, enable the carve-out toggle to reconstruct EBITDA from segment data.

Tab 2 — Assumptions: Enter base year EBITDA, then for each projection year choose between Growth % / % of EBITDA (derived) or Direct $M (analyst projection / explicit forecast). UFCF drivers (D&A, Capex, ΔNWC) vary by year. Set exit multiples (low/base/high), GGM perpetuity growth rates, net debt, and M&A offer details.

Tab 3 — Model: Review the full DCF table showing EBITDA projections, UFCF derivation, PV calculations, and terminal value under both methods. Key numbers and scenario cards summarize the output.

Tab 4 — Valuation: The Fairness Verdict compares implied share prices against the offer. The Bridge table and bar chart show how each scenario stacks up against the benchmark deal.

Tab 5 — Sensitivity: Two heatmaps — WACC × Exit Multiple and WACC × Perpetuity Growth Rate — let you stress-test the valuation across assumptions.

Tab 6 — Export: Download a fully formatted Excel workbook with all assumptions, the DCF model, both sensitivity grids, and the valuation bridge.

Exam Notes Mid-year convention: projection years discount at t=0.5, 1.5, ... 4.5, but terminal value discounts at t=5 (end of final year). The Gordon Growth Model requires g < WACC — if g ≥ WACC the model flags it as invalid. All valuation must be done manually without Excel’s built-in financial functions (YIELD, IRR, etc.) — using them scores zero.

Section 1 — WACC Component Builder

Computes WACC from independent CAPM inputs. Feeds directly into the DCF discount factor.

Cost of Equity — CAPM

Risk-Free Rate (Rf) 10-yr UST%

Equity Risk Premium Damodaran ERP%

Levered Beta (β)β

Cost of Equity (Ke) = Rf + β × ERP—

Cost of Debt

Credit Spread Issuer rating spread%

Pre-Tax Cost of Debt (Kd) = Rf + Credit Spread—

Effective Tax Rate Marginal corporate%

After-Tax Kd = Kd × (1 − t)—

Capital Structure Weights

Debt Weight (D/V)%

Equity Weight (E/V)—

Weighted Average Cost of Capital—

Division Carve-Out (optional — for segment-level DCF)

Enable this if your target is a division or segment of a larger group.

Division / Segment Carve-Out

OFF

Division Identification

Parent Company

Division Name

EBITDA Reconstruction

Division Revenue ($M)$M

Estimated EBITDA Margin%

Reconstructed Division EBITDA—= Revenue × Margin

Group-Level Allocation

Group Total Revenue ($M)$M

Revenue Share Ratio= Div Rev / Group Rev

Group D&A ($M)$M

Group Capex ($M)$M

Group ΔNWC ($M)$M

Allocated Results (auto-fed into DCF)

Division D&A ($M)—

Division Capex ($M)—

Division ΔNWC ($M)—

Implied D&A % of EBITDA—

Implied Capex % of EBITDA—

Implied ΔNWC % of EBITDA—

⚠ Revenue-share allocation assumes similar capital intensity across divisions.

Section 2 — EBITDA Projections & UFCF Drivers

For each year, choose Growth % / % of EBITDA (derived) or Direct $M (analyst projection / explicit forecast). Recommended: use analyst estimates for Yrs 1–3, then growth-driven for Yrs 4–5.

Base Year

Base Fiscal Year

Base EBITDA ($M)$M

EBITDA — Projection Years 1–5

Yr 1Yr 2Yr 3Yr 4Yr 5

Mode

Growth %

EBITDA $M — — — — —

Switch any year to Direct $M to override with an explicit analyst estimate.

UFCF Drivers — Variable by Year

Yr 1Yr 2Yr 3Yr 4Yr 5

D&A

Value

Capex

Value

ΔNWC

Value

Tip: For analyst-projected years, switch the relevant cells to Direct $M and enter the consensus figure. Tax rate is now in the WACC tab.

Section 3 — Terminal Value & Equity Bridge

Two TV methodologies and equity bridge inputs for M&A offer analysis.

Exit Multiples (EV/EBITDA)

Low Multiple×

Base Multiple CCA Median×

High Multiple×

Perpetuity Growth Rate — Gordon Growth Model

Low Growth Rate (g)%

Base Growth Rate (g)
Long-term GDP/Inflation%

High Growth Rate (g)%

Equity Bridge

Net Debt at Close ($M)$M

Benchmark Deal EV ($M) Optional$M

Implied Share Price & M&A Offer Analysis

Diluted Shares OutstandingM shares

M&A Offer Price per Share
Cash component of bid$/share

DCF Model — Step-by-Step Working Mid-Year Convention

Valuation Bridge — Exit Multiple & GGM vs Benchmark

Sensitivity — Exit Multiple (WACC × EV/EBITDA)

High

Mid

Low ■ = Base case ★ = Nearest to benchmark

Sensitivity — Gordon Growth (WACC × Perpetuity Growth Rate) → $/Share

High

Mid

Low ■ = Base case ★ = Offer price zone

Download Your DCF Model

Exports a fully formatted Excel workbook with four sheets: Assumptions, DCF Model (including GGM and share price), Sensitivity (both EM and GGM grids), and Valuation Bridge.