Learning how to calculate XIRR is the best way to judge returns when money goes in and out on different dates. Unlike a simple yearly return, XIRR takes every cash flow and the exact date it happened into account, so it works for SIPs, lump sums, withdrawals and irregular investments alike. In this guide, you will learn the XIRR formula in plain language, follow it step by step, and work through a clear example. Once you understand the idea, you can check any investment with our free XIRR Calculator and compare returns on a fair, annualised basis.
The Formula
XIRR is the annual rate that makes the present value of all your cash flows add up to zero. In symbols, it is the rate r that satisfies: the sum of each cash flow divided by (1 + r) raised to the power of (days since the first cash flow divided by 365) equals zero. You do not usually solve this by hand; a calculator or spreadsheet tries different rates until the sum reaches zero.
Each cash flow is one amount of money, with money you invest written as a negative number and money you receive written as a positive number. The date of each cash flow matters because it decides how long that money was at work. Dividing the number of days by 365 turns each period into a fraction of a year, which is why XIRR is an annualised rate. The result is the single yearly rate that explains all those dated flows together.
This is why XIRR beats CAGR for irregular cash flows. CAGR assumes one lump sum invested once and left alone, so it cannot handle monthly SIPs, top-ups or part withdrawals fairly. XIRR, by contrast, weights every flow by its actual date. In a spreadsheet, the same calculation is done with the XIRR function: you give it the list of amounts and the matching list of dates, and it returns the annualised rate. Excel and Google Sheets both support this function, which uses the same method as the calculator on this site.
How to Calculate XIRR Step by Step
- List every cash flow with its date: investments as negative amounts, and receipts and the current value as positive amounts.
- Count the days from the first cash flow to each later one, and divide by 365 to get the time in years.
- Find the rate r that makes the sum of the discounted cash flows zero, using a calculator or the XIRR spreadsheet function.
- Read the result as a yearly percentage, and compare it only with other investments measured the same way.
- Check the sign of the answer: a positive XIRR means growth, while a negative XIRR means the investment lost value on this basis.
Worked Example
Suppose you invested Rs 1,00,000 on 1 January 2023 and received Rs 1,20,000 on 1 January 2024. That gives two cash flows: -1,00,000 on 1 January 2023 and +1,20,000 on 1 January 2024. The gap between the dates is exactly one year, or 365 days, so the time factor for the second flow is 365 / 365 = 1.
XIRR is the rate r that makes -1,00,000 + 1,20,000 / (1 + r) equal zero. Solving gives 1 + r = 1.20, so r = 0.20, or 20.00% per year. That is the same result shown on the tool page: invested Rs 1,00,000 on 1 January 2023, received Rs 1,20,000 on 1 January 2024, XIRR 20.00%.
In plain terms, your money grew at 20% a year over that single year. With more cash flows on different dates, the same idea applies, but only a calculator or spreadsheet can find the rate quickly, because each flow is discounted by its own time factor. That is exactly the kind of irregular history, such as a SIP with monthly instalments, where XIRR is far more honest than a single start-to-end percentage.
Common Mistakes / Good to Know
- Using the wrong signs: if investments are entered as positive amounts, the result flips. Keep money going in as negative and money coming out as positive.
- Leaving out exact dates, or using only years: XIRR needs the real dates, because a flow in January and one in December are not worth the same.
- Comparing XIRR directly with CAGR or a simple return: they measure different things, so compare XIRR with XIRR over similar periods.
- Good to know: XIRR can be negative, and that is a valid answer. It simply means the dated flows lost value overall, which is useful information, not a calculation error.
Frequently Asked Questions
What is XIRR in simple terms?
XIRR is the single annual rate that explains a set of dated cash flows. It finds the yearly rate at which the value of everything you put in, discounted by its date, equals the value of everything you took out.
Why is XIRR better than CAGR for SIPs?
CAGR assumes one investment made once, while a SIP has many instalments on different dates. XIRR gives each instalment its own time weight, so it reflects the real history of the investment.
Can XIRR be negative?
Yes. A negative XIRR means the money you received, after allowing for timing, was worth less than the money you invested. It is a valid result and a sign the investment lost value.
How do I calculate XIRR in Excel?
Use the XIRR function with two matching lists: the amounts and their dates. Enter investments as negative numbers and receipts as positive numbers, and the function returns the annualised rate.
Once you can work out XIRR, you can also check the average price of your holdings with our Groww Average Calculator, and compare loan costs in our guide How to Calculate EMI.
1 thought on “How to Calculate XIRR: Formula with a Worked Example”