create or replace type TXirrAmtDt as object( amount number, dt date)
/

Type created

create type TXirrAmtDtTab as table of TXirrAmtDt
/

Type created

create or replace type TXirrImpl as object
( firstDate date,
  xirrValues TXirrAmtDtTab,
  static function ODCIAggregateInitialize(sctx IN OUT TXirrImpl)return number,
  member function ODCIAggregateIterate(self IN OUT TXirrImpl, value IN TXirrAmtDt) return number,
  member function ODCIAggregateTerminate(self IN TXirrImpl, returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT TXirrImpl, ctx2 IN TXirrImpl) return number
)
/

Type created

create or replace type body TXirrImpl is
static function ODCIAggregateInitialize(sctx IN OUT TXirrImpl)
return number is
begin
  sctx := TXirrImpl(date'3000-01-01',TXirrAmtDtTab());
  return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT TXirrImpl, value IN TXirrAmtDt) return number is
begin
	self.xirrValues.extend;
	self.xirrValues(self.xirrValues.count) := value;
	firstDate := least(value.dt, firstDate);
  return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN TXirrImpl, returnValue OUT number, flags IN number) return number is
  z          number := 0;
  step_limit number := 0;
  temp       number;
  step       number := 0.001;
  d          number := 0.5;
begin
  loop
    temp := self.xirrValues(1).amount;
    for i in 2 .. self.xirrValues.count loop
      temp := temp +
              self.xirrValues(i).amount /
              power(1 + d, (self.xirrValues(i).dt - firstDate) / 365);
    end loop;
    if temp > 0 and z = 0 then
      step := step / 2;
      z    := 1;
    end if;
    if temp < 0 and z = 1 then
      step := step / 2;
      z    := 0;
    end if;
    exit when round(temp * 100000) = 0 or step_limit = 100000;
    if z = 0 then
      d := d - step;
    else
      d := d + step;
    end if;
    step_limit := step_limit + 1;
  end loop;
  returnValue := round(d, 6);
  return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT TXirrImpl, ctx2 IN TXirrImpl) return number is
begin
	self.xirrValues.extend(ctx2.xirrValues.count);
  for i in 1..ctx2.xirrValues.count loop
		self.xirrValues(self.xirrValues.count) := ctx2.xirrValues(i);
  end loop;
  return ODCIConst.Success;
end;
end;
/

Type body created

CREATE OR replace FUNCTION xirr (input TXirrAmtDt) RETURN NUMBER AGGREGATE USING TXirrImpl;
/

Function created

with t as (  select 'Ex1' exNo, to_date('22.12.2021','dd.mm.yyyy') dt, -100 summ from dual union all
             select 'Ex1', to_date('31.12.2021','dd.mm.yyyy') dt, 101 summ from dual union all
             select 'Ex2', to_date('03.11.2020','dd.mm.yyyy') dt, -103860 summ from dual 
             union all
             select 'Ex2', to_date('25.12.2020','dd.mm.yyyy') dt, -85470 summ from dual union all
             select 'Ex2', to_date('13.01.2021','dd.mm.yyyy') dt, -76067 summ from dual union all
             select 'Ex2', to_date('09.02.2021','dd.mm.yyyy') dt, -299049 summ from dual union all
             select 'Ex2', to_date('30.07.2021','dd.mm.yyyy') dt, 21.5 summ from dual union all
             select 'Ex2', to_date('31.07.2021','dd.mm.yyyy') dt, 23.9 summ from dual union all
             select 'Ex2', to_date('16.12.2021','dd.mm.yyyy') dt, 476370 summ from dual
             union all
             select 'Ex3', to_date('31.12.2018','dd.mm.yyyy') dt, -1510127687 summ from dual union all
             select 'Ex3', to_date('07.01.2019','dd.mm.yyyy') dt, -127896811 summ from dual union all
             select 'Ex3', to_date('31.01.2019','dd.mm.yyyy') dt, 49587359.44 summ from dual union all
             select 'Ex3', to_date('01.02.2019','dd.mm.yyyy') dt, -61323424.81 summ from dual union all
             select 'Ex3', to_date('25.02.2019','dd.mm.yyyy') dt, -36562446.39 summ from dual union all
             select 'Ex3', to_date('26.02.2019','dd.mm.yyyy') dt, -35316612.63 summ from dual union all
             select 'Ex3', to_date('15.04.2019','dd.mm.yyyy') dt, -36540049.58 summ from dual union all
             select 'Ex3', to_date('23.04.2019','dd.mm.yyyy') dt, -6895856.787 summ from dual union all
             select 'Ex3', to_date('29.04.2019','dd.mm.yyyy') dt, -51712892.95 summ from dual union all
             select 'Ex3', to_date('09.05.2019','dd.mm.yyyy') dt, 51869877.05 summ from dual union all
             select 'Ex3', to_date('10.05.2019','dd.mm.yyyy') dt, 27445324.92 summ from dual union all
             select 'Ex3', to_date('13.05.2019','dd.mm.yyyy') dt, 45919585.24 summ from dual union all
             select 'Ex3', to_date('15.05.2019','dd.mm.yyyy') dt, 37121868.33 summ from dual union all
             select 'Ex3', to_date('23.05.2019','dd.mm.yyyy') dt, 77442350.67 summ from dual union all
             select 'Ex3', to_date('13.06.2019','dd.mm.yyyy') dt, -173754425.9 summ from dual union all
             select 'Ex3', to_date('27.06.2019','dd.mm.yyyy') dt, -42441539.49 summ from dual union all
             select 'Ex3', to_date('11.07.2019','dd.mm.yyyy') dt, 44388616.31 summ from dual union all
             select 'Ex3', to_date('19.07.2019','dd.mm.yyyy') dt, -17468777.79 summ from dual union all
             select 'Ex3', to_date('17.09.2019','dd.mm.yyyy') dt, 84595194.25 summ from dual             )
select exNo, xirr(TXirrAmtDt(summ,dt)) xirr_value
  from t
 group by exNo
;

EXNO XIRR_VALUE
---- ----------
Ex1    0.497117
Ex2   -0.167186
Ex3   -0.990952

SQL>