Apple iWork '09 - Manual (Page 125)

Chapter 6 Financial Functions 125
IRR
The IRR function returns the internal rate of return for an investment that is based on a
series of potentially irregular cash flows (payments that do not need to be a constant
amount) that occur at regular time intervals.
IRR(flows-range, estimate)
 flows-range: A collection that contains the cash flow values. flows-range is a
collection containing number values. Income (a cash inflow) is specified as a positive
number, and an expenditure (a cash outflow) is specified as a negative number.
There must be at least one positive and one negative value included within the
collection. Cash flows must be specified in chronological order and equally spaced
in time (for example, each month). If a period does not have a cash flow, use 0 for
that period.
 estimate: An optional argument specifying the initial estimate for the rate of return.
estimate is a number value and is either entered as a decimal (for example, 0.08) or
with a percent sign (for example, 8%). If omitted, 10% is assumed. If the default value
does not result in a solution, initially try a larger positive value. If this does not result
in an outcome, try a small negative value. The minimum value allowed is –1.
Usage Notes
If the periodic cash flows are the same, consider using the NPV function. Â
Example 1
Assume you are planning for your daughter’s college education. She has just turned 13 and you
expect she will begin college in 5 years. You have $75,000 to set aside in a savings account today and
will add the bonus you receive from your employer at the end of each year. Since you expect your
bonus to increase each year, you expect to be able to set aside $5,000, $7,000, $8,000, $9,000, and
$10,000, respectively, at the end of each of the next 5 years. You think you will need to have $150,000
set aside for her education by the time your daughter reaches college.
Using the IRR function, you can determine the rate you would need to receive on invested amounts
in order to have $150,000. Based on the assumptions given, the rate would be 5.70%.
Initial
Deposit Year 1 Year 2 Year 3 Year 4 Year 5 Amount
Required
=IRR(B2:H2) -75000 -5000 -7000 -8000 -9000 -10000 150000