Calculate XIRR, the annualized return on cash flows made on irregular dates, and XNPV at your discount rate, matching Excel's actual/365 method.
By AdminDrafted with AI writing toolsUpdated Checked examples: 5
Over 731 days these flows earn 11.71% a year (XIRR). Discounted at 8% a year to 2024-01-15, they are worth $788.66.
| Date | Days from start | Years (÷ 365) | Amount | Discount factor | Present value |
|---|---|---|---|---|---|
| 2024-01-15 | 0 | 0 | −$10,000.00 | 1 | −$10,000.00 |
| 2024-07-01 | 168 | 0.4603 | −$2,500.00 | 0.9652 | −$2,412.99 |
| 2025-03-10 | 420 | 1.1507 | $1,500.00 | 0.9153 | $1,372.88 |
| 2026-01-15 | 731 | 2.0027 | $13,800.00 | 0.8572 | $11,828.78 |
Flows are sorted by date; the earliest is t = 0. Actual days over 365, as in Excel's XIRR and XNPV, even across leap years.
Bracketed on a rate grid, narrowed by bisection, then refined with Newton's method: 16 steps, |XNPV| = 1 × 10⁻⁴⁵ at the root.
XIRR is the yearly rate r that makes dated cash flows net to zero when each one is discounted by (1 + r)^(days since the first flow ÷ 365). It is the money-weighted return for an investment with deposits and withdrawals on irregular dates, such as a fund topped up now and then or a monthly SIP. XNPV applies the same discounting at a rate you choose and values every flow on the earliest date.
With the defaults, 10,000 invested on 15 January 2024, 2,500 more on 1 July 2024, 1,500 taken out on 10 March 2025 and a value of 13,800 on 15 January 2026, the XIRR is 11.71% a year. Discounted at 8%, the flows are worth 788.66 on the first date, and the net gain is 2,800 over 731 days.
Enter money you put in as negative and money you take out, including today's value, as positive. As in Excel, days are counted exactly and divided by 365, leap years included.
XIRR is the annual rate r at which dated cash flows net to zero, each discounted by (1 + r)^(days from the first flow ÷ 365). 1,000 invested on 1 January 2023 and worth 1,100 a year later gives 10%. It suits money moved on irregular dates.
Take 10,000 invested on 15 January 2024, 2,500 more on 1 July 2024, 1,500 taken out on 10 March 2025 and a value of 13,800 on 15 January 2026. Count each flow's days from the first date and divide by 365:
XIRR is the rate that makes −10,000 − 2,500 ÷ (1 + r)^0.4603 + 1,500 ÷ (1 + r)^1.1507 + 13,800 ÷ (1 + r)^2.0027 equal zero. Solving by trial gives 11.71% a year.
The net gain is 2,800 on 12,500 paid in, or 22.4%. That simple figure ignores timing: the 2,500 was invested for only 563 days, and 1,500 came back early.
XNPV uses the same discounting at a fixed rate and values every flow on the earliest date. At 8%, these flows are worth 788.66, so they beat an 8% alternative. At the XIRR itself, XNPV is zero.
Days are counted exactly and always divided by 365, so a year containing 29 February counts as 366 ÷ 365 years. Both rows below invest 1,000 and receive 1,100, with XNPV at 10%:
| Actual day interval | XIRR (%) | XNPV at 10% |
|---|---|---|
| 365 | 10.000000 | 0.00 |
| 366 | 9.971359 | -0.26 |
The first runs from 1 January 2023 to 1 January 2024; the second spans 2024, so XIRR is 1.1^(365/366) − 1 = 9.971359%. Excel's XIRR and XNPV functions use the same actual/365 count.
Microsoft's published example, −10,000 on 1 January 2008 followed by four receipts over 456 days, gives 37.34%; to six decimals the rate is 37.336253%.
XIRR measures the return on your own money, so the timing of your deposits changes it. A fund's published return measures the fund's growth regardless of when you bought, which is why the two figures differ.
2024-01-15 -10000.# are ignored, so you can label entries.The rate search runs from −99.9% to 1,000% a year. A loss is a negative XIRR: 1,000 that is worth 900 a year later gives −10%.
For equally spaced periods, use the NPV and IRR calculator. The ROI and CAGR calculator handles a single start and end value, and the SIP calculator projects a monthly investment plan.
Checked against: Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. exact decimal arithmetic bisection gives 37.3362533519% and 2,086.6476
Checked against: 365 days = 1 year under actual/365, so 1100/1000 − 1 = 10% and XNPV at 10% is 0
Checked against: 1.1^(365/366) − 1 = 9.9713585934%
Checked against: Inflows equal the outflow, so NPV at 0% is 0 by definition
Both find the rate at which discounted cash flows sum to zero, but IRR assumes the flows are one period apart, while XIRR uses the actual date of each flow and returns an annual rate. For the default flows, spread over 731 days at uneven intervals, XIRR is 11.71% a year; an IRR on the same amounts would treat them as four equally spaced periods and give a different, per-period rate.
=XIRR(values, dates, [guess]) searches for the rate iteratively, measuring time as days ÷ 365, and returns #NUM! if it has not converged after 100 tries. Microsoft's example, −10,000 on 1 January 2008 followed by 2,750, 4,250, 3,250 and 2,750 over the next 456 days, gives 37.34%, which this page reproduces to 37.336253%.
CAGR describes one deposit and one final value; XIRR handles any number of deposits and withdrawals. With a single flow in and out they agree when the gap is exactly 365 days: 1,000 growing to 1,100 in a year is 10% either way. Add a second deposit and only XIRR accounts for how long each amount was invested.
Because XIRR divides the actual number of days by 365, a calendar year that contains 29 February counts as 366 ÷ 365 years. 1,000 growing to 1,100 from 1 January 2024 to 1 January 2025 therefore gives 1.1^(365/366) − 1 = 9.97%, not 10%, while the same growth across 2023 gives exactly 10%.
List every installment as a negative amount on the date it was invested, then add the current value as a positive amount on today's date. Twelve installments of 5,000 on the first of each month in 2025, worth 64,000 on 1 January 2026, give an XIRR of 12.48%: the 4,000 gain is large relative to the average time each installment was invested, about half a year.
For planning only. Lenders, tax authorities and markets apply their own rounding, fees and rules; confirm figures with them before you commit.
This calculator includes 5 worked examples with independently sourced answers. They run in the test suite, and you can run them here too.