IRR
Find the discount rate at which a series of cash flows has a net present value of zero — the return an investment earns.
Syntax
IRR(values, [guess])| Argument | Required | Description |
|---|---|---|
values | Required | The cash flows, in order, starting with the initial investment as a negative number. |
guess | Optional | A starting estimate for the rate. Rarely needed. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=IRR(B2:B5) | 0.09 | About 8.9% a year, shown to two decimal places. Format the cell as a percentage to read it as one. |
=IRR(B2:B5)*100 | 8.90 | |
=NPV(IRR(B2:B5),B3:B5)+B2 | 0.00 | At the IRR the net present value is zero — that is the definition. The .00 is floating-point residue. |
=IRR(B7:B8) | #NUM! | With no positive cash flow there is no rate that breaks even. |
What it means
IRR answers “what annual return does this investment earn?” Put in 1,000 and
get 300, 400 and 500 back over three years, and the return is about 8.9%. If
that beats what the money could earn elsewhere, the investment is worth making.
Cash flows need both signs
At least one value must be negative (money in) and at least one positive (money
out), or no rate can make them balance and IRR returns #NUM!. The first
value is usually the investment, entered as negative.
Unlike NPV, IRR includes the first value at time zero, so
the initial investment goes inside the range.
Equal periods, regular order
The values are assumed to be one period apart and in order. For cash flows on
irregular dates, IRR will give a wrong answer without complaint — the periods
are the only notion of time it has. Use XIRR instead,
which takes a date for each flow.
Excel compatibility
Matches Excel, including #NUM! when no rate can be found.