Financial Modeling
Irregular-Date Internal Rate of Return (XIRR Equation, Newton Step & XNPV = 0 Proof)
Solve and audit exact annualized IRR on irregular calendar dates using ACT/365 day-count exponents (d_i - d_0) / 365, Newton-Raphson updates, and zero-XNPV verification.
Why regular IRR distorts irregular deal cash flows—and how XIRR is defined
Standard IRR(values) assumes every row is spaced by one identical period, distorting annualized returns whenever capital calls and distributions occur on uneven calendar dates.
Annualized XIRR is defined as the discount rate r satisfying SUM(CF_i / (1 + r) ^ ((Date_i - Date_0) / 365)) = 0. Because its derivative with respect to r is SUM(-t_i * CF_i / (1 + r) ^ (t_i + 1)) where t_i = (Date_i - Date_0) / 365, just 3 Newton-Raphson steps r_next = r - f(r) / f'(r) from a 10% seed converge to machine precision—and plugging r back into the XNPV sum proves f(r) = 0.
| Cash Flow Date | Cash Flow (CF_i) | ACT/365 Exponent t_i | PV at r = 20.00% |
|---|---|---|---|
| 2026-01-01 (Initial Outlay) | -10,000 | 0.0000 | -10,000.00 |
| 2027-01-01 (Distribution 1) | +4,800 | 1.0000 | +4,000.00 |
| 2028-01-01 (Exit Proceeds) | +8,640 | 2.0000 | +6,000.00 (Net XNPV = 0.00) |
One Newton-Raphson XIRR Iteration Step
Given year-fraction vector C2:C4 = (Date - $A$2) / 365, cash flows B2:B4, and prior rate estimate E2, computes r_next = r - XNPV(r) / XNPV'(r).
=LET(npv, SUMPRODUCT($B$2:$B$4 / ((1 + E2) ^ $C$2:$C$4)), dnpv, SUMPRODUCT(-$C$2:$C$4 * $B$2:$B$4 / ((1 + E2) ^ ($C$2:$C$4 + 1))), E2 - npv / dnpv)Zero-XNPV Audit Proof & Multiple on Invested Capital (MOIC)
Verifies that discounting all cash flows at the converged XIRR produces 0.00 net present value.
=ROUND(SUMPRODUCT(B2:B4 / ((1 + E5) ^ C2:C4)), 4)Try the example
Use the original values or change them to test your understanding.