• Aman Jain

Basic Excel Functions you should know #4 (IRR)

Hey, welcome to the second post of the Investment function series. If you haven’t read the first post; here it is. Continuing where we left, excel provides us a various function which we use to evaluate investment decisions. While NPV was helpful to find the Net present value of different investments, IRR is used to calculate the Rate of Return.

In simpler words, the IRR is a discount rate that makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis, which means IRR is the bare minimum you should be earning from your investment to not lose money. IRR is also useful in comparing Insurance policies to understand how much is it that you are actually earning (Note: IRR should be greater than the current inflation rate to ensure you are making money on your investment). Simply speaking, higher the IRR, better the investment, and can be used to rank multiple prospective investments or projects on a relatively even basis.

Formula – “=IRR(series of cash flow)

As usual, I have saved the file here, so you can work along as you learn :)

Let’s take an example:

Money Back at Maturity

  • Here, we have assumed an insurance policy where we pay an annual premium for a few years (in our case it is 20 years)

  • The total investment across 20 years is (35,000*20 – ₹7,00,000)

  • We get a Maturity of ₹12,00,000

  • Note that the outflow is negative, and inflow is positive

  • Now, in cell, B23, we will use the formula to calculate the IRR

  • That’s it. You get IRR of 4.89% (Not a good return if you ask me)

Let’s take another type of insurance policy:

Here, we have assumed the insurance policy as in the last example where we pay an annual premium for a few years (in our case it is 20 years). However, in this policy, we get some money back every 4th year as below:

Due to the money back scheme, we adjust it towards the premium payments, which is why you can see that every 4th year we see a net inflow of money

We get a Maturity of ₹12,00,000 (the money back every 4th year plus the money at maturity)

Again, in cell, B23, we will use the formula to calculate the IRR, you get IRR of 6.21%

  • This is a huge improvement over the previous policy

  • There is no difference in the maturity amount or the investment amount. The only difference is the timing of the cash flows (and that makes all the difference)

What about a Pension Plan?

Now, we also have some interesting pension plans where you invest a fixed annual premium for a few years (say 10) and from 21st year for the next 30 years you will get back the premium amount every year. What do you think? Is this a good investment? Use the IRR function to find out :D


©2020 by IndexMatched. Proudly created with