IRR Formula Excel #NUM! Error: Causes and Fixes

In Excel, =IRR(A2:A5) returns #NUM! when Excel cannot find an internal rate of return for the cash flows using its iterative calculation, or when the cash flows do not include both a negative and a positive value. Check the cash-flow signs and order first; if those are correct, try a different guess or confirm that the investment actually has a real IRR.

What Excel’s IRR function calculates

IRR estimates the rate at which a series of regularly spaced cash flows has a net present value of zero. In a typical investment example, the initial outlay is negative because it is money paid out, while later receipts are positive. The cash flows must occur at equal intervals, such as every month or every year. Excel does not use dates in the IRR function; it treats each value as the next period in sequence.

The function syntax is IRR(values, [guess]). The values argument is the range or array of cash flows, and guess is an optional starting estimate for Excel’s search. If you leave guess out, Excel uses 10%. The guess is not a discount rate that Excel must return; it helps determine which solution its iterative search may find.

For a basic, illustrative annual project, assume the initial investment is $1,000 and the project produces $400 at the end of each of the next three years. With cash flows in A2:A5, where A2 is -1000 and A3:A5 each contain 400, enter =IRR(A2:A5). The result is approximately 9.7% per year. At that rate, the present value of the three receipts is roughly $1,000, offsetting the initial outlay. The example explains the spreadsheet calculation only; it is not financial or investment advice.

Check the cash-flow signs and range

A valid conventional investment normally has at least one negative cash flow and at least one positive cash flow. If every value is positive, or every value is negative, there is no rate that can make the cash flows balance in the usual IRR calculation. A missing minus sign on the initial purchase price is a frequent reason for the error.

With the initial outlay in A2 and subsequent cash flows in A3:A5, make sure A2 contains a negative number, such as -1000, rather than 1000. Then use =IRR(A2:A5). This layout returns approximately 9.7% when A3:A5 each contain 400. Include the initial outlay in the range: selecting only the receipts would give Excel no negative cash flow to balance them.

Also check that the selected range contains the full series, in the correct chronological order. IRR uses the sequence of values, not row labels or dates. A blank row or an accidentally omitted payment can change the periods represented and therefore change the result. Text, logical values, and empty cells in a referenced range are not cash-flow amounts; make sure each intended payment is stored as a numeric value. A text number imported from another system may look like a number but not behave like one.

Use a different guess when iteration fails

Excel uses an iterative method rather than solving every possible equation symbolically. According to the function’s documented behavior, it starts with the supplied guess (or 10% by default) and searches for a rate. If it does not find a result after 20 iterations, it returns #NUM!. A different initial guess can help it converge to a solution, especially when the likely return is far from 10%.

For the same A2:A5 cash-flow layout (-1000 followed by three 400 values), enter =IRR(A2:A5,0.1) to explicitly start at 10%. The result is still approximately 9.7%. If the expected rate is much higher or lower, test a reasonable alternative guess, such as 0.5 for 50% or -0.1 for -10%. A guess is entered as a decimal rate: 0.1 means 10%, not 0.1%.

Try a few informed guesses rather than changing the guess randomly. If different guesses produce different answers, investigate whether the cash flows change sign more than once; the series may have multiple valid IRRs. If many plausible guesses all produce #NUM!, that is a reason to check the math and cash-flow assumptions, not proof that another arbitrary guess will create a meaningful return.

Understand multiple IRRs and misleading results

IRR can have more than one mathematical solution when cash flows switch between negative and positive more than once. For example, assume A2:A4 contain -1000, 2300, and -1320, respectively. With that layout, =IRR(A2:A4,0.1) can return 10%, while =IRR(A2:A4,0.2) can return 20%. Both rates make the net present value zero, so neither is automatically the uniquely correct answer. The choice of guess can influence which solution the iterative search finds.

This example is also a useful correction to the idea that every #NUM! can be fixed just by increasing the guess. Some cash-flow patterns have no real solution in the range Excel can find; others have several solutions. When cash flows have multiple sign changes, report the pattern and the ambiguity rather than presenting one IRR as an unquestionable result. For decision-making, a net present value calculation at a clearly chosen discount rate can be easier to interpret, but the appropriate rate depends on the situation.

Confirm the result with net present value

