Excel XIRR With Dates: Formula, Examples, and Fixes

To calculate a return from cash flows that happen on specific, uneven dates, use Excel’s XIRR function: with cash flows in B2:B4 and their matching dates in A2:A4, enter =XIRR(B2:B4,A2:A4). XIRR returns an annualized rate; unlike IRR, it accounts for the actual spacing between dates.

Why XIRR is the right function for dated cash flows

Excel’s standard IRR function assumes the cash flows occur at regular intervals, such as once a month or once a year. It does not read a date column. If you use IRR on payments that happened on irregular dates, the result treats each row as one equal period, whether the time between rows was a week or a year.

XIRR is designed for a series of cash flows with corresponding dates. It is useful for investment contributions and withdrawals, project costs and receipts, and other situations where amounts arrive on different days. The dates do not need to be evenly spaced. The result is an annualized rate, which makes it easier to compare with other yearly rates, but it is not simply the total percentage gain over the investment.

Cash flows use the perspective of the investor or project being measured: money paid out is generally negative, and money received is positive. A typical investment begins with a negative amount and later has positive proceeds. XIRR needs at least one cash flow of each sign to find a meaningful rate.

How XIRR uses dates

XIRR finds the rate that makes the present value of the dated cash flows add up to zero. In simplified terms, it discounts each cash flow according to how many days have passed since the first date, using a 365-day year. A receipt a few months after the initial investment is therefore discounted for less time than a receipt several years later.

The function uses this syntax: XIRR(values,dates,[guess]). The values and dates arguments are required; guess is optional. Each amount must line up with its date in the same position. For example, the second value belongs to the second date, not to a date inferred from the row number. The date at the start of the series is the reference date for the calculation.

XIRR uses an iterative calculation to find a rate that satisfies the cash-flow equation. Excel starts with a default guess of 10% when you omit the optional argument. That default is only a starting point for the search, not an assumption that the actual return is 10%.

Prepare the worksheet

Enter one cash flow per row, with a real Excel date beside each amount. A simple layout is dates in column A and amounts in column B. Include the initial investment as a negative number, then enter later contributions as negative amounts and withdrawals or sale proceeds as positive amounts, according to the perspective you are measuring.

Put the transaction dates in A2:A20 and the corresponding cash flows in B2:B20.
Check that each amount is paired with the correct date and that the rows are in chronological order.
Enter the XIRR formula in an empty cell, using the same-sized ranges for dates and values.
Format the result as a percentage, and choose the number of decimal places that makes sense for your analysis.

Excel dates are stored as serial numbers, even when displayed in a familiar date format. Type dates as dates or use cells that already contain valid Excel dates; text that merely looks like a date may not be recognized as a date value. To check, select a date cell and apply a date format from Home > Number. If it continues to display as text, convert the data before relying on the result.

Worked example: an investment with two receipts

Suppose an investment costs $10,000 on January 1, 2024, pays $4,000 on December 31, 2024, and pays another $7,000 on December 31, 2025. Enter the dates in A2:A4 and the matching cash flows in B2:B4:

A2: January 1, 2024; B2: -10000
A3: December 31, 2024; B3: 4000
A4: December 31, 2025; B4: 7000

With those dates in A2:A4 and cash flows in B2:B4, enter =XIRR(B2:B4,A2:A4). The result is approximately 6.02% per year. The first receipt is 365 days after the initial investment and the final receipt is 730 days after it, so the calculation solves approximately -10000 + 4000/(1+r) + 7000/(1+r)^2 = 0. At a rate of about 6.023%, the discounted receipts total about $10,000, offsetting the initial outflow.

This example also illustrates why the answer is not the simple total gain divided by the initial investment. The investor receives $11,000 in total against a $10,000 outlay, a $1,000 nominal gain, but the timing of the two receipts matters. XIRR expresses the time-adjusted result as an annualized rate.

XIRR compared with IRR and CAGR

Choose IRR when cash flows are equally spaced and each row represents the same period. For instance, monthly cash flows should be treated as monthly periods; the raw IRR result is then a rate per month, not automatically an annual rate. XIRR is usually the more appropriate choice when actual transaction dates differ or when a reporting standard calls for a date-based annualized return.

