The IRR function is provided by Excel so you can calculate an
internal rate of return for a series of values. The IRR is the interest
rate accrued on an
investment
![]()
consisting of payments and income that occur at the same regular
periods. In the values provided to the function, you enter payments you
make as negative values and
income you receive as positive values.
For instance, let's say you are investing in your daughter's
business, and she will make payments back to you annually over the
course of four years. You are planning to invest $50,000, and you
expect to receive $10,000 in the first year, $17,500 in the second
year, $25,000 in the third, and $30,000 in the fourth.
Since the $50,000 is
money
you are paying out, it is entered in Excel as a negative value. The
other values are entered as positive values. For instance, you could
enter –50000 in cell D4, 10000 in cell D5, 17500 in cell D6, 25000 in
cell D7, and 30000 in cell D8. To calculate the internal rate of
return, you would use the following formula:
=IRR(D4:D8)
The function returns an IRR of 19.49%.
The ranges you use with the IRR function must include at least one
payment and one receipt. If you get a #NUM error, and you have included
payments and receipts in the range, then Excel needs more information
to calculate the IRR. Specifically, you need to provide a "starting
guess" for Excel to work with. For example:
=IRR(D4:D8, -5%)
This usage means that the IRR function starts calculating at –5%,
and then recursively attempts to resolve the IRR based on the values in
the range.
×