Excel's IRR function is the fastest way to calculate Internal Rate of Return
for conventional cash flows. Here is how to use it correctly.
Excel IRR syntax
=IRR(values, [guess])
values: a range containing the cash flows (must include at least one negative and one positive value). Year 0 (initial investment) should be the first cell.
guess: optional starting guess for the iteration (default 0.1 = 10%).
Only needed if IRR returns #NUM! or you expect an unusual result.
Example
In cells A1:A5: -100000, 25000, 30000, 35000, 40000
Formula: =IRR(A1:A5) → 7.66%
If your hurdle rate is 12%, this investment should be rejected (IRR < hurdle).
XIRR vs. IRR
Use XIRR when cash flows don't occur on regular annual intervals:
=XIRR(values, dates)
XIRR is more accurate for real-world investments where cash flows vary (e.g. quarterly distributions, irregular capital calls).
Common errors
- #NUM!: IRR couldn't converge. Try a different
guessor check for multiple sign changes (use MIRR instead). - Forgetting Year 0: if you don't include the initial investment as a negative value in the first cell, IRR returns incorrect results.
Use the browser-based IRR calculator for instant calculation without Excel.