MIRR in financial models

MIRR in financial models

MIRR in financial models is potentially a better way to determine the return on an investment than the typical IRR. It stands for the Modified Internal Rate of Return and its actual workings are better explained in Wikipedia.

In our simplistic terms it allows you to understand the returns on an investment when the cash in and outflows are put into another investment (IRR assumes that the money goes back into a similar investment- so if the current project has a 50% IRR it assumes you can find another 50% project to invest in, which in same cases is unlikely).

Excel has a function called MIRR which follows all the normal requirements. There is just one thing you need to be aware of when using MIRR in Excel and that is any 0’s (zeros) after the investment period and the impact they have on the answer.

If you are building a financial model and playing with the term of the investment, the model may make the cash flows in the post investment period zero which for IRR and XIRR are no problem. However, MIRR treats it as a period and continues calculating.

As shown below, we have a 5 months investment and we get an IRR of 14% and a MIRR of 9% which seems to make sense.

MIRR-in-financial-modelsHowever, if we delete the zeros in months 6 to 12 as shown below, the IRR stays the same (which  makes sense) but the MIRR goes up to 13% which is a vastly different number.

MIRR-in-financial-modelsThe issue here is that when the zeros are shown, MIRR keeps reinvesting the money at 9% and financing at 6% which means that over time it should converge to the re investment rate. If this is what you intended (an evaluation period of 12 months) then it is not a problem. However, if you want to compare the two over the same period then you need to make sure there are no unnecessary zero’s at the end.

Unfortunately it still happens if you replace the zeros with blanks, dashes or other text.

The best way to get it to work is to use an OFFSET to explicitly specify the number of cells to include otherwise you may get the incorrect numbers.