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).

An issue with Excel’s MIRR

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.


However, 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.


The 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.

Finding errors in NPV and IRR Calculations

Financial models generally make use of NPV and IRR functions. But NPV in particular has an issue that users regularly misunderstand and as a result the NPV can be more than 10% wrong. Have a look at the risk of NPV calculations.

Time Value of Money in Excel online course

Auditing Financial models