Private Equity & Credit Fund Model: Waterfall, TVPI, IRR – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Shows the gross-to-net bridge explicitly: a 2.10x gross fund delivering 1.67x net TVPI, with the 0.43x fee-and-carry drag isolated as its own output.
2. European waterfall with preferred return and a fully solved GP catch-up, built with no circular references, so results never drift between recalculations.
3. 20 built-in integrity checks plus an independent 374-point re-derivation of every output, so you can audit the arithmetic before you rely on it.

PRIVATE EQUITY EXCEL DESCRIPTION

Private Equity & Credit Fund Model: Waterfall, TVPI, IRR is an Excel template (XLSX) available for immediate download upon purchase.

A fund that exits at 2.10x gross hands its LPs 1.67x net. The 0.43x in between is management fees, organisational costs and carried interest, and it costs 4.6 percentage points of IRR. Most fund models never show that difference on a single screen. This one makes it the headline output.

This is a complete limited partnership model for a private equity or private credit fund: capital calls, deployment, management fees, exits, a European (whole-of-fund) distribution waterfall with preferred return and full GP catch-up, LP and GP cash flows, and a returns page that reconciles gross multiple to net TVPI, DPI, RVPI and IRR.

WHAT YOU GET

Six worksheets: Read Me, Assumptions, Fund, Waterfall, Returns and Checks. 424 live formulas across a 10-year annual horizon. Every input sits on one Assumptions tab in blue; nothing is hard-coded inside a calculation. 20 integrity checks run in the workbook and all 20 pass on delivery. Beyond the in-file checks, every output in this model was re-derived by a separate independent script using a different method: 374 of 374 figures matched.

THREE THINGS THIS MODEL DOES THAT MOST FUND MODELS DO NOT

1. Preferred return accrues on the opening balance. Charging the hurdle on the closing balance of unreturned capital creates a circular reference, which is why so many fund models ship with iterative calculation switched on and a result that drifts every time you press F9. Here the accrual is taken on the opening balance of unreturned contributions, so the workbook is fully deterministic: no circularity, no iteration setting, no macros. Check 13 proves it.

2. The catch-up is solved, not approximated. Tier 3 pays the GP exactly until cumulative carry equals the carry rate applied to profit distributed to date, which is what a full catch-up actually means. Most templates either skip the tier or apply a flat percentage that leaves the split wrong by a few million on a mid-sized fund. Check 15 rebuilds total carried interest from the profit split and check 16 tests the clawback position at the end of fund life.

3. The gross-to-net bridge is on the surface. Realised gross multiple, net TVPI, and the gap between them as its own line, in multiple terms and in IRR terms. That gap is precisely what the LP pays the GP. It belongs in view, not buried three tabs away.

THE WORKED EXAMPLE SHIPPED IN THE FILE

A $250m fund. GP commitment 2%. Five-year investment period inside a ten-year life, 85% of commitments deployed into investments. Management fee 2.0% of commitments during the investment period stepping to 1.5% of unrealised cost thereafter, organisational costs 0.5% one-off. Exits after a five-year hold at a 2.10x gross multiple. Hurdle 8%, carry 20%, full catch-up.

Total capital called $248.2m of $250m committed. LP contributions $243.2m, distributions to LPs $405.6m. Gross multiple 2.10x, net TVPI 1.67x, DPI 1.67x, RVPI 0.00x at the end of life. Gross IRR 16.0%, net LP IRR 11.4%. Total fees and costs $35.7m, GP carried interest $40.6m, total fee-and-carry drag 0.43x of multiple and 4.6 points of IRR.

Change the exit multiple, the hold period, the deployment pace, the fee basis, the hurdle, the carry rate or the catch-up and every one of those figures moves consistently, with the 20 checks continuing to police the arithmetic.

WHO USES IT

GPs sizing a fund and testing what a fee and carry structure leaves for LPs. LPs and fund-of-funds analysts stress-testing a sponsor's promised net returns. Placement agents, fund administrators and advisers who need a defensible waterfall they can hand to a client. Candidates preparing for private equity and credit interviews, where the European waterfall with catch-up is the standard technical question.

WHAT IT DOES NOT DO

It is a European, whole-of-fund waterfall, not a deal-by-deal (American) one, although the tiers sit on balances that a deal-by-deal variant can be built from. It does not model fund-level leverage, a subscription credit line, recycling of proceeds, management fee offsets or secondary transfers of LP interests. It assumes a uniform hold period and a uniform exit multiple across investments; the shape of the cash flows comes from the deployment schedule, not from an assumption about individual exits. Saying so up front is more useful than a feature list that quietly overstates what is inside.

FREQUENTLY ASKED QUESTIONS

Does it contain macros? No. The file is formula-only, with no VBA, no add-ins and no external links. It opens and calculates in Excel for Windows and Mac, in Google Sheets and in LibreOffice.

Do I need iterative calculation switched on? No. The model has no circular references anywhere, by design. That is the single most common defect in fund waterfall templates and the reason this one accrues the preferred return on opening balances.

Can I change the hurdle, carry and catch-up? Yes. Hurdle rate, carry rate and the catch-up percentage are all inputs. A 100% catch-up, a 50% catch-up and a no-catch-up waterfall are all a single cell change, and the checks verify the split in each case.

Is the horizon fixed at 10 years? The delivered horizon is 10 annual periods, which covers the life of a standard closed-end fund including a two-year extension. The structure extends by copying columns; the checks and the waterfall logic follow.

How do I know the numbers are right? Two ways, both visible to you. A Checks tab with 20 named tests that must all read PASS, covering sources equal uses, capital called never exceeding commitments, the waterfall tiers summing to distributions, the preferred return accrual, the catch-up ceiling, clawback and the gross-to-net reconciliation. And the independent re-derivation described above, 374 of 374 figures matched by a second method.

Is it unlocked? Yes. No password, no hidden sheets, no protected cells. Every formula is readable and editable so you can audit it and adapt it to your own structure.

What about credit funds specifically? The same engine runs a private credit fund: deploy capital, take cash yield and repayments rather than a single exit multiple, and the fee, hurdle, catch-up and net return mechanics are identical. The Read Me tab sets out which assumptions to change for a credit mandate.

Delivered as a single .xlsx file. One-time purchase, lifetime use, unlimited projects.

Source: Best Practices in Private Equity, Integrated Financial Model Excel: Private Equity & Credit Fund Model: Waterfall, TVPI, IRR Excel (XLSX) Spreadsheet, Balancewright