A late call after a large distribution turns the IRR equation into a quadratic, and Excel picks a root by its starting guess.
A fund has two IRRs when its cash flows change sign twice, typically a large distribution followed by a large late call, because the NPV equation then has two roots and both are mathematically correct. On an illustrative LP position that paid in 260 and got back 255, a TVPI of 0.98x, the IRR is both 5.59 per cent and 19.79 per cent, and Excel returns whichever one its starting guess lands nearer. Both are positive on a position that lost money; report the multiple, the NPV at a stated rate or a MIRR instead.
Worked in full in Private Markets Performance by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The usual reflex is to treat a strange IRR as a spreadsheet error. Here the spreadsheet is right twice. The problem is the measure: an internal rate of return assumes the cash flows look like an investment, money out first and money back later. Once money goes out again at the end, the series looks partly like a loan, and the IRR can no longer tell which.
| Year | Event | Cash flow |
|---|---|---|
| 0 | Capital called | -100 |
| 2 | Sale of the largest company, proceeds distributed as recallable | 255 |
| 4 | Distributions recalled for a warranty claim on that sale and a rescue that fails | -160 |
| Net, undiscounted | -5 |
Paid in is 260, distributed 255: a TVPI of 0.98x and a loss of 5. The pattern is not exotic. Recallable distributions, clawbacks, indemnity calls on an exit and late rescue financings all put a negative flow after positive ones, and recallable distributions are written into most limited partnership agreements.
Descartes' rule of signs bounds the number of positive roots of a polynomial by the number of sign changes in its coefficients. The IRR is a root of NPV = 0 written in powers of 1 ÷ (1 + r), and the coefficients are the cash flows, so the rule caps the number of IRRs above minus 100 per cent. Minus, plus, minus is two changes, so there can be up to two IRRs. A conventional fund, calls then distributions, has one change and at most one IRR.
NPV(r) = −100 + 255 ÷ (1 + r)2 − 160 ÷ (1 + r)4 = 0
With x = 1 ÷ (1 + r)2 this is the quadratic −160x2 + 255x − 100 = 0, which has two positive solutions for x. Each gives a rate: r = x−1/2 − 1.
The two rates are 5.59 per cent and 19.79 per cent. Plug either into the NPV and the result is zero.
| Discount rate | NPV |
|---|---|
| 0% | -5.00 |
| 3% | -1.80 |
| 8% | 1.02 |
| 12% | 1.60 |
| 16% | 1.14 |
| 25% | -2.34 |
| 30% | -5.13 |
The NPV starts negative, rises to a peak of 1.60 around 12 per cent and falls again. It crosses zero once on the way up and once on the way down. At low rates the late call of 160 weighs almost as much as the distribution, and the position loses. At high rates the initial 100 dominates everything after it, and it loses again. In between, the 255 received in year 2 is worth more than the 100 paid before it and the 160 repaid after it combined, each discounted at that rate.
=IRR(values, guess) iterates from the guess and stops at the first root it reaches. Run on the flows above, a guess of 0 or the default 10 per cent gives 5.59 per cent, and a guess of 15 or 25 per cent gives 19.79 per cent. Nothing in the output warns that a second answer exists. Two analysts, one reporting template each, can publish both numbers for the same fund.
Three measures do not suffer from the problem.
The MIRR here simply tracks the rate you choose: 4.96 per cent at 5, 10.19 per cent at 10 and 15.20 per cent at 15. That is the honest answer. The position has no rate of return of its own; it earns roughly what the LP could earn on the cash it was handed early.
| Year 4 recall | Net gain | TVPI | IRRs |
|---|---|---|---|
| 120 | 35 | 1.16x | -21.09% and 38.83% |
| 140 | 15 | 1.06x | -10.56% and 32.29% |
| 150 | 5 | 1.02x | -4.05% and 27.65% |
| 160 | -5 | 0.98x | 5.59% and 19.79% |
| 170 | -15 | 0.94x | none |
While the fund makes money, the second root is negative and nobody reports it; the positive one, 38.83 per cent on a 1.16x position, is still inflated by the early distribution. Once the net turns negative, both roots are positive and the choice between them becomes a reporting decision. Above a recall of 162.56 the two roots merge at 12.92 per cent and then vanish: Excel returns an error, which is at least honest.
Reporting the IRR Excel returned without checking the signs. Any series with a negative flow after a positive one should be tested at two or three guesses, and if they disagree, the IRR should not appear in the report. The same check applies to deal-level IRRs with a guarantee called after an exit, and to GP carry accounts that pay a clawback. A related trap is IRR timing: IRR vs XIRR covers the dating side.
Two sign changes, two possible IRRs: here 5.59 and 19.79 per cent on a position with a 0.98x TVPI. Count the sign changes before quoting a rate, and when there is more than one, report the multiple and an NPV or MIRR at a stated rate. Private Markets Performance works the multiples, the rate of return and all four public market equivalent methods on one fund's cash flows, and the PME calculator among the free workbooks for the book takes a cash flow series of your own.
Count the sign changes in the series. Calls then distributions is one change and gives at most one IRR. A distribution followed by a late call or clawback adds a second change and allows two. In the illustrative case, minus 100, plus 255, minus 160 has two changes and two IRRs, 5.59 and 19.79 per cent.
The one nearest its starting guess. On the illustrative flows, a guess of 0 or the default 10 per cent returns 5.59 per cent and a guess of 15 or 25 per cent returns 19.79 per cent, with no warning that another root exists. Test several guesses whenever a negative flow follows a positive one.
A measure with one answer: the multiple (TVPI 0.98x here), the NPV at a stated rate (1.02 at 8 per cent), or a MIRR with explicit finance and reinvestment rates (8.13 per cent at 8 per cent). Each settles what the IRR cannot: whether the position made money.
This article is one calculation from Private Markets Performance. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: private equity and private markets → · All 1501 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.