Working out the internal rate of return

A small firm weighing up a new machine wants to know what the money will earn. So does a landlord looking at a property to let. One way to answer is a single percentage, the internal rate of return (IRR). This page explains what that percentage means, how to work it out and how to read it well.

What the rate tells you

Every project has money going out and money coming in, often years apart. Accountants discount the later amounts, because people generally prefer to receive money sooner and pay out later. Added together, the discounted amounts give the project's net present value (NPV).

The IRR is the discount rate that brings a project's NPV down to exactly nil. Many firms treat it as the project's forecast return. They set a target rate in advance. A project whose IRR beats the target goes ahead. The calculation uses cash flows, not accounting profit.

A worked example

ACCA, the accountancy body, uses a simple project to teach the method. It costs $7,000 today and brings in $4,000 at the end of each of the next two years.

Discounted at 5%, the project's NPV is above nil. Discounted at 20%, it is below nil. Drawing a straight line between those two results gives an IRR of about 9.9%. A second method, using annuity tables, gives about 9%. Both are estimates, so they differ a little.

With a target return of 6%, this project would go ahead. With a target of 15%, it would not.

Letting a spreadsheet do it

Excel, Google Sheets and LibreOffice Calc all have an IRR function. You list the cash flows in order, with payments as negative numbers and income as positive ones. The list needs at least one of each. IRR expects the cash flows to fall at regular intervals, such as monthly or yearly.

Excel finds the rate by trial, starting from a guess of 10%. If it cannot settle on an answer after 20 tries, it shows the #NUM! error. Microsoft suggests trying again with a different guess. For cash flows on irregular dates, the XIRR function takes a date for each payment.

Reading the result well

The IRR is a quick and clear way to rank a project against a target. A few habits help it give the right answer.

How government appraises projects

HM Treasury's Green Book guides how UK government departments appraise spending. It works the other way round from an IRR. The discount rate is fixed in advance and every option is compared at that rate.

The Green Book (2026) calls this rate the social time preference rate. It is 3.50% in real terms for years 1 to 30, 3.00% for years 31 to 75 and 2.50% after that. Effects on health and life are discounted at 1.5%. Appraisers then work out each option's net present social value and its benefit-cost ratio.

Useful links