Updated: 07 February 2011

*Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.*

Click here for the SQL2005 version of the MIRR function

Use the AGGREGATE function MIRR to calculate the modified internal rate of return, where positive and negative cash flows are financed at different rates. For a description of the formula used to calculate the modified internal rate of return, see the Examples.

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

r_{r} = Reinvestment rate

r_{f} = Finance rate

NPV = Net Present Value function

SELECT [westclintech].[wct].[MIRR] (

<@CF_Amt, float,>

,<@Per, int,>

,<@Finance_rate, float,>

,<@Reinvest_rate, float,>)

the cash flow amounts. *@CF_Amt* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the period in which the cash flow occurred. *@Per* is an expression of type **int** or of a type that can be implicitly converted to **int**.

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**.

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**.

float

Â· The MIRR aggregate function requires a series of cash flows (*@CF_Amt*) and the periods in which those cash flows occurred (*@Per*) as input. As a result, the order of the cash flows is not important.

Â· Periods in which the cash flow is zero, or in which there is no cash flow, do not have to be included.

Â· The periods can start and end with any integer value, including negative numbers.

Â· There can be multiple cash flows with the same period number.

Â· The finance rate (*@Finance_rate*) must remain constant for the aggregate GROUP.

Â· The reinvestment rate (*@Reinvest_rate*) must remain constant for the aggregate GROUP.

Â· If the finance rate (*@Finance_rate*) is equal to -1, a NULL will be returned.

Â· If the reinvestment rate (*@Reinvest_rate*) is equal to -1, a NULL will be returned.

Â· It is important to be consistent with the units for *@Finance_rate*,* @Reinvest_rate *and *@Per*. For example if payments are to be paid monthly, then *@Finance_rate* and* @Reinvest_rate *should be the monthly rate, which can be specified as the annual rate divided by 12. If payments are made quarterly, divide the annual rate by 4. If payments are made semi-annually, divide the annual rate by 2.

Â· Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.

Â· If there are no positive cash flows, MIRR will return a value of -1 (-100%).

Â· If there are no negative cash flows, or all the cash flows have the same period number, MIRR will return a NULL.

Â· For calculations involving dates, consider using the XMIRR aggregate function.

In this example there are no cash flows in periods 3, 5, 7, 9, 10, 12, 13, 15, or 16. There are 2 cash flows, one positive and one negative, in period 6.

SELECT wct.MIRR(cf_amt, per, .008333, .01) as MIRR

FROM (VALUES

(-1000, 0),

(-4000, 1),

(-6000, 2),

(-10000, 4),

(-12000, 6),

(1000, 6),

(7000, 8),

(12000, 11),

(15000, 14),

(22000, 17)

) n(cf_amt, per)

This produces the following result.

MIRR

----------------------

0.0366852516799792

(1 row(s) affected)

In this example, we will use the same cash flows as the previous example, with the same intervals between the cash flows, but instead of starting at period 0, we will start at period 100.

SELECT wct.MIRR(cf_amt, per, .008333, .01) as MIRR

FROM (VALUES

(-1000, 100),

(-4000, 101),

(-6000, 102),

(-10000, 104),

(-12000, 106),

(1000, 106),

(7000, 108),

(12000, 111),

(15000, 114),

(22000, 117)

) n(cf_amt, per)

This produces the following result.

MIRR

----------------------

0.0366852516799792

(1 row(s) affected)

In this example, we will use the same cash flows, with the same spacing among periods, but we will use negative period numbers and the cash flows will not be in order in the derived table.

SELECT wct.MIRR(cf_amt, per, .008333, .01) as MIRR

FROM (VALUES

(-1000, -18),

(-12000, -12),

(1000, -12),

(7000, -10),

(-6000, -16),

(12000, -7),

(-4000, -17),

(15000, -4),

(22000, -1),

(-10000, -14)

) n(cf_amt, per)

This produces the following result.

MIRR

----------------------

0.0366852516799792

(1 row(s) affected)

In this example, the period numbers are not stored in a table, but have to be calculated as an input into the function. We will store the cash flows by year and month.

SELECT wct.MIRR(cf_amt, yr*12 + mth, .008333, .01) as MIRR

FROM (VALUES

(2011,1,-1000),

(2011,2,-4000),

(2011,3,-6000),

(2011,5,-10000),

(2011,7,-12000),

(2011,7,1000),

(2011,9,7000),

(2011,12,12000),

(2012,3,15000),

(2012,6,22000)

) n(yr, mth, cf_amt)

This produces the following result.

MIRR

----------------------

0.0366852516799792

(1 row(s) affected)

In this example, we will add another column to the derived table from the previous example, which will allow for different rates for each period. This will return an error message, as the function requires that the rate values be the same for all cash flows.

SELECT wct.MIRR(cf_amt, yr*12 + mth, f_rate, r_rate) as MIRR

FROM (VALUES

(2011,1,-1000, .008333, .01),

(2011,2,-4000, .008333, .01),

(2011,3,-6000, .008333, .01),

(2011,5,-10000, .008333, .01),

(2011,7,-12000, .008333, .01),

(2011,7,1000, .008333, .01),

(2011,9,7000, .008333, .01),

(2011,12,12000, .008333, .01),

(2012,3,15000, .01, .0125),

(2012,6,22000, .01, .0125)

) n(yr, mth, cf_amt, f_rate, r_rate)

