Reference · PE & returns

Private Equity & Returns Models

Load when **PE & returns**.

Part of the Financial Modeling skill · loaded on demand from SKILL.md

Six recipes: LP/GP waterfall, value-creation bridge, management incentive plan, fund (J-curve), IRR/MOIC calculator, dividend recap. Shared mechanics in references/build-engine.md. These lean on XIRR over dated flows — always supply a 0.1 guess and confirm sign sanity (at least one outflow and one inflow).

PE Returns Waterfall (pe-returns-waterfall)

Split fund/deal proceeds between LPs and GP through return of capital, preferred, GP catch-up, and carry. Default European (whole-fund) with an American (deal-by-deal) toggle.

When: "carried interest", "preferred return hurdle", "GP catch-up", "LP/GP split", "distribution waterfall".

Tabs: Cover · Assumptions · Cash Flows (dated) · Waterfall (4 tiers) · Summary · Checks. Named ranges: Hurdle, Carry, CatchUpGPShare, WaterfallType.

Assumptions: Hurdle (e.g. 0.08), Carry (0.20), CatchUpGPShare (1.00 full, or 0.20 for 80/20), GP commitment %, WaterfallType list ("European"/"American").

Waterfall (one row per distribution period; cascade remaining cash):

  • Distributable =CashFlows!GrossDistribution.
  • Tier 1 RoC (100% LP until contributed capital returned) =MIN(Distributable, MAX(0, CumContrib - PriorRoC)).
  • Tier 2 Preferred — accrue PrefAccrued =OutstandingCapital*Hurdle*(Days/365) (Days = date diff); Tier 2 LP =MIN(RemainingAfterT1, MAX(0, CumPrefAccrued - PriorPrefPaid)).
  • Tier 3 GP Catch-up — target =Carry/(1-Carry)*CumPrefPaid (full catch-up); Tier 3 GP =MIN(RemainingAfterT2, MAX(0, Target - PriorGPCatchUp)).
  • Tier 4 Carry split on residual: LP =RemainingAfterT3*(1-Carry), GP =RemainingAfterT3*Carry.
  • LP total =T1+T2+T4_LP; GP total =T3+T4_GP; cumulative columns. American: wrap tiers in =IF(WaterfallType="European", whole-fund, deal-level) resetting capital/pref per deal.

Summary: LP MOIC =LP_Distributed/LP_Contributed; LP net & gross IRR via XIRR on dated flows; GP carry $; realized carry % =GP_Carry/Profit_Above_RoC; DPI.

Checks: tiers sum to total (ROUND(...,2)=0); LP+GP = total; realized carry = target after full catch-up; no negative tiers; T1_GP=0 and T2_GP=0; catch-up ≤ target; net IRR ≤ gross.

Inputs: dated fund/deal cash flows, committed & contributed capital, hurdle, carry %, catch-up basis, European/American.

Example: LPs contribute 100 (y0–2), gross distributions 60/80/90 (y3–5); hurdle 8%, carry 20%, full catch-up → LP net MOIC ≈ 1.8x; GP carry ≈ 20% of profit above capital. Illustrative.

Value-Creation Bridge (value-creation-bridge)

Decompose the equity gain over a hold into EBITDA growth, multiple expansion/contraction, and deleveraging/FCF, tying to MOIC and IRR.

When: "why a deal made money", "attribute returns", "value bridge", "operational vs financial drivers".

Tabs: Cover · Inputs · Bridge · Chart (waterfall) · Checks. Named ranges: EBITDA_in/out, Mult_in/out, ND_in/out, Div.

Bridge:

  • Entry equity =EBITDA_in*Mult_in - ND_in; exit equity =EBITDA_out*Mult_out - ND_out.
  • Effect 1 EBITDA growth =(EBITDA_out-EBITDA_in)*Mult_in.
  • Effect 2 Multiple =(Mult_out-Mult_in)*EBITDA_out.
  • Effect 3 Deleveraging/FCF =ND_in-ND_out.
  • Effect 4 Dividends =Div (optional).
  • Reconstructed exit =Entry + E1+E2+E3+E4; MOIC =(ExitEquity+Div)/EntryEquity; IRR via XIRR or =MOIC^(1/Years)-1.
  • Cross-attribution: EBITDA effect on entry multiple, multiple effect on exit EBITDA — both interaction terms land in the multiple effect; state this. Optional symmetric split toggle: E1 =(ΔEBITDA)*(Mult_in+Mult_out)/2, E2 =(ΔMult)*(EBITDA_in+EBITDA_out)/2.

Chart: stacked BarChart with invisible base series; first/last bars (Entry, Exit) sit on a zero base as totals. Percent-of-gain per effect =Effect_n/(ExitEquity+Div-EntryEquity).

Checks: effects reconcile to the equity change (ROUND(...,2)=0); reconstructed = exit + div; MOIC > 0; effect-2 sign = sign(ΔMult); effect-3 sign = sign(ND_in−ND_out); %-of-gain sums to 100%; entry equity > 0 (guards MOIC). Guard Years if entry/exit dates match.

