What Is XIRR in Mutual Funds: How to Calculate Your SIP Returns Accurately

XIRR in mutual funds is a method used to calculate the annualised return on investments when money is invested or withdrawn on different dates. Instead of calculating the return on each transaction separately, XIRR considers all investments, withdrawals, and their respective dates together. 

It then calculates a “single annualised return” that reflects the overall performance of the investment. This makes XIRR suitable for investment scenarios where cash flows occur at different points in time. For this reason, XIRR is potentially used to measure returns for:

• SIPs (Systematic Investment Plans)
• SWPs (Systematic Withdrawal Plans)
• STPs (Systematic Transfer Plans), and 
• Portfolios involving additional investments or partial withdrawals

Since it accounts for both the amount and the timing of every cash flow, XIRR may provide a more accurate measure of the investor's actual annualised return than methods that only compare the initial and final investment values (such as absolute returns or CAGR).

Firstly, note that XIRR is the rate of return at which the Net Present Value (NPV) of all cash flows becomes equal to “zero”. In other words, XIRR is the annualised return that makes the “present value (PV) of all investments” equal to the “present value (PV) of all withdrawals and the final investment value. 

Mathematically, it can be presented as follows:

Further, since XIRR is the rate at which NPV = 0, it can also be written as follows: 

Where,

• t = Time elapsed (in years) from the first cash flow
• ​ = Cash flow at time “t” (investments are negative and withdrawals/final value are positive)
• r = XIRR (annualised rate of return)
   

Investors may observe that this mathematical definition directly states that the discounted value of all cash flows sums to zero.

If you were to manually calculate XIRR in mutual funds, you would have to:

• Assume a rate of return
• Calculate the NPV of all cash flows using that rate, and
• Check whether the NPV equals zero

If it does not, you would repeat the process with another assumed rate. This “trial-and-error” process continues until you find the rate at which the NPV becomes zero.

Finding it complex? Since the manual process involves multiple calculations, several investors use spreadsheet software such as Microsoft Excel or Google Sheets to calculate XIRR. Let’s see how.

Spreadsheet applications perform the required iterations automatically while making XIRR calculations. The software returns the annualised rate of return based on the investment amounts and their corresponding dates.

The syntax for the XIRR function or XIRR formula in Excel is:

• =XIRR(values, dates)

Where:

• “Values” represent the range of cash flows. In the cells, investments are entered as negative values, while withdrawals and the current portfolio value are entered as positive values.
• “Dates” represent the corresponding dates for each cash flow.

Need a better understanding? Let’s study a hypothetical example. 
 

Example

Suppose an investor starts a monthly SIP of ₹5,000 in an equity mutual fund on 1 January 2025 and continues investing until 1 December 2025, making a total of 12 installments. On 31st December 2025, the investor decided to check the portfolio value. By then, the total investment amounts to ₹60,000, while the portfolio is worth ₹62,500. 

To calculate the return using any spreadsheet software:

• The investor enters all SIP installments as negative values (cash outflows) and 
• The final portfolio value is entered as a positive value (cash inflow)
   

The corresponding transaction dates are also entered alongside each cash flow. For your reference, a sample calculation has also been made below:

In the above example, you can observe that the XIRR is approximately 8% p.a.

This return reflects the fact that each SIP instalment remained invested for a different period. The first instalment earned returns for nearly the whole year, while the last instalment remained invested for only about one month. 

By considering the amount and date of every transaction, the XIRR formula in Excel calculated the annualised return on the entire investment.

CAGR (Compound Annual Growth Rate) measures the annualised return of an investment that grows from an initial value to a final value over a specific period. It assumes that the entire investment was made at the beginning and remained invested throughout the investment period. 
 

On the other hand, XIRR is designed for investments where money is invested or withdrawn on different dates. Instead of considering only the initial and final values, XIRR considers every cash flow along with its date. 
 

To gain more clarity on XIRR vs CAGR, let’s study the comparative analysis below:

So, now you know what XIRR in mutual funds is and how it is calculated. To revise, the technique of XIRR is used to calculate returns when money is invested or withdrawn on different dates. This method considers the amount and timing of every cash flow and calculates a single annualised return for the entire investment. 
 

This makes it suitable for SIPs, SWPs, STPs, and portfolios involving additional investments or partial withdrawals. Since the XIRR calculation involves multiple iterations to find the rate at which the NPV equals zero, calculating it manually could be highly complex (particularly for beginners). Therefore, several investors use spreadsheet software such as Microsoft Excel or Google Sheets as an alternative. 
 

The XIRR formula in Excel is “=XIRR(values, dates)”, where values represent the cash flows and dates contain the corresponding transaction dates. The spreadsheet performs all the XIRR calculations automatically and returns a single annualised rate.
 

For more information, you can visit www.tatamutualfund.com/deshkarenivesh. The Investor Service Centre of Tata Asset Management Pvt. Ltd. is located at Mulla House, Ground Floor, 51, M.G. Road, Near Flora Fountain, Mumbai – 400 001, Maharashtra. The office hours are Monday to Friday, 9:00 AM to 5:30 PM. For assistance, you can also call (022) 6282 7777 from Monday to Saturday, 9:00 AM to 5:30 PM, or email service@tataamc.com

1. Can XIRR be negative even if I have invested regularly?

Yes, if the current value of your investments is lower than the total amount invested, XIRR can be negative. This usually happens during market declines or when the investment period is too short to recover from temporary losses. 

Usually, a negative XIRR in mutual funds indicates that your investment has generated a negative annualised return so far.
 

2. Should I use XIRR or CAGR to measure my SIP returns?

As per general industry practice, you may use XIRR for SIPs. That’s because every SIP installment is invested on a different date. The XIRR method considers the amount and date of each instalment and then calculates a single annualised return for the entire investment. 

In contrast, CAGR is a “point-to-point” return metric and assumes that the entire investment was made at the beginning. Thus, it might not give the correct annualised return for investments involving multiple cash flows.
 

3. Why is my XIRR different from the returns shown by my mutual fund?

The return shown by a mutual fund usually reflects the fund's performance over a specific period. Your XIRR, however, depends on:

• When you invested 

and 

• How much did you invest each time

Therefore, two investors in the same mutual fund can have different XIRR values if their investment dates or amounts differ.