XIRR and XNPV: dated cash flows
Calculate present value and annualized IRR using actual dates and an actual/365 convention.
Change one assumption and observe
This hypothetical example updates as you move the slider.
Cash-flow amount
Days from first date
Days from first date: 365 · Cash-flow amount: 1,100
Chart data
| Days from first date | Cash-flow amount |
|---|---|
| 0 | -1,000 |
| 365 | 1,100 |
Current substitution
A positive XNPV means discounted inflows exceed outflows at the chosen rate. Interpret XIRR alongside scale, timing and reinvestment assumptions; do not rank mutually exclusive projects by rate alone.
Why does the date of a 1,100 receipt matter?
Pay 1,000 and later receive 1,100: the gain appears to be 100 either way. But receiving it after one year ties up capital for less time than receiving it after two. Treating irregular payments as equally spaced periods can misstate the cost of waiting.
NPV and IRR work with defined, evenly spaced periods. XNPV and XIRR use dates to determine discount time. They help explain staged investments, private-investment cash flows and irregular project payments. This page uses hypothetical flows rather than imported market performance.
Convert dates into year fractions and present values
Use the first date d₀ as the base and set tᵢ=(dᵢ−d₀)/365. Dividing CFᵢ by (1+r)^tᵢ gives its value at the base date. Positive amounts are receipts; negative amounts are payments. The effective annual rate must exceed −100%.
The convention is actual days/365, including in leap years; a calendar year is not always exactly t=1. The examples use 365 days from 2025-01-01 to 2026-01-01, or 730 days to 2027-01-01, so their dates match the one-year and two-year equations.
XNPV and XIRR answer different questions
Given a user-selected hurdle, XNPV measures value added above that requirement. A one-year receipt of 1,100 gives −1,000+1,100/1.10=0 at 10%. At a 5% hurdle, XNPV is about 47.62. Zero means meeting the requirement, not receiving no cash or earning no interest.
XIRR finds the annual rate making XNPV zero: 10% in the one-year example. Delay the same receipt until day 730 and XNPV at 10% becomes about −90.91; XIRR falls to about 4.8809%, because 1,000 takes two years to become 1,100. A two-year gain of 10% is not a 10% annual return.
Why can there be multiple roots or no root?
An outflow followed by an inflow and another outflow can make the present-value curve cross zero more than once. Flows −100, +230 and −132 exactly 365 days apart have both 10% and 20% roots. Choosing the higher root does not establish that an investment is better.
Flows all carrying the same sign cannot offset to zero within the valid range. Having both signs does not guarantee a suitable root either. The tool uses a finite search: no root is not reported as 0%, and found roots are not proof that every root was found. For mutually exclusive projects, compare XNPV at a consistent hurdle and consider investment scale.
Connect the interaction to a financing decision
The control changes when the 1,100 is received, not its amount. Compare the timeline, XNPV and XIRR rather than attributing the difference to extra income. Later receipt generally has lower present value at a positive rate and spreads the same growth over a longer interval.
Real comparisons require fees, taxes, currency conversion and complete flows. A single XIRR does not arrange reinvestment of interim cash; terminal-wealth comparisons need explicit reinvestment and funding assumptions. Keep amounts within the same investment boundary so a contribution is not mistaken for income.
Show formal definition and formula
r > −100%; Actual/365.
- CFᵢ / dᵢ
- Cash-flow amount and calendar date.
- d₀ / r
- First date / effective annual discount rate, decimal; day count divided by 365.
Scope and limitations
Dates cannot precede the first row. Uses UTC calendar days and a fixed 365 denominator, including leap years. Same-day flows net together. XIRR searches log(1+r)∈[−16,16], not all possible roots; multiple sign changes need care.
Frequently asked questions
Is the denominator 366 in leap years?
No. Actual days are divided by a fixed 365 under the Excel dated-flow convention; a leap day increases the numerator.
Does finding one XIRR prove uniqueness?
No. A finite search may miss roots. Multiple sign changes need care; also inspect XNPV at a chosen rate.
You can now explore…
- XNPV discounts each cash flow to the first date; XIRR is an effective annual rate making that sum zero.
- Dates cannot precede the first row. Uses UTC calendar days and a fixed 365 denominator, including leap years. Same-day flows net together. XIRR searches log(1+r)∈[−16,16], not all possible roots; multiple sign changes need care.