Built around Prof. de Groot's two-direction framework — Forward (entry multiple → IRR) and Backward (target IRR → max bid). Sources & Uses, debt waterfall, cash sweep, and sensitivity tables — built from first principles, no Excel functions.
How This Model Works
This simulator builds a Leveraged Buyout valuation from first principles, following Prof. de Groot's methodology. Unlike a DCF (which produces an intrinsic value) or comparable companies analysis (which produces a market-based value), the LBO produces a floor valuation — the maximum price a financial sponsor can pay while still meeting a target IRR. The model runs in two directions: traditional (input multiple → output IRR) and de Groot's signature valuation framing (input target IRR → output maximum bid). Both produce results in EV and per-share space.
Mode A — Forward (Traditional)
You input: entry multiple, financing structure, exit multiple, cash flows. Model outputs:IRR, MOIC, implied exit share price. Use case: "What return does this deal produce at the announced price?"
Mode B — Backward (de Groot Valuation)
You input: target IRR, exit multiple, financing structure, cash flows. Model outputs:maximum entry EV, maximum offer price per share. Use case: "What's the most we can pay and still hit 15% IRR?"
How to Use — Step by StepTab 1 — Cash Flows: Set base year EBITDA and hold period (3–10 yrs). Choose Build mode to project EBITDA from a growth path with UFCF drivers, or Paste mode to drop in UFCF directly from a DCF model. Operating Scenario presets (Base / Sponsor / Management / Downside-1 / Downside-2) follow R&P 3E conventions.
Tab 2 — Sources & Uses: Offer price per share, basic + dilutive shares, existing net debt → Equity Purchase Price → Entry EV. Pick a Financing Structure preset (1–5) covering TLB-heavy through Sub-Notes-heavy capital structures, or customize tranches manually.
Tab 7 — Mini Lab: The "why it works" foundational lesson — four canonical scenarios (debt paydown / EV growth / 25% leverage / 75% leverage) with interactive sliders showing how leverage amplifies equity returns.
Exam Notes
No Excel functions allowed in coursework — IRR is computed via Newton-Raphson iteration, not =IRR(). Cash sweep is 100% of levered FCF to the senior TLB until it's repaid; only then does cash accumulate on the balance sheet. Mandatory amortization (typically 1% of original TLB principal per year) runs before the optional sweep. Strategic / IG-adjacent buyers use lower coupons (4–5%); PE sponsors face SOFR+250bps or wider (7–8%+). The Mezzanine / PIK tranche is unique to PE sponsor structures — strategic buyers typically don't need it.
Simulator Complete
All eight tabs are now functional. Tabs 0–5 cover the full build workflow (Guide → Cash Flows → Sources & Uses → Debt Schedule → Returns → Sensitivity). Tab 6 exports a 6-sheet Excel workbook (Cover, Assumptions, Sources & Uses, Debt Schedule, Returns, Sensitivity) with blue inputs / black outputs / gold section headers per industry color-coding conventions. Tab 7 (Mini Lab) is the pedagogical closer with four canonical scenarios.
Section 1 — Deal Anchors
Base year EBITDA and hold period drive the entire projection horizon. Hold period determines which projection year is the exit year for IRR / MOIC.
Base Year
$M
Hold Period
5 yrs
FY2025= Base Year
FY2030= Base Year + Hold
Section 2 — Cash Flow Input Mode
Choose Build from EBITDA to project from a growth path and UFCF drivers, or Paste UFCF to drop in UFCF figures directly (e.g. from a completed DCF model).
Operating Scenario Preset (R&P 3E convention)
—
EBITDA — 10-Year Projection
Yr 1Yr 2Yr 3Yr 4Yr 5Yr 6Yr 7Yr 8Yr 9Yr 10
Switch any year to Direct $M to override growth-driven projection with an explicit analyst estimate.
UFCF Drivers — Variable by Year
Yr 1Yr 2Yr 3Yr 4Yr 5Yr 6Yr 7Yr 8Yr 9Yr 10
Effective Tax Rate
%
Tax is applied to EBIT (EBITDA − D&A) to derive NOPAT, then D&A added back, Capex and ΔNWC subtracted to arrive at UFCF.
UFCF Direct Input ($M)
Yr 1Yr 2Yr 3Yr 4Yr 5Yr 6Yr 7Yr 8Yr 9Yr 10
Paste UFCF figures from your DCF model. EBITDA still drives entry/exit multiples — use Section 1 for that.
EBITDA path, UFCF path, and exit-year metrics derived from the inputs above. These feed Tabs 2–5.
—
EBITDA Entry
—
EBITDA Exit
—
EBITDA CAGR
—
Σ UFCF (hold period)
Section 1 — Buyer Type & Entry Valuation
Switch between PE Sponsor (LBO-style, higher leverage + Mezz/PIK enabled) and Strategic / IG-adjacent buyer (lower leverage, no PIK, IG coupons).
Buyer Type
—
Entry Valuation — EV Approach
x
—= Entry Mult × EBITDA0
Per-Share Inputs (deal-pricing lens)
$
M
M
—= Basic + Dilutive
—= Offer × FDSO
EV ↔ Per-Share Reconciliation
Entry EV and Offer/Share are linked: Entry EV = Offer × FDSO + Existing Net Debt. Edit either and the other will update to keep them in sync (last-edit wins). The model uses Entry EV as the canonical source of truth internally.
Existing Capitalization
$M
$M
—= Gross Debt − Cash
Section 2 — Financing Structure
Choose a preset structure or customize the tranche mix manually. Total Debt = sum of tranches; Equity Cheque is the plug.
Structure Preset (R&P 3E convention, total leverage targeted)
Mezzanine / PIK PIK interest accrues to balance, bullet at exit
x
%
—= Mezz Mult × EBITDA0
Transaction Fees
%
%
$M
$M
Sources & Uses Balance
Sources must equal Uses. Equity Cheque is the residual plug. A green check means the deal balances; red means recheck inputs.
Sources of Funds
Uses of Funds
—
Entry EV
—
Total Debt
—
Gross Leverage
—
Equity Cheque
Annual Debt Schedule & Cash Flow Waterfall
EBITDA → UFCF (from Tab 1) → minus cash interest (per tranche) → minus mandatory amort → levered FCF → 100% cash sweep to TLB. Ending balances per tranche. Years beyond hold period are dimmed.
—
Debt at Entry
—
Debt at Exit
—
Total Paydown
—
Exit Leverage
Waterfall MechanicsStep 1: UFCF (from Tab 1 cash flow projection) is the source of debt repayment. Step 2: Cash interest deducted — computed on each tranche's beginning balance for the year. PIK interest (if Mezz on) accrues to balance, no cash impact. Step 3: Mandatory amort on TLB (typically 1% of original principal per year) deducted. Step 4: Levered FCF = UFCF − cash interest − mandatory amort. Step 5: 100% cash sweep applied to TLB until repaid in full. If sweep capacity exceeds remaining TLB, excess is held as cash on the balance sheet (rare in well-structured LBOs). Step 6: Sr Unsec and Mezz are bullets — balances unchanged until exit (except Mezz balance grows from PIK accrual).
Section 1 — Calculation Mode
Forward: input entry multiple, output IRR. Backward (de Groot signature): input target IRR, output max entry multiple & max offer price.
Exit Assumptions (common to both modes)
x
—yrs
Backward Mode — Target IRR
%
—solves IRR = Target
—
—
—
How Backward Mode Works
The model performs a binary search on the entry multiple, recalculating the full debt schedule and IRR at each candidate value, converging to the entry multiple that produces an IRR equal to the target (within 0.01% tolerance, typically < 30 iterations). This is the maximum bid the model supports.
Section 2 — Returns Summary
All metrics computed at exit year (entry + hold). MOIC = Equity Out / Equity In; IRR computed via Newton-Raphson iteration (no Excel functions).
—
IRR
—
MOIC
—
Exit EV
—
Exit Equity
—
Implied Exit Share Price
—
Share Price Multiple
—
Equity at Entry
—
Debt Paydown
Section 3 — Equity Value Bridge
Decomposes equity return into three drivers: EBITDA growth (operational), multiple expansion (re-rating), and debt paydown (financial engineering). The classic LBO returns attribution.
Driver
$M
% of Exit Equity
Section 1 — Sensitivity Configuration
Three traffic-light heatmaps stress-test the LBO across pricing and timing assumptions. Grids re-center on the current base case (Entry & Exit multiples from Tabs 2 and 4). Target IRR threshold marks the "feasible bid" boundary.
%
x
x
How to Read These TablesBase case (current Entry × Exit) is marked with a heavy navy border. Color gradient runs red → orange → yellow → green from lowest to highest value in each table. Target IRR line: cells at or above the threshold (Section 1 input) carry a small ☆ marker — these represent feasible bids from a PE sponsor's perspective. In the third table (Entry × Exit Year), each column tests a different exit timing — useful for understanding how the IRR/MOIC trade-off depends on hold period.
Reading Timing Sensitivity (Table 3)
Earlier exits (Years 3–4) typically show higher IRRs but lower MOICs — you're compounding return over fewer years on less debt paydown. Later exits (Years 7–10) show diminishing IRRs as the high-return early years get diluted by lower marginal-return later years. The "sweet spot" for PE sponsor exits is usually Years 4–6.
Excel Workbook Export
Generates a six-sheet Excel workbook with the full LBO build at the current state of inputs. Follows industry color-coding conventions and matches the Unilever Foods reference structure.
Workbook Contents
1
Cover
Deal summary header · current LBO output (Entry EV, Equity, IRR, MOIC) · buyer type and hold period
2
Assumptions
Base year, EBITDA path with growth/direct mode flags, UFCF drivers (D&A, Capex, ΔNWC by year), tax rate, hold, operating scenario preset
3
Sources & Uses
Side-by-side balance · offer price & FDSO · tranche sizes (TLB, Sr Unsec, optional Mezz) · fees, tender premiums, cash on hand
Forward mode summary (IRR, MOIC, equity bridge) · Backward mode (if active): max entry mult, max offer/share, premium vs current
6
Sensitivity
Three traffic-light tables: IRR(Entry × Exit), MOIC(Entry × Exit), IRR(Entry × Exit Year) — base case marked, color-coded cells
Color-coding ConventionBlue = hard-coded inputs (analyst-editable). Black = formulas / computed outputs. Gold = section headers. Green = positive output highlight (IRR, MOIC). Red = negative values. Sensitivity tables retain their traffic-light gradient.
Exam Note
Prof. de Groot's exam (Session 15) prohibits AI use and Excel financial functions. The exported workbook contains the output values computed by this tool's manual Newton-Raphson and binary-search engines — not formulas. For exam preparation, use this workbook as a check-figure reference: build your own workbook from scratch and verify your numbers against these. The structure of this workbook (sheets, layout, columns) mirrors how an answer should look on paper.
Mini Lab — Why LBO Returns Work
The four canonical scenarios below are drawn from the 2012 Economics of LBO foundation. They isolate the two mechanisms by which leverage produces equity returns: debt repayment (Scenario I) and enterprise value growth (Scenario II). The surprising result — both produce identical IRRs (24.6%). Scenarios III & IV then introduce leverage variation, showing how the same operational performance ($500 EV growth) produces a 13.4-IRR-point spread between 25% debt and 75% debt structures. This is the "wax on, wax off" foundational lesson before tackling the full Unilever model.
Interactive Sandbox
Click a scenario card above to load its parameters, then experiment with the sliders below. All inputs use a $1,000 purchase price for pedagogical clarity. The output updates in real time.
Capital Structure
75%
$250M= Purchase × (1 − Debt%)
$750M= Purchase × Debt%
Operations
50%
$0M
$1,500M= Purchase × (1 + Growth)
Cost of Debt
8.0%
Equity Outcome (Year 5 Exit)
24.6%
IRR
3.00x
MOIC
$750M
Equity Out
$250M
Debt at Exit
Equity Value Bridge — Where Did the Return Come From?
Three Key Insights
1
Debt Repayment ≡ EV Growth (when leverage is fixed)
Scenarios I and II produce identical IRRs (24.6%). Both convert $500 of "value creation" into $750 of equity at exit. The mechanism doesn't matter — what matters is that some mechanism moves value from debt-holders or operations to equity-holders. This is the core economic argument for PE: there are two paths to alpha, and most deals blend both.
2
Leverage Amplifies, Doesn't Create
Scenarios III and IV operate on the same $500 EV growth, but produce IRRs of 14.9% vs 28.3% — a 13.4-point spread purely from the capital structure choice. Higher leverage amplifies both upside and downside. Try Scenario IV with negative EV growth (slide left) — the IRR turns sharply negative. This is the symmetry PE sponsors live with.
3
Interest Eats Cash Flow
In Scenario IV (75% debt), 8% interest on $750 = $60/year — nearly the entire gross FCF. So even though leverage is higher, less debt actually gets repaid because most operating cash flow goes to interest expense. The cumulative FCF drops from $250 (Scenario III) to ~$118 (Scenario IV). This is why "covenant-lite" loose-amort structures became popular post-2010: PE sponsors learned that aggressive debt amortization can starve growth investment.