@IRR(guess;range) calculates the internal rate of return (profit) for a series of cash-flow values generated by an investment. The internal rate of return is the percentage rate that equates the present value of an expected future series of cash flows to the initial investment.
guess is a decimal or percentage value that represents your estimate of the internal rate of return. In most cases, guess should be a percentage between 0 (0%) and 1 (100%). With very large cash flows, make guess as accurate as possible.
range is the address or name of a range that contains the cash flows. Your spreadsheet considers negative numbers as cash outflows and positive numbers as cash inflows. Normally, the first cash-flow amount in the range is a negative number (a cash outflow) that represents the investment. Your spreadsheet assigns the value 0 to all blank cells and labels in range and includes them in the calculation.
Use @IRR to determine the profitability of an investment. Combine @IRR with other financial @functions, such as @NPV, to assess an investment.
Your spreadsheet assumes that cash flows are received at regular, equal intervals.
@IRR uses a series of approximations, starting with your guess value, to calculate the internal rate of return. Start with a guess that you feel is reasonable for the internal rate of return. More than one solution may be possible, so try another guess if the result is less than 0 or greater than 1.
If @IRR cannot approximate the result to within 0.0000001 after 30 calculation iterations, the result is ERR. If your guesses continue to return ERR, use @NPV to determine a better guess. If @NPV returns a positive value, your guess is too low. If @NPV returns a negative value, your guess is too high. @NPV returns 0 if your guess is accurate.
Use @AVG to determine the internal rate of return if you calculate several rates.
A schedule calculates the internal rate of return of an initial investment of $10,000 that is followed by 12 monthly payments of $1,500. guess (12.00%) is entered in GUESS and the payments are listed in a range named CASHFLOWS.
@IRR(GUESS;CASHFLOWS) = 10.45%, the internal rate of return.