How is XIRR calculated?
XIRR uses a mathematical equation that can get complicated to calculate manually. The important thing is understanding what goes into the calculation. XIRR needs two main things:
- Cash flows: How much money you invested or withdrew.
- Dates: The exact date on which each cash flow happened.
For example:
The investment amounts are entered as negative numbers because money is leaving your pocket. The final amount is entered as a positive number because money is coming back to you. XIRR then finds the annualised rate at which these cash flows work out to the current value.
How to calculate XIRR in Excel
Google Sheets and Microsoft Excel both let you calculate XIRR with a simple formula. All you need is to enter a few details:
- Type each SIP date in a separate row in one column.
- Type the corresponding investment amounts in another column.
- Enter investments as negative values.
- Enter the present value of your investment as a positive value.
- Use the formula =XIRR(values, dates).
For example, if your cash flows are in cells B2:B7 and the dates in A2:A7, you can use: =XIRR (B2 : B7 , A2 : A7). The result will be an annualised percentage.
If you haven’t redeemed your mutual fund yet, you can use its current value as the final positive cash flow along with the date on which that value was calculated.
XIRR vs CAGR: What’s the difference?
Both XIRR and CAGR measure annualised returns, but they are used in different situations. The main difference between XIRR and CAGR, is that CAGR works well for a one-time investment. Suppose you invest ₹10,000 once and it becomes ₹16,000 after 5 years. CAGR tells you how much your investment has grown annually in those 5 years.
What are the benefits of XIRR?
- It handles different investment amounts: Your investments may not always be the same. You may invest ₹1,000 one month and ₹2,000 the next. But the XIRR tells you the net return by taking every single cash flow into account.
- It considers withdrawals: It can also include cash coming out of your investment. For example, if you make a partial redemption, that cash flow is also included in the calculation.
- It gives an annualised return: The result is shown as a yearly rate. This makes it easier to compare the performance of investments that have different cash-flow patterns.
What are the limitations of XIRR?
- You need accurate transaction details: XIRR depends on the cash flows and dates you enter. If you enter incorrect dates or amounts, your result will also be incorrect.
- It can be complicated to calculate: The math behind XIRR isn't very easy. Thankfully, you don’t need to calculate it by hand. Excel, Google Sheets and many investment platforms can do the calculation for you.
- It isn’t useful for lump-sum investments: If you invested one amount once and haven’t made any other transactions after that, CAGR is a better method to look at your annualised return.
When should you use XIRR?
If you invest ₹5,000 once in a mutual fund SIP and leave it untouched, CAGR may be enough. But if you invest ₹500 every month through SIP, XIRR is a more useful method, as every ₹500 was invested on a different date.
The same logic applies if you make additional purchases, partial redemptions or other transactions at different times.
The bottom line
Understanding what is XIRR in mutual funds isn’t enough. You should review your SIP’s performance annually to know exactly where it stands. But don’t get too caught up in short-term numbers. Checking your XIRR too often can make you worry and take decisions based on emotions.
Mutual funds are generally meant for the long term. Markets will fluctuate along the way. Stay consistent and give your investments time to grow towards your bigger financial goals.
