Before learning how to calculate XIRR, let's first understand what the term XIRR means.
XIRR is commonly expanded as Extended Internal Rate of Return.
X – Extended (investments made on different dates)
IRR – Internal Rate of Return
Unlike CAGR, which assumes you invest a lump sum only once, XIRR is used when you invest or withdraw money on different dates. Like doing SIPs, STPs, SWPs where the transactions are done at different times.
In simple words, XIRR calculates the annualized rate of return by considering both the amount and the date of every investment and withdrawal.
That is why most mutual fund platforms display your returns using XIRR instead of CAGR.
Although XIRR is calculated using a complex mathematical equation, you don't need to solve it manually.
Tools like Google Sheets and Microsoft Excel can calculate it instantly using the built-in XIRR function.
XIRR Example in a Mutual Fund
Let's understand how XIRR works with a real-life mutual fund example.
Date | Transaction | Cash Flow (₹) |
10-Jan-2025 | SIP Investment | -10,000 |
10-Feb-2025 | SIP Investment | -10,000 |
10-Mar-2025 | SIP Investment | -10,000 |
10-Mar-2026 | SIP Redemption | 5,000 |
30-Jul-2026 | Current Portfolio Value | 31,850 |
As you can see, all SIP investments are entered as negative (-) cash flows because the money is going out of your pocket and into the mutual fund.
On the other hand, the SIP redemption and the current portfolio value are entered as positive (+) cash flows because the money is coming back to you.
Now, let's use the built-in XIRR function in Google Sheets to calculate the annualized return.
Example of XIRR calculation through Googlesheet
Google Sheets Formula:
=XIRR(H2:H6, F2:F6)
Only the Date and Cash Flow columns are used in the calculation.
In the example below, Google Sheets returns 0.1564683082. Since this value is in decimal form, multiply it by 100 or simply format the cell as Percentage. The result is:
XIRR = 15.65%
This means your mutual fund investment, after considering all SIP investments, the partial redemption, and the current portfolio value, has generated an annualized return of 15.65%.
Importance of XIRR as an Investor
- XIRR is one of the best tools to measure the actual return on your investments, especially when you invest or withdraw money at different points in time.
- XIRR helps you compare the performance of different mutual fund categories, such as large-cap, mid-cap, and multi-cap funds, on a common basis.
- It can also help you calculate the actual returns from other investments and government-backed schemes such as PPF.
- XIRR is an in-built function available in Google Sheets, Microsoft Excel, and various other applications, so you can easily calculate your investment returns without remembering its complex mathematical formula.
Want to Master the Basics of Mutual Funds Explore our Mutual Fund Learning Hub, which hosts 100+ questions and detailed guides on mutual funds to help you build a strong understanding and become a more informed investor.
