IRR Function in Excel: Calculate an Investment Return

Use Excel’s IRR function to estimate the rate of return for a series of cash flows that occur at regular intervals. Enter the cash flows in chronological order, including the initial outlay as a negative amount and later receipts as positive amounts, then calculate the result with =IRR(A2:A5). You will need a worksheet containing at least one negative and one positive cash flow; the worked example below shows how to build and check it.

IRR stands for internal rate of return. It is the rate that makes the net present value of the listed cash flows equal zero, assuming the periods between entries are equal. Excel calculates an estimate using an iterative method, so the result can depend on the cash-flow pattern and, in some cases, the starting guess.

What You’ll Need

Excel for Microsoft 365 on Windows, or another recent Excel version with the IRR function.
A sequence of cash flows in time order, with the initial investment or payment entered as a negative number.
Consistent time intervals, such as yearly or monthly periods. Include a zero for a period with no cash flow so the spacing stays accurate.
A clear understanding of what one period means. IRR returns a rate per cash-flow interval, not automatically a yearly rate.

The cash-flow example in this guide is illustrative only. It is not financial or investment advice; check actual terms with a lender or a qualified professional before making a decision.

Step-by-Step

Plan the periods. Decide whether each row represents a year, quarter, month, or another equal interval. IRR assumes the time from one value to the next is the same throughout the series. If your actual cash flows happen on irregular dates, use Excel’s XIRR function instead of treating the entries as evenly spaced.
Enter the cash flows in chronological order. For a simple project, the first amount is often the money paid at the start and is entered as a negative value. Money received later is usually entered as positive values. For this example, enter -10000 in A2, 3000 in A3, 4200 in A4, and 6800 in A5. These represent an initial outlay followed by three yearly receipts.
Keep one cash flow per period. If there is no payment or receipt in a period, enter 0 rather than leaving out the period. For instance, if the project has no cash flow in year two, preserve a row for year two with a zero. Omitting it would make Excel treat the year-three cash flow as if it arrived one period earlier and would change the answer.
Select an empty cell for the result. Click a blank cell outside the cash-flow list, such as A7. With the example values in A2:A5, enter =IRR(A2:A5) and press Enter. The range contains the initial outflow and all three later receipts; do not include a heading such as “Cash flow” in the selected range.
Read the result as a rate per period. For the example cash flows in A2:A5, Excel returns approximately 16.34%. Because the example uses annual periods, this is an annual rate. If the same row pattern represented months, the result would be a monthly rate, not an annual one. Make the period explicit in your worksheet so another reader does not mistake the unit.
Format the result as a percentage. Select the cell containing IRR, then choose Home > Number > Percent Style. Increase or decrease the displayed decimal places with the decimal-place controls in the Number group. Formatting changes how the value appears, not the underlying calculation. A displayed 16.34% is stored as a decimal rate of approximately 0.1634.
Check that the answer is plausible. Confirm the signs, order, amounts, and periods. For the example, an IRR near 16.34% means that discounting the three yearly receipts at approximately that rate makes their present value about equal to the initial 10,000 outlay. It does not mean the project pays exactly 16.34% in cash every year.
Try a guess only if Excel needs help converging. IRR uses 10% as its default guess. If Excel returns an error or a result that seems to be a different possible solution, you can provide a starting guess. With the example cash flows still in A2:A5, enter =IRR(A2:A5,10%). The guess helps Excel search for a solution; it is not a target rate or a value that Excel simply returns.

In Microsoft 365 for Windows, the formula and ribbon steps above apply directly. The IRR function is also available in Excel for Mac and Excel for the web. The ribbon layout can vary slightly by platform, but you can enter the same formula in a worksheet cell. In installations that use a different regional list separator, Excel may require a semicolon rather than a comma between function arguments; follow the separator Excel uses in your locale.

Common Mistakes

