Excel IRR Function

IRR finds the periodic rate that makes the net present value of a cash-flow series equal to zero.

Use it to estimate a project’s return from an initial investment and later receipts or expenses at equal time intervals.

Include the whole cash-flow timeline Enter the initial investment as a negative value, followed by cash flows at equal intervals. Keep numeric zeros for periods with no cash flow.

Calculate the rate per period IRR finds the rate that makes the cash flows’ net present value zero. Monthly periods produce a monthly rate; annual periods produce an annual rate.

IRR syntax & arguments

Syntax

=IRR(values, [guess])

Required Optional

1

values
Required

A range or array of cash flows in chronological order at equal intervals, including the initial investment. Include at least one negative and one positive value.
2

guess
Optional

An initial estimate of the rate per period for the iterative calculation. Try another estimate if the calculation does not converge. Defaults to 10%.

Example

=IRR(B3:E3)

Return the annual internal rate of return for an initial investment of 1000 followed by year-end receipts of 100, 100, and 1100, with B3:E3 containing -1000, 100, 100, and 1100 in that order.

IRR caveats

Both signs are required

Money paid out is negative and money received is positive. The values must contain at least one of each sign. An all-positive or all-negative series returns #NUM!.
Dates must be equally spaced

IRR uses position, not calendar dates. It cannot account for irregular dates between payments.
The result follows your period unit

Annual cash flows produce an annual rate; monthly cash flows produce a monthly rate. To convert a monthly result to an effective annual rate, use (1+monthly_rate)^12-1. Multiplying by 12 instead gives a nominal annual rate.
Keep zero-cash-flow periods

IRR ignores text and empty cells in a reference. Use a numeric 0 to keep a period with no cash flow in the timeline.
The calculation may fail to find a rate

Excel searches for a rate starting from guess, which defaults to 10%. If it cannot find a sufficiently accurate result within 20 iterations, it returns #NUM!. A different guess may help, but does not guarantee a result.
More than one rate can fit the same cash flows

When cash flows change sign more than once, more than one rate may make their net present value zero. Different guesses can lead to different rates that satisfy the same calculation.