Excel IRR #NUM! Error: Causes and Practical Fixes

An Excel IRR formula returns #NUM! when it cannot find a rate that makes the cash flows’ net present value zero using its iterative calculation, or when the cash-flow values do not describe a valid investment pattern. Check that the range contains both a negative and a positive amount, then try a different guess; if cash flows occur on irregular dates, use XIRR instead.

What IRR means in Excel

IRR is the internal rate of return: the periodic rate at which the present value of a series of cash flows equals zero. The first value normally represents money paid out, so it is negative; later receipts are usually positive. Excel treats the values as occurring at regular, equal intervals, such as every month or every year. It does not infer whether your rows represent months or years.

For example, an initial outlay of $1,000 followed by three annual receipts of $400 has an IRR of about 9.70% per year. In a worksheet with those amounts in B2:B5 in date order, use =IRR(B2:B5). The approximate result is 9.70%. If the same four rows represent quarters, the returned rate is per quarter, not per year. The timing represented by each row is essential to interpreting the answer.

IRR is a rate, not a dollar profit or a guarantee of what an investment will earn. It is a calculation based on the cash flows entered. A spreadsheet result can be mathematically valid while still being a poor description of a real project if amounts, timing, or assumptions are incomplete.

Check the cash-flow range first

The most common first check is whether the selected values include at least one negative and one positive number. An all-positive range has no initial outflow to recover; an all-negative range has no inflow. Neither describes the usual investment cash-flow pattern needed for a useful IRR result. Confirm that the initial cost is included and entered as a negative number, rather than omitted or mistakenly entered as positive.

For instance, suppose B2:B5 contains 1,000, 400, 400, and 400. If B2 is the investment paid at the start, correct that entry to -1,000. With the corrected values in B2:B5, use =IRR(B2:B5); the result is approximately 9.70%. This example assumes each later receipt is one equal period after the preceding value. The error may also arise from referencing the wrong cells, including a heading or excluding the starting investment.

Check the sign of every payment and receipt. Costs and deposits made by the investor are generally negative; money received is generally positive.
Inspect the exact range in the formula bar. Make sure it includes the starting outflow and the intended later cash flows.
Look for text values, blanks, or numbers stored as text. Correct imported or pasted data so the cash-flow entries are numeric.
Verify that the rows are in chronological order and that each entry represents one period of the same length.

Use the guess argument when iteration struggles

Excel uses iteration to search for a rate. The optional guess argument gives the calculation a starting point; if it is omitted, Excel uses 10%. A guess is not a limit on the answer and does not change the cash flows. It can help Excel converge to a solution, especially when the actual rate is far from the default or when a cash-flow pattern can produce more than one possible rate.

With cash flows in B2:B5, use =IRR(B2:B5,0.2) to start the search at 20%. For the example of -1,000 followed by three annual receipts of 400, this still returns approximately 9.70%; the guess is only a starting value. If you do not know the likely rate, try reasonable guesses such as 0.1, 0.2, or -0.1, interpreting each as 10%, 20%, or -10%. A guess is entered as a decimal rate, not as a whole-number percentage: use 0.2 for 20%.

Do not keep changing the guess until a preferred-looking result appears. First verify the cash flows, then compare any result with the project’s assumptions and an NPV check. Different guesses can lead to different valid answers if the cash-flow series has multiple IRRs; that is a feature of the mathematics, not proof that one answer is automatically correct.

Check for multiple IRRs

A conventional pattern has one sign change: an outflow followed by inflows. A project with later costs, cleanup expenses, or additional investment may switch signs more than once. Such a sequence can have multiple rates that make NPV zero. Excel’s IRR function returns one solution it finds from its guess, so a different starting guess can lead to a different solution or fail to converge.

For example, consider cash flows of -100, 230, and -132 in three equally spaced periods. In a worksheet with these values in B2:B4, =IRR(B2:B4,0.1) can return 10%, while =IRR(B2:B4,0.2) can return 20%. Both rates make the series’ NPV zero. This is why trying guesses may reveal a problem that a single displayed answer hides. If there are several sign changes, do not assume there is one uniquely meaningful IRR; inspect the cash-flow schedule and consider NPV at a stated hurdle rate instead.

Use XIRR for dates that are not evenly spaced

IRR assumes equal periods between entries. If you have actual dates with uneven gaps—for example, an initial payment in January, a receipt in April, and another in December—use XIRR. XIRR uses the dates to calculate an annualized return, so the result is not simply a monthly or quarterly rate. The date range and value range must contain corresponding entries, and the dates must be valid Excel dates.

