XIRR Function
Financial
Work out the annual rate of return on cash flows that happen on real, uneven dates.
Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards
XIRR gives you the annual rate of return on a set of cash flows that happen on specific dates. Unlike IRR, it does not assume the money moves at even intervals, which makes it the right tool for real payments: a purchase in January, income in April, a sale the following December. This guide covers the syntax, the sign convention, a worked example and the errors you are most likely to meet.
In this guide
XIRR syntax
=XIRR(values, dates, [guess])| Argument | Required? | What it does |
|---|---|---|
values |
Required | The cash flows. Money going out is negative and money coming in is positive. There must be at least one of each. |
dates |
Required | The date of each cash flow, matching values row for row. The first date marks the start, and no other date may be earlier. |
guess |
Optional | A starting estimate for the rate. Excel uses 10% if you leave it out, which works nearly every time. |
XIRR returns an annual rate, worked out on a 365-day year. It is available in every current version of Excel.
Why dates matter: XIRR versus IRR
IRR assumes every cash flow is exactly one period apart, whether that period is a month or a year. Real payments are rarely that tidy. XIRR uses the actual number of days between each payment and the first one, so money that arrives later counts for less than money that arrives sooner.
Behind the scenes, XIRR searches for the rate r at which the sum of value / (1 + r) ^ ((date - first_date) / 365) across every cash flow equals zero. Because the exponent is measured in years, the answer is an annual, compounded rate. A result of 20% means the same thing whether the flows cover four months or four years.
Worked example: equipment for a studio
A podcast studio buys a $12,000 camera and lighting kit on 15 January 2026 and hires it out to other creators. Hire income arrives whenever jobs come in, and the last entry includes selling the kit second-hand.
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Date | Cash flow | XIRR | 20.94% | ||
| 2 | 15-Jan-26 | ($12,000.00) | IRR (per period) | 6.70% | ||
| 3 | 30-Apr-26 | $2,100.00 | ||||
| 4 | 31-Aug-26 | $2,600.00 | ||||
| 5 | 15-Dec-26 | $2,900.00 | ||||
| 6 | 20-May-27 | $3,200.00 | ||||
| 7 | 10-Dec-27 | $4,000.00 |
E1 holds =XIRR(B2:B7, A2:A7), formatted as a percentage with two decimals. E2 holds =IRR(B2:B7) for comparison.
The kit returns 20.94% a year. Income totals $14,800 against $12,000 spent, but it arrives over almost two years, and the annual rate is what lets you compare that with other uses of the money.
IRR on the same values returns 6.70%. That is a rate per period, and IRR has assumed every period is the same length. Here the gaps run from 105 to 204 days, so 6.70% does not describe a month, a quarter or a year.
Test the timing
Move the final $4,000 from 10-Dec-27 to 10-Jun-28 and XIRR drops to 18.75%, because the same money arrives six months later. IRR stays at 6.70%, since it never looks at the dates.
Showing decimals
Press Ctrl + Shift + % to apply the Percentage format, then click Increase Decimal (Home > Number) twice to show 20.94% instead of 21%.
The sign convention
XIRR, like all of Excel's cash flow functions, reads the direction of each flow from its sign:
- Negative for money you pay out: the purchase price, upgrades, extra costs.
- Positive for money you receive: income, refunds, the sale price at the end.
You need at least one of each. A list that is all positive has no rate of return, because nothing was put in, and XIRR returns #NUM!. Costs and income that fall on the same day can be netted into one row or listed as separate rows with the same date. XIRR handles both.
Fixing #NUM! and #VALUE!
| Error | Likely cause | Fix |
|---|---|---|
| #NUM! | No negative value, or no positive value | Check the signs. Costs must be negative. |
| #NUM! | A date is earlier than the first date | Put the starting cash flow in the first row, or sort the rows by date. |
| #NUM! | values and dates are different sizes | Make both ranges cover the same rows. |
| #NUM! | No answer found after 100 attempts | Enter a guess closer to the rate you expect, such as 0.5 or -0.5. |
| #VALUE! | A date is stored as text | Re-enter it as a real date, or convert it with DATEVALUE. |
Excel truncates dates to whole days, so a time of day attached to a date makes no difference. If you do need a guess, it only changes where Excel starts searching, not the answer:
=XIRR(B2:B7, A2:A7, 20%)Still returns 20.94% for the studio example. A guess matters only when the default 10% start fails, or when unusual cash flows that switch sign several times have more than one possible answer.
IRR and MIRR
For evenly spaced flows, =IRR(values, [guess]) is simpler. It returns the rate for one period, so a monthly IRR needs converting to an annual figure:
=(1 + IRR(B2:B13))^12 - 1Turns the IRR of twelve monthly cash flows into an annual rate.
=MIRR(values, finance_rate, reinvest_rate) also works on evenly spaced flows, but lets you set the interest rate paid to fund the negative cash flows and the rate earned when the positive ones are reinvested. Excel has no dated version of MIRR.
To check any XIRR, feed it into XNPV. The net present value at the XIRR rate is zero:
=XNPV(E1, B2:B7, A2:A7)Returns $0.00 for the studio example, give or take a fraction of a cent, which confirms the 20.94% in E1.
And to find the final sale price that would hit a target return, Goal Seek can adjust the cell for you.
Frequently asked questions
What is the difference between IRR and XIRR?
IRR assumes evenly spaced cash flows and returns a rate per period. XIRR uses the actual dates and always returns an annual rate.
Does XIRR use 365 or 366 days?
Always 365, including in leap years.
Do the dates need to be in order?
Only the first one matters: it must be the earliest, because it marks the start. The rest can be in any order, though sorted dates are easier to check.
Why is my XIRR result so large?
Short periods magnify an annual rate. A 5% gain over 30 days works out at about 81% a year once compounded. Check the dates too: a mistyped year can squeeze two years of cash flows into a few weeks.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā