Formula Errors

How to Fix Spreadsheet IRR and XIRR Returning Errors or Nearly Zero

Emma Brown Oct 23, 2026 868 views

Question details

The user needs to correct IRR and XIRR formulas that return errors or extremely small negative numbers in a cash-flow model expecting negative returns.

How to Fix IRR and XIRR Results That Return N/A, Errors, or Nearly Zero

Product: Spreadsheets
Device & OS: not provided
Scenario: Calculating the internal rate of return for a cash-flow model where the expected outcome is a negative percentage.
Observed behavior: The IRR function returns a #NUM! error, and the XIRR function returns a value close to zero (such as -2.98E-09) instead of the actual negative rate.

Before you start

Ensure your cash-flow data contains at least one positive and one negative value, and verify that all dates in your date range are formatted correctly as valid spreadsheet dates.

Solution 1Recommended

Add a Negative Guess Value to the XIRR Formula

Provide a starting negative guess to help the financial algorithm find the correct internal rate of return.

When a cash-flow model has a negative internal rate of return, the default XIRR or IRR algorithm might fail to converge on the correct answer. This results in a #NUM! error or an internal algorithm limit result like -2.98E-09. Providing a realistic negative guess helps the calculation converge correctly.

Select the formula cell

Click on the cell containing your existing XIRR or IRR formula.

Modify the formula parameters

Click into the formula bar and add a negative guess as the final argument. For example, change =XIRR(B2:B32,A2:A32) to =XIRR(B2:B32,A2:A32,-10%).

Apply the updated formula

Press Enter to apply the formula. Check the cell to ensure it now displays the expected negative percentage instead of an error or zero.

Add a Negative Guess Value to the XIRR Formula

Understanding XIRR Limits: A result around -2.98E-09 is an internal mathematical limit indicating the formula failed to converge. Treat this exactly as you would treat a #NUM! error.

Solution 2

Verify the Result Using a SUMPRODUCT Calculation

Confirm the accuracy of your new XIRR calculation manually, as XNPV does not support negative discount rates.

Powerful Financial Calculations

Easily Calculate IRR and XIRR with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced financial functions, including IRR, XIRR, and SUMPRODUCT. You can effortlessly manage complex cash-flow models and apply custom guess values to calculate accurate internal rates of return without mathematical limitations.

1. Open your cash-flow document: Launch WPS Spreadsheet and open the document containing your financial data.
2. Start the XIRR function: Click on the cell where you want to display the rate and type =XIRR( in the formula bar.
3. Select your data arrays: Highlight your cash-flow amounts array, add a comma, then highlight the corresponding dates array.
4. Enter the guess value: Type a comma followed by your negative guess value, such as -10% or -0.1, close the parentheses, and press Enter.

Full compatibility with Microsoft Excel's financial formulas (IRR, XIRR, NPV).Intuitive formula builder with built-in error checking.Handles complex cash-flow models effortlessly.Completely free and lightweight spreadsheet software.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the XIRR function return exactly -2.98E-09?

This specific scientific notation value (-2.98E-09) is an internal mathematical limit reached by the XIRR algorithm when it fails to converge on a valid rate. It should be treated just like a standard #NUM! error, indicating that you need to provide a custom guess value for the formula to work.

Why can't I use the XNPV function to check my negative XIRR result?

The standard XNPV function is natively designed to calculate net present value based on positive discount rates. It does not accept a negative discount rate, which is why an equivalent SUMPRODUCT formula is recommended to verify negative XIRR returns.

What is considered a good guess value for a negative internal rate of return?

A good starting guess is usually a rough estimate close to what you expect the actual return to be. For negative returns, a standard guess of -10% (entered as -10% or -0.1) is commonly used and is usually sufficient to help the calculation process converge correctly.

Excel & Data Problems