ORA-01428: argument '-33.333333333333333333333333333333333333' is out of range
01428. 00000 - "argument '%s' is out of range"
I m getting this error while running the following code:
-- 1) temporary table create:
create global temporary table eff_rate_tmp(dt date,summ number)
on commit preserve rows
/
grant delete on eff_rate_tmp to public
/
grant insert on eff_rate_tmp to public
/
grant select on eff_rate_tmp to public
/
grant update on eff_rate_tmp to public
/
-- 2) function ddl:
create or replace function xirr return number is
l_eff_rate number;
begin
select round((1/x-1),6) into l_eff_rate
from
(
select * from eff_rate_tmp
model
dimension by ( row_number() over (order by dt) rn )
measures
(
dt-first_value(dt) over (order by dt) dat,
summ s,
0 ss,
0 f_a,
0 f_b,
0 f_x,
0 a,
1 b,
0 x,
0 iter
)
rules iterate(100)
until (abs(f_x[1])< power(10,-6))
(
ss[any]=s[CV()]*power(a[1],dat[CV()]/365),
f_a[1]=sum(ss)[any],
ss[any]=s[CV()]*power(b[1],dat[CV()]/365),
f_b[1]=sum(ss)[any],
x[1]=a[1]-f_a[1]*(b[1]-a[1])/(f_b[1]-f_a[1]),
ss[any]=s[CV()]*power(x[1],dat[CV()]/365),
f_x[1]=sum(ss)[any],
a[1]=decode(sign(f_a[1]*f_x[1]),1,x[1],a[1]),
b[1]=decode(sign(f_a[1]*f_x[1]),1,b[1],x[1]),
iter[1]=iteration_number+1
)
)
where rn=1;
return l_eff_rate;
end;
/
-- 3) test data
insert into eff_rate_tmp (dt,summ) values( to_date('08.10.2013','dd.mm.yyyy'),450000)
/
insert into eff_rate_tmp (dt,summ) values( to_date('12.09.2013','dd.mm.yyyy'),-50000)
/
insert into eff_rate_tmp (dt,summ) values( to_date('01.01.2014','dd.mm.yyyy'),-52500)
/
insert into eff_rate_tmp (dt,summ) values( to_date('01.01.2014','dd.mm.yyyy'),-399000)
/
-- 4) xirr
SQL> select xirr from dual;
select xirr from dual
*
ERROR at line 1:
ORA-01428: argument '-33.333333333333333333333333333333333333' is out of range
ORA-06512: at "ORCL.XIRR", line 4