Understanding the IRR function in Excel

Say, you have been investing in mutual funds through the systematic investment plan (SIP) route. Now, you want to check what returns you have made. Microsoft (MS) Excel can help you do this with functions that can be found using the command fx and under the category ‘Financial’.

Earlier, we had seen how the RATE function can be used to calculate the return on investments, whether lump-sum or periodic. This can come in handy in the calculation of SIP returns, too. Assume you invested ₹5,000 in a mutual fund SIP at the beginning of each month for 12 months, that is, ₹60,000 in total. At the end of 12 months, the value of the investment is ₹65,000. The annual return in this case is 14.7 per cent.

You can calculate this with the RATE function by filling its five fields — Nper (number of periods - 12), Pmt (periodic investment – 5000 with a minus sign), Pv (present value – 0), Fv (maturity value – 65000), and Type (timing of investment – 1). The formula result 1.23 per cent is the monthly return, which multiplied by 12 gives the annual return of 14.7 per cent.

Easier with IRR

Now, the same result can also be got — more easily — with the IRR (internal rate of return) function. It essentially gives the internal rate of return for a series of cash flows that occur at regular intervals, such as monthly or annually. The IRR is the rate on investment flows consisting of payments (negative values) and income (positive values) that occur at regular periods.

There are two conditions for the IRR function to give the correct results. One, the cash flows should happen at set regular intervals — that is, the time gap between the cash flows must be the same, say monthly, annually, etc. Two, there must be at least one positive value and one negative value to calculate the IRR — that is, there must be at least one payment (investment value) and one receipt (maturity value).

The IRR function has only two arguments or fields.

The first one, ‘Values’ is an array or a reference to cells that contain the numbers for which you want to calculate the internal rate of return. This field has to be filled. The second one, ‘Guess’, is a number that you think is close to the result of IRR; this is an optional field most times and need not be filled always. If Guess is omitted, it is assumed by Excel to be 0.1 (10 per cent).

Here’s how the IRR function works. First, list out in an Excel sheet the series of cash flows in the correct sequence. Make sure to enter the outgo and income values in sequence. That’s because the IRR function uses the order of values in the cells to interpret the order of cash flows. So, in our example, first enter 5000 with a minus sign in 12 cells one after the other – these are the 12 monthly SIP instalments; the minus sign indicates cash outflows. Then, enter 65000 with a positive or no sign – this is the maturity value of the investments.

Monthly SIP

Next, in the IRR function, in the Values field, select the array of cells that contains the values of the payments and receipts for which you want to calculate the IRR. In our example, it is A1:A13. You can leave the Guess field empty or enter a number that you think is close to the result.

That’s it. The formula result is displayed as 1.23 per cent. This is a monthly return since the SIP is on a monthly basis; you can get the annual return — 14.7 per cent — by multiplying the result by 12. Note that the IRR function does not require the dates of cash flows; it assumes that the cash flows happen at set, regular frequencies.

Annual cash flows

Let’s consider another example, in which ₹50,000 is invested at the beginning of each year for five years, and ₹3,00,000 is the maturity value at the end of the fifth year (beginning of the sixth year). The result displayed by the IRR function, 6.14 per cent, is the annual return since the cash flows here are on a yearly basis.

But why use IRR when RATE can do the job just as well? Besides the ease, IRR can be used when the regular cash flows (for instance, periodic monthly investments) are different. In RATE, the periodic investments have to be of the same amount. Say, in the above example, in the year 3 beginning, you invest ₹75,000 instead of the usual ₹50,000. The RATE function cannot handle this calculation, but IRR can.

Annual investment, same amount

The IRR function gives the result (annual return) as 2.92 per cent. So, IRR can be handy when the frequencies of the cash flows (monthly, annually, etc) is the same but the amount of the regular cash flows (SIP investments, etc) is different.

Annual investment, different amount

But what if the frequency of the regular investments itself is different? Say, you make the year 1 investment in March, the year 2 investment in June, the year 3 investment in February and so on. IRR cannot handle this but XIRR can. More on this in the coming weeks.