Free Excel templates

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.

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.

PME_Calculator.xlsx (26 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.

The book behind this model

This file is one worked case from Private Markets Performance. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

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

Get the Library, $79 →

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

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

MeasureValue
Since-inception IRR, annualized11.07%
IRR excluding the residual value7.95%
TVPI1.83x
DPI1.52x
Realized share of TVPI82.8%
Kaplan-Schoar PME1.24
Long-Nickels synthetic terminal value-25.76 (unusable)
PME+ rate, annualized7.05%
Direct Alpha, annualized3.80%
Margin against a 3% policy hurdle101 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

  1. Open sheet 1. Inputs and select B14:D53, then delete. Every result on sheet 2 goes blank rather than showing an error.
  2. Enter your own periods: fund name, vintage, measurement date and residual value, then the contributions, distributions and index level row by row.
  3. Set periods per year to 4 if you have quarterly data, so the rate annualizes by compounding rather than by multiplying.
  4. 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.
  5. 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. 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: 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.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from Private Markets Performance. The book is on Amazon.

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.