# Modified Internal Rate of Return, MIRR

Definition

The modified internal rate of return (MIRR) is a modification of the internal rate of return (IRR) and is used in capital budgeting as a ranking criterion for mutually exclusive projects. The idea behind the MIRR method is that all project cash outflows are discounted at the cost of capital, and all cash inflows are reinvested at the reinvestment rate.

Formula

In general terms, the equation of MIRR can be written as follows:

where N is the number of years (periods) of investment, COFt is the cash outflow for a relevant year (period) t, r is the cost of capital (discount rate), CIF is the cash inflow for a relevant year (period) t, and k is the reinvestment rate.

The left side of the equation is the present value of all a project’s cash outflows (the sing “-” is needed because cash outflows have negative value). The numerator in the right side of the formula is the future value (also called terminal value) of a project’s cash inflows. So the equation above can be modified as shown below.

Finally, the formula for the modified internal rate of return is as follows:

Decision Rule

If the MIRR is the only screening criterion of a project, the decision rule is rather straightforward. The project should be accepted if the modified internal rate of return is greater than the cost of capital.

Problems occur in case of ranking mutually exclusive projects, especially if they are of different size. In such a scenario, the net present value (NPV) should be used as a ranking criterion, and the MIRR can be used as a supplemental criterion measuring project sensitivity to changes in cost of capital and reinvestment rates.

The modified internal rate of return resolves two problems inherent to the IRR.

1. All cash inflows are reinvested at the reinvestment rate, which is more realistic than reinvesting at the IRR.
2. The method of calculation eliminates the problem of multiple IRR for projects with abnormal cash flows.

The main disadvantage of the MIRR method is the potential conflict with the NPV method. The reason may be due to a difference in project scale or in the timing of cash flows (the problem was discussed in “NPV vs IRR method”). Furthermore, if the reinvestment rate is lower than the cost of capital, there is a conflict with the basic assumption of the NPV method, which is that all expected cash inflows are reinvested at the cost of capital (discount rate). Thus, the project can simultaneously have positive NPV and MIRR lower than the cost of capital. That is the reason why some academic studies recommend using the reinvestment rate equal to the cost of capital raised for a project.

Example

Company N is considering two mutually exclusive projects. The cost of capital is 12%, and the expected reinvestment rate is 10%. Detailed information about expected cash flows is presented in the table below.

Please note that Project Z has abnormal cash flows, so we have the multiple IRR problem.

To solve the equation above, we need to calculate the present value (PVCOF) of all cash outflows and the future (terminal) value of all cash inflows (TV) for both projects.

TVProject Y = 12,000(1+0.1)3 + 10,500(1+0.1)2 + 9,000(1+0.1)1 + 8,500(1+0.1)0 = \$47,077K

TVProject Z = 11,000(1+0.1)4 + 9,000(1+0.1)3 + 7,500(1+0.1)2 + 6,000(1+0.1)1 = \$43,759K

Schematically, cash inflows and outflows of both projects are presented in the figure below.

Thus, the MIRR of Project Y is 13.99% and 13.89% for Project Z.

Both projects have the MIRR greater than the cost of capital, so they could be accepted if they were independent. Because they are mutually exclusive, Project Y should be accepted, and Project Z should be rejected. On the other hand, the NPV of Project Y is \$3,119K and \$3,310 for Project Z. Thus, there is a conflict between the NPV and the MIRR method. In such cases, it is recommended that the NPV be used as a single screening criterion, so Project Z should then be accepted. We should note, though, that Project Z is more sensitive to change in the cost of capital and the reinvestment rate than Project Y.

MIRR in Excel

The modified internal rate of return can be also calculated in Excel as in the example below.

1. Select output cell H7.
2. Click fx button, select All category, and select MIRR function from the list.
3. In field Values, select the data range B7:G7.
4. In field Finance_rate, select cell B1.
5. In field Reinvest_rate, select cell B2, and press OK button.

So we have the same MIRR of Project Y of 13.99% and 13.89% for Project Z.

Calculator

Alternatively, the MIRR can be calculated using our online calculator.