Microsoft Office Tutorials and References

In Depth Information

**Multiple Rates of IRR and the MIRR Function**

In column C, formulas calculate the present value. They use the IRR (calculated in cell B17) as the

discount rate, and use the period number (in column A) for the
nper.
For example, the formula in

cell C6 is

=PV($B$17,A6,0,–B6)

The sum of the values in column C is 0, which verifies that the IRR calculation is accurate.

The formulas in column D use the discount rate (in cell D3) to calculate the present values. For

example, the formula in cell D6 is

=PV($D$3,A6,0,–B6)

The sum of the values in column D is equal to the NPV.

For serious applications of NPV and IRR functions, it is an excellent idea to use this type of

cross-checking.

Multiple Rates of IRR and the MIRR Function

In standard cash flows, there is only one sign change: from negative to positive, or from positive

to negative. However, there are cash flows in which the sign can change more than once. In those

cases, it is possible that more than one IRR can exist.

Multiple IRRs

Figure 12-15 shows an example that has two IRR calculations, each of which uses a different

“seed” value for the guess argument. As you can see, the formula produces different results.

Figure 12-15:
The same cash flows can have multiple IRRs.