Inputs: entry/exit EBITDA, entry/exit EV/EBITDA, entry/exit net debt, entry/exit dates, dividends.

Example: entry EBITDA 50 @ 8.0x, ND 200 → entry equity 200; exit EBITDA 75 @ 9.0x, ND 120 → exit equity 555; E1 200, E2 75, E3 80 sum to 355 (the gain); MOIC ≈ 2.8x. Illustrative.

Management Incentive Plan (management-incentive-plan)

Pro forma cap table + exit split paying a preferred/loan-note stack first, then dividing ordinary equity, with a management ratchet keyed to sponsor IRR or MOIC.

When: "management equity", "sweet equity", "ratchet", "exit waterfall for ordinary shares".

Tabs: Cover (ratchet basis toggle MOIC/IRR) · Cap Table · MIP (ratchet bands) · Exit Returns · Checks. Named ranges: OrdEquityTotal, PrefBalance, SponsorOrd%, MgmtOrd%, PoolOrd%.

Cap Table: rows for sponsor loan notes/preferred, sponsor ordinary, management sweet equity, option pool; ordinary ownership % =OrdinaryInvested_row/SUM(Ordinary invested).

MIP ratchet: band table — threshold (sponsor MOIC e.g. 2.0/2.5/3.0x, sorted ascending, starting at 0.0x) × management ordinary % (non-decreasing). Applied mgmt % =CHOOSE(MATCH(SponsorMOIC, Thresholds, 1), Band1, Band2, ...). Ratchet key =IF(RatchetBasis="MOIC", PreRatchetSponsorMOIC, PreRatchetSponsorIRR).

Exit Returns: preferred redemption =PrefBalance*(1+Coupon)^Years, paid first =MIN(ExitEquity, AccretedPref); residual ordinary =ExitEquity-PrefPaid; mgmt proceeds =Residual*AppliedMgmtPct; sponsor proceeds =PrefPaid+Residual*(1-AppliedMgmtPct); MOIC each =Proceeds/Invested; IRR via XIRR. Break the ratchet circularity by keying on the pre-ratchet sponsor MOIC: =(PrefPaid+Residual*(1-MgmtBasePct))/SponsorInvested, so the band lookup never depends on its own output. Value transferred by the ratchet =Residual*(AppliedMgmtPct-MgmtBasePct).

Checks: ownership sums to 100%; proceeds reconcile to exit equity; bands non-decreasing; pref fully redeemed before ordinary if cash allows; applied mgmt % ≥ base; ratchet transfer ≥ 0. Add a 0.0x base band so MATCH never returns #N/A.

Edge cases: exit below accreted pref → ordinary gets nothing, sweet-equity MOIC 0 (never negative); option pool funded from ordinary dilutes pro rata, net it out before applying the base %.

Inputs: instruments & amounts, coupon/PIK rates, entry/exit dates, exit equity, ratchet thresholds & percentages.

Example: sponsor 90 (80 loan notes @ 8% PIK, 10 ordinary), mgmt 2 sweet, pool 8%; at exit 300, notes accrete ≈ 117 paid first, residual 183 splits with mgmt at the 2.5x band; sponsor MOIC ≈ 2.5–3.0x. Illustrative.

Fund Model — J-Curve (fund-model)

Draw capital against commitments, charge fees, grow portfolio value, distribute, and produce LP net cash flow and DPI/RVPI/TVPI/net IRR.

When: "fund cash flows", "J-curve", "paid-in vs distributions", "fund-level returns", "DPI/TVPI".

Tabs: Cover · Assumptions · Calls & Distributions · NAV · Metrics · Checks. Named ranges: Commitment, FeeRate, Growth, InvPeriod, FundLife.

Calls & Distributions (per period): capital called =MIN(Commitment*CallPace, Commitment-PriorCumCalls); cumulative called =SUM; management fee =IF(Year<=FeeStepDown, FeeRate*Commitment, FeeRate*InvestedCapital); distributions =NAV!realized; net LP cash flow =-(Called) - Fee + Distribution (dips negative early = the J-curve, then turns positive).

NAV: opening =PriorClosing; + invested =Called-Fee; + growth =Opening*Growth; − distributions; closing =Opening+Invested+Growth-Distributions. Final-period NAV = residual unrealized used in RVPI.

Metrics: paid-in =SUM(Called)+SUM(Fees) (state basis); DPI =SUM(Distributions)/PaidIn; RVPI =EndingNAV/PaidIn; TVPI =DPI+RVPI; net IRR =XIRR(net LP flows with final NAV as a terminal inflow, dates); uncalled =Commitment-CumCalls; J-curve trough =MIN(CumNetCashFlow) + its period via INDEX/MATCH.

Checks: cumulative calls ≤ commitment; TVPI = DPI+RVPI; uncalled ≥ 0; NAV ≥ 0 every period; net IRR ≤ gross; DPI & RVPI ≥ 0. Include the terminal NAV inflow so XIRR converges; supply a guess.

