 SQL Server MIRR function

MIRR

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.

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.

Given a vector of cash flows, the equation for MIRR is: 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
Syntax
SELECT [westclintech].[wct].[MIRR] (
<@CF_Amt, float,>
,<@Per, int,>
,<@Finance_rate, float,>
,<@Reinvest_rate, float,>)
Arguments
@CF_Amt
the cash flow amounts. @CF_Amt is an expression of type float or of a type that can be implicitly converted to float.
@Per
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.
@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.

@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.
Return Type
float
Remarks
·         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.
Examples
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)

Support  Copyright 2008-2019 Westclintech LLC         Privacy Policy        Terms of Service