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)

-- Excel's default guess of 0.1 would fail here, but hey, we're smarter than that
SELECT xirr(amt, ts, null) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
 xirr 
------
 -0.8
(1 row)

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

SELECT xirr(amt, ts, -0.9999) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
 xirr 
------
 -0.8
(1 row)

-- Window function
SELECT xirr(amt, ts) OVER (ORDER BY ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (10, '2013-02-01'), (110, '2013-03-01')) x(amt, ts);
     xirr     
--------------
             
           -1
 2.2427441504
(3 rows)