How to Calculate SIP Returns Using XIRR
Mutual funds are now the popular choice of many investors looking to accumulate wealth in the long term. While multiple options are available to estimate mutual fund returns, you must know which option will give you accurate results. One of the important metrics used in calculating the return of the mutual fund SIP is XIRR (Extended Internal Rate of Return).
When you invest a lumpsum amount in any mutual fund scheme, you can use CAGR to estimate the return, as there is no regular investment or series of investments. But when you choose to invest through SIP, the popular option for mutual fund investment, it includes regular payments on a particular date.
In this case, using CAGR is not an appropriate way to calculate the return, as it ignores the timing of investments and considers only first and last values. So, in such cases, You can use XIRR to calculate the return for your SIP investments. It factors in the timing of investment to calculate your SIP return. In this blog, we will learn more about the XIRR, its formula, and its calculation.
XIRR’s formula takes into account different cash inflows and outflows. As per the XIRR formula, the annual average return of each installment is calculated and then they are adjusted to give you the overall average annual rate of return for all your investments.
It helps you to estimate the return on your SIP investments, wherein you make a regular investment in any mutual fund scheme. Also, if you are choosing SWP (Systematic Withdrawal Plan) option, you can use XIRR to estimate your overall return. The SWP enables you to make regular withdrawals of a predetermined amount at fixed intervals.
Let’s understand how XIRR is accurate with an example. Suppose you have started a monthly SIP of Rs. 10,000 in a mutual fund scheme and continued the monthly SIP for 5 years. Let us also assume that after a lot of ups and downs, your total investment value grew to Rs. 8.84 lakh by the end of 5 years. In this example, your first Rs. 10,000 installments have been invested for 5 years or 60 months.
This first month’s installment’s annual return will be different because it was invested for the most number of months. In other words, as each installment remains invested for a different time period, their respective CAGR also varies.
If you see the CAGR of each of these installments of a mutual fund scheme, it will be quite complicated to comprehend and analyze its performance. Thus, to make things simple, all these CAGRs are taken together and adjusted to a common CAGR. And this adjusted CAGR is shown as the XIRR of a mutual fund scheme.
You can also calculate XIRR in MS Excel using the XIRR function. For this, you must enter the transaction amount and its dates. XIRR function in Excel: =XIRR(Values, Dates, [guess]) Here, Values refer to the total amount you have invested, which also includes any redemption, if any.
For every SIP installment, the value should be written in negative. The value of the investment on which the date XIRR is being calculated amount should be positive. Dates refer to the date of investment. As far as the Guess part is concerned, you can leave it blank.
Step-by-step process of calculating XIRR in Excel:
Step 1: Enter the series of investments done on different dates in sequence in one column.
Step 2: Enter the corresponding date of each transaction in another column.
Step 3: In the last row, enter the current value of your investment, along with the current date.
Step 4: Now, use the XIRR function in any other row. ( =XIRR(Values, Dates, [guess]))
Step 5: After writing =XIRR, select all values or transactions, add a comma, select all dates, and click on enter.
Let’s take an example to understand how XIRR is calculated in Excel:
Assume you start a SIP of Rs 10,000 on January 10, 2023, and continue to invest the same amount throughout the year on the same date. And, as of 10, December 2023, your maturity amount is Rs. 1,30,000.
In this case, you can calculate the XIRR of your SIPs as shown below:
In the above table, the cash flows happen at irregular intervals.
Here, you can use the XIRR function to calculate the return for these cash flows. Do not forget to include the ‘minus’ sign whenever you invest money.
Follow these steps after opening an Excel sheet:
In column A, add the transaction dates. In column B, input the SIP amount of 10000 as a negative amount because it’s an outflow cash flow.
Against the redemption date (Column A), enter the redemption amount (Column B) (1,30,000). In the cell below 1,30,000, enter the following formula: “=XIRR (B1: B14, A1: A14)*100” and hit enter You can easily calculate the XIRR, you can simply head to our SIP calculator.