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
Examples
Suppose you're a commercial fisherman just completing your fifth year of operation. Five years ago, you borrowed $120,000 at 10 percent annual interest to purchase a boat. Your catches have yielded $39,000, $30,000, $21,000, $37,000, and $46,000. During these years you reinvested your profits, earning 12 percent annually. On a worksheet, your loan amount is entered as -$120,000 in B1, and your five annual profits are entered in B2:B6.
To calculate the investment's modified rate of return after five years:
MIRR(B1:B6, 10%, 12%)
equals 12.61 percent
To calculate the modified rate of return after three years:
MIRR(B1:B4, 10%, 12%)
equals -4.80 percent
To calculate the five-year modified rate of return based on a reinvest_rate of 14 percent
MIRR(B1:B6, 10%, 14%)
equals 13.48 percent