Finance & Investment

Calculate IRR in Excel with a Checked Cash-Flow Example

Use Excel IRR for equally spaced cash flows, verify the result with NPV, and distinguish monthly returns, irregular dates and multiple solutions.

By Uddhav Gopani, FounderPublished 25 September 2026Reviewed 29 September 2026

Calculate IRR in Excel with a Checked Cash-Flow Example

Lay out the cash flows in time order

Excel's IRR function finds a discount rate that makes a sequence of cash flows have zero net present value. Put the initial investment in the first cell as a negative amount and later receipts as positive amounts. Each row must represent one equal time interval. For a simple example, enter -1000, 600 and 600 in cells A1:A3. The first amount occurs at time zero, followed by receipts after one and two periods. A positive result does not identify whether those periods are months or years; your schedule does.

Enter the formula and inspect its meaning

Use =IRR(A1:A3) and format the result as a percentage. The example returns about 13.0662% per period. The mathematical check is -1000 + 600/(1+r) + 600/(1+r)^2 = 0. Substituting approximately 0.130662 gives a residual close to zero; extra displayed digits improve the residual without changing the financial interpretation. The result is not total profit divided by the investment. Total undiscounted profit here is 200, or 20%, but the timing of two receipts makes the annualized or periodic measure different.

Check NPV without shifting the first payment

For this arrangement, verify with =NPV(IRR(A1:A3),A2:A3)+A1. Excel NPV discounts the first value supplied to it by one period, so the time-zero investment is added separately. Including A1 inside that NPV range discounts the initial investment incorrectly. A near-zero result is evidence that the returned rate solves the entered schedule. It does not prove that the cash-flow estimates are complete. Include costs at their actual modeled times and identify whether the amounts are before or after tax when comparing projects.

Keep zero periods and distinguish blanks

If no money moves in period one, enter 0 for that period. Deleting its row moves every later cash flow earlier and changes IRR. Blank cells or text in a referenced range can be ignored by Excel, so they are not reliable substitutes for explicit zero cash flows. At least one positive and one negative amount are needed for the usual investment problem. A schedule with only outflows cannot be repaired by changing percentage formatting. Check the signs and period positions before changing the optional numerical guess.

Annualize a monthly result correctly

For monthly cash flows, the result is a monthly rate. Convert it to an effective annual rate using (1+r_month)^12-1. For example, a monthly IRR of 1% corresponds to about 12.6825% effective annually, not exactly 12%. Multiplying by twelve produces a nominal annualized figure, which is a different convention. Do not compare a monthly IRR directly with an annual loan rate or an annual project hurdle. Label the period and annualization convention beside the number so another reader can reproduce the comparison.

Use XIRR when dates are irregular

IRR assumes equal spacing. If payments occur on dates such as 5 January, 21 March and 2 December, record those dates and use Excel XIRR with matching values and dates. Rounding the dates into arbitrary monthly rows changes the investment being measured. The linked CalcToolsBase IRR calculator accepts equal-period cash flows, while the separate XIRR calculator serves dated flows. This article's example therefore belongs in the IRR tool as -1000,600,600. It should return the same periodic root within the displayed rounding tolerance.

Investigate errors and multiple roots

IRR uses a numerical search, so some schedules return #NUM! or depend on the initial guess. Cash flows with repeated sign changes may have multiple mathematically valid IRRs. For -100,230,-132, both 10% and 20% make NPV zero. Choosing one without explanation hides that ambiguity. Plot or inspect NPV at several discount rates and consider NPV at a stated hurdle rate when selecting between projects. A successful solver run is only one part of interpreting the cash-flow pattern; it is not an assurance of future performance.

Use the IRR Calculator

Open IRR Calculator

Source and scope

Microsoft IRR function documentation