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)

-- Must work with both positive and negative inputs
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)

-- 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)

-- Different guesses
SELECT 0.1*i AS guess, xirr(amt, ts, 0.1*i) FROM (VALUES
    (-10000, '2009-01-01'::timestamptz),
    (-10000, '2010-01-01'),
    ( 10000, '2011-01-01'),
    ( 10000, '2012-01-01'),
    (  1000, '2013-01-01')
) x(amt, ts)
CROSS JOIN generate_series(-10, 10) i
GROUP BY i
ORDER BY i;
 guess |      xirr      
-------+----------------
  -1.0 |               
  -0.9 | 0.023844261812
  -0.8 | 0.023844261812
  -0.7 | 0.023844261812
  -0.6 | 0.023844261812
  -0.5 | 0.023844261812
  -0.4 | 0.023844261812
  -0.3 | 0.023844261812
  -0.2 | 0.023844261812
  -0.1 | 0.023844261812
   0.0 | 0.023844261812
   0.1 | 0.023844261812
   0.2 | 0.023844261812
   0.3 | 0.023844261812
   0.4 | 0.023844261812
   0.5 | 0.023844261812
   0.6 |               
   0.7 |               
   0.8 |               
   0.9 |               
   1.0 |               
(21 rows)