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.

=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.

=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.

=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.

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

Conclusion Recap

• 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.