Excel Tip: Calculating the internal rate of return for a project

Last week I introduced the PMT function for calculating payments on a loan.

This is one of the many functions under the Financial heading in Excel. This week I want to introduce you to another. One that allows you to calculate the internal rate of return (IRR) for a series of cash flows.

The IRR is a method often used to compare alternative projects or investments. and is stated as a percentage return, within a specified period.

As with the PMT function, it is worth starting by stating the assumptions used in the calculation. The IRR function requires the cash flows to be at regular intervals and the return percentage it calculates is  for the length of that interval. So, for example, if the cash flows fed to the function are annual cash flows, then the IRR returned will be a percentage per annum.

Also, the cash flows involved must include both positive and negative cash flows. If you think about this is self-evident, as any return percentage would be infinite if there was no outlay and all cash flows were positive!

Typically this will involve an initial outflow (or investment) followed by a series of projected inflows.

For example, a business investment opportunity might be presented as follows:

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

We might want to compare it to another project and a useful piece of informationin doing this would be to calculate the IRR for each project.

So, how do we do it.

The IRR function syntax is as follows:

=IRR(values,[guess])

Most of the time you won't need the optional [guess] argument, but I will come to that in a minute.

values is the range of cells that show the cash flows in chronological order.

So, for our example we could enter the cash flows in cells B2 to B7 as follows:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

Our function would then be:

=IRR(B2:B7)

which returns an IRR of 14% (rounded to zero decimal places). This means that over this 5 year period, the project has an internal rate of return of 14% per annum.

The [guess] argument is there because Excel uses an iterative process to calculate IRR and will give up if it has not arrived at it after 20 iterations. To do this it must start with an estimate. If the [guess] argument is omitted, an estimate of 10% (or 0.1) is used. For most projects this will work with no problems, however with cash flows that fluctuate wildly and/or have an unusually high or low return, it may not get there in the first 20 iterations, if it starts at 10%.

If this happens it will return a #NUM! error. You can then add a guess to the function so that it starts closer to the true answer and therefore can get there within the 20 iterations.

Next week we will look at calculating Net Present Value, which is the flipside of IRR.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".