IRR
Returns the internal rate of return for a series of cash flows. Used to evaluate the profitability of an investment.
IRR(values, [guess])Arguments
valuesRange of cash flows (must contain at least one positive and one negative value)guessInitial guess for the rate (default 0.1 = 10%)(optional)
=IRR({-10000, 3000, 4000, 5000})Rate of return on $10K investment with 3 years of income
=IRR({-50000, 15000, 15000, 15000, 15000})Return rate on a 4-year project
- •Higher IRR = better investment
- •Compare IRR to your cost of capital to decide if a project is worth it
- •Values must start with a negative number (initial investment)
- •Forgetting that the cash flow series must include at least one negative value (the initial investment) and one positive value (a return) - an all-positive or all-negative series has no solvable rate and returns a #NUM! error
- •Assuming cash flows must be evenly spaced in time - IRR assumes equal periods between values, and unevenly spaced cash flows (like irregular real-world payment dates) require XIRR instead
- •Not providing a guess argument when IRR fails to converge, particularly with cash flow patterns that could mathematically support more than one valid rate of return
NPVCalculates net present value at a fixed rate, the inverse relationship to what IRR solves for (the rate that makes NPV zero).XIRRHandles cash flows at irregular, specific dates rather than IRR's assumption of equally spaced periods.MIRRAccounts for different financing and reinvestment rates, addressing some of the unrealistic assumptions baked into a plain IRR calculation.Why does IRR return a #NUM! error?
The cash flow series probably doesn't contain both a negative value (an outflow, like the initial investment) and a positive value (a return) - IRR needs both to calculate a meaningful rate.
Can IRR handle cash flows that aren't evenly spaced?
No, IRR assumes equal time periods between each cash flow. Use XIRR when your payment or return dates are irregular.
What does the guess argument do in IRR?
It gives the iterative calculation a starting point. It's rarely needed, but supplying one can help IRR converge, or resolve ambiguity, when the cash flow pattern could mathematically support multiple valid rates.
Need to translate a formula using IRR?
Use our translator to convert your complete formula