Wall Street Prep

Modified Internal Rate of Return (MIRR)

Guide to Understanding the MIRR Excel Function

Learn Online Now

Modified Internal Rate of Return (MIRR)

In This Article
  • What is the definition of the modified internal rate of return (MIRR)?
  • How can the MIRR be calculated in Excel?
  • What is the difference between the MIRR and IRR Excel function?
  • How can MIRR be used for capital budgeting decisions?

How to Calculate IRR with the MIRR Excel Function

MIRR stands for the “modified internal rate of return” and attempts to measure the potential profitability (and returns) from undertaking a project or investment.

As implied by the name, the MIRR Excel function is different from the traditional IRR function in that:

  • Positive Cash Flows are Reinvested at the Reinvestment Rate
  • Negative Cash Flows (i.e. the Initial Outlay) are Discounted at the Financing Rate

MIRR Formula

The modified internal rate of return (MIRR) formula in Excel is as follows.

Modified Internal Rate of Return (MIRR) Formula
  • MIRR Excel Function = “MIRR(values, finance_rate, reinvest_rate)”

The inputs in the MIRR formula are as follows:

  • values: The array or range of cells with the value of the cash flows, including the initial outflow.
  • finance_rate: The cost of borrowing (i.e. interest rate) to fund the project or investment.
  • reinvest_rate: The compounding rate of return at which positive cash flows are assumed to be reinvested.

The initial outlay must be entered as a negative number in Excel for the formula to work properly.

Interpreting MIRR vs. Cost of Capital

For purposes of capital budgeting, the following rules are generally followed:

  • If MIRR > Cost of Capital ➝ Accept Project
  • If MIRR < Cost of Capital ➝ Reject Project

When comparing numerous projects, the one with the highest MIRR is likely the one to pick, especially if other metrics also lead to the same conclusion.

MIRR vs. IRR Excel Function

The issue with the IRR Excel function is the implicit assumption that future positive cash flows are reinvested at the project or company’s cost of capital (i.e. required rate of return).

Critics of the IRR function argue that the assumption that the reinvestment rate is equal to the cost of capital overstates the return on the project or investment.

The reinvestment rate and cost of capital are often different in reality, so MIRR provides the option to specify a different reinvestment rate for future cash flows.

In effect, the MIRR Excel function is considered a more conservative measure compared to the IRR function (and usually results in a lower return).

Drawbacks to MIRR Excel Function

One limitation to the MIRR Excel function is that it assumes 100% of cash flows are reinvested into the project/company, which is not always the case.

Hypothetically, one could adjust the reinvestment rate and financing rate for each progressive stage, but doing so brings up the issue of uncertainty around the timing of specific actions.

In other words, attempting to precisely model out the reinvestment rate, financing rate, and cost of capital for each period does not necessarily add more accuracy to the analysis due to future uncertainty.

The IRR Excel function remains frequently used due to its simplicity, however.

MIRR Calculator – Excel Template

We’ll now move to a modeling exercise, which you can access by filling out the form below.

dl
Submitting ...

MIRR Calculation Example

In our example scenario, we will be assuming a project has an initial cost of $1 million.

After the initial cash outlay during the initial period (Year 0), the project is projected to produce the following cash flow amounts each year:

  • Year 0: –$1m
  • Year 1: $50k
  • Year 2: $100k
  • Year 3: $400k
  • Year 4: $500k
  • Year 5: $600k

As for the financing rate and reinvestment rate, we’ll assume the following:

  • Financing Rate: 10%
  • Reinvestment Rate: 12.5%

The greater the difference between the financing rate and reinvesting rate, the more that the IRR and MIRR will diverge from each other.

If we enter the provided assumptions into the MIRR formula, we get 12.5% as the MIRR.

The MIRR formula entered for our Excel model is shown below:

MIRR Excel Formula

Conversely, if we had used the IRR function, the resulting IRR is 14%, which demonstrates how MIRR is viewed as a more conservative measure.

But again, whether MIRR is more “accurate” or not is dependent on the amount of information on hand and the rationale behind the associated assumptions.

MIRR Excel Calculation

Turbo-charge your time in Excel Used at top investment banks, Wall Street Prep's Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.
Learn More

Comments
guest
0 Comments
Inline Feedbacks
View all comments
Learn Excel Online

Become an Excel “Power User.” Used to train new hires at top financial institutions around the world!

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.