CAGR is different again. It describes the annualized growth rate between a beginning value and an ending value over a stated period, typically without accounting for additional investments or withdrawals along the way. XIRR can incorporate multiple dated contributions and receipts. If a portfolio has no intermediate cash flows, a CAGR-style calculation may be enough; if money moves in and out on multiple dates, XIRR is generally more informative.

Neither function tells the whole story on its own. A rate does not show the size of the investment, the amount of risk, or whether a return is before or after fees and taxes. Keep the underlying cash flows and the assumptions visible alongside the percentage.

Dates, signs, and data conventions

Reliable results depend on consistent inputs. Keep the rows in date order, and make sure the dates and amounts refer to the same transactions. Excel uses the first date as the starting point for the date intervals. If a later row contains a date earlier than that starting date, XIRR can return an error. If several transactions occur on the same day, you can usually combine them into one net cash flow for that date, provided doing so preserves the perspective and purpose of the analysis.

Use signs consistently. For an investor’s return, an amount invested is negative and money returned to that investor is positive. If you reverse the signs for every cash flow, the result is mathematically equivalent in many ordinary cases, but a consistent investor or project perspective makes the worksheet much easier to interpret. Do not enter all amounts as positive: without both positive and negative values, there is no investment return for XIRR to solve.

Also decide what the cash flows represent. If the amounts exclude fees, taxes, or inflation, XIRR does not add those adjustments for you. Include relevant amounts in the cash-flow series if your goal is to measure a net return, and label the result clearly so readers know what has been included.

Common XIRR errors and how to fix them

#NUM! can mean Excel could not find a solution using its starting guess, or that the cash-flow pattern does not produce a usable solution. First verify that the ranges contain at least one negative and one positive amount, that the dates are valid, and that they are aligned with the values. If the pattern includes several changes from negative to positive and back again, there may be more than one mathematical solution; different starting guesses can lead the iterative search toward different roots.

When the data is sound but Excel still returns #NUM!, provide an optional guess near a plausible rate. With dates in A2:A4 and matching values in B2:B4, =XIRR(B2:B4,A2:A4,0.05) starts the search at 5%. This does not force the answer to equal 5%; Excel continues iterating to seek a rate that satisfies the cash flows. Try a different reasonable guess if necessary, and investigate the cash-flow pattern rather than accepting a result without checking it.

#VALUE! commonly points to an invalid argument, such as text in a required value or date cell. Confirm that the selected ranges have matching dimensions and that dates are stored as Excel dates, not unconverted text. A result that seems implausibly high or low is often a sign error, a mismatched row, a missing cash flow, or a date entered in the wrong year.

Interpreting and checking the result

A result of 6.02% means XIRR found an annualized rate that balances the dated cash flows under its 365-day convention. It does not mean the investment earned exactly 6.02% in every calendar year, nor does it guarantee that future returns will match. When comparing two investments, check that both calculations use a consistent perspective, include comparable fees and cash flows, and cover an appropriate period.

For a basic audit, check the cash-flow signs, date order, and transaction totals, then inspect whether the output changes dramatically when you correct a questionable date. You can also use XIRR alongside an independent cash-flow review: discount each amount using the reported rate and confirm that their total is close to zero. Small differences can result from rounding the displayed rate; the underlying cell retains more precision than the formatted percentage shows.

Frequently Asked Questions

Can Excel IRR use a date column?
IRR does not use dates; it assumes equal periods between values. Use XIRR when you have actual dates or unequal gaps between cash flows.

Does XIRR return a monthly or annual rate?
XIRR returns an annualized rate, based on a 365-day year. The dates determine the fractional year for each cash flow.

Do the dates have to be evenly spaced?
No. XIRR is intended for unevenly spaced cash flows. Each date must still match its corresponding value, and keeping the transactions in chronological order helps prevent errors.

Why does XIRR show #NUM!?
It may not have converged from its starting guess, or the inputs may not yield a usable solution. Check signs, dates, and matching ranges; then try a plausible optional guess if the data is correct.

Can XIRR calculate a return with no initial investment?
It can calculate from a series with positive and negative cash flows even if there is no single initial lump-sum investment. The signs must represent the cash-flow perspective consistently, and at least one value of each sign is required.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.