XL: Using NPV() or Solver to Find Guess for IRR()

ID: Q100331

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.x, 3.0

SUMMARY

In Microsoft Excel, if the IRR() function cannot find a solution that is accurate within .00001 percent after 20 iterations, a #NUM! error value is returned. If you specify a guess, the iterations start with that value; if the guess argument is omitted, it is assumed to be 10 percent. If you receive a #NUM! error value and are unsure of what the guess should be, you can use the NPV() function to determine at what interest rate the formula result is closest to zero.

The following methods also work by using XIRR() and XNPV().

MORE INFORMATION

The net present value and the internal rate of return for a given investment are closely related calculations. The internal rate of return is equal to the interest rate at which the net present value is zero. The following formula shows how you can express the relationship between these two calculations with the NPV() and IRR() functions:

NPV(IRR(array),array)=0

In this formula, IRR() may return a #NUM! error value if your array contains very large cash flows combined with cash flows that are very small by comparison. IRR() takes two arguments: array (required) and guess (optional). If IRR() returns a #NUM! error value, you must specify a guess. To narrow the range, you can either substitute random values or you can use NPV().

For example, if you have a table with the following cash flows and IRR() formula


   A1: ($100)
   A2:    $1
   A3:    $1
   A4:    $5
   A5:   $10
   A6: =IRR(A1:A5)

without a guess, the formula in cell A6 returns a #NUM! error value. To determine a close guess, use NPV() either by itself or in conjunction with Microsoft Solver.

NOTE: You can also use NPV() and Microsoft Solver to check the result of your IRR() function if you think it is incorrect.

Using NPV()

The NPV() function takes an interest rate and an array argument. For the array, use the cash flows the IRR() is based on (A1:A5) and for the interest rate, use a range of guessed rates of return. Given the data, it's reasonable to assume that the internal rate of return is negative, so begin with the percentages in B1:B6. The Formula column shows the formula to enter in C1:C6 and the Result column shows what that formula returns.


               Formula                   Result

   ----------------------------------------------


   B1: -50%    C1: =NPV(B1,$A$1:$A$5)    C1: $212
   B2: -40%    C2: =NPV(B2,$A$1:$A$5)    C2:   $8
   B3: -30%    C3: =NPV(B3,$A$1:$A$5)    C3: ($58)
   B4: -20%    C4: =NPV(B4,$A$1:$A$5)    C4: ($79)
   B5: -10%    C5: =NPV(B5,$A$1:$A$5)    C5: ($84)
   B6:   0%    C6: =NPV(B6,$A$1:$A$5)    C6: ($83)

The NPV() goes to zero somewhere between C2 and C3 which use interest rates of -40% and -30% respectively. Since the result in C2 is closer to zero, enter -40% into the IRR() formula in cell A6 so that it resembles the following example:


   =IRR(A1:A5,-.40)

The result of this formula is 39%.

Using NPV() and Microsoft Solver

Rather than enter a range of percentages to compute the NPV(), you can also enter one guess percentage and the formula and then use Microsoft Solver to calculate the result.

For example, use the following data:


   B1: -50%    C1: =NPV(B1,$A$1:$A$5)

To calculate NPV, follow these steps:

On the Tools menu (Formula menu in versions 4.0 and earlier), click Solver.

In the Set Target Cell box, enter $C$1.

In the Equal To box, select the Value Option. In the Of box, enter 0.

In the By Changing Cells box, enter $B$1.

Click Solve.

As with the previous method, Microsoft Solver finds a solution at -39%. This value is displayed in cell B1. You are then given the option to keep or discard the solution.

Checking your Result with Solver

To check the result of your IRR() function, use NPV() with a blank cell as your first argument and the data range you used for your IRR() function as the second argument. For example if you use the following sample data


   A1: -100            B1:      C1: =NPV(B1,$A$1:$A$4)
   A2:   20
   A3:   30
   A4:   75
   A5: =IRR($A$1:$A$4)

the IRR() function returns 10%. To determine if this is correct, click Solver on the Formula menu. In Set Target Cell, enter $C$1; in Equal To, select Value and in Of, enter 0. In By Changing Cells, enter $B$1 and click Solve. Microsoft Solver returns 10% in cell B1. You can then choose to keep or discard the solution.

REFERENCES

"Function Reference," pages 247, 291-292

Additional query words:

Keywords : xlformula
Version : WINDOWS: 2.0, 3.0, 4.0, 4.0a, 5.0; MACINTOSH: 2.0, 3.0, 4.0, 5.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo