XIRR calculator

How the XIRR calculator works

XIRR is the single annual rate that makes the net present value of all your dated cashflows come out to zero. It is the number your mutual fund statement calls your return, because it weights every instalment and withdrawal by the exact date it happened. Give this tool a recurring plan, or type a custom list of dated transactions, and it reports that rate.

The two modes cover the two ways people actually invest. SIP or recurring mode takes an instalment amount, how often you invest, the first and last dates, and the value today. Custom mode lets you enter each buy and each redemption on its own date, so a lumpsum, a top-up, and a partial withdrawal can all sit in one calculation. A ₹10,000 monthly SIP that grows to ₹5,00,000 over three years works out to roughly 23.8 percent XIRR, even though the plain gain on it is 38.9 percent.

The formula XIRR actually solves

XIRR is the rate r that satisfies 0 = the sum of each cashflow Pᵢ divided by (1 + r) raised to the power (dᵢ minus d₁) over 365, where dᵢ is the date of that cashflow and d₁ is the earliest date. That day-count exponent is the whole point: money invested in January and money invested in December are discounted by different amounts, so the rate reflects how long each amount actually stayed invested.

There is no algebra that isolates r, so the rate has to be found by iteration. This calculator uses the Newton-Raphson method and falls back to bisection when Newton struggles to converge, which is the same approach Microsoft describes for its own XIRR function, solving until the result is accurate to about a millionth of a percent. Worth knowing, because some published XIRR pages quietly show the wrong equation: a few print a plain (final over initial) power formula, which is CAGR, not XIRR.

The exponent uses a 365-day year and whole days, so the time of day never enters it, and the rate that comes out is an effective annual one compounded daily.

XIRR or CAGR, which one applies

CAGR fits one lumpsum held from start to end; XIRR handles many buys and withdrawals on irregular dates. A SIP breaks CAGR, because each instalment has its own holding period and CAGR only knows one start date. For a single lumpsum the two rates are identical, so the moment there is more than one dated flow, XIRR is the honest measure.

The gap also shows why a headline gain says little on its own. The same 100 percent total gain is a very different annual rate depending on how long it took, which is exactly the effect XIRR captures by date.

Doubling in 3 years is a strong 26 percent a year; doubling in 10 is 7.2 percent, and XIRR draws that line automatically. To measure a single lumpsum without dates, the mutual fund return calculator reports the simpler CAGR.

A worked example with real dates

Picture a portfolio with a top-up and a partial withdrawal along the way. You invest ₹1,00,000 on 10 January 2022, add ₹50,000 on 10 July 2022, pull out ₹30,000 on 15 March 2023, and the holding is worth ₹1,60,000 on 10 January 2024.

Total invested is ₹1,50,000 and the money coming back is ₹1,90,000, so the absolute return is 26.7 percent. Run those four dates through the equation and the XIRR is about 14.9 percent a year. The absolute figure and the annual rate answer different questions, and only the second one is comparable across investments of different lengths.

How to calculate XIRR in Excel or Google Sheets

Both apps have the same function, so the steps are identical. Put every cashflow in one column and its date in the next, and enter money you invest as a negative number and money you redeem or the current value as a positive number. Then type =XIRR(values, dates) and format the cell as a percentage.

Using the example above, four rows of amounts (minus 100000, minus 50000, plus 30000, plus 160000) next to their dates, with =XIRR(B2:B5, A2:A5), returns the same 14.9 percent.

A third argument is available and rarely needed. The guess seeds the solver, and Excel uses 0.1, meaning ten percent, when you leave it out. Supply one as =XIRR(values, dates, 0.1) only if the flows are unusual enough that the default fails to converge.

Two precise details the Excel documentation carries and most calculator pages skip. XIRR discounts on a 365-day year, and it truncates each date to a whole day, so times of day never enter it. And what it returns is an effective annual rate compounded daily, which is why it can sit above an IRR computed on the same numbers: SpreadsheetPlanet runs one dataset both ways and gets 25.55 percent from XIRR against 11.52 percent from IRR, because IRR quietly assumes the flows were evenly spaced when they were not.

When Excel refuses

#NUM! has four causes, and the field usually names only the first two. Every amount carrying the same sign, so there is no root to find. The solver failing to converge inside its hundred attempts. The values and dates ranges being different lengths. And a date later in the list falling before the first date in it.

#VALUE! is a different failure: something in the range is not a number, or a date is text that Excel cannot parse. Google Sheets behaves the same way on both.

Which sign goes on which number

Investments are negative and money coming back is positive, which is the convention Excel documents and the one this page uses throughout.

The field does not agree with itself about this. Of seven pages checked on 15 September 2026 that state a rule, four give it correctly, two describe an investment as an "inflow" while still signing it negative, and one states the opposite outright, telling readers to enter investments as positive and withdrawals as negative, then works an example consistent with its own reversal.

That contradiction has survived because it does not change the answer. XIRR finds the rate where the discounted flows sum to zero, and multiplying every flow by minus one leaves that equation with the same root. Running the reversed example both ways here returns 152.5311 percent either way, identical to four decimal places.

So the rule that actually binds is narrower than the usual phrasing suggests. You need at least one sign change in the list, and you need to be consistent. Which direction you pick is a readability choice, not a mathematical one. Mixing the two within one list is the mistake that matters, and it produces a number that is quietly wrong rather than an error you can see.

