MIRR Formula

In the previous post we discussed various excel formulas to calculate IRR, and we know for sure that the modified internal rate of return (MIRR) is a better performance indicator than the internal rate of return (IRR).

But why don't we always calculate MIRR or use Excel MIRR formula, rather than still using IRR as a performance indicator? Do you calculate MIRR? Why or why not?

I have rarely seen any analyst using Excel MIRR formula. From investment banking to project finance, we always use IRR.

Internal rate of return (IRR) has some inherent problems and modified internal rate of return (MIRR) was invented to solve these issues. By definition, MIRR is a modification of the internal rate of return (IRR) and aims to resolve the following issues with the IRR calculation:

• IRR assumes that interim positive cash flows are reinvested at the same rate of return as that of the project that generated them. This is usually an unrealistic scenario and a more likely situation is that the funds will be reinvested at a different rate.
• Using IRR function, more than one IRR can be found for projects with alternating positive and negative cash flows. MIRR formula solves this issue and finds only one value.

This sounds amazing! We solved a major financial problem J. But still why we don't calculate MIRR or use Excel MIRR formula?

Ok, let's look at the syntax of the Excel MIRR function:

=MIRR(values, finance_rate, reinvest_rate)

Where values() is the array of cash flow values. Finance_rate is the interest rate you pay on the money used in the cash flows or say it is your cost of capital. Reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.

MIRR calculation involves two rates - finance rate, which is your weighted average cost of capital and reinvest rate. Ok, we got it. These two rates are the reason for us not using MIRR formula. Calculation of weighted average cost of capital involves estimating the cost of equity, which is very tricky and sometimes get bogged down in discussion.

The other rate, refinance rate, is also confusing. Because we don't know at what rate we may be able to invest the future cash flows. Assuming refinance rate as bank deposit rate or risk-free rate can be an option. But it is never correct; we don't know what will be reinvest rate for future cash flows.

So, we simply ignore the first problem associated with IRR. An the second problem - multiple IRRs for projects with alternating positive and negative cash flows - can be easily solved by bringing the problematic cash flow one period prior (by finding present value) or one period later (by finding the future value). Ok, ok, yes we will be using discount rate for this. First of all, since it involves only one or two cash flows so it doesn't matter much. Secondly, there is no other solution.

Also, for the same reason we tend to use IRR more often as a performance indicator than NPV. Net present value calculation involves discount rate, and estimating discount rate (or cost of capital) accurately is very difficult. We will discuss this in some other post why it is difficult.

What do you think, how often do you calculate modified internal rate of return (MIRR)? Use the comment section below.

Check out more useful articles on Our Blog Page

Are you someone involved with real estate feasibility?

We are excited to launch the next generation of real estate feasibility software by April 2023.

claim your 15% early-bird discount

Know more about the product features

Learn More