MIRR Function

Summary

The Excel MIRR function returns the modified internal rate of return for a series of periodic cash flows, using one rate for the cost of money used in negative cash flows and another rate for reinvestment of positive cash flows. Microsoft defines MIRR this way to address one of the key limitations of standard IRR.

Regular IRR assumes that positive cash flows are reinvested at the same internal rate, which is often unrealistic. MIRR replaces that assumption with an explicit reinvestment rate and also makes the financing cost explicit. That gives the analyst more control over the economic meaning of the result.

MIRR is therefore useful when return analysis should reflect a more defensible capital structure or reinvestment assumption than a plain IRR can provide.

Syntax

=MIRR(values, finance_rate, reinvest_rate)

values is the cash-flow series in chronological order, finance_rate is the rate paid on negative cash flows, and reinvest_rate is the rate earned on positive cash flows. Microsoft notes that the cash-flow series must contain at least one positive value and one negative value or the function returns an error.

MIRR assumes the cash flows are periodic. If the project uses irregular dates rather than equal spacing, another method is needed.

Arguments

• values - Cash flows in time order.
• finance_rate - Rate used for negative cash flows.
• reinvest_rate - Rate used for positive cash flows.

The two rates should use the same period basis as the cash-flow stream. If the values are annual, both rates should be annual as well. Mixed period assumptions make the return hard to interpret.

MIRR vs Other Functions

MIRR sits beside IRR and NPV, but it answers a different kind of return question.

Use MIRR when the model needs a rate-based measure, but the standard IRR reinvestment assumption is too strong or too opaque.

Using the MIRR Function

MIRR is common in capital budgeting because it makes the analyst state two assumptions that are often hidden inside IRR discussions: the financing cost for negative flows and the reinvestment rate for positive flows. That usually produces a return measure that is easier to explain in a decision memo or review meeting.

It is also a useful sensitivity tool. Changing the finance rate or reinvestment rate can show whether the project's attractiveness depends heavily on capital market assumptions.

• Use MIRR when the model should separate borrowing cost from reinvestment return.
• Keep the cash flows in chronological order and on a consistent period basis.
• Make sure the values include at least one negative and one positive cash flow.

Example 1 - Standard Realistic Yield

This formula calculates the modified return for an initial outflow of $1,000 followed by three positive cash inflows. The separate 10% finance rate and 12% reinvestment rate make the result more explicit than a plain IRR based on one implicit reinvestment assumption.

This is a helpful starting example because it shows what makes MIRR different. The formula does not treat borrowing cost and reinvestment return as the same number, so the result is often easier to defend in real analysis.

=MIRR({-1000,300,400,500},0.1,0.12)

Example 2 - High Financing Cost

Raising the finance rate to 15% tests how sensitive the project's return is to a more expensive funding environment. This is useful when comparing the same project under different capital-cost assumptions.

That makes the example practical for scenario work. The project cash flows stay the same, but the return changes because the cost of funding changed, which is exactly the kind of comparison decision-makers care about.

=MIRR({-1000,300,400,500},0.15,0.12)

Example 3 - Profitability Threshold Check

This logical test checks whether the modified return clears a 10% hurdle. It turns MIRR from a descriptive metric into a screening rule for capital allocation decisions.

So the sheet is doing more than reporting a percentage. It is checking whether the project meets the minimum return requirement needed to move forward.

=MIRR({-1000,300,400,500},0.1,0.12)>0.1

Example 4 - Structural ROI Gap

Subtracting MIRR from a benchmark 15% return highlights the shortfall between the project's modified return and the target. This kind of gap measure is useful in ranking or escalation discussions.

This makes the result easier to discuss because a gap is often more intuitive than the raw MIRR alone. It shows how far the project is from the benchmark instead of only stating its return.

=0.15-MIRR({-1000,300,400,500},0.1,0.12)

Conclusion Recap

MIRR is useful when plain IRR feels too optimistic because it quietly assumes reinvestment at the same internal rate. In this lesson, that meant adding separate finance and reinvestment rates, testing sensitivity to funding cost, checking a hurdle rate, and comparing the result with a benchmark return.

The main advantage is that MIRR makes your assumptions more honest. Instead of hiding them inside one solved rate, it asks you to state how negative cash flows are financed and how positive cash flows are reinvested. That usually makes the result easier to explain in real decision-making.

• Summary: MIRR returns a modified internal rate of return using explicit finance and reinvestment rates.
• Syntax: =MIRR(values,finance_rate,reinvest_rate).
• Core setup: Use periodic cash flows, consistent rate units, and at least one positive and one negative cash flow.
• Best use: Capital budgeting, IRR sensitivity review, and return analysis with explicit reinvestment assumptions.