XIRR: Calculating the Modified Internal Return Rate (Periodic or Non-Periodic)

How to:

Reference:

Available languages: reporting

The XIRR function calculates the internal rate of return for a series of cash flows that can be periodic or non-periodic.

Syntax: How to Calculate the Internal Rate of Return 

TABLE FILE ...
{PRINT|SUM} field ...  
COMPUTE rrate/fmt = XIRR (cashflow, dates,guess, maxiterations, output);
WITHIN {sort_field|TABLE}

where:

field ...

Are fields that appear in the report output.

rrate

Is the field that contains the calculated return rate.

fmt

Is the format of the return rate. The data type must be D.

cashflow

Is a numeric field. Each value of this field represents either a payment (negative value) or income (positive value) for one period. The values must be in the correct sequence in order for the sequence of cash flows to be calculated correctly. The dates corresponding to each cash flow should be equally spaced and sorted in chronological order. The calculation requires at least one negative value and one positive value in the cashflow field. If the values are all positive or all negative, a zero result is returned.

dates

Is a date field containing the cash flow dates. The dates must be full component dates with year, month, and day components. Dates cannot be stored in fields with format A, I, or P. They must be stored in date fields (for example, format YMD, not AYMD). There must be the same number of dates as there are cash flow values. The number of dates must be the same as the number of cash flows.

guess

Is an (optional) initial estimate of the expected return rate expressed as a decimal. The default value is .1 (10%). To accept the default, supply the value 0 (zero) for this argument.

maxiterations

Is an (optional) number specifying the maximum number of iterations that can be used to resolve the rate using Newton's method. 50 is the default value. To accept the default, supply the value 0 (zero) for this argument. The rate is considered to be resolved when successive iterations do not differ by more than 0.0000003. If this level of accuracy is achieved within the maximum number of iterations, calculation stops at that point. If it is not achieved after reaching the maximum number of iterations, calculation stops and the value calculated by the last iteration is returned.

output

D

Is the name of the field that contains the return rate, or its format enclosed in single quotation marks.

sort_field

Is a field that sorts the report output and groups it into subsets of rows on which the function can be calculated separately. To calculate the function using every row of the report output, use the WITHIN TABLE phrase. A WITHIN phrase is required.

Reference: Usage Notes for the XIRR Function

  • This function is only supported in a COMPUTE command with the WITHIN phrase.
  • The cash flow field must contain at least one negative value and one positive value.
  • Dates cannot be stored in fields with format A, I, or P. They must be stored in date fields (for example, format YMD, not AYMD).
  • Cash flows or dates with missing values are not supported.

Example: Calculating the Internal Rate of Return

The following request creates a FOCUS data source with cash flows and dates and calculates the internal return rate.

The Master File for the data source is: 

FILENAME=XIRR01,SUFFIX=FOC
SEGNAME=SEG1,SEGTYPE=S1
FIELDNAME=DUMMY,FORMAT=A2,$
FIELDNAME=DATES,FORMAT=YYMD,$
FIELDNAME=CASHFL,FORMAT=D12.4,$
END

The procedure to create the data source is: 

CREATE FILE XIRR01
MODIFY FILE XIRR01
FREEFORM DUMMY DATES CASHFL
DATA
AA,19980101,-10000. ,$
BB,19980301,2750.   ,$
CC,19981030,4250.   ,$
DD,19990215,3250.   ,$
EE,19990401,2750.   ,$
END

The request is sorted by date so that the correct cash flows can be calculated. The rate returned by the function is multiplied by 100 in order to express it as a percent rather than a decimal value. Note that the format includes the % character. This causes a percent symbol to display, but it does not calculate a percent: 

TABLE FILE XIRR01
PRINT CASHFL
COMPUTE RATEX/D12.2%=XIRR(CASHFL, DATES, 0., 0., RATEX) * 100;
WITHIN TABLE
BY DATES
END

One rate is calculated for the entire report because of the WITHIN TABLE phrase: 

DATES               CASHFL       RATEX 
----                ------       ----- 
1998/01/01    -10,000.0000      37.49%
1998/03/01      2,750.0000      37.49%
1998/10/30      4,250.0000      37.49%
1999/02/15      3,250.0000      37.49%
1999/04/01      2,750.0000      37.49%