Internal Rate of Return (IRR)

All versionsIRR

IRR is the annual return an investment earns — the discount rate at which its cash flows break even (NPV = 0). It turns a stream of outflows and inflows into a single “this project returns X%” number you can compare to a hurdle rate.

Quick formula: for a column of cash flows starting with the negative investment:

=IRR(B2:B7)

List the initial outlay (negative) and the later inflows (positive) in order; IRR returns the break-even annual rate.

Functions used (tap for the full reference guide):

IRR NPV

The example

Invest $1,000, then receive cash for 4 years.

	A	B
1	Year	Cash flow
2	0	-$1,000
3	1	$300
4	2	$400
5	3	$500
6	IRR:	~10.6%

The formula

The internal rate of return:

=IRR(B2:B5) // -1000, 300, 400, 500 → ~10.6%

How it works

IRR finds the rate that makes the deal break even:

List the cash flows in time order, starting with the negative initial investment, then the inflows — one per equal period.
IRR(range) solves for the discount rate where the NPV of those flows equals zero.
That rate is the project’s annual return: ~10.6% here. Compare it to your hurdle rate — above it, the project clears the bar.
IRR assumes evenly spaced periods. For irregular dates, use XIRR instead.

IRR can mislead. Cash flows that flip sign more than once can have multiple IRRs (or none), and IRR assumes you reinvest at the IRR itself. For tricky streams, cross-check with NPV at your actual rate, or use MIRR.

Try it: interactive demo

Live demo

Adjust the initial investment; watch the IRR move.

Initial investment: $1,000

IRR:

Variations

Provide a guess

Help IRR converge on tricky data:

=IRR(B2:B20, 0.1)

Cross-check with NPV

NPV at your real rate should be near 0 at the IRR:

=NPV(rate, B3:B20) + B2

Irregular dates? XIRR

When periods aren’t equal:

=XIRR(values, dates)

Pitfalls & errors

#NUM! — can’t converge. IRR needs at least one negative and one positive flow, and may need a guess argument for unusual streams.

Order and spacing matter. Flows must be in time order, one per equal period. Out-of-order or irregular timing gives wrong results — use XIRR for real dates.

Multiple IRRs. If the sign of the cash flow changes more than once, several rates can satisfy NPV=0. Validate with NPV or MIRR.

Practice workbook

📊

Download the free Internal Rate of Return (IRR) practice workbook

A cash-flow stream with live IRR, the guess and NPV cross-check variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate IRR in Excel?

List the cash flows in order with the initial investment negative, then use =IRR(range). It returns the annual rate where the net present value of the flows is zero.

Why does IRR return #NUM!?

IRR couldn't converge. Make sure there's at least one negative and one positive flow, and supply a guess as the second argument, e.g. =IRR(range, 0.1).

When should I use XIRR instead of IRR?

Use XIRR when the cash flows occur on irregular dates. IRR assumes equally spaced periods; XIRR takes an explicit dates range.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: IRR · NPV