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%

WebFOCUS |