MIRR: Calculating the Modified Internal Return Rate

How to: Reference: |

Available languages: reporting

The MIRR function calculates the modified internal rate of return for a series of periodic cash flows.

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%

WebFOCUS |