Knowledge
Why the spreadsheet is where every fund starts, and the specific points at which it stops keeping up at portfolio scale.
Last updated 4 July 2026
In brief: Excel is a perfectly good place to build a single cap table, and almost every fund starts there. It begins to break down not because the maths is hard but because a spreadsheet stores a snapshot, not a history. At portfolio scale — many companies, interest-bearing instruments, and a multi-level holding hierarchy — the things that matter most (reconstructing ownership as at any past date, proving how a number was reached, and rolling positions up the structure consistently) are exactly the things a workbook does worst.
Excel is the natural home for a first cap table, and for good reason. It is already installed, it costs nothing incremental, and anyone in a deal team can read and edit it without training. For a single portfolio company shortly after completion, a workbook that lists the share classes, subscription amounts, and each investor's holding is genuinely sufficient. You can see the whole picture on one screen, sanity-check it against the subscription agreement, and send it to a colleague in seconds.
The flexibility that makes Excel so useful early on is the same flexibility that becomes a liability later. Any cell can hold any value; any formula can reference any other cell; and there is nothing to stop two versions of the truth existing in two tabs of the same file. For one company at one point in time, that freedom is harmless. The problems appear when a fund holds a dozen companies, each with preference shares and shareholder loans that accrue interest, and when someone asks what the position looked like eighteen months ago.
The failure points are not exotic. They are the ordinary, predictable consequences of using a snapshot tool to manage something that changes continuously across many companies.
One workbook per company. A fund with twelve portfolio companies typically ends up with twelve workbooks, each maintained slightly differently — different tab names, different layouts, different assumptions about where a given number lives. There is no single register of the fund's holdings, so any portfolio-wide question requires opening and reconciling a dozen files by hand.
Divergent interest formulas. Interest on prefs and shareholder loans is calculated per investor, per quarter, rounded to two decimal places, compounding annually on the anniversary, on a day-count basis of Act/365, Act/360, or 30/360 (see interest calculations). When each workbook implements that logic independently, the formulas drift. One file counts days on Act/365, another on Act/360; one compounds on the calendar year, another on the subscription anniversary. The result is that the same instrument type accrues differently in two companies, and nobody notices until an auditor or a buyer's adviser does.
No reliable as-at-date reconstruction. A cap table changes every time there is a new subscription, a transfer, a buyback, or a further drawdown. A spreadsheet stores only the latest state. To answer “what did we own on 31 December last year?” you either keep dated copies of every file (which multiplies the version problem) or you rebuild the past by hand and hope you remembered every movement. Neither is defensible.
No audit trail. When a figure changes in a workbook, the previous value is simply overwritten. There is no record of who changed it, when, or why. For a regulated fund that must show its workings, the absence of an attributable history is a genuine governance gap, not merely an inconvenience — a point we return to under preparing the cap table for audit.
Version control and broken references. Files travel by email as captable_v7_FINAL_updated.xlsx. Someone edits an out-of-date copy; two versions diverge; a row is inserted and a SUM silently stops including it; a linked workbook is renamed and every reference resolves to #REF!. These are not user errors so much as inherent properties of interlinked spreadsheets under multiple hands.
Key-person and handover risk. The person who built the model knows which tab feeds which, which cells are hard-coded, and which assumptions are buried three sheets deep. When they leave, that knowledge leaves with them. A successor inheriting a mature workbook often cannot fully trust it without rebuilding it — which is itself a source of new error.
Error-prone manual roll-ups. Private equity ownership runs through a chain: LP → Fund (Lux SCSp) → MasterCo (Lux SARL) → [CoInvest SPV →] LuxCo → TopCo → HoldCo → OpCo. Producing a look-through view — what each LP effectively owns at the operating level — means multiplying ownership percentages down every branch of that hierarchy. In Excel this is a lattice of cross-references that has to be rebuilt whenever the structure changes, and a single mis-keyed percentage propagates silently to every number below it.
The alternative is not “a fancier spreadsheet”. It is a different data model. Instead of storing the current state and recalculating in place, a transaction-based register records every event — each subscription, transfer, and redemption — as a dated entry, and derives the cap table at any date from that ledger. The contrast is stark on exactly the points that matter at portfolio scale.
| Capability | Excel workbook | Transaction-based register |
|---|---|---|
| Data model | Stores the current snapshot; history overwritten | Stores every dated event; state is derived from the ledger |
| As-at-date view | Only the latest state, unless dated copies are kept manually | Any past date reconstructed on demand from the same records |
| Interest accrual | Re-implemented per file; formulas drift between companies | One calculation engine applied consistently to every instrument |
| Audit trail | None — changes overwrite silently | Every change attributed to a user, with a timestamp |
| Hierarchy roll-up | Manual cross-references; brittle when structure changes | Look-through computed automatically down the entity graph |
| Multi-company | One file each; no single portfolio register | All companies in one register, rolled up to fund level |
| Handover | Undocumented logic tied to its author | Structure and rules explicit; no hidden cells |
| Formulas at export | Native, but only as good as the workbook | Exports to Excel with the formulas intact |
The point is not to abandon Excel. A transaction-based register still needs to hand a working spreadsheet to advisers, LPs, and auditors who live in Excel. The distinction is where the source of truth lives: in a dated ledger you can reconstruct and audit, rather than in the spreadsheet itself. Excel becomes an output format, not the system of record.
Three properties separate a system you can rely on for years from a workbook you have to rebuild every audit season. First, everything is an event: no holding exists implicitly — each one traces to a dated transaction, so the cap table on any date is simply the sum of what happened up to that date. Second, calculation is centralised: interest, ownership, and waterfall logic live in one engine rather than being copied into every file, so two companies cannot quietly disagree. Third, every change is attributable: the register knows who did what and when, which is the difference between explaining a number and merely asserting it.
This is the model CapTab is built on: a transaction-based register where the cap table at any date is derived from the underlying ledger, interest accrues by a single consistent method across every company, ownership rolls up the full holding hierarchy automatically, and each entry carries an audit trail — while still exporting to Excel, with the formulas, for everyone downstream who expects a spreadsheet. If the spreadsheet has started to creak, that is usually the signal it has outgrown its job.
A transaction-based register that reconstructs any past date, accrues interest consistently, rolls up the whole hierarchy — and still exports to Excel with the formulas.