The IRR function
Returns the internal rate of return of a series of cash flows, the discount rate at which their net present value is 0.
How to use IRR
IRR answers one question: what return per period does this investment earn? You list the cash flows in order, and IRR finds the interest rate at which the money you put in and the money you get back cancel out to exactly zero. Think of it as the rate a savings account would have to pay to keep up with your project.
Use it to compare investments: a machine that costs $50,000 and saves money every year, a rental property, or a campaign that pays back over several months. The money you spend comes first as a negative number, and the money you receive comes after it as positive numbers. With the investment in B1 and three payments below it, you write =IRR(B1:B4). The order is the timeline, so every period needs its own cell, even one with 0.
IRR needs at least one negative and one positive value, or there is nothing to balance. The optional guess only helps when the cash flows are unusual and IRR cannot settle on an answer. If you want an amount of money at a rate you choose, use NPV. For the payment on a loan, use PMT.
Syntax
IRR(values, [guess])- values
- The cash flows in order, one per period, with at least one negative (the investment) and one positive.
- guess (optional)
- A starting guess for the rate. Leave it out for 10%.
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Year 0 | -1000 | 0.09701 | |
| 2 | Year 1 | 400 | ||
| 3 | Year 2 | 400 | ||
| 4 | Year 3 | 400 |
| A | B | |
|---|---|---|
| 1 | 0.1 |
Common IRR mistakes
IRR returns #NUM!
IRR needs at least one negative and one positive value. If the investment is typed as a positive number, write it with a minus, like -1000. If the signs are right and you still get #NUM!, give IRR a guess close to what you expect, as in
=IRR(B1:B8,0.05).IRR shows 0.097 instead of 9.7%
IRR returns the rate as a decimal. Format the cell as a percentage and it reads 9.7%.
The rate looks far too small for monthly cash flows
IRR gives the rate per period, so monthly values give a monthly rate. Turn it into a yearly rate with
=(1+IRR(B1:B13))^12-1.The result is wrong because a month with no payment is missing
IRR skips empty cells and text, so later payments slide one period earlier. Type 0 in every period without a cash flow.
Practice IRR
Questions
What is the difference between IRR and NPV?
NPV gives an amount at a discount rate you choose. IRR gives the rate at which that amount is exactly 0. A project is worth doing at your required rate if its IRR is higher than that rate.
What is a good IRR?
It depends on the return you require. Compare the IRR with the interest on a loan for the investment or with what the money would earn elsewhere. If the IRR is higher, the investment can pay off.
Can IRR have more than one answer?
Yes. If the cash flows change sign more than once, such as minus, plus, minus, several rates can fit. IRR returns the one closest to the guess. Try another guess and check the result with NPV.
What if the payments are not evenly spaced in time?
IRR assumes equal periods. For payments on irregular dates, use XIRR, which takes the dates as a second range.