Leaving out the initial outlay or entering it as positive. The initial investment is normally a cash outflow, so enter it as a negative number. IRR needs at least one negative and one positive value. If every value has the same sign, there is no rate that balances the series in the intended way.
Using unevenly spaced cash flows with IRR. IRR treats each adjacent value as one equal period apart. If one amount arrives after two months and another after a year, putting them in consecutive rows does not tell IRR about those different dates. Use XIRR with actual dates for irregular timing.
Skipping a zero period. Omitting a row for a quiet month or year compresses the timeline. A zero is still a period and should remain in the sequence when periods are regular.
Misreading the rate’s time basis. The formula does not inspect date labels or convert monthly periods into an annual figure. If entries are monthly, IRR is monthly. Do not label it “annual return” unless the intervals are annual or you have correctly converted the result.
Assuming there is always one meaningful answer. Cash-flow patterns with multiple changes between negative and positive can have more than one IRR, or Excel may fail to find a solution from its starting guess. In those situations, inspect the cash flows and use other measures, such as net present value at a clearly stated discount rate, as appropriate.
Comparing projects using IRR alone. IRR is a percentage and does not show the size of a project’s cash flows. A small project can have a higher IRR than a much larger one while producing less total value. Consider scale, timing, risk, and assumptions alongside the calculated rate.

Tips

Add a period label beside each amount, such as Year 0, Year 1, and Year 2. Labels help you spot missing intervals, even though the IRR formula uses the values rather than the labels.
Keep cash-flow values as numbers, not text that merely looks like numbers. If you imported data, check for leading apostrophes, spaces, or text symbols that may prevent Excel from interpreting values as intended.
Use the optional guess to explore a result when cash flows have several sign changes. You can try reasonable starting rates, but treat different returned rates as a signal to investigate rather than selecting whichever one looks best.
Document the assumptions next to the result: the currency, period length, whether amounts are before or after fees, and which cash flows are included. These notes make the percentage easier to interpret later.
Remember that IRR is a mathematical summary, not a forecast or guarantee. It does not account for changes in assumptions unless you change the cash-flow values, and it should not replace a review of the underlying terms.

Troubleshooting

Excel displays #NUM!. First confirm that the range has at least one negative and one positive cash flow and that the selected cells are the intended values. If the cash-flow pattern is unusual, provide a sensible guess with the optional second argument. If different guesses produce different results, the series may have multiple possible IRRs; review the sign changes and consider whether IRR is suitable for that series.

Excel displays #VALUE! or the formula is not accepted. Check for text, invalid cell references, or a malformed formula. The values argument must refer to a range or array of cash flows. Make sure parentheses are balanced and that your Excel locale uses the separator you typed. In English-language Excel, the usual argument separator is a comma.

The result is much higher or lower than expected. Check that the first cash flow is negative, that no amount was entered in the wrong period, and that the range has not accidentally included a heading or omitted a value. Verify that zeros preserve empty periods. Also check whether you are reading a monthly rate as an annual rate, or the reverse.

The formula returns a rate, but it does not match a spreadsheet or calculator. Confirm that both tools use the same cash flows, interval length, and sign convention. Check whether one calculation uses actual dates and therefore XIRR, while the other assumes equal periods with IRR. Small differences can also result from rounding the cash-flow inputs or displaying too few decimal places.

Nothing changes after you edit an input. Check that calculation is not set to manual under Formulas > Calculation Options. If it is set to Manual, choose Automatic or recalculate the workbook. Also make sure the result cell contains a formula rather than a value pasted over the formula.

Frequently Asked Questions

What does IRR mean in Excel?
IRR means internal rate of return. It is the rate that makes the net present value of a sequence of regularly spaced cash flows equal zero.

Does IRR return a monthly or yearly percentage?
It returns a rate for each interval between cash-flow entries. Annual entries produce an annual rate; monthly entries produce a monthly rate. The function does not infer a calendar period from dates or labels.

Why does Excel’s IRR function need positive and negative numbers?
The function searches for a rate that balances cash paid out against cash received. A conventional project therefore needs at least one outflow and one inflow. If your data has only positive values or only negative values, check whether the sign convention or range is wrong.

What is the difference between IRR and XIRR?
IRR assumes equal intervals between cash flows. XIRR uses a separate range of actual dates, making it appropriate when receipts or payments occur at irregular intervals. Use the function that matches the timing in your data.

Can IRR give more than one answer?
Yes. A series that switches between negative and positive more than once can have multiple mathematical solutions. Excel’s result may depend on its guess, so examine the cash-flow pattern and do not assume the first result is the only relevant one.

Is IRR enough to decide whether an investment is worthwhile?
No. IRR summarizes a cash-flow pattern as a rate, but it does not by itself show project size, risk, or whether the assumptions are realistic. Use it as one measure and check the real terms with a lender or qualified professional.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.