XIRR in SIP – Step-by-Step Guide to Calculating SIP Returns Using XIRR

When you invest in mutual funds through a Systematic Investment Plan, each contribution you make – monthly, quarterly, daily, or at any other interval – is invested at different times and with different Net Asset Values. Every installment counts as its own investment, which means that each contribution will experience different market conditions, and holding periods, and generate varying returns. 

Using CAGR to evaluate your SIP returns will not provide an accurate picture because CAGR assumes a single investment with constant growth over time. In SIPs, multiple investments happen at different times, and each contribution amount grows at a different rate. Since CAGR does not consider the irregular cash flows involved, it’s not a very effective way to measure how your SIP has performed.

So the question is, how can investors accurately measure the returns on their SIP investments? An effective and popular way is using the Extended Internal Rate of Return, or XIRR, for short. This metric is an excellent way to calculate the annualised return for a number of cash flows happening at different time intervals. So, what is XIRR and how is it calculated? Let’s answer these questions.

What is XIRR in SIP?

The Extended Internal Rate of Return is a measure that tells us about the annualised returns on investments that involve multiple cash flows occurring at different times. When you invest in an SIP, you contribute a fixed amount to a mutual fund in regular installments. You can even choose to invest an extra lump sum you received as a bonus or a gift. You might need to pause your SIP for a few months due to financial circumstances or withdraw a portion of your investment to meet emergency expenses. All these scenarios make the cash flow irregular, and using CAGR is not the best way to calculate your actual returns here.

Since each instalment (or lump sum) is made at a different time and NAV, every contribution has a unique holding period and experiences varying market conditions. For example, suppose you started an SIP of Rs. 5,000 per month in April 2023. In October, you decided to invest an extra Rs. 25,000 you received as a bonus as a lump sum. You continued your SIP until April 2024, completing one year of investments. Here’s how the holding periods for each of these contributions look by then:

• Your April instalment has been invested for 12 months.
• Your May installment has been invested for 11 months, June for 10, and so on.
• The Rs. 25,000 lump sum investment made in October has been invested for only 6 months.

Each of these investments has compounded and grown at different rates. A simple average like CAGR is not an accurate measure of your returns as it treats all investments as if they had been made at the same time. So what is XIRR in sip? It’s simply a method that calculates the annualised return on irregular investments. Usually, this is calculated using an XIRR calculator for sip or through Excel using the formula =XIRR (values, dates, guess).

Features of XIRR in SIP

1. Considers Irregular Cash Flow

SIP contributions can vary from time to time as you may increase, decrease, pause, or even add lump sum investments alongside your regular installments. It also accounts for any partial withdrawals you may make due to financial needs and gives a more accurate picture of your returns.

2. A Versatile Measure

Even though XIRR is widely used for SIP investments, it is not limited to just SIPs. It can be applied to any investment that involves multiple cash flows occurring at different times like ULIPs.

3. Considers Exacts Dates of Investments

SIP investments happen on different dates each month and at varying NAVs. XIRR takes into account the exact date of every installment, lump sum, and withdrawal, rather than assuming all investments were made at the same time. For this reason, an sip investment planner may recommend using an XIRR calculator sip to review performance, as it provides the most accurate measure of returns.

4. Helps in Financial Planning

Knowing the XIRR of SIPs helps you evaluate your portfolio’s performance. You can use it to compare your mutual fund SIP returns with other investment options like ULIPs, stocks, or any investment with an irregular cash flow. When you know the true annualised return, you can make better decisions about whether to continue, alter, or switch your SIP investments.

Step-by-Step Guide to Calculate XIRR in SIP

The manual calculation for XIRR can be very complex, which is why investors generally use either an XIRR calculator or Excel function to get results. Follow this step-by-step guide to understand how is XIRR calculated using both ways:

Calculating XIRR using the Excel Function

Step 1: 

Create a ‘Date’ column. Input all dates of transaction here. This includes every installment, withdrawal, and lump sum investment.

Step 2: 

