XIRR_q
Updated 2023-10-09 13:09:33.123000
Syntax
SELECT [westclintech].[wct].[XIRR_q] (
<@CashFlows_RangeQuery, nvarchar(4000),>
,<@Guess, float,>)
Description
Use the scalar function XIRR_q to calculate the internal rate of return for a schedule of cash flows that is not necessarily periodic using dynamic SQL.
Arguments
@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.
@CashFlows_RangeQuery
the select statement, as text, used in determing the cash flows and cash flow dates to be used in this function.
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.
{"columns":[{"field":"proj_no","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"1","IRR":"1.47538054052834"},{"proj_no":"2","IRR":"5.78085888700177"},{"proj_no":"3","IRR":"2.11913826387648"}]}
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 table.
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
(SELECT DISTINCT proj_no from cf2) b;
Here is the result set.
{"columns":[{"field":"proj_no","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"1","IRR":"1.47538054052834"},{"proj_no":"2","IRR":"5.78085888700177"},{"proj_no":"3","IRR":"2.11913826387648"}]}
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.
{"columns":[{"field":"proj_no"},{"field":"IRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"proj_no":"A","IRR":"1.47538054052171"},{"proj_no":"B","IRR":"5.78085888688986"},{"proj_no":"C","IRR":"2.11913826386616"}]}
Here’s an example 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.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0701339951890285"}]}
This previous examples works, because it generates a properly formatted SQL Statement. So this statement
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
);
produces this result.
{"columns":[{"field":"column 1"}],"rows":[{"column 1":"SELECT -16654.47,'7/13/2009' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13/2010' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13/2011' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13/2012' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13/2013' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13/2014' UNION ALL"},{"column 1":"SELECT -5707.20,'07/13"}]}
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.