Seven groups, one cash flow
Forty-eight checks follow the cash: cap tables, fund flows, four return measures, IRR roots, MIRR, the waterfall, and one aggregate residual. Each layer feeds the next, and the checks sit at the junctions.
The fund workbook builds a ten-year private equity return in seven layers, and each layer has its own checks.
| Layer | Checks | What it establishes |
|---|---|---|
| Cap tables | 6 | Every round sums to 100% for fund and co-investors |
| Fund cash flow | 9 | Calls, distributions and net flow rebuild |
| Return multiples | 12 | MOIC = DPI + RVPI in four scenarios, two ways |
| IRR roots | 10 | Sign changes, Descartes bound, NPV zero at each root |
| MIRR | 4 | Sits between the IRR and the reinvestment rate |
| Waterfall | 6 | Tiers sum to distributable; carry is the residual |
| Aggregate | 1 | The multiple identity holds across everything |
The interesting structural feature is that the layers feed each other in one direction: ownership produces proceeds, proceeds produce the cash flow, the cash flow produces the returns, and the returns drive the waterfall. The checks are placed at each junction rather than only at the end. That one-way chain has a corporate analogue in the capital budgeting integration, where assumptions feed cash flows and then metrics.
The three junctions worth watching
Ownership into proceeds. A cap table that sums to 100% can still produce the wrong proceeds if the fund's share is taken from the wrong round. The check on the diluted tranche is what ties the two together.
Proceeds into the cash flow. Net cash flow has to rebuild from gross proceeds less calls, in every scenario. Three checks, one per scenario plus the reported case. A model that imports a net figure and reports returns from it has no cash flow.
Returns into the waterfall. The tiers have to sum to the distributable amount, and the carry has to be the carry rate applied to the residual above the hurdle. Six checks, and the last one is the one that catches a carry computed on the whole distribution.
The single most useful check
The IRR root count.
A fund cash flow with three sign changes and one positive root is fine. A cash flow with two sign changes and two positive roots is a reported IRR waiting to be wrong. Excel's IRR() does not say which case it is in.
The workbook computes the roots independently, checks the NPV is zero at each, and compares what IRR() returned. That is ten of the forty-eight checks, and it is the one place in the series where a function's answer is treated as a claim about a function rather than a result.
What the 48 do not establish
That the fund is real. Every input is constructed: five rounds, a distribution profile, scenario weights. Nothing is calibrated to a real fund.
That the return is good. A 6.03% IRR on a 1.46× multiple is reported; whether that compensates for ten years of illiquidity is a judgement the workbook does not make.
That the waterfall matches the LPA. The tiers are modeled in simplified form. A real partnership agreement has a catch-up, fee offsets and clawback provisions that change the GP's share.
That property is covered. Two articles in this topic, the real estate DCF and the cap rate piece, describe methods the workbook does not contain. They are included because a private markets model is incomplete without them, and they say so.
How to read it
Start with the cap tables. If ownership is wrong, every return downstream is wrong and the checks will still pass, because they test the arithmetic that follows.
Then the cash flow rebuild, then the multiple identity, then the root count, then the waterfall. That is the same order the layers are built in, and it is the reason 48 checks is enough rather than excessive: each group tests a junction, and there are seven junctions.
The IRR article is the root-count case, the multiple article is the identity, the dilution piece is where ownership comes from, and the complete check sheet sits in the alternatives hub. The property side the fund does not model is in the property DCF and cap rate and leverage.
Ownership, cash flow and carry are linked rather than typed in the fund model Mica builds end to end. That end-to-end workbook is what reporting automation needs to keep the layers in step.