The Modified Internal Rate of Return (MIRR) is a function in Excel that takes into account the financing cost (cost of capital) and a reinvestment rate for cash flows from a project or company over the investment’s time horizon.
The standard Internal Rate of Return (IRR) assumes that all cash flows received from an investment are reinvested at the same rate. The Modified Internal Rate of Return (MIRR) allows you to set a different reinvestment rate for cash flows received. Additionally, MIRR arrives at a single solution for any series of cash flows, while IRR can have two solutions for a series of cash flows that alternate between negative and positive.
As you can see in the image above, there is a major difference in the return calculated by MIRR and IRR in Project #2. In project #1, there is no difference.
Let’s break down the reasons why.
MIRR Project #1
In Project #1, essentially all cash flow is received at the end of the project, so the reinvestment rate is irrelevant.
It’s important to show this case to clearly illustrate that reinvestment doesn’t matter when a project only has one final cash flow. Examples would be a zero-coupon bond or a Leveraged Buyout (LBO) where all cash flow is used to service debt until the company is sold for one large lump sum.
MIRR Project #2
In Project #2, by contrast, essentially all of the cash flow is received in year one. This means that the reinvestment rate is going to play a big role in the overall IRR of the project.
Since we set the reinvestment rate for MIRR to 0%, we can make an extreme example to illustrate the point.
The life of the investment is 7 years, so let’s look at what each result is saying.
MIRR is saying that, if you invested $1,000 at 8% for 7 years you would have $1,756 by the end of the project. If you sum up the cash flows in the example, you get $1,756, so this is correct.
So, why is the IRR result of 75% saying? Clearly, it’s not saying that if you invested $1,000 at 8% for 7 years you would have $50,524.
Recall that IRR is the discount rate that sets the Net Present Value (NPV) of an investment to zero. So, what the IRR case is saying is simply that discounting the $1,750 cash flow in year one needs to be discounted by 75% to arrive at an NPV of $0.
Which is Better, IRR or MIRR?
The answer is that it depends on what you’re trying to show and what the takeaway is. It can be helpful to look at both cases and play with different reinvestment rates in the MIRR scenario.
One thing that can be definitively said is that MIRR offers more control and is more precise.
When it comes to financial modeling, and specifically in private equity and investment banking, the standard IRR function is common practice. The reason for this is that transactions are looked at in isolation and not with the effect of then another investment assumption layered in.
MIRR requires an additional assumption, which could make two different transactions less comparable.
The Downside of Using Modified Internal Rate of Return
There are some downsides to using MIRR, the main one being the added complexity of making additional assumptions about what rate funds will be reinvested at. Additionally, it is not nearly as widely used as traditional IRR, so it will require more socializing, buy-in, and explaining at most corporations, banks, accounting firms, and institutions.
We hope this has been a useful guide to the Modified Internal Rate of Return MIRR, how to use it in Excel, and what the pros and cons of it are.
For more learning and development, we highly recommend the following additional CFI resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.