14. Excel Tips - IRR - internal rate of return

Nick's picture

I am adding this as I looked at the search results, and noticed that people had searched for IRR, and sadly they didn't find anything useful.

Here's a noddy's guide to IRR.

IRR is a function that takes a series of cash flows and works out the effective rate of return if they were to be annually invested.

Let's take a look.
Here's an example of an IRR function call:


I think IRR is easiest explained with a bond example
In this example, there are 4 cash flows
- at time 0 (today), I pay 100 for a bond (a negative number means you pay out money... positive means receive)
- at years 1 and 2, the bond pays me 10
- at maturity, it pays back the 100, and also 10.
... The technical name for this is: a 3 year 10% coupon bond trading at par, but don't worry too much abt that.

If you insert an IRR function call and input the cash flows, it returns 10%. This is the internal rate of return on the bond cash flows.

Now, let's look into this a bit deeper.
We'll extract the cash flows, invest them at each point, and grow them at 10% each year. If we sum them at the end, they sum to 0.

SO, another way to look at IRR is that it is the rate at which these cash flows invested sum to 0 at the end.


IRR Spreadsheet

As a side note, the last thing is that you will notice that the IRR function call has 2 arguments. The 2nd argument is a guess. The reason for this is that there's no mathematical way of solving this equation, so a numerical way is used. The only difference this makes for a sensible set of cash flows is in the speed of the calculation. The closer you are with your guess, the faster the calc. A normal user is unlikely to notice the difference.
To demonstrate this, in my example sheet, on the "Explained" tab.. select cell g5. Then go: Tools => Goal Seek
You want to set g5 = 0, by changing g2
That's: "Sum of invested cash flows" = 0, by changing "Investment Rate"


... the difference is small, and due to Excel giving up before it finds the right solution.. it thinks that 6 decimal places is good enough.

Hope that helps !


IRR training Video:

IRR.xls18 KB