PE Returns Analysis & Sensitivity Tables
Build IRR/MOIC sensitivity tables across entry multiple, leverage, exit multiple, growth, and hold period scenarios for PE deal evaluation.
Before you present a deal to the investment committee, you need to stress-test returns across dozens of scenarios — entry multiple, leverage, exit multiple, growth rate, hold period — and a single spreadsheet error in the returns math can kill your credibility.
Who it's for: PE associates, vice presidents, financial modelers, deal team analysts
Example
"Model returns at 8x entry, 4x leverage, 10% EBITDA growth, exit at 10x in 5 years" → Base case returns with IRR/MOIC, returns attribution waterfall, 2-way sensitivity matrices, and bull/base/bear scenario comparison
New here? 3-minute setup guide → | Already set up? Copy the template below.
# Returns Analysis
description: Build quick IRR/MOIC sensitivity tables for PE deal evaluation. Models returns across entry multiple, leverage, exit multiple, growth, and hold period scenarios. Use when sizing up a deal, stress-testing assumptions, or preparing IC returns exhibits. Triggers on "returns analysis", "IRR sensitivity", "MOIC table", "what's the return at", "model the returns", or "back of the envelope".
## Workflow
### Step 1: Gather Deal Inputs
Ask for (or extract from prior analysis):
**Entry:**
- Entry EBITDA (LTM or NTM)
- Entry multiple (EV / EBITDA)
- Enterprise value
- Net debt at close
- Equity check size
- Transaction fees & expenses
**Financing:**
- Senior debt (x EBITDA, rate, amortization)
- Subordinated debt / mezzanine (if any)
- Total leverage at entry (x EBITDA)
- Equity contribution
**Operating Assumptions:**
- Revenue growth rate (annual)
- EBITDA margin trajectory
- Capex as % of revenue
- Working capital changes
- Debt paydown schedule
**Exit:**
- Hold period (years)
- Exit multiple (EV / EBITDA)
- Exit EBITDA (calculated from growth assumptions)
### Step 2: Base Case Returns
Calculate:
| Metric | Value |
|--------|-------|
| Entry EV | |
| Equity invested | |
| Exit EBITDA | |
| Exit EV | |
| Net debt at exit | |
| Exit equity value | |
| **MOIC** | |
| **IRR** | |
| Cash-on-cash | |
Show the returns waterfall:
- EBITDA growth contribution
- Multiple expansion/contraction contribution
- Debt paydown contribution
- Fee/expense drag
### Step 3: Sensitivity Tables
Build 2-way sensitivity matrices:
**Entry Multiple vs. Exit Multiple**
| | Exit 6x | Exit 7x | Exit 8x | Exit 9x | Exit 10x |
|---|---------|---------|---------|---------|----------|
| Entry 7x | | | | | |
| Entry 8x | | | | | |
| Entry 9x | | | | | |
| Entry 10x | | | | | |
**EBITDA Growth vs. Exit Multiple** (at fixed entry)
**Leverage vs. Exit Multiple** (at fixed entry and growth)
**Hold Period vs. Exit Multiple**
Show both IRR and MOIC in each cell (IRR / MOIC format).
### Step 4: Scenario Analysis
Build 3 scenarios:
| | Bull | Base | Bear |
|---|------|------|------|
| Revenue CAGR | | | |
| Exit EBITDA margin | | | |
| Exit multiple | | | |
| Exit EBITDA | | | |
| MOIC | | | |
| IRR | | | |
### Step 5: Output
- Excel workbook with:
- Assumptions tab
- Returns calculation
- Sensitivity tables (formatted with conditional coloring)
- Scenario summary
- One-page returns summary suitable for IC deck
## Key Formulas
- **MOIC** = Exit Equity Value / Equity Invested
- **IRR** = solve for r: Equity Invested × (1 + r)^n = Exit Equity Value (adjust for interim cash flows)
- **Returns attribution**:
- Growth: (Exit EBITDA - Entry EBITDA) × Exit Multiple / Equity
- Multiple: (Exit Multiple - Entry Multiple) × Entry EBITDA / Equity
- Leverage: Debt paydown over hold period / Equity
## Important Notes
- Always show returns both gross and net of fees/carry where applicable
- Management rollover and co-invest change the equity check — ask if relevant
- Dividend recaps or interim distributions affect IRR significantly — include if planned
- Don't forget transaction costs (typically 2-4% of EV) — they reduce Day 1 equity value
- Tax considerations (asset vs. stock deal, 338(h)(10) election) can materially affect after-tax returns
What This Does
Builds quick IRR/MOIC calculations with full sensitivity tables for PE deal evaluation. Models returns across entry multiple, leverage, exit multiple, EBITDA growth, and hold period scenarios. Produces IC-ready returns exhibits with attribution waterfall and scenario analysis.
Quick Start
Step 1: Create a Project Folder
Create a folder for your returns analysis and place the downloaded template inside as CLAUDE.md.
Step 2: Download the Template
Click Download above, then move the file into your project folder as CLAUDE.md.
Step 3: Start Working
"Model returns at 8x entry, 4x leverage, exit at 10x in 5 years"
"Build an IRR sensitivity table for this deal"
"What's the return if we pay 9x and exit at 8x?"
What Gets Calculated
Base Case Returns:
- Entry EV, equity invested, exit EBITDA, exit EV, exit equity value
- MOIC and IRR
- Cash-on-cash return
Returns Attribution Waterfall:
- EBITDA growth contribution
- Multiple expansion/contraction contribution
- Debt paydown contribution
- Fee/expense drag
Sensitivity Tables (2-way matrices):
- Entry Multiple vs. Exit Multiple
- EBITDA Growth vs. Exit Multiple
- Leverage vs. Exit Multiple
- Hold Period vs. Exit Multiple
Scenario Analysis:
- Bull, Base, and Bear cases with revenue CAGR, exit margin, exit multiple, MOIC, and IRR
Key Formulas
- MOIC = Exit Equity Value / Equity Invested
- IRR = solve for r where Equity Invested x (1 + r)^n = Exit Equity Value
- Growth attribution: (Exit EBITDA - Entry EBITDA) x Exit Multiple / Equity
- Multiple attribution: (Exit Multiple - Entry Multiple) x Entry EBITDA / Equity
- Leverage attribution: Debt paydown over hold period / Equity
Tips & Best Practices
- Always show returns both gross and net of fees/carry where applicable
- Management rollover and co-invest change the equity check — ask if relevant
- Dividend recaps or interim distributions affect IRR significantly — include if planned
- Do not forget transaction costs (typically 2-4% of EV) — they reduce Day 1 equity value
- Tax considerations (asset vs. stock deal, 338(h)(10) election) can materially affect after-tax returns