Create a ‘Transaction column’. Here you’ll be entering the investment or withdrawal amounts corresponding to the dates. For every investment (inflow), use a negative value, and for every withdrawal (outflow) use a positive value. For example, if your SIP investment amount is Rs. 10,000, simply enter ‘-10000’.

Step 3: 

Enter the maturity date and final value. In the date column, add the date when you want to redeem or check your investment value, and in the transaction column, enter the present or maturity value of your investment as a positive amount (since it is an outflow).

Step 4: 

Apply the XIRR formula. In the cell beneath your transactions, enter the XIRR formula: =XIRR(values, dates, guess). Here, ’values’ is the range of the transaction amounts (investments, withdrawals, and maturity). For example, if your transactions are listed in cells B2 to B10, you would use B2:B10 for this parameter. Similarly, ‘date’ refers to the range of the corresponding dates for each transaction. For example, if the dates are in cells A2 to A10, you would use A2:A10. ‘Guess’ refers to the initial guess at the rate of return. This is an optional field so you can leave this blank (or input a value like 0.1 which represents a 10% expected return) if you prefer to set a guess. Excel will calculate XIRR without it as well.

An Example

Raj has been investing Rs. 6,000 every month in a hybrid mutual fund since January. After completing a year without missing any SIP contributions, he wants to assess how his investment has performed. He also made a lump sum investment of Rs. 20,000 in May, as he felt the market dip presented a good opportunity to invest more. Now that his investment has grown to Rs. 1 lakh, he wants to calculate the overall returns on his investment for the year.

The XIRR can be calculated in an Excel table like this:

The XIRR formula is entered in the cell B16, which is =XIRR(B2:B15, A2:A15). The result is in decimal form, which can be converted into a percentage by multiplying it with 100. So Raj’s investments have returned approximately 15.8% since he started investing. Every cash flow is accounted for in this formula. If any partial withdrawals were made, they would also be accounted for in the XIRR formula as positive cash flows.

Calculating XIRR using an Online Calculator

You can find various XIRR calculators online to quickly calculate your SIP’s annualised returns.

• Step 1: Enter the amount you’re investing on a regular basis.

• Step 2: Enter the frequency of contributions, which can be monthly, weekly, daily, quarterly, and so on.

• Step 3: Enter the start date.

• Step 4: Select the maturity or current date.

• Step 5: Input the maturity or current amount.

• Step 6: Add any lump sum investments or withdrawals with their corresponding dates. Your SIPs’ XIRR will be displayed instantly. Not all XIRR calculators available online allow you to enter additional irregular cash flows. If you are having trouble finding accurate calculators, you can use Excel or Google Sheets to calculate your returns. 

Importance of XIRR in SIP

XIRR is an invaluable tool as far as SIPs are concerned. When you invest in a long-term SIP, you will likely increase or decrease your installment amount with time. You may receive windfalls like bonuses or inheritances, which could also prompt you to make lump sum investments into your SIP. You might want to pause them temporarily or even make partial withdrawals in case you; ‘re hit with a financial emergency. 

Over time, these irregularities can make it difficult to accurately assess the SIP’s performance. Since XIRR takes the exact date and cash flow into account, it provides a very accurate measure of your annualised returns.

Risks of Using XIRR in SIP

XIRR works best when there are irregularities in cash flows and exact investment dates are known. You won’t be able to account for the true impact of each cash flow on your returns if the data isn’t accurately known or entered. XIRR results for short-term SIPs may not be very reliable as there might not be enough time for the compounding effect to accurately reflect the impact of each investment.

Other than these, the main limitation of XIRR arises from not knowing when to use it, and when not to. For example, there are times when you’ll find CAGR to be the more useful measure, like when looking for the overall growth rate of an investment. Also when using an XIRR return calculator online, remember that not all calculators take irregularities into account. As any financial planner would recommend, you’re better off using the Excel function to handle the complexities of measuring SIP returns.

Conclusion

Extended Internal Rate of Return is a method of measuring the annualised return of investments like SIPs that involve irregular cash flows. Since it considers all investments, withdrawals, and their respective investment dates, it accurately reflects the true performance of an investment. You can use an sip calculator XIRR online or the XIRR function in Excel to get a clear picture of your SIP’s returns.