Best Method To Calculate SIP Returns | IRR Vs XIRR Vs CAGR

Many investors are unaware of how to accurately calculate the return on their investments. Although these investors may be investing in multiple instruments for a long time. Not knowing the correct return calculation will only mislead you into not making informed decisions. Moreover, this misinformation can cause you to invest in instruments that may actually not be worth your money. Thus, any investment will go through multiple ups and downs to reach the final top value. The key is to hold onto the investment for as long as possible to create maximum wealth and lower risk. And the correct method to calculate their returns is with IRR or XIRR.

Issue with SIP

Most people have investments via SIP. These SIP are deducted automatically from the bank account on a set date every month. A number of mutual fund units are bought against that SIP which varies every month as per the NAV.

For example, person A invest Rs 10,000 as SIP on the first of every month for 1 year. On 31st December the total investment is Rs 1.20 lakh and the total value of the investment is Rs 1.50 lakh. Now, if A calculates the return on his investment as –

(30,000/1,20,000) X 100 = 25% —> Then this is called Absolute Return and it is an incorrect method of SIP return calculation. Since this only calculates the total profit on the total investment.

The correct method of SIP return calculation will take time into account as well. Since the entire Rs 1.20 lakh was not invested for the full one year. The January Rs 10,000 was invested for one year, the February Rs 10,000 was invested for 11 months, and so on. Likewise, the December Rs 10,000 was only invested for one month. To know the correct method of SIP return calculation, we cannot take CAGR into account since there as well multiple investments are not considered. Hence the best way for SIP return calculation is IRR or XIRR.

What is IRR?

IRR stands for Internal Rate of Return. Whenever there are multiple investments, then the annualized return of the entire investment journey is known as IRR. Similarly, there is XIRR which stands for Extended Internal Rate of Return. There is just a minimal difference in the calculation formula for both.

It is important to note that both IRR and CAGR are calculated as annual returns.

IRR Calculation Method with Excel

Example 1 – Monthly SIP mutual funds plan

In the above example, Rs 1000 is paid as SIP on the first of every month for a whole year. On 1st January 2021, when one withdraws this investment, its total value is Rs 13,000. Hence, total investment is Rs 12,000 and total profit is Rs 1000.

The Absolute Return is 8.33%. But this is an incorrect SIP return calculation method since all investments were not invested for the same duration.

Hence, we will calculate the XIRR on an excel as follows:

XIRR Value calculation on excel = XIRR(select range of all the values of investment from 1st to last withdrawal),(select all the time periods) x 100

In this case, XIRR = 15.7%

This XIRR value can be used to compare the return from different investment instruments.

Note: The dates on excel sheet must always be entered as D/M/YYYY format to ensure correct results.

Example 2 – Yearly endowment/money back plan/guaranteed plan

In the above example, a lumpsum premium of Rs 1 lakh is paid on the first of January every year from 2020 to 2026. For the next 8 years, one may get nothing and finally get Rs 12 lakh at the end of maturity period.

Hence, we will calculate the XIRR on an excel as follows:

XIRR Value calculation on excel = XIRR(select range of all the values of investment from 1st to last withdrawal),(select all the time periods) x 100

In this case, XIRR = 4.56%

XIRR Vs IRR

In the above examples we calculated XIRR on excel. But if we were to calculate IRR on the same excel, then the formula will be

IRR Value calculation on excel = IRR(select range of all the values of investment from 1st to last withdrawal)

Here, we will not take the time duration into account or multiply by 100, as in the case of XIRR calculation. That is because, the excel formula automatically assumes that all investments input have been done annually. Thus, IRR works for all annual investment patterns. But it is recommended to use XIRR as it takes the time duration clearly into account. Additionally, it is important to fill all cells in case of IRR calculation else the result calculation will be incorrect. This is not necessary in the case of XIRR calculation on excel.

Example 3 – Periodic and irregular investments

In the above example, the investment has been done at equal intervals which is annually in this case but can be monthly as well. However, the amount of investment are irregular and differ each year. Here, there have been varying investments between 2020 and 2024. There was no investment in 2025. In 2027 there was Rs 10,000 withdrawal and in 2032 there was Rs 40,000 withdrawal. Finally, in 2035 there was total investment journey withdrawal of Rs 20 lakh.

Thus, XIRR = 14.13%

Example 4 – Irregular period and irregular investments

In the example above, all outflow amount are given in negative and inflow amounts in positive. The time duration of investment and amounts, both are irregular. Here too, the same XIRR formula will be used to give the value of 9.91%.

IRR Calculation Method without Excel

Any investment app which you use, be it Kuvera, Groww, etc., will automatically show you the XIRR or IRR against every investment instrument. The XIRR value shown here is on the basis of an assumption and will change daily. The assumption being that you will withdraw all the investment money on the day of checking this figure.

Learn more about XIRR and IRR in the video below.

Join the LLA telegram group for frequent updates and documents.
Download the telegram group and search ‘Labour Law Advisor’ or follow the link – t.me/JoinLLA
It’s FREE!