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
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
Goal Seek command. In this example (see Figure 12-17), cell D21 was set to zero by
changing cell C6.