Think & Done

We discuss MIRR - Modified internal rate of return by showing you MIRR formula and an illustration that shows MIRR calculation with MIRR formula. Here we will have an in depth view of the way Modified Internal Rate of Return or MIRR is used to decide financial viability of an investment.

MIRR Calculator

Here you will find an online MIRR calculator that calculates modified internal rate of return given that you provide the series of cash flows, reinvestment rate and finance rate

MIRR Definition

The regular IRR may not always yield a value and in some cases a solution may not exist for IRR. To overcome this shortcomings of IRR we extend it to define a modified internal rate of return. MIRR value is always unique given that we have at least one negative and one positive net cash flow. The modified internal rate of return is a geometric average of the compounded future value of positive cash flows over the discounted present value of negative cash flows. Here we compound each postive cash flow at the reinvestment rate aka WACC or discount rate to find future value, and we discount each negative cash flow at the finance rate to find the present value. We then find the geometric average of this ratio of net future value over the net present value to come up with MIRR value

MIRR Formula

MIRR Example

Let us show you MIRR Calculation with an example investment proposal. Let us assume we set out on an investment that requires an initial outlay of $100,000 and we expect to receive benefits and incur costs as $40,000 35,000 -20,000 40,000 38,000 40,000. We further assume that our reinvestment rate (WACC or simply the discount rate) is 11% and finance rate is 13%.

MIRR Calculation

I will now show you step by step MIRR calculation for the net cash flows from our example. As you can see we compound each of the positive net cash flows at the reinvestment rate and get a net future value. We also discount each of the negative net cash flows to get a net present value. Finally we find the geometric average of the these two values to get the required MIRR value

Net Cash Flows

CF0 = -100000
CF1 = 40000
CF2 = 35000
CF3 = -20000
CF4 = 40000
CF5 = 38000
CF6 = 40000.

Compounded Net Cash Flows at 11%

CCF1 = 40000 x (1+11%)⁵ = 40000 x 1.68506 = 67402.33
CCF2 = 35000 x (1+11%)⁴ = 35000 x 1.51807 = 53132.46
CCF4 = 40000 x (1+11%)² = 40000 x 1.2321 = 49284
CCF5 = 38000 x (1+11%)¹ = 38000 x 1.11 = 42180
CCF6 = 40000. x (1+11%)⁰ = 40000. x 1 = 40000
FV = 251998.79

Discounted Net Cash Flows at 13%

DCF0 = -100000 / (1+13%)⁰ = -100000 x 1 = -100000
DCF3 = -20000 / (1+13%)³ = -20000 x 1.4429 = -13861
PV = -113861

MIRR Calculation

MIRR = (-FV/PV)^1/n-1-1
MIRR = (-251998.79/-113861)^1/6-1
MIRR = 1.1415735830404 - 1
MIRR = 0.14157358304039
MIRR = 14.16%

MIRR Calculation Online

This online tool will perform MIRR calculation and will display step by step workout similar to one you have seen in this discussion.

Frequently Asked Questions

Here I will attempt to answer some of the frequently asked questions pertaining to modified internal rate of return. This will provide you with an ample material to discern the topic in detail.

Q: What is MIRR in finance?

A: In finance, MIRR or modified internal rate of return is the interest rate that reflects investors return on investment. MIRR is a modified or adjusted form of IRR. IRR has inherent problems such as multiple IRR values or none at all in some cases, to overcome these shortfalls MIRR is used. MIRR assumes that the reinvestment rate from the cash flows is the cost of capital. This is in contrast to IRR's assumption where reinvestment rate is IRR itself.

Q: What is MIRR stand for?

A: MIRR stands for modified internal rate of return, in certain quarters it is referred to as Adjusted internal rate of return or the AIRR.

Q: What is MIRR calculation?

A: MIRR is calculated by first finding the future value of positive cash flows (net benefits) and present value of costs (net costs). The final calculation calculates the geometric mean or geometric average for these future and present values. MIRR calculation uses a closed form MIRR formula that always produces results given that at least one of the cash flows is positive and at least one of the cash flows is negative.

Q: What is MIRR and IRR?

A: MIRR and IRR are two methods used in analysis of investments in capital budgeting analysis. IRR is the internal rate of return at which the resulting net present value is zero. IRR is a popular technique yet in certain cases it may not be possible to calculate IRR and in other cases there may exist multiple IRRs. MIRR is the modified internal rate of return that eliminates the shortcomings of IRR. MIRR is easily calculated by using a closed form mathematical formula unlike the IRR where no such formula exists.

Q: What is MIRR advantages and disadvantages?

A: A key advantage of MIRR is the assumption that cost of capital is used as the reinvestment rate. Another advantage of IRR over MIRR is the fact that one can always calculate modified internal rate of return given a basic presumption. This is unlike the IRR which may not always be calculable. A disadvantage of MIRR may be that in most cases a MIRR is less than IRR thus it may not reflect the true worthiness of an investment.

Q: What is MIRR finance rate?

A: The MIRR finance rate refers to interest rate an organization has to pay in order to fund the pending project. It is the cost of raising the funds needed to undertake the investment. In most cases the finance rate and reinvestment rate is the same when calculating MIRR.

Q: What is MIRR in Excel?

A: Excel is a popular spreadsheet program that provides ample features for financial analysis. Excel has built in functions that automate and help in calculating various financial formulas and ratios. One such Excel function is it's MIRR function that accepts a series of cash flows along with finance rate and reinvestment rate to calculate and display the results of modified internal rate of return. The results from MIRR function in Excel can be used for other calculations in the worksheet.

Q: What does MIRR do?

A: MIRR helps an organization determines its rate of return from an investment. MIRR is a handy and useful method for analyzing investments when an organization knows it's finance rate and reinvestment rate. MIRR can be compared with company's cost of capital to determine the health of the investment.

Q: What does MIRR measure?

A: MIRR measures the return an organization expects from undertaking a capital budgeting projects. MIRR measures the profitability of an investment by showing the percentage returns from such an investment.

Q: Why MIRR is better than IRR?

A: The main reason MIRR is better than IRR is the fact that one is always able to calculate MIRR whereas IRR may not be calculable in a number of cases. MIRR's assumption of cost of capital as reinvestment rate gives it advantage over the IRR.

Q: Can MIRR exceed IRR?

A: It is usually observed that MIRR is lower than IRR. It would be rather rare if MIRR were to exceed IRR.

Q: Can Mirr be negative?

A: Yes it is possible that MIRR turns out to be negative. This may happen when the net costs exceed net benefits.

Related DCF analysis methods

Following is a list of related readings that cover other 5 commonly used DCF analysis methods

1. Internal Rate of Return
2. Net Present Value
3. Profitability index
4. Payback period
5. Discounted payback period