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.