Financial
IRR function: calculate Internal Rate of Return across uneven cash flows
Find the periodic discount rate that sets project Net Present Value to zero with IRR, and understand why cash flows need at least one sign change.
What IRR solves across regular periods
IRR(values, [guess]) finds the periodic discount rate r at which the Net Present Value of equally spaced cash flows equals zero: CF0 + CF1/(1+r) + CF2/(1+r)^2 + ... = 0.
Include the negative initial investment at period 0 as the first cell of the range, and enter 0 explicitly for any year with zero cash flow rather than leaving a gap that shifts timing.
| Period (A) | Project A Cash Flow (B) | Project B Cash Flow (C) |
|---|---|---|
| Year 0 | -1000 | -1000 |
| Year 1 | 550 | 400 |
| Year 2 | 605 | 500 |
| Year 3 | 0 | 300 |
Verify IRR by hand and compare against a hurdle rate
For Project A (-1,000, +550, +605), discounting at 10% gives -1,000 + 550/1.10 + 605/1.21 = -1,000 + 500 + 500 = 0, so IRR(B2:B4) is exact 0.10 (10.00%). For Project B (-1,000, +400, +500, +300), IRR(C2:C5) is 0.1013 (10.13%).
=ROUND(IRR(B2:B4), 4)Why same-sign cash flows return #NUM!
Every investment return requires both an outflow (negative) and an inflow (positive). If you forget the minus sign on the initial outlay so every cell is positive, no real discount rate can sum positive numbers to zero, and IRR returns #NUM!.
Cash flows that change sign more than once can have multiple IRRs. The initial guess may affect which solution an iterative solver finds, and convergence is not guaranteed. Compare the cash-flow pattern and NPV as well as the returned rate.
=IRR(C2:C5)Try the example
Use the original values or change them to test your understanding.