What you're solving for
IRR is the discount rate at which an investment's cash flows add up to zero in present-day money. Put another way, it's the return the investment earns on the money still tied up in it.
You need a list of cash flows in order, equally spaced, with money out as negative and money in as positive. We'll use one example throughout: pay $10,000 now, then receive $2,500, $3,000, $3,500 and $4,000 at the end of each of the next four years.
Formula notes
0 = -10,000 + 2,500/(1+r) + 3,000/(1+r)^2 + 3,500/(1+r)^3 + 4,000/(1+r)^4
Step 1: try a rate and compute NPV
You can't rearrange that equation to get r on its own, so you guess. Start at 10%. Divide each cash flow by 1.10 raised to its year and add them up. You get an NPV of $113.72.
NPV is positive, which means 10% is too low. The investment earns more than 10%, so try a higher rate.
Step 2: bracket the answer and interpolate
At 11% the NPV is -$118.79. Now you know the IRR sits between 10% and 11%, because NPV changes sign in that gap.
Draw a straight line between the two points. NPV falls by $232.51 across that 1-point gap, and you need it to fall by $113.72, so the IRR is about 10% + 113.72 / 232.51 x 1%, or 10.49%. The exact answer is 10.4845%. Linear interpolation lands close because the NPV curve is nearly straight over a small range.
The fast way: Excel or a calculator
In Excel or Google Sheets, put the cash flows in one column and use =IRR(A1:A5). It runs the same search for you. For cash flows on specific dates, use =XIRR with a column of dates, since plain IRR assumes every gap is the same length.
Our IRR calculator does the search too, and adds NPV at your hurdle rate, MIRR, payback and a warning when there's more than one IRR.
Three times IRR misleads you
IRR is popular because one percentage is easy to compare. It goes wrong in three common situations.
- Different sizes. A $1,000 project returning $1,500 has a 50% IRR. A $10,000 project returning $13,000 has a 30% IRR. At a 10% hurdle the bigger one adds $1,818.18 of value against $363.64, so the lower IRR is the better pick.
- Cash flows that flip sign more than once. -$100, +$230, -$132 has two IRRs, 10% and 20%. Neither one is the answer.
- Reinvestment. IRR assumes early cash earns the IRR again. If your real reinvestment rate is lower, MIRR gives a more honest figure. For the example, MIRR at 8% is 9.60% against the 10.48% IRR.
Common mistakes
- Mixing monthly and yearly rows in one list. Every row needs the same spacing.
- Forgetting the sign. If every cash flow is positive, there's no IRR to find.
- Multiplying a monthly IRR by 12. Compound it instead: (1 + monthly IRR)^12 - 1.
- Leaving out costs that come later, like repairs, fees or a final cleanup.
Frequently asked questions
Can you calculate IRR by hand?
Yes, by trial and error. Compute NPV at two rates, one giving a positive NPV and one negative, then interpolate between them. It gets you close, and repeating with a tighter range gets closer.
What is the IRR function in Excel?
=IRR(values, [guess]) returns the IRR for a range of equally spaced cash flows. The first value is usually negative. Use XIRR when the cash flows have specific dates.
Why does Excel's IRR return an error?
Excel returns #NUM! when the values don't include at least one negative and one positive number, or when it can't find a result after 20 tries. Check your signs, then try a different guess.
Is IRR the same as the interest rate on a loan?
For a simple loan it works out the same way. The lender's IRR on the loan's cash flows equals the periodic interest rate, compounded to an annual figure if you annualize it.
References
These sources support the method or guidance used for IRR Calculator. Verify time-sensitive rules at the source.
Try the calculator
Open IRR Calculator, enter your scenario, and compare its supporting rows with this guide's method and checks.