IRR Explained: Formula, Excel Calculation, and XIRR
The internal rate of return is the single number that summarises an investment’s profitability: the discount rate at which the present value of everything you receive equals everything you pay. It lets you compare a rental property, a bond, and a business project on one scale — and it has traps (multiple IRRs, reinvestment assumptions) that every user should know before trusting it.
Key takeaways
- IRR is the discount rate r that makes NPV = 0: the sum of cash flows discounted at r equals zero. There is no direct formula — it is found by iteration.
- In Excel, =IRR(values) handles regular periodic flows; =XIRR(values, dates) handles irregular dates — use XIRR for real-world investments with uneven timing.
- IRR assumes interim cash flows are reinvested at the IRR itself, which flatters high-IRR projects; compare with NPV at your actual cost of capital, and watch for multiple IRRs when cash flows change sign more than once.
What IRR actually is
Imagine an investment as a series of cash flows: money out (negative) and money in (positive) over time. The net present value (NPV) discounts each flow back to today at some rate: future money is worth less than present money, and the discount rate expresses how much less. The IRR is the discount rate at which NPV is exactly zero — the rate at which the investment exactly breaks even in present-value terms. If your required return (cost of capital) is below the IRR, the investment creates value (NPV positive); if above, it destroys value (NPV negative). That break-even framing is the whole intuition: IRR is the investment’s own implied yearly return.
The formula and why it needs iteration
NPV = Σ [ CFₜ / (1 + r)ᵗ ] = 0, solved for r
CFₜ is the cash flow in period t, and r is the IRR. For a two-period investment the equation is quadratic and solvable directly; for longer series it is a high-degree polynomial with no closed-form solution, so every IRR computation — calculator, spreadsheet, or software — iterates: guess a rate, compute NPV, adjust, repeat until NPV is (near) zero. The IRR calculator on this page performs that iteration for your cash flows, including the year-by-year NPV path that shows convergence.
Worked example by hand
Invest $1,000 today, receive $600 after one year and $600 after two years. NPV(r) = −1000 + 600/(1+r) + 600/(1+r)². Try r = 10%: −1000 + 545.45 + 495.87 = 41.32 (positive, so the true IRR is higher). Try r = 15%: −1000 + 521.74 + 453.69 = −24.57 (negative, so the IRR is between 10% and 15%). Try r = 13%: −1000 + 530.97 + 469.88 = 0.85. Try r = 13.1%: −1000 + 530.50 + 469.02 = −0.48. The IRR is about 13.05%. That bracketing — positive at one rate, negative at another, narrowing the gap — is exactly what software does, faster and to more decimals.
Sanity check: total received is $1,200 on $1,000 over two years, so a simple (non-compounded) average return would be 10% a year; the IRR of 13% is higher because the first $600 arrives after one year and is worth more than money arriving later. Timing, not just totals, drives IRR — which is why two investments with identical totals can have very different IRRs.
IRR and XIRR in Excel
For regular periodic flows (yearly, monthly), list the cash flows in order including the initial outflow as a negative, then use =IRR(values). Example: cells A1:A3 holding −1000, 600, 600 give =IRR(A1:A3) ≈ 13.05%. Add a guess argument (=IRR(values, 0.1)) if Excel struggles to converge — the default guess is 10%, and unusual cash-flow patterns sometimes need a closer starting point.
For verification, pair IRR with NPV: =NPV(rate, future flows) + initial outflow should be near zero at the IRR. If it is not, the IRR result is suspect — check signs (outflows negative, inflows positive) and ordering (chronological). Sign errors are the commonest Excel IRR mistake: a missing minus sign on the initial investment produces nonsense or no solution.
XIRR: when dates are irregular
Real investments rarely pay on neat yearly anniversaries. XIRR handles dated flows: =XIRR(values, dates) computes the annualised rate that makes the date-weighted NPV zero, using actual day counts (365-day years). Example: invest $10,000 on 2024-03-15, receive $3,000 on 2024-11-20, $4,000 on 2025-06-10, and $5,000 on 2026-01-05. IRR cannot handle these uneven gaps correctly; XIRR gives the true annualised return (roughly 13–14% for this pattern, depending on exact day counts — compute it in the spreadsheet rather than trusting a rounded recollection).
XIRR rules: at least one positive and one negative flow, dates in chronological order, and dates as real Excel dates (not text). XIRR is the right tool for property deals, private investments, and any series where money moves on real calendar dates rather than period numbers.
The traps: multiple IRRs and reinvestment
Multiple IRRs. When cash flows change sign more than once (invest, receive, invest again — as in mining, staged construction, or projects with cleanup costs), the NPV equation can have several roots, and several IRRs can all be “correct” mathematically. Software typically returns one, silently. The defence: plot NPV against discount rate, or rely on NPV at your cost of capital, which is always single-valued.
Reinvestment assumption. IRR implicitly assumes interim cash flows are reinvested at the IRR itself. A project boasting a 40% IRR assumes you can reinvest its early payouts at 40% — usually fantasy. The modified IRR (MIRR) fixes this by letting you specify a reinvestment rate and a finance rate separately; when a project’s IRR looks too good, MIRR is the reality check.
Scale blindness. IRR is a rate, not an amount. A $100 investment returning $200 (IRR 100%) beats a $1,000,000 investment returning $1,100,000 (IRR 10%) on IRR alone — but the second creates $100,000 of value and the first $100. Always read IRR alongside NPV (absolute value created) and the capital at risk.
IRR vs NPV: which to trust
| Situation | Trust | Why |
|---|---|---|
| Single project, accept or reject | Either (they agree) | IRR above cost of capital ⇔ NPV positive. |
| Comparing projects of different sizes | NPV | IRR ignores scale; NPV measures value created. |
| Cash flows change sign repeatedly | NPV | Multiple IRRs may exist; NPV is single-valued. |
| Capital is rationed | IRR (with NPV check) | Rate per dollar matters when dollars are scarce. |
| Irregular dates | XIRR + NPV | XIRR annualises correctly; NPV grounds the amount. |
Frequently asked questions
What is IRR in simple terms?
The annual return rate at which an investment’s cash inflows and outflows break even in present-value terms (NPV = 0). If your required return is below the IRR, the investment creates value.
How do I calculate IRR in Excel?
For regular flows, =IRR(values) with outflows negative and inflows positive, in chronological order. For irregular dates, use =XIRR(values, dates). Both iterate numerically — there is no direct formula for longer series.
What is the difference between IRR and XIRR?
IRR assumes regular periods (yearly, monthly). XIRR uses actual calendar dates and day counts, so it is correct for real-world investments with uneven payment dates.
Can an investment have more than one IRR?
Yes, when cash flows change sign more than once (for example, invest, receive, then invest again). In that case IRR is ambiguous and NPV at your cost of capital is the reliable measure.
Why is IRR sometimes misleading?
It assumes interim cash flows are reinvested at the IRR itself (often unrealistic), and it ignores scale — a tiny high-IRR project can look better than a large project creating far more value. Read IRR with NPV.
What is a good IRR?
One comfortably above your cost of capital for the risk involved. There is no universal threshold: a 8% IRR may be excellent for a low-risk bond-like project and poor for a high-risk venture. Compare with similar-risk alternatives, not with a fixed number.
Compute the IRR of your cash flows
Run your own numbers instead of guessing from someone else’s example.