# XIRR calculator (irregular cash flows)

> Calculate XIRR, the annualized return on cash flows made on irregular dates, and XNPV at your discount rate, matching Excel's actual/365 method.

Interactive version: https://www.calcopenly.com/finance/xirr-calculator
Subject: Finance calculators

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.

## Inputs

- **Dated cash flows**: One per line: date as YYYY-MM-DD, then the amount. Money you put in is negative, money you take out (or the current value) is positive.
- **Discount rate for XNPV (per year)**

## Results

- XIRR (annualized) — main result
- XNPV at the discount rate
- Total paid in
- Total received
- Net gain
- Days from first to last flow

## Formula

$$
\sum_{i} \frac{P_i}{(1+XIRR)^{(d_i - d_0)/365}} = 0,\qquad XNPV = \sum_{i} \frac{P_i}{(1+r)^{(d_i - d_0)/365}}
$$

## Worked examples

### Microsoft XIRR example

- Dated cash flows: 2008-01-01 -10000 / 2008-03-01 2750 / 2008-10-30 4250 / 2009-02-15 3250 / 2009-04-01 …
- Discount rate for XNPV (per year): 9%
- **XIRR (annualized): 37.336253%**
- **XNPV at the discount rate: 2,086.65**
- **Days from first to last flow: 456**
- Checked against: Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. Python decimal bisection gives 37.3362533519% and 2,086.6476

### Exactly one non-leap year

- Dated cash flows: 2023-01-01 -1000 / 2024-01-01 1100
- Discount rate for XNPV (per year): 10%
- **XIRR (annualized): 10%**
- **XNPV at the discount rate: 0.00**
- Checked against: 365 days = 1 year under actual/365, so 1100/1000 − 1 = 10% and XNPV at 10% is 0

### A leap year spans 366 days

- Dated cash flows: 2024-01-01 -1000 / 2025-01-01 1100
- Discount rate for XNPV (per year): 10%
- **XIRR (annualized): 9.971359%**
- **Days from first to last flow: 366**
- Checked against: 1.1^(365/366) − 1 = 9.9713585934% (Python decimal, closed form and bisection agree)

### Break-even flows give 0%

- Dated cash flows: 2024-01-01 -1000 / 2024-07-01 400 / 2025-03-01 600
- Discount rate for XNPV (per year): 0%
- **XIRR (annualized): 0%**
- **XNPV at the discount rate: 0.00**
- **Net gain: 0.00**
- Checked against: Inflows equal the outflow, so NPV at 0% is 0 by definition

### Order of lines doesn't matter

- Dated cash flows: 2009-04-01 2750 / 2008-01-01 -10000 / 2009-02-15 3250 / 2008-03-01 2750 / 2008-10-30 …
- Discount rate for XNPV (per year): 9%
- **XIRR (annualized): 37.336253%**
- **XNPV at the discount rate: 2,086.65**
- Checked against: Microsoft XIRR/XNPV examples with the rows shuffled; flows are sorted by date first

## Questions

### What is XIRR and how is it different from IRR?

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.

### How does Excel calculate XIRR?

=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%.

### What is the difference between XIRR and CAGR?

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.

### Why does XIRR change in a leap year?

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%.

### How do I calculate XIRR for a SIP or a fund with top-ups?

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.

### How accurate is the XIRR calculator?

Accuracy depends on your inputs and the method's assumptions. Decimal arithmetic uses 50 significant digits, but estimates, numerical methods and source data can be less precise; the displayed rounding does not remove those limits. It is checked against 5 worked examples whose answers come from independent sources; for example, “Microsoft XIRR example” is checked against Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. Python decimal bisection gives 37.3362533519% and 2,086.6476.

### Where does the method come from?

Microsoft Excel XIRR function; Microsoft Excel XNPV function.

## Sources

- [Microsoft Excel XIRR function](https://support.microsoft.com/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d)
- [Microsoft Excel XNPV function](https://support.microsoft.com/office/xnpv-function-1b42bbf6-370f-4532-a0eb-d67c16b664b7)

_Note: financial information, not professional advice._