When a spreadsheet stops working for fund and angel investments

A spreadsheet is a perfectly good record for three or four fund commitments and a handful of angel holdings, all in one currency, kept by one person who enters every notice the week it arrives. It stops working at a point that can be named: when nobody can state the unfunded commitment across all funds within five minutes, when the IRR cell shows an error or a number nobody believes, and when a figure can no longer be traced to the document it came from. Size is not the trigger. The trigger is the first decision made on a number the sheet got wrong.

We write this as people who kept such a spreadsheet for a long time. Digital Pioneers has invested in venture funds and start-ups for more than 25 years, and the record was a workbook. This month we began building Valued to replace it. What follows is the honest version of when that is necessary and when it is not.

How long is a spreadsheet enough?

Longer than software vendors say. One sheet with one row per cash flow (date, fund, type, amount) and a second sheet with one row per fund (commitment, currency, latest NAV and its date) answers every question an LP has: paid in, distributed, unfunded, TVPI, DPI, IRR. It costs nothing, it is yours, and the tax adviser can open it.

It holds as long as four conditions do. The number of notices per year is small enough that each one is entered at once; a dozen is manageable, sixty is not. Everything is in euros. One person maintains it and that person is available. And the notices are simple: a call is a call and a distribution is a distribution.

The fourth condition fails first, usually without anyone noticing.

Sign one: nobody can state the unfunded commitment

The unfunded commitment is what the funds can still call. The spreadsheet formula is commitment minus the sum of calls, and it is wrong for most funds after a few years.

Say you committed EUR 250,000 to a fund. It has called EUR 180,000, and it has distributed EUR 40,000. The formula says EUR 70,000 is unfunded. Now read the notices. One call included EUR 5,000 of equalisation interest paid to earlier investors, which the fund agreement does not count against the commitment, so only EUR 175,000 of the commitment has been drawn. And EUR 15,000 of the distribution was marked recallable: the fund may call it again. The amount the fund can still ask for is 250,000 minus 175,000 plus 15,000, which is EUR 90,000. The sheet is EUR 20,000 short on one fund.

Nothing in the workbook signals this. The information is in a sentence on page two of a PDF, and the sheet has one column called "amount". Across eight funds, errors of this kind do not cancel out; both make the sheet show less than can be called. If the bank or your own liquidity plan relies on that total, this is the number to distrust first.

Sign two: the IRR is broken or unbelievable

Excel computes an IRR for irregular dates with XIRR. The function needs at least one positive and one negative value, it searches from a starting guess of 10 per cent, and it returns #NUM! if it has not found a result after 100 iterations. A date that Excel reads as text produces #VALUE!. All of that is documented, and all of it happens in practice: a column of dates pasted from a PDF in a different format, a young fund deep in its J-curve whose true IRR is far from 10 per cent, a distribution entered with the wrong sign.

The quieter failure is an IRR that computes and is wrong. The usual cause is the final value. An IRR for a fund that still holds companies needs the current NAV as a last, positive cash flow on the NAV's date. If the sheet uses today's date with a NAV from two quarters ago, or leaves the NAV out, the result is a number, and it means nothing. A portfolio IRR built by averaging the fund IRRs is also a number that means nothing; it has to be computed from all cash flows together.

Sign three: a second currency

A commitment in US dollars is a commitment in dollars. Each call and each distribution has a euro value on the day it was paid, and the unfunded rest has a euro value that changes daily. A spreadsheet typically has one cell with "the" exchange rate. Change it, and the euro amounts of calls paid three years ago change with it, which is wrong: that money left the account at the rate of its value date.

Doing it properly means one rate per cash flow. The European Central Bank publishes euro reference rates around 16:00 CET on every working day, and looking up the rate for each value date is possible by hand. People do it for the first five entries.

Sign four: figures without documents, and one reader

Ask of any cell: which document says so? In the first year the answer is in your head. In the sixth year, with a few hundred rows, a corrected capital account statement that replaced an earlier one and a fund that changed administrator, it is not. A figure that cannot be traced cannot be checked, and a record that cannot be checked gets rebuilt from the PDFs once a year, usually for the tax adviser, usually in a hurry.

The same goes for the person. If only one member of the family can read the workbook, the record is that person's memory with a file attached.

What we would do

If the four conditions hold, stay with the spreadsheet and improve it in three ways. Give recallable distributions and amounts outside the commitment their own columns, so that unfunded is computed from what the notices say. Store the file name of the source document in every row. And keep amounts in their original currency with the rate of the value date next to them.

If two of the signs above are familiar, the spreadsheet is costing more than it saves, and the cost arrives as a wrong number at the moment a call has to be paid. That was our situation. A workbook like ours cannot say what is unfunded until someone has read the PDFs again, and the first thing we want from software is that it reads the notice a fund sends, shows what it read next to the document, and books it only after someone has approved it. That is what we started on this month, with fund commitments first. The spreadsheet stays open beside it until the two agree.