Skip to content
Google Sheets

MIRR function in Google Sheets

Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income.

=MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)

MIRR syntax and parameters

Three arguments, three required.

  • cashflow_amountsarrayRequired

    An array or range containing the income or payments associated with the investment. cashflow_amounts must contain at least one negative and one positive cash flow to calculate rate of return.

  • financing_ratenumberRequired

    The interest rate paid on funds invested.

  • reinvestment_return_ratenumberRequired

    The return (as a percentage) earned on reinvestment of income received from the investment.

MIRR examples

Formulas you'll actually reuse.

  1. IRR with realistic assumptions — outlays financed at 6%, inflows reinvested at 4%:

    =MIRR(B2:B7, 6%, 4%)

    Result0.102

  2. How much the plain IRR overstates the return:

    =MIRR(B2:B7, 6%, 4%) - IRR(B2:B7)

    Result-0.0504

MIRR in Excel

Same name — your formula ports as-is.

Try MIRR in the playground

Edit the example — nothing to install.

Preloaded with the MIRR formula from Example 1 — change anything and watch it respond.

Loading the editor…

MIRR errors

What they mean — and the fixes.

  • #DIV/0!

    The cash flows contain no negative (or no positive) value — MIRR needs at least one of each.

Related functions

More ways Google Sheets gets this done.