Excel 2000 in a Nutshell

Name

XIRR

Synopsis

Use XIRR (an Analysis ToolPak function) to determine the internal rate of return for a series of cash flows that are not received regularly. The function returns a decimal value that represents the rate of return for a payment and the income for the initial payment. To get the appropriate return value, specify the income amounts and then the corresponding dates of the returns.

To Calculate

=XIRR(Values, Dates, Guess)

The Guess argument is optional.

Values

Contains a series of numbers that you want used to determine the internal rate of return. The series must contain at least one positive and one negative value. For example, to determine the rate of return for a $100 investment over five years your first value in the series would be -100 followed by positive values indicating the amount of interest received during the investment period (i.e., {-100, 10, 20, 15, 20, 30, 55}.

The series of values must be enclosed in brackets ({}) and the values must be separated with commas.

Dates

Contains a series of dates that correspond to the return values specified for the Values argument. Keep in mind that if you specify the actual dates, and not cell references, you must enclose the dates within quotes. The series of values must be enclosed in brackets ({}) and the values must be separated with commas.

Guess

An optional argument that can be used to indicate your guess as to the rate of return. If this argument is omitted, Excel starts at 0.1 (10%) and tries to calculate the ...