To calculate IRR in Excel, put each periodic cash flow in one cell, including the initial investment as a negative number, then use =IRR(B2:B5). For example, cash flows of -$10,000, $3,000, $4,000, and $5,000 produce an IRR of about 8.90% per period.
What IRR means
IRR stands for internal rate of return. It is the rate that makes the net present value (NPV) of a series of cash flows equal zero. In practical terms, it is the discount rate at which the present value of the money received balances the present value of the money paid out.
IRR is a percentage, but it is tied to the interval between the cash flows you enter. If the entries represent months, the result is a monthly rate; if they represent years, it is an annual rate. Excel’s IRR function does not inspect dates or infer a time unit. It treats the first value as occurring at the start of the investment and each following value as one equal period later.
This makes IRR useful for summarizing a project, property, or other cash-flow forecast as a rate. It is not the same as a guaranteed return, and it does not by itself show the amount of money earned. A small investment and a large investment can have the same IRR while producing very different dollar gains.
Prepare the cash-flow column
Use one cell per cash flow and keep the entries in chronological order. Payments or investments made by you are normally negative; money received by you is positive. Include the initial outlay as the first value, even if it occurs at time zero. Do not include dates in the IRR range.
For the example, enter the following values in B2:B5:
- B2: -10000, the initial investment.
- B3: 3000, the cash flow after one period.
- B4: 4000, the cash flow after two periods.
- B5: 5000, the cash flow after three periods.
Those numbers assume the cash flows arrive at equal intervals, such as once per year. Format the values as currency if that makes the worksheet easier to read; number formatting does not change the calculation. Check that the range contains at least one negative and one positive amount. Without both signs, a conventional IRR cannot be found.
Calculate IRR with the Excel function
In Microsoft Excel for Microsoft 365 on Windows, select an empty cell and enter the function below. With the example cash flows in B2:B5, enter this in B7:
=IRR(B2:B5)
Excel returns approximately 8.90%. Format B7 as a percentage, for example with Home > Number, or use the percentage button on the Home tab. The displayed precision depends on the cell’s number format; showing more decimal places changes only what you see, not the underlying result.
The function’s structure is IRR(values, [guess]). The values argument is the range or list of cash flows. The optional guess is an initial estimate that helps Excel search for a solution. If omitted, Excel uses 10%. For example, with the same cash flows in B2:B5, this formula in B7 asks Excel to start near 8%:
=IRR(B2:B5,0.08)
It returns the same approximate 8.90% for this example. A guess is not a hurdle rate or a promise about the answer; it is a starting point for the calculation. Microsoft 365 for Mac and Excel for the web also support IRR. The exact ribbon layout can differ, but the formula syntax is the same.
How Excel finds the result
Excel searches for a rate at which discounting each future cash flow back to the start makes the total equal to the initial outflow. At a rate below the example’s IRR, the discounted receipts are worth more than $10,000; at a rate above it, they are worth less. The IRR is the rate at the crossover, subject to the cash-flow pattern and Excel’s numerical calculation.
This is an iterative calculation rather than a simple average of the cash-flow amounts. The timing and size of every entry matter. For instance, receiving $5,000 sooner generally has a different effect from receiving the same amount later, even if the total receipts are unchanged. IRR also should not be read as “the investment grows by this exact percentage every year” when the cash flows are uneven or intermediate proceeds are not reinvested at that rate.
Use XIRR when cash-flow dates are uneven
IRR assumes evenly spaced periods. If payments occur on irregular dates, use Excel’s XIRR function instead. Put the cash flows in one range and their corresponding Excel dates in a second range, with the initial outlay and its date first. XIRR calculates a rate based on the actual dates, using a 365-day year.
For a comparable example, place -10000, 3000, 4000, and 5000 in B2:B5, and enter January 1, 2021; January 1, 2022; January 1, 2023; and January 1, 2024 in A2:A5, respectively. With dates in A2:A5 and matching cash flows in B2:B5, enter in B7:
=XIRR(B2:B5,A2:A5)
The result is approximately 8.90% per year for these dates and cash flows. The result is close to the IRR example because the dates are evenly spaced at one-year intervals. With irregular dates, XIRR can differ. Make sure every cash flow has a matching valid date and that the date and value ranges have the same number of cells. XIRR is available in Microsoft 365 for Windows, Mac, and the web.
Interpret the percentage carefully
Match the reported rate to the period represented in the data. If each cash flow is a year apart, an IRR result is annual. If the entries are monthly, the result is monthly; multiplying a monthly IRR by 12 gives a simple annualized figure, not necessarily the same as a compounded annual rate. For annualized calculations with actual uneven dates, XIRR is generally the more appropriate Excel function.
IRR is a rate of return, not a comparison of project scale, risk, or total dollars. It can be useful alongside NPV, which expresses a result in money at a chosen discount rate, and alongside the underlying cash-flow forecast. The value of an IRR estimate depends on the accuracy of its cash-flow assumptions. For a real lending, investment, tax, or business decision, check the actual terms and consult a qualified professional as appropriate; this spreadsheet example is illustrative, not financial advice.
Common errors and how to fix them
A #NUM! result can mean Excel did not find a solution after iterating, or that the cash flows do not have a suitable IRR. Confirm the signs, check for missing or mistyped amounts, and verify that the range represents the intended periods. You can also try an appropriate guess. With multiple sign changes, a different guess may lead Excel toward a different possible root; that is a feature of the mathematics, not necessarily a worksheet error.
#VALUE!: Check for text or invalid entries inside the values range. A label such as “Investment” should be outside the selected range.- An unexpected percentage: Confirm that outflows are negative, inflows are positive, and values are in time order. Check whether the entries are monthly, quarterly, or annual.
- IRR seems unlike a quoted rate: Make sure you are comparing the same period and method. A periodic IRR and an annualized XIRR are not interchangeable when timing differs.
- More than one sign change: A pattern such as negative, positive, negative can have multiple rates that make NPV zero, or no useful single rate for interpretation. Review the cash flows and consider NPV or a different measure.
A guess can help Excel converge, but it cannot repair incorrect data or make an ambiguous result meaningful. If the project has several sign reversals, report the cash-flow pattern and consider an additional metric instead of relying on one IRR figure.
IRR, XIRR, and MIRR compared
Choose the function that matches the schedule and the question. IRR is for equally spaced cash flows and returns a rate per interval. XIRR is for cash flows on specific, possibly uneven dates and returns an annualized rate based on actual dates. Both depend on the cash flows entered and can be misleading when the pattern produces multiple possible rates.
Excel also has MIRR, which lets you specify financing and reinvestment rates for a cash-flow series. It can be useful when the standard IRR’s implied reinvestment assumption is not suitable for the analysis. MIRR requires both a finance rate and a reinvestment rate, so those assumptions should be explicit and justified. None of these functions replaces checking the forecast, the timing, or the decision criteria.
Frequently Asked Questions
What is the formula for IRR in Excel?
Use =IRR(values), where values is the range containing the cash flows in chronological order. Include the initial investment as a negative value and later receipts as positive values. Add an optional guess after the range if needed.
Why does Excel IRR return #NUM!?
Excel may have failed to converge, the range may not contain both positive and negative values, or the cash-flow pattern may not have a single useful solution. Check the data and period spacing, then try a suitable guess. Multiple changes between positive and negative can create multiple possible IRRs.
Should the initial investment be negative?
Usually, yes, when it is money paid out by the investor or project owner. IRR needs the cash-flow direction represented consistently: payments out are negative and receipts in are positive. Reversing every sign does not change the mathematical rate, but mixing signs incorrectly can produce an invalid or misleading result.
Is IRR the same as annual return?
Only when the input periods are annual is the IRR result directly an annual periodic rate. Monthly cash flows produce a monthly IRR. For cash flows on uneven dates, use XIRR for an annualized, date-based calculation.
When should I use XIRR instead of IRR?
Use XIRR when cash flows do not happen at regular intervals or when the actual dates matter. Use IRR for equally spaced periods, such as one cash flow at the end of each year. If you have real dates, make sure they are valid Excel dates and aligned with the cash-flow values.
Related
- Attendance Register Template in Excel: Setup and Tips
- Attendance Roster Template in Excel: Setup and Tips
- Attendance Point Tracking Excel Template?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Formula for Dates: Examples and Common FixesUse Excel’s SUMIF with a date as the criterion to add values for a specific date; use comparison…
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH…
- Excel FILTER and UNIQUE: Remove Duplicate Rows EasilyExcel’s FILTER function does not remove duplicates by itself; combine it with UNIQUE to return distinct rows that…
- Power Query If Function: Syntax, Examples, and TipsPower Query’s if is a conditional expression, not a function: write if condition then value_if_true else value_if_false. For…
- Excel Date Minus 1 Year: Formula and Leap-Year RulesTo subtract one calendar year from a date in Excel, use =EDATE(A2,-12), assuming the original date is in…
- Clear Sort A to Z in Excel: Restore Your Data OrderTo undo an A-to-Z sort in Excel, press Ctrl+Z right away (Command+Z on Mac). If you have already…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.