13:49:00
IRR Function and Ways to use
Objective: Returns the internal rate of return for a series
of cash flows represented by the numbers in values. These cash flows do not
have to be even, as they would be for an annuity. However, the cash flows must
occur at regular intervals, such as monthly or annually. The internal rate of
return is the interest rate received for an investment consisting of payments
(negative values) and income (positive values) that occur at regular periods.
Syntax
IRR(values, [guess])The IRR function syntax has the
following arguments (argument: A value that provides information to an action,
an event, a method, a property, a function, or a procedure.):
Values Required. An
array or a reference to cells that contain numbers for which you want to
calculate the internal rate of return.
Values must contain at least one positive value and one
negative value to calculate the internal rate of return.
IRR uses the order of values to interpret the order of cash
flows. Be sure to enter your payment and income values in the sequence you
want.
If an array or reference argument contains text, logical
values, or empty cells, those values are ignored.
Guess Optional. A
number that you guess is close to the result of IRR.
Microsoft Excel uses an iterative technique for calculating
IRR. Starting with guess, IRR cycles through the calculation until the result
is accurate within 0.00001 percent. If IRR can't find a result that works after
20 tries, the #NUM! error value is returned.
In most cases you do not need to provide guess for the IRR
calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
If IRR gives the #NUM! error value, or if the result is not
close to what you expected, try again with a different value for guess.
Remarks
IRR is closely related to NPV, the net present value
function. The rate of return calculated by IRR is the interest rate
corresponding to a 0 (zero) net present value. The following formula
demonstrates how NPV and IRR are related:
NPV(IRR(B1:B6),B1:B6)equals 3.60E-08 [Within the accuracy of
the IRR calculation, the value 3.60E-08 is effectively 0 (zero).]
Example