A useful check is to calculate the net present value of the later cash flows at the IRR and add the initial cash flow. Excel’s NPV function treats its first listed value as occurring one period from now, so the initial investment at period zero should be added separately. Do not put the initial outlay inside the NPV range if you want this standard period-zero setup.

For the example, keep the initial outlay in A2, the three future 400 receipts in A3:A5, and put the IRR result in B2. With that layout, enter =NPV(B2,A3:A5)+A2 in another cell. The result should be close to zero, subject to rounding. If it is far from zero, check that the rate is stored as a percentage value such as 9.7% (equivalent to 0.097), and that the range includes exactly the future periods used in the IRR calculation.

This check does not resolve multiple-IRR ambiguity: each valid IRR can make the NPV approximately zero. It verifies that a proposed rate is consistent with the cash flows and period convention, not that the rate is the only or most useful interpretation.

Use XIRR for cash flows on irregular dates

If payments are not evenly spaced, standard IRR’s equal-period assumption can produce a misleading annual rate. Excel’s XIRR function uses actual dates and is intended for cash flows that may occur at irregular intervals. It requires a values range and a corresponding dates range; the two ranges must have the same number of entries. Dates should be valid Excel dates, and the cash flows should still include both positive and negative amounts.

For an example with cash flows in B2:B5 and their matching dates in A2:A5, enter =XIRR(B2:B5,A2:A5). The first date should correspond to the initial cash flow, and the later rows should pair each amount with its actual date. XIRR returns an annualized rate based on those dates, so its result may differ from IRR even when the rows contain the same amounts. XIRR is available in Microsoft 365 for Windows; Excel for Mac and Excel for the web also support it, though ribbon locations can vary. If the dates are equally spaced by exactly one year, IRR is generally the simpler function.

Distinguish #NUM! from other Excel errors

#NUM! points to a numerical problem such as the absence of a suitable solution or failure of the iterative search. Other errors suggest different checks. #VALUE! can indicate an argument Excel cannot interpret as a valid value or a problem with the supplied input. A misspelled function name may produce a name-related error rather than #NUM!. Check the formula bar and cell values instead of treating every error as an IRR convergence issue.

In Microsoft 365 for Windows, select the formula cell and inspect the formula bar to confirm the range and optional guess. Use Formulas > Evaluate Formula to step through how Excel evaluates the expression. You can also select the referenced cells and check whether the initial investment is negative and the later receipts are numeric. On Mac, the Evaluate Formula command is available from the Formulas tab in supported desktop versions; Excel for the web has a more limited set of auditing tools, so inspect the formula and source cells directly if that command is unavailable.

Confirm there is at least one negative and one positive cash flow.
Verify the initial payment is included and the rows are in chronological order.
Check that amounts are numeric, not text that merely looks numeric.
Try an informed alternative guess if the cash-flow series otherwise makes sense.
Use XIRR when the timing is irregular, and investigate multiple sign changes before trusting one answer.

Frequently Asked Questions

Why does IRR return #NUM! even though I have positive and negative numbers?
Having both signs is necessary, but it does not guarantee that Excel will find a solution. The cash-flow pattern may have no suitable real rate, the iteration may fail from its starting guess, or several solutions may make the result ambiguous. Check the timing, range, and sign changes, then test a sensible guess.

What should I enter for the IRR guess?
Use a plausible starting rate expressed as a decimal. For example, 0.1 means 10%. If the default 10% does not converge, try a different estimate informed by the project’s expected cash flows. The guess guides Excel’s search; it does not promise the result.

Can IRR return more than one answer?
Yes. When the cash flows switch between negative and positive more than once, the IRR equation can have multiple solutions. Different guesses can lead Excel to different answers. Check the cash-flow pattern and verify each candidate with NPV rather than assuming one result is unique.

Should I use IRR or XIRR?
Use IRR when each cash flow is one equal period after the previous one, such as monthly or annual entries. Use XIRR when the actual dates are unevenly spaced. XIRR needs a matching date for every value and calculates an annualized rate from those dates.

Does a higher IRR always mean a better investment?
No. IRR is one measure and can be difficult to interpret for projects of different sizes, durations, or cash-flow patterns. It can also have multiple solutions. This spreadsheet explanation is not financial advice; check real terms with the lender or a qualified professional before making financial decisions.

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.