Excel IRR #NUM! Error: Causes and Fixes

In Excel, IRR returns #NUM! when it cannot find a rate that makes a set of periodic cash flows balance to a net present value of zero. Check that the cash flows include at least one positive and one negative number, then try a different guess if the cash flows could have more than one valid IRR.

What the IRR #NUM! error means

IRR estimates the internal rate of return for cash flows that occur at regular intervals, such as monthly or annually. It searches for a rate at which the present value of the inflows offsets the present value of the outflows. The rate is per interval in the values you supply; Excel does not know whether those intervals represent months, quarters, or years.

The function uses an iterative calculation. If Excel cannot find a result within its iteration limit, it returns #NUM!. Microsoft documents a default guess of 10%, a maximum of 20 iterations, and a stopping tolerance of 0.00001 percent. A calculation that does not converge within those attempts can therefore show an error even if a useful rate exists and a different starting guess would find it.

There is another basic requirement: the cash-flow series must contain at least one negative value and at least one positive value. A typical investment starts with a negative outflow and has positive receipts later. A list containing only positive amounts, only negative amounts, or zeros cannot describe an ordinary investment return for IRR to solve.

Check the cash-flow data first

Before changing the guess, inspect the cells passed to IRR. The function interprets their order as successive, equally spaced periods. It does not use dates to determine when a payment happened. An incorrectly ordered list, a missing initial cost, or a cash flow entered with the wrong sign can change the answer or prevent a meaningful calculation.

Look for these common data issues:

No outflow or no inflow: Confirm that the initial investment is usually negative and that at least one later receipt is positive, or vice versa for a financing perspective.
Numbers stored as text: A cell that looks numeric may not be treated as a number. Check its contents and convert it to a real numeric value if necessary.
Incorrect range: Make sure the formula includes the initial investment and all intended later cash flows, without accidentally including a heading or unrelated cell.
Wrong signs or duplicated amounts: Review the source records. Reversing a sign changes the economic meaning; do not flip signs just to make the error disappear.
Irregular timing: If the dates are not equally spaced, ordinary IRR may not be the right function. Use XIRR with actual dates instead.

Blank cells and text in referenced ranges can make the series behave differently from what a reader expects. Keep the calculation range simple and verify that each intended cash flow is a numeric value. Also check for other Excel errors in the source cells, since an error in an input can cause an error to flow through the calculation.

Calculate IRR with a simple example

Suppose cells A2:A4 contain three annual cash flows: -100 in A2, 60 in A3, and 60 in A4. The initial 100 is money invested, so it is negative from the investor’s perspective; the two later receipts are positive. With those values in A2:A4, enter =IRR(A2:A4). Excel returns approximately 13.07% per period, which in this example is an annual rate because the values represent successive years.

The result makes sense because, discounted at about 13.07% per year, the two receipts are worth approximately 100 in total today. This is an illustrative calculation, not a forecast or a promise about an investment. If the same three values represented months, the returned 13.07% would be a monthly rate, not an annual one.

For this example, the formula can also include an explicit starting guess. With the same cash flows in A2:A4, enter =IRR(A2:A4,0.1). The guess of 0.1 means 10%, and Excel returns approximately 13.07%. Giving a guess is optional; if omitted, Excel starts from 10%.

Try a different guess when Excel does not converge

The optional guess is a starting point for Excel’s search, not a target rate and not a constraint on the answer. If =IRR(range) returns #NUM!, try a reasonable positive or negative guess that reflects the possible scale of the result. For example, a project with large early costs and later receipts may have a return far from 10%.

With cash flows in A2:A4, a 50% starting guess is entered as =IRR(A2:A4,0.5). The guess is a decimal, so 0.5 means 50%, not 0.5%. If the cash flows are the -100, 60, 60 example, this formula still returns approximately 13.07%; the guess does not change the underlying cash flows or the true root.

Do not keep changing the guess until Excel produces a number that looks attractive. A different guess can lead to a different valid root when the cash-flow pattern has multiple solutions. Treat a returned rate as a mathematical result to interpret and verify, not as evidence that a project is worthwhile.

Understand multiple IRRs and unusual cash flows

