The tutorial explains the basics of the modified internal rate of return, in what way it is different from IRR, and how to calculate MIRR in Excel.
For many years, finance experts and textbooks have warned about the flaws and deficiencies of the internal rate of return, but many executives keep using it for assessing capital projects. Do they enjoy living on the edge or simply are not aware of the existence of MIRR? Though not perfect, the modified internal rate of return resolves two main issues with IRR and provides a more realistic evaluation of a project. So, please meet the Excel MIRR function, which is our star guest today!
The modified internal rate of return (MIRR) is a financial metric to estimate the profitability of a project and rank equally sized investments. As its name suggests, MIRR is a modified version of the traditional internal rate of return that aims to overcome some deficiencies of IRR.
Technically, MIRR is the rate of return at which the net present value (NPV) of terminal inflows is equal to the investment (i.e. outflow); whereas IRR is the rate that makes the NPV zero.
IRR implies that all positive cash flows are reinvested at the project's own rate of return while MIRR allows you to specify a different reinvestment rate for future cash flows. For more information, please see MIRR vs. IRR.
How do you interpret the rate returned by MIRR? As with IRR, the bigger the better :) In situation when the modified internal rate of return is the only criterion, the decision rule is very simple: a project can be accepted if its MIRR is greater than the cost of capital (hurdle rate) and rejected if the rate is lower than the cost of capital.
The MIRR function in Excel calculates the modified internal rate of return for a series of cash flows that occur at regular intervals.
The syntax of the MIRR function is as follows:
The MIRR function is available in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.
Before you go to calculate modified IRR in your Excel worksheets, here's a list of useful points to remember:
Calculating MIRR in Excel is very straightforward – you just put the cash flows, cost of borrowing and reinvestment rate in the corresponding arguments.
As an example, let's find the modified IRR for a series of cash flows in A2:A8, finance rate in D1, and reinvest rate in D2. The formula is as simple as this:
Tip. If the result is displayed as a decimal number, set the Percentage format to the formula cell.
To quickly evaluate different projects of unequal size, let us create a MIRR template. Here's how:
Where Sheet1 is the name of your worksheet and A2 is the initial investment (first cash flow).
Name the above formula as you like, say Values.
For the detailed steps, please see How to make a dynamic named range in Excel.
For this example, I've created the following names:
So, our MIRR formula takes this shape:
=MIRR(Values, Finance_rate, Reinvest_rate)
And now, you can type any number of values in column A, beginning in cell A2, and your MIRR calculator with a dynamic formula will immediately give a result:
While the theoretical basis of MIRR is still disputed among finance academics, generally it is considered a more valid alternative to IRR. If you are not sure which method produces more accurate results, as a compromise you can calculate both, keeping in mind the following limitations.
Though IRR is a commonly accepted measure of an investment's attractiveness, it has several inherent problems. And MIRR solves two of them:
The Excel IRR function works under the assumption that interim cash flows are reinvested at the rate of return equal to the IRR itself. The catch is that in real life, firstly, a reinvestment rate tends to be lower than a finance rate and closer to the company's cost of capital and, secondly, the discount rate may change substantially over time. As the result, IRR often gives an overly optimistic view on the project's potential.
MIRR more accurately reflects the profitability of the investment because it considers both the finance and reinvest rate and allows you to change the anticipated rate of return from stage to stage in a long-term project.
In event of alternating positive and negative values (i.e. if a series of cash flows changes sign more than once), IRR can give multiple solutions for the same project, which leads to uncertainty and confusion. MIRR is designed to find only one value, eliminating the problem with multiple IRRs.
Some finance experts consider the rate of return produced by MIRR less reliable because a project's earnings are not always fully reinvested. However, you can easily make up for partial investments by adjusting the reinvest rate. For example, if you expect the reinvestments to earn 6%, but only half of the cash flows are likely to be reinvested, use the reinvest_rate of 3%.
If your Excel MIRR formula results in an error, there are the two main points to check:
That's how to use MIRR in Excel to find the modified rate of return. For practice, you are welcome to download our sample workbook to Calculating MIRR in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents