XIRR for Irregular Cash-Flow Dates

Excel Formulas › Financial

All versionsXIRRXNPV

Real investments rarely pay on a tidy yearly schedule. XIRR calculates the annualized return when cash flows land on specific, uneven dates — the right tool whenever the gaps between flows aren’t equal.

Quick formula: for amounts in B2:B6 with their dates in A2:A6:
=XIRR(B2:B6, A2:A6)
Pair each cash flow with its actual date; XIRR returns the true annualized return accounting for the exact timing.

Functions used (tap for the full reference guide):

XIRRXNPV

The example

An investment with cash flows on irregular dates.

AB
1DateCash flow
21/15/2025-$1,000
33/02/2025$300
49/20/2025$400
52/10/2026$450
6XIRR:~13.4%

The formula

The date-aware annualized return:

=XIRR(B2:B5, A2:A5) // uses the exact dates → ~13.4%

How it works

XIRR is IRR with real dates:

  1. Give it two ranges: the cash flows (first one negative) and the matching dates.
  2. It finds the annual rate that discounts every flow to a net present value of zero — weighting each by its actual date, not an assumed equal period.
  3. Because timing is exact, XIRR is more accurate than IRR for sporadic investments, dividends, or capital calls.
  4. Its sibling XNPV(rate, values, dates) gives the date-aware net present value at a rate you choose.

XIRR vs IRR: use IRR only when flows are exactly one period apart; use XIRR whenever the dates are irregular (the common real-world case). XIRR returns an annualized rate regardless of how short or long the gaps are.

Try it: interactive demo

Live demo

Adjust the final inflow; watch the annualized XIRR.

XIRR:

Variations

Date-aware NPV

XNPV at a chosen rate:

=XNPV(0.1, B2:B6, A2:A6)

Provide a guess

Help convergence:

=XIRR(B2:B6, A2:A6, 0.1)

Equal periods? Use IRR

Simpler when flows are evenly spaced:

=IRR(B2:B6)

Pitfalls & errors

#NUM! without a sign change. Like IRR, XIRR needs at least one negative and one positive flow; add a guess for hard cases.

Dates must be real dates and align one-to-one with the values. Text dates or mismatched counts error.

First date is the baseline. XIRR measures everything relative to the earliest date; it doesn’t need to be sorted, but the first cash flow is usually the investment.

Practice workbook

📊
Download the free XIRR for Irregular Cash-Flow Dates practice workbook
Dated cash flows with XIRR (result shown), the XNPV and IRR variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate return for cash flows on irregular dates?
Use XIRR with values and their dates: =XIRR(B2:B6, A2:A6). It returns the annualized rate accounting for the exact timing of each flow.
What's the difference between IRR and XIRR?
IRR assumes cash flows are equally spaced; XIRR uses explicit dates, so it's accurate for irregular timing, which is the usual real-world situation.
How do I get the present value with specific dates?
Use XNPV: =XNPV(rate, values, dates) discounts each flow by its actual date to give a date-aware net present value.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Internal rate of return (IRR) · Net present value (NPV) · CAGR

Function references: XIRR · XNPV