Excel XIRR function: Calculate annualized return for irregular cash flows
Excel’s XIRR function uses the actual dates of cash flows to calculate an annualized internal rate of return. That is the discount rate at which the cash inflows and outflows, valued on the first date, sum to zero. For cash flows in regular periods, such as month-end payments, use Excel’s IRR function to get a per-period return.
Prepare the amounts and dates
=XIRR(values,dates,[guess])
Enter payments as negative amounts and receipts as positive amounts. You need both signs and one date per amount. Enter dates as Excel date values. When a date includes a time, XIRR uses the whole-day part.
Put the earliest date in the first row because it is the starting date for the calculation. Later rows can be out of order, but sort both columns together if you sort the data. The optional third argument is the starting guess for the calculation and defaults to 10%.
Microsoft’s XIRR function reference
Calculate XIRR with three dated cash flows
Fill columns A and B as shown below. If Excel does not recognize the displayed date format, enter the dates in the format your installation accepts or use the DATE function.
| Cell | Amount | Date cell | Date |
|---|---|---|---|
| A2 | -1000 | B2 | Jan 1, 2025 |
| A3 | 300 | B3 | Apr 1, 2025 |
| A4 | 820 | B4 | Jan 1, 2026 |
Enter the formula below in C2. On the Home tab, format the result as a percentage with two decimal places. If your regional settings use semicolons, use a semicolon between the two ranges.
=XIRR(A2:A4,B2:B4)
The result is about 15.42%. The total net inflow is 120 on an initial payment of 1000, and XIRR accounts for receiving 300 after 90 days and 820 after 365 days.
Interpret the annual rate and check it with XNPV
XIRR divides the actual elapsed days by 365 to calculate the discount period. If you pay 100 on January 1, 2025 and receive 110 on January 1, 2026, the annualized rate is 10%. Move the receipt to July 1, 2025 and the gain is still 10, but receiving it after 181 days raises the annualized rate to about 21.19%.
Pass the rate in C2 to XNPV to compute the net present value on the first date. For the three cash flows above, the result should be close to zero. Refer directly to C2 so the check uses the full precision behind the displayed 15.42%.
=XNPV(C2,A2:A4,B2:B4)
Microsoft’s XNPV function reference
Fix errors and investigate unexpected results
- #NUM! Check that the ranges have the same length, both signs are present, and no other date precedes the first date. The error can also mean the iterative calculation did not converge.
- #VALUE! Check whether Excel recognizes each date. For example, a FALSE result from
ISNUMBER(B2)suggests the date may be stored as text. TRUE confirms a number is stored, so also verify that it represents the intended date.
If payments and receipts alternate several times, more than one rate can satisfy the equation. After checking the inputs, try a different guess in the third argument and check each result with XNPV. A value near zero confirms a solution, while other solutions may still exist.

