Internal Rate of Return (IRR): Formula and Worked Examples

The internal rate of return (IRR) is the discount rate at which a project's net present value equals zero: the annual return the project earns on the money invested, given the timing of its cash flows. If the IRR is higher than the required return (the hurdle rate), the project earns more than it costs to fund; if it is lower, it does not. IRR is popular because it is a single percentage that managers find intuitive, but it has well-known weaknesses when cash flows are unusual or when projects are compared. This guide defines IRR, shows how to calculate it by hand and in Excel with worked examples, explains multiple IRRs and the modified IRR, and says when to prefer net present value. It is part of budgeting and forecasting, builds on net present value and capital budgeting methods, and leads into the payback period and capital allocation frameworks.

The definition

IRR is the rate r that solves:

0 = -Initial investment + CF1 / (1 + r) + CF2 / (1 + r)^2 + ... + CFn / (1 + r)^n

In words: find the discount rate that makes the present value of the future cash flows exactly equal to the investment.

The simplest case: one cash flow

If you invest $1,000 and receive $1,210 two years later, the IRR solves 1,000 × (1 + r)^2 = 1,210, so (1 + r)^2 = 1.21 and r = 10%. With a single inflow, the IRR is just the compound annual growth rate of the money.

Example 1: uneven cash flows, by trial and error

A project costs $1,000 and returns $400, $500 and $600 in years 1 to 3.

NPV is positive at 20 percent and negative at 22 percent, so the IRR lies between. Linear interpolation gives 20% + 2% × 27.78 / (27.78 + 5.78) = 20% + 1.66% = 21.66%. The exact value is about 21.65%. At that rate, NPV is zero.

If the company's hurdle rate is 12 percent, the project clears it by about nine points. The margin tells you how much room there is for error: the project's returns could fall a long way before it fails the test.

Example 2: the five-year project

For a $100,000 investment with cash flows of $30,000, $35,000, $40,000, $40,000 and $30,000, the NPV at 20 percent is +$3,800, at 21 percent +$1,504 and at 22 percent -$710. The IRR is about 21.7%, consistent with the NPV article. The NPV profile (NPV against the discount rate) crosses zero there.

Calculating IRR in Excel

=IRR(values, [guess])

List the cash flows with the investment as a negative first value (period 0), then the inflows. For Example 1: =IRR({-1000,400,500,600}) returns about 21.65%. Rules:

• The cash flows must include at least one negative and one positive value.
• IRR assumes equal periods. For irregular dates, use XIRR(values, dates).
• If Excel returns an error or an odd result, supply a guess (for example 10%) as the second argument.
• Check the result by computing NPV at the IRR: it should be near zero.

When IRR misleads

1. Multiple IRRs

When the sign of cash flows changes more than once, there can be more than one IRR. Example: a project costs $100, returns $230 in year 1 and requires a further outlay of $132 in year 2 (for example, a decommissioning cost).

Both 10 percent and 20 percent make NPV zero. Which is "the" IRR? Neither is meaningful. For projects with large final-year costs (mines, landfill, nuclear, some leases), rely on NPV.

2. Ranking mutually exclusive projects

IRR ignores scale and timing differences. A project can have a higher IRR but a lower NPV than another, as shown in capital budgeting methods: Project B had an IRR of 24.3 percent against Project A's 21.7 percent, but NPV of $26,973 against $32,199 at a 10 percent hurdle. Follow NPV when the choice is either-or.

3. The reinvestment assumption

IRR implicitly assumes interim cash flows are reinvested at the IRR itself. For a project with a 40 percent IRR, that assumes you can find other investments earning 40 percent, which is rarely true. NPV assumes reinvestment at the required return, which is more realistic.

4. Scale

A small project with a 50 percent IRR may add less value than a large project with a 15 percent IRR. A 50 percent return on $1,000 is $500; a 15 percent return on $1,000,000 is $150,000.

Modified IRR (MIRR)

MIRR addresses the reinvestment problem by assuming that inflows are reinvested at a stated rate and that outflows are financed at another.

For the five-year project, compound each inflow to year 5 at 10 percent:

• 30,000 × 1.10^4 = 43,923
• 35,000 × 1.10^3 = 46,585
• 40,000 × 1.10^2 = 48,400
• 40,000 × 1.10 = 44,000
• 30,000 × 1 = 30,000
• Terminal value = 212,908

MIRR = (212,908 / 100,000)^(1/5) - 1 = 2.12908^0.2 - 1 = 16.3%.

MIRR (16.3 percent) is lower than IRR (21.7 percent), because it assumes reinvestment at 10 percent rather than 21.7 percent. In Excel, use =MIRR(values, finance_rate, reinvest_rate). MIRR gives a single answer even when cash flows change sign repeatedly.

How to use IRR sensibly

Choosing the hurdle rate

The IRR rule is only as good as the benchmark. Typical choices include the weighted average cost of capital adjusted for project risk. See WACC and hurdle rates.

Link to operations and profit

Project cash flows depend on volumes, prices, margins and working capital. For product investments, tie volume assumptions to a demand plan; see demand planning and inventory optimization. For margin assumptions, see unit economics.

Common mistakes

• Treating IRR as the actual return on every dollar.
• Using IRR to choose between mutually exclusive projects.
• Ignoring multiple IRRs when cash flows change sign.
• Using the wrong period (monthly cash flows give a monthly IRR, which must be annualized).
• Omitting working capital and taxes from cash flows.
• Comparing IRR to a hurdle rate that has no basis.

FAQs

What is the internal rate of return?

The discount rate at which a project's net present value is zero, which is the annual return the project earns on its investment.

How do I calculate IRR?

By trial and error, interpolation or the IRR function in a spreadsheet, using cash flows with the investment as a negative value at time zero.

What is a good IRR?

One that comfortably exceeds the required return for the project's risk. There is no universal number.

Why is NPV considered better than IRR?

NPV measures the dollar value created, handles different scales and timing, and does not suffer from multiple solutions.

What is the difference between IRR and MIRR?

MIRR assumes explicit reinvestment and financing rates and produces a single answer, while IRR assumes reinvestment at the IRR itself.