How to Fix Excel IRR Function Returning a #NUM! Error
Question details
The user encounters a #NUM! error when trying to calculate the internal rate of return using the =IRR() formula for a series of cash flows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating the internal rate of return (IRR) for a series of financial cash flows in a spreadsheet.
- Observed behavior
- The formula =IRR(B2:G2) outputs a #NUM! error instead of the expected percentage return rate.
Ensure that your cash flow data represents a valid financial scenario. The IRR function strictly requires at least one positive value (income) and one negative value (payment) to calculate a meaningful rate of return.
Verify Cash Flows and Correct Cell Formatting
The most common cause of the #NUM! error in IRR calculations is a missing negative value or numbers being formatted as text.
The Internal Rate of Return formula requires a series of cash flows that includes both an initial investment (a negative number) and subsequent returns (positive numbers). Additionally, if Excel reads any of these numbers as text, the formula will fail.
Review your data range (e.g., B2:G2) and ensure there is at least one negative number representing cash outflow, and at least one positive number representing cash inflow.
Select the entire range of your cash flows. Right-click the selection and choose 'Format Cells'. Under the 'Number' tab, select 'Number' or 'Currency' and click OK.
If the numbers were previously stored as text, double-click each cell and press Enter to refresh them, or multiply the range by 1 using Paste Special to convert them to numeric values.
Select the result cell, type =IRR(B2:G2), and press Enter to see the updated internal rate of return.
Provide an Estimated Guess in the IRR Formula
Excel uses an iterative process to calculate IRR. If it cannot find a valid rate after 20 attempts, it throws a #NUM! error. Adding a 'guess' helps Excel reach the correct figure.
Easily Calculate Internal Rate of Return with WPS Office
WPS Spreadsheet offers full support for advanced financial formulas, including IRR, NPV, and PMT. It is fully compatible with Microsoft Excel formulas, making financial modeling seamless and highly efficient.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your financial cash flows.
- 2. Select the output cell: Click on the cell where you want the IRR result to be displayed.
- 3. Apply the IRR formula: Type =IRR(range, [guess]) ensuring your selected range includes both positive and negative values.
- 4. Calculate the result: Press Enter to instantly view your internal rate of return.
Frequently Asked Questions
Why does the IRR function absolutely require a negative number?
IRR calculates the rate of return on an investment. A negative number represents your initial investment or cash outflow, while positive numbers represent returns or cash inflows. Without both an outflow and an inflow, an investment return rate mathematically cannot exist.
How does the 'guess' argument work in the IRR function?
Spreadsheet software uses an iterative technique to calculate IRR. It starts with your guess (the default is 10% or 0.1 if omitted) and cycles through calculations until the result is accurate within 0.00001%. If it fails to find a result after 20 attempts, it returns a #NUM! error. Providing a closer guess gives the software a better starting point.
What if I have multiple internal rates of return in my cash flows?
If your cash flows alternate between positive and negative values multiple times across periods, the mathematical equation may have multiple solutions, causing confusion or calculation errors. In these complex scenarios, using the MIRR (Modified Internal Rate of Return) function is recommended to get a more accurate picture.
Can blank cells in my data range cause an IRR error?
Yes, blank cells or text values within the selected array can disrupt the sequence of periodic cash flows. If a period had no cash flow, you should explicitly enter a '0' rather than leaving the cell blank to ensure the timing of the returns is calculated correctly.
