XIRR in Mutual Funds

SIP returns rarely match lump sum returns, and the calculation behind that difference matters. XIRR shows you the actual return on a SIP because it accounts for every instalment and the time each one stayed invested. Beginners default to CAGR, which assumes a single investment over a fixed period and misses the staggered nature of SIPs. This blog covers what XIRR means, why it matters, how to calculate it, and where it differs from CAGR. 

What Does XIRR Mean?

An XIRR in mutual funds, or extended internal rate of return, is a single rate of return in mutual funds. It tells about the current value of the total investment, particularly in each instalment of your SIP. It is the actual return on your investments.

Investment cash flows can be dynamic. For example, if you start a SIP and can continue it for 6 months. You can skip the instalment for 2 months, and then resume it. Additionally, you may also keep changing the SIP amount in regular intervals or even redeem some part of the investment.

You can use XIRR to calculate your investment returns with multiple transactions which are not evenly spaced, just like this example. This rate can give the total value of your investment as it is applied to all the instalments and redemptions.

Why is XIRR Important in Mutual Funds? 

Now that you have an idea of what is XIRR in mutual funds, let us understand its significance.

Since it provides an accurate calculation of investment returns over time, XIRR is crucial for evaluating mutual fund returns. XIRR provides a more realistic representation of an investor’s experience by taking into account the timing of each cash flow, in contrast to basic annualised returns.

If you invest in mutual fund online, especially in SIPs, where investments and withdrawals can be made at different times, this is especially crucial. Below are the reasons why XIRR is important in mutual funds.

1. Precise Evaluation of Returns

   The amount and timing of each cash flow are taken into account by XIRR. This helps to provide an accurate evaluation of returns. For investors who have made several transactions along their financial journey, this is very helpful.

2. Appropriate for Systematic Investments

   Considering the staggered structure of these investments, XIRR efficiently calculates the annualised return for those using a SIP method with regular payments.

   Try our SIP Calculator to estimate SIP returns.

3. Calculating Overall Gains

   Overall gains might be greatly impacted by redemptions. Here, XIRR provides an accurate view of the performance of your investment, which accounts for all withdrawals.

XIRR formula explained 

In Excel, XIRR is calculated using the formula =XIRR(values, dates, [guess]). Each component does a specific job: 

• Values are the cash flows from your investment. Outflows such as SIP instalments and lump sum purchases are entered as negative numbers; inflows such as redemptions, SWP withdrawals, and dividends are entered as positive numbers. 

• Dates are the exact dates on which each cash flow occurred. This is what allows XIRR to weight every transaction by the time it stayed invested. 

• Guess is an optional starting estimate for the rate of return. Excel uses 10% by default if you leave it blank. 

How to Calculate XIRR in Mutual Funds?

Step 1: Enter the dates of your transactions in Column A and the cash flows in Column B. Mark investments (SIP instalments, lump sum purchases) as negative and redemptions or dividends as positive. 

Step 2: In the last row, enter the current date and the present value of your investment. This is treated as a final inflow. 

Step 3: Apply the formula =XIRR(values, dates) in an empty cell. Here, values refers to the column of cash flows, and dates refers to the column of corresponding transaction dates. 

Step 4: Press Enter. Excel returns the annualised rate of return as a decimal; format the cell as a percentage to read it directly. 

 Example of XIRR Calculation Using Excel 

Let us use a realistic example to understand the XIRR calculation. Suppose your transactions look like this: 

Follow the steps below to calculate XIRR: 

This single number reflects the actual return on your investment, accounting for the lump sum, every SIP instalment, the partial redemption, and the time each rupee stayed invested. 

Follow the steps below to make the XIRR calculation in Excel for this example:

1. Enter the details – A2: 01-01-2023, A3: 01-02-2023, A4: 01-03-2023, and so on until A7: 01-06-2023. Put 01-12-24 in A8, which is the redemption date.
2. Enter the cash flows or SIP amount in Column B. B2: -5000, B3: -5000, B4: -5000, and so on until B7: -5000. Enter 40,000 in B8, which is your redemption amount.
3. In the next empty cell, C2, enter the formula: =XIRR(B1:B7, A1:A7)
4. Press enter, and the rate of return will be determined by the XIRR function. You will see a value of 18.28% displayed on C2. This is your XIRR in this investment.

Final Thought

Understanding what is XIRR in mutual funds is crucial since it is the most accurate method for determining your actual investment returns. While CAGR should be considered while choosing a lump sum investment, XIRR is essential for assessing the returns on your SIP investments.

If you want to start an SIP, open a Demat account with PL Capital Group – Prabhudas Lilladher for free. You can start SIP with as low as INR 100 per month with your preferred mutual fund schemes.

Frequently Asked Questions

1. Is XIRR a better method than CAGR?

Both XIRR and CAGR have different purposes. While CAGR is suitable for calculating consistent growth, XIRR is appropriate for irregular cash flows.

2. What is a perfect XIRR in mutual funds?

There is no specific XIRR, which can be considered perfect. It varies depending on an individual’s risk tolerance and goals.

3. What is the difference between XIRR and absolute return?

An absolute return is the total return according to an investment’s future value and its initial amount, without considering the cash flow timing. Conversely, XIRR in mutual funds also analyses the returns by considering the amount and timing of the cash flow.

4. How to improve XIRR?

To improve an XIRR, you have to focus on timely and consistent investment in your SIP.

5. Can XIRR be negative?

Yes. XIRR turns negative when the value of your withdrawals and current holdings is lower than the total amount you invested. It reflects a loss on an annualised basis, accounting for the timing of each cash flow.