Updated: 07 February 2011

Use XIRR to calculate an internal rate of return for a series of cash flows on different dates.

video: XIRR SQL Server 2008 Example

SELECT [westclintech].[wct].[XIRR] (

<@CF, float,>

,<@CFDate, datetime),>

,<@Guess, float,>)

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

the date on which the cash flow occurred. *@CFDate* is an expression of type **datetime** or of a type that can be implicitly converted to **datetime**.

a user-supplied suggestion as to a rate of return to use as a starting point in the iteration calculations. If you do not wish to supply a guess, make this argument NULL. *@Guess* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

Â· The XIRR aggregate function requires a series of cash flows (*@CF*) and the dates on which those cash flows occurred (*@CFDate*) as input. As a result, the order of the cash flows is not important.

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

Â· The guess (*@Guess*) must remain constant for the GROUP of the aggregate.

Â· XIRR and XNPV are related in that the XIRR function is solving for the value that makes the cash flows as sent to XNPV equal to zero.

Â· XIRR solves for XNPV approximately equal to zero, iteratively with a maximum of 100 iterations. If XIRR fails to resolve to zero within the maximum number of iterations, it will return a NULL.

Â· XIRR requires that there be at least one date with a negative cash flow and one date with a positive cash flow, otherwise it will return a NULL.

Â· If you want to calculate the internal rate of return using periods rather than dates, use the IRR function.

In this example we have cash flows occuring in sporadically from 30-Nov-2011 through to 06-Feb-2014.

SELECT wct.XIRR(cf_amt, cf_date, NULL) as IRR

FROM (VALUES

(-100000, '11/30/2011'),

(-50000, '3/15/2012'),

(-2500, '7/18/2012'),

(12500, '11/30/2012'),

(37500, '1/23/2013'),

(75000, '4/30/2013'),

(90000, '2/6/2014')

) n(cf_amt, cf_date)

This produces the following result.

IRR

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

0.249256702747074

(1 row(s) affected)

If we use the XIRR value in theX NPV function, it returns a value sufficiently close to zero out to 6 decimal places.

SELECT ROUND(wct.XNPV(0.249256702747074, cf_amt, cf_date), 6) as NPV

FROM (VALUES

(-100000, '11/30/2011'),

(-50000, '3/15/2012'),

(-2500, '7/18/2012'),

(12500, '11/30/2012'),

(37500, '1/23/2013'),

(75000, '4/30/2013'),

(90000, '2/6/2014')

) n(cf_amt, cf_date)

This produces the following result.

NPV

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

0

(1 row(s) affected)

If we wanted to calculate the XNPV using the XIRR calculated values, without having to copy the IRR calculated values into the SQL, the SQL is little more complicated, since AGGREGATE functions cannot contain other AGGREGATE functions. If we tried to do something like this:

SELECT ROUND(wct.XNPV(wct.XIRR(cf_amt, cf_date, NULL), cf_amt, cf_date), 6) as NPV

FROM (VALUES

(-100000, '11/30/2011'),

(-50000, '3/15/2012'),

(-2500, '7/18/2012'),

(12500, '11/30/2012'),

(37500, '1/23/2013'),

(75000, '4/30/2013'),

(90000, '2/6/2014')

) n(cf_amt, cf_date)

We will get the following error.

Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

So, we need to try something like this.

;with mycte as (

SELECT *

FROM (VALUES

(-100000, '11/30/2011'),

(-50000, '3/15/2012'),

(-2500, '7/18/2012'),

(12500, '11/30/2012'),

(37500, '1/23/2013'),

(75000, '4/30/2013'),

(90000, '2/6/2014')

) n(cf_amt, cf_date)

), mycte2(rate, cf_amt, cf_date) as (

SELECT wct.XIRR(cf_amt, cast(cf_date as datetime), NULL), 0, 0 from MYCTE

UNION ALL

SELECT NULL, cf_amt, cast(cf_date as datetime) FROM MYCTE

) SELECT m1.rate as IRR

,ROUND(wct.XNPV( m1.rate, m2.cf_amt,m2.cf_date), 6) as NPV

FROM mycte2 m1, mycte2 m2

WHERE m1.rate is NOT NULL and m2.rate is NULL

GROUP BY m1.rate

This produces the following result.

IRR NPV

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

0.249256702747074 0

(1 row(s) affected)

In this example, the dates are not stored, but we will convert stored month & year to the last day of the month and use the calculated value as an input into the function.

SELECT wct.XIRR(cf, wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0), NULL) as IRR

FROM (VALUES

(2011,1,-10000),

(2011,2,2000),

(2011,5,1500),

(2011,6,3000),

(2011,10,3800),

(2012,2,2500),

(2012,2,2500)

) n(yr, mth, cf)

This produces the following result.

IRR

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

0.991587447223907

(1 row(s) affected)

In this example, we have positive and negative cash flows, but there is no date with a net negative cash flow, so the result of the calculation is NULL.

SELECT wct.XIRR(cf_amt, cf_date, NULL) as IRR

FROM (VALUES

('01/01/2010',8039),

('01/01/2010',-8039),

('02/01/2010',9333),

('02/01/2010',-9333),

('03/01/2010',9783),

('03/01/2010',-9783),

('04/01/2010',8373),

('04/01/2010',-8373),

('05/01/2010',7493),

('05/01/2010',-7493),

('06/01/2010',9738),

('06/01/2010',-9738),

('07/01/2010',6382),

('07/01/2010',-6382),

('08/01/2010',8627),

('08/01/2010',-8627),

('09/01/2010',6871),

('09/01/2010',-6871),

('10/01/2010',9091),

('10/01/2010',-9091),

('11/01/2010',9153),

('11/01/2010',-9153),

('12/01/2010',7253),

('12/01/2010',-7253),

('01/01/2011',8197)

) n(cf_date, cf_amt)

