Use Excel’s XIRR function when your cash flows occur on irregular dates. Put each date beside its matching cash flow, use negative numbers for money invested and positive numbers for money received, then enter:
=XIRR(B2:B5,A2:A5)The result is an annualized, money-weighted return. Format the result cell as a percentage. This guide explains three ways to build the formula, how to include an ending value, and how to fix common errors.
What does XIRR calculate?
XIRR solves for the annual rate that makes the net present value of a series of dated cash flows equal to zero. In plain language, it answers:
What constant annual rate would reproduce these cash flows on these exact dates?
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.#1 Best Overall
Sale
TI-30XIIS Scientific Calculator Texas Instruments, Black
- Fundamental, two-line calculator that combines statistics and advanced scientific functions for high school math and science
- Two-line display shows the entry and calculated result at the same time for easy understanding of the calculation
- Fraction features, conversions, and basic scientific and trigonometric functions
- Solar and battery powered
- Approved for use on SAT, ACT and AP exams
Unlike a simple return, XIRR accounts for both the size and timing of deposits, withdrawals, distributions, and proceeds. It is therefore a money-weighted return.
Conceptually, Excel is solving:
0 = Σ CashFlowi / (1 + rate)^((Datei - Date0) / 365)You do not need to calculate this equation manually. Excel uses a 365-day year, including when the period contains a leap day. That convention can produce slightly different results from software using another day-count basis. See Microsoft’s XIRR documentation.
XIRR compared with other return measures
- IRR: Use
IRRfor cash flows at regular, equally spaced intervals. UseXIRRwhen the actual dates are irregular. - Time-weighted return: Attempts to measure investment performance independently of when the investor added or removed money.
- Simple total return: Compares total proceeds and distributions with contributions but does not annualize the result.
- CAGR: Usually assumes one beginning value, one ending value, and a known holding period.
- XNPV: Calculates present value at a rate you provide. XIRR finds the rate at which that present value is zero.
Microsoft describes IRR as the function for regular-period cash flows and XIRR as the function for nonperiodic cash flows.
Prepare the worksheet correctly
Use one row for every cash-flow event. The date and amount on each row must describe the same transaction.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Date | Cash flow | Description |
|---|---|---|
| 1/1/2024 | -10000 | Initial investment |
| 3/15/2024 | -1000 | Additional contribution |
| 9/30/2024 | 500 | Distribution |
| 6/30/2025 | 12500 | Sale proceeds or ending value |
With dates in A2:A5 and cash flows in B2:B5, use:
=XIRR(B2:B5,A2:A5)Sign convention
Enter the cash flows from the investor’s perspective:
- Money paid into an investment, such as a contribution or purchase: negative.
- Money received from an investment, such as a distribution, withdrawal, sale proceeds, or loan repayment: positive.
You need at least one negative and one positive value. If you are measuring an investment that has not been sold, add its current market value or estimated liquidation value as a positive final cash flow on the valuation date. This treats the value as a terminal value for measurement; it does not mean you have actually received the money.
Rank #2
- Scientific Calculator for Students: ROATEE portable scientific calculator containing 401 functions such as scientific functions, scientific computation, complex number calculations, integrals, matrix calculations, vector calculations and so on.Suitable for middle school courses such as general mathematics, statistics, chemistry, biology, and general science
- 2-Line Display: Showing input and calculation results at the same time on the display. Greatly improves computational efficiency
- Dust-resistant and Durable: ROATEE scientific calculator comes with a dust cover that can be placed at the back while in use and covered after use to prevent dust from entering. The portable size and durable material designed for students are not only convenient for storage and carrying but also have a longer service life
- Dual Power Supply & More Sensitive Key: Solar panel + replaceable button battery for long term use. High quality plastic buttons, more sensitive and precise than silicone buttons, more comfortable to touch
- Essential School Supplies: Suitable for SAT, GCSE, etc...Functionally complete with a stylish design, color scheme, and exterior, making it highly suitable for middle school students and above
Do not count the same value twice. For example, do not enter a distribution as a cash flow and also include that distribution in the ending balance.
Use real Excel dates
Dates that only look like dates but are stored as text can cause errors. Enter dates using Excel’s date system or construct them explicitly, for example:
=DATE(2024,3,15)Keep the rows in chronological order even though Microsoft documents that the dates do not have to be ordered. Chronological order makes the model easier to audit and reduces the chance of pairing a cash flow with the wrong date.
Method 1: Use ordinary cell ranges
This is the most practical method for an investment ledger or a model that will change over time.
- Put transaction dates in one column.
- Put the matching cash flows in an adjacent column.
- Select the cell where you want the return.
- Enter
=XIRR(B2:B5,A2:A5). - Press Enter.
- Format the result as Percentage, usually with one or two decimal places.
The formula syntax is:
=XIRR(values, dates, [guess])valuesis the cash-flow range.datesis the corresponding date range.guessis optional and gives Excel a starting estimate.
The two ranges must contain the same number of entries. Microsoft documents a default guess of 0.1, or 10%, when you omit the optional argument.
Make the range expandable with an Excel Table
For a growing transaction history, select the data and press Ctrl+T to convert it to an Excel Table. If the table is named Investment and its columns are named Date and Cash Flow, use:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #3
- Scientific Calculator for Students: ROATEE portable scientific calculator containing 401 functions such as scientific functions, scientific computation, complex number calculations, integrals, matrix calculations, vector calculations and so on.Suitable for middle school courses such as general mathematics, statistics, chemistry, biology, and general science
- 2-Line Display: Showing input and calculation results at the same time on the display. Greatly improves computational efficiency
- Dust-resistant and Durable: ROATEE scientific calculator comes with a dust cover that can be placed at the back while in use and covered after use to prevent dust from entering. The portable size and durable material designed for students are not only convenient for storage and carrying but also have a longer service life
- Dual Power Supply & More Sensitive Key: Solar panel + replaceable button battery for long term use. High quality plastic buttons, more sensitive and precise than silicone buttons, more comfortable to touch
- Essential School Supplies: Suitable for SAT, GCSE, etc...Functionally complete with a stylish design, color scheme, and exterior, making it highly suitable for middle school students and above
=XIRR(Investment[Cash Flow],Investment[Date])New rows added to the Table are included automatically. This is a variation of the cell-range method, not a different calculation.
Method 2: Use inline arrays
For a short, fixed example, enter the amounts and dates directly in the formula:
=XIRR({-10000,-1000,500,12500},{DATE(2024,1,1),DATE(2024,3,15),DATE(2024,9,30),DATE(2025,6,30)})This is useful for demonstrations, quick checks, or teaching how each amount corresponds to a date. It is less suitable for real records because numbers are harder to audit, easy to mistype, and difficult to expand.
Regional settings affect separators. In some Excel locales, semicolons are required instead of commas for array elements or function arguments. Do not confuse list separators with currency symbols or thousands separators: raw formula numbers should be entered as numeric values, such as -10000, not as text containing a currency sign.
Method 3: Use named ranges
Named ranges make a reusable model easier to read:
=XIRR(CashFlows,CashFlowDates)To create them:
- Select the cash-flow range.
- Choose Formulas > Define Name.
- Name the range
CashFlows. - Select the date range and define it as
CashFlowDates. - Enter the formula above.
Named ranges are useful in dashboards, templates, and workbooks intended for people who should not need to inspect cell coordinates. Check their definitions carefully: workbook-level and worksheet-level names can be confusing, and a fixed name may omit newly added transactions.
For a growing dataset, an Excel Table is usually safer:
Rank #4
- The UNIONE UC-500M is a large size desktop and is easy to use with a large font.
- The UC-500M has a new sleek design and 2-line display.
- 240 Functions Scientific Calculator: Pungltd scientific calculator has 240 functions and covers the most scientific functions what students need to use at school.
- Fraction Key for easy entry.
- Convert fractions and mixed numbers to their decimal equivalents.
=XIRR(Investment[Cash Flow],Investment[Date])What to do when Excel returns #NUM!
First try a reasonable starting estimate:
=XIRR(B2:B10,A2:A10,0.1)If necessary, try another plausible estimate:
=XIRR(B2:B10,A2:A10,0.25)=XIRR(B2:B10,A2:A10,-0.1)
Excel uses an iterative process and can return #NUM! if it cannot find a result after 100 attempts. A different guess can help, but it should not replace checking the model.
| Check | What to do |
|---|---|
| All values have the same sign | Confirm there is at least one negative and one positive cash flow. |
| Range lengths differ | Make sure the values and dates ranges contain the same number of rows. |
| Dates are invalid or problematic | Check that every date is a real Excel date and sort the schedule chronologically. |
| No convergence | Try a sensible guess, then inspect the cash-flow pattern and model assumptions. |
| Missing terminal value | Add the current value or sale proceeds if you are measuring an unrealized investment. |
| Duplicated or unusual amount | Look for a transaction entered twice or a misplaced decimal. |
Cash flows with several sign changes can have multiple mathematical solutions. If different guesses produce different answers, test the model rather than selecting the most attractive percentage. Use XNPV at several trial rates to see whether, and how often, the cash flows cross zero. Microsoft’s general guidance for correcting #NUM! errors also discusses iterative formulas and calculation settings, but changing settings is not a universal fix for a flawed cash-flow schedule.
What to do when Excel returns #VALUE!
#VALUE! commonly means that a date or cash-flow entry is text instead of a usable number.
Test individual cells with:
=ISNUMBER(A2)
=ISNUMBER(B2)If a date is unambiguous text, DATEVALUE may convert it:
=DATEVALUE(A2)However, date formats such as 03/04/2024 can mean different things in different regions. Do not use DATEVALUE blindly for ambiguous text; rebuild the date with DATE(year,month,day) or correct the source data. Also check that formulas in the cash-flow column are not returning text messages, blanks with unexpected behavior, or numbers stored as text.
Why can the result look implausible?
A high or low XIRR is not automatically an Excel error. Annualization can magnify short holding periods, and the result is sensitive to when large contributions and withdrawals occur.
Recommended Free Tools
Best Value
- 5-Inch Jumbo Solar Panel – 200% More Power: Harness any light with our industry-leading 5-inch extra-long solar panel, featuring over 200% more surface area than standard calculators. This massive panel provides exceptional energy efficiency, allowing for consistent, reliable operation under ambient light. A backup AAA battery is included for total power assurance.
- Large 14-Digit Tilted Display for Easy Viewing: Reduce neck strain with the ergonomic, angled design. The large 14-digit LCD screen is tilted to naturally align with your line of sight when placed on a desk, offering a clear view of every calculation from any angle, perfect for long work sessions.
- Oversized 10-Key Buttons for Rapid Data Entry: Built for speed and accuracy with a professional 10-key layout that mirrors a standard computer keyboard. The extra-large, soft-touch buttons prevent mis-hits and enable fast, comfortable data entry, ideal for accounting, banking, and high-volume number crunching.
- Reliable Two-Way Power for Uninterrupted Use: Never run out of power. This dual-power calculator operates primarily on its efficient jumbo solar panel and includes a AAA battery for backup. It’s engineered for continuous, dependable performance in any office, store, or home environment.
- Essential Desktop Calculator for Professional & Daily Use: The perfect tool for daily calculations across all settings. Its comprehensive functions meet the needs of office work, business, retail, and education, making it a versatile choice for students, bankers, analysts, and home users alike.
Review the schedule if the answer seems surprising:
- A large contribution may occur immediately before a large distribution.
- Frequent withdrawals can materially change the money-weighted result.
- A current valuation may be assigned to an arbitrary date.
- The ending value may be missing or counted twice.
- Cash flows may have been entered from the company’s perspective rather than the investor’s.
- Fees, taxes, valuation assumptions, and transaction timing may not be represented consistently.
A negative XIRR can be legitimate: it means the investor received less, after considering timing, than was invested. XIRR is not necessarily the same as an investment’s price return or a time-weighted performance figure.
Validate an XIRR result with XNPV
Once you have an XIRR, use it as the discount rate in XNPV:
=XNPV(XIRR(B2:B5,A2:A5),B2:B5,A2:A5)The result should be approximately zero, subject to rounding and Excel’s iterative precision. This checks that the rate and the exact cash-flow schedule are consistent.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesWhen should you use something else?
- Use
IRRwhen every cash flow occurs at regular intervals, such as exactly monthly or annually. One value per row is not enough; the intervals themselves must be regular. - Use a time-weighted return when you want to evaluate investment performance independently of the timing and size of investor contributions and withdrawals.
- Use
XNPVwhen you know the discount rate and want the present value rather than the implied return. - Investigate further when the cash flows change sign multiple times and different guesses produce different roots.
When comparing investments, standardize the valuation date and define fees, taxes, distributions, and terminal values consistently. Otherwise, differences in the input schedules—not investment performance alone—may drive the comparison.
Excel availability
Microsoft currently lists XIRR for Excel for Microsoft 365, Excel for the web, Excel 2024, and Excel 2021, including the listed Mac equivalents. Excel for the web is available with a Microsoft account, while desktop applications and broader workbook features depend on the edition or subscription. If you only need a one-off calculation, you may not need to buy software solely for XIRR.
Google Sheets also documents an equivalent XIRR function, and LibreOffice Calc documents XIRR(Values; Dates [; Guess]) with a 365-day basis. Separators, date parsing, workbook compatibility, and advanced Excel features can differ across applications.
Microsoft’s documented example
Microsoft’s example uses these cash flows:
| Cash flow | Date |
|---|---|
| -10,000 | 1-Jan-08 |
| 2,750 | 1-Mar-08 |
| 4,250 | 30-Oct-08 |
| 3,250 | 15-Feb-09 |
| 2,750 | 1-Apr-09 |
Its formula is:
=XIRR(A3:A7,B3:B7,0.1)Microsoft reports approximately 0.373362535, or 37.34% when formatted as a percentage.
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.
