Microsoft Office Tutorials and References

In Depth Information

**Using the IRR Function**

following formula (in cell C25) returns $10,923.24, which is the payment amount that would

substitute for the varying payment amounts in column B:

=PMT(C5,C4,–B23,0,C6)

Figure 12-11:
Calculating equivalent payments with NPV.

Using the IRR Function

Excel’s IRR function returns the discount rate that makes the NPV of an investment zero. In other

words, the IRR function is a special-case NPV.

The syntax of the IRR function is

IRR(range,guess)

The range argument must contain values. Empty cells are not treated as zero. If the

range contains empty cells or text, the cells are ignored.

In most cases, the IRR can be calculated only by iteration. The guess argument, if supplied, acts

as a “seed” for the iteration process. It has been found that a guess of –90% will almost always

produce an answer. Other guesses, such as 0, usually (but not always) produce an answer.