Articles

IRR vs XIRR: which should a private equity fund use?

One investor's calls, distributions and NAV run through XIRR, quarterly IRR and annual IRR, with the Excel formulas and the date errors that move the answer.

Julian R. Sterling · from The Private Equity Fund Controller Playbook

Use XIRR for private equity cash flows. Capital calls and distributions arrive on irregular dates, and Excel's IRR assumes every value is exactly one period apart. On the illustrative fund below, XIRR on the actual dates gives 11.33 per cent; the same flows summed into calendar years and run through IRR give 12.43 per cent, a gap of 1.10 points that comes entirely from moving cash to the wrong dates.

Worked in full in The Private Equity Fund Controller Playbook by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →

Both functions solve the same equation: the discount rate at which the present value of the cash flows is zero. The difference is the time each flow is assumed to sit at. IRR counts periods, 0, 1, 2 and so on, whatever the dates were. XIRR measures the actual days from the first flow and discounts each value by (1 + r) raised to days ÷ 365. For a fund whose calls and distributions land whenever deals close, only the second describes what happened.

The case

An investor commits to a fund in 2019. It meets four capital calls totalling $60.0m, receives three distributions, and holds a remaining NAV of $28.0m at 31 December 2025, which is treated as a final inflow on the valuation date, as in any interim IRR.

Investor cash flows, $m. Illustrative.

Date	Flow	$m
15 March 2019	Call	−10.0
20 November 2019	Call	−15.0
10 June 2020	Call	−20.0
5 February 2021	Call	−15.0
30 September 2022	Distribution	12.0
15 December 2023	Distribution	25.0
1 July 2024	Distribution	30.0
31 December 2025	NAV	28.0
Net, TVPI 1.583x		35.0

The calculation, three ways

Method	Result	Gap to XIRR, points
XIRR on actual dates	11.33%	0.00
IRR on quarterly buckets, compounded	11.34%	0.01
IRR on quarterly buckets, times four	10.89%	−0.44
IRR on the eight flows as listed	11.82%	0.49
IRR on calendar-year buckets	12.43%	1.10

The annual buckets are the most common version in a fund model, and they overstate by 9.7 per cent of the true figure. The reason is visible in the dates. The model puts both 2019 calls at period 0 and the 2025 NAV at period 6, six years apart. In reality the first call and the NAV are 6.8 years apart. Compressing the holding period while keeping the same profit raises the rate.

Quarterly buckets, compounded properly, are almost exact, because no flow moves by more than a quarter. Multiplying the quarterly rate by four instead of compounding it loses 0.44 points; that conversion is covered in why you should not multiply a quarterly IRR by four.

The listed-flows result is right by accident. Running IRR down the eight dated rows treats each as one year apart. Here the seven intervals average 0.97 of a year, so the answer lands near XIRR. Feed the same function 28 quarterly rows and it returns 2.72 per cent, because it now believes the fund lasted 27 years.

What if the dates are moved?

XIRR is sensitive to the dates you give it, which is the point, and also the risk. Each line below changes one date and nothing else.

What-if: one date changed, XIRR in per cent.

Change	XIRR	Change, points
Base case	11.33	0.00
The $30.0m distribution booked at 31 December 2024, not 1 July	10.91	−0.42
The first call dated 1 January 2019, not 15 March	11.23	−0.10
NAV dated 30 September 2025, not 31 December	11.51	0.18

Six months of delay on one distribution costs 0.42 points. That is why the dates in a performance calculation must be the cash value dates from the bank statement, not the notice date, the booking date or the quarter end the administrator rolled them into. The NAV line shows the opposite trap: dating the valuation earlier, with the same value, flatters the rate, so the terminal value must carry the date it was struck.

The common mistakes

• Annual buckets in a fund model. Fine for a forecast, wrong for reporting. Here 12.43 per cent against 11.33.
• Notice dates instead of value dates. A call notice issued ten business days before the cash moves overstates the time the money was at work, and so understates the rate.
• A NAV without its date. The terminal value is a flow like any other and is discounted by its own date.
• Reading XIRR as a nominal rate. It is an effective annual rate on a 365-day year. It needs no further compounding, and it should not be compared with a quarterly figure multiplied by four.

Takeaway

For any reported fund or investor IRR, use XIRR on cash value dates, with the NAV as a dated final flow. Keep IRR for evenly spaced model periods, and if the model is quarterly, compound the result. When two reports of the same fund disagree by a point, check the dates before the cash flows. The full set of performance measures for a worked fund, with the IRR on dates, is in the free workbook for this case; for how the rate relates to the multiple, see how to convert MOIC to IRR.

Questions readers ask

Why is my XIRR different from IRR in Excel?

IRR assumes each value is one period apart; XIRR discounts each value by its actual days from the first date over 365. With irregular fund flows they diverge. In the worked case the same cash flows give 11.33 per cent by XIRR and 12.43 per cent by IRR on calendar-year totals, because the annual layout squeezes 6.8 years of holding into six periods.

Does XIRR give an annual rate?

Yes. XIRR returns an effective annual rate on a 365-day year, so it needs no conversion. A quarterly IRR does: compound it as (1 + q) to the fourth power minus one. In the worked case quarterly buckets give 11.34 per cent compounded, against 10.89 per cent when the quarterly rate is simply multiplied by four.

Which date should a capital call carry in an IRR calculation?

The date the cash actually moved, from the bank statement, not the notice date or the quarter end. Dates move the result more than people expect: in the worked case booking one $30.0m distribution six months late lowers XIRR from 11.33 to 10.91 per cent, a cost of 0.42 points from a booking convention alone.

Also on this site

• Should you multiply a quarterly IRR by four to annualise it?No: compound it, (1 + q)^4 minus 1. A 3.091 per cent quarterly IRR is 12.95 per cent a year, not 12.36. Times four understates at every positive rate.
• How do you convert MOIC to IRR?IRR = MOIC^(1/years) minus 1 for a single exit: 2.0x is 14.9 per cent over five years and 26.0 over three. Interim distributions break the formula.
• How do you calculate TVPI, DPI and RVPI for a private fund?Divide by paid-in capital: 791 distributed and 143 of NAV on 564 called give DPI 1.4025x, RVPI 0.2535x and TVPI 1.6560x. Worked case.
• Can you average fund IRRs to get a portfolio return?No. Three funds at 11.52, 10.12 and 19.51 per cent average to 13.72 per cent. The pooled return is 11.78 per cent, 194 basis points lower.
• How is equalisation interest calculated for a late-closing investor?Call by call, from each call date. A $60m commitment at 8 per cent owes $0.90m of equalisation interest, not the $1.20m the first-close shortcut gives.
• How the GP catch-up is actually solvedThe catch-up is an equation, not a number: C / (pref + C) = carry rate. Worked on a $160m distribution, at three carry rates.

Reading guide: private equity and private markets → · All 453 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.