A SIP calculator’s headline return assumes deposits arrive on schedule. XIRR makes no such assumption: it weights every deposit by the time it actually spent invested. This guide works two dated examples, contrasts XIRR with CAGR, and names the cases where XIRR misleads.

What XIRR is: the return of dated cash flows

Most return figures assume money goes in on a schedule. Real money does not. Instalments arrive late, extra deposits land in odd months, a withdrawal interrupts the plan, and the final value is measured on a date that is not a round anniversary. XIRR — the extended internal rate of return — is the return measure built for that reality: it takes every cash flow with its actual calendar date, and finds the single annualised rate that makes the present value of the whole dated list equal to zero. In other words, it answers the question “at what constant annual rate would this exact pattern of deposits and withdrawals, on these exact dates, have grown to this exact ending value”. The answer is annualised, so it can be compared across plans of different lengths, and it is date-exact, so an instalment paid on the 1st and an instalment paid on the 28th are not treated as the same thing.

That date-exactness is the whole point of XIRR, and the reason it exists alongside the simpler measures. A SIP calculator typically reports a simple annualised return computed from the total invested, the ending value, and the number of years — a clean figure that assumes the deposits were evenly spread. XIRR makes no such assumption: it weights each deposit by how long it was actually invested, so a deposit made in January counts for nearly a full year of growth while a deposit made in December counts for almost none. When deposits are perfectly regular, the two figures are close. When they are not — a missed month, a doubled month, a mid-year lump sum — XIRR is the honest figure and the simple return is an approximation that can be off by points, not by rounding.

Worked example: two deposits, one ending value

The smallest example that shows the machinery needs only three dated flows. Invest $1,000 on 1 January 2024, invest a further $1,000 on 1 July 2024, and measure the value at $2,300 on 1 January 2025. The XIRR is the rate r that satisfies the equation: 1,000 grown for 366 days, plus 1,000 grown for 184 days, equals 2,300, where each growth factor is (1 + r) raised to the number of days divided by 365. Solving gives r equal to 20.21% a year. The figure is high because the money was invested for less than a full year on average — the second $1,000 worked for only half the year — yet the total grew by $300 on $2,000 contributed. A simple return computed as $300 on $2,000 over one year would report 15%, understating the achievement, because it credits the second deposit with a full year it never had. XIRR corrects exactly that error, and the correction is larger whenever the deposits are bunched late in the period.

The day-count convention matters and should be stated: XIRR uses actual days divided by 365, with 2024 being a leap year, so the first deposit’s exponent is 366/365 and the second’s is 184/365. Different tools sometimes use 365.25 or actual/actual conventions, and the resulting XIRR can differ in the second decimal place between implementations. That is not an error in either tool; it is a convention difference, and it is why XIRR figures should be compared only within one tool, or with the convention stated. The calculator on the hub page does not print an XIRR — it reports the simple annualised return on the regular schedule — and this guide’s figures are computed on the actual/365 convention, matching the spreadsheet function described below.

Worked example: a monthly SIP

Now the pattern SIP investors actually live with. Invest $500 on the 1st of each month through 2024 — twelve deposits, $6,000 in total — and measure the value at $6,400 on 1 January 2025. The XIRR solves the same equation with thirteen dated flows: twelve outflows of $500 on the first of each month, and one inflow of $6,400 at the end. The answer is 12.47% a year. The simple annualised return on the same figures — treating $6,000 as if it had all been invested for the full year — would be lower, because in reality the December deposit had only a month to work, and the average dollar was invested for about six and a half months, not twelve. The gap between the two figures is the information: XIRR credits the early deposits with the time they actually had, and the plan’s true annualised performance is 12.47%, not the figure a single lump-sum calculation would suggest.

The third row of the table is the control case: a single deposit has no timing pattern to weight, so its XIRR equals its compound annual growth rate exactly — $10,000 growing to $13,310 over three years is 10.00% by either measure. That identity is a useful check on any XIRR implementation: feed it a single dated flow and a single ending value, and it must return the CAGR. If it does not, the implementation, the dates, or the day-count convention is wrong, and the more complex examples built on it cannot be trusted either.

XIRR versus CAGR: when each one lies

CAGR, the compound annual growth rate, answers a narrower question: at what constant rate would a single lump sum have grown from the starting value to the ending value over the stated years? It takes two values and a duration, and nothing else. It is the right measure for a single investment held untouched, and it is the wrong measure for any pattern with more than one cash flow, because it has nowhere to put the intermediate deposits: it treats them as if they had all been invested on day one, which overstates the time they worked and understates the return. XIRR answers the wider question — the rate for any dated pattern — and it is the right measure whenever deposits or withdrawals occur during the period. The two agree on single-flow patterns and diverge everywhere else, and the divergence grows with the irregularity of the pattern.

Each measure also has a characteristic failure. CAGR fails silently on irregular patterns: it returns a plausible-looking number that quietly assumes the wrong timing, and nothing in the output warns the reader. XIRR fails loudly, which is better: it needs at least one positive and one negative flow, it needs a guess to start its iteration, and on patterns with many sign changes it can return multiple mathematically valid rates, of which the tool reports one. A pattern with a single deposit and a single value cannot mislead XIRR, and a pattern with a withdrawal in the middle can — because a withdrawal is a negative investment that the rate must also explain, and the resulting XIRR describes the whole pattern, deposits and withdrawals together, not the investment skill behind it. Neither measure includes fees or tax unless the cash flows include them: an XIRR computed on gross deposits and gross values is a gross return, and the net return — the one the investor actually keeps — requires the fees and taxes to appear as flows in the list.

