Microsoft Office Tutorials and References

In Depth Information

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

Figure 12-16:
Multiple internal rates of return.

You can replicate the MIRR algorithm by setting up a revised flow, which compares the two NPVs

(refer to Figure 12-16, columns C:E). The negative flow NPV is placed at period 0, and the positive

flow is expressed as its equivalent future value (by accumulating it at the deposit rate) at the end

of the investment term. The IRR of the revised flow is the same as the MIRR of the original

(source) flow.

This example reveals that the methodology is suspect. In separating negative and positive flows,

the MIRR implies that interest is charged on flows. Banks, of course, charge interest on balances.

An attempt at resolving the problem is shown in the next example.

Using balances instead of flows

The MIRR function uses two rates: one for negative flows and one for positive flows. In reality,

interest rates are charged on
balances
and not on flows. The example in this section applies

different rates on negative and positive balances. The interest calculation uses an IF function to

determine which rate to use.

When analyzing a project in which interest is paid and received, the end balance must be zero. If it

is greater than zero, you have actually received more than the stated deposit rate. If it is less than

zero, you still owe money, and the finance rate has been underestimated. This example assumes a

fixed finance rate and calculates the deposit rate needed to secure a zero final balance.

In the Risk Rate Equivalent IRR method, the finance rate is fixed (at 9% in this example). The

interest received on positive balances is found by using the Data

➜

Data Tools

➜

What-If

Analysis

Goal Seek command. In this example (see Figure 12-17), cell D21 was set to zero by

changing cell C6.

➜