For a conventional series with one initial outflow followed by positive receipts, there is generally one economically relevant IRR. Cash flows that switch signs more than once are different. For example, a project could have an initial cost, later operating receipts, and then a large cleanup or closure cost. Such a pattern can produce multiple rates that make net present value zero, or no useful real rate at all.

When multiple roots exist, IRR may return one root depending on its starting guess. That is not necessarily a spreadsheet defect: the cash-flow pattern itself makes a single rate an ambiguous summary. Try several documented guesses to diagnose the behavior, but do not present one result as the only possible return without checking the cash flows and the project’s economics.

For a clearer comparison between alternatives, consider looking at net present value at a specified discount rate, or use a measure designed for a particular analysis. Excel’s MIRR function can be useful when you want to specify financing and reinvestment rates. These methods answer different questions, so choose based on the assumptions your analysis needs rather than simply replacing an error with another output.

Use XIRR when dates are uneven

IRR assumes every value occurs at the same interval. If deposits and receipts happen on particular, irregular dates, use XIRR, which takes both cash flows and dates. The first date is the starting date, and later dates identify when the corresponding amounts occur. XIRR calculates an annualized rate based on those dates, unlike IRR’s rate per equal period.

For example, suppose A2:A4 contain the dates January 1, 2025, January 1, 2026, and January 1, 2027, while B2:B4 contain -100, 60, and 60. With dates in A2:A4 and cash flows in B2:B4, enter =XIRR(B2:B4,A2:A4). Because these dates are one year apart, Excel returns approximately 13.07% annualized. For cash flows occurring on irregular dates, the result can differ from IRR even when the amounts are identical.

Use genuine Excel dates, not date-looking text, and make sure each date lines up with the cash flow in the same row. XIRR also requires at least one positive and one negative amount. If it returns #NUM!, check the cash-flow signs and dates, then consider whether an explicit guess or a different analytical approach is appropriate.

A practical troubleshooting sequence

Work through the checks in order instead of editing the formula randomly. This makes it easier to identify whether the issue is the data, timing, or the calculation’s convergence.

Click the formula cell and confirm the values range includes all intended periods and nothing unintended.
Inspect the values themselves. Confirm there is at least one positive and one negative numeric cash flow.
Verify the order of the amounts and that the sign convention matches the viewpoint being analyzed.
Ask whether the periods are equally spaced. If they are not, arrange dates alongside amounts and use XIRR.
Try an explicit, plausible guess. If the data change signs more than once, be alert to multiple roots.
Check the workbook for errors in the referenced cells. In Excel for Microsoft 365 on Windows, use Formulas > Formula Auditing > Evaluate Formula to step through the calculation. Trace Precedents in the same Formula Auditing group can help show which cells feed the formula.

After changing the data or formula, format the result as a percentage and consider whether the period matches the label. A monthly IRR displayed as a percentage can look plausible while being mistaken for an annual return. State the period explicitly in a report, and do not annualize a monthly figure by simply multiplying by 12 if you need an effective annual rate; compounding changes the result.

Frequently Asked Questions

Why does IRR return #NUM! even though I have positive and negative values?
Mixed signs are necessary but do not guarantee that Excel will find a root from its starting guess within its iteration limit. Check timing and cell contents, try a reasonable alternate guess, and consider whether sign changes create multiple or no usable roots.

What is the default guess for Excel IRR?
If you leave out the optional guess argument, Excel uses 10%. You can supply another decimal guess as the second argument, such as 0.2 for 20%. The guess guides the search; it is not a required return or a cap on the answer.

Can IRR calculate a return if every cash flow is positive?
No meaningful investment IRR can be solved from an all-positive series alone. Include the actual outflow as a negative amount if that reflects the transaction. Do not add an artificial negative value merely to force Excel to return a number.

Should I use IRR or XIRR?
Use IRR when cash flows occur at regular, equal intervals and you want a rate per interval. Use XIRR when actual dates are important or periods are irregular; it uses the dates and returns an annualized rate.

Does a higher IRR always mean a better investment?
No. IRR is a rate summary and does not by itself show the project’s scale, risk, timing assumptions, or total value created. Compare cash flows and assumptions, and use other measures where appropriate. For real financial decisions, check the actual terms with the lender or a qualified professional; this spreadsheet example is not financial advice.

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.