What counts as a good XIRR

A concrete benchmark helps, and ICICI Direct puts it plainly: an XIRR above 12 percent is considered good for equity mutual funds, and above 7.5 percent is acceptable for debt funds. Treat those as reference points, not targets, because a fair XIRR really depends on the asset class, the period, and the risk you carried to earn it.

There is no single perfect number, and a high XIRR over a short, lucky window can flatter a fund that has not been tested through a downturn. Read the rate next to its time frame and its risk. Because mutual fund returns are market-linked and not guaranteed, this is a measurement of the past, not investment advice, so for decisions consult a SEBI-registered adviser. If you want to project a future value, the SIP calculator and the step-up SIP calculator do the forward maths.

Frequently asked questions

What is XIRR? XIRR, or extended internal rate of return, is the single annual rate that makes the net present value of a set of dated cashflows equal to zero. It is the return figure your mutual fund statement and apps like Groww or Zerodha Coin show, because it accounts for the exact date and size of every investment and withdrawal.

What is the difference between XIRR and CAGR? CAGR fits a single lumpsum held from start to end, so it needs just one buy and one sell. XIRR handles many transactions on irregular dates, which is how a SIP actually works, since every instalment has its own holding period. For one lumpsum the two are identical, but for a SIP only XIRR is meaningful.

How do I calculate XIRR in Excel or Google Sheets? Put each cashflow in one column and its date in the next, entering money you invest as a negative number and money you redeem or the current value as a positive number. Then use =XIRR(values, dates) in Excel, or the identical =XIRR(values, dates) in Google Sheets. Both return the annualised rate as a decimal, so format the cell as a percentage.

Why is my XIRR different from the absolute return? Absolute return divides total gain by total invested and ignores time. XIRR annualises and weights each rupee by how long it stayed invested. A ₹10,000 monthly SIP that grows to ₹5,00,000 over three years is about a 23.8 percent XIRR, even though its absolute return is 38.9 percent, because most instalments were invested for far less than three years. That 23.8 assumes the redemption falls on the same day as the thirty-sixth instalment. Push it out by one month and the same money reads 22.5 percent, which is a fair warning about how much XIRR depends on the exact dates you feed it.

What is a good XIRR for mutual funds? ICICI Direct notes that an XIRR above 12 percent is considered good for equity mutual funds, and above 7.5 percent is acceptable for debt funds. There is no single perfect figure, since a fair XIRR depends on the asset class, the period, and the risk you took, so compare it against a relevant benchmark for a fund like yours.

Can XIRR be negative? Yes. When the value you redeem is less than what you put in, XIRR is negative, showing the annual rate at which the money shrank. A ₹1,00,000 investment worth ₹80,000 two years later carries a negative XIRR of roughly minus 10.6 percent a year.

How does this calculator work out XIRR? It solves 0 equals the sum of each cashflow divided by (1 plus r) raised to the number of days since the first flow over 365. Because there is no direct formula for r, it uses the Newton-Raphson method and falls back to bisection if that does not converge, the same iterative approach Excel uses.

Why does Excel show a #NUM! error for XIRR? Excel returns #NUM! when the flows have no sign change, meaning you entered every amount as positive, or when it cannot converge within 100 tries. Two more causes are less often named: the values and dates ranges being different lengths, and a later date in the list falling before the first one. A separate #VALUE! error means something in the range is not a number, or a date is text Excel cannot parse. Enter at least one negative invested amount and one positive redeemed or current value, and add a guess such as =XIRR(values, dates, 0.1) if the numbers are unusual.

Does it matter which way round the signs go? Only that you are consistent. Of seven pages checked on 15 September 2026, four state the convention correctly, two call an investment an inflow while still signing it negative, and one says the opposite outright. It survives because flipping every sign leaves the equation with the same root: the reversed example returns 152.5311 percent either way. What you cannot do is mix conventions inside one list, which produces a wrong number rather than a visible error.

What day-count basis does XIRR use? A 365-day year, with each date truncated to a whole day, so times never enter it. The rate it returns is an effective annual one compounded daily, which is part of why it can differ from IRR on the same figures: SpreadsheetPlanet runs one dataset both ways and gets 25.55 percent from XIRR against 11.52 percent from IRR.

Is XIRR the same as IRR? IRR assumes cashflows are evenly spaced, one per period. XIRR is the extended version that uses actual calendar dates, so it is accurate when your investments and withdrawals happen on irregular days, which is almost always the case with SIPs and real portfolios.

Sources

Fetched 15 September 2026.

• AbleBits, Excel XIRR for non-periodic cash flows, for the day-count basis and the full error list
• SpreadsheetPlanet, the XIRR function, for the XIRR against IRR figures and the guess default
• Wall Street Prep, the XIRR function, on daily compounding and the IRR comparison
• Bajaj AMC XIRR calculator, which states the sign convention correctly
• Finnovate XIRR calculator, which offers both a plan and an irregular-flow mode
• Groww XIRR calculator, a plan-only tool that cannot take an irregular list
• MySIPonline, how to calculate XIRR for SIPs, which states the sign rule in reverse
• UrbanMoney XIRR calculator, for the per-transaction Excel guidance