A free private equity fund cash flow model in Excel, with a complete worked programme
Capital calls and distributions as inputs, the J-curve of net capital at work, the multiples, the IRR and the over-commitment a target allocation needs: six sheets, one fictional programme, no macros.
The file models one private equity commitment from first call to last distribution. The Thornbury Foundation commits 600 to Brayfield Partners; 564 is called over five years, 791 comes back and 143 is still held at the end of Year 10. The fund returns 13.29% and 1.66x, but net capital at work peaks at 383 in Year 4, so 36.17% of the commitment never works. To hold 600 genuinely invested on the same schedule, the foundation would have had to commit 939.95, an over-commitment of 56.66%. Amounts are in millions, without a currency, as in the book.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Private_Equity_Fund_Cash_Flow_Model.xlsx (29 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from Alternative Investments. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
Go further: build every LP's calls and distributions from the fund terms
Private Fund Finance & Controllership: capital calls and equalization, fees and offsets, both waterfalls, NAV and capital accounts, IRR and PME, fund financing and ILPA reporting, on one fictional fund. Course Book, Excel exercises that check your answers, a 160-question quiz and the Private Equity Fund Model, Pro Edition included ($199).
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets, in the order the calculation runs. Inputs are blue type on a yellow fill and sit on one sheet; everything else is a formula.
Read me
What the workbook computes, the first experiment to run (push every call one year later) and the revision notes.
Inputs
The foundation and the commitment, the fee and benchmark rates, and ten years of capital calls, distributions, index and cash returns.
Engine
Fund net flows and cumulative net capital at work, the sleeve with its uncalled balance held in cash or in the listed index, compounding factors and the totals.
Multiples and the Ramp
TVPI, DPI and RVPI, the J-curve year by year, the peak against the commitment, the commitment required to hold the target, and the fund IRR with the residual written down from 0 to 100 per cent.
The Ramp in Detail
The rates a multiple implies over four to twelve years, the slow-exit case with every distribution two years later, the schedule at 939.95, the denominator effect and the IRR timing experiments.
Checks
72 figures printed in the book set against what the workbook computes, each to half a unit of the last printed digit, with a verdict on each line.
What the worked programme returns
Measure
Value
Capital called / distributed / residual
564 / 791 / 143
Fund IRR, residual at carrying value
13.29%
TVPI / DPI / RVPI
1.66x / 1.40x / 0.25x
Peak net capital at work (Year 4)
383
Peak as a share of the commitment
63.83%
Commitment required to hold 600 at work
939.95
Over-commitment
56.66%
Peak if every distribution comes two years later
535
Fund IRR if the residual is written off
9.82%
Read the peak before the return. A commitment of 600 never had more than 383 at work, because 115 had already come back before the Year 4 peak. The over-commitment of 56.66% belongs to this call and distribution schedule, not to private equity in general: slow the exits by two years and the peak rises to 535. That is the number a commitment pacing paper needs, and it is the risk of committing 939.95 on someone else's schedule.
How to use it on your own case
Open Inputs and overwrite the blue cells: the commitment, the target allocation, the residual value and the ten years of calls and distributions.
Read the J-curve on Multiples and the Ramp first: the peak net capital at work and the year it falls in.
Read the commitment required to hold the target. It is the target squared over the peak, so it moves with every call and distribution you change.
Run the experiment the Read me sets: push every call one year later. The multiples do not move; the IRR, the peak and the over-commitment do.
Use the residual write-down table to see how much of the IRR rests on value not yet distributed.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this private equity cash flow model really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. The IRR is Excel's IRR function on the ten annual net flows, and the over-commitment is a closed-form ratio. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why does the commitment have to be 939.95 and not 600?
Calls and distributions overlap, so net capital at work peaks at 383, or 63.83% of what was committed. Holding 600 at the peak takes 600 divided by that ratio: 939.95. Change the schedule and the ratio changes, which is why the file computes it rather than using a rule of thumb.
Can I use it for a real fund commitment?
You can reuse the structure. The foundation, the manager and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Alternative Investments: the full set adds The Three Returns, the public market equivalent workbook and the smoothing, fees and exit workbook, with the four models emptied of inputs. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.