IRR in Excel: Internal Rate of Return in Plain English

IRR in Excel answers one question: what yearly return does this stream of cash flows earn? You list the money paid out and received in time order, and =IRR(B2:B5) returns a rate, such as 8.9%. That rate is the internal rate of return: the discount rate at which the cash flows break even in today's money. This guide explains IRR in plain English, works through an example you can check by hand, and covers XIRR and the #NUM! error. It explains the calculation only; it is not investment advice.

What is IRR, in plain English?#

IRR is the interest rate that an investment behaves like. If you put 10,000 into a project and it pays back 3,000, 4,000 and 5,000 over the next three years, IRR asks: "What yearly rate, on the money still tied up, would give exactly those payments?"

A more formal way to say it: IRR is the rate that makes the net present value (NPV) zero. NPV converts every future payment into today's money at a chosen rate and adds them up. At a low rate, future payments are worth a lot today and NPV is positive. At a high rate, they are worth less and NPV turns negative. The rate where it crosses zero is the IRR. The guide to the NPV function explains NPV step by step.

How to use the IRR function in Excel#

The syntax is short:

=IRR(values, [guess])

Here is a project in A1:B5:

=IRR(B2:B5)

The result is about 0.0890. Format the cell as a percentage with two decimals (Ctrl+Shift+% then add decimals, or Ctrl+1 on Windows and ⌘1 on a Mac) and it shows 8.90%. The project returns 15,000 for 10,000 paid out, but because the money comes back gradually, the yearly rate is 8.9%, not 50% spread over three years.

Two rules for the values: they must be in time order, and they must contain at least one negative and one positive number. Empty cells and text in the range are ignored, so a year with no cash flow must be typed as 0, not left blank. Otherwise the later payments slide forward by a year.

How to check an IRR with NPV#

You can prove the IRR is right by working out the NPV at that rate. Excel's NPV function treats its first value as arriving one period from now, so leave the year-0 amount out of NPV and add it separately:

=NPV(8%,B3:B5)+B2

At 8% this returns 176.29: positive, so the project earns more than 8%. At 10% it returns -210.37: negative, so it earns less than 10%. The IRR sits between them, and at 8.90% the NPV is 0.

To see the whole pattern, put a few rates in D2:D6 and this formula in E2, then fill it down:

=NPV(D2,$B$3:$B$5)+$B$2

The $ signs lock the cash-flow ranges, so only the rate moves as you fill down. That locking is an absolute reference, one of the most tested skills in Excel. The comparison of NPV vs PV shows when to use each function.

How to read the IRR result#

On its own, an IRR is just a rate. It becomes useful when you compare it with another rate. Businesses often compare it with the return they require from a project, sometimes called the hurdle rate. If the IRR is higher, the NPV at the required rate is positive. If it is lower, the NPV is negative.

Keep three limits in mind:

• IRR ignores size. A small project can have a higher IRR than a large one that adds far more money.
• IRR assumes equal periods. Each value is one period apart, whatever the dates really are.
• Unusual cash flows can have more than one IRR. If the signs change more than once (out, in, out again), there may be two rates that make NPV zero.

IRR when there is one payment out and one in#

When money goes in once and comes back once, IRR equals the yearly growth rate, also called the compound annual growth rate (CAGR). You can then work it out with a simple formula:

=(end_value/start_value)^(1/years)-1

For 5,000 paid today that becomes 6,655 after three years: =(6655/5000)^(1/3)-1 returns 0.10, or 10%. =IRR() on the cash flows -5,000, 0, 0, 6,655 gives the same 10%. Checking: 5,000 × 1.1 × 1.1 × 1.1 = 6,655. The guide to the CAGR formula covers this growth rate in more detail, and the FV function works the other way, from a rate to an end value.

Monthly cash flows and XIRR#

IRR returns a rate per period. If your cash flows are monthly, the result is a monthly rate. To turn a monthly rate into a yearly one, compound it:

=(1+IRR(B2:B14))^12-1

A monthly IRR of 1% becomes about 12.68% a year, not 12%, because the returns compound.

When payments land on irregular dates, use XIRR instead. It takes the cash flows and a matching list of dates, and always returns a yearly rate:

=XIRR(B2:B6,A2:A6)

Excel also has MIRR, which lets you set separate rates for borrowing and for reinvesting the money a project pays back.

Why does IRR return #NUM!?#

IRR finds its answer by trial and error. Microsoft's documentation says it tries up to 20 times, and returns #NUM! if it hasn't found a rate by then. The usual causes are:

1. No sign change. All values are positive, or all negative. Make the money paid out negative.
2. A poor starting point. Give a guess closer to the answer, such as =IRR(B2:B5,-0.1) for a project that loses money.
3. Several sign changes. The cash flows have more than one possible IRR, or none. Check the data, or use MIRR.

The guide to fixing the #NUM! error covers this and other causes.

Practise it#

IRR results only make sense if you're at ease with rates and percentages. Practise percentages and percent change in a live spreadsheet: you type each formula and it checks it instantly. Then practise absolute references so your NPV table fills down correctly. Or try the growth-rate exercise on this page first.