IRR Formula Not Working in Excel? Causes and Fixes

The IRR formula usually fails because the cash-flow range is incomplete or contains no valid positive-and-negative combination, the cash flows are not evenly spaced in time, or Excel cannot find a solution from its starting guess. Check the signs and timing first; then use a different guess or switch to XIRR if the dates are irregular.

What IRR calculates—and what it assumes

IRR means internal rate of return. In Excel, it estimates the interest rate that makes the net present value of a series of cash flows equal to zero. In practical terms, it is the rate at which the present value of money received balances the present value of money paid out.

The key assumption is that the cash flows occur at regular, equal intervals. For example, entries in a range might represent the initial investment followed by one payment at the end of each year. IRR does not read dates from the range: it treats the first value as time zero, the next as period one, and so on. If the entries are months apart in one place and years apart in another, ordinary IRR does not know that.

The first cash flow is commonly a negative investment, while later proceeds are positive. A negative value represents money going out; a positive value represents money coming in. Both signs must appear somewhere in the values supplied to IRR. The first value does not technically have to be negative, but the cash-flow direction should reflect the real transaction.

Check the range, signs, and period order

A reliable first check is to inspect every cell in the range from top to bottom. Make sure the range includes the initial investment and all later cash flows, and that the order matches the sequence of periods. A common mistake is to leave the initial investment out of the range or to include a heading such as “Year 1” among the cash-flow cells.

For an illustrative annual project with an initial cost of $1,000 and four $300 receipts, put the five cash flows in B2:B6 in chronological order: -1000, 300, 300, 300, 300. With that layout, enter =IRR(B2:B6) in another cell. It returns approximately 7.71% per period, which here means per year because the cash flows are annual. The result is plausible: discounting those four receipts at roughly 7.71% makes their combined present value close to $1,000.

Look for these range problems before changing the formula:

The initial outflow is missing, entered as a positive number, or stored outside the selected range.
A later receipt or cost has the wrong sign. A cost paid later should normally be negative.
The range includes a label, subtotal, or unrelated number instead of one cash flow per period.
A zero-cash-flow period was skipped. If a year has no cash flow, retaining a zero can preserve the spacing.
The cells look numeric but contain text, such as values imported with an apostrophe or an unexpected currency character. Convert them to actual numbers and check the formula range again.

Formatting a cell as Currency or Percentage does not by itself change a numeric value into text. Conversely, a value that looks like a number on the screen may still be text, so check the underlying cell contents rather than relying only on appearance.

Understand #NUM!, #VALUE!, and other results

#NUM! is the error most associated with IRR. Excel calculates IRR iteratively: it tries a rate, evaluates the cash flows, and adjusts the rate in search of a value that makes net present value zero. If it does not converge under its calculation process, it returns #NUM!. A cash-flow sequence with no usable solution, an unsuitable starting guess, or a difficult pattern of sign changes can all be involved.

#VALUE! often points to an argument or data problem. Check for text in the values argument, a malformed formula, or cells that were imported in a way that left numbers stored as text. The IRR values argument should refer to the cash-flow numbers, not to dates or descriptive labels.

A result of 0% is not automatically an error. It can mean that the cash flows balance without a positive or negative return, or that the displayed number has been rounded. Increase the number of displayed decimal places to see whether a small rate is being hidden. Also confirm that the values are in the intended units: mixing dollars with thousands of dollars can make the result nonsensical even if Excel calculates it.

Excel’s workbook setting for iterative calculation is generally not the fix for IRR. That setting is used for circular references in formulas; IRR has its own numerical search. Changing workbook iteration settings will not repair bad cash-flow signs or irregular dates.

Try a different guess when IRR returns #NUM!

IRR accepts an optional guess: a starting rate for its search. If you omit it, Excel uses 10%. A guess is not a required return, nor does it force Excel to return that rate; it gives the calculation a place to begin. If Excel returns #NUM!, trying a reasonable alternative can help it converge to a solution.

For the same illustrative cash flows in B2:B6 (-$1,000 followed by four $300 annual receipts), use =IRR(B2:B6,0.1) to specify a 10% starting guess. The result is still approximately 7.71% per year; the guess changes the starting point, not the cash-flow data or the answer when the same unique solution is found.

If a new guess produces a different valid rate, do not assume the higher result is automatically correct. The cash-flow pattern may have multiple possible IRRs, or one guess may have led to a different mathematical solution. Review the pattern and test whether the candidate rate makes the net present value approximately zero. A guess should be chosen to help diagnose convergence, not to produce a preferred-looking return.

Use XIRR when cash-flow dates are irregular

