Function: MIRR

by | Jan 30, 2019 | Excel Formulas

mirr excel

The MIRR Excel function is a finance function that calculates the modified internal rate of return (MIRR) for a series of periodic cash flows. This function works by using both the cost of the investment and the interest received by reinvesting the cash. In this guide, we’re going to show you how to use the MIRR Excel function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

Syntax

MIRR(values, finance_rate, reinvest_rate)

Arguments

values

A series of cash flows. Cash flows must contain at least one positive value and one negative. Payments are expressed as negative values and income as positive.

finance_rate

The rate of return on cash flows.

reinvest_rate

The interest rate received on cash flows reinvested.


Example

=MIRR(cash_flow,finance_rate,reinvest_rate)
 formula demonstrates basic usage of the MIRR function. The formula returns the modified internal rate of return after four years.

MIRR Function

Download Workbook

Tips

  • Try IRR or XIRR functions, if all cash flows are reinvested at the same rate as the internal rate of return.
  • MIRR function assumes that cash flows are in regular periods.
  • The function ignores text, logical values, or empty cells.
  • Values must be in chronological order.
  • VBA also has a MIRR function that works using the same syntax.

Issues

#DIV/0!

  • Cash flows must contain at least one positive value and one negative value.