The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Use XIRR when your portfolio cash flows happen on irregular dates. Enter every contribution, withdrawal, distribution, sale receipt, and ending portfolio value with its actual date, then apply =XIRR(amount_range,date_range). The result is an annualized rate for those specific cash flows; it does not check whether your data is complete or whether the investment is suitable.
What XIRR measures
XIRR calculates the annualized internal rate of return for cash flows that are not evenly spaced on a calendar. Microsoft describes it as returning the internal rate of return for a schedule of cash flows that is not necessarily periodic in its XIRR function documentation. Google likewise directs users to XIRR when dates are irregular, while its IRR function is intended for periodic flows (Google XIRR help; Google IRR help).
As an Amazon Associate I earn from qualifying purchases.
Conceptually, XIRR finds the annual rate that makes the net present value of all supplied, dated cash flows equal to zero. A contribution is money leaving you, a receipt is money coming back to you, and the timing of each amount affects the result. Because the calculation is driven entirely by the rows you provide, XIRR is a measurement tool rather than a data-audit, forecast, or recommendation.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
The function does not know what an account statement means. It cannot determine whether a row is a fee, a dividend, a transfer between accounts, or a duplicate transaction. It simply solves for the rate implied by the numbers and dates in the selected ranges. A correct-looking percentage can therefore still describe an incomplete schedule.
#1 Best Overall
Choose the function from the schedule
| Function | Use it when | What it provides |
|---|---|---|
| XIRR | Cash-flow dates can be irregular | Annualized rate for dated cash flows |
| IRR | Cash flows occur at regular intervals such as monthly or yearly periods | Periodic internal rate of return |
| XNPV | You need the net present value of irregularly dated flows at a chosen discount rate | Present-value cross-check for an XIRR calculation |
Do not substitute IRR merely because the number of rows looks tidy. If one contribution, distribution, or valuation occurs between your normal periods, the dates are no longer periodic and XIRR is the appropriate choice. Conversely, if every row represents the same regular period and you intentionally work with period numbers rather than calendar dates, IRR is the matching function.
Think of the function choice as a question about the schedule, not the investment product. A mutual fund, brokerage account, private investment, or project can all use XIRR if its external cash flows occur on uneven dates.
Build the cash-flow table first
Use two adjacent columns: one for amounts from the investor’s perspective and one for the corresponding spreadsheet dates. Every amount must line up with the date in the same row. This compact example is illustrative, not a published performance result:
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →| Cash flow (investor perspective) | Date |
|---|---|
| -10000 | 2024-01-02 |
| -500 | 2024-04-15 |
| 300 | 2024-09-30 |
| 11200 | 2025-01-02 |
The first negative row represents an initial investment, the second a later contribution, the positive 300 a receipt, and the final positive 11,200 the portfolio value received on the selected measurement date. If you are measuring an account that has not been sold, treat its market value on the valuation date as a positive cash flow. Without that ending value, the calculation cannot represent the return realized or unrealized through that date.
The final row is a valuation convention, not necessarily a cash withdrawal. It asks, “What would the investor receive if the holdings were valued or liquidated on this date?” Including it allows the rate to include both realized receipts and the value still held. If you are measuring through an actual sale, use the sale proceeds instead of an estimated ending value.
Sign convention
- Enter contributions, purchases, and other money you put into the investment as negative numbers.
- Enter distributions, withdrawals, sale proceeds, and the ending portfolio value as positive numbers.
- Include at least one negative and one positive amount. A series containing only one sign cannot produce an internal rate of return.
These signs describe the investor’s cash position. A fund manager preparing a different viewpoint could use the opposite convention, but you must keep one perspective consistent across the entire schedule. Reversing every sign does not change the economic timing pattern, but mixing conventions within one table does change the answer and can prevent a result.
Fees and taxes need the same perspective treatment as any other external cash flow. If a fee is taken from your account and is part of the return you want to measure, record the amount leaving you as a negative cash flow on the date charged. If a tax payment is outside the portfolio and you want an after-tax investor return, record it according to when money actually left you. The key is to define the measurement question first and apply it consistently.
Dates and row integrity
- Enter actual spreadsheet date values, not text that merely looks like a date. Microsoft warns that text dates can cause problems in XIRR calculations.
- Use the same number of amount and date cells, in the same order.
- Add a row for every external cash flow you want included; do not combine transactions that occurred on different dates unless you intentionally aggregate them to a documented date.
- Choose and record one measurement date. The ending value belongs on that date, not on the date you happen to open the workbook.
Row alignment is a practical control. If you sort the amount column without sorting the date column, XIRR will still process the rows but will pair the wrong amounts with the wrong dates. Sort the two columns together, or leave them in transaction order and check each pair manually.
Use the actual transaction date represented by your records. A contribution dated April 15 should not be moved to April 30 simply to fit a monthly report unless you deliberately choose that aggregation and document it. Dates drive the discounting, so even a correct amount can produce a different rate when it is assigned to the wrong day.
Calculate XIRR in Excel
- Place amounts in one column, for example
A2:A5, and matching dates in the next column, for exampleB2:B5. - Click the cell where you want the result and enter
=XIRR(A2:A5,B2:B5). In Excel’s English function syntax, the arguments are calledvalues,dates, and the optionalguess; see Microsoft’s XIRR documentation. - Press Enter and format the result as a percentage. Set the number of decimal places to a level that communicates precision without implying that the inputs are more accurate than they are.
- Expand or contract both ranges together whenever you add or remove a transaction. For example, adding a fifth row means changing both ranges to
A2:A6andB2:B6. - Review the signs, dates, and valuation row before interpreting the percentage.
The function accepts the ranges in parallel: the first item in the values range is matched with the first item in the dates range, the second with the second, and so on. A blank or extra cell in one range can therefore create a mismatch. Keep the range boundaries visible while you build the schedule, and update both references whenever the table changes.
When to supply a guess in Excel
Excel starts its iterative search with a default estimate of 0.1, or 10 percent. Microsoft says a guess is usually unnecessary, but the iteration can fail to find a result after 100 tries. If a valid-looking schedule returns #NUM!, you can provide an alternate starting point, such as =XIRR(A2:A5,B2:B5,0.1). The guess is only a solver starting point; it is not an assumption that changes the underlying cash flows or proves that the resulting rate is economically reasonable.
Recommended Free Tools
Try a guess only after checking the data. A third argument cannot repair a text date, a missing sign, mismatched ranges, or an incorrect ending value. If the schedule is valid and the first attempt does not converge, document the guess you used so another reviewer can reproduce the result.
Calculate XIRR in Google Sheets
- Enter the cash-flow amounts and dates in paired columns, with no missing row between an amount and its date.
- Select the output cell and enter
=XIRR(A2:A5,B2:B5). Google Sheets uses the English function argumentscashflow_amounts,cashflow_dates, and optionalrate_guess; the syntax and requirements are listed in Google’s XIRR help. - Format the returned decimal as a percentage from the toolbar or Format menu.
- If you add a transaction, extend both ranges to include its row. Keep the ending portfolio value on the date at which you want the measurement to stop.
- Use a locale-appropriate argument separator if your Sheets settings require semicolons instead of commas. The function and range logic remain the same.
Google documents the default rate estimate as 0.1 (10 percent). You can provide another starting estimate with a third argument, for example =XIRR(A2:A5,B2:B5,0.1), when the solver needs help finding a rate. The estimate is an initial value for the calculation, not a target return or a forecast.
Google Sheets may display a decimal such as 0.084 until you apply percentage formatting. That decimal represents the same rate as 8.4 percent. Decide whether the workbook should show whole percentages or decimal places, then use the same display choice for comparable portfolios.
How to read the result
The displayed percentage is an annualized rate implied by the exact amounts and dates in your table. It is not the simple gain divided by the amount invested, and it is not automatically the return of a benchmark or of an entire account. A large contribution shortly before the valuation date can affect the result differently from the same contribution made months earlier because XIRR accounts for the time each dollar was invested.
For example, the table’s 2024-04-15 contribution is invested for a different length of time than the 2024-01-02 contribution. The 2024-09-30 receipt also changes the schedule before the final value is recognized. XIRR combines all of those timings into one annualized rate; it does not first calculate a separate percentage for each row.
Excel discounts later payments using a 365-day year, as described in Microsoft’s documentation. Consequently, two spreadsheets can show different annualized rates when they use different date conventions, cash-flow scope, or ending valuation dates even if their account balances appear similar. Keep the original dates rather than rounding them to months if you want the documented date-based calculation.
Make comparisons on the same basis
Before comparing two XIRR figures, confirm that both schedules use:
- the same portfolio or account scope;
- the same treatment of contributions, withdrawals, and distributions;
- the same type of ending value, such as market value on a stated date; and
- the same measurement date and date values.
A rate from one account through 30 June is not directly comparable with a rate from another account through 31 December if either schedule includes different transactions or valuation timing.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsAlso compare the precision and currency treatment of the inputs. A rounded ending value, a missing fee, or a contribution recorded on a statement date rather than its transaction date changes the cash-flow schedule. Those are data differences, not differences in the XIRR function.
Cross-check the result with XNPV
XNPV is the companion calculation for irregularly dated net present value. Microsoft defines XIRR as the rate corresponding to an XNPV of zero, and Google states that XNPV is zero when its discount rate is set to the XIRR calculated from the same amounts and dates (Microsoft XIRR; Google XIRR; Google XNPV).
- Put the XIRR result in a cell, such as
C2. - Use the same amount and date ranges in XNPV, with that result as the discount rate:
=XNPV(C2,A2:A5,B2:B5). - Format the XNPV result as a number in the currency of your cash-flow table.
- Expect a value at or extremely close to zero, subject to spreadsheet rounding. A material nonzero result signals that the rate, ranges, or dates are not the same.
Use the identical ranges, including the ending value row. If XIRR uses A2:A6 and B2:B6 but XNPV uses only rows 2 through 5, the check is not testing the same schedule. Likewise, do not substitute a different discount rate or a different valuation date for the cross-check.
This check confirms the mathematical relationship between the two functions. It cannot tell you whether you omitted a distribution, entered a wrong price, or selected an inappropriate valuation date. A zero XNPV validates the arithmetic relationship for the supplied inputs, not the completeness of your records.
Free tools Windows power users keep installed
One-click scans. No signup required.
Troubleshoot errors and unexpected results
| Symptom | What to inspect | Correction |
|---|---|---|
#NUM! or no converged result |
Whether the schedule has both signs, valid dates, matching ranges, and a solvable rate | Correct the inputs first; if the data is valid, try a reasonable alternate third argument such as 0.1 as the starting estimate. |
#VALUE! |
Date cells that contain text or invalid date values | Convert the entries to real spreadsheet dates and check the cell format. |
| Result changes after adding a transaction | Whether the new amount was entered with the correct sign and paired with its actual date | Insert the row in both ranges and verify the investor-perspective sign. |
| Excel rejects the schedule because of date order | Whether any date precedes the starting date in the Excel range | Correct the starting date or remove an accidentally earlier date; then recalculate. |
| Very high, very low, or counterintuitive percentage | Missing ending value, omitted cash flow, misplaced decimal, or an unusually timed contribution | Reconcile the table to account statements and restate the measurement date before interpreting the rate. |
| Formula separator error | Regional spreadsheet settings | Use the separator required by your locale, often a semicolon rather than a comma, while keeping the same argument order. |
Work through the checks in that order rather than immediately changing the formula. A solver error is often an input problem. First verify that the amount and date ranges have equal lengths, then inspect the signs, then test every date cell, and only afterward consider a different guess.
When a percentage looks implausibly large or negative, inspect units as well as signs. A value entered as 112 instead of 11,200, or a withdrawal entered as a positive contribution, can dominate the result. Compare each row with the statement and confirm whether the final row is a portfolio value, a sale receipt, or an accidental duplicate.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.A disciplined review before relying on XIRR
- Define the question: for example, the return of one account from its first contribution through a particular valuation date.
- Export or transcribe every contribution, withdrawal, distribution, and sale receipt that belongs to that scope.
- Record the ending portfolio value as a positive cash flow on the chosen valuation date if the investment remains open.
- Check that each amount has exactly one date and that all dates are true date values.
- Confirm that at least one amount is negative and at least one is positive.
- Run XIRR, format the output as a percentage, and retain the underlying table so another person can reproduce it.
- Run XNPV with the XIRR result and the identical ranges as a mathematical cross-check.
- When comparing portfolios, align scope, cash-flow treatment, and measurement date before drawing a conclusion.
Save the valuation date and the source of the ending value alongside the formula. If you rerun the workbook later with a new market value or a later date, you are measuring a new period, not correcting the old result. Keeping prior schedules prevents accidental apples-to-oranges comparisons.
XIRR answers a narrow question: which annualized rate makes these particular dated cash flows balance to zero under the spreadsheet’s convention? The quality of the answer depends on the completeness and accuracy of the rows you supplied, not on the function’s ability to validate the investment itself.
Frequently Asked Questions
Should I use XIRR or IRR for monthly contributions?
Use IRR only when the cash flows are genuinely periodic and represented at regular intervals. If contributions, distributions, or the ending valuation occur on different calendar dates, use XIRR with those actual dates.
What sign should my initial investment have?
From the investor’s perspective, money paid into the investment is negative. Money received, including distributions, sale proceeds, withdrawals, and the ending portfolio value, is positive. The schedule needs at least one amount of each sign.
Do I include the current value of an account that has not been sold?
Yes, if you want the return through a stated valuation date. Add the account’s market value as a positive cash flow on that date. Without an ending value, the result does not include the value still held.
Why does Excel return #VALUE!?
Inspect the date cells. XIRR requires valid spreadsheet dates; text that merely looks like a date can cause an error. Also verify that the amount and date ranges contain corresponding rows.
Why does Excel or Sheets return #NUM!?
Check for both positive and negative amounts, matching range lengths, valid dates, and an appropriate schedule. If those inputs are correct but the calculation does not converge, try a reasonable third-argument starting estimate such as 0.1.
What does the optional guess or rate_guess do?
It is the starting estimate for the iterative solver. Both Excel and Google Sheets document a default of 0.1, or 10 percent. It does not change your cash flows, guarantee a result, or make a return economically appropriate.
Why is my XNPV cross-check not zero?
Use the XIRR result as XNPV’s discount rate and copy the exact same amount and date ranges, including the ending value. A small residual can be rounding; a material residual usually means the rate, ranges, or dates differ.
Can XIRR tell me whether an investment is good?
No. It reports the annualized rate implied by the supplied cash flows and dates. It does not validate that the data is complete, forecast future performance, compare a benchmark automatically, or determine suitability.
Why did my return change after adding a contribution?
Every added amount changes the schedule, and its date determines how long that money was invested. Confirm the contribution’s sign, actual date, and inclusion in both the values and dates ranges.
The Bottom Line
Bottom line: Build a complete two-column schedule, use negative amounts for investor outflows and positive amounts for receipts and ending value, then run =XIRR(amounts,dates). Choose XIRR for irregular dates, IRR for regular periods, and use XNPV with the identical inputs to verify the arithmetic. Treat the percentage as a calculation for your stated scope and valuation date—not as a data audit or investment recommendation.
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.
