Tracking a private equity portfolio in Excel, and when it stops working

Most private markets portfolios are tracked in a spreadsheet, and most of the software industry treats this as a problem to be corrected. It usually is not. A well-built workbook is cheap, transparent, and does exactly what its owner asks. Firms that replace one too early end up with a platform they use as a worse spreadsheet.

So: how to build one that survives, and how to recognise when it stops.

A structure that holds up

The single most common mistake is a one-row-per-fund layout, with columns for called, distributed and NAV that get overwritten each quarter. It is compact, it is easy to read, and it destroys your history, which means it destroys any IRR you compute from it.

Build it in three sheets instead.

Positions. One row per fund or vehicle, holding only the things that do not change: fund name, manager, vintage year, commitment, currency, entity that holds it, and a stable position ID.

Cashflows. One row per movement, ever. Position ID, date, type (call, distribution, fee), amount, and whether a distribution is recallable. This sheet only ever grows. It is the sheet that matters, and it is the one most workbooks do not have.

Valuations. One row per position per reporting date, holding the NAV from the statement and the date it is as of. Also append-only.

Everything else (called to date, unfunded, DPI, TVPI, IRR) is computed from those three with SUMIFS and XIRR. Nothing is typed twice, nothing is overwritten, and a wrong figure is corrected at the row it came from rather than in six places.

Two conventions worth fixing in writing, in a fourth sheet, because they are where reconciliations die: whether fees drawn outside commitment count toward paid-in, and how recallable distributions are added back to unfunded.

The five things that break it

Multi-entity structures. Adding an entity column looks like it solves this. It does not, because entities need different reporting currencies, different books, and different people allowed to see them. A workbook has one access level: whoever has the file.

Currency. As soon as you hold in more than one, you need a rate per cashflow date and a rate per valuation date, and a decision about which rate a consolidated total uses. This is where spreadsheet totals quietly become wrong rather than loudly breaking.

The document trail. The workbook says NAV was €4.2m. Which statement said so? At audit, or the first time two sources disagree, the answer is somebody opening PDFs. Nothing in the file connects a figure to its evidence.

Concurrent editing. The moment two people maintain it, you have a merge problem with no merge tool. Most workbooks solve this by having exactly one person who understands them, which is a different and larger risk.

Volume. Around thirty to forty positions, quarterly data entry stops fitting into the gaps of someone's week. The workbook does not fail. The updating does.

The honest threshold

Not a position count. Three questions:

Can you reconstruct any figure back to a document, in under a minute? If not, you do not have a record. You have a number that everyone currently believes.

Does more than one person need to write to it? One maintainer is a spreadsheet. Two is a database, whether or not you have installed one.

Is the quarterly update displacing work only you can do? A week of data entry per quarter is roughly a month a year. That is the actual price of the workbook, and it is paid in the time of whoever understands the portfolio best.

If all three answers are comfortable, keep the spreadsheet and spend the money elsewhere. That is a real answer and it is right more often than this industry admits.

If they are not, the thing to replace it with should do what the workbook did well (visible arithmetic, corrections at the source, no black box between a statement and a number), while adding the parts a file cannot have: figures that keep their document, metrics computed from your own cashflows, and separation between entities that survives more than one person.

If you want the arithmetic itself, IRR, TVPI and DPI, and why your three sources disagree covers what the formulas actually measure and why the same fund produces three different answers.