With cash flows in B2:B5 and their corresponding dates in A2:A5, enter =XIRR(B2:B5,A2:A5). XIRR returns an annualized rate based on those dated cash flows. Check that each cash flow has the correct date and that the date range has the same number of entries as the value range. XIRR also needs at least one positive and one negative cash flow and can return #NUM! if it cannot find a solution.

If your entries really are equally spaced monthly amounts, IRR is appropriate for the monthly rate. To express a monthly IRR as an effective annual rate, raise one plus the monthly rate to the twelfth power, then subtract one. With the monthly IRR stored in E2, use =(1+E2)^12-1. This assumes E2 is a monthly rate and uses twelve equal monthly periods; it is not the same as simply multiplying by 12.

Verify a result with NPV

A useful check is to calculate NPV using the IRR that Excel returned. Excel’s NPV function discounts the listed values as end-of-period cash flows, so the initial investment at time zero is normally added separately. If B2 holds the initial outflow, B3:B5 holds the later periodic cash flows, and E2 holds the IRR, use =NPV(E2,B3:B5)+B2. The result should be close to zero, subject to rounding and calculation precision.

For the -1,000, 400, 400, 400 example, enter the IRR in E2 and check it with that NPV formula. The result should be approximately $0. Small residuals such as a fraction of a cent can arise because the displayed rate is rounded; increase the displayed decimal places or use the full stored value to inspect it. A result far from zero suggests the NPV range, timing assumptions, signs, or rate reference needs review.

When comparing investments, NPV at a chosen discount rate can be easier to interpret than IRR, particularly when projects have different sizes, timing, or multiple sign changes. IRR reports a percentage; NPV reports a currency amount. Neither should be treated as a substitute for checking the underlying assumptions.

Calculation settings and other troubleshooting

Excel may be set to manual calculation, which can leave displayed results out of date after edits. On Windows, check Formulas > Calculation Options > Automatic. The equivalent calculation setting is available on Mac Excel as well, though ribbon layout can differ slightly by version. Press F9 on Windows to recalculate formulas; on Mac, use the recalculation shortcut shown for your Excel version or select Formulas > Calculate Now. Recalculation alone will not fix a genuinely unsolvable IRR, but it can rule out a stale result.

Also check for accidental formula edits, such as a range that stops one row too early, a guess entered as 10 instead of 0.1, or a cash flow stored as text after importing a CSV file. A number format does not convert text into a number. If the range includes cells with formulas returning text or unexpected errors, correct those entries before relying on the IRR output.

There is no general need to enable iterative calculation in Excel’s workbook settings just because IRR is iterative internally. The IRR function performs its own numerical search. Workbook iterative calculation is a separate setting used to calculate circular references, and enabling it does not repair missing positive or negative cash flows.

When another method is more useful

If IRR produces a confusing answer, choose the method that fits the question. Use NPV when you want the value of future cash flows at a specified discount rate. Use XIRR when intervals are irregular and dates matter. If a cash-flow pattern has multiple sign changes, examine NPV across relevant rates rather than treating one IRR as the whole story. For conventional equally spaced cash flows, IRR remains a convenient way to express the rate that makes NPV zero.

For a monthly series, report clearly whether the displayed result is monthly or converted to an effective annual rate. For dated cash flows, note that XIRR returns an annualized rate. These labels prevent a common interpretation error: comparing a quarterly IRR directly with an annual percentage as if they used the same period.

Frequently Asked Questions

Why does IRR return #NUM! even though my cash flows have both signs?
Both signs are necessary, but not always sufficient. The cash flows may have no usable solution, Excel’s iteration may not converge from the default guess, or multiple solutions may exist. Check the range and timing, then try a reasonable guess and verify any result with NPV.

What guess should I use for IRR?
Use a plausible starting rate based on the cash flows, entered as a decimal: 0.15 means 15%. If you are unsure, test several reasonable guesses and investigate whether they lead to different answers. Do not treat the guess as an instruction that forces the final IRR to equal that rate.

Does IRR assume annual cash flows?
No. It assumes equal intervals, but it does not know whether they are years, quarters, or months. The result is the rate per interval represented by the rows. Use XIRR when the dates are uneven.

Can IRR return more than one answer?
Yes. Cash flows that switch between negative and positive more than once can produce multiple rates that make NPV zero. Different guesses may find different roots. Review the full cash-flow pattern and use NPV at relevant discount rates to understand its implications.

Should I enable iterative calculation to fix IRR?
No, not for IRR’s own search. Excel calculates IRR iteratively within the function. The workbook’s iterative-calculation setting is for circular references, so changing it will not correct invalid cash flows or guarantee convergence.

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.