Formulas and Functions

Chapter 6 Financial Functions 12 5
IRR
The IRR function returns the internal rate of return for an investment that is based on a
series of potentially irregular cash ows (payments that do not need to be a constant
amount) that occur at regular time intervals.
IRR(ows-range, estimate)
 ows-range:A collection that contains the cash ow values. ows-range is a
collection containing number values. Income (a cash inow) is specied as a positive
number, and an expenditure (a cash outow) is specied as a negative number.
There must be at least one positive and one negative value included within the
collection. Cash ows must be specied in chronological order and equally spaced
in time (for example, each month). If a period does not have a cash ow, 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 ows 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