Excel IRR #DIV/0! Error: Why It Happens and How to Fix

If Excel shows #DIV/0! beside IRR, the error is usually in one of the cash-flow formulas or cells: IRR normally reports #NUM! when it cannot calculate a result, not #DIV/0!. Trace the error to its source and correct it there; do not replace it with zero unless zero is genuinely the right cash-flow value.

What #DIV/0! means when you use IRR

The #DIV/0! error means a formula has tried to divide a number by zero or by an empty cell treated as zero. IRR does not ask you to divide by anything in its arguments. It takes a sequence of cash flows and estimates the rate that makes their net present value equal to zero. So when #DIV/0! appears in a result cell, first check whether a cash-flow cell contains that error, or whether the formula producing a cash flow divides by a zero or blank input.

For example, suppose a project’s cash flow in D2 is calculated from revenue in B2 divided by units in C2. If C2 is zero, D2 can show #DIV/0!, and an IRR formula that uses D2 can also show an error. Correcting the division or the underlying input is more useful than changing the IRR formula. If the IRR cell instead shows #NUM!, that is a different problem: check the cash-flow signs, the guess, and whether a solution can be found.

What IRR calculates

IRR stands for internal rate of return. It estimates the periodic rate of return for a series of cash flows spaced at regular intervals. The first value is commonly an initial investment, written as a negative amount, followed by later receipts or payments. The order matters: Excel treats each value as occurring at the end of a successive period, except that the first value is at the beginning of the first period.

With an initial investment of $1,000 followed by receipts of $300, $400, and $500, put those values in B2:B5, in that order. Enter this formula in another cell: =IRR(B2:B5). Excel returns approximately 8.90% per period. The answer is an estimate; display it with a suitable percentage format and remember that the period is determined by the spacing of the cash flows. If the amounts are annual, the rate is annual; if they are monthly, it is monthly.

IRR is not a measure of the cash received in total, and it is not automatically an annual rate. It is also not a substitute for checking whether the cash-flow assumptions make sense. A large return can result from a small initial outlay or an unusual pattern, so interpret the result alongside the underlying amounts and project assumptions.

Trace the error to the cash-flow cells

Start by inspecting every cell included in the IRR range. Look for visible #DIV/0! errors, and check formulas even in cells that look blank or are far from the result cell. A formula may return an error because a source cell is empty, zero, or calculated incorrectly. Click a suspicious cell and read its formula in the formula bar; then follow the references to the inputs that supply its denominator.

In Excel for Microsoft 365 on Windows, select the IRR result cell and use Formulas > Error Checking to review a reported error. You can also select a cash-flow cell and choose Formulas > Trace Precedents to display cells it depends on. These tools help reveal where a calculation is coming from, but they do not decide what the correct financial treatment should be. Confirm that any zero or missing input reflects the actual situation.

If the cash-flow range contains a formula error, fix that formula or its source data first, then recalculate. If the error is not visible in the range, verify that the IRR formula references the intended cells and that the referenced cells contain the figures you expect. A formula can be syntactically valid while pointing at the wrong period or including an unrelated calculation.

Correct a division in a cash-flow formula

Suppose B2 contains revenue and C2 contains units, and D2 is intended to calculate revenue per unit. If C2 is zero, ordinary division produces #DIV/0!. The correct response depends on what zero units means for your model. If there truly should be no cash flow for that period, you might return zero; if the input is missing or invalid, returning zero could hide a data problem.

With revenue in B2, units in C2, and the per-unit calculation in D2, use this only when zero units should mean a zero result: =IF(C2=0,0,B2/C2). The formula returns 0 when C2 equals zero; otherwise it returns B2 divided by C2. For example, if B2 is 120 and C2 is 0, the result is 0. If C2 is 10, the result is 12. Check that this is the intended meaning before letting the result feed into IRR.

If a zero denominator means the data needs correction, it is usually better to fix the input or make the model flag the problem rather than silently substitute a value. Using IFERROR to turn every error into zero can make a worksheet look clean while changing the financial result. Only suppress an error when the replacement value has a clear, defensible meaning.

Check cash-flow signs, order, and periods

After removing any upstream errors, check that the cash flows represent the investment from one consistent point of view. An investor’s initial outlay is generally negative, while money received is positive. If all values have the same sign, IRR cannot find a rate that makes net present value zero; Excel typically returns #NUM!, rather than #DIV/0!.

