Why Is My IRR Formula Not Working in Excel?

Your IRR formula usually is not working because the cash-flow range has no change from negative to positive (or positive to negative), the cash flows are not in equal time periods, or Excel cannot converge from its starting guess. Check the signs and order first; then use a different guess or XIRR if the dates are irregular. The error message and the layout of the cash flows help pinpoint which fix applies.

What Excel’s IRR function calculates

IRR means internal rate of return. In Excel, IRR estimates the rate that makes the net present value of a series of regularly spaced cash flows equal to zero. The first value is usually the initial investment, shown as a negative number, and later receipts are positive. Each position in the range represents one equal period, such as a month, quarter, or year.

For example, with an initial outlay of $1,000 followed by a $1,100 receipt one year later, the return is 10% for that period. With the initial outlay in B2 and the receipt one period later in B3, enter =IRR(B2:B3). It returns 10%. The result is a rate per period, not automatically an annual rate: if each row represents a month, the result is monthly.

IRR uses an iterative calculation rather than a simple division. Its optional guess is a starting point for the search, so different cash-flow patterns can require a different guess. If Excel cannot find a result under its search process, it may return an error even when the formula itself is syntactically valid.

Check the cash-flow signs and order

The range must contain at least one negative and one positive cash flow. An investment outflow is normally negative, and money received is positive. If every value is positive, every value is negative, or the amounts are stored as text rather than numbers, there may be no meaningful rate for Excel to calculate.

Use a simple timeline so that the order is unambiguous. For instance, put the initial investment in the first period, then put each later payment or receipt in the row for the period when it occurs. Do not put a label, date, or running balance inside the values range in place of a cash-flow amount. Labels next to the range are fine; the range passed to IRR should represent the cash flows.

A quick audit can catch sign mistakes:

Confirm the initial investment is negative, unless the perspective of the cash flows intentionally treats it otherwise.
Confirm at least one subsequent amount has the opposite sign.
Check whether a minus sign was omitted or whether an amount was accidentally entered as text.
Verify that the range starts with the first cash flow and includes the intended later periods in chronological order.

Do not flip signs just to remove an error. Reversing every sign does not change the mathematical rate, but changing only one or more signs changes the cash-flow pattern and therefore the result.

Diagnose the error message

#NUM! is the most common IRR-specific error. It can mean the values do not include both positive and negative amounts, or that Excel did not converge on a result using the supplied starting guess. First confirm the signs and the range. If those are sound, try a plausible guess, and consider whether the cash flows could produce more than one IRR.

#VALUE! generally points to an argument Excel cannot use as supplied, such as an invalid reference or a nonnumeric guess. Check that the first argument refers to the cash-flow values and that an optional guess is a number. Also inspect the range for existing error values such as #N/A or #DIV/0!, since an error in an input cell can affect the calculation. Text labels in a separate column should not be included accidentally.

If the cell displays a result that looks wrong rather than an error, check its number format. A value such as 0.1 may display as 0.10 or 10%, depending on formatting. Select the result and use Home > Number to choose Percentage or another suitable format. Formatting changes how the result appears; it does not change the calculation.

Use a guess when Excel cannot converge

The optional guess gives IRR a starting point for its iterative search. Excel uses 10% when no guess is supplied. A guess is not a target or a guaranteed answer: it helps determine which solution the calculation may find. If a conventional cash-flow series still returns #NUM!, try a reasonable rate based on the scale and timing of the flows.

With the cash flows -1,000 in B2 and 1,210 in B3, the return over one period is 21%. Enter =IRR(B2:B3,0.2) with that layout; the result is 21%. For a more complicated range, a guess such as 0.1 means 10%, while 10 means 1,000%, so enter rates as decimal values rather than whole percentages.

Changing the guess can matter especially when cash flows switch signs more than once. In that situation, there may be multiple rates that make net present value zero. A returned number is not automatically the only possible IRR or the economically relevant one. Compare results from different guesses and inspect the cash-flow pattern before interpreting the rate.

Make sure periods are equally spaced

IRR assumes that every cash flow is separated by the same length of time. A range with annual amounts should have one value per year; a monthly series should have one value per month. Missing periods should generally be represented by a zero in the appropriate period, rather than by removing a row and making the next payment appear sooner.

For example, if payments occur in January, February, and April, an IRR range with only those three amounts treats them as three consecutive equal periods. To preserve monthly timing, include a zero for March, so April remains four monthly periods after January. This is a frequent reason a formula produces a number but that number does not match the actual schedule.

