Resources

What does MIRR mean? It’s a metric that reveals the return you earn, or are hoping to earn, on a project or investment. Unsurprisingly, modified internal rate of return is closely related to a more familiar number, internal rate of return. Accordingly, in this article we’ll explain both metrics and specify the MIRR formula. Then we’ll dive into how to calculate MIRR, including how to use an MIRR calculator or two. Next, we’ll move on how to calculate MIRR in Excel. Finally, we’ll end with frequently asked questions about modified internal rate of return.

This article joins a growing list of articles about financial statistics published by Assets America^® that includes:

• How to Use the Loan-to-Cost Ratio
• How to Calculate Net Income and NOI
• Effective Gross Income | Investor’s Guide (+ Calculator)
• Debt Yield — Everything Investors Need to Know
• Cap Rate Simplified (+ Calculator)

What Does MIRR Mean?

MIRR stands for modified internal rate of return. Specifically, it is how much money you earn on a project as a percentage of the amount invested. As you’d expect, modified internal rate of return employs the time value of money. That is, modified internal rate of return uses present and future values in its calculation. Importantly, this allows you to compare the modified internal rate of returns from different projects that have different timelines. As its name implies, MIRR is a modified version of the standard internal rate of return (IRR) formula.

However, it differs in one crucial way. That is, modified internal rate of return assumes that you reinvest positive cash inflows at your cost of capital. Furthermore, it supposes that you finance the initial outlays at your organization’s financing cost. Conversely, IRR assumes that you reinvest cash inflows at the IRR rate. Therefore, modified internal rate of return more precisely reflects a project’s profitability and cost.

Apply For Financing

What MIRR Reveals

You use modified internal rate of return to rank projects or investments, regardless of size. Specifically, you assign the highest rank to the project with the highest percentage return on your investment. Then you work your way down from best to worst. You want to use MIRR instead of IRR to solve two problems:

1. Multiple Solutions: IRR can produce more than one solution for the same project. However, you get only one solution with modified internal rate of return.
2. Reinvested Cash Flows: IRR is impractical because it presumes that you reinvest positive cash flows at the IRR. But MIRR more precisely reflects reality by using the actual reinvestment rate of cash flows.

By using modified internal rate of return, you can alter the assumed reinvestment growth rate for each project stage. Normally, you use the average estimated cost of capital, although there is plenty of wiggle room for other rates.

MIRR Limitations

While modified internal rate of return improves upon IRR, it has a few limitations:

1. Dependent on Assumptions: You estimate cost of capital when making decisions with modified internal rate of return. However, you might make subjective assumptions when calculating cost of capital. Unfortunately, this can lead to varying values for MIRR.
2. Not Always Optimal: Modified internal rate of return might not maximize value when you are deciding among several investments simultaneously. Specifically, this can occur if your company must ration capital expenditures. Also, net present value (NPV) can give you better selections when your project choices are mutually exclusive.
3. Clarity: Many folks who are not financial experts might not fully understand how MIRR works and why it’s important.
4. Backbiting: Academics often dispute the theory behind modified internal rate of return.

Nonetheless, businesspeople rely on modified internal rate of return to help make decisions, despite its supposed drawbacks.

MIRR Formula

Modified internal rate of return uses several concepts and terms.

Concepts Used Within MIRR Formula

MIRR encompasses these important concepts:

1. Future Value (FV): This is a calculation method you use to determine how much cash flows will be worth in the future. Moreover, it assumes some rate of interest, either simple or compound, on the cash.
2. Present Value (PV): This specifies how much future cash flows would earn or cost assuming a specific rate of return. Specifically, this is the discount rate, and the higher it is, the lower the present value.
3. n^th Root: When you use this number and multiply it by itself n times, you get the original value. For example, when n is three, the 3^rd root of 8 is 2, because 2 x 2 x 2 = 8. That is, 8 is the original number and the third root of 8 is 2. Clearly, 2 is also the 4^th root of 16 and the 5^th root of 32.
4. Cash Flows: A cash flow is a series of payments or receipts of cash at specified times or intervals. To no one’s surprise, cash inflows are cash flows you receive from the project. As you’d expect, cash outflows are cash flows you pay for the project or investment.
5. Cost of Capital: This metric tells you how much you must spend to obtain financing for a project or investment. Normally, you have different costs for equity and debt financing. Together, they form the weighted average cost of capital for the organization.

MIRR Formula Terms

The modified internal rate of return formula contains the following terms:

1. n: This is the number of periods for the project or investment cash flows.
2. FV_ci: This is the sum of future values of all cash inflows through the end of the project or investment. You can also calculate the FV_ci for each stage of the project. You use the cost of capital as the compound interest rate when figuring the future value of each cash inflow.
3. PV_co: This is the present value of all cash outflows for the project or investment. Specifically, you use the financing rate as the discount factor when figuring the present value of each cash outflow. Naturally, the initial cash outflow occurs when n = 0.

The Modified Internal Rate of Return Formula

Without further ado:

MIRR = (FV_ci / PV_co)^(1/n) — 1

MIRR equals the nth root of future value of cash inflows divided by present value of the cash outflows. Then, you subtract 1 to get the final result.

How to Calculate MIRR

