MIRR

Returns the modified internal rate of return for a series of periodic cash flows.

Syntax:

MIRR(values:Array|Range, finance_rate:Number, reinvest_rate:Number)

where:

values is an array (or range of cells) containing numbers representing payments (negative numbers) and receipts (positive numbers) occurring at regular periods.
finance_rate is the interest rate you pay;
reinvest_rate is the interest rate you receive on reinvested cash.

This function considers both the cost of the investment and the interest received on reinvestment of the cash receipts.

Example:

If you borrowed £120,000 to set up a business, and reinvested income of £39,000, £30,000, and £21,000, you should enter these figures in four cells (say B1:B4). Interest paid on the load was 10% and interest received on investments 12%. The formula would be:

MIRR(B1:B4, 0.10, 0.12)

and the rate of return calculated would be 0.04804.