If cash flows happen on specific, uneven dates, use XIRR rather than trying to make IRR account for the gaps. XIRR calculates an annualized return using the actual dates and a 365-day year. It needs one date for each cash-flow amount.

With cash flows in B2:B3 of -1,000 and 1,100, and their dates in A2:A3 as January 1, 2025 and January 1, 2026, enter =XIRR(B2:B3,A2:A3). Because those dates are 365 days apart, the result is 10%. If the dates are different, XIRR uses the actual day spacing and the annualized result may differ from a simple periodic IRR.

Check the formula, range, and Excel settings

The standard syntax is =IRR(values,[guess]). The guess is optional, so a basic formula can contain only the cash-flow range. Make sure the opening parenthesis has a matching closing parenthesis, that the range is the intended one, and that the optional second argument is separated from the range correctly.

Excel installations normally accept commas in formulas, but regional settings can use a different list separator. If Excel rejects a formula that otherwise looks correct, check the separator expected by that installation. Also ensure that the cell is not formatted as Text: change it to General, re-enter the formula, and press Enter. A formula stored as text may display literally instead of calculating.

Calculation mode can also make results appear stale. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options and select Automatic if appropriate. On Mac, the Calculation options are also on the Formulas tab, though the ribbon layout can differ. Press F9 on Windows to recalculate formulas; on Mac, use fn+F9 on keyboards where the function keys control hardware features. These steps help when a result is not updating, but they will not fix invalid cash flows.

Understand multiple IRRs and misleading results

Traditional investment cash flows often have one initial negative amount followed by positive receipts. A project with later costs, cleanup expenses, or other reversals may switch signs more than once. Such a pattern can have multiple IRRs or no useful single IRR, even if Excel returns a result.

For example, with -100, 230, and -132 in A2:A4, the cash flows change sign twice. Enter =IRR(A2:A4,0.1) with those values in that order; Excel can return 10%. Enter =IRR(A2:A4,0.2) for the same range; Excel can return 20%. Both rates make the periodic net present value zero, so reporting one without explaining the cash-flow pattern can be misleading.

For this reason, IRR should not be treated as a standalone verdict on a project. The rate does not explain the size of the investment, the timing of every payment, or the assumptions behind the cash flows. When the pattern is unconventional, reviewing net present value at a chosen discount rate can provide a more interpretable companion measure. Financial decisions depend on real terms and assumptions, so verify those with the lender or a qualified professional rather than relying on a spreadsheet result alone.

Frequently Asked Questions

Why does my IRR formula return #NUM!?
Check that the range includes at least one positive and one negative cash flow. If it does, Excel may not have converged from its default 10% guess; try a reasonable alternative. Multiple sign changes can also mean there is more than one possible IRR.

Why does Excel show 0% for IRR?
The actual result may be close to zero, or the displayed percentage may be rounded. Increase the displayed decimal places using Home > Increase Decimal and inspect the underlying cash flows. Also check that the cash-flow periods and signs are correct.

Should I use IRR or XIRR?
Use IRR when the cash flows represent equally spaced periods. Use XIRR when you have actual dates and the gaps between cash flows are uneven, or when the schedule does not fit a regular monthly, quarterly, or annual sequence.

Does IRR return an annual rate?
Not necessarily. IRR returns a rate for each period represented by one position in the values range. If the entries are monthly, the result is monthly; if they are annual, it is annual. XIRR returns an annualized rate based on dates.

Can a range contain zero cash flows?
Yes. A zero can represent a period with no payment and is useful for keeping a regular timeline intact. The range still needs at least one positive and one negative cash flow for a meaningful IRR calculation.

A practical troubleshooting sequence

When an IRR formula fails, troubleshoot in a fixed order rather than changing several things at once:

Read the exact error or inspect whether the formula is displayed as text.
Confirm the values range contains numeric cash flows with at least one positive and one negative amount.
Verify that the first value and every later row correspond to the intended period, with zeros for empty periods when needed.
Use IRR only for equally spaced periods; switch to XIRR when actual dates are irregular.
If Excel returns #NUM!, try a plausible numeric guess and consider whether the cash flows have multiple sign changes.
Check calculation mode and number formatting if the result is stale or merely looks unexpected.

This sequence separates formula syntax problems from cash-flow modeling problems. Once the signs, spacing, and function match the real schedule, the output is much easier to interpret—and a valid result is less likely to be mistaken for a trustworthy conclusion when the underlying cash flows do not support one clear rate.

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.