The modified IRR on a fund's own call and distribution schedule, and why it can reverse a ranking that the IRR gets wrong.
Take the present value of every capital call at a finance rate, the future value of every distribution at a reinvestment rate to the end of the fund's life, and solve for the growth rate between the two: MIRR = (FV of distributions ÷ PV of calls)1/n − 1. On an illustrative ten-year fund that called 80 and returned 180, a 14.15 per cent IRR becomes an 11.35 per cent MIRR at a 5 per cent finance rate and an 8 per cent reinvestment rate. A quick-exit fund reporting a higher 15.50 per cent IRR falls to 10.68 per cent and ends up behind.
Worked in full in Alternative Investments by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The IRR of a fund answers one question: what single rate makes the calls and distributions net to zero? It does not say what the investor earned, because it implicitly assumes every distribution was reinvested at that same rate for the rest of the fund's life. A fund that hands back cash early at a high rate gets credit for compounding it has nothing to do with. MIRR replaces that assumption with two explicit rates the investor chooses, so two funds can be compared on what their cash flows are worth to the same investor.
Two illustrative funds, annual net cash flows to the limited partner, year 0 to year 10. Calls are negative, distributions positive, and any residual value is treated as distributed in year 10.
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Fund A | -25 | -25 | -20 | -10 | 0 | 20 | 35 | 40 | 40 | 30 | 15 |
| Fund B | -30 | -25 | -15 | 20 | 40 | 30 | 15 | 10 | 5 | 3 | 2 |
| Item | Fund A | Fund B |
|---|---|---|
| Paid-in | 80 | 70 |
| Distributed | 180 | 125 |
| Net gain | 100 | 55 |
| MOIC | 2.25x | 1.79x |
| IRR | 14.15% | 15.50% |
| Finance rate (earned on cash held for calls) | 5% | 5% |
| Reinvestment rate (earned on distributions) | 8% | 8% |
On the IRR, Fund B wins by 1.35 points. It also made 45 less money on 10 less capital. That combination is the situation MIRR exists for.
Step 1: the present value of the calls at the finance rate. Each call is discounted to year 0 at 5 per cent, the rate the investor earns on the cash it holds back to meet calls.
Fund A: 25.00 + 25 ÷ 1.05 + 20 ÷ 1.052 + 10 ÷ 1.053 = 25.00 + 23.81 + 18.14 + 8.64 = 75.59
Fund B: 30.00 + 23.81 + 13.61 = 67.41
Step 2: the future value of the distributions at the reinvestment rate. Each distribution is compounded to year 10 at 8 per cent, the return the investor expects on the portfolio the cash goes back into.
Fund A: 20 × 1.085 + 35 × 1.084 + 40 × 1.083 + 40 × 1.082 + 30 × 1.08 + 15
= 29.39 + 47.62 + 50.39 + 46.66 + 32.40 + 15.00 = 221.45
Fund B: 34.28 + 63.47 + 44.08 + 20.41 + 12.60 + 5.83 + 3.24 + 2.00 = 185.91
Step 3: the growth rate between the two. Ten years separate year 0 from year 10.
Fund A: (221.45 ÷ 75.59)1/10 − 1 = 2.92970.1 − 1 = 11.35%
Fund B: (185.91 ÷ 67.41)1/10 − 1 = 2.75770.1 − 1 = 10.68%
Excel, with the eleven net flows in B2:L2: =MIRR(B2:L2, 5%, 8%)
Excel's MIRR takes the finance rate first and the reinvestment rate second. It discounts negative values and compounds positive ones period by period, so it needs evenly spaced flows; for dated quarterly cash flows, build the two legs by hand with the actual year fractions.
Fund A's MIRR is 11.35 per cent, 2.80 points below its IRR. Fund B's is 10.68 per cent, 4.82 points below. The ranking reverses: Fund A leads by 0.67 points. Fund B's IRR was earned on capital that came back in years 3 and 4 and then, in the IRR's arithmetic, kept compounding at 15.50 per cent for six more years. No investor's reinvestment programme does that. Once the early cash is credited with a realistic 8 per cent, the fund that more than doubled the money is the better one, as the MOIC already suggested.
MIRR does not replace the IRR in a fund report. It is the comparison tool: run every fund in a shortlist through the same two rates and rank on that.
| Reinvestment rate | Fund A | Fund B | A minus B |
|---|---|---|---|
| 4% | 10.20% | 8.52% | 1.68 |
| 6% | 10.78% | 9.60% | 1.18 |
| 8% | 11.35% | 10.68% | 0.67 |
| 10% | 11.92% | 11.75% | 0.17 |
| 12% | 12.49% | 12.82% | -0.33 |
The two funds tie at a reinvestment rate of 10.67 per cent. Below it, Fund A is worth more to the investor; above it, Fund B's early cash is worth more. That crossover is the real question behind the comparison: can the investor redeploy distributions at more than 10.67 per cent? For most allocators, whose next-best use is a listed portfolio or the next fund's calls, the honest answer is no.
The finance rate matters much less. Moving it from 3 to 7 per cent shifts Fund A's MIRR only from 11.10 to 11.59 per cent and Fund B's from 10.51 to 10.83, because the calls are concentrated in the first four years and discounting them barely changes their sum.
Setting the reinvestment rate equal to the fund's own IRR. With both rates at the IRR, MIRR returns the IRR exactly, 14.15 and 15.50 per cent, and the exercise proves nothing. Even with a 5 per cent finance rate, reinvesting at the IRR gives 13.11 per cent for Fund A and 14.70 for Fund B, and the misleading ranking survives. The reinvestment rate has to belong to the investor, and it has to be the same for every fund on the shortlist.
The second mistake is mixing conventions across the shortlist. Use the net flow to the LP in each period, as Excel's function does, and use the same period length for every fund. A fund modelled on quarterly flows and another on annual flows will not produce comparable MIRRs even at identical rates, because n and the compounding steps differ.
The book makes the same point with a full call schedule and an endowment's own account, and the free workbook for this case sets the fund's IRR beside what the investor really earned. The real estate version of the formula, with a mid-hold capital call, is in how to calculate MIRR for a real estate investment, and the gap between a reported IRR and the investor's return is worked in how much of the IRR reaches the investor.
The rate the investor can actually earn on distributions, usually the expected return of the portfolio the cash goes back into, not the fund's own IRR. In the illustrative case an 8 per cent rate gives an 11.35 per cent MIRR; using the fund's own 14.15 per cent IRR as the reinvestment rate pushes it to 13.11 per cent and quietly restores most of the overstatement MIRR was meant to remove.
Yes, when the reinvestment rate is above the IRR. With both the finance and reinvestment rates set equal to the IRR, MIRR equals the IRR exactly: 14.15 per cent for the illustrative fund. Below that, which is the normal case for a top-quartile-looking fund, MIRR is lower, here by 2.80 points.
Because its IRR assumes the early distributions keep compounding at the IRR. The illustrative quick-exit fund reports 15.50 per cent but returns only 1.79x; at an 8 per cent reinvestment rate its MIRR is 10.68 per cent, a fall of 4.82 points against 2.80 for the slower 2.25x fund.
This article is one calculation from Alternative Investments. 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 1340 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.