Formula Errors

How to Fix Excel IRR Function Returns Zero

Maira Mehtab Oct 7, 2026 871 views

Question details

The user is experiencing an issue where the IRR (Internal Rate of Return) function evaluates to zero instead of calculating the correct percentage.

How to Fix Excel IRR Function Returning Zero

Product: Excel
Device & OS: not provided
Scenario: Calculating the internal rate of return for a series of financial cash flows.
Observed behavior: The IRR formula returns a result of 0% or exactly zero instead of the expected internal rate of return, often due to formatting or range errors.

Before you start

Ensure that your selected cash flow range contains at least one positive value (representing income) and one negative value (representing your initial investment). The IRR function cannot mathematically calculate a rate of return without both.

Solution 1Recommended

Verify and Clean Nonnumeric Cash Flow Data

Hidden spaces, special characters, or numbers formatted as text can cause the IRR function to ignore cells and evaluate the overall formula as zero.

Financial data imported from other software or web pages often contains invisible spaces or formatting that Excel cannot read as numerical data.

Identify text-formatted numbers

Select the cash flow cells used in your IRR formula. Look for small green triangles in the upper-left corner of the cells, which indicate numbers are being stored as text.

Convert text to numbers

Click the warning icon that appears next to the selected cells and choose 'Convert to Number' from the dropdown menu.

Remove hidden spaces

Press Ctrl+H to open the Find and Replace dialog. Type a single space in the 'Find what' field, leave 'Replace with' blank, and click 'Replace All' to clean the dataset.

Verify and Clean Nonnumeric Cash Flow Data

Quick Formatting Tip: Applying the General or Number format from the Home tab does not automatically convert text to numbers. You must double-click the cell and press Enter, or use the Text to Columns tool to force the conversion.

Solution 2

Adjust the Formula Range and Values

An incorrect range selection or missing negative values will prevent the formula from identifying the cash flow pattern correctly.

Solution 3

Add a Guess Argument to the Formula

If the cash flows are complex, the internal calculation may require a starting point to successfully converge on the correct rate rather than returning zero or an error.

Calculate Financial Formulas Effortlessly

Calculate IRR Seamlessly with WPS Spreadsheet

WPS Spreadsheet offers a highly compatible and intuitive platform for all your financial modeling. With intelligent error checking and one-click data formatting, you can accurately calculate IRR and resolve formula issues in seconds.

1. Open your financial dataset: Launch WPS Spreadsheet and open the workbook containing your cash flow data.
2. Clean your cash flows: Highlight your data range and look for the error-checking icon. Click 'Convert to Number' if any cells are improperly formatted as text.
3. Input the IRR formula: Type =IRR( into a blank cell, select your newly cleaned cash flow range, and type a closing parenthesis.
4. Get your result: Press Enter to instantly receive the accurate internal rate of return without formula errors.

Fully compatible with Microsoft Excel formulas, including IRR, NPV, and XIRR.Built-in data evaluation tools to quickly spot and fix numbers formatted as text.Free and lightweight spreadsheet solution for Windows, Mac, and mobile devices.Familiar user interface ensuring a zero-learning-curve transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the IRR function return a #NUM! error instead of zero?

The #NUM! error occurs when the IRR function cannot find a valid result after 20 calculation iterations. This usually happens if the cash flows do not contain both a negative and a positive value, or if the rate is highly unusual, requiring you to add a manual 'guess' argument to help the formula succeed.

Can I calculate IRR for non-periodic cash flows?

The standard IRR function assumes regular, periodic intervals (such as exactly one month or one year between each value). If your cash flows occur at irregular dates, you should use the XIRR function instead, which allows you to select a secondary range containing the specific dates for each cash flow.

What is the quickest way to fix hundreds of numbers stored as text?

Highlight the affected column of numbers, navigate to the Data tab, and click on 'Text to Columns'. Without changing any settings in the wizard, immediately click 'Finish'. This forces the spreadsheet to re-evaluate the entire column and instantly converts the text strings into usable numeric values.

Excel & Data Problems