SQL Server modified internal rate of return for irregular perdiods

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.

·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":