Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server 2005 internal rate of return function for irregular cash flows


 
Updated: 5 August 2010

Use XIRR_q to calculate the internal rate of return for a schedule of cash flows that is not necessarily periodic. Use this function instead of XIRR when you want to join multiple tables or views in a single SELECT statement.
Syntax
SELECT [westclintech].[wct].[XIRR_q] (
   <@CashFlows_RangeQuery, nvarchar(4000),>
 ,<@Guess, float,>)
Arguments
@CashFlows_RangeQuery
the select statement, as text, used in determing the cash flows and cash flow dates to be used in this function.
@Guess
the user-supplied initial guess used in the first iteration of the internal rate of return calculation. @Guess is an expression of type float or of a type that can be implicitly converted to float or is NULL.
 
Return Type
float
Remarks
·         XNPV is related to the XIRR_q function in that XIRR_q is the value which when used to discount the cash flows returns an XNPV of zero.
·         Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.
·         XIRR_q requires that there be at least one negative and one positive cash flow, otherwise it will return a NULL value.
Examples
 
Create a table to store cash flow projections, by date, for a variety of projects:
CREATE TABLE [dbo].[cf2](
      [proj_no] [float] NOT NULL,
      [cf_date] [datetime] NOT NULL,
      [cf_amt] [float] NOT NULL,
 CONSTRAINT [PK_cf2] PRIMARY KEY CLUSTERED
(
      [proj_no] ASC,
      [cf_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert the data for three projects into the table.


INSERT INTO cf2 VALUES (1,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (1,'01/31/2009',5000)
INSERT INTO cf2 VALUES (1,'06/01/2009',10000)
INSERT INTO cf2 VALUES (1,'10/30/2009',15000)
INSERT INTO cf2 VALUES (1,'04/29/2010',20000)
INSERT INTO cf2 VALUES (1,'11/26/2010',25000)
INSERT INTO cf2 VALUES (2,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (2,'01/31/2009',25000)
INSERT INTO cf2 VALUES (2,'06/01/2009',20000)
INSERT INTO cf2 VALUES (2,'10/30/2009',15000)
INSERT INTO cf2 VALUES (2,'04/29/2010',10000)
INSERT INTO cf2 VALUES (2,'11/26/2010',5000)
INSERT INTO cf2 VALUES (3,'11/01/2008',-25000)
INSERT INTO cf2 VALUES (3,'01/31/2009',5000)
INSERT INTO cf2 VALUES (3,'06/01/2009',25000)
INSERT INTO cf2 VALUES (3,'10/30/2009',10000)
INSERT INTO cf2 VALUES (3,'04/29/2010',20000)
INSERT INTO cf2 VALUES (3,'11/26/2010',15000)
 
Enter a SELECT statement to calculate XIRR for the 3 projects so as to compare the results
select b.proj_no,
wct.XIRR_q
(
      'SELECT a.cf_amt
      ,a.cf_date
      from cf2 a
      where a.proj_no = ' + convert(char, b.proj_no),
      NULL
) as IRR
from cf2 b
group by b.proj_no

Here is the result set
proj_no                IRR
---------------------- ----------------------
1                      1.47538054052834
2                      5.78085888700177
3                      2.11913826387648
 
(3 row(s) affected)

Since there are many rows for each proj_no in the temporary table, we needed to use a GROUP BY to get one row for each proj_no. We can eliminate the GROUP BY by using a derived table to select DISTINCT proj_no from the temporary table.
 
SELECT b.proj_no,
wct.XIRR_q
(
      'SELECT a.cf_amt
      ,a.cf_date
      FROM #cf a
      WHERE a.proj_no = ' + convert(char, b.proj_no),
      NULL
) as IRR
FROM (SELECT DISTINCT proj_no from #cf) b

Here is the result set
proj_no                IRR
---------------------- ----------------------
1                      1.47538054052834
2                      5.78085888700177
3                      2.11913826387648
 
(3 row(s) affected)

This will be more efficient, as the XIRR_q function will only be invoked once for each proj_no.
If the where clause is going to join on a non-numeric column value, make sure that the value is enclosed in single quotes. Let’s change the definition of our table, and create the appropriate query.
CREATE TABLE #cf(
      proj_no           varchar(10),
      cf_date           datetime,
      cf_amt            float,
CONSTRAINT [PK_#cf] PRIMARY KEY CLUSTERED
(
      proj_no ASC,
      cf_date ASC
)
)
 
INSERT INTO #cf VALUES ('A','11/01/2008',-25000)
INSERT INTO #cf VALUES ('A','01/31/2009',5000)
INSERT INTO #cf VALUES ('A','06/01/2009',10000)
INSERT INTO #cf VALUES ('A','10/30/2009',15000)
INSERT INTO #cf VALUES ('A','04/29/2010',20000)
INSERT INTO #cf VALUES ('A','11/26/2010',25000)
INSERT INTO #cf VALUES ('B','11/01/2008',-25000)
INSERT INTO #cf VALUES ('B','01/31/2009',25000)
INSERT INTO #cf VALUES ('B','06/01/2009',20000)
INSERT INTO #cf VALUES ('B','10/30/2009',15000)
INSERT INTO #cf VALUES ('B','04/29/2010',10000)
INSERT INTO #cf VALUES ('B','11/26/2010',5000)
INSERT INTO #cf VALUES ('C','11/01/2008',-25000)
INSERT INTO #cf VALUES ('C','01/31/2009',5000)
INSERT INTO #cf VALUES ('C','06/01/2009',25000)
INSERT INTO #cf VALUES ('C','10/30/2009',10000)
INSERT INTO #cf VALUES ('C','04/29/2010',20000)
INSERT INTO #cf VALUES ('C','11/26/2010',15000)
 
SELECT b.proj_no,
wct.XIRR_q
(
      'SELECT a.cf_amt
      ,a.cf_date
      FROM #cf a
      WHERE a.proj_no = ' + char(39) + convert(char, b.proj_no) + Char(39),
      NULL
) as IRR
FROM (SELECT DISTINCT proj_no from #cf) b

Here is the result set
proj_no    IRR
---------- ----------------------
A          1.47538054052171
B          5.78085888688986
C          2.11913826386616
 
(3 row(s) affected)

Here’s an exmple where we enter the date and cash flow values directly into the function.
SELECT wct.XIRR_q(REPLACE('SELECT -16654.47,''7/13/2009''
-5707.20,''07/13/2010''
-5707.20,''07/13/2011''
-5707.20,''07/13/2012''
-5707.20,''07/13/2013''
-5707.20,''07/13/2014''
-5707.20,''07/13/2015''
-5707.20,''07/13/2016''
-475.60,''08/13/2016''
77200,''08/19/2016''', CHAR(13) + CHAR(10), ' UNION ALL' + CHAR(10) + 'SELECT ' )
,NULL)

Here is the result set
----------------------
0.0701339951890285
 
(1 row(s) affected)

This previous examples works, because it generates a properly formatted SQL Statement. So this statement
SELECT REPLACE('SELECT -16654.47,''7/13/2009''
-5707.20,''07/13/2010''
-5707.20,''07/13/2011''
-5707.20,''07/13/2012''
-5707.20,''07/13/2013''
-5707.20,''07/13/2014''
-5707.20,''07/13/2015''
-5707.20,''07/13/2016''
-475.60,''08/13/2016''
77200,''08/19/2016''', CHAR(13) + CHAR(10), ' UNION ALL' + CHAR(10) + 'SELECT ' )

produces this result


SELECT -16654.47,'7/13/2009' UNION ALL
SELECT -5707.20,'07/13/2010' UNION ALL
SELECT -5707.20,'07/13/2011' UNION ALL
SELECT -5707.20,'07/13/2012' UNION ALL
SELECT -5707.20,'07/13/2013' UNION ALL
SELECT -5707.20,'07/13/2014' UNION ALL
SELECT -5707.20,'07/13/2015' UNION ALL
SELECT -5707.20,'07/13/2016' UNION ALL
SELECT -475.60,'08/13/2016' UNION ALL
SELECT 77200,'08/19/2016'

which is then processed by the query. We could have also entered


SELECT wct.XIRR_q(
'SELECT -16654.47,''7/13/2009'' UNION ALL
SELECT -5707.20,''07/13/2010'' UNION ALL
SELECT -5707.20,''07/13/2011'' UNION ALL
SELECT -5707.20,''07/13/2012'' UNION ALL
SELECT -5707.20,''07/13/2013'' UNION ALL
SELECT -5707.20,''07/13/2014'' UNION ALL
SELECT -5707.20,''07/13/2015'' UNION ALL
SELECT -5707.20,''07/13/2016'' UNION ALL
SELECT -475.60,''08/13/2016'' UNION ALL
SELECT 77200,''08/19/2016''', NULL)

which produces exactly the same result.

 

See Also 

·         XIRR.

·         Read blog posting on XIRR.

·         Read a blog or view a video on a problem with Excel's XIRR function.

 

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service