Determining IRR using Excel’s goal seek
IRR, like NPV, is a measure of a project’s expected return (IRR stands for “internal rate of return”), expressed as an annualised % rate of return.
The difference between IRR and NPV is that NPV tells us something about the size of the project. An investor might prefer a first project that delivers a high NPV (i.e. delivers high absolute value) over a second project with very high IRR where the second project is very small. Alternatively, an investor might still be concerned about a large project that has high NPV but an IRR only just above the cost of funds. It wouldn’t take much of a drop in cash flows for the project’s value to be destroyed.
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)”. Yes, like the NPV formula there are some traps, so we’ll look at a couple of other ways of calculating IRR before we use the formula.
Calculating IRR in an Excel model
Have a look at the Excel spreadsheet again. Where can you see an IRR being calculated?
Have a look at exercise 9. Imagine this project were 5 years long, instead of 3. That is, the 60m return happened in year 5. Extend the table so that you are modelling a 5 year project with no returns until year 5, when you get back the 60.
Now slowly change the discount rate at cell B132 until the NPV = 0.
- What’s the IRR? Click here if you are unsure of the answer.
If you are having trouble, or can’t resist temptation, there’s a worked answer hidden in the spreadsheet. If you are running Excel 2003 go “format”, “sheet”, and “unhide” the “Basic IRR calcs” tab. If you are running Excel 2007, on the home tab go “cells”, “format”, “visibility”, “unhide & hide”, “unhide sheet” and unhide the “Basic IRR calcs” tab. By going through the exercise above, hopefully we have illustrated that we can calculate IRR by finding the discount rate that makes NPV = 0.
Using Excel’s goal seek to iterate
If you have used “goal seek” in Excel before, please skip this section.
Iterating or trying different values for the discount rate in cell B132 takes time. Fortunately Excel gives us a wonderful shortcut in “goal seek”. Goal seek is a tool that can automate the process of trying different values for the discount rate until NPV = 0.
In Excel 2003 go “tools”, “goal seek”. Then enter these values. “Set cell” = the cell with NPV in, B139. “To value” = 0. “By changing cell” = the cell with the discount rate in, B132. In Excel 2007 you can find goal seek on the “data” tab, under “data tools”, “what if analysis”.
Very quickly goal seek finds the answer for us. Goal seek is a great help in scenario analysis. For example, if we had a full model, we could use goal seek to run a sensitivity on project revenues. We could iterate to see how far project revenues could drop before NPV were zero and/ or key banking ratios were breached. For example “set cell” = the cell with say the NPV calculation in, or the key banking ratio. “To value” = 0 in the case of NPV or whatever limit set by the bank. “By changing cell” = the cell with say the revenue growth assumption in. By looking at the model we could judge how far revenues would have to drop before NPV was zero or a key ratio were breached.
Return to the advanced financial modelling course page
Please click to return to the advanced financial modelling course page.
Back to top