IRR in a nutshell

I think of internal rate of return (IRR) as the interest rate that will turn the amount of money I have today into the money I will get out in the future.

IRR is the interest rate that turns my money today into the money I expect out tomorrow.

Imagine putting money in the bank

Imagine you had ￡100 to put in the bank for five years at a (whopping) 1% interest rate. Your Finance 101 programme and compounding and maybe even Excel would tell you that your ￡100 today is going to turn into ￡100 x (1+1%) ^ 5 = ￡105.1 (wow).

Mark's got a deal for you

Instead of putting your money in the bank at a measly interest rate, imagine that today I've got a deal for you. If you invest ￡100 in my great deal, I'm promising that you will triple your money in five years.

I am sure you can guess that the interest rate that will turn ￡100 into ￡300 in five years is going to be quite a bit higher than the 1% we were hoping for from the bank. If we can work out the answer to that conundrum then we will have?calculated the IRR (equals the Compound Annual Growth Rate or CAGR that transforms my money in into my money out).

Solving for IRR

Yes there's a formula in Excel that will do all the heavy lifting for you, but imagine we could dredge up a bit of alegbra from college and really wanted to understand this IRR thing a bit better.

If we rearranged the same maths that we used a minute ago in compounding we could solve for the interest rate and find the IRR on the triple-your-money deal:

￡100 x (1+X%) ^ 5 = ￡300

(1 x X%) ^ 5 = 300/100

(1+X%) = (300/100) ^ (1/5)

X% = (300/100) ^ (1/5) -1

Plugging the formula into Excel we find out that the triple-your-money deal has the astronomically high interest rate of 24.6%.

If you want to check you got the maths right, do some compounding like before and you should end up right back where you started.

Pitfalls in Excel's IRR formula

Yes Excel has a formula that's supposed to do all this for you, but if you use the regular IRR formula, you'll have to make sure you set it up correctly. You'll need to be careful to:

• enter the first cash flow as a negative number (the negative sign 'tells' Excel that your first cash flow is a cash in rather than a cash out)
• enter zeros in the periods where there is no cash flow (otherwise Excel will assume the cash flows that you have slotted in happen straight after each other)
• do something else when your cash flows are anything other than annual e.g. quarterly or semi-annual (Excel's IRR formula assumes the cash flows are annual)
• remember to use six columns when your project is running for example for five years.

Top tip: always use the XIRR formula

Now that you understand what IRR is (the interest rate that turns my money today into my money tomorrow), and you've remembered that you don't much like algebra, and you've been warned about Excel's regular IRR formula, here's my top tip: always use XIRR.

XIRR avoids all the problems of Excel's standard IRR formula. All you have to do is assign dates to the cash flows (easy if you get in the habit of running dates across the top of your models) and point XIRR to both the dates and the cash flows.

Using XIRR?it doesn't matter:

• if you enter zero values in the sequence
• if you are working with irregular non-annual cash flows
• how many columns you use.

XIRR?will be able to cope!

Summary: IRR in a nutshell

To summarise:

• IRR is an interest rate. What you're working out is the interest rate that will turn the money you put in into the money you get out
• When you're calculating IRR in Excel always use the XIRR formula.

Mastering Excel financial modelling with just 10 formulas

Is it possible to master Excel modelling in as few as 10 formulas? Maybe. Almost.