Excel functions / Financial
XIRR function
The yearly rate of return for cash flows that fall on any dates.
Syntax
=XIRR(values, dates, [guess])
Arguments
values: The cash flows: at least one negative and one positive.dates: The date of each cash flow.guess(optional): A starting guess (default 10%).
When to use it
XIRR gives the yearly rate of return for money that goes in and comes out on any dates: an investment topped up now and then, a property bought and sold, a loan repaid unevenly.
IRR assumes the cash flows are exactly one period apart. XIRR reads the real dates, so use it whenever the gaps are not equal. The result is always a rate per year.
Examples
The yearly return on dated cash flows
A B C D 1 Date Cash flow 2 2025-01-01 -10000 16.3% 3 2025-06-15 2500 4 2026-01-01 4000 5 2026-09-30 5500 In D2:
=XIRR(B2:B5, A2:A5)→ 16.3%Money paid out is negative, money received is positive.
Check it: at that rate the cash flows are worth nothing today
A B C D 1 Date Cash flow 2 2025-01-01 -10000 0 3 2025-06-15 2500 4 2026-01-01 4000 5 2026-09-30 5500 In D2:
=ROUND(XNPV(XIRR(B2:B5, A2:A5), B2:B5, A2:A5), 2)→ 0XIRR is the rate at which XNPV comes to zero. That is its definition.
Common mistakes
- No negative or no positive value. A return needs money going out and money coming back. With only one of the two, XIRR gives #NUM!.
- Dates stored as text. XIRR needs real dates. If a date is left-aligned in its cell it is probably text; retype it or convert it with DATEVALUE.
- A date before the first one. The first date is the starting point, and no other date may be earlier. The rest can be in any order.
- Reading the result as a total. XIRR is a rate per year. 16.3% over two years is not a 16.3% gain in total.
Common questions
What is the difference between IRR and XIRR?
IRR assumes every cash flow is one equal period after the last and gives a rate per period. XIRR takes the date of each cash flow and gives a yearly rate, so it is the one to use when payments are not evenly spaced.
XIRR in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: XIRR
- Danish: INTERN.RENTE
- Dutch: IR.SCHEMA
- Finnish: SISÄINEN.KORKO.JAKSOTON
- French: TRI.PAIEMENTS
- German: XINTZINSFUSS
- Hungarian: XBMR
- Italian: TIR.X
- Norwegian: XIR
- Polish: XIRR
- Portuguese (Brazil): XTIR
- Portuguese (Portugal): XTIR
- Russian: ЧИСТВНДОХ
- Spanish: TIR.NO.PER
- Swedish: XIRR
- Turkish: AİÇVERİMORANI
Related functions
- XNPV: Net present value of cash flows that fall on any dates, not evenly spaced ones.
- IRR: Internal rate of return for a series of cash flows.
- NPV: Net present value of future cash flows at a discount rate.
- MIRR: Rate of return when money borrowed and money reinvested earn different rates.
- RRI: The steady growth rate per period that turns one amount into another (CAGR).