Excel XIRR Function
last modified October 5, 2026
The XIRR function returns the internal rate of return for a series
of cash flows that occur on irregular dates. The internal rate of return is the
discount rate at which the net present value of the cash flows equals zero, so
it expresses an investment's break-even return as a single annual percentage.
This tutorial covers the syntax, the irregular-timing equation, the
sign-change requirement, and how Excel searches for a solution.
XIRR Function Basics
The XIRR function determines the discount rate that makes the net
present value (NPV) of dated cash flows equal to zero. Unlike IRR,
which assumes every period has the same length, XIRR pairs each
amount with its own date and discounts it by the exact number of days, using a
365-day year. The result is always an annualized rate, so a monthly or
quarterly schedule still reports a yearly figure.
| Component | Description |
|---|---|
| Function Name | XIRR |
| Syntax | =XIRR(values, dates, [guess]) |
| Arguments | values (required), dates (required), guess (optional) |
| Return Value | Internal rate of return as an annual decimal |
This table breaks down the essential components of the XIRR
function. It shows the function name, syntax format, arguments, and the
return value.
| Argument | Meaning |
|---|---|
| values | A range or array of numbers that correspond, one by one, to the payment dates. The series must contain at least one positive and one negative value, so that the cash flow changes sign at least once. The first value is usually the initial investment and is entered as a negative number. |
| dates | A range or array of payment dates that corresponds to
values. Both ranges must contain the same number of entries.
Use real date values created with the DATE function, or dates
that Excel recognizes, because dates stored as text can cause problems.
The first date is the reference point, or time zero. |
| guess | A number you expect the result to be close to. When omitted, Excel
starts from 0.1 (10%). Change it when the result is unexpected or when
XIRR returns #NUM!. |
The values and dates must be paired: the first amount belongs to the first
date, the second amount to the second date, and so on. XIRR does
not sort the rows for you. It measures every date against the first date and
treats that first date as time zero, so enter the cash flows in chronological
order with the earliest date first. Microsoft's documentation states that dates
may appear in any order, but a date that falls before the first date can
produce a #NUM! error, and unsorted data makes the result hard to
audit.
The XIRR Equation
XIRR finds the annual rate r that solves the equation
XNPV = 0. Because the dates are irregular, each cash flow is discounted by an
exponent equal to the number of days between its date and the first date,
divided by 365.
0 = P0/(1+r)^((d0-d0)/365) + P1/(1+r)^((d1-d0)/365) + ... + Pn/(1+r)^((dn-d0)/365)
Here d0 is the first date, dn is the last date, and
Pi is the cash flow on date di. The exponent
(di - d0)/365 is the fraction of a year between the two dates, so
the same equation works whether the cash flows are weeks, months, or years
apart. This equation cannot be rearranged to isolate r, so Excel
solves it by iteration. It starts from the guess (0.1 by default),
refines the estimate repeatedly, and stops when the result is accurate to
within 0.000001 percent. If no solution is found within 100 tries,
XIRR returns the #NUM! error.
You can confirm the definition with the XNPV function. If
A1:A5 holds the amounts, B1:B5 holds the matching
dates, and C1 contains =XIRR(A1:A5, B1:B5), then
=XNPV(C1, A1:A5, B1:B5) returns a value very close to zero.
Basic XIRR Example
This example demonstrates a simple investment scenario with irregular cash flows. The initial outlay is negative and the later returns are positive, so the cash flow changes sign once and the result is reliable.
| A | B |
|---|---|
| 1/1/2023 | -10000 |
| 4/15/2023 | 2750 |
| 10/30/2023 | 4250 |
| 2/15/2024 | 3250 |
| 6/1/2024 | 2750 |
The table shows an initial investment of $10,000 on 1/1/2023 (negative value) followed by four positive returns at irregular intervals. The dates are listed in column A and the matching amounts in column B, so the two ranges line up row by row.
=XIRR(B1:B5, A1:A5)
This formula calculates the internal rate of return for the cash flows in column B with the corresponding dates in column A. It returns approximately 0.343, or 34.3%. That is the annual rate at which the five dated cash flows have a net present value of zero.
XIRR with the Guess Parameter
The guess argument tells Excel where to begin its iterative
search. The default, 0.1, works for most investments, but supplying a value
close to the expected answer can help the solver converge when cash flows are
unusual, and it chooses which root is returned when more than one exists. This
example shows how to use it.
| A | B |
|---|---|
| 3/1/2023 | -5000 |
| 6/1/2023 | 1500 |
| 9/1/2023 | 1500 |
| 12/1/2023 | 1500 |
| 3/1/2024 | 1500 |
| 6/1/2024 | 1500 |
The table shows an investment with multiple quarterly returns. We'll pass a guess value of 0.1 (10%) explicitly, which is the same value Excel would use by default.
=XIRR(B1:B6, A1:A6, 0.1)
This formula calculates the internal rate of return with an initial guess of 10%. It returns approximately 0.759, or 75.9%. The quarterly returns are large relative to the initial outlay and arrive quickly, so the annualized rate is high. The guess parameter does not change a well-behaved result; it only steers the search when the equation is hard to solve.
XIRR for Business Investment
This example demonstrates using XIRR to evaluate a business investment with irregular cash flows over several years.
| A | B |
|---|---|
| 1/15/2022 | -250000 |
| 7/30/2022 | 45000 |
| 1/15/2023 | 55000 |
| 7/30/2023 | 65000 |
| 1/15/2024 | 75000 |
| 7/30/2024 | 85000 |
| 1/15/2025 | 95000 |
The table shows a $250,000 business investment with semi-annual returns that increase over time. The six returns arrive on two dates each year, so the gaps are not all exactly equal because of varying month lengths.
=XIRR(B1:B7, A1:A7)
This formula evaluates the business investment's performance. It returns approximately 0.318, or 31.8%. Because the returns grow and are spread over three years, the annualized figure summarizes the whole schedule in one number that can be compared with a required return or cost of capital.
XIRR for Personal Finance
This example shows how to use XIRR to calculate returns on personal investments with irregular contributions and withdrawals.
| A | B |
|---|---|
| 1/1/2023 | -10000 |
| 4/1/2023 | -2000 |
| 7/1/2023 | -1500 |
| 10/1/2023 | 5000 |
| 1/1/2024 | 12000 |
The table shows initial and additional investments (negative values) followed by withdrawals (positive values). The stream changes sign only once, from the final contribution to the first withdrawal, so the single IRR is meaningful.
=XIRR(B1:B5, A1:A5)
This formula calculates the personal investment's annualized return. It
returns approximately 0.317, or 31.7%. XIRR handles mixed
contributions and withdrawals because each amount is discounted from its own
date, no matter whether it is a deposit or a withdrawal.
XIRR for Real Estate Investment
This advanced example demonstrates using XIRR to evaluate a real estate investment with irregular income and final sale.
| A | B |
|---|---|
| 5/15/2020 | -350000 |
| 8/1/2020 | 12000 |
| 11/1/2020 | 12000 |
| 2/1/2021 | 12000 |
| 5/1/2021 | 12000 |
| 8/1/2021 | 12000 |
| 11/1/2021 | 12000 |
| 2/1/2022 | 12000 |
| 5/1/2022 | 12000 |
| 8/1/2022 | 12000 |
| 11/1/2022 | 12000 |
| 2/1/2023 | 12000 |
| 5/1/2023 | 450000 |
The table shows a real estate purchase, quarterly rental income, and final property sale. This complex cash flow pattern is ideal for XIRR analysis.
=XIRR(B1:B13, A1:A13)
This formula evaluates the real estate investment's performance over almost
three years. It returns approximately 0.219, or 21.9%. This example shows why
XIRR suits long-term, irregular cash flow scenarios: the rental
income arrives on the first of each quarter while the purchase and sale fall on
other dates, and every amount is discounted from its own point in time.
XIRR vs IRR vs XNPV
IRR, XIRR, and XNPV all discount cash
flows, but they answer different questions. IRR assumes equal
periods and returns a rate per period. XIRR accepts a list of
dates and always returns an annualized rate. XNPV also accepts
dated cash flows but returns a currency amount for a rate you supply. The three
are linked: the rate returned by XIRR is the rate at which
XNPV equals zero.
| Function | Timing | Result | Typical use |
|---|---|---|---|
| IRR | Equal periods | Rate per period | Regular project cash flows |
| XIRR | Specific dates | Annualized rate | Dated payments and portfolios |
| XNPV | Specific dates | Net present value in currency | Value of dated cash flows at a known rate |
A monthly IRR is a monthly rate, while XIRR reports a
yearly rate for the same schedule. If a project's cash flows arrive on known
calendar dates, XIRR is the more accurate choice. To check an
XIRR result, compare it with a hurdle rate by evaluating
XNPV at that hurdle: a positive XNPV means the
investment beats the required return.
Like IRR, XIRR assumes that interim cash flows are
reinvested at the return it reports, and it assumes a 365-day year. When the
cash flows change sign more than once, several mathematical roots can exist and
Excel returns only the one near the guess, so verify the answer with
XNPV. The sign-change rule is strict: without at least one
positive and one negative amount, there is no meaningful rate and Excel returns
#NUM!.
XIRR Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| All cash flows have the same sign | #NUM! error | Include at least one positive and one negative value |
| Values and dates have different sizes | #NUM! error | Make the two ranges the same length and paired row by row |
| A date is not a valid date | #VALUE! error | Use the DATE function or a real date value, not text |
| A date precedes the first date | #NUM! error | Sort the rows chronologically with the earliest date first |
| No convergence within 100 tries | #NUM! error | Supply a guess close to the expected rate |
| Alternating-sign cash flows | Unexpected rate | Multiple roots may exist; try another guess or verify with XNPV |
| Text dates entered instead of dates | Wrong rate or #VALUE! | Convert the entries to real dates with DATEVALUE or DATE |
A result that looks wrong is often a date or sign problem. When the cash flows
change sign only once, the XIRR is unique and dependable. When they
change sign several times, test the reported rate with XNPV; a
value far from zero means the guess led Excel to a different root.
The XIRR function is essential for financial analysis in Excel. It
turns a stream of dated cash flows into a single annualized break-even rate by
solving the XNPV = 0 equation, which makes investments with irregular timing
easy to compare with a required return. Use it whenever the exact calendar
dates matter, keep the values and dates aligned and in chronological order, and
remember the requirement for at least one positive and one negative amount.
Choose the guess deliberately when the pattern is unconventional, and confirm
important results with XNPV. Mastering these details turns
XIRR from a black box into a dependable decision tool.
Author
List all Excel Formulas.