This produces the following result.

IRR

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

NULL

(1 row(s) affected)

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

SELECT wct.XIRR(cf, wct.EOMONTH(wct.CALCDATE(yr, mth, 1), 0), guess) as IRR

FROM (VALUES

(2011,1,-10000, .20),

(2011,2,2000, .10),

(2011,5,1500, .10),

(2011,6,3000, .10),

(2011,10,3800, .10),

(2012,2,2500, -.10),

(2012,2,2500, -.20)

) n(yr, mth, cf, guess)

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

XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:

[Product version 1.7 Build: 0126.208]

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

Function: XIRR

Passed Parameters:

'CF' = System.Data.SqlTypes.SqlDouble:2000

'CFdate' = System.Data.SqlTypes.SqlDateTime:2/28/2011 12:00:00 AM

'Guess' = System.Data.SqlTypes.SqlDouble:0.1

Internal refs:

'pos_cf' = System.Double:0

'neg_cf' = System.Double:-10000

'guess' = System.Double:0.2

'count_x' = System.Int32:1

'cf' = XLeratorDB_financial2008.Core_Functions_Misc+AggArrayClass_Double:[AggArrayClass_Double]:count=1,bytes=8192,maxvalue=-10000,_minValue=-10000,BYTESIZE=8

'cfdate' = XLeratorDB_financial2008.Core_Functions_Misc+AggArrayClass_Int32:[AggArrayClass_Int32]:count=1,bytes=8192,maxvalue=40574,_minValue=40574,BYTESIZE=4

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

XLeratorDB_financial2008.Core.Exceptions+Function_AggregateException:

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

at XLeratorDB_financial2008.XIRR.Accumulate(SqlDouble CF, SqlDateTime CFdate, SqlDouble Guess)

.

XIRR is an AGGREGATE function and supports all the syntax available to any SQL Server AGGREGATE function. Here is an example using a GROUP BY.

SELECT port

,wct.XIRR(cf_amt, cf_date, -.10) as IRR

FROM(VALUES

('ABCD','07/27/2010',-4518),

('ABCD','10/21/2009',839),

('ABCD','05/01/2010',7676),

('ABCD','01/10/2010',-6610),

('ABCD','12/28/2009',-1646),

('ABCD','04/30/2010',7466),

('ABCD','06/23/2009',5665),

('ABCD','07/11/2009',-988),

('ABCD','03/12/2010',-5926),

('ABCD','08/12/2010',7457),

('ABCD','05/17/2010',-3598),

('ABCD','03/05/2010',-4448),

('ABCD','09/26/2009',-4204),

('ABCD','03/24/2010',-578),

('ABCD','05/22/2010',476),

('ABCD','12/24/2009',-1354),

('ABCD','03/20/2010',-679),

('ABCD','08/10/2009',-879),

('ABCD','04/04/2010',3229),

('ABCD','02/19/2009',-9061),

('ABCD','07/24/2010',8229),

('ABCD','10/31/2010',6110),

('ABCD','12/26/2009',-2248),

('ABCD','02/17/2009',2662),

('EFGH','01/24/2010',8786),

('EFGH','05/10/2010',-7380),

('EFGH','08/02/2009',2486),

('EFGH','11/18/2010',8777),

('EFGH','06/21/2010',-6877),

('EFGH','08/29/2010',-6250),

('EFGH','01/27/2010',-60),

('EFGH','12/20/2009',-1454),

('EFGH','06/29/2009',-2426),

('EFGH','11/14/2010',-4546),

('EFGH','01/06/2010',7072),

('EFGH','11/19/2010',-6746),

('EFGH','07/31/2010',6757),

('EFGH','11/16/2009',1938),

('EFGH','01/09/2009',-5383),

('EFGH','09/26/2010',6758),

('EFGH','12/01/2010',1842),

('EFGH','02/01/2009',-1188),

('EFGH','01/05/2009',4002),

('EFGH','11/06/2010',-7839),

('EFGH','07/24/2009',2039),

('EFGH','03/13/2010',-1934),

('EFGH','08/30/2010',-2541),

('EFGH','06/11/2010',9104),

('IJKL','02/05/2009',6339),

('IJKL','08/25/2009',-7640),

('IJKL','02/06/2009',-2503),

('IJKL','12/06/2009',-4209),

('IJKL','12/11/2010',-4945),

('IJKL','01/03/2010',7654),

('IJKL','08/08/2009',-8400),

('IJKL','02/12/2009',9909),

('IJKL','01/15/2009',-4827),

('IJKL','09/01/2010',3839),

('IJKL','05/26/2009',-7535),

('IJKL','04/05/2009',6875),

('IJKL','06/08/2010',-5919),

('IJKL','03/23/2010',-140),

('IJKL','02/23/2010',-4690),

('IJKL','04/07/2009',-6383),

('IJKL','12/21/2009',2781),

('IJKL','07/09/2010',3034),

('IJKL','11/03/2009',7370),

('IJKL','09/25/2010',-3765),

('IJKL','12/14/2009',7364),

('IJKL','06/27/2009',8778),

('IJKL','04/27/2009',-6416),

('IJKL','11/10/2010',-9891)

) n(port, cf_date, cf_amt)

GROUP BY port

This produces the following result.

port IRR

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

ABCD 0.186749212764617

EFGH -0.519057898838459

IJKL 2.81177268966162

(3 row(s) affected)