The formulas
- t_i = (day_i - day_1) / 365
- XNPV = sum of A_i / (1 + r)^t_i
- XIRR = r that makes XNPV = 0 (solved by bisection)
How it works
Regular NPV and IRR assume one cash flow per evenly spaced period. Real investments pay on irregular dates: a deposit in January, a draw in March, a payout in October. XNPV and XIRR discount each flow by its exact time from the first flow, measured in days over a 365-day year, which is the spreadsheet convention.
Enter each date as a day number counted from one start date, usually day 0 at the first flow. Time is measured from flow 1, so flow 1 sets time zero. The example is the common textbook set of flows from January 1 of a leap year through April 1 of the next year, which gives XIRR of 37.34 percent and XNPV of 2,086.65 at 9 percent. Rows with a zero or blank amount are skipped.
Illustrative figures only. Check the result against your firm's methodology before relying on it.
Common questions
How do I turn dates into day offsets?
Use a days-between-dates count from the first flow date to each later date. Count leap days.
Why 365 and not 360?
XNPV and XIRR divide day counts by 365 in every common spreadsheet. This keeps results comparable.
Does flow 1 have to be the outflow?
No. Flow 1 only sets time zero. Any flow can be negative.
Formulas on this page are the standard definitions.
Runs in your browser. The figures you type are not sent to CORAA or stored. Check the result against your firm's methodology before relying on it.