Excel IRR function: Calculate internal rate of return
Excel’s IRR function calculates the internal rate of return per period for evenly spaced cash flows. The internal rate of return is the discount rate that makes the net present value of the initial outflow and later cash flows equal to zero. For payments on irregular dates, use XIRR.
IRR syntax and cash flow requirements
=IRR(values, [guess])
The first argument is a range containing the cash flows. Enter the initial outlay as a negative number and receipts as positive numbers. Start with the initial cash flow and enter one value for each period in chronological order. Amounts may differ, but the range needs at least one positive and one negative number.
Enter a numeric 0 for a period with no cash flow. Excel skips blank cells, text, and logical values in the range, so leaving a cell blank removes that period from the calculation. Convert pasted amounts stored as text to numbers before using them.
The optional guess is the starting point for Excel’s iterative calculation. Excel starts at 10% if you omit it. See Microsoft’s IRR documentation for the argument rules.
Calculate IRR in an Excel worksheet
- In a blank worksheet, enter
-1000in A2,0in A3, and1210in A4. This represents an outlay of 1,000 today, no receipts in year one, and a receipt of 1,210 at the end of year two. - Type
=IRR(A2:A4)in B2 and press Enter. Select B2 and choose Home → Number → Percent Style to display the result as approximately 10%. - Clear only the
0from A3 and compare. Excel skips the blank cell and treats 1,210 as arriving after one period, giving 21%. Put the0back in A3 after the comparison.
To check the result, discount 1,210 for two periods at 10%. The expression 1210/(1+10%)^2 gives 1,000, exactly offsetting the initial outlay.
Troubleshoot #NUM! and check for multiple rates
If you see #NUM!, check that the range contains positive and negative numbers, includes the initial outlay, and excludes subtotals and the result cell. Excel also returns the error if it cannot find a solution in 20 iterations. With a comma as your argument separator, try another starting guess, such as =IRR(A2:A4,5%).
When the signs change more than once, there may be multiple solutions. For -100, 230, and -132, both 10% and 20% make net present value zero. If a different guess yields a different rate, inspect the sign changes and check net present value at both rates.
Interpret monthly IRR and choose between IRR and XIRR
With annual cash flows, each period is one year and IRR is annual. With monthly cash flows, IRR is monthly. To convert a monthly rate of 1% to an effective annual rate assuming 12 months of compounding at the same rate, enter =(1+1%)^12-1. The result is about 12.68%.
Use IRR when monthly or annual periods describe the cash flows. If actual payment dates are unevenly spaced and elapsed days matter, use Microsoft’s XIRR function with an amount range and a date range. Enter each date on the same row as its amount and make sure Excel recognizes it as a date.