Use a negative value for an investment or payment made, and a positive value for money received, as appropriate to the viewpoint of the analysis.
Keep the values in chronological order. IRR reads the order of the cells as the order of the cash flows.
Include actual zero cash flows for periods with no payment when those periods belong in the sequence. Do not use a blank to stand in for a missing amount without deciding how it should be treated.
Use evenly spaced periods. Standard IRR assumes each value is one equal period after the previous one; it does not use calendar dates.

For a simple annual project, B2:B5 might contain the initial cost and three year-end receipts. If one year has no receipt, a numeric 0 in that year keeps the timing clear. A blank or text entry may be ignored when Excel evaluates a referenced range, so it should not be used casually to represent a meaningful zero-period cash flow. Make the range reflect the intended schedule.

When IRR returns #NUM! instead

Excel calculates IRR iteratively: it tries a rate, evaluates the cash flows at that rate, and adjusts its estimate in search of a result. The optional guess argument gives Excel a starting estimate. If it cannot find a result under its calculation process, it returns #NUM!. That is the error to investigate when the cash-flow cells themselves do not contain #DIV/0!.

With the example values in B2:B5 (-1000, 300, 400, and 500), enter this formula in another cell to provide a 10% starting guess: =IRR(B2:B5,0.1). It returns approximately 8.90%, the same approximate rate as the default-guess calculation. A different guess can help Excel converge for some cash-flow patterns, but it does not repair bad inputs or guarantee that a meaningful rate exists.

Some non-standard cash-flow patterns can have more than one mathematically valid IRR, particularly when the signs change more than once. In that situation, different starting guesses may lead Excel to different solutions. Treat the result as one possible rate, inspect the cash-flow pattern, and consider whether IRR is suitable for comparing the projects. A guess is a computational starting point, not an assumption that the investment will earn that return.

When to use XIRR instead

Use IRR when cash flows occur at regular intervals, such as at the end of each month or year. If the dates are irregular, XIRR is designed to use actual dates and annualize the return based on the timing between them. XIRR will not automatically fix a #DIV/0! in a cash-flow formula: its cash-flow inputs still need to be valid numbers, and its dates must be valid dates.

With cash flows in B2:B5 and their corresponding Excel dates in A2:A5, enter =XIRR(B2:B5,A2:A5) in another cell. XIRR returns an annualized rate based on those dated cash flows; the exact result depends on the amounts and dates in the range. Keep each date paired with its matching cash flow and make sure the first cash flow is included. XIRR is available in Excel for Microsoft 365, as well as supported desktop Excel versions; it is not simply a different spelling of IRR, because it applies different timing assumptions.

A reliable repair checklist

Work through the problem in this order so that the final result is both error-free and financially interpretable:

Read the exact error in the result cell. #DIV/0! points first to a division in an input formula; #NUM! points toward an IRR calculation or cash-flow pattern problem.
Check the cells in the IRR range and the formulas feeding them. Correct any division by zero, missing input, or bad reference at its source.
Confirm that the range contains the intended cash flows in chronological order, with a sensible negative investment and positive receipts where appropriate.
Verify that periods are equally spaced for IRR. If dates are irregular, consider XIRR and supply the corresponding dates.
If the result is #NUM!, try a reasonable guess only after checking the data, and investigate whether the signs change more than once.
Review the result’s period and number format. A displayed percentage does not make a monthly IRR annual, and rounding can obscure small differences.

Do not judge a repair only by whether the error message disappears. If a formula was changed to return zero, blank, or another substitute, confirm that the chosen value reflects the real cash-flow model. Documenting that choice can prevent another reader from mistaking a deliberate assumption for an observed amount.

Frequently Asked Questions

Can IRR itself return #DIV/0!?
In ordinary use, Excel’s documented IRR calculation failure is #NUM!, while #DIV/0! usually comes from a cash-flow cell or a formula feeding that cell. Check the range and its precedents before changing the IRR arguments.

Why does IRR show #NUM! when my range has positive and negative values?
Opposite signs are necessary, but they do not guarantee that Excel can find a result. Check the order and values, then try a reasonable guess. Multiple sign changes can also create multiple possible rates, so a returned value may depend on the guess.

Should I replace a #DIV/0! cash flow with zero?
Only if zero is the correct financial value for that period. If the denominator is zero because an input is missing or incorrect, substituting zero hides the issue and may distort the IRR.

Does IRR calculate an annual return?
Only when the cash-flow periods are annual. IRR treats each successive value as one equal period apart, so monthly cash flows produce a monthly rate. XIRR uses actual dates and returns an annualized rate.

Will changing the guess fix #DIV/0!?
No. The guess helps Excel search for an IRR when its iteration has difficulty; it does not fix a division-by-zero error in the inputs. Find and correct the upstream error first.

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.