Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.
Syntax
MIRR(values,finance_rate,reinvest_rate)
Values is an array or a reference to cells that contain numbers. These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.
Finance_rate is the interest rate you pay on the money used in the cash flows.
Reinvest_rate is the interest rate you receive on the cash flows as you reinvest them.
Remarks
Example
The example may be easier to understand if you copy it to a blank spreadsheet.
Selecting an example from Help
Data | Description |
---|---|
-$120,000 | Initial cost |
39,000 | Return first year |
30,000 | Return second year |
21,000 | Return third year |
37,000 | Return fourth year |
46,000 | Return fifth year |
10.00% | Annual interest rate for the 120,000 loan |
12.00% | Annual interest rate for the reinvested profits |
Formula | Description (Result) |
=MIRR(A2:A7, A8, A9) | Investment's modified rate of return after five years (13%) |
=MIRR(A2:A5, A8, A9) | Modified rate of return after three years (-5%) |
=MIRR(A2:A7, A8, 14%) | Five-year modified rate of return based on a reinvest_rate of 14 percent (13%) |