This produces the following message and will not return a result.

Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "MIRR":

XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:

[Product version 1.7 Build: 0126.208]

An error occurred in Aggregate Function. (Accumulate) - Invalid Reinvest_rate - Reinvest_rate parameter cannot vary

Function: MIRR

Passed Parameters:

'CF_Amt' = System.Data.SqlTypes.SqlDouble:15000

'Per' = System.Data.SqlTypes.SqlInt32:24147

'Finance_rate' = System.Data.SqlTypes.SqlDouble:0.01

'Reinvest_rate' = System.Data.SqlTypes.SqlDouble:0.0125

'Ref' = System.String [3chars] :<2>

Internal refs:

'd_rate' = System.Double:0.008333

'f_rate' = System.Double:0.01

'disc_amt' = System.Double:-31958.8343722178

'fv_amt' = System.Double:20263.1170501

'start_per' = System.Int32:24133

'end_per' = System.Int32:24144

*** Extra Information: Invalid Reinvest_rate - Reinvest_rate parameter cannot vary

XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:

at XLeratorDB_financial2008.MIRR.HandleException(String Method, String Ref, MIRR& Merge, FunctionParamsClass& Params, Exception& Ex)

at XLeratorDB_financial2008.MIRR.Accumulate(SqlDouble CF_Amt, SqlInt32 Per, SqlDouble Finance_rate, SqlDouble Reinvest_rate)

.

In this example, we set the finance rate equal to -1.

SELECT wct.MIRR(cf_amt, per, -1, .01) as MIRR

FROM (VALUES

(-1000, 0),

(-4000, 1),

(-6000, 2),

(-10000, 4),

(-12000, 6),

(1000, 6),

(7000, 8),

(12000, 11),

(15000, 14),

(22000, 17)

) n(cf_amt, per)

This produces the following result.

MIRR

----------------------

NULL

(1 row(s) affected)

This example demonstrates the formula used to calculate MIRR, using other AGGREGATE functions.

SELECT MIRR

,POWER(num/-denom,cast(1 as float)/(max_per-min_per))-1 as my_calc

FROM(

SELECT wct.MIRR(cf_amt, per, .008333, .01)

,wct.ENPV(.008333, case when cf_amt < 0 THEN cf_amt ELSE 0 END,per)

,wct.NFV(.01, case when cf_amt > 0 THEN cf_amt ELSE 0 END,per)

,MAX(per)

,MIN(per)

FROM (VALUES

(-1000, 0),

(-4000, 1),

(-6000, 2),

(-10000, 4),

(-12000, 6),

(1000, 6),

(7000, 8),

(12000, 11),

(15000, 14),

(22000, 17)

) n(cf_amt, per)

) m(mirr, denom, num, max_per, min_per)

This produces the following result.

MIRR my_calc

---------------------- ----------------------

0.0366852516799792 0.0366852516799792

(1 row(s) affected)

In this example we will evaluate 3 different projects with different cash flow characteristics as well as different funding rates and different reinvestment rates.

SELECT Project

,cast(finance_rate as float) / cast(12 as float) as finance_rate

,cast(reinvest_rate as float) / cast(12 as float) as reinvest_rate

INTO #t

FROM (

VALUES

('Project A', .10, .12),

('Project B', .09, .13),

('Project C', .08, .14)

) m(Project, finance_rate,reinvest_rate)

SELECT n.Project

,wct.MIRR(cf_amt, yr*12 + mth, #t.finance_rate, #t.reinvest_rate) as MIRR

FROM (VALUES

('Project A', 2011,1,-1000),

('Project A', 2011,2,-4000),

('Project A', 2011,3,-6000),

('Project A', 2011,5,-10000),

('Project A', 2011,7,-12000),

('Project A', 2011,7,1000),

('Project A', 2011,9,7000),

('Project A', 2011,12,12000),

('Project A', 2012,3,15000),

('Project A', 2012,6,22000),

('Project B', 2011,1,-5000),

('Project B', 2011,2,-4000),

('Project B', 2011,3,-11000),

('Project B', 2011,5,-7000),

('Project B', 2011,7,-4000),

('Project B', 2011,7,7000),

('Project B', 2011,9,11000),

('Project B', 2011,12,9000),

('Project B', 2012,3,6000),

('Project B', 2012,6,3000),

('Project C', 2011,1,-15000),

('Project C', 2011,2,-2200),

('Project C', 2011,3,-11000),

('Project C', 2011,4,-7000),

('Project C', 2011,5,-7000),

('Project C', 2011,6,-4000),

('Project C', 2011,8,-8000),

('Project C', 2011,8,3000),

('Project C', 2011,9,11000),

('Project C', 2011,10,14000),

('Project C', 2011,11,16000),

('Project C', 2011,12,34000)

) n(Project, yr, mth, cf_amt), #t

WHERE n.Project = #t.Project

GROUP BY n.Project

DROP TABLE #t

This produces the following result.

Project MIRR

--------- ----------------------

Project A 0.0366853292453446

Project B 0.0144037225349316

Project C 0.0366117842937457

(3 row(s) affected)