Determining IRR using Excel’s goal seek

When we looked at NPV we looked at its use as an investment appraisal tool: someone evaluating opportunities would be inclined to invest in projects that generate a positive NPV. Similarly, someone evaluating projects would be inclined to invest in a project that generated an IRR in excess of the cost of funds (10% in our earlier example). The 20m in 60m out project generates positive NPV (NPV = 17.3) and an IRR greater than our 10% cost of funds (IRR = 24.6%), so the opportunity is looking attractive.

IRR vs. NPV

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 that project’s value to be destroyed.

IRR is the discount rate that makes NPV = 0

With the numbers that we’ve been running so far, and the discussion above, you may already be appreciating that NPV and IRR are intimately related. The cash flows from a positive NPV project (37.3m), discounted at the cost of funds (10%), are higher than the initial cost of investment (20m).  A positive NPV project (17.3) generates an IRR greater than the cost of funds (10%). To illustrate how closely related NPV and IRR are, open the spreadsheet: IRR using goalseek.

Now slowly increase the discount rate at cell B12 until the NPV = 0 (24.6% should do it!). By going through the exercise above, hopefully we have illustrated that you 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 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 2007 and 2010 you can find goal seek on the “data” tab, under “data tools”, “what if analysis”. In this example your “set cell” is B17 (NPV). At “to value” you should enter zero. The “changing cell” is B12, the cell with the discount rate in it. click on “OK”.

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.  “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.

Continue the Excel financial modelling course extract

Next we look at pitfalls in using Excel’s IRR formula. Alternatively, please click to return to the start of the Excel financial maths modelling course page.