The rule everyone learns, and where it fails
A cash flow that changes sign twice can have two positive internal rates of return. This workbook contains one: 6.10% and 163.9%, both of which zero the NPV, with Excel returning whichever its starting guess reaches.
The internal rate of return is the discount rate that makes NPV zero. Take a conventional cash flow: one outflow, then inflows. There is exactly one positive root, and IRR() finds it.
Change the sign twice and the guarantee disappears. A cash flow with two sign changes can have two positive roots, both of which make NPV zero, and Excel will return whichever one its iteration reaches.
Both cases appear in this workbook's fund model.
The fund cash flow: three sign changes, one root
The reported fund flow changes sign three times: contributions, distributions, then a late call. It has exactly one positive IRR, 6.03%.
Three independent checks confirm it:
- A located root at 0.0603197, with NPV at that point equal to 5 × 10⁻⁵ against a tolerance.
- The number of zero crossings in the NPV grid equals the number of roots found: one.
- Excel's
IRR()lands on the same root, to 10⁻¹¹.
The fund cash flows were read from a two-page summary, and some lines needed more than one OCR pass. The waterfall and the multiple identity are computed from the same cash flow; a missing distribution changes the input, not the arithmetic.
The Descartes bound is also checked: positive roots cannot exceed the number of sign changes. One root against three sign changes is consistent. It is also the usual shape of a real fund: several sign changes, one economically meaningful answer.
The second cash flow: two sign changes, two roots
The workbook carries a second series constructed to have two sign changes. It has two positive roots:
| Root | IRR |
|---|---|
| 1 | 6.10% |
| 2 | 163.9% |
Both are checked: the NPV at each located root is zero to within the tolerance, the zero crossings in the NPV grid match the count of roots found, and IRR() is shown to land on one of them, the first, without any indication that the other exists.
This is the failure mode. A model that reports "the IRR is 6.10%" is not wrong about that root; it is wrong about there being only one. The 163.9% root is just as real and just as meaningless.
Why the second root is meaningless
A 164% IRR from a cash flow that changes sign twice is an artifact of the reinvestment assumption. The IRR formula assumes every interim cash flow is reinvested at the IRR itself, which for the second root means assuming a 164% reinvestment rate for a decade. The project version of the same assumption is set out in the NPV and IRR comparison, which shows where the two metrics diverge.
The second root is not an economic scenario; it is what happens when a sign change creates a second intersection between the NPV curve and zero.
The first root, 6.10%, is the one with an economic interpretation. It is also the one Excel happens to return. Returning that root is luck of the starting guess rather than a property of the method.
The checks that make this visible
Four, and they are the pattern worth copying.
Count the sign changes, independently. Twice, by two different passes over the series. The counts must agree.
Bound the roots by Descartes. Positive roots ≤ sign changes. It is a weak bound and it catches gross errors.
Locate roots on the NPV grid. Step the discount rate, find the zero crossings, then refine each. The number of crossings is the number of roots.
Confirm NPV is zero at each located root. A root that does not zero the NPV is a solver artifact.
Those four turn "the IRR" from a function call into a statement about roots. That is the only way to know whether the number is unique.
What MIRR Does About Multiple Roots
Nothing, directly. MIRR removes the reinvestment assumption by stating a rate, so the second root's absurdity disappears. The MIRR of the series is a single number between the IRR and the reinvestment rate.
But MIRR does not tell you that the IRR was ambiguous. It replaces one number with another; the root-count question remains, and a reader who wants it has to run the four checks above.
The practical rule
For any cash flow with more than one sign change, report the sign-change count next to the IRR. If the count exceeds one, run an NPV grid and count the crossings.
For a fund with contributions, distributions and a late capital call, that is almost always the situation, and almost always the answer is a single economically meaningful root. The check is what distinguishes "almost always" from "in this case".
The MIRR and multiple article covers the return metrics that do not depend on a root at all, and the alternatives hub collects all 48. The ownership that produces these flows is in the cap tables, and the exit-value version of the same arithmetic is in the property DCF.
In Mica, each root is located independently in the sheet, which puts the root count next to the IRR that gets reported. Seeing both is what the cell-level audit layout is for: an ambiguous IRR is hard to miss when the count sits beside it.