XIRR Calculator for SIP and Mutual Fund Returns

Every cash flow has a date and an amount. Money you invest is an outflow; redemptions, dividends paid out to you and the current value of what you still hold are inflows. The current value is treated as if you sold everything on its date, which is how a statement works out your return to date.

XIRR is the single annual rate at which all these flows balance: discount every flow back to the first date at that rate, and the total comes to zero. Time is measured in actual days divided by 365, the same convention spreadsheet XIRR functions use.

There is no formula that gives the rate directly, so it is found by trial. The calculator uses Newton-Raphson, which homes in on the rate quickly, and falls back to bisection, which is slower but certain once it has bracketed the answer. If the flows have no balancing rate, for example only investments and no value, it says so instead of showing a number.

Absolute return is simpler: total gain divided by total invested, ignoring when the money went in. For a SIP it understates how hard the money worked, because later instalments were invested for only a short time. XIRR corrects for that, which is why the two figures differ.

Worked example: a ₹5,000 monthly SIP for 36 months is ₹1,80,000 invested. If the holding is worth ₹2,20,000 at the end, the absolute return is 22.2% but the XIRR is about 13.4% a year. The XIRR is higher than 22.2% ÷ 3 because most of the money was invested for much less than three years.

For a single lump sum held for a known period, the CAGR Calculator gives the same answer as XIRR with less input. To project what a SIP will grow to, use the SIP Calculator; to check returns from NAVs on one purchase, the Mutual Fund Returns Calculator.