Inputs: commitment, investment period & fund life, fee rate & basis + step-down, call/distribution pacing, portfolio growth.

Example: 100 commitment, 5y invest, 2% fee committed→invested, 12% growth, calls 20%/yr, distributions from y4 → negative LP cash y1–3 (trough), then positive; DPI 1.4x, RVPI 0.6x, TVPI 2.0x, net IRR mid-teens. Illustrative.

IRR / MOIC Calculator (irr-moic-calculator)

General returns engine on a dated cash-flow stream: IRR, XIRR, MOIC, gross vs net of fees & carry, holding-period return, and an exit-value × exit-year sensitivity.

When: "compute IRR", "MOIC on a cash-flow stream", "gross vs net returns", "exit sensitivity".

Tabs: Cover · Cash Flows (dated; negative invested, positive returned) · Returns · Sensitivity · Checks. Named ranges: Flows, Dates, Carry, FeeRate.

Returns: IRR =IRR(Flows); XIRR =XIRR(Flows,Dates,0.1); MOIC =Inflows/-Outflows; holding years =(MAX(Dates)-MIN(Dates))/365; HPR =MOIC-1. Gross vs net: net inflow =GrossInflow - MgmtFees - MAX(0, GrossProfit*Carry); net IRR =XIRR(NetFlows,Dates,0.1); net MOIC =NetInflows/-Outflows. Show side by side. Annualized check =MOIC^(1/HoldYears)-1 ≈ XIRR for single-in/single-out; flag large gaps (interim-flow timing).

Sensitivity: rows = exit value, columns = exit year. Each grid cell is a self-contained =XIRR over the original outflows plus one exit inflow dated at the column's exit date (do not rely on Data Tables for this; write explicit per-cell XIRR). Pair with a MOIC grid =RowExitValue/-Outflows. Colour-scale both.

Checks: XIRR converges (ISNUMBER); MOIC > 0; sign sanity (MIN(Flows)<0 AND MAX(Flows)>0); net ≤ gross; MOIC−1 = HPR; sensitivity anchor matches base.

Edge cases: all-one-sign streams have no IRR (sign check catches it); same-day flows break the holding divisor (collapse/nudge dates); very high returns may need a 0.5 guess.

Inputs: dated flows + signs, fees & carry for the net view, entry date, exit-value & exit-year ranges.

Example: invest −100, receive +250 after 5y → MOIC 2.5x, XIRR ≈ 20%, HPR 150%; after 2% fee + 20% carry on 150 profit, net MOIC and IRR drop modestly. Illustrative.

Dividend Recap (dividend-recap-model)

Size incremental debt to a target leverage, fund a dividend net of fees, and show how the early cash inflow lifts sponsor IRR while MOIC barely moves.

When: "dividend recap", "leveraging up to pay a dividend", "return impact of an interim distribution".

Tabs: Cover (exit-equity basis toggle) · Assumptions · New Debt Capacity · Recap · Returns Impact · Checks. Named ranges: EBITDA, ExistingND, TargetLev, NewRate, FeePct.

New Debt Capacity: target net debt =TargetLev*EBITDA; gross incremental =MAX(0, TargetND-ExistingND); fees =GrossIncremental*FeePct; net dividend capacity =GrossIncremental-Fees.

Recap: dividend =MIN(NetDividendCapacity, ProposedDividend); pro forma net debt =ExistingND+GrossIncremental; pro forma leverage =ProFormaND/EBITDA; new interest =ProFormaND*NewRate; coverage =EBITDA/TotalInterest.

Returns Impact: pre-recap flows {-OrigEquity@Entry, ExitEquity@Exit}, pre MOIC/IRR via XIRR; post-recap exit equity =ExitEquity-Dividend (enterprise-driven; state the assumption); post flows {-OrigEquity@Entry, +Dividend@Recap, +PostExit@Exit}; post MOIC =(Dividend+PostExit)/OrigEquity; post IRR =XIRR(...,0.1). Show IRR delta and MOIC delta; the dividend pulls cash forward so IRR rises while MOIC stays ~flat. Years to recap =(RecapDate-EntryDate)/365 is the lever.

Checks: post-recap leverage = target (at full capacity); dividend ≤ capacity; coverage ≥ min; IRR improved; pro forma ND = existing + gross incremental; dividend ≥ 0 (no recap if target < existing). Guard coverage denominator if no incremental debt drawn.

Inputs: EBITDA, existing net debt, target net debt/EBITDA, new rate & fees, min coverage & cash, original equity, projected exit equity, entry/recap/exit dates.

Example: EBITDA 50, existing ND 150 (3.0x), target 4.5x → target ND 225, gross 75, fees 2% → net dividend ≈ 73.5; pro forma 4.5x, coverage above 2.0x; with equity 100 and exit 300, a ~74 dividend at recap lifts IRR several points, MOIC stays ≈ 2.7–2.9x. Illustrative.