The IRR function computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for each period that makes the net present value of the corresponding cash flows equal to zero.
DECIMAL (For example, n 8.25 percent internal rate of return produces a result value of .0825
.)
The result returned by the IRR function is dimensioned by all the dimensions of cashflows except its time dimension. When cashflows is dimensioned only by the time dimension, IRR returns a single value.
IRR(cashflows, [time-dimension])
A numeric expression dimensioned by time-dimension, that specifies the series of cash flow values.
Note:
All the cash flows used to compute a result value are assumed to occur at the same relative point within the period with which they are associated. Cash flows that corresponds to out-of-status dimension positions are ignoredA name that specifies the time dimension. When cashflows has a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the time-dimension argument is optional since IRR automatically uses the DAY, WEEK, MONTH, QUARTER, or YEAR dimension of cashflows when you do not specify a value for time-dimension.
Some series of cash flows have multiple discount rates, which make the net present value equal to zero. In such cases, IRR will find and return only one of these discount rates as the internal rate of return. When there is only a single solution and it is between -99.9 percent and 10,000 percent, the IRR function will find it. When IRR cannot calculate an internal rate of return, the corresponding value in the result is NA
.
Example 7-131 Calculating the Internal Rate of Return
The following statements create a dimension called project
, add values to it, and create a variable called cflow
, which is dimensioned by year
and project
.
DEFINE project DIMENSION TEXT MAINTAIN project ADD 'a' 'b' 'c' 'd' 'e' DEFINE cflow VARIABLE DECIMAL <project year>
Once you have assigned the following values to CFLOW,
------------------------CFLOW---------------------- -----------------------PROJECT--------------------- YEAR a b c d e -------------- ---------- ---------- ---------- ---------- ------- Yr95 -200.00 -200.00 -300.00 -100.00 -200.00 Yr96 100.00 150.00 200.00 25.00 25.00 Yr97 100.00 400.00 200.00 100.00 200.00
then the following statement
REPORT IRR(cflow, year)
produces the following report of the internal rate of return.
IRR(CFLOW, PROJECT YEAR) -------------- ---------- a 0.00 b 0.84 c 0.22 d 0.13 E 0.06