The best way to show how to calculate modified internal rate of return is step through an example. Therefore, suppose you want the MIRR for a two-year project with the following characteristics:

• Initial cost: $1.95M
• Cost of capital: 12%
• First year cash inflows: $1.21M
• Second year cash inflows: $1.31M
• n = 2 years

Perform the following steps:

1. Find the future value of the cash inflows as of n = 2. The first year cash flow of $1.21M was reinvested at 12%, so its contribution to the total future value is (1.12 x $1.21M), or $1.3552M. The second-year cash inflow is $1.31M, as stated. Thus, the future value of the cash inflows is ($1.3352M + 1.31M), or $2.6652M.
2. Find the present value of the cash outflows. Obviously, there was only one cash outflow, at n = 0, of $1.95M. Since that outlay occurs on the day starting the calculation, then the present value of cash outflows is -$1.95M.
3. Find the geometric mean for the two years. That is, divide the future value of cash inflows ($2.6652M), by the present value of the cash outflows ($1.95M). Mathematically, this quotient is 1.366769.
4. Take the square root of the quotient. That is, the n^th root when n = 2. Clearly, the result is (1.366769)^1/2, or 1.1691, rounded.
5. Subtract 1 from the square root to get the MIRR. That is, the MIRR = (1.1691 – 1) = 16.91%.

Hence, the project’s annual return, as expressed by the modified internal rate of return, is 16.91% after two years. Therefore, this is a profitable project, because the cost of capital is only 12%.

Calculating MIRR on Financial Calculators

For some unknown reason, a few folks like using calculators. Here is how to compute our example modified internal rate of return on two popular models.

How to Calculate MIRR on TI 84 Plus

1. Bring up the TMV Solver app by pressing APPS, ENTER, ENTER.
2. Enter the following: N = 2; I% = 0.12, PV = -1.95, PMT = 0, FV = 2.6652; P/Y =1; C/Y = END.
3. Press APPS, ENTER, 7, which brings up NPV on the screen.
4. Enter the NPV cash flow information as NPV (12, -1.95, {1.21, 1.31}) ENTER.
5. Multiply this result by the interest rate squared (i.e., to account for two periods). Type in 1.12^2, ENTER.
6. Put the cursor on I% and press ALPHA, SOLVE. You should get the final result, 16.91%.

How to Calculate MIRR on BA II Plus

1. Clear the calculator by entering CF, 2^nd, CE/C.
2. Start by using the CF key to enter the year 1 and year 2 cash flows. Then, press CF, 0, ENTER, DOWN ARROW, 1.21, ENTER, DOWN ARROW (twice), 1.31, ENTER, DOWN ARROW (twice).
3. Press the NPV key and type in 12, ENTER when prompted for the interest rate.
4. Press CPT to find the present value of the cash flows.
5. Next, find the future value of the cash flows by entering the result from step 4 as a negative number and pressing ENTER. Enter 2 into the N key and 12 into the I/Y key. Press CPT, PV to the future value.
6. Enter the initial investment as a negative number by pressing -1.95, PV and then press CPT, I/Y. Conclusively, the result is your modified internal rate of return, 16.91% per year.

MIRR Calculator

Frankly, it’s a lot easier to use an online modified internal rate of return calculator. For example, you can try out the OMNI MIRR Calculator. In our example, you would enter a 12% financing rate, a 12% reinvestment rate, and an initial investment of 1.95. Then, you’d enter the first and second year cash flows of 1.21 and 1.31. Immediately, the answer of 16.91% pops up.

MIRR Calculator – Modified Internal Rate of Return

Excel MIRR Function

If you are an Excel fan, you’ll be pleased to know that there is an Excel MIRR function. Here’s how you use it for our example.

1. Create an array with the three cash flows: -1.95, 1.21, 1.31. Label the array FLOWS.
2. Enter into any cell the following: =MIRR(FLOWS, .12, .12)
3. Expand the result to two decimal places, which reveals 16.91%.

There is also a help topic for the Excel MIRR function if you’d like to read more about it.

FAQ

What’s the difference between IRR and MIRR?

The difference is that IRR assumes you reinvest positive cash flows at the IRR. Obviously, that’s circular reasoning. On the other hand, MIRR assumes you reinvest positive cash flows at your finance rate, which you should know. In addition, IRR can give you multiple results. However, you get just one result from modified internal rate of return.

Why is IRR used?

Some managers use IRR because they feel it gives them better-looking returns than those they get from MIRR. However, MIRR gives more accurate results, because it uses your actual financial rate and cost of capital. Managers who knowingly fluff up their results to look good might live to regret it.

What is NPV?

NPV is net present value. It is the value of all the project’s cash flows discounted by the appropriate interest rate. The initial cash flow is usually an outflow, given by a negative number. Periodic inflows should eventually follow. Explicitly, a positive NPV indicates a profitable investment.

Why is NPV better than IRR?

NPV is better because it uses the correct interest rate to discount the cash flows. IRR uses IRR as the discount rate, which is highly problematic. Also, NPV gives better results with mutually-exclusive projects. Clearly, NPV and MIRR are remarkably similar. Specifically, NPV shows amount of profit or loss, modified internal rate of return shows your annual percentage return.