- IRR state
- ok
- IRR
- 9.7%
- Periods
- 3 after t=0
Show the math
Period 0: -1000 / (1 + 0.1)^0 = -1000Period 1: 400 / (1 + 0.1)^1 = 363.6363636363636Period 2: 400 / (1 + 0.1)^2 = 330.5785123966942Period 3: 400 / (1 + 0.1)^3 = 300.525920360631
- XIRR state
- ok
- XIRR
- 10%
- Dates
- 2
Show the math
2026-01-01: -1000 / (1 + 0.1)^0 = -10002027-01-01: 1100 / (1 + 0.1)^1 = 999.9999999999999
Periodic and dated modes are not interchangeable
Periodic NPV discounts the first cash flow at t=0 and each following cash flow at one equal interval. Microsoft’s NPV documentation explains a spreadsheet difference: the NPV function assumes listed values occur at the end of periods, so an immediate initial cash flow is commonly added outside the function. This page keeps t=0 visible in the model input.
XNPV and XIRR use actual dates. The model specification uses day differences divided by 365. Dated and periodic results should match only when timing assumptions align.
Why IRR can be ambiguous
IRR is a root of the NPV equation, not a universal project score. The cash flows −100, +230 and −132 produce two roots near 10% and 20%. The engine reports an ambiguous state and shows both roots instead of choosing one. For nonconventional cash flows, compare NPV across a relevant range or use a separately specified reinvestment model.
| Function | Timing | Important boundary |
|---|---|---|
| NPV | Equal periods | Keep the immediate cash flow outside spreadsheet NPV or model it explicitly at t=0. |
| IRR | Equal periods | Can fail or return multiple roots when cash-flow signs change more than once. |
| XNPV | Actual dates | Every cash flow needs a valid date on or after the first date. |
| XIRR | Actual dates | Iteration can fail; multiple roots remain possible. |
| MIRR | Equal periods | Requires separately stated finance and reinvestment rates. |