How to Fix Excel XIRR #NUM Error with Horizontal Dates and Amounts
Question details
The user needs to resolve a #NUM! error that occurs when calculating the XIRR function using horizontally arranged dates and cash flow amounts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating the internal rate of return (XIRR) on a financial model where cash flows and dates are laid out horizontally in rows rather than vertically in columns.
- Observed behavior
- The XIRR formula evaluates to a #NUM! error instead of a percentage. This is typically triggered by dates being stored as text, mismatched array dimensions, or conflicting regional date settings (e.g., 31/12/2026 evaluated in a month/day/year locale).
Verify that your horizontal date and amount arrays contain the exact same number of columns, and ensure the first cash flow amount is negative (representing the initial investment).
Correct Date Formats and Regional Locale Settings
Ensure that your dates are recognized as valid Excel serial numbers rather than text strings, particularly when dealing with international date formats.
A common cause of the #NUM! error in XIRR is Excel failing to parse dates due to regional setting mismatches. For example, '31/12/2026' will not be recognized as a valid date if your system is set to a MM/DD/YYYY locale, resulting in a text value.
Select your row of dates. If they are aligned to the left by default, Excel is likely treating them as text. Valid dates align to the right.
Highlight the horizontal row of dates. Go to the 'Data' tab and click 'Text to Columns'. Click 'Next' twice, and on the third step, select 'Date' and choose the format that matches how the text is written (e.g., DMY or MDY), then click 'Finish'.
If errors persist across workbooks, check your computer's OS regional settings to ensure the short date format matches your standard data entry method.
Ensure Matching Range Dimensions
Verify that the ranges selected for values and dates are exactly the same size.
Use the DATE Function for Text Dates
Bypass regional formatting ambiguities by constructing unambiguous dates using the DATE function.
Calculate XIRR Seamlessly with WPS Spreadsheet
If you are experiencing persistent calculation bugs or locale issues with XIRR, try WPS Spreadsheet. It offers highly robust financial formula engines, excellent date parsing, and complete compatibility with standard spreadsheet formats.
- 1. Download and Install: Download WPS Office and open your existing financial workbook in WPS Spreadsheet.
- 2. Organize Your Data: Ensure your cash flows and dates are laid out in parallel horizontal rows.
- 3. Insert the XIRR Formula: Select an empty cell, navigate to the Formulas tab, or simply type =XIRR(values_range, dates_range).
- 4. Calculate Accurately: Press Enter. WPS Spreadsheet reliably parses standard and international dates to deliver the accurate internal rate of return.
Frequently Asked Questions
Why does XIRR return a #NUM! error even when my arrays match perfectly?
This often happens if the calculation cannot converge on a result within 100 iterations. It can also occur if the cash flows do not contain at least one positive and one negative value, or if the dates are not recognized as actual numerical date values.
Does XIRR require horizontal dates to be sorted in chronological order?
No, the XIRR function does not strictly require dates to be sorted sequentially. However, the very first date in your selected array must be the earliest date, representing the start of the investment.
Can I provide a custom guess for the XIRR calculation?
Yes. If XIRR returns a #NUM! error due to convergence issues, you can add an optional third argument for your estimated rate. For example: =XIRR(B2:F2, B1:F1, 0.1) provides a 10% guess to help the algorithm find the result.
How do I easily spot text-formatted dates in a horizontal row?
By default, Excel aligns text to the left and numbers (including dates) to the right. If you widen your columns and the dates stick to the left side, they are formatted as text. You can also use =ISNUMBER(cell) which will return FALSE for text dates.
