Module IV· IRR / MOICIntermediate
Question
How do you calculate IRR with multiple cash flows in Excel?
Answer
What
Three Excel functions for IRR calculations:
- `IRR()`: evenly spaced annual cash flows, e.g. `=IRR(B2:B8)`
- `XIRR()`: arbitrary dates, e.g. `=XIRR(Cashflows, Dates)`. The standard in PE modeling.
- `MIRR()`: modified IRR with a separate reinvestment rate, e.g. `=MIRR(B2:B8, FinanceRate, ReinvestRate)`
Why XIRR is the PE standard
It supports multiple investments (add-on strategy), arbitrary dates (closing day, quarter-end), and is more robust when the sign changes.
Deep diveShow more details
Example setup
| Cell | Date | Cash flow |
|---|---|---|
| A2 | Jan 1, 2024 | −200 |
| A3 | Dec 31, 2025 | −50 (add-on investment) |
| A4 | Jun 30, 2027 | +80 (recap) |
| A5 | Dec 31, 2029 | +400 (exit) |
Formula: `=XIRR(B2:B5, A2:A5)` → returns the exact IRR over the irregular dates.
Common pitfalls
- The initial cash flow must be entered as a negative (−200, not 200).
- IRR can have multiple solutions when the sign changes: XIRR is more robust.
- Excel needs an initial guess (default 10%); with extreme returns, convergence can be slow. For an IRR above 50% or a negative IRR, set the guess explicitly.
Pitch tip
Question: "Why XIRR instead of IRR?"
Answer: "For add-on investments or multiple distributions over the hold period, XIRR is standard. A pure 'Year 0 investment, Year N exit' can be computed with simple IRR — but that's junior level. In any realistic LBO model with a recap or bolt-on, the calculation runs through XIRR"