XMIRR
Updated 2023-10-09 13:12:20.833000
Syntax
SELECT [westclintech].[wct].[MIRR] (
<@CF_Amt, float,>
,<@CF_Date, datetime,>
,<@Finance_rate, float,>
,<@Reinvest_rate, float,>)
Description
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:
Wherec+ = The greater of 0 or the cash flow amountc- = The lesser of 0 or the cash flow amountrr = Reinvestment raterf = Finance rated = date of the cash flowdmax = maximum date value in the setdmin = minimum date value in the setXNPV = XNPV functionXNFV = XNFV function
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.
@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.
@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.
@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.
{"columns":[{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MIRR":"0.555762831092004"}]}
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.
{"columns":[{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MIRR":"0.524881753702055"}]}
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.
{"columns":[{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MIRR":"0.524881753702055"}]}
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.XMIRR(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 1A .NET Framework error occurred during execution of user-defined routine or aggregate "XMIRR":Reinvest_rate cannot vary from its original value of 0.01
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.
{"columns":[{"field":"MIRR"}],"rows":[{"MIRR":"NULL"}]}
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.
{"columns":[{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"my_calc","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MIRR":"0.555762831092004","my_calc":"0.555762831092004"}]}
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,
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.
{"columns":[{"field":"Project"},{"field":"MIRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Project":"Project A","MIRR":"0.548223172908616"},{"Project":"Project B","MIRR":"0.184694776721734"},{"Project":"Project C","MIRR":"0.497476531222213"}]}