Calculating IRR using algebra
In the last section we saw that IRR is the discount rate that sets NPV to zero. Another way of thinking about IRR is as the interest rate that turns the cash I put into an investment into the cash I get out.
For example, imagine I am investing 20m and expect to extract 60m cash out in 5 years’ time. Compounding up the 20m by the investment’s IRR is going to turn it into 60m. So 20 x (1+IRR)5 = 60m. By using a bit of algebra, and re-shuffling the terms, I could solve for the interest rate. That means, for a simple cash in cash out, we could use a simple formula in Excel to calculate IRR. In this case, the formula we would want to enter in Excel is “=(60/20)^(1/5)-1”. Please try entering the formula in Excel if you are unsure. It should give you exactly the same answer as the one you got when you used goal seek. That’s not a coincidence.
If you are still unsure how to calculate IRR using this formula, you can find the worked answers in the “Basic IRR calcs” tab in your spreadsheet. It was one of the ones you needed to unhide.
To recap so far though, as well as being the discount rate that sets NPV to zero, another way of thinking about IRR is as the compound annual return for the project.
Return to the advanced financial modelling course page
Please click to return to the advanced financial modelling course page.
Back to top