To calculate IRR in Excel, enter the cash flows in chronological order and apply =IRR(range). Enter the initial outflow as a negative number and receipts as positive numbers. If a real period has no payment or receipt, enter zero instead of leaving the cell blank.
In the example below, =IRR(B2:B4) has a solution of 10% per period. The result was checked with Calculaderia's actual function and an independent mathematical identity. No Excel run of this example was recorded.
Copyable example
| Row | Period in column A | Cash flow in column B |
|---|---|---|
| 2 | 0 | -1000 |
| 3 | 1 | 0 |
| 4 | 2 | 1210 |
IRR function step by step
- Enter periods 0, 1, and 2 in A2:A4 so the sequence remains visible.
- Enter -1000, 0, and 1210 in B2:B4. Use numbers without adding text inside the cells.
- In another cell, enter =IRR(B2:B4) and confirm the formula.
- Format the result cell as a percentage. With two decimal places, it should display 10.00%.
- Identify the time unit: the rate is monthly if each row represents a month and annual if each row represents a year.
The function accepts an optional guess, such as =IRR(B2:B4,10%). It provides a starting point for the numerical search and can help with more difficult cash flows. This example does not require it.
Why are zero and a blank cell different?
Excel includes zero in the IRR range but ignores empty cells and text in a referenced range. In this cash flow, removing the zero would move 1210 from period 2 to period 1. The problem would change from -1000 + 1210 divided by (1 + r) squared to -1000 + 1210 divided by (1 + r), producing a different rate.
Use zero when a real period passed with no cash flow. Leave a cell blank only when it does not belong to the cash flow sequence.
How to check IRR with NPV
IRR is the rate that makes the cash flow's NPV equal to zero. Excel's NPV function discounts the values in its range starting with the first future period. The period-0 cash flow therefore stays outside NPV and is added separately: =NPV(10%,B3:B4)+B2.
For this example, the independent calculation is -1000 + 0 divided by 1.1 + 1210 divided by (1.1 squared) = 0. Calculaderia's function returned 9.999996% because of its numerical-search tolerance: less than 0.00001 percentage point away from 10%, with an NPV residual below 0.001 monetary unit.
Is IRR monthly or annual?
The IRR function does not infer a time unit from the cells. It returns a rate for each interval in the cash flow:
- If the rows represent years, 10% is an annual rate.
- If the rows represent months, 10% is a monthly rate.
- A monthly rate of 10% is equivalent to approximately 213.84% per year using compounding: (1 + 0.10) raised to the 12th power, minus 1. Multiplying the monthly rate by 12 does not produce the equivalent annual rate.
Compare IRR with another rate only after expressing both in the same time unit. To understand the concept, interpretation, and limitations before using a spreadsheet, read the guide to what IRR means.
When to use XIRR
IRR assumes equal intervals between rows. If one receipt occurred on January 15 and another on March 28, use the actual dates and the XIRR function. Its syntax is =XIRR(values,dates), and it returns an annual rate calculated on a 365-day basis.
Keep the two interpretations separate: IRR returns a rate per regular interval, while XIRR uses the actual distance between dates and returns an annual rate. See Microsoft's XIRR function documentation for its argument requirements.
Errors and unexpected results
- The function can return #NUM! when its numerical search does not converge. Check that the range has at least one negative and one positive cash flow, that the order is correct, and that the cells contain numbers.
- Excel can ignore a number stored as text. Convert it to a numeric value before calculating.
- A blank cell can remove a period. Enter zero when the time interval exists but has no cash flow.
- Cash flows that change sign several times can have more than one solution. A different guess can lead to another root; examine NPV and the cash flow instead of selecting the highest rate.
- Irregular dates call for XIRR. Adding artificial rows to simulate days can make the spreadsheet difficult to audit.
Excel performs the numerical search, but it does not decide whether the cash flow reflects realistic risk, liquidity, or assumptions. IRR describes the values entered; it is not investment advice.