IRR
The IRR function returns the internal rate of return for an investment based on a series of potentially irregular cash flows (payments that do not need to be a constant amount) that occur at regular time intervals.
IRR(flows-range, estimate)
flows-range: A collection that contains the cash flow values. flows-range must contain number values. Income (a cash inflow) is specified as a positive number, and an expenditure (a cash outflow) is specified as a negative number. There must be at least one positive and one negative value included within the collection. Cash flows must be specified in chronological order and equally spaced in time (for example, each month). If a period does not have a cash flow, use 0 for that period.
estimate: An optional number value specifying the initial estimate for the rate of return. estimate is entered as a decimal (for example, 0.08) or with a per cent sign (for example, 8%). If estimate is omitted, 10% is assumed. If the default value does not result in a solution, initially try a larger positive value. If this does not result in an outcome, try a small negative value. The minimum value allowed is –1.
Notes
If all periodic cash flows are the same, consider using the NPV function.
Example 1 |
|---|
Suppose you are planning for your daughter’s university education. She has just turned 13 and you expect she will begin college in 5 years. You have $75,000 to set aside in a savings account today and will add the bonus you receive from your employer at the end of each year. Because you believe your bonus will increase each year, you expect to be able to set aside $5,000, $7,000, $8,000, $9,000 and $10,000, respectively, at the end of each of the next 5 years. You think you will need to have $150,000 set aside for her education by the time your daughter reaches 18. Assume that cells B2 to G2 contain the amounts you will deposit, as negative amounts because they are outflows, starting with the initial deposit (-75000, -5000, -7000, -8000, -9000, -10000). Cell H2 contains the amount you estimate will be required to fund your daughter’s university education, expressed as a positive number because it will be a cash inflow (150000). flows-range is B2:H2. =IRR(B2:H2) returns 5.69965598016224%, the compound interest rate required so that the amounts deposited, together with annual interest, grow to $150,000 over the 5 years. |
Example 2 |
|---|
Suppose you are presented with the opportunity to invest in a partnership. The initial investment required is $50,000. Because the partnership is still developing its product, an additional $25,000 and $10,000 must be invested at the end of the first and second years, respectively. Assume you place these cash outflows, as negative numbers, in cells B3 to D3. In the third year, the partnership expects to be self-funding but not return any cash to investors (0 in E3). In the fourth and fifth years, investors are projected to receive $10,000 and $30,000, respectively (as positive numbers in F3 and G3). At the end of the sixth year, the company expects to sell and investors are projected to receive $100,000 (as a positive number in H3). =IRR(B3:H3) returns 10.2411564203%, the compound annual interest rate earned (the internal rate of return) assuming all cash flows happen as scheduled. |