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.