Why Is IRR Not Working in Excel? Causes and Fixes Explained

IRR in Excel usually fails because the cash-flow range has the wrong signs or data, the cash flows are not evenly spaced, or Excel cannot find a rate from its starting guess. Check that the range contains at least one negative and one positive number, put a zero in any period with no cash flow, and use XIRR instead of IRR when dates are irregular.

What IRR calculates—and what it needs

Excel’s IRR function estimates the rate that makes the net present value of a series of periodic cash flows equal to zero. A typical investment starts with a payment (a negative cash flow) and then has receipts (positive cash flows). The function needs the cash flows in chronological order and assumes the time between each one is the same. For example, entries in a monthly series must represent consecutive months, not a mixture of monthly and annual amounts.

With cash flows in B2:B5, including an initial outflow followed by receipts, the formula is =IRR(B2:B5). IRR returns a rate per period represented by the values: if each row is one year, the result is an annual rate; if each row is one month, it is a monthly rate. A monthly result is not automatically converted to an annual rate.

IRR is an estimate found through iteration rather than a direct calculation. Excel starts with a guess—10% when none is supplied—and tries to find a rate that satisfies the cash-flow equation. That process explains why some otherwise valid cash-flow patterns need a different guess or may have more than one possible answer.

Check the cash-flow signs, order, and spacing

The first checks are basic, but they account for many apparent failures. A range containing only positive values or only negative values has no ordinary IRR solution. The initial investment is generally entered as negative, and later receipts as positive. Do not make every amount positive just because the source report describes them as costs or revenue; use the cash flow from the perspective of the person or project being evaluated.

Confirm both signs are present. The selected range needs at least one negative and at least one positive cash flow.
Check chronological order. IRR treats the first value as time zero and each following value as the next equal-length period.
Use zero for a no-cash-flow period. A blank cell may be ignored rather than treated as a period containing zero. If a month has no movement, enter 0 so later cash flows stay in the correct month.
Check the selected range. Make sure it includes the initial investment and all later cash flows, without accidentally including headings or unrelated totals.

For instance, suppose a project has a cost today, no cash flow in year one, and a receipt in year two. Omitting the year-one row makes Excel treat the receipt as if it happened after one period. Entering a zero for year one preserves the two-period timing. The resulting rate can differ substantially because timing is part of the calculation, not just a label in the worksheet.

Understand #NUM!, #VALUE!, and unexpected answers

#NUM! commonly means Excel could not converge on a rate from its starting point, or that the cash-flow pattern does not produce a usable solution. It can also occur when there is no valid rate for the values provided. A different guess may help if a solution exists, but it cannot fix missing cash flows, incorrect signs, or an inappropriate IRR model.

#VALUE! usually points to an invalid argument or a value Excel cannot use as supplied. Check that the formula’s first argument is a valid range or array and inspect cells for imported text, errors, or unexpected content. In a referenced range, text, logical values, and empty cells may be ignored; that can silently leave out intended cash flows rather than produce the expected calculation. Convert numeric-looking text to real numbers and use explicit zeros for empty periods.

An answer that looks wrong is not always an Excel error. IRR can return a mathematically valid rate based on the values and order it received even when those values do not represent the real payment schedule. Also check whether the result cell is formatted as a percentage. A stored value such as 0.13 displays as 13% with percentage formatting, but as 0.13 with general number formatting.

Use a different guess when IRR returns #NUM!

The optional guess tells Excel where to begin its search. If the default 10% does not lead to a result, trying a reasonable alternative can help. With cash flows in B2:B5, the formula =IRR(B2:B5,0.2) starts the search at 20%. The guess is not a rate to add to the answer, and it does not change the cash flows. It is a starting point for the iterative calculation.

A different guess is particularly relevant when cash flows change sign more than once—for example, a project has an initial cost, later receipts, and then a large cleanup or closure cost. Such a pattern can have multiple IRRs, or more than one rate that makes net present value zero. Different guesses can lead Excel toward different roots, while some guesses may fail to converge. In that situation, do not assume that the first answer is the only meaningful one. Inspect the cash-flow pattern and consider comparing net present value at a rate chosen for the analysis.

