XIIR.sql

CREATE FUNCTION array_xirr(flows double precision[], force_period double precision) RETURNS double precision
  LANGUAGE plpgsql
AS
$$
DECLARE
  v FLOAT8;
  rate FLOAT8;
  firstday FLOAT8;
  period FLOAT8;
  exp FLOAT8;
  periods FLOAT8;
  npv FLOAT8;
  dnpv FLOAT8;
  iter INTEGER;
  n INTEGER;
  i INTEGER;
BEGIN
  --   RAISE NOTICE '%', flows;

  IF flows IS NULL
  THEN
    RETURN NULL;
  END IF;

  n = array_upper(flows, 1);
  IF n = 0
  THEN
    RETURN NULL;
  END IF;

  -- arrays are 1's based.
  firstday = flows [1] [1];
  period = flows [n] [1] - firstday;
  IF period > 365.25
  THEN
    period = 365.25;
  END IF;

  IF force_period IS NOT NULL AND force_period > 0
  THEN
    period = force_period;
  END IF;

  IF period <= 0
  THEN
    RETURN 0;
  END IF;
  --   RAISE NOTICE '%', period;

  rate = 0;

  FOR iter IN 1 .. 50
  LOOP
    -- compute npv and dnpv
    npv = 0;
    dnpv = 0;
    exp = rate + 1;
    IF exp <= 0
    THEN
      RETURN -1;
    END IF;
    FOR i IN 1 .. n
    LOOP
      periods = (flows [i] [1] - firstday) / period;
      v = flows [i] [2];
      npv = npv + v * power(exp, -periods);
      dnpv = dnpv - periods * v * power(exp, -periods - 1);
    END LOOP;
    --     RAISE NOTICE 'r=% i=% npv=% dnpv=%', rate, iter, npv, dnpv;
    IF npv < 0.0000001 AND npv > -0.0000001
    THEN
      RETURN rate;
    END IF;
    IF dnpv < 0.0000001 AND dnpv > -0.0000001
    THEN
      rate = rate + 1;
    ELSE
      rate = rate - (npv / dnpv);
    END IF;
  END LOOP;
  RETURN NULL;
END;
$$;