You often hear that the return from investment in terms of IRR or XIRR. Do you know what these terms are? What is the difference between these two terms? How to calculate these two functions using Excel sheet? Let us see in detail about these two calculating methods.

**What is IRR (Internal Rate of Return)?**

Let us say you are an irregular monthly investor. Like in the first month, you invest Rs.5, 000 and next month Rs.3, 000 but again on third month Rs.6, 000 and so on (but on the same date of each month). Then at the end of a period how you calculate your return on investment? A typical CAGR formula will not work here. Because even though your monthly investment is on the same date like a typical Mutual Fund monthly SIP, but the investment amount varies each month. In this case, we can use the IRR calculator of Excel Sheet.

For example, Mr.X invested Rs.1, 00,000 at the beginning of a year. Next year on the same date he will invest another Rs.50, 000. On third year same date, he withdraws Rs.60, 000. On fourth year, he withdraws another Rs.20, 000. Finally, on the fifth year (same date) he withdraws Rs.2, 10,000. Then how much return, he got from all these transactions?

Before proceeding further to calculate, please note few points to calculate IRR.

- If your investment is beginning of a period then directly you can enter values. However, suppose in case your investment is at the end of the period, then first values must be ZERO. Because, by default IRR formula considers your investment or withdrawal at the beginning of a period.
- Anything that goes out from your pocket is considered as +ve value and if you withdraw and get into your pocket is considered as -ve value.
- There must be at least at least one investment and withdrawal required to calculate IRR.
- Last value must be the total amount you receive.
- In IRR formula payments or receivables by default are considered as annual. If your investments are monthly, then either you need to multiply it by 12 using the formula (1 + Monthly-IRR)^12 – 1 or use XIRR function. It applies to quarterly or half-yearly payments also.
- Interval of investment must be equal like monthly, quarterly, half yearly or yearly but on the same date.

In below image will show you how to calculate the IRR using Excel sheet. I took the example of Mr.X to calculate IRR return.

Hope there is no confusion now to calculate the IRR.

**What is XIRR?**

In IRR calculation we calculated return on investment only when there is equal distance of either investments or withdrawal like yearly, half yearly, quarterly or monthly (same date). What if there are withdrawals or investments with irregular dates along with irregular withdrawals or investments?

In this scenario, XIRR will help you to arrive at a return on your investment. I will take the same example of Mr.X but with irregular time intervals by mentioning the dates. Notice in below image that for calculating XIRR, I mentioned the dates and formula asks for date values.

So do remember that your investment or withdrawal period is equal and yearly then use IRR function. If not, then it is always best to use XIRR, even for equal investment or withdrawal periods other than yearly (I mean not to confuse yourself by multiplying by 12 for monthly investment). Because XIRR will not confuse you. Only additional data you need to enter is to have date values.

Hope above two functions of Excel will solve your doubts on how to calculate return on your investment with ease.

Very useful article Basavaraj . I have some doubt . In what situation I will calculate IRR , XIRR , CAGR ? My situation is follows . I invested 24000 yearly for 15 years and I will get maturity value after 21 yrs . In this situation how to calculate return ?

OR

My goal is 25 Lakh. I invest 7000 monthly for 10 yrs through SIP then I will stop SIP and wait for getting my excepted

value and lets say value of my investment will become 25 lakh after 17 yrs and then I will redeem all unit from that fund . In this case how to calculate return ?

Anirban-CAGR best to use when you have a constant stream of interest income like Bank FDs. Use IRR when you have fluctuating returns like Mutual Fund or Stock Investments. Same way XIRR is used when you have investment and withdrawal in different dates.

Nice article basavaraj. I just noticed that signs in amounts in your examples are inverted. Right? If yes then it might confuse some

Sumit-Inverted in the sense?

Hi Basu

Wish ” A Happy New Year to You and Your Family”

I want an advice from you

1) I do not have saving till now and I had lost much money on a few personal family constrains

2) I am 36 years age old – Recently started 2 SIP one for 2k PM and other 1K PM

Planning to invest in PPF. 5000 p

Please advise and guide me for the best investment and for bigger returns as the investment started with huge delay in my life

Thanks in Advance and waiting for your reply. My email id [email protected]

Sreenivas-Thanks for your wish and same for you. What do you mean by BEST investment and BIGGER returns? Also why you have more allocation towards PPF?

Awesome info article

Thanks Sharing with us

Sachin-Pleasure 🙂

Dear Mr. Basu,

I am new to your blog, and also to the world of investments. I found your blog very enlightening and would firstly like to thank you for the same.

I needed some guidance from you-

I am 26 with an annual income of 2.7L and just married. I am currently investing 50k annually in PPF and also have health insurance of Rs. 2L covered under family floater scheme by my husband (he is also new to investments and has been investing 4k monthly to kotak MF and 6k quarterly towards Jeevan Saral plan). So I was planning to invest further an amount of Rs. 4k monthly, can you advise as to should I be investing in MF (If yes, then which would be advisable considering that I am a newbie) or start investing in equity through online portals like sharekhan.com (ofcourse taking investment inputs from friends/relatives), or any other option which you may suggest?

Looking forward to your valuable guidance.

Thanks

Jigna

Jigna-First understand the basic things you need to do immediately. Buy yourself and for your husband a term insurance to the tune of around 15-20 times of your yearly income. Increase your health insurance a bit further. Because 2 lakh will vanish within a day if in bad case of any surgery. Buy accidental insurance for your both separately. Create an emergency fund of at least of 6-12 months household expenses. Once all these are in place then proceed for investing. By the way Rs.50,000 in PPF for what purpose? Try to come out dummy products like called Jeevan Saral.

Dear Mr. Basu,

Thanks for your response.

I am investing in PPF as per the advice of my dad, as I can get a bulk amount (safely) with a decent interest after 15 years. Went for Jeevan Saral, as my hubby was advised for it some 2 years back as being a good option. Are their any better alternatives to it? Also what would be the deduction in case we withdraw from that scheme?

Also, is it advisable in my case to take services from an insurance agent for term policy, as taking his/her services would increase my premium? Which would be a good option for me?

Thank you very much for your time!

Regards

Jigna

Jigna-PPF is good. But parking all your money into such low yielding product for long term goals is not good. Instead keep some % into it. Jeevan Saral is a crap product. So try to come out of it once completion of 5 years. You can buy term insurance online. They will be cheaper than agent mode. Now your agent may be helpful for you. But what guarantee that he will be in same profession for rest of his life and will assist you in your need? Keep learning yourself and try to manage the things on your own. This will be best in long run and no one will cheat you easily.

Thank you very much for the guidance, Mr. Basu!

Will do the requisite research….

Which plan can be a good and effective alternate to Jeevan saral after we bail out of it after 5 years.

Jigna-It again depends on your goal. So no blindfold investment or product choice 🙂

Thanks. Very Clear. and the video was descriptive and simple and elegant.

Lakshmipathy-Pleasure 🙂

Very useful article….Thanks

Abhishek-Pleasure 🙂