Yes. In Excel for Microsoft 365 on Windows, use the IRR function to estimate the return for a series of cash flows that occur at regular intervals. With an initial investment in B2 and three annual cash flows in B3:B5, enter =IRR(B2:B5); for the example below, Excel returns approximately 8.9% per year.
What IRR measures
IRR stands for internal rate of return. It is the discount rate at which the net present value of a set of cash flows equals zero. Put another way, it is the rate that makes the present value of the money invested equal the present value of the money received, assuming the cash-flow timing and amounts in the worksheet are correct.
IRR is usually expressed as a percentage per period, not automatically as a yearly return. If each row represents a year, the result is an annual rate. If each row represents a month, the result is a monthly rate. Excel does not infer the interval from dates; the ordinary IRR function treats the values as equally spaced periods.
The result is a model output, not a guaranteed or realized return. It depends on the cash flows entered and assumes that interim receipts can be reinvested at the calculated IRR, an assumption that may not match reality. IRR is useful for comparing a project with a target rate, but it does not by itself show the size of the investment, the timing details, or the total cash earned.
Set up the cash-flow column
Put each cash flow in a single column, ordered from earliest to latest. Include the initial investment as a negative amount because it is money paid out. Enter later receipts as positive amounts. For example, a project that costs $10,000 now and returns $3,000, $4,000, and $5,000 at the ends of the next three years could use this layout:
- B2: -10000, the investment at the start
- B3: 3000, the receipt after year one
- B4: 4000, the receipt after year two
- B5: 5000, the receipt after year three
Use actual numeric values, not text such as “$3,000 received.” Format the cells as Currency or Accounting if you want the amounts to display with currency symbols. The formatting does not change the values used in the calculation. Check that all listed amounts use the same currency and that there are no missing periods hidden by blank cells.
Enter the IRR formula
With the initial investment in B2 and the three subsequent annual cash flows in B3:B5, enter =IRR(B2:B5) in another cell. Excel returns approximately 8.9%. Format the result cell as Percentage, for example with one or two decimal places. The result means that, for this evenly spaced annual series, the rate that makes the cash flows’ net present value zero is about 8.9% per year.
The syntax is IRR(values, [guess]). The values argument is the range or array containing the cash flows in time order. The optional guess is an initial estimate that helps Excel search for a solution; if you omit it, Excel uses 10%. A guess is not a target return and does not alter the cash flows. With the same values in B2:B5, =IRR(B2:B5,10%) also returns approximately 8.9% for this example.
Keep the initial cash flow in the range. Leaving it out, reversing the signs, or selecting a heading cell instead of a number can produce an error or a misleading result. You can place the formula in any cell outside the input range, such as D2, and label it “Annual IRR” to make the period clear.
How to interpret the result
Compare IRR with a relevant required return, hurdle rate, or alternative project, while keeping the assumptions consistent. If a project has an IRR above a chosen hurdle rate, that may indicate it meets the rate criterion under the modeled cash flows. It is not enough to conclude that the project is preferable: a smaller project can have a higher IRR while producing less total value, and the timing or risk of cash flows may differ.
For the example, the 8.9% figure is an annual rate because each cash flow is one year apart. It is not an 8.9% gain on the original investment every year in a simple, guaranteed sense. The receipts vary, and the IRR summarizes the series as a single rate. If cash flows are monthly, do not label the raw IRR as an annual rate. A common annualization calculation for a monthly IRR is to compound it for 12 periods, but that is a separate calculation and should reflect the intended convention.
IRR can also be ambiguous when cash flows change sign more than once. A project might require an investment, generate receipts, and later require a large cleanup or replacement outlay. Such a series can have more than one rate that makes NPV zero, or none that Excel can find. In those cases, one returned result should not be treated as the only possible interpretation.
Use XIRR when dates are irregular
If payments do not arrive at equal intervals, use XIRR rather than treating every row as a full month or year. XIRR uses cash-flow dates and returns an annualized rate based on the actual spacing between them. Put real Excel dates in one column and corresponding cash flows in another; keep the two ranges the same length and aligned row by row.
For example, with dates 1/1/2025, 1/1/2026, 1/1/2027, and 1/1/2028 in A2:A5, and the example cash flows -10000, 3000, 4000, and 5000 in B2:B5, enter =XIRR(B2:B5,A2:A5). Because these dates are exactly 365 days apart, the result is approximately 8.9%, matching the annual IRR example. With different dates, XIRR can differ because it accounts for the actual day gaps. Make sure dates are stored as Excel dates, not text that merely looks like a date.
IRR and NPV are related, but different
IRR answers the question “What rate makes this cash-flow series break even in present-value terms?” NPV answers “What is the value of these cash flows today at a rate I specify?” NPV is often more useful for measuring the amount of value created at a chosen discount rate; IRR gives a rate that can be easy to compare with a benchmark.
Excel’s NPV function discounts the values supplied as future, equally spaced cash flows. It does not include a time-zero cash flow in its range, so add the initial investment separately. With the initial investment in B2, annual receipts in B3:B5, and a 10% annual discount rate, enter =NPV(10%,B3:B5)+B2. The example returns approximately -$210.37: the future receipts are worth about $9,789.63 at 10%, which is less than the $10,000 initial outlay. That negative NPV is consistent with an IRR below 10%.
Do not compare an IRR with an NPV as if both were percentages. They answer different questions and have different units. For uneven dates, use XIRR and XNPV as the date-aware alternatives, rather than forcing irregular cash flows into equal periods.
Common errors and misleading results
- #NUM!: Excel could not find a solution using its search process and the supplied guess. The cash flows may have no suitable rate, may contain multiple possible rates, or may need a different guess.
- #VALUE!: A value in the selected range may be text or another nonnumeric entry. Check for labels, numbers stored as text, and error values.
- Wrong sign convention: IRR generally needs at least one negative and one positive cash flow. If every value is positive or every value is negative, there is no investment-and-return pattern for the function to solve.
- Wrong period: IRR assumes equal spacing. Monthly cash flows entered as if they were annual produce a monthly rate, not an annual one.
- Wrong range: Selecting only later receipts omits the original investment. Including a total or heading can also distort the result.
- Multiple sign changes: When cash flows switch between negative and positive more than once, multiple IRRs may exist. Try different guesses as a diagnostic, but do not assume that one answer resolves the ambiguity.
Inspect the underlying cash-flow schedule before changing the formula. A different guess can help Excel find a root, but it cannot repair incorrect timing, missing cash flows, or a mistaken sign. For a project with complex flows, compare NPV at a clearly stated rate and consider presenting the cash-flow schedule alongside the rate.
A practical checking routine
Before relying on an IRR figure, confirm that the first amount represents the investment at time zero, that positive values are receipts and negative values are outlays, and that each row corresponds to the same length of time. Then check the formula range and inspect the result’s number format. A cell formatted as Percentage makes a rate readable; it does not change the underlying answer.
For an intuitive check, calculate NPV at a rate a little above and below the reported IRR. In a simple series with one initial outflow followed by inflows, NPV should generally be close to zero at IRR, positive at a lower discount rate, and negative at a higher one. Small differences can result from rounding the displayed rate. If the pattern does not make sense, recheck signs, timing, and selected cells instead of simply accepting the displayed percentage.
Frequently Asked Questions
Is IRR in Excel annual?
Only if the cash flows are spaced one year apart. IRR reports a rate per period represented by the entries. Monthly, quarterly, and other regular intervals produce rates for those periods.
Why does Excel IRR return #NUM!?
Excel may not find a solution from its starting guess, or the cash-flow pattern may have no single usable IRR. Check that the range includes both an outflow and an inflow, then verify signs and timing. Trying another guess can help diagnose a search issue, but multiple roots require careful interpretation.
Should the initial investment be negative?
Yes, when it represents money paid out at the start. Use negative values for outflows and positive values for receipts so the signs reflect the direction of the cash.
What is the difference between IRR and XIRR?
IRR assumes cash flows occur at regular intervals. XIRR uses a separate range of dates, so it is appropriate when the time between payments varies, and it returns an annualized rate.
Can IRR be negative?
Yes. A negative result can occur when the cash flows imply a loss under the modeled pattern. Review the amounts and timing, and compare NPV at a relevant discount rate before drawing a conclusion.
Related
- Unhide Rows in Excel: Shortcut Keys and Other Methods
- Unhide Columns in Excel Using the Keyboard
- Excel Unhide Shortcut: Reveal Hidden Rows and Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Smartsheet SUMIF Examples for Everyday ReportsIn Smartsheet, SUMIF adds values in a sum range only when corresponding cells meet one condition. For example,…
- Excel FILTER Formula to Find Cells Containing TextTo return rows where a column contains particular text, combine Excel’s FILTER function with SEARCH: with records in…
- How to Get the Filter Option in Excel and Use It EasilyTo get the Filter option in Excel, click a cell in your data and select Data > Filter.…
- Excel Power Query Table Filter: How to Apply and RefreshTo filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the…
- How to Filter by Color in ExcelTo filter by color in Excel for Microsoft 365 on Windows, turn on filters for your data, open…
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.