Use XIRR when actual transactions occur on dates that are not evenly spaced, such as deposits on different days or receipts at irregular intervals. XIRR takes both cash-flow values and their corresponding dates into account. The values and dates ranges must have the same number of entries, and each date must be paired with the cash flow on that date. In Microsoft 365 for Windows, enter the function directly in a worksheet cell; the same function is available in current Excel for Mac.

For a concrete example, enter the same cash flows -1000, 300, 300, 300, 300 in B2:B6. Put these corresponding dates in A2:A6: 1-Jan-2025, 1-Jan-2026, 1-Jan-2027, 1-Jan-2028, and 31-Dec-2028. Those dates are each 365 days apart. With that layout, enter =XIRR(B2:B6,A2:A6). It returns approximately 7.71% annually, matching the ordinary IRR example because the dates are equally spaced by 365 days. With real irregular dates, XIRR can return a different result because it accounts for the time between cash flows.

Do not use XIRR merely to silence an error if the dates are missing or incorrectly paired. Check that dates are real Excel dates, not text that only looks like a date, and that the first date corresponds to the first cash flow. If the dates are genuinely regular, IRR is often the simpler choice; if they are not, XIRR is the appropriate comparison.

Watch for multiple IRRs and misleading interpretations

Most simple projects have one initial outflow followed by positive receipts, which usually produces one meaningful IRR. A project that alternates between costs and receipts can have multiple sign changes. In that case, the equation can have more than one rate that makes net present value zero, and Excel may return a rate that depends on the guess.

For example, consider cash flows of -100, 230, and -132 at equally spaced periods. The cash-flow direction changes twice: outflow, inflow, then outflow. The corresponding IRR equation has two positive rate solutions, 10% and 20%. This is a mathematical reason that an IRR result can appear inconsistent when the guess changes; it is not necessarily a spreadsheet bug.

When a project has several sign changes, compare its net present value at relevant rates or examine the cash-flow economics instead of relying on a single IRR. IRR also does not describe the project’s size: a small investment can have a high percentage return but generate less money than a larger project with a lower rate. Treat the result as a measure based on the cash flows and timing supplied, not as a complete investment decision.

A practical troubleshooting sequence

Work through the problem in this order rather than changing several things at once. This makes it easier to identify the cause and keep the calculation auditable:

Confirm that the selected range contains only the intended cash-flow values, in chronological order.
Verify that at least one value is negative and at least one is positive; confirm every later cost or receipt has the correct sign.
Check that each value represents one equally spaced period. Use a zero for a period with no cash flow if needed to keep later values aligned.
Check for numbers stored as text, labels, accidental blanks, and missing cash flows. Recalculate the range after correcting the data.
If the data are correct but Excel returns #NUM!, try a different plausible guess. If different guesses return different rates, investigate multiple IRRs.
If the transaction dates are uneven, use XIRR with a matching date range rather than ordinary IRR.
Check the result’s units and display precision. A monthly IRR is a monthly rate, not an annual rate, and a rounded display can hide detail.

For a formula that appears not to update after editing values, check Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. In Excel for Mac, use Formulas > Calculation Options and select Automatic. Calculation mode can explain a stale displayed result, but it will not resolve #NUM! caused by the cash-flow pattern or a poor guess.

Frequently Asked Questions

Why does Excel IRR say #NUM! even though I have positive and negative values?
Having both signs is necessary but does not guarantee convergence or a unique solution. Check the range and period order, try another guess, and look for multiple sign changes. If the dates are uneven, use XIRR.

Does the first IRR cash flow have to be negative?
No. Excel requires at least one positive and one negative cash flow, but the first entry is not required to be negative. In a typical investment model, it is negative because it represents the initial amount paid.

Why is my IRR different from the return I expected?
IRR depends on the signs, amounts, order, and spacing of every cash flow. It is a per-period rate, and it can be affected by multiple solutions. Check that your range reflects the real cash flows and that you are comparing rates over the same period.

Should I multiply a monthly IRR by 12 to get an annual return?
Not for a compounded annual rate. Multiplying by 12 gives a simple annualized approximation, while compounding a monthly rate produces a different annual figure. Make sure the period basis is clear before comparing it with an annual rate.

When should I use XIRR instead of IRR?
Use XIRR when cash flows occur on irregular dates and you have a date for each value. Use IRR when the values occur at equal intervals, such as each month or each year.

Can changing the guess change the IRR answer?
It can change whether Excel converges, and with multiple valid solutions it can lead to a different returned rate. A changed answer is a reason to inspect the cash-flow pattern and verify the result, not to select whichever rate looks best.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.