EN
XIRR in mutual funds is a method used to calculate annualised returns when investments and withdrawals occur at different points in time. It accounts for both the amount and timing of each transaction, making it suitable for SIPs and multiple cash flows.
Unlike simple return measures, XIRR provides a more accurate view of actual investment performance by considering every inflow and outflow. This makes it a widely used metric for evaluating mutual fund portfolios with irregular investment patterns.
Key Takeaways
● XIRR measures returns by considering when and how much you invest.
● It is particularly useful for SIPs and investments made at irregular intervals.
● It gives a more realistic return than simple methods like CAGR when multiple cash flows are involved.
● It is commonly calculated using Excel or investment‑tracking tools that accept cash‑flow and date data.
What is XIRR?
XIRR means Extended Internal Rate of Return. It is a method used to calculate the annual return of an investment when there are multiple transactions at different points in time. Instead of looking only at the start and end value, it considers every investment and withdrawal along with their dates.
This makes XIRR more suitable for mutual funds, where investors often invest through SIPs or make additional purchases and redemptions. Taking both timing and amount into account, it gives a clearer picture of actual returns compared to simple point-to-point measures. XIRR is widely used in India to calculate SIP and portfolio returns for mutual funds, as it factors in the timing and size of each cash flow.
Why Does it Make Sense for Mutual Fund Investments?
For many investors, mutual fund contributions are not evenly spaced over time. There may be SIPs, ad‑hoc lump‑sum top‑ups, and redemptions at different dates. In such cases, both the amount invested and the timing of investments and withdrawals affect the overall return.
XIRR (Extended Internal Rate of Return) is designed precisely for this scenario. It treats all money paid out by the investor (SIP instalments, lump sum purchases) as negative values, and all money received by the investor (redemptions, SWP payouts, IDCW/dividend receipts, and current portfolio value) as positive values.
This makes XIRR a practical way to compute a consolidated return for mutual fund portfolios with irregular cash flows, whether you invest via SIP, lump sum, or redeem via SWP or lump sum.
How to Calculate XIRR in Mutual Funds
● Record all transactions in a sheet with two columns: date and cash flow.
● Enter investments (SIP or lump sum) as negative values and withdrawals or current value as positive values.
● Make sure each amount is matched with the correct transaction date.
● Apply the XIRR function in Excel using the selected date and cash flow range.
● The result shows your annualised return, based on both timing and amount of each transaction.
XIRR Formula
The XIRR formula is derived from the net present value (NPV) equation:
where:
● Ci is the cash flow on date i (negative for outflows, positive for inflows)
● ti is the time in years from the first transaction to that cash‑flow date.
The XIRR function solves for the rate r (the annualised return) that makes the sum of all discounted cash flows equal to zero. Because this is iterative and complex, it is typically computed using spreadsheet tools such as Excel.
Step-By-Step Process To Calculate XIRR in Excel
● Create two columns in Excel: one for dates and one for cash flows.
● Enter all transactions carefully, making sure each amount matches the correct date.
● Record investments (like SIPs or lump sum) as negative values and redemptions or current value as positive values.
● In the last row, add the current value of your investment along with the latest date if units are not redeemed.
● Use the XIRR formula in Excel: =XIRR (values, dates, [guess]) by selecting the cash flow and date ranges.
● Press Enter to get the annualised return, which reflects both the timing and the amount of each transaction.
Example of how to use the XIRR function in Excel
To understand how XIRR works, consider a simple SIP example.
An investor puts ₹6,000 every month for six months, starting from 02-01-2026 to 02-06-2026. The investment is then redeemed on 02-07-2026, and the total value received is ₹38,500.
In Excel, each investment is entered as a negative value, while the final redemption is entered as a positive value. The dates and cash flows would look like this:
|
Date |
Cash Flow |
|
02-01-2026 |
-6000 |
|
02-02-2026 |
-6000 |
|
02-03-2026 |
-6000 |
|
02-04-2026 |
-6000 |
|
02-05-2026 |
-6000 |
|
02-06-2026 |
-6000 |
|
02-07-2026 |
38500 |
After entering this data, apply the XIRR function using the selected date and value ranges. The result will show the annualised return, taking into account when each investment was made.
Understanding XIRR in Account Statements
Mutual fund providers issue Account Statements (AS) to investors, offering a detailed overview of their investments. These statements include essential information such as the average investment cost per unit, total units held, total investment amount, current valuation, and annualised returns.
The annualised returns are calculated using the commonly used XIRR method, which takes into account both Net Asset Value (NAV) growth and dividends received over the investment period.
XIRR provides a standardised measure of investment performance, making it a useful tool for comparing the returns of different mutual fund schemes. By evaluating XIRR values, investors can assess performance based on investment duration and market conditions, enabling them to make well-informed decisions about their portfolios.
Understanding XIRR in SIP Investments
XIRR (Extended Internal Rate of Return) is a method used to calculate annualised returns for investments that have irregular cash flows. In a Systematic Investment Plan (SIP), where contributions are made at different times, the exact investment dates and amounts can vary.
Unlike basic return calculations, which may not account for these variations, XIRR provides a clear measure of investment performance by factoring in both the timing and value of each transaction. This makes it a more accurate tool for evaluating SIP returns.
For investors, XIRR plays a crucial role in assessing real investment performance, offering a more practical way than simplified return calculations. It is particularly beneficial when comparing different investment options and helps in making informed decisions.
Whether investing in mutual funds or stocks through SIPs, using XIRR ensures a clearer understanding of annualised returns, making it easier to plan and manage investments.
Importance of XIRR in SIP
● Accurate return calculation: XIRR provides a precise measure of SIP returns by considering the exact dates and amounts of each investment, offering a true reflection of growth.
● Accounts for irregular investments: Unlike traditional return calculations, XIRR factors in variations in investment amounts and frequency, giving a more reliable view of performance.
● Effective comparison tool: Helps compare returns across different SIPs or investments, making it easier to identify high-performing investments.
● Enhances financial planning: Helps with goal setting, tracking progress, and making changes when needed.
● Encourages long-term commitment: Knowing your returns helps you stay consistent with your SIP.
Limitations of XIRR
● Requires accurate data: XIRR relies on precise cash flow data, including exact dates and amounts. Any errors can impact the accuracy of the results.
● Highly sensitive to changes: Even minor alterations in cash flow data can significantly affect the XIRR calculation, making it harder to compare different investments clearly
● Not suitable for all investments: While ideal for irregular cash flows like SIPs, private equity, or real estate, XIRR may not be as effective for fixed-income investments such as bonds or annuities.
Can We Use CAGR Instead for Calculating Returns?
When investing in a mutual fund, people usually look at its returns over the past three, five, or more years. These returns are point-to-point returns and are denoted by the acronym CAGR, or compound annual growth rate.
CAGR is easily determined by applying the following formula:
CAGR = ((Ending Amount/Beginning Amount)^(1/No. of years)) – 1
This method is commonly used to estimate returns in mutual funds. While calculating CAGR for a mutual fund is simple, doing it for individual investments can be slightly difficult.
What is the difference between XIRR and CAGR?
|
Basis |
CAGR |
XIRR |
|
Definition |
Shows annual return between start and end value |
Shows annual return considering multiple transactions |
|
Calculation focus |
Uses beginning value, ending value, and time period |
Considers every cash inflow and outflow with dates |
|
Suitability |
Suitable for lump sum investments |
Suitable for SIPs and irregular investments |
|
Accuracy |
May not reflect real returns for multiple investments |
Gives a more realistic return by considering timing |
|
Method |
Based on compounded annual growth rate |
Based on discounting all cash flows to one rate |
Both metrics are useful, but XIRR is more practical when investments happen at different points in time.
Read More About: CAGR vs XIRR
Importance of XIRR in Mutual Funds
You can use the XIRR method in Excel to handle irregular cash-flow times. Simply put, XIRR is IRR adjusted to allow assigning exact dates to individual cash flows, making it a more accurate way to calculate returns.
You only need to enter the transactions (SIP/SWP installments, additional purchases, redemptions, etc.) and the relevant dates to find out the XIRR for mutual funds. You can use the XIRR formula in an Excel sheet to obtain these transactional facts from the account statement provided.
Read More About: SIP vs SWP
Conclusion
XIRR in mutual funds provides an accurate measure of annualised returns by accounting for both the timing and amount of each transaction. It is particularly relevant for investments made through SIPs, lump sum additions, or partial withdrawals over time.
By capturing the impact of multiple cash flows, XIRR offers a clearer view of actual portfolio performance compared to simpler return measures. This makes it a useful metric for evaluating and comparing mutual fund investments with irregular investment patterns.
Looking to invest? Open a Demat Account with Angel One and start trading seamlessly.
