MIRR_q

Updated 2024-02-23 21:41:36.477000

Syntax

SELECT [westclintech].[wct].[MIRR_q] (
  <@Cashflows_RangeQuery, nvarchar(4000),>
 ,<@Finance_rate, float,>
 ,<@Reinvest_rate, float,>)

Description

Use MIRR_q to calculate the modified internal rate of return, where positive and negative cash flows are financed at different rates

Given a vector of cash flows, the equation for MIRR is:

MIRR=\left(\frac{-NPV\left(r_r,c_{+}\right)\times\left(1+r_r\right)^n}{NPV\left(r_f,c_{-}\right)\times\left(1+r_f\right)}\right)^{\frac{1}{n-1}}-1

Where

    n = Number of cashflows

    c+ = The greater of 0 or the cash flow amount

    c- = The lesser of 0 or the cash flow amount

    rr = Reinvestment rate

    rf = Finance rate

    NPV = Net Present Value function

Arguments

@Reinvest_rate

the rate to be applied to positive cash flows. @Reinvest_rate is an expression of type float or of a type that can be implicitly converted to float.

@Finance_rate

the rate to be applied to negative cash flows. @Finance_rate is an expression of type float or of a type that can be implicitly converted to float.

@Cashflows_RangeQuery

a select statement, as text, which specifies the cash flow values to be used in the modified internal rate of return calculation.

Return Type

float

Remarks

This function requires that there be at least one positive cash flow and one negative cash flow.

The formula for modified internal rate of return is: 

MIRR = (((-NPV(@Reinvest_rate, reinvest_cash_flows) * (1 + @Reinvest_rate) ^ n) / (NPV(@Finance_rate, finance_cash_flows) * (1 + @Finance_rate))) ^ (1 / (n - 1))) - 1

 Where n is the number cash flows.

Examples

Create a table to store cash flow projections, by year, for a variety of projects:

CREATE TABLE [dbo].[cf1]
(
    [proj_no] [float] NOT NULL,
    [period] [float] NOT NULL,
    [cf_amt] [float] NOT NULL,
    CONSTRAINT [PK_cf1]
        PRIMARY KEY CLUSTERED (
                                  [proj_no] ASC,
                                  [period] ASC
                              )
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
                  ALLOW_ROW_LOCKS = ON,
              ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
) ON [PRIMARY];

Insert the data for 3 projects into the table.

INSERT INTO cf1
VALUES
(1, 1, -25000);
INSERT INTO cf1
VALUES
(1, 2, 5000);
INSERT INTO cf1
VALUES
(1, 3, 10000);
INSERT INTO cf1
VALUES
(1, 4, 15000);
INSERT INTO cf1
VALUES
(1, 5, 20000);
INSERT INTO cf1
VALUES
(1, 6, 25000);
INSERT INTO cf1
VALUES
(2, 1, -25000);
INSERT INTO cf1
VALUES
(2, 2, 25000);
INSERT INTO cf1
VALUES
(2, 3, 10000);
INSERT INTO cf1
VALUES
(2, 4, 15000);
INSERT INTO cf1
VALUES
(2, 5, 10000);
INSERT INTO cf1
VALUES
(2, 6, 5000);
INSERT INTO cf1
VALUES
(3, 1, -25000);
INSERT INTO cf1
VALUES
(3, 2, 5000);
INSERT INTO cf1
VALUES
(3, 3, 25000);
INSERT INTO cf1
VALUES
(3, 4, 10000);
INSERT INTO cf1
VALUES
(3, 5, 20000);
INSERT INTO cf1
VALUES
(3, 6, 15000);

Enter a SELECT statement to calculate the MIRR for the 3 projects so as to compare the results, assuming a 10% finance rate and a 12% reinvestment rate:

select d.proj_no,
       wct.MIRR_q('SELECT c.cf_amt
      from cf1 c
      where c.proj_no = ' + convert(char, d.proj_no), .10, .12) as MIRR
from cf1 d
group by d.proj_no;

Here is the result set.

{"columns":[{"field":"proj_no","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"1","MIRR":"0.286588269972987"},{"proj_no":"2","MIRR":"0.287376740493951"},{"proj_no":"3","MIRR":"0.300312295003069"}]}