HomeJAGRUK INVESTORBest Method To Calculate SIP Returns | IRR Vs XIRR Vs CAGR

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.

upstox

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.

IRR 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

TIMELINEMONTHLY SIP
1 JANUARY 2020-1000
1 FEBRUARY 2020-1000
1 MARCH 2020-1000
1 APRIL 2020-1000
1 MAY 2020-1000
1 JUNE 2020-1000
1 JULY 2020-1000
1 AUGUST 2020-1000
1 SEPTEMBER 2020-1000
1 OCTOBER 2020-1000
1 NOVEMBER 2020-1000
1 DECEMBER 2020-1000
1 JANUARY 202113000

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

TIMELINEMONTHLY SIP
1 JANUARY 2020-100000
1 JANUARY 2021-100000
1 JANUARY 2022-100000
1 JANUARY 2023-100000
1 JANUARY 2024-100000
1 JANUARY 2025-100000
1 JANUARY 2026-100000
1 JANUARY 20270
1 JANUARY 20280
1 JANUARY 20290
1 JANUARY 20300
1 JANUARY 20310
1 JANUARY 20320
1 JANUARY 20330
1 JANUARY 20340
1 JANUARY 20351200000

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

TIMELINEMONTHLY SIP
1 JANUARY 2020-100000
1 JANUARY 2021-1000
1 JANUARY 2022-18000
1 JANUARY 2023-120000
1 JANUARY 2024-100000
1 JANUARY 20250
1 JANUARY 2026-59000
1 JANUARY 202710000
1 JANUARY 20280
1 JANUARY 2029-20000
1 JANUARY 20300
1 JANUARY 20310
1 JANUARY 203240000
1 JANUARY 20330
1 JANUARY 20340
1 JANUARY 20352000000

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

IRR XIRR

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!

Related Blogs

spot_img

Financial Advisor

Follow Us

72,000FollowersFollow
1,860,000SubscribersSubscribe

Jagruk Investor

Jagruk Employees

A salaried employee is someone who receives a fixed amount of pay/salary for the number of hours worked. A salaried person may get their...

Don't Miss

Recent Comments