XIRR: The Key To Calculating Your Investment Returns

Investing involves numbers. And some mathematics. Before you say “I am not good with numbers” and decide that “investing is not for me”, I urge you to read on.

You only need to know about a few numbers to be a successful investor and XIRR is one of them.

What is XIRR and how is it useful?

XIRR is Extended Internal Rate of Return. For most practical purposes, XIRR is the rate of return that is used to compare investments with a bank fixed deposit.

Consider two cases:

• If you invested INR 1200 into a bank fixed deposit today and the bank promises you 10% return per annum. At the end of one year you receive INR 1320 (i.e. 1200 principal + 120 as 10% simple interest).
• You also invested INR 100 into a mutual fund every month, which at the end of one year has grown to INR 1320 in total.

Which investment gave more returns? In absolute terms, they both returned the same. You have an absolute gain of INR 120 in both cases.

However, in the second case with the mutual fund, only the first investment of INR 100 stayed invested for a year. The second INR 100 stayed invested for 11 months, the third investment of INR 100 stayed invested for 10 months and so on.

To compare these two investments, you need to calculate the XIRR for the mutual fund investment. It will come to just about 19%. That’s way better than the 10% returns in the fixed deposit.

Note: Comparing an investment against a bank fixed deposit is not an apples to apples comparison. In the case of the bank deposit, you know what you are going to receive before you part with your money. It’s relatively safe and the chances of you losing your investment or your returns is negligible as long you you choose a reputed and recognized bank. In the case of a mutual fund, the risk is much higher which may or may not convert into higher returns. Every instrument (except ULIPs maybe) serve a purpose.

How to calculate XIRR?

Open up your favorite spreadsheet tool like Microsoft Excel or Google sheets. We will use Google sheets for this example since it’s free and everyone can access it.

• Create a column named Dates. You will enter the date on which you made the investments here and all dates on which you made a withdrawal.
• Create another column named Amount. You will enter the amount you invested as -ve (as having left your pocket) and the amount you withdrew as positive (as having entered your pocket).
• If you have not made a withdrawal, enter the current value of the investment as the last row.

Now to calculate XIRR, enter this formula in a different cell:

= XIRR( cashflow_amounts, cashflow_dates ) x 100

where cashflow_amounts is the range of cells in the Amount column and cashflow_dates is the range of cells in the Date column.

We will use our previous example of investing INR 100 each month to calculate it’s XIRR.

The result is 18.98%, a tad below 19%.

You have just unlocked a superpower! Now when your neighborhood insurance aunty tries to sell you her shiny new policy in which if you invest INR 100000 / year for ‘just’ 10 years will return INR 100000 each year for the next 15 years, use this superpower to calculate the XIRR. Tell her at 3.34% annual returns (which is the XIRR) the ULIP is worse than a savings bank account!

And the fact that your fund is locked with them for 25 years is simply insult to the injury. XIRR is the truly the hero we need.

Hello, I am Arun Basil Lal. Thank you for reading!

I am a WordPress product developer and creator of Image Attributes Pro. I am passionate about solving problems and travelling the world.