[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

MIRR

MIRR ( values , finance-rate , reinvest-rate )

Description: Computes the modified internal rate of return for a series of periodic cash flows. (Both the cost of the investment and the interest received on reinvestment of cash are considered.)

Mathematical Formula:

If n is the number of cash flows in values, frate is the finance-rate, and rrate is the reinvest-rate, then the formula for MIRR is:

Arguments:

Name

Type

Description

values

array, reference

Designates a set of numbers for which the rate of return is to be calculated. values shall contain at least one positive value and one negative value to calculate the internal rate of return. The order of numbers in values is significant, so be sure payment and income numbers are in the desired sequence. If values contains elements that are text, logical values, or empty cells, those elements are ignored.

finance-rate

number

The interest rate paid pay on the money used in the cash flows.

reinvest-rate

number

The interest rate received on the cash flows as they are reinvested.

 

Return Type and Value: number – The modified internal rate of return for a series of periodic cash flows.

However, if values does not contain at least one positive value and one negative value, #DIV/0! is returned.

[Example:

MIRR({-120000,39000,30000,21000,37000,46000},0.1,0.12) results in 12.6094%
MIRR({-120000,39000,30000,21000},0.1,0.12) results in -4.8045%
MIRR({-120000,39000,30000,21000,37000,46000},0.1,0.14) results in 13.4759%

end example]