If Excel keeps returning #NUM!, verify the data first, then try one or two plausible guesses. Avoid repeatedly entering arbitrary guesses until a preferred answer appears. A guess can expose a valid solution, but selecting among multiple mathematical answers requires understanding the cash flows and the question being asked.

Use XIRR for dates that are not evenly spaced

IRR assumes one equal interval between every adjacent cash flow. That is appropriate for a regular monthly or annual series, but not for payments on actual dates that vary—for example, an investment made on January 10, a receipt on March 31, and another receipt the following January. Putting those amounts in consecutive rows and using IRR treats them as equally spaced periods, regardless of the dates.

For dated cash flows, use Excel’s XIRR function, which uses the dates to account for the time between payments. With cash-flow amounts in B2:B4 and their corresponding Excel dates in A2:A4, use =XIRR(B2:B4,A2:A4). Keep each amount aligned with its date, make sure the dates are valid Excel dates rather than text, and include at least one positive and one negative cash flow. XIRR is designed for irregularly timed cash flows; it is not simply a way to force IRR to return a number.

When cash flows are genuinely periodic, use IRR and document the period represented by each row. When their dates determine the timing, use XIRR. Choosing the function that matches the schedule prevents a plausible-looking result from being based on the wrong timing assumption.

A small example to verify the setup

Suppose an investment costs 100 at the start and pays back 60 at the end of each of the next two years. Put -100 in B2, 60 in B3, and 60 in B4. With those annual cash flows in B2:B4, enter =IRR(B2:B4). Excel returns approximately 13.07% per year.

The result is reasonable because discounting the two receipts at about 13.07% gives a combined present value of approximately 100: the first 60 is worth about 53.07 today, and the second 60 is worth about 46.93. Together they match the initial outlay. If the first value were entered as positive, if a period were missing, or if the second receipt were accidentally left out of the range, Excel would be solving a different problem.

This example is also a useful worksheet check: make sure the initial payment is in the first row, later cash flows follow in order, and the displayed percentage matches the period length. It is illustrative arithmetic, not a promise that an actual investment will earn that rate.

Common misconceptions and practical checks

One misconception is that IRR always returns an annual percentage. It does not know whether a row means a month, quarter, or year; the user’s schedule defines the period. Another is that a successful calculation proves an investment is attractive. IRR is one way to summarize a cash-flow pattern, but it does not by itself explain risk, the amount invested, or whether the timing assumptions are appropriate.

Before relying on a result, check that the range contains numeric cash flows, that each row represents the intended period, and that the sign convention is consistent. Confirm the output is formatted as a percentage and note whether the rate is monthly, annual, or another period. If dates are uneven, use XIRR. If the series changes signs repeatedly or different guesses produce different results, investigate the possibility of multiple rates rather than treating one output as definitive.

In Microsoft 365 for Windows, the IRR and XIRR formulas work in the worksheet in the usual way. The same functions are available in current Excel for Mac and Excel for the web, although ribbon layout and some data-conversion controls can differ. If a formula is rejected despite correct cell references, check the regional list separator: some installations expect semicolons instead of commas. The formulas above use commas as requested for standard English-language Excel syntax.

Frequently Asked Questions

Why does Excel IRR return #NUM!?
Excel may not find a rate from its default guess, the cash flows may not have a valid solution, or the range may have the wrong signs or timing. Check the inputs first; if they are sound, try a plausible optional guess.

Can IRR work with only positive cash flows?
No ordinary IRR can make the net present value of an all-positive series equal zero at a suitable finite rate. A typical calculation needs at least one outflow and one inflow, represented by negative and positive numbers.

Should periods with no cash flow be blank or zero?
Enter zero. Empty cells in a referenced range may be ignored, which can cause later cash flows to be treated as closer together than they really are. A zero records the period without a payment.

What is the difference between IRR and XIRR?
IRR assumes equal time intervals between cash flows. XIRR uses a corresponding date for each cash flow, so it is the better fit when actual payment dates are irregular.

Why does changing the guess change my IRR result?
Some cash-flow patterns can have more than one valid IRR, especially when the signs change multiple times. The guess guides Excel’s search and can lead it to a different solution; review the cash flows and do not assume a single rate is unique.

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.