How to prepare PCAP & Waterfall Modules in Private Equity
- User
How to prepare PCAP & Waterfall Modules in Private Equity
- Assistant
Below is a practitioner-style guide for building two of the most common work-papers inside a PE fund model:
1️⃣ PCAP (Partner Capital Account) schedule – tracks every LP’s capital balance over time.
2️⃣ Distribution Waterfall – determines how each $ leaving the fund is split between LPs and the GP/Carry Vehicle.The steps are written for Excel/Google Sheets, but the logic is platform-agnostic.
Feel free to copy-paste the pseudo-formulas directly into a workbook and replace with cell references.────────────────────────────────────────── A. Preparing the PCAP (Partner Capital Account) ──────────────────────────────────────────
-
Set up your INPUTS tab • LP list: Commitment, Name, Vintage/Share Class, Mgmt-Fee % Adjustments (if any).
• Cash flow table: Date | LP | Contribution (+) | Distribution (–) | Mgmt Fee (–) | Expenses (–).
• Valuation marks: Date | Fund NAV (gross) | Unrealised Gain/(Loss) | Write-offs, etc. -
Create a dynamic calendar – One column per period you want to report (Monthly or Quarterly is typical).
– Row 2: hardcode the dates.
– Row 3: “Days in Period” for accrual items (helps with partial-period fees). -
Build the LP capital roll forward
Example header (rows ⇣, columns → time):| Beg Bal | Contrib | Mgmt Fee | Net Income | Distrib | End Bal | Cum. IRRLP_A | LP_B |
Fund Tot. |Suggested formulas inside the LP row i and period t:
BegBalₜ = EndBalₜ₋₁
Contribₜ = Σ(contributions where LP = i AND date ∈ period t) ➜ Use SUMIFS()
MgmtFeeₜ = IF(FEES_ON_COMMIT, Commit_i * MgmtRate, BegBalₜ * MgmtRate) * Daysₜ/365
NetIncomeₜ = (NAVgainₜ × LP_i_%share) ⬅️ From valuation tab
Distribₜ = Σ(distributions where LP = i AND date ∈ period t)
EndBalₜ = BegBalₜ + Contribₜ – MgmtFeeₜ + NetIncomeₜ – DistribₜTip: Keep a hidden helper column that stores the cash-flow sign convention (+Outflow from LP = Contribution) for easier IRR testing.
-
Quality checks • ΣAll_LPs EndBal must tie to Fund NAV (gross).
• ΣContributions ≤ ΣCommitments.
• Periodic “waterfall vs PCAP” proof: distributions allocated in the waterfall should equal the negative cash line in PCAP. -
Optional analytics fields – Unfunded Commitment = Commitment – ΣContributions.
– DPI (cash-on-cash) = ΣDistrib / ΣContrib.
– TVPI = (EndBal + ΣDistrib) / ΣContrib.
– XIRR per LP with Excel’s =XIRR() referencing the cash-flow row.
────────────────────────────────────────── B. Building the Distribution Waterfall ──────────────────────────────────────────
-
Understand the legal language Common European style (whole-fund):
Tier 1 – Return of Capital to LPs
Tier 2 – Preferred Return (e.g., 8% compounded)
Tier 3 – GP Catch-up (e.g., 20% to GP until GP has 20% of profits)
Tier 4 – Residual Split (80/20 LP/GP)Deal-by-Deal tweaks: you run the same tiers but triggered per‐investment with clawbacks.
-
Create a cash-flow timeline Column A : Date
Column B : Net Distributable Cash Available (NDCA) – comes from your Fund Cash-flow model.
Column C : Cumulative Contributed Capital (CCC).
Column D : Remaining Preferred Return (Pref Bal).
Column E : Allocation to each tier. -
Mechanics by tier (illustrative formulas)
Pref Return Accrual each period
PrefBalₜ = PrefBalₜ₋₁ × (1+PrefRate×Daysₜ/365) + Contribₜ – Distributions_applied_to_Tier1ₜTier 1: Return of Capital
Allocationₜ = MIN(NDCAₜ, CCC_remaining)
CCC_remaining ⇣ as it is repaid.Tier 2: Preferred Return
Allocationₜ = MIN(NDCA_remaining, PrefBalₜ)
PrefBalₜ reduces after payment.Tier 3: Catch-up (assume 80/20 LP/GP catch-up until GP receives 20% of total)
Allocation_GPₜ = MIN(NDCA_remaining × 100%, CatchUpCap – CumGPfromTier3)
Allocation_LPₜ = NDCA_remaining – Allocation_GPₜTier 4: Residual Split
Allocation_LPₜ = NDCA_remaining × 80%
Allocation_GPₜ = NDCA_remaining × 20% -
Code it in Excel without circularities – Separate the PREF ACCRUAL (which can be done period-by-period) from the IRR checks.
– If you MUST do an IRR-driven hurdle (instead of simple pref accrual) use XIRR in a helper column but expect iterations / circular refs ⇒ allow iterative calc with a low max-change. -
Push waterfall outputs into PCAPs For each distribution event, allocate the LP share pro-rata by % ownership OR by deal-specific exposure and book that amount in the PCAP “Distribution” column.
GP/Carry distributions flow to a “GP Capital Account” schedule (good practice for audit). -
Test the build • Back-solve with a “dummy” cash-flow (e.g., single contribution, single distribution) and verify the split equals what the LPA says.
• Stress with zero exits → no carry should be paid.
• Stress with mega exit → GP carry should equal 20% (or whatever %) of fund-level profit.
────────────────────────────────────────── C. Practical Tips & Common Pitfalls ──────────────────────────────────────────
• Time buckets: Monthly buckets make fee accrual smoother and reduce pref compounding error vs. annual.
• Sign convention: cash FROM investors = +ve in waterfall but –ve IRR for LPs; be consistent.
• Separate inputs, calcs, outputs. Lock the inputs tab so associates can’t “fix numbers in the model”.
• Don’t mix realised and unrealised inside the waterfall ‑ only realised cash passes through. Unrealised gains/marks sit in PCAP for performance reporting.
• Document every assumption in a Notes column; auditors will ask.
• If using VBA or Python, write a unit test per tier to ensure correct split before layering complexity.────────────────────────────────────────── D. Skeleton Template (pseudo-layout) ────────────────────────────────────────── Tabs:
- _Inputs
- _Valuation
- _PCAP
- _Waterfall
- _Analytics / KPIs
- _Checks
Example PCAP sheet:
LP BegBal Contrib MgmtFee NetInc Distrib EndBal DPI TVPI XIRR LP_A link formula formula link link calc .. .. .. GP Carry .. .. .. .. .. .. Fund Tot. SUM SUM SUM SUM SUM SUM Waterfall sheet (European style):
Date NDCA Tier1 ROC Tier2 Pref Tier3 CatchUp_LP Tier3 CatchUp_GP Tier4 LP Tier4 GP Notes ... link formula formula formula formula formula formula ────────────────────────────────────────── E. Where to deepen your knowledge ────────────────────────────────────────── • ILPA Reporting Template – shows PCAP line items and standard definitions.
• AICPA Accounting & Valuation Guide: Investment Companies.
• “Inside the Black Box of Private Equity” (Burgiss Institute papers) on waterfall math.
• YouTube: “Macabacus Private Equity Waterfall Tutorial” (hands-on Excel example).
• Templates: Macabacus, CFI PE Modeling Course provide downloadable waterfall/PCAP models.────────────────────────────────────────── Wrap-up ──────────────────────────────────────────
- Build the PCAP first – it is your ledger.
- Layer the waterfall – it is the rules engine for distributing real cash.
- Reconcile the two every period.
- Stress-test, audit, annotate.
Master these two modules and 80% of fund-level modeling questions in PE interviews, investor reporting, and audits become routine. Happy modeling! 🎯
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.