XIRR Calculator
💡 Key Tips
- Use XIRR whenever money went in or came out on more than one date — SIPs, top-ups, partial redemptions, dividends, rent from property.
- For a single lump sum with a single exit, XIRR and CAGR give the same number, so CAGR is the simpler tool there.
- Signs matter: outflows negative, inflows positive. If every row carries the same sign, no rate can solve the equation.
- Include your current portfolio value as a positive final row if you have not actually sold anything yet.
- XIRR is annualised, so a 6-month result gets scaled up to a yearly figure — short windows can look dramatic in both directions.
⚠️ Points to Note
- XIRR is a measurement of what already happened. It carries no promise about the future.
- The figures here are pre-tax and ignore exit loads, brokerage, STT, stamp duty and GST. Your take-home return will be lower.
- The calculation assumes every interim cash flow is reinvested at the same XIRR — in reality that rarely holds.
- With several sign changes in the cash flow series, more than one mathematical solution can exist; treat unusual results with care.
- An unusually high XIRR over a short period is often a timing artefact, not evidence of skill.
📋 Disclaimer
This calculator is provided for educational and informational purposes only. It does not constitute financial, investment, or trading advice, nor a recommendation to buy, sell or hold any security. EquityTimer is not a SEBI-registered investment adviser or research analyst. All outputs are illustrative estimates based on the inputs you supply and do not account for taxes, exit loads, transaction costs or inflation. Investments in securities markets carry substantial risk of loss and are not suitable for every investor. Past performance is not indicative of future results. Always consult a qualified, registered financial adviser and conduct your own due diligence before making any investment decision.
📐 Formulas Explained
1. Net Present Value of a Cash Flow Series
2. XIRR — the rate that makes NPV zero
3. Growing Each Instalment Forward (the schedule table)
4. Absolute Return vs XIRR
5. CAGR — the single-cash-flow special case
An XIRR calculator works out the true annualised return on money that went in and out on different dates — a SIP, a top-up, a partial redemption, or a portfolio you have simply been adding to for years. This guide explains what XIRR measures, how it differs from CAGR and absolute return, how to use the free EquityTimer XIRR calculator above, and how to reproduce the same figure in Excel or Google Sheets. Written for education and research only, with no buy, sell or hold advice.
What XIRR actually measures
XIRR stands for Extended Internal Rate of Return. It answers a single question: given every rupee that entered and left an investment, and the exact date each of those movements happened, what one annual rate of growth explains the outcome?
The word that carries all the weight is dates. Most return measures assume your money arrived in one lump on day one. Real investing almost never looks like that. You start a SIP, you skip a month when cash is tight, you add a bonus in March, you redeem a slice to pay a school fee. Each of those rupees was invested for a different length of time, so each deserves a different amount of credit for the final value. XIRR is the method that does that arithmetic properly.
The idea behind the maths
Imagine testing a rate — say 10% a year. You take every cash flow and pull it back to a common date at that rate. If the investments and the final value cancel out exactly, 10% was the right answer. If they do not cancel, you try a different rate and repeat. The rate at which everything balances to zero is the XIRR.
In textbook language, XIRR is the discount rate at which the net present value of the whole cash flow series equals zero. There is no formula you can rearrange to get the answer directly — it has to be found by trial and refinement. That is precisely why a calculator is useful, and why Excel needs an iterative routine to do it too.
Why the “internal” in the name
The rate is called internal because it is derived entirely from the investment’s own cash flows. Nothing external — no benchmark, no inflation figure, no interest rate — feeds into it. It is a self-contained description of what your particular pattern of investing produced.
XIRR describes what has already happened. It is a measurement, not a forecast, and it says nothing about what any investment will do next.
How to use the EquityTimer XIRR calculator
The calculator at the top of this page works in two ways, depending on how tidy your transaction history is. Both are free and need no login.
Option 1 · A regular schedule
Use this when you invested the same amount at a steady interval — the classic SIP. Four inputs:
- Investment frequency — 14 days, monthly, quarterly, half-yearly or yearly.
- Recurring investment amount — what you put in each time.
- Start date and maturity date — the first instalment, and the day you are valuing the holding. For an investment you still own, the maturity date is simply today.
- Total maturity or current value — what the holding is worth on that valuation date.
The calculator builds the full instalment schedule for you, works out how many instalments fall inside the period, and returns the XIRR along with total invested, estimated returns and absolute return.
Option 2 · Irregular cash flows
Use this when the real history is messy — different amounts, uneven gaps, a redemption in the middle. Enter one row per transaction with its own date and amount. The sign convention is the important part, and it is the same one Excel uses:
- Money leaving your pocket into the investment is entered as a negative amount.
- Money coming back to you — a redemption, a dividend received in cash — is a positive amount.
- If you still hold the investment, put today’s value as a positive amount on the last row, dated today. This is the step most people forget, and without it there is nothing for the calculator to solve against.
Reading the cash flow schedule
Below the result, the calculator lists every instalment with the number of days it stayed invested and what it grew into by the valuation date at the calculated rate. That last column is worth a look: add it up across all instalments and you get back your maturity value exactly. That reconciliation is what “net present value equals zero” means in practice, shown as rupees rather than algebra.
Nothing you enter is stored or sent anywhere — the whole calculation runs in your own browser.
XIRR vs CAGR vs absolute return
These three are often used loosely as if they were interchangeable. They are not, and the gap between them can be enormous for the very same investment.
| Absolute return | CAGR | XIRR | |
|---|---|---|---|
| What it answers | How much did my money grow in total? | What steady yearly rate turns the start value into the end value? | What yearly rate explains cash flows arriving on many different dates? |
| Uses dates? | No | Only the total number of years | Yes — the exact date of every flow |
| Handles multiple investments? | Only as a single total | No | Yes |
| Handles withdrawals? | No | No | Yes |
| Annualised? | No | Yes | Yes |
| Typically used for | Holdings under a year | A single lump sum held for several years | SIPs, top-ups, partial redemptions, whole portfolios |
The convention widely followed in Indian mutual fund reporting is straightforward: for a holding period of less than a year, absolute return is the sensible measure, because annualising a few months of movement exaggerates it. Beyond a year, a single lump sum is described by CAGR. The moment there is more than one dated cash flow, XIRR becomes the appropriate measure — which is why fund platforms show XIRR against SIP holdings rather than CAGR.
Where CAGR quietly goes wrong
A common mistake is to take the total invested in a SIP, treat it as though it all arrived on the first day, and run a CAGR on it. That understates the result badly, because the later instalments were never invested for the full period. Applied to the SIP in the chart above, this shortcut produces 7.21% when the honest answer is 13.88% — a gap wide enough to make a perfectly ordinary investment look poor.
The opposite error is quoting the absolute return of 41.67% as though it were an annual figure. It is not. It is the total growth across five years, and it makes the same investment look far better than it was.
A worked example, step by step
Take a straightforward case. You invest ₹10,000 on the first of every month for five years, starting 1 January 2021. On 1 January 2026 the holding is worth ₹8,50,000. What was the return?
Step 1 · Lay out the cash flows
There are 60 instalments of ₹10,000, so ₹6,00,000 went in altogether. Each instalment is a negative cash flow on its own date. Then there is one positive cash flow of ₹8,50,000 on 1 January 2026. That is 61 dated flows in total.
Step 2 · Notice how differently each instalment was treated
The first instalment sat invested for 1,826 days. The last one had just 31 days before the valuation date. They cannot possibly deserve equal credit, and XIRR does not give them equal credit:
| Instalment | Date | Amount | Days invested | Grew to by 1 Jan 2026 |
|---|---|---|---|---|
| 1st | 01 Jan 2021 | ₹10,000 | 1,826 | ₹19,156 |
| 60th | 01 Dec 2025 | ₹10,000 | 31 | ₹10,111 |
| All 60 instalments grown forward | ₹8,50,000 | |||
The first instalment nearly doubled; the last barely moved. Grow all sixty forward at the same rate and the total lands exactly on the maturity value — that is the check that confirms the rate is right.
Step 3 · Compare the three answers
| Measure | Result | Is it appropriate here? |
|---|---|---|
| Absolute return | 41.67% | No — it is five years of growth quoted as one number, with no annualisation. |
| CAGR on the total invested | 7.21% | No — it pretends all ₹6,00,000 was invested on day one. |
| XIRR | 13.88% | Yes — every instalment is weighted by the days it was actually invested. |
Gain of ₹2,50,000 on ₹6,00,000 invested, earned at an annualised 13.88%. That is the figure you can meaningfully set beside any other investment, whatever its cash flow pattern.
Calculating XIRR in Excel and Google Sheets
Both Excel and Google Sheets include a built-in XIRR function, and the calculator on this page follows the same definition, so the two should agree.
The formula
=XIRR(values, dates, [guess])
- values — the range of cash flow amounts, negative for money invested and positive for money received.
- dates — the matching range of dates, one per cash flow.
- guess — optional starting estimate. Leave it out; if omitted, Excel assumes 0.1 (10%).
Laying out the sheet
Two columns is all it takes. Amounts in one, dates in the other, one transaction per row, with the current value as the final positive row:
| A — Amount | B — Date | |
|---|---|---|
| 2 | -50000 | 01-04-2022 |
| 3 | -50000 | 01-04-2023 |
| 4 | -30000 | 01-10-2023 |
| 5 | 180000 | 01-04-2026 |
| 7 | =XIRR(A2:A5, B2:B5) → 10.40% | |
Format the answer cell as a percentage, or it will show as a decimal like 0.1040.
How Excel arrives at the answer
Per Microsoft’s documentation, Excel discounts payments on a 365-day year and solves by iteration, starting from the guess and adjusting until the result is accurate to within 0.000001%. If it cannot settle on an answer within 100 attempts, it returns a #NUM! error. Usefully, the dates may be listed in any order — they do not have to be sorted.
Why you might see #NUM!
| Cause | Fix |
|---|---|
| Every amount has the same sign | The series needs at least one negative and one positive value. Usually the current value has been left out. |
| Amounts and dates ranges are different lengths | Make both ranges cover the same number of rows. |
| Dates entered as text | Enter real dates, or build them with the DATE function. |
| Iteration did not converge | Supply a guess — try a value between 0 and 1. |
The calculator above sidesteps the guess entirely: it brackets the answer and narrows in on it, so it does not depend on a good starting estimate.
How to read your XIRR figure
Once you have a number, the natural question is whether it is any good. There is no universal threshold — that depends entirely on your own horizon, the risk you took and the alternatives available to you. What can be done is to place the number in factual context.
Historical reference points
AMFI’s Best Practices Guidelines Circular No. 109/2023-24 published the mean of ten-year rolling returns for several asset classes between 1 June 2013 and 30 May 2023. These are historical figures for broad asset classes, published for illustration purposes:
| Asset class | Mean 10-year rolling return |
|---|---|
| Nifty 50 | 12.93% |
| Sensex | 12.64% |
| Gold (INR) | 9.34% |
| 10-year G-Sec | 7.20% |
Source: AMFI Best Practices Guidelines Circular No. 109/2023-24. Past performance does not indicate future results, and these figures are not a target or a projection.
Short periods distort badly
Because XIRR always annualises, a modest gain achieved quickly is projected into a dramatic-looking yearly rate. The same 5% gain, measured over different holding periods, produces wildly different XIRR figures:
| Gain | Held for | XIRR |
|---|---|---|
| 5% | 30 days | 81.05% |
| 5% | 90 days | 21.88% |
| 5% | 182 days | 10.28% |
| 5% | 365 days | 5.00% |
An 81% XIRR here is arithmetically correct but tells you almost nothing useful. This is why XIRR is best read over holding periods of a year or more, and why short-period returns are conventionally reported in absolute terms.
Two adjustments the figure does not make
- Costs and taxes. XIRR is calculated on the cash flows you enter. Exit loads, brokerage, STT, stamp duty, GST and capital gains tax are not deducted unless you have entered amounts net of them. Your realised return will be lower.
- Inflation. The result is a nominal rate. To judge whether purchasing power grew, you would need to compare it against inflation over the same period.
Limitations and common mistakes
What XIRR assumes
The arithmetic quietly assumes that every interim cash flow could have been reinvested at the same XIRR for the remaining period. In reality that opportunity may not have existed. For an investment with large withdrawals partway through, this assumption does more work than most people realise, and the resulting figure should be treated as an approximation of experience rather than a precise fact.
More than one possible answer
When the cash flow series changes sign several times — invest, redeem, invest again, redeem again — the underlying equation can in principle have more than one mathematically valid solution. Calculators return one of them. If a result looks strange for a heavily traded holding, that is worth knowing about before drawing conclusions from it.
Mistakes worth avoiding
| Mistake | What happens |
|---|---|
| Leaving out the current value | Every amount is negative, so no rate can solve the series and you get no result. |
| Entering investments as positive | The sign convention inverts and the answer becomes meaningless. |
| Using approximate dates | Small date errors matter more over short periods than long ones. |
| Comparing XIRR against a CAGR | Comparing a cash-flow-weighted rate with a lump-sum rate is not like for like. |
| Reading XIRR as a forecast | It measures the past. It carries no information about future returns. |
| Mixing several unrelated holdings in one series | You get a blended portfolio rate, not the return of any individual investment. |
Where it genuinely helps
Despite all of that, XIRR remains the most honest simple measure available for irregular investing. It is the right tool for a SIP with skipped or topped-up months, a portfolio built by adding money whenever it was available, a property where purchase, expenses and sale fell on scattered dates, or any comparison between two investments whose cash flow patterns differ. It puts them on a common footing, which absolute return and CAGR cannot do.
EquityTimer publishes factual, educational data only. It is not a SEBI-registered adviser, and nothing here is a recommendation to buy, sell or hold any security.
Frequently asked questions
What is XIRR in simple terms?
XIRR is the single annual rate of return that explains an investment where money went in or came out on several different dates. It gives each rupee credit for exactly how long it stayed invested, so an amount added five years ago counts for more than one added last month. It is the measure fund platforms use to report SIP returns.
What is the difference between XIRR and CAGR?
CAGR describes a single lump sum: one amount in, one amount out, and the number of years between them. XIRR handles any number of cash flows on any dates, including withdrawals. For a single investment with a single exit, the two give the same answer. The moment there is more than one dated cash flow — as in any SIP — CAGR no longer applies and XIRR is the correct measure.
What counts as a good XIRR?
There is no fixed threshold, and what is appropriate depends on your horizon, the risk taken and the alternatives open to you. For context, AMFI’s Best Practices Circular No. 109/2023-24 reported mean ten-year rolling returns between June 2013 and May 2023 of 12.93% for the Nifty 50, 12.64% for the Sensex, 9.34% for gold in rupee terms and 7.20% for the 10-year G-Sec. Those are historical figures for broad asset classes, not targets, projections or benchmarks you should expect to match. Remember too that XIRR is calculated before taxes and costs.
Why is my SIP’s XIRR higher than its absolute return?
Because absolute return ignores time. In a five-year SIP, your final instalment was invested for about a month, yet absolute return treats it exactly like the first instalment that was invested for five years. That drags the total-growth percentage down. XIRR gives each instalment credit only for the days it was actually invested, which usually produces a higher annualised figure. In the example on this page, an absolute return of 41.67% corresponds to an XIRR of 13.88%.
How do I calculate XIRR in Excel?
Put your cash flow amounts in one column and the matching dates in the next, with investments as negative numbers and redemptions or the current value as positive. Then use =XIRR(values, dates) — for example =XIRR(A2:A5, B2:B5) — and format the result as a percentage. The optional guess argument can normally be left out; Excel assumes 0.1, or 10%, when it is omitted. Google Sheets uses the same function and syntax.
Why is the calculator not showing a result?
Almost always because the cash flow series does not contain both a negative and a positive amount. The most common cause is forgetting to enter the current value of a holding you have not sold — add it as a positive amount dated today. Other causes are an invalid or missing date, a maturity date that falls on or before the start date, or a period too short to contain a single instalment at the chosen frequency. Excel signals the same problem with a #NUM! error.
Can XIRR be negative?
Yes. A negative XIRR simply means the money came back worth less than it went in, annualised over the holding period. If you invested ₹1,00,000 and another ₹1,00,000 a year later, and the holding was worth ₹1,50,000 at the end of the second year, the XIRR works out to about −17.70%. The calculation handles losses in exactly the same way as gains.
Is the EquityTimer XIRR calculator free to use?
Yes. It is completely free, needs no login and no subscription, and works for both regular SIP-style schedules and fully irregular cash flows. The entire calculation runs inside your own browser, so no transaction detail you enter is stored or transmitted.
Educational and informational content only. EquityTimer is not a SEBI-registered investment adviser and does not provide buy, sell or hold recommendations. Calculator outputs are illustrative estimates based on the figures you enter, are stated before taxes, exit loads and transaction costs, and may contain errors. Past performance is not indicative of future results. Verify independently and consult a registered financial adviser before making any investment decision.