How to Use XIRR While Calculating SIP Returns

August 24, 2022 Mutual Fund

In mutual funds, there are two investment options you can either park a lumpsum amount in a scheme or go for Systematic Investment Plan i.e. the SIP route. Regardless of the path you take, you should have an estimate of the returns you could expect from your mutual fund investments a few years from now. By calculating your returns, you may easily determine if you are progressing toward your financial goals. Mutual fund returns can be computed in a variety of ways, including CAGR, XIRR, absolute returns, etc.

Now, when you opt for a lumpsum investment, you invest in units at a single net asset value (NAV). Compounded Annualised Growth Rate (CAGR) is usually used to calculate returns on your lump sum investment if the holding period is more than one year and simple absolute return for less than one year. However, CAGR is not relevant if there are multiple investments in a scheme over a period.

In case of mutual fund SIPs, there are multiple investments each done on a different date and at fluctuating NAVs. It is relatively more complex to calculate SIP returns due to the series of instalments and changing NAVs associated with each instalment. Hence, there are two ways to calculate the effective return – either calculate the CAGR of each investment or apply the XIRR (Extended Internal Rate of Return) formula.

Many of you invest in mutual funds via SIPs, which is among the most sought-after ways to invest in mutual funds today and are regarded as one of the best options to take advantage of rupee cost averaging and power of compounding. But while calculating SIP returns XIRR is the ideal method. Thus, it is important to understand What is XIRR, How to calculate XIRR in mutual funds.

What is XIRR in mutual funds?

XIRR is an Extended Internal Rate of Return. It is a method used to calculate returns on investments where there are multiple transactions taking place at different points in time. XIRR helps you to evaluate the actual returns on your investment. It factors all the aspects of every SIP instalment, including the instalment amounts, the instalment dates, the investment maturity date, the cash inflows, and outflows.

If you’re investing in mutual funds by SIP or lumpsum or redeeming via SWP, XIRR can handle all these scenarios and assist you in computing a consolidated return by accounting for the date of all your transactions, both buy and sell. When there are multiple inflows and outflows, XIRR is far superior to other methods of computing returns. In XIRR, the CAGR of each instalment is calculated, and then they are added together to give you the overall Compounded Annual Growth Rate.

How to Calculate XIRR in mutual funds?

XIRR simplifies the process of calculating returns, even if the investments are conducted at irregular investments. You can calculate the returns using an excel sheet wherein the XIRR function allows the flexibility to allot specific dates to individual cash flows and calculates annualised returns.

The XIRR formula calculates the annual average return of each SIP instalment to give you the total average annual return on investments. To determine the investment’s present value, XIRR discounts the cash flows based on the timing of the flows. Early cash flows throughout the investment period are discounted more, but later ones are discounted less. This is because the value of money diminishes over time.

Step-by-step process to calculate XIRR in MS Excel:

Let’s take an example here of a 12-months SIP to calculate XIRR in mutual funds:

• SIP amount = Rs 5,000
• SIP investment dates: Start on 01-04-2021, End on 01-03-2022
• Date of redemption: 31-03-2022

In the excel sheet, every cash outflow is denoted with a minus sign while inflows are positive numbers.

Step #1 – After opening the excel sheet, in column A, enter the transaction dates. In column B, enter the SIP amount as a negative figure (cash outflow). Make sure the dates are on the left side, while the Amount is on the right side.

Step #2 – Against the redemption date (Column A), enter the redemption amount (Column B). If you haven’t redeemed, you may use the current date and redemption amount equal to the amount arrived by multiplying the current NAV and units you hold. Do not prefix a minus sign here, since it will be an inflow of cash as you redeem your units.

(For illustration purposes only) 

Step #3 – In the box below type in ‘=XIRR’ which will ask you to fill in three parameters: Values, Dates, and Guess.

Values – select the cells with SIP amount and market value

Dates – select the cells with SIP dates and the date at which you are calculating the returns.

Leave the Guess field blank and click on the OK button.

(For illustration purposes only) 

XIRR will help you calculate the actual return on your investments in a better way. Even if you have an ongoing monthly SIP investment of Rs 5,000 and invest a lump sum intermittently, you can calculate the returns using the same XIRR formula. Also, if you redeem certain units in between on any date, the XIRR will help you find the actual return as on the date.

To conclude…

XIRR is an excellent tool for calculating real-world investment returns. To accurately assess the growth of your investments, it is critical for investors to calculate returns using the appropriate formula. Thus, as demonstrated by the example above, XIRR is a simple and helpful method for calculating your SIP returns.

XIRR is the most comprehensive way of determining your returns in case of multiple transactions and it allows you to compute your potential mutual fund Investment returns, especially for irregular SIP investments. Investment in mutual funds helps you attain, your vital goals such as a child’s higher education, their wedding expenses, your retirement needs, etc. To begin your investment journey you may choose from a variety of mutual funds – equity, hybrid, and debt funds.

Therefore, choosing the best mutual funds for your portfolio is just as important as choosing the correct approach for computing returns. Considering the current market volatility, selecting the best suitable mutual fund scheme that aligns with your financial goals, investment horizon, and risk-taking capacity will be a prudent choice.

This article first appeared on PersonalFN here