The IRR function returns the Internal Rate of Return for a supplied series of periodic cash flows, i.e. a set of values, which includes an initial investment value and a series of net income values.
|values||A range of cells that represent the series of cash flows|
|[est_irr]||Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an [est_irr] of 0.1 or 10%|
Note: Excel tries to recalculate the IRR until the result is accurate within 0.00001 percent. If after 20 tries Excel has not calculated an accurate value, it will return the #NUM! error.
|3||$3,000||Income (yr 1)|
|4||$5,000||Income (yr 2)|
|5||$1,200||Income (yr 3)|
|6||$4,000||Income (yr 4)|
|7||$3,800||Income (yr 5)|
|10||=IRR(A2:A5)||12.16%||Investment’s internal rate of return after 3 years|
|11||=IRR(A2:A7)||35.04%||Investment’s internal rate of return after 5 years|
|12||IRR(A2:A4,-10%)||4.06%||To calculate the internal rate of return after two years, you need to include a guess, in this example, -10%|
Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.
Common Function Error(s)
|Problem||What went wrong|
The Internal Rate of Return (IRR) indicates the profitability of an investment and therefore is commonly used in business, when choosing between investments.
This measurement uses a series of cash flows (including an initial investment, along with the net income) over a number of periods, to calculate the compounded return, assuming the Net Present Value of the investment is zero.
The value of the IRR is calculated as the value of r that satisfies the following equation:
- Cn = a series of cash flows
- N = the number of periods over which the returns have been made
A full explanation of the IRR can be found on the Wikipedia Internal Rate of Return page.