Skip to content
TheChatArchiveThe Chat Archive
o3-2025-04-16· OpenAIHugging Face LMArena Human Preference 140K

How to prepare PCAP & Waterfall Modules in Private Equity

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Coherecommand-a-03-2025’s answer.
  1. User

    How to prepare PCAP & Waterfall Modules in Private Equity

  2. 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) ──────────────────────────────────────────

    1. 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.

    2. 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).

    3. Build the LP capital roll forward
      Example header (rows ⇣, columns → time):

            | Beg Bal | Contrib | Mgmt Fee | Net Income | Distrib | End Bal | Cum. IRR
      

      LP_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.

    4. 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.

    5. 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 ──────────────────────────────────────────

    1. 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.

    2. 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.

    3. 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%

    4. 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.

    5. 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).

    6. 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:

    1. _Inputs
    2. _Valuation
    3. _PCAP
    4. _Waterfall
    5. _Analytics / KPIs
    6. _Checks

    Example PCAP sheet:

    LPBegBalContribMgmtFeeNetIncDistribEndBalDPITVPIXIRR
    LP_Alinkformulaformulalinklinkcalc......
    GP Carry............
    Fund Tot.SUMSUMSUMSUMSUMSUM

    Waterfall sheet (European style):

    DateNDCATier1 ROCTier2 PrefTier3 CatchUp_LPTier3 CatchUp_GPTier4 LPTier4 GPNotes
    ...linkformulaformulaformulaformulaformulaformula

    ────────────────────────────────────────── 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 ──────────────────────────────────────────

    1. Build the PCAP first – it is your ledger.
    2. Layer the waterfall – it is the rules engine for distributing real cash.
    3. Reconcile the two every period.
    4. 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! 🎯

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.