Fix Excel XIRR Wrong Data Type and #VALUE! Errors
Question details
Identify and correct invalid data types in the cash-flow and date ranges used by an XIRR formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is calculating the internal rate of return for cash flows that occur on different dates.
- Observed behavior
- XIRR returns a wrong data type or #VALUE! error even though the referenced cells appear to contain dates and numbers.
Locate the XIRR formula and identify its cash-flow and date ranges. Check that the ranges have the same number of rows and that each cash flow corresponds to the date on the same row.
Check and correct the values and dates used by XIRR
Use this method when XIRR returns a data type or #VALUE! error despite cells appearing correctly formatted.
Cell formatting changes how a value looks, but it does not convert text into a number or a valid Excel date.
In a blank cell beside the first date, enter =ISNUMBER(A2), assuming dates start in A2. Fill the formula down the date range; FALSE identifies a cell that is not stored as a numeric date.
In another blank column, enter =ISNUMBER(B2), assuming cash flows start in B2, and fill it down. Inspect cells marked FALSE for text, empty strings, or other nonnumeric entries.
Edit the affected cells in columns A and B. Enter cash flows as numbers and dates in a format recognized by Excel, or use a formula such as =DATE(2024,12,31) to create an unambiguous date. Check that formulas in the ranges do not return empty strings.
Select the XIRR formula cell and press Enter to recalculate it. For dates in A2:A10 and cash flows in B2:B10, use =XIRR(B2:B10,A2:A10).

Check XIRR inputs in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, test the date and cash-flow cells, and correct invalid entries before recalculating XIRR.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
- 2. Check input types: In blank columns, enter =ISNUMBER(A2) for the first date and =ISNUMBER(B2) for the first cash flow, then fill both formulas down their ranges.
- 3. Correct the inputs: Edit cells that return FALSE. Enter numeric cash flows and valid dates, or use a formula such as =DATE(2024,12,31) for an unambiguous date.
- 4. Recalculate the formula: Select the XIRR formula cell and press Enter. For cash flows in B2:B10 and dates in A2:A10, enter =XIRR(B2:B10,A2:A10).

Frequently Asked Questions
Why does XIRR return #VALUE! when my cells look like dates?
A date-looking entry may be stored as text. Enter =ISNUMBER(A2) beside a date cell to check it; FALSE means it is not stored as a numeric date. Reenter the date or use DATE with separate year, month, and day arguments.
Can an empty string cause an XIRR error?
Yes. A formula that returns an empty string, such as ="", can cause a data type error in the cash-flow range. Inspect formulas in the referenced cells and replace invalid results with appropriate numeric cash-flow values.
Does changing a cell's format convert text into a number?
No. Changing the format only affects display. Use =ISNUMBER on the cell to check its stored type, then edit or convert the entry if it is text.
What should I check if XIRR returns #NUM! instead?
Make sure the cash-flow range contains at least one positive and one negative value, and check that no date precedes the first cash-flow date. If the calculation still does not converge, try a different optional guess in the XIRR formula.




