Use Excel’s XIRR function when an investment’s cash flows occur on uneven dates. Put each contribution or other outflow in one column as a negative number, put each distribution or proceeds amount in the same row of a second column as a positive or negative number as appropriate, and place the actual date beside every amount. Then enter =XIRR(values, dates), such as =XIRR(A2:A6,B2:B6). Excel returns an annualized rate based on the elapsed days between dates, using a 365-day year.
The result is the rate that makes the net present value of those dated cash flows equal to zero. It is not the same calculation as periodic IRR: use IRR when periods are equal, and XIRR when the calendar dates are irregular.
As an Amazon Associate I earn from qualifying purchases.
What XIRR calculates
Microsoft defines XIRR as the function that returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. In practical terms, it answers this question: what single annual rate would make the present value of every contribution, withdrawal, distribution, and final proceeds amount balance to zero when each is discounted from its actual date?
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
The syntax is XIRR(values, dates, [guess]). The values range contains the cash amounts, the dates range contains the corresponding dates, and guess is an optional starting estimate. If you omit the guess, Excel assumes 0.1, or 10%. See Microsoft’s XIRR function documentation for the documented syntax and behavior.
#1 Best Overall
Conceptually, XIRR solves the dated cash-flow equation:
0 = Σ cash flowi / (1 + rate)(datei − first date)/365
The 365 in that expression matters. XIRR uses the actual number of days between dates and a 365-day year, rather than treating every interval as an equal month or quarter. The returned percentage is already annualized; do not multiply it by 12 merely because some transactions happened monthly.
When to use XIRR instead of IRR
Choose the function according to the timing assumption built into your data. Periodic IRR treats each row as one equal period after the previous row. XIRR uses the calendar dates supplied for each row.
| Function or measure | Timing assumption | What it reports | Best use |
|---|---|---|---|
IRR |
Equal periods, such as every month or every year | A rate that makes periodic net present value equal to zero | A schedule whose intervals are genuinely regular |
XIRR |
Actual dates, which may be uneven | An annualized rate that makes dated net present value equal to zero | Investments with irregular contributions, distributions, or exit dates |
NPV or XNPV |
A discount rate is supplied by you | The value created or lost at that chosen rate | Testing an investment against a hurdle rate rather than solving for a rate |
For example, a monthly savings plan with a deposit on the same day of every month can be modeled with IRR if every period is equal. A private investment with contributions on 5 January, 22 February, and 17 July, followed by a distribution in December, should use XIRR because the elapsed days differ.
IRR and XIRR are therefore not interchangeable labels for the same annual return. Applying IRR to uneven dates silently replaces the real calendar spacing with an equal-period assumption. XIRR preserves the timing information that affects compounding and discounting. Microsoft’s comparison of NPV and IRR methods is available in its NPV and IRR in Excel guide.
Set up the cash-flow table correctly
Before writing the formula, build a two-column schedule in which every amount has exactly one matching date. A simple layout is:
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteRank #2
- Ideal for Gifting
- Ideal for a bookworm
- Compact for travelling
| Cell or column | Contents | Sign convention |
|---|---|---|
| A1 | Cash flow | Header only |
| B1 | Date | Header only |
| A2 downward | Investment amounts, contributions, withdrawals, distributions, or proceeds | Outflows are negative; inflows are positive |
| B2 downward | The date on which the amount in the same row occurred | Use real Excel dates or formulas that return dates |
Use the correct signs
Record money leaving the investor as a negative value. An initial purchase of 10,000 is -10000; a later contribution of 2,000 is -2000. Record money received by the investor as a positive value, such as a 4,500 distribution or sale proceeds. From the investor’s perspective, the schedule must contain at least one negative and at least one positive amount. Without both signs, there is no conventional internal rate for Excel to solve.
Keep each amount paired with its date
Rows are pairs, not independent lists. If the amount in A4 belongs to 30 October 2026, the date in B4 must be that date. Inserting or deleting a row in one column without making the same change in the other shifts the cash flows and produces a different investment than the one you intended to analyze.
Enter dates as dates, not text
Excel must be able to interpret every entry in the dates range as a valid date value. Typing a date in a format your regional settings recognize, using the DATE(year,month,day) function, or referencing a cell that already contains a valid date avoids the common problem of date-looking text. A date that Excel stores as text is not equivalent to a date serial value. Also check that no date precedes the first date in the schedule; Microsoft lists dates before the starting date among conditions that can cause XIRR to fail.
Keep the ranges the same length
If the values range contains five rows, the dates range must contain those same five corresponding rows. Do not include a header in one range but not the other, and do not include a blank or unrelated row at the bottom of only one range. A quick visual check is to select both ranges and confirm that their first and last rows match.
Calculate XIRR in Excel
- Create the headers. In a blank worksheet, enter
Cash flowin A1 andDatein B1. - Enter the transactions. Put each contribution or other outflow in column A as a negative number and each receipt in column A as a positive number. Enter the actual transaction date in column B on the same row.
- Check the schedule. Confirm that at least one value is negative and at least one is positive, that every value has a valid date, and that the two ranges contain the same number of rows.
- Enter the formula. In an empty cell, type
=XIRR(A2:A6,B2:B6), replacing the ranges with your own cash-flow and date columns. - Supply a guess only when useful. You can enter
=XIRR(A2:A6,B2:B6,0.1)to start the iteration at 10%. The third argument is a decimal rate, so 0.1 means 10%, not 0.1%. - Format the result as a percentage. Increase or decrease decimal places to show the precision appropriate for your analysis. The underlying result is a rate; changing the display format does not change the calculation.
- Review the cash flows alongside the percentage. A high annualized rate can arise from a short holding period or a very early distribution. Read the dates, total dollars contributed, total dollars received, and any remaining value before deciding what the percentage means for you.
Worked example: Microsoft’s five dated cash flows
Microsoft’s XIRR documentation uses the following schedule:
| Cash flow | Date | Investor interpretation |
|---|---|---|
| -10,000 | 1-Jan-08 | Initial investment |
| 2,750 | 1-Mar-08 | Distribution or proceeds |
| 4,250 | 30-Oct-08 | Distribution or proceeds |
| 3,250 | 15-Feb-09 | Distribution or proceeds |
| 2,750 | 1-Apr-09 | Final distribution or proceeds |
If the cash flows are in A3:A7 and the dates are in B3:B7, enter:
=XIRR(A3:A7,B3:B7,0.1)
Microsoft reports a result of 37.34% for these displayed values and dates. That number is an annualized internal rate, not the simple percentage obtained by subtracting 10,000 from the total receipts and dividing by 10,000. The timing of each receipt is part of the result: money received earlier is discounted for fewer days than money received later.
At 37.34%, the present values of the five dated amounts sum to approximately zero under XIRR’s 365-day convention. The result therefore summarizes the schedule as one rate, while the schedule itself remains the evidence you need to understand what happened.
How the date calculation affects the result
XIRR does not count “one month” as a fixed period. It calculates the actual day difference from the first date to each later date and applies the 365-day basis. A payment 30 days after the initial investment and a payment 45 days after it receive different discount exponents, even if both are described informally as occurring in the next month.
This is why you should preserve the transaction dates instead of replacing them with month numbers. Moving a distribution by several weeks can change the annualized result, particularly when the investment is short-lived or when a large amount is received early. The function also annualizes automatically. If XIRR returns 12%, report it as a 12% annualized rate; do not multiply by 12 because the underlying transactions were monthly.
Use dates in the same calendar system and verify the year, month, and day for every row. A single transposed day or year can have a material effect while still producing a mathematically valid answer.
What to do when Excel returns an error
There is no positive and negative cash flow
Check the signs first. If every amount is positive, or every amount is negative, change the entries to reflect the investor’s direction of cash movement. A purchase or contribution is normally negative from the investor’s viewpoint; a distribution, sale receipt, or other amount received is normally positive. Excel requires at least one amount of each sign.
Recommended Free Tools
The amounts and dates are misaligned
Compare the first and last row of both ranges. If the formula is =XIRR(A2:A8,B2:B7), one range has an extra item. If a row was sorted in only one column, the amounts no longer belong to their original dates. Restore the transaction pairs, then re-enter the formula using equal-length ranges.
A date is text, invalid, or earlier than the starting date
Select each date cell and check that Excel recognizes it as a date rather than left-aligned text. Re-enter a problem date using a recognized date format or construct it with DATE(year,month,day). Confirm that no date is earlier than the first date in the schedule. These checks address the most common input failures before you change the mathematical assumptions.
Rank #4
The result is #NUM!
XIRR uses an iterative calculation. Microsoft says Excel starts from the supplied guess, seeks accuracy within 0.000001 percent, and stops after 100 tries if it cannot find a result. First verify the signs, ranges, and dates; an incorrect input cannot be repaired by a different guess.
If the inputs are sound, try a plausible alternative starting value, such as -0.5, 0, 0.1, or 0.5, depending on the economics of the investment. Enter the value as the third argument, for example =XIRR(A2:A8,B2:B8,0.5). A new guess can help the iteration reach a solution, but it is not a guarantee that the investment has one unique or economically meaningful return.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Why the guess can change the answer
Some cash-flow patterns have more than one rate that makes net present value equal to zero. This is more likely when the signs change several times, such as an initial investment, a later contribution, an interim distribution, and another contribution before the final sale. In those cases, Excel may converge on different roots from different starting guesses.
When changing the guess changes the reported XIRR, do not select the most attractive percentage automatically. Record the cash-flow sequence, test several reasonable guesses, and examine the corresponding XNPV profile. Microsoft notes that there may be more than one acceptable internal rate of return and that a different guess may return a different one. The important question is whether the selected rate corresponds to the economic interpretation you intend, not merely whether Excel produced a number.
A single XIRR also cannot tell you how much money was invested, how much was received, or whether a large unpaid ending value remains. Keep those amounts visible beside the rate and use the dated cash-flow schedule as the primary record.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Reading XIRR alongside XNPV and a hurdle rate
XIRR solves for the rate. XNPV evaluates the same dated cash flows at a rate you choose. If your decision rule is “accept this investment only if it exceeds 8%,” evaluate XNPV at 8% and inspect whether the result is positive or negative under your sign convention. XIRR can then provide a rate summary, but it does not replace the hurdle-rate test.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Because XIRR is defined as the rate at which XNPV equals zero, the two functions are natural partners. Use XNPV to explore sensitivity: calculate values at several discount rates and see how the investment’s value changes. This is especially important when the schedule has several sign changes or when the XIRR appears unusually high or low.
Best Value
- It can be a gift option
- Comes with secure packaging
- Helpful in various ways
| Question you are asking | Useful calculation | What to inspect |
|---|---|---|
| What annualized rate summarizes these dated flows? | XIRR | The signs, dates, and possibility of multiple roots |
| What is this schedule worth at my required return? | XNPV at the chosen rate | Whether the result is positive or negative and how it changes with the rate |
| Are these transactions evenly spaced? | Review the calendar dates before choosing IRR or XIRR | The actual day gaps, not the labels you use for periods |
A practical audit checklist
- Every transaction appears once, with its original amount and date.
- Outflows are negative and inflows are positive from the investor’s perspective.
- At least one negative and one positive amount are present.
- The values and dates ranges start and end on matching rows.
- Every date is a valid Excel date value or a formula result that returns a date.
- No date precedes the first date in the schedule.
- The worksheet preserves the actual day, month, and year rather than substituting equal periods.
- The formula uses XIRR for uneven timing and IRR only when the periods are genuinely regular.
- The annualized result is reviewed together with total contributions, total receipts, and any remaining investment value.
- If the result is
#NUM!, the inputs are checked before changing the guess. - If different guesses produce different rates, the cash-flow pattern and XNPV behavior are examined instead of reporting one rate without qualification.
Common interpretation mistakes
Calling the result a simple profit percentage
XIRR is a time-weighted rate summary. It accounts for when each dollar moved, so it is not the same as total receipts minus total contributions divided by contributions. Two investments with the same total profit can have different XIRRs if one returns money much earlier.
Multiplying by 12
The output is annualized already. Multiplying it by 12 creates an overstated figure and changes the meaning of the calculation. Monthly transaction frequency does not make the XIRR a monthly rate.
Ignoring the sign convention
Signs describe direction, not whether an investment was “good” or “bad.” A refund received is positive even if it is disappointing; a follow-on contribution is negative even if it improves the investment’s prospects. Consistent direction is what allows the present-value equation to be solved.
Free tools Windows power users keep installed
One-click scans. No signup required.
Treating every returned rate as unique
Multiple valid roots can exist. A plausible-looking percentage is not proof that the schedule has only one solution. Investigate changing signs and use XNPV to understand the shape of the calculation when necessary.
Choosing the right level of precision
Excel’s documented iteration target is within 0.000001 percent, but the precision you display should match the decision. Two decimal places may be enough for a personal portfolio summary; a model used for accounting review may retain more decimals internally while presenting a rounded rate. Do not confuse a more finely displayed percentage with greater certainty about future returns. The precision refers to solving the supplied mathematical schedule, not predicting what the investment will earn next.
Further reading and official documentation
For the function definition, syntax, date requirements, default guess, 365-day basis, and iteration behavior, consult Microsoft Support’s XIRR function page. For the distinction between periodic IRR, date-aware calculations, and NPV analysis, see Microsoft’s NPV and IRR in Excel guide.
Those references describe Excel’s calculation rules. Your own workbook still needs an accurate transaction ledger and a clear decision rule: XIRR tells you the annualized rate implied by the dates and amounts you entered; it does not validate that the entries are complete or that the investment is suitable.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
