Microsoft Office Tutorials and References

In Depth Information

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

You can find the workbook with all of the examples in this section,
multiple irr.

xlsx
, on the companion CD-ROM.

The IRR formula in cell B21 (which returns a result of 13.88%) is

=IRR(B7:B16,B3)

The IRR formula in cell B22 (which returns a result of 7.04%) is

=IRR(B7:B16,B4)

So which rate is correct? Unfortunately,
both
are correct. Figure 12-15 shows the interest and

running balance calculations for both of these IRR calculations. Both show that the investor can pay

and receive either rate of interest, and can secure a (definitional) final balance of $0. Interestingly,

the total interest received ($1,875) is also the same.

But there’s a flaw. This example illustrates a worst-case scenario of the practical fallacy of many

IRR calculations. NPV and IRR analyses make two assumptions:

h
You can actually get the assumed (for NPV) or calculated (for IRR) interest on the

outstanding balance.

h
Interest does not vary according to whether the running balance is positive or negative.

The first assumption may or may not be correct. It’s possible that balances could be reinvested.

However, in forward-projections in times of changing interest rates, this might not be the case.

The real problem is with the second assumption. Banks simply do not charge the same rate for

borrowing that they pay for deposits.

Separating flows

The MIRR function attempts to resolve this multiple rate of return problem. The example in this

section demonstrates the use of the MIRR function.

Figure 12-16 shows a worksheet that uses the same data as in the previous example. Rates are

provided for borrowing (cell B3) and for deposits (cell B4). These are used as arguments for the

MIRR function (cell B19), and the result is 6.1279%:

=MIRR(B7:B16,B3,B4)

The MIRR function works by separating negative and positive flows, and discounting them at the

appropriate rate — the finance rate for negative flows and the deposit rate for positive flows.