The IRR formula in Excel

Yes there’s a simple formula in Excel that can be used to calculate IRR, with the format “=IRR (value of cash flow 1, value 2, value 3, etc)” but you are already suspicious about the traps in using the formula, of which more shortly.

To help understand some of the pitfalls, why don’t you try building a formula to calculate IRR for our 20-in-60-out-in-5-years-deal?  Set up a very small table with the cash flows in it. To calculate IRR enter “=IRR(” in a cell and point the formula to the cash flows.  Alternatively, click on the “fx” button on your taskbar and select IRR from the list of functions – that will also help you construct the formula.

Here are some of the ways that you could set the formula up.  Only one way yields the correct answer.  Which one of the methods is correct?  Try building the tables below in your own spreadsheet and see which results in what we already know (from our previous work calculating IRR using algebra and goal seek) is the correct answer: 24.6%?

Excel financial modelling: the IRR formula

If you have been having some trouble (and, if it’s the first time you’ve seen this, it’s likely you will need some help), download the IRR formula Excel spreadsheet.  If you have been able to build some of the tables above, and have thought about why some of them don’t yield the correct of 24.6%, hopefully you will have become aware of some of the pitfalls of using the IRR formula.

Financial modelling traps no.s 2&3

When using the IRR formula in financial modelling you must enter 0 (zero) values for years where you have no cash flow.  Otherwise Excel will not recognise that a year has passed (see C above, which yields an incorrect IRR).

Where you have a five year project you will need to use six columns for your IRR formula (see (A) above, which is wrong).

Continue with the Excel financial modelling course extract

There’s another major trap with the IRR formula yet so don’t relax too much!  Next we look at the benefits of using a different Excel formula in financial modelling: XIRR. Alternatively, please click to return to the start of the Excel financial maths modelling course page.