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