Source

pg_financial / test / expected / xirr.out

CREATE EXTENSION financial;
SET extra_float_digits = -4;
-- SET client_min_messages = 'debug';
SELECT xirr(amt, ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (110, '2013-02-01')) x(amt, ts);
     xirr     
--------------
 2.0716058535
(1 row)

SELECT xirr(amt, ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), ('Infinity', '2013-02-01')) x(amt, ts);
 xirr 
------
     
(1 row)

SELECT xirr(-100, '2013-01-01');
 xirr 
------
     
(1 row)

SELECT xirr(100, '2013-01-01');
 xirr 
------
     
(1 row)

SELECT xirr(NULL, '2013-01-01');
ERROR:  xirr amount input cannot be NULL
SELECT xirr(100, NULL);
ERROR:  xirr timestamp input cannot be NULL
CREATE TEMPORARY TABLE transactions AS
    SELECT -10.0::float8 amt, generate_series(timestamptz '2001-01-01', '2012-01-01', '1 day') ts
    UNION
    SELECT 12.0::float8, generate_series(timestamptz '2001-02-01', '2012-02-01', '1 day')
;
SELECT xirr(amt, ts ORDER BY ts) FROM transactions;
     xirr     
--------------
 7.5587924432
(1 row)

SELECT xirr(amt, ts) FROM (SELECT sum(amt) as amt, ts FROM transactions GROUP BY ts ORDER BY ts) subq;
     xirr     
--------------
 7.5587924432
(1 row)

SELECT xirr(amt, ts) FROM (SELECT amt, ts FROM transactions ORDER BY ts, random()) subq;
     xirr     
--------------
 7.5587924432
(1 row)