Key takeaways
- IRR is the discount rate that makes NPV = 0. Compare to your hurdle rate (typically WACC) — accept if IRR > hurdle.
- In private equity, the headline base-case underwrite is typically 20%+ IRR, with bear scenarios above the cost of capital (10–12%).
- Use
=XIRR()with actual dates over=IRR()with annual periods — material when entry/exit aren't on year-ends. - IRR is misleading when comparing projects of different scale or duration. NPV is the cleaner ranking rule.
- Cash-flow signs matter: investments are negative, returns are positive. Reversed signs return meaningless IRR.
The intuition
IRR answers: "what compound annual return is this cash-flow stream actually earning?" If you invest $100 and receive $200 in five years, the IRR is the rate at which $100 compounds to $200 over five years — which is roughly 14.9% per year. Mathematically, IRR is the discount rate that satisfies:
There's no closed-form algebraic solution; spreadsheets compute IRR iteratively (Newton-Raphson or bisection). Excel: =IRR(cash_flows) for evenly-spaced periods or =XIRR(cash_flows, dates) for irregular timing.
Worked example — a private equity exit
Sponsor invests $430M of equity at deal close (negative cash flow). Five years later, the company is sold and equity proceeds are $1,350M (positive cash flow).
| Date | Cash flow ($M) |
|---|---|
| 2026-04-27 (close) | (430) |
| 2031-04-27 (exit) | 1,350 |
Excel: =XIRR(cash_flow_range, dates_range) ≈ 25.7% using two cells (−430, 1,350) and the matching close and exit dates. The sponsor delivered a 25.7% IRR over the 5-year hold.
With interim distributions (a dividend recap, say $200M in year 3), the IRR rises because capital is returned earlier:
| Date | Cash flow ($M) |
|---|---|
| 2026-04-27 (close) | (430) |
| 2029-04-27 (recap) | 200 |
| 2031-04-27 (exit) | 1,150 |
IRR rises to roughly 28% — same total proceeds ($1,350M) but capital returned earlier compounds at a higher rate. This is why dividend recaps boost reported IRR even when total dollar returns are unchanged.
IRR vs MOIC
Two return metrics, both useful:
| IRR | MOIC | |
|---|---|---|
| Definition | Annualised return | Multiple of invested capital |
| Sensitive to timing | Yes — earlier cash boosts IRR | No — only total dollars matter |
| Dividend recaps | Boost IRR | No effect |
| Hold period | Embedded | Not reflected |
| Best for | Time-weighted return comparison | Dollar-amount return |
Sponsors report both. A 3.0× MOIC over 5 years (IRR ~24.6%) is generally preferred to a 2.5× MOIC over 3 years (IRR ~35.7%), even though the latter has a higher IRR — because the absolute dollar return is larger and the firm gets to redeploy capital sooner. Total returns and time-weighted returns answer different questions.
The IRR pitfalls
- Multiple IRRs. If a project's cash flows change sign more than once (negative, positive, negative again), there can be multiple solutions to NPV = 0. Excel returns one; the rest are hidden. For non-conventional cash flows, use NPV instead.
- Reinvestment assumption. Standard IRR implicitly assumes interim cash flows are reinvested at the IRR — which is rarely realistic. Modified IRR (MIRR) lets you specify a separate reinvestment rate; use
=MIRR(cash_flows, finance_rate, reinvest_rate)when this matters. - Scale insensitivity. A small project with a high IRR can be worth less in absolute dollars than a large project with a lower IRR. Don't rank mutually exclusive projects on IRR alone.
- Wrong cash-flow signs. Investment must be negative, returns positive. Excel returns
#NUM!or a meaningless number with reversed signs. - Year-end vs actual dates.
=IRR()assumes annual cash flows. Real deals close mid-year and exit mid-year — use=XIRR()with actual dates.
How Smalt AI builds it
LBO and project-finance models compute IRR via =XIRR() with the actual transaction dates, not annual periods. Returns analysis includes IRR, MOIC, and money-back date (when cumulative distributions cross the initial investment). Sensitivity tables on entry × exit multiple → IRR are standard and color-coded green > 20%, yellow 15–20%, red < 15%.
Further reading
- Brealey, Myers & Allen — Principles of Corporate Finance, chapter on alternatives to NPV.
- Damodaran — discussion of IRR pitfalls and MIRR.
Related
NPV · LBO · WACC · Financial modeling