MIRR: Calculating the Modified Internal Return Rate

Syntax: How to Calculate the Modified Internal Rate of Return

TABLE FILE ...
{PRINT|SUM} field ...COMPUTE rrate/fmt = MIRR(cashflow, finrate, reinvrate, 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 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.
finrate: Is a finance rate for negative cash flows. This value must be expressed as a non-negative decimal fraction between 0 and 1. It must be constant within each sort group for which a return rate is calculated, but it can change between sort groups.
reinvrate: Is the reinvestment rate for positive cash flows. This value must be expressed as a non-negative decimal fraction between 0 and 1. It must be constant within each sort group but can change between sort groups. It must be constant within each sort group for which a return rate is calculated, but it can change between sort groups.
output: 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 MIRR 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 must be equally spaced.
Missing cash flows or dates are not supported.

Example: Calculating the Modified Internal Rate of Return

The following request calculates modified internal return rates for categories of products. It assumes a finance charge of ten percent and a reinvestment rate of ten percent. The request is sorted by date so that the correct cash flows are 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.

In order to create one cash flow value per date, the values are summed. NEWDOLL is defined in order to create negative values in each category as required by the function:

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 NEWDOLL/D12.2 = IF DATE LT '19970401' THEN -1 * DOLLARS ELSE DOLLARS;
END
TABLE FILE GGSALES
  SUM NEWDOLL
  COMPUTE RRATE/D7.2% = MIRR(NEWDOLL, .1, .1, RRATE) * 100;
  WITHIN CATEGORY
  BY CATEGORY
  BY SDATE
  WHERE SYEAR EQ 97
END

A separate rate is calculated for each category because of the WITHIN CATEGORY phrase. A portion of the output is shown:

Category     SDATE           NEWDOLL      RRATE 
--------     -----           -------      ----- 
Coffee       1997/01     -801,123.00     15.11% 
             1997/02     -682,340.00     15.11% 
             1997/03     -765,078.00     15.11% 
             1997/04      691,274.00     15.11% 
             1997/05      720,444.00     15.11% 
             1997/06      742,457.00     15.11% 
             1997/07      747,253.00     15.11% 
             1997/08      655,896.00     15.11% 
             1997/09      730,317.00     15.11% 
             1997/10      724,412.00     15.11% 
             1997/11      620,264.00     15.11% 
             1997/12      762,328.00     15.11% 
Food         1997/01     -672,727.00     16.24% 
             1997/02     -699,073.00     16.24% 
             1997/03     -642,802.00     16.24% 
             1997/04      718,514.00     16.24% 
             1997/05      660,740.00     16.24% 
             1997/06      734,705.00     16.24% 
             1997/07      760,586.00     16.24%

To calculate one modified internal return rate for all of the report data, use the WITHIN TABLE phrase. In this case, the data does not have to be sorted by CATEGORY:

DEFINE FILE GGSALES
 SDATE/YYM = DATE;
 SYEAR/Y = SDATE;
 NEWDOLL/D12.2 = IF DATE LT '19970401' THEN -1 * DOLLARS ELSE DOLLARS;
END
 
TABLE FILE GGSALES
  SUM NEWDOLL
  COMPUTE RRATE/D7.2% = MIRR(NEWDOLL, .1, .1, RRATE) * 100;
  WITHIN TABLE
  BY SDATE
  WHERE SYEAR EQ 97
END

The output is:

SDATE           NEWDOLL      RRATE 
-----           -------      ----- 
1997/01   -1,864,129.00     15.92% 
1997/02   -1,861,639.00     15.92% 
1997/03   -1,874,439.00     15.92% 
1997/04    1,829,838.00     15.92% 
1997/05    1,899,494.00     15.92% 
1997/06    1,932,630.00     15.92% 
1997/07    2,005,402.00     15.92% 
1997/08    1,838,863.00     15.92% 
1997/09    1,893,944.00     15.92% 
1997/10    1,933,705.00     15.92% 
1997/11    1,865,982.00     15.92% 
1997/12    2,053,923.00     15.92%