Calculating XIRR in Excel, step by step

The spreadsheet calculation is deliberately mechanical. Put the dates in one column, in chronological order, and the amounts in the next, with investments as negative numbers and the final value as a positive number. Then enter =XIRR(amounts, dates) in a third cell, with an optional third argument, a guess, if the function struggles to converge — 10% is the usual starting guess, and it matters only as a starting point for the iteration, not as part of the answer. Excel solves by iteration: it guesses a rate, discounts every flow by its actual day count, sums the present values, and adjusts the guess until the sum is zero to within its tolerance. The function returns the annualised rate as a decimal, formatted as a percentage. For the monthly SIP example above, the thirteen rows produce 12.47%, and changing any date by a month moves the result by a visible amount — which is the date sensitivity the measure exists to capture.

Three spreadsheet habits prevent most XIRR errors. First, dates must be real dates, not text that looks like dates: text dates are ignored or misread, and the day counts silently go wrong. Second, at least one flow must be positive and one negative, or the function has no rate to solve for and returns an error — a list of deposits with no ending value, or a value with no deposits, cannot produce an XIRR. Third, the final value must be dated on the measurement date, not on the last deposit date: valuing the plan on the last deposit date credits the final deposit with no time at all and distorts the result. With those three habits, the spreadsheet and the definition agree, and the figure can be compared across plans, across years, and across tools that share the day-count convention.

The limits: what XIRR cannot tell you

XIRR is a descriptive measure, not a promise, and its limits should be stated wherever the figure is quoted. It says nothing about risk: two plans with the same XIRR can have wildly different volatility, and the smoother one is the better plan at the same return. It says nothing about the future: it annualises the past pattern, and a high past XIRR on a short, lucky pattern — the two-deposit example’s 20.21% — does not predict the next period. It is distorted by withdrawals: a plan from which money was withdrawn shows an XIRR that blends the investment return with the withdrawal pattern, and comparing it with a plan that was left alone compares unlike with unlike. And it has no opinion on what counts as “good”: the rows asking what a good XIRR is have no numerical answer, because a good return depends on the asset, the risk taken, the inflation over the period, and the benchmark the plan should be judged against. The honest use of XIRR is comparative and historical — this pattern, over this period, annualised to this rate, against the same measure for the alternative — and any use beyond that is asking the figure a question it cannot answer.

For SIP planning specifically, the measure to pair with XIRR is the calculator’s own projection: the SIP Calculator shows what a regular monthly amount becomes at an assumed return, in future dollars and in inflation-adjusted dollars, and XIRR shows what the pattern actually achieved once the dates are real. The projection looks forward with an assumption; XIRR looks backward with dates. Keeping the two directions separate — never quoting a projected XIRR as if it were a measured one — is the discipline that keeps the whole subject honest.

Frequently asked questions

What is XIRR in a SIP calculator?

XIRR is the annualised return of the dated cash flows: every instalment counted on the day it was actually paid, and the ending value counted on the day it was measured. It answers the question of what constant annual rate would turn that exact pattern of dated deposits into that exact ending value. On the worked monthly example — 500 dollars on the 1st of each month in 2024, worth 6,400 dollars on 1 January 2025 — the XIRR is 12.47 percent a year. It differs from the simple annualised return because it weights each deposit by the time it was actually invested.

How do I calculate XIRR for a SIP?

List every cash flow with its date, then solve for the rate that makes the present value of the whole list zero, discounting each flow by its actual day count divided by 365. With two deposits of 1,000 dollars on 1 January and 1 July 2024 and a value of 2,300 dollars on 1 January 2025, the XIRR is 20.21 percent a year. The calculation is iterative — guess a rate, discount, adjust — which is why it is done by spreadsheet or calculator rather than by hand, and why at least one positive and one negative flow are required.

How do I calculate XIRR in Excel?

Put the dates in one column and the amounts in the next, with investments as negative numbers and the final value as positive, then enter XIRR with the amounts and the dates as its arguments, with an optional guess such as 10 percent if it struggles to converge. The dates must be real dates, not text, and the final value must be dated on the measurement date. On the thirteen-row monthly SIP example the function returns 12.47 percent. The companion guide on building a SIP calculator in Excel shows the full schedule behind the function.

What is the difference between XIRR and CAGR?

CAGR is the constant rate that grows a single lump sum from start to end over a stated period — two values and a duration. XIRR is the constant rate for any dated pattern of deposits and withdrawals. They agree exactly on single-flow patterns: 10,000 dollars to 13,310 dollars over three years is 10 percent by either measure. They diverge whenever deposits arrive during the period, because CAGR treats every deposit as if it had been invested on day one, while XIRR weights each by its actual date.

What is a good XIRR for a mutual fund portfolio?

There is no number that answers that question honestly. A good XIRR depends on the assets held, the risk taken, the inflation over the period, and the benchmark the portfolio should be judged against — and a high XIRR over a short, lucky period says nothing about the next one. Compare the XIRR with the return of a comparable benchmark over the same dated pattern, and with inflation over the same period, rather than with any fixed threshold. Any source that states a good XIRR as a single percentage is inventing a standard that does not exist.

Why does XIRR differ from the return shown on a SIP calculator?

Because the two figures assume different timing. A SIP calculator typically reports a simple annualised return that treats the deposits as evenly spread over the period. XIRR uses the actual calendar dates, so a deposit paid early counts for more time than one paid late, and a missed or doubled month changes the result. On regular monthly patterns the figures are close; on irregular patterns they can differ by points. Neither is wrong — they answer different questions — but only XIRR describes the dated pattern that actually happened.