Previously, when we looked at DCF, we discovered that discounting tells us what money received tomorrow is worth or valued at today. Whereas discounting answers the question: “What’s tommorrow’s money worth today?”, IRR (internal rate of return) answers the question: “What’s the effective interest rate?”. In our first example (where we were putting 20m in the bank for 5 years and compounding) we had an interest rate of 1%. In our second example (DCF) we were discounting using an interest rate of 10%.

Discounting algebra

The IRR challenge: what’s the interest rate?

It may help to work through an example. Imagine we are investing 20m. At the end of 5 years we expect our investment to return back 60m. What we want to know is “what’s the effective interest rate?”, or what’s the internal rate of return on that investment.

Some of the numbers being used in this example might be sounding familiar. That’s deliberate! Part of the aim here is to show you how these concepts relate together, and are really re-presentations of the same concepts or information. If you look again at our compounding example, you may be able to guess that the interest rate from the 20m in 60m out investment is likely to be a lot higher than the alternative of putting the money in the bank at 1%.

Solving for IRR using algebra

Like we did when we looked at DCF, a judicious reshuffling of the terms will help us solve for interest rate. This time the formula you need in Excel is “=(60/20)^(1/5)-1”. Try entering that formula in Excel and confirm the answer. Alternatively, you can download the Excel spreadsheet that shows you how to calculate IRR using algebra in financial modelling.

Modelling IRR

Problems with the IRR formula in Excel

Yes, like NPV there is an Excel formula that will help us with this one. But yes, like NPV, the Excel formula for IRR contains a few traps that are easy to fall into. At first, we just want to make sure you understand what IRR is, and that it’s a re-presentation of concepts that we’ve seen elsewhere. In this case, it’s the interest rate that turns a 20m investment into 60m proceeds 5 years later (=the compound annual growth rate for the project).

Continue with the Excel financial modelling course extract

Next we go on to look more closely at the relationship between IRR and NPV, solving for IRR using Excel’s goalseek function. Alternatively, please click to return to the start of the Excel financial maths modelling course extract.