XMIRR
Updated: 07 February 2011
Use the AGGREGATE function XMIRR to calculate the modified internal rate of return, where positive and negative cash flows are financed at different rates and where the cash flows occur irregularly and are specified by date. See the Examples for an explanation of the XMIRR calculation.
Given a set of cash flow dates and rates the equation for XMIRR is:
Where
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
d = date of the cash flow
dmax = maximum date value in the set
dmin = minimum date value in the set
XNPV = XNPV function
XNFV = XNFV function
Syntax
SELECT [westclintech].[wct].[MIRR] (
<@CF_Amt, float,>
,<@CF_Date, datetime,>
,<@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.
@CF_Date
the date on which the cash flow occurred. @CF_Date is an expression of type datetime or of a type that can be implicitly converted to datetime.
@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 XMIRR aggregate function requires a series of cash flows (@CF_Amt) and the dates on which those cash flows occurred (@CF_Date) as input. As a result, the order of the cash flows is not important.
· Dates in which the cash flow is zero, or in which there is no cash flow, do not have to be included.
· There can be multiple cash flows with the same date.
· The finance rate (@Finance_rate) must remain constant for the aggregate GROUP.
· The reinvestment rate (@Reinvest_rate) must remain constant for the aggregate.
· 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.
· The @Reinvest_rate and the @Finance_rate are both annual rates.
· 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, XMIRR will return a value of -1 (-100%).
· If there are no negative cash flows, or all the cash flows have the same date, XMIRR will return a NULL.
Examples
In this example cash flows occur sporadically from the 31-Jan-2011 to 19-Jun-2012. The finance rate is 10% and the reinvestment rate is 12%. The result reflects discounting the cash flows to 31-Jan-2011.
SELECT wct.XMIRR(cf_amt, cf_date, .1, .12) as MIRR
FROM (VALUES
(-1000, '01/31/2011'),
(-4000, '03/15/2011'),
(-6000, '04/19/2011'),
(-10000, '06/06/2011'),
(-12000, '07/02/2011'),
(1000, '07/28/2011'),
(7000, '08/26/2011'),
(12000, '11/16/2011'),
(15000, '03/17/2012'),
(22000, '06/19/2012')
) n(cf_amt, cf_date)
This produces the following result.
MIRR
----------------------
0.555762831092004
(1 row(s) affected)
In this example, we will use the same cash flows as the previous example, but instead of discounting the cash flows to the 31st of January, 2011, we will discount them to the 31st of December, 2010. Since XMIRR is insensitive to order, we can just add another row at the end of the derived table.
SELECT wct.XMIRR(cf_amt, cf_date, .1, .12) as MIRR
FROM (VALUES
(-1000, '01/31/2011'),
(-4000, '03/15/2011'),
(-6000, '04/19/2011'),
(-10000, '06/06/2011'),
(-12000, '07/02/2011'),
(1000, '07/28/2011'),
(7000, '08/26/2011'),
(12000, '11/16/2011'),
(15000, '03/17/2012'),
(22000, '06/19/2012'),
(0, '12/31/2010')
) n(cf_amt, cf_date)
This produces the following result.
MIRR
----------------------
0.524881753702055
(1 row(s) affected)
We could have achieved the same result with a variety of different syntaxes. Here’s another way to arrive at the same result.
SELECT wct.XMIRR(cf_amt, cf_date, .1, .12) as MIRR
FROM (
SELECT 0
,'12/31/2010'
UNION
SELECT *
FROM (VALUES
(-1000, '01/31/2011'),
(-4000, '03/15/2011'),
(-6000, '04/19/2011'),
(-10000, '06/06/2011'),
(-12000, '07/02/2011'),
(1000, '07/28/2011'),
(7000, '08/26/2011'),
(12000, '11/16/2011'),
(15000, '03/17/2012'),
(22000, '06/19/2012'),
(0, '12/31/2010')
) n(cf_amt, cf_date)
) m(cf_amt, cf_date)
This produces the following result.
MIRR
----------------------
0.524881753702055
(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 "XMIRR":
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: XMIRR
Passed Parameters:
'CF_Amt' = System.Data.SqlTypes.SqlDouble:15000
'CF_Date' = System.Data.SqlTypes.SqlDateTime:3/17/2012 12:00:00 AM
'Finance_rate' = System.Data.SqlTypes.SqlDouble:0.12
'Reinvest_rate' = System.Data.SqlTypes.SqlDouble:0.15
Internal refs:
'd_rate' = System.Double:0.1
'f_rate' = System.Double:0.12
'disc_amt' = System.Double:-32043.7444096259
'fv_amt' = System.Double:20215.5742618483
'start_date' = System.Double:40574
'end_date' = System.Double:40863
*** Extra Information: Invalid Reinvest_rate - Reinvest_rate parameter cannot vary
XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:
at XLeratorDB_financial2008.XMIRR.HandleException(String Method, String Ref, XMIRR& Merge, FunctionParamsClass& Params, Exception& Ex)
at XLeratorDB_financial2008.XMIRR.Accumulate(SqlDouble CF_Amt, SqlDateTime CF_Date, SqlDouble Finance_rate, SqlDouble Reinvest_rate)
.
In this example, we set the finance rate equal to -1.
SELECT wct.XMIRR(cf_amt, cf_date, -1, .12) as MIRR
FROM (VALUES
(-1000, '01/31/2011'),
(-4000, '03/15/2011'),
(-6000, '04/19/2011'),
(-10000, '06/06/2011'),
(-12000, '07/02/2011'),
(1000, '07/28/2011'),
(7000, '08/26/2011'),
(12000, '11/16/2011'),
(15000, '03/17/2012'),
(22000, '06/19/2012')
) n(cf_amt, cf_date)
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(365 as float)/(max_date-min_date))-1 as my_calc
FROM(
SELECT wct.XMIRR(cf_amt, cf_date, .1, .12)
,wct.XNPV(.10, case when cf_amt < 0 THEN cf_amt ELSE 0 END,cf_date)
,wct.XNFV(.12, case when cf_amt > 0 THEN cf_amt ELSE 0 END,cf_date)
,cast(MAX(cast(cf_date as datetime)) as float)
,cast(MIN(cast(cf_date as datetime)) as float)
FROM (VALUES
(-1000, '01/31/2011'),
(-4000, '03/15/2011'),
(-6000, '04/19/2011'),
(-10000, '06/06/2011'),
(-12000, '07/02/2011'),
(1000, '07/28/2011'),
(7000, '08/26/2011'),
(12000, '11/16/2011'),
(15000, '03/17/2012'),
(22000, '06/19/2012')
) n(cf_amt, cf_date)
) m(mirr, denom, num, max_date, min_date)
This produces the following result.
MIRR my_calc
---------------------- ----------------------
0.555762831092004 0.555762831092004
(1 row(s) affected)
In this example we will evaluate 3 differente projects with different cash flow characteristics as well as different funding rates and different reinvestment rates.
SELECT Project
,finance_rate
,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.XMIRR(cf_amt, cf_date, #t.finance_rate, #t.reinvest_rate) as MIRR
FROM (VALUES
('Project A', '2011-01-31',-1000),
('Project A', '2011-02-14',-4000),
('Project A', '2011-03-29',-6000),
('Project A', '2011-05-01',-10000),
('Project A', '2011-07-13',-12000),
('Project A', '2011-07-29',1000),
('Project A', '2011-09-05',7000),
('Project A', '2011-12-24',12000),
('Project A', '2012-03-17',15000),
('Project A', '2012-06-19',22000),
('Project B', '2011-01-15',-5000),
('Project B', '2011-02-25',-4000),
('Project B', '2011-03-15',-11000),
('Project B', '2011-05-22',-7000),
('Project B', '2011-07-11',-4000),
('Project B', '2011-07-29',7000),
('Project B', '2011-09-14',11000),
('Project B', '2011-12-12',9000),
('Project B', '2012-03-20',6000),
('Project B', '2012-06-01',3000),
('Project C', '2011-01-01',-15000),
('Project C', '2011-02-19',-2200),
('Project C', '2011-03-22',-11000),
('Project C', '2011-04-19',-7000),
('Project C', '2011-05-07',-7000),
('Project C', '2011-06-22',-4000),
('Project C', '2011-08-15',-8000),
('Project C', '2011-08-31',3000),
('Project C', '2011-09-11',11000),
('Project C', '2011-10-13',14000),
('Project C', '2011-11-24',16000),
('Project C', '2011-12-27',34000)
) n(Project, cf_date, 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.548223172908616
Project B 0.184694776721734
Project C 0.497476531222213
(3 row(s) affected)