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.

For MIRR calculation, why not use the cost of debt for both the finance rate and reinvestment rate? It makes sense because rather than reinvesting, you can always pay off debt and save the interest.

Interesting because you had the chutzpah to disagree with McKinsey and Co. I hope you’re right because I went with it.

http://www.mckinsey.com/insights/corporate_finance/internal_rate_of_return_a_cautionary_tale

The MIRR function acutally accumulates all interim cash flows into the last period of the stream of cash flows. It then performs a computation of the present value of those cash flows as if they will all be received at that point in time. Thus, it appears the function misrepresents and understates the expected rate of return.

Talking about Excel MIRR, If finance rate is your “cost of capital”, or the interest paid to cover negative cash flows, how come the MIRR gets higher when you raise the finance rate?

Instinctively, the higher the interest you pay, the lower the return is.