In Excel, the optional guess argument in the IRR function is an initial estimate of the rate of return, not a limit or a required answer. If you leave it out, Excel starts at 10%; change it when IRR returns an error or when you need Excel to search toward a particular possible solution.
What the IRR guess means
IRR stands for internal rate of return. It is the rate that makes the net present value of a series of periodic cash flows equal zero. The cash flows might represent an investment, project, or loan, and must be entered in chronological order at regular intervals, such as once a year or once a month.
The guess gives Excel a starting point for its numerical search. It does not tell Excel that the final answer must be close to that percentage, and it does not cap the answer. If the calculation converges to a single solution, different reasonable guesses will generally lead to that same solution. The guess matters when the calculation struggles to converge or when the cash flows have more than one possible IRR.
Excel’s default guess is 10%, equivalent to 0.1. A guess of 10% is not a claim that the investment will earn 10%; it is simply the starting estimate used to look for a rate satisfying the cash-flow equation.
How Excel calculates IRR
For cash flows at equal time intervals, Excel searches for a rate that discounts future amounts so their value balances the initial and other cash flows. In practical terms, it repeatedly calculates the implied net present value and adjusts its rate estimate. The guess influences where that search starts.
Excel’s documented process iterates from the guess until the result is accurate to within 0.00001 percent, or until it reaches 20 attempts. If it cannot find a result within those attempts, it returns #NUM!. A different guess can help the search find a solution, but it cannot make an invalid cash-flow pattern have a unique answer.
IRR also assumes equally spaced periods. If the first amount is at the start of the first period, the next amount is one period later, and so on, the order of the values is essential. IRR does not use calendar dates to determine the length of each interval.
Entering IRR in a worksheet
Put the cash flows in a single range, with the initial investment shown as a negative number and later receipts or payments shown with their appropriate signs. Include at least one negative and one positive cash flow. The guess is optional, so you can first try the default before adding an estimate.
- Enter the cash flows in chronological order, one cell per regular period.
- Select an empty cell for the result and enter the IRR formula.
- Press Enter, then format the result as a percentage if Excel displays it as a decimal.
- If Excel returns
#NUM!, or you have reason to suspect multiple solutions, try a different guess and assess whether the result makes sense for the cash flows.
With the initial investment in B2 and the next three annual cash flows in B3:B5, use:
=IRR(B2:B5)
That formula omits the guess, so Excel uses 10%. To start the search at 5%, with the same cash flows in B2:B5, use:
=IRR(B2:B5,0.05)
The second argument is a rate expressed as a decimal: 0.05 means 5%, and 0.15 means 15%. You can also refer to a cell containing the guess, which is useful when comparing different starting estimates. The cell should contain a numeric value such as 0.05, not text such as “5 percent.”
A worked example with annual cash flows
Suppose an illustrative project requires 1,000 currency units now and is expected to return 300 after one year, 400 after two years, and 500 after three years. Enter the initial outflow as -1,000 and the later inflows as positive amounts. These are assumed annual, equally spaced cash flows, and the figures are only an example—not a forecast or investment recommendation.
With -1,000, 300, 400, and 500 in B2:B5 in that order, enter:
=IRR(B2:B5)
Excel returns an annual IRR of approximately 8.9% (about 8.89%). At that rate, the present values of the three future inflows total roughly 1,000, balancing the initial outflow. Entering a different sensible guess, such as 5%, should lead to essentially the same result for this cash-flow pattern because it has one sign change and one relevant root.
To check the rate another way, Excel’s NPV function discounts the later cash flows but treats them as occurring at the end of each period. The initial amount at time zero must therefore be added separately. With the initial outflow in B2, the three future cash flows in B3:B5, and the calculated IRR in C2, the check is:
=NPV(C2,B3:B5)+B2
The result should be close to zero, with a small difference possible from rounding or displayed precision. Do not include the time-zero amount inside the NPV range in this check; doing so discounts it by one period and misstates the calculation.
How to choose a guess
For an ordinary project with one initial outflow followed only by inflows, begin with the default. If Excel returns a plausible result, changing the guess usually is not necessary. If it returns #NUM!, use a starting estimate informed by the scale and direction of the cash flows, then try a few alternatives rather than treating one guess as automatically correct.
There is no universal “best” guess. A reasonable approach is to try a positive rate when the cash flows describe an investment that appears to earn a positive return, or a negative rate if the amounts imply a loss. If the answer changes substantially between guesses, investigate the cash-flow pattern rather than selecting whichever result looks most attractive.
- Check that the initial outflow has a negative sign and later receipts have positive signs.
- Confirm that the range contains the intended periods and that no cash flow was omitted or placed out of sequence.
- Try a guess near a plausible rate, then compare the result with the cash flows and a separate NPV check.
- If the signs change more than once, look for multiple possible IRRs before interpreting any one result.
Why different guesses can produce different answers
Some cash-flow sequences switch between negative and positive more than once. These nonconventional patterns can have multiple rates that make NPV zero. A guess can then affect which solution Excel finds, so different guesses may return different valid IRRs. This is not necessarily a spreadsheet error: it reflects ambiguity in summarizing that cash-flow pattern with one rate.
For example, consider cash flows of -100, 230, and -132 at three consecutive annual points. The pattern has two sign changes. Its NPV equation has two solutions: 10% and 20%. If those values are in B2:B4 in chronological order, formulas with different starting estimates can be used to investigate the solutions:
=IRR(B2:B4,0.08)
=IRR(B2:B4,0.18)
For this example, the two rates both make the discounted cash flows balance; the rate returned depends on the starting search and convergence. Do not assume that every pair of guesses will reliably isolate the two rates in every case. When multiple IRRs are possible, explain the cash-flow pattern and consider an NPV profile or another decision measure instead of presenting one IRR as uniquely definitive.
Common errors and misconceptions
#NUM! commonly means Excel did not converge within its iteration limit, but it can also arise when the cash flows do not support a real solution. Changing the guess is a reasonable diagnostic step, not a guarantee. Verify the signs, values, period spacing, and range before repeatedly trying arbitrary percentages.
IRR also needs at least one positive and one negative value. If every amount is positive or every amount is negative, there is no ordinary rate that balances the series. A blank, accidental text entry, or a misplaced cash flow can also change what Excel calculates, so review the actual cells rather than relying only on the visible result.
Another frequent misunderstanding is that a guess of 10% makes the reported IRR 10%. It does not: it only supplies a starting point. Likewise, formatting the result as a percentage changes how the number appears, not the cash-flow calculation. A displayed 8.9% is stored as approximately 0.089.
IRR, XIRR, and other measures
Use IRR when the cash flows are at regular intervals, such as annual or monthly periods. If actual dates are irregular—for example, a payment arrives after 10 months and another 15 months later—XIRR is designed to calculate an annualized return using the dates supplied. XIRR also has an optional guess, and its default is 10%. Use dates that correspond to each cash flow and keep both ranges aligned.
IRR is different from a simple average return: it accounts for the timing of cash flows. It is also different from MIRR, which applies separate assumptions for the rate at which positive interim cash flows are reinvested and the rate used to finance negative flows. MIRR may be useful when those assumptions are important, while IRR is the familiar rate that sets the cash-flow series’ NPV to zero.
These measures are analytical tools, not a complete basis for a financial decision. IRR can be hard to compare across projects with different sizes, durations, or cash-flow timing. The example figures here are illustrative, not financial, tax, investment, or legal advice; check actual terms with the lender or a qualified professional where relevant.
Frequently Asked Questions
What is the default guess for Excel IRR?
When you omit the optional guess argument, Excel starts at 10%, or 0.1.
Does the IRR guess change the final answer?
For a cash-flow series with one relevant solution, different reasonable guesses generally converge to the same answer. For a series with multiple IRRs, or one that is difficult to solve, the guess can affect the result Excel finds.
What guess should I use for IRR?
Start with the default unless you have a reason to use another estimate. If Excel returns #NUM!, check the cash flows and try plausible alternative guesses; do not assume a particular guess is always best.
Why does Excel IRR return #NUM!?
Excel may not have converged within 20 iterations, or the cash-flow sequence may not have a real solution. Check for at least one positive and one negative amount, correct signs and ordering, and a suitable guess.
Should I use IRR or XIRR?
Use IRR for equally spaced periods. Use XIRR when each cash flow has a date and the gaps between dates are not regular; its result is annualized using those dates.
Related
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
- Excel Not Blank Symbol: What <> Means
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
- Excel Formula to Split Cell Data After a Comma: ExamplesTo return everything after the first comma in cell A2 in Microsoft 365, use =TEXTAFTER(A2,","). To remove the…
- Excel Formula for the Greater of Two Values: MAX or IF?To return the greater of two numeric values in Excel, use =MAX(A2,B2), assuming the values are in cells…
- Excel MONTH Function Examples: Formulas and TipsExcel’s MONTH function extracts the month number from a date, returning an integer from 1 (January) to 12…
- Copy a Formula in Excel: Keyboard ShortcutsTo copy a formula in Excel on Windows, select its cell and press Ctrl+C, select the destination cell…
- Concatenate Excel Formula With Comma: ExamplesTo concatenate two cells with a comma and a space between them, use =A2&", "&B2. For example, if…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.