Excel 2000 in a Nutshell

Name

IRR

Synopsis

You can use IRR to determine the internal rate of return for a series of cash flows. 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, you need to specify income amounts based on regular time intervals. For example, you can indicate the amount of income for each year.

To Calculate

=IRR(Values, Guess)

The Guess argument is optional.

Values

Contain 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 five positive values indicating the interest received each year (i.e., {-100, 10, 20, 25, 40, 55}.

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

Guess

This is 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 rate of return to be accurate within 0.00001 percent. If the IRR function cannot find an accurate result within 20 tries, an error value of #NUM! is placed in the cell.

If you get the error value, you will want to specify a value for the Guess parameter.

Example

As shown in Figure 12-14, IRR is used to determine the percentage earned on an investment. In ...