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

Phiên bản tương tác: https://www.calcopenly.com/vi/finance/xirr-calculator
Chủ đề: Máy tính tài chính

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.

## Dữ liệu đầu vào

- **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)**

## Kết quả

- XIRR (annualized) — kết quả chính
- XNPV at the discount rate
- Total paid in
- Total received
- Net gain
- Days from first to last flow

## Công thức

$$
\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}}
$$

## Ví dụ có lời giải

### 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**
- Nguồn đối chiếu: 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**
- Nguồn đối chiếu: 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**
- Nguồn đối chiếu: 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**
- Nguồn đối chiếu: 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**
- Nguồn đối chiếu: Microsoft XIRR/XNPV examples with the rows shuffled; flows are sorted by date first

## Câu hỏi

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

### “XIRR calculator (irregular cash flows)” chính xác đến mức nào?

Độ chính xác phụ thuộc vào dữ liệu nhập và giả định của phương pháp. Phép tính thập phân dùng 50 chữ số có nghĩa, nhưng ước lượng, phương pháp số và dữ liệu nguồn có thể kém chính xác hơn; làm tròn khi hiển thị không loại bỏ các giới hạn đó. Ví dụ có lời giải đã đối chiếu với nguồn độc lập: 5. Ví dụ, “Microsoft XIRR example” được kiểm tra bằng Microsoft XIRR documentation: 0.373362535; XNPV documentation at 9%: 2,086.65. Python decimal bisection gives 37.3362533519% and 2,086.6476.

### Phương pháp này lấy từ đâu?

Microsoft Excel XIRR function; Microsoft Excel XNPV function.

## Nguồn

- [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)

_Chỉ dùng để lập kế hoạch. Bên cho vay, cơ quan thuế và thị trường áp dụng cách làm tròn, phí và quy tắc riêng; hãy xác nhận số liệu với họ trước khi cam kết._