MIRR

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