What is MIRR?
The Modified Internal Rate of Return (MIRR) is an Excel function that accounts for the cost of capital and reinvestment rate of the cash flows from a project or company.
- 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?
Table of Contents
MIRR Excel Formula
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
The formula for calculating the MIRR is shown below.
- 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.
Note that the initial outlay must be entered as a negative number in Excel.
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
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 Excel Template
We can now move on to a modeling exercise to calculate the MIRR of a project.
To get started, fill out the form below to access the Excel file.
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:
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.