A free PME calculator template in Excel, with a complete worked fund
Kaplan-Schoar, Long-Nickels, PME+ and Direct Alpha from one cash flow series and one index, alongside DPI, RVPI, TVPI, both IRRs and a policy hurdle test: five linked sheets, one fictional fund, no macros.
The file measures Meridian Capital Partners IV, a fictional 2018-vintage fund, against a total return index over nine annual periods. On its own cash flows the fund earns a since-inception IRR of 11.07% and a TVPI of 1.83x. Against the index it beats it: a Kaplan-Schoar PME of 1.24, a Direct Alpha of 3.80% a year, and enough margin to clear a policy hurdle of 3% over the index by 101 basis points. Every one of those numbers is a formula you can move.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Five sheets, in the order the calculation runs. Blue type on a pale blue fill is an input; black type is a formula.
0. Read Me
What the workbook does, how to enter your own fund, and what it will not do for you: choose your index, date your flows, or tell you whether a subscription facility was used.
1. Inputs
The fund name, vintage, measurement date, periods per year, residual value, policy premium, and the period-by-period series: contributions, distributions and the index level. Arrives pre-filled with the Meridian fund.
2. Results
The multiples (paid-in, distributions, residual value, DPI, RVPI, TVPI, realized share), both IRRs, the index's own annualized return, all four public market equivalents with their diagnostics, and the policy hurdle test.
3. Reporting Line
The one sentence to put in a report, assembled from the cells on sheet 2, with the six disclosures that must go alongside it.
4. Checks
Five identities that hold for any fund you enter, and the six chapter 19 figures on the pre-filled example, each with a pass or fail verdict.
What the worked fund shows
Measure
Value
Since-inception IRR, annualized
11.07%
IRR excluding the residual value
7.95%
TVPI
1.83x
DPI
1.52x
Realized share of TVPI
82.8%
Kaplan-Schoar PME
1.24
Long-Nickels synthetic terminal value
-25.76 (unusable)
PME+ rate, annualized
7.05%
Direct Alpha, annualized
3.80%
Margin against a 3% policy hurdle
101 bp (cleared)
Read the multiples against the rate. TVPI of 1.83x looks strong, but 82.8% of it is already realized: the fund has returned 1.52 times its paid-in capital in cash, and the residual value is the smaller, unrealized share.
On the index, the fund's own cash flows beat the benchmark on all four PME methods that return a usable number: a Kaplan-Schoar above 1.00, a positive Direct Alpha, and a PME+ rate the fund beats by enough to clear its policy hurdle.
Long-Nickels is the one method that fails, and the file shows exactly why: it rescales each distribution by the index and drives the synthetic public fund negative here, which is why PME+ exists.
How to use it on your own fund
Open sheet 1. Inputs and select B14:D53, then delete. Every result on sheet 2 goes blank rather than showing an error.
Enter your own periods: fund name, vintage, measurement date and residual value, then the contributions, distributions and index level row by row.
Set periods per year to 4 if you have quarterly data, so the rate annualizes by compounding rather than by multiplying.
Enter the index as a level series, on the same dates as the flows. A price index instead of a total return index understates the benchmark and makes every fund look better than it is.
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 PME calculator really free?
Yes. It is a companion file to a book. There is no sign-up, no email address and no paid version.
Does it use macros or circular references?
No macros and no circular reference: every rate is solved from the cash flow series and the index level on the same rows, never from its own result. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why does Long-Nickels fail here, and what does PME+ do instead?
Long-Nickels rescales each distribution by the index and can drive the synthetic public fund negative, which is what happens on this fund: -25.76. A negative terminal value has no rate. PME+ instead scales every distribution by one constant, lambda, chosen so the scaled distributions plus the residual value compound to the same value as the contributions. That constant is always positive, so PME+ always returns a usable rate.
Can I use it for a real fund?
You can reuse the structure. The fund, the index levels 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 Private Markets Performance: the full set adds
the complete Chapter 19 measurement, the practice cases, the measurement checklist and the cohort of twenty-four. Free, like this one.