ReSQL.ru
     
powered by simpleCommunicator - 2.0.30     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / XIRR function in PL/SQL ?
34 сообщений из 34, показаны все 2 страниц
  все  
XIRR function in PL/SQL ?
    #34560654
Valeriy Borysyuk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valeriy Borysyuk
Гость
Привет всем!

Поделитесь пожалуйста процедурой вычисления XIRR на PL/SQL, если есть у кого-то
...
Рейтинг: 0 / 0
30.05.2007, 12:09
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34695465
Serg Kravchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serg Kravchenko
Гость
Добрый день.

Аналогичная история - ищу реализацию данной функции.
Вам удалось найти?

Если да - поделитесь, плиз, на zchermit@mail.ru...
...
Рейтинг: 0 / 0
31.07.2007, 13:51
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34695572
Valeriy Borysyuk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valeriy Borysyuk
Гость
На сипане смотри реализацию на перле. Там есть все исходники с комментариями. Эта функция там IRR называется, но на самом деле она XIRR. Я пока не буду делать ее на PL/SQL тк уже сменил работу и надобность отпала (надолго -ли...).

http://search.cpan.org/~erwan/Finance-Math-IRR-0.10/lib/Finance/Math/IRR.pm

Очень хорошо прокомментирован исходник. Придецца сначала освоить методы решения полиномов.
...
Рейтинг: 0 / 0
31.07.2007, 14:17
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34697010
ЭСП
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ЭСП
Гость
У меня тоже проблема - нужно доходность к погашению и амортизацию скидок-премий
по ценным бумагам вычислять по эффективной ставке процента (IRR).
И всё-таки, кто то может уже делал такую ф-ю.
Поделитесь плиз.
...
Рейтинг: 0 / 0
01.08.2007, 02:04
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34698003
Volder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volder
Гость
ЭСПИ всё-таки, кто то может уже делал такую ф-ю.
Поделитесь плиз.
ставьте задачу конкретную (с данными, алгоритмами, желаемыми результатами).
Думаю простого SQL достаточно будет.
...
Рейтинг: 0 / 0
01.08.2007, 13:53
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34699870
ЭСП
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ЭСП
Гость
Нет, для IRR просто SQL не достаточно.
Кто это делал, тот знает. Тот и может подсказать.
А я в полиномах не шарю.
...
Рейтинг: 0 / 0
02.08.2007, 04:28
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34700127
Volder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volder
Гость
ЭСПНет, для IRR просто SQL не достаточно.ну почему же недостаточно...

на 10-ке в лоб - метод подбора на простом примере с некоторыми допущениями (про perfomance речь не идет):

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SQL> with t as (select to_date('01.01.2007','dd.mm.yyyy') dt, - 100  summ from dual union all
   2              select to_date('01.07.2007','dd.mm.yyyy') dt,  58  summ from dual union all
   3              select to_date('01.01.2008','dd.mm.yyyy') dt,  60  summ from dual)
   4             --
   5              select round(irr* 100 , 2 )||' %' эффективная_ставка from
   6              (select * from t
   7               model
   8                dimension by (row_number() over (order by dt) rn)
   9                measures(dt-first_value(dt) over (order by dt) dt, summ s,  0  ss,  0  irr, power( 10 ,- 4 ) rate_step,  0  disc_summ)
  10                rules iterate( 100000 ) until (s[ 1 ]+disc_summ[ 1 ]< 0 )
  11                (ss[any]=s[CV()]/power( 1 +IRR[ 1 ],dt[CV()]/ 365 ),
  12                 disc_summ[ 1 ]=sum(ss)[rn> 1 ],
  13                 irr[ 1 ]=irr[ 1 ]+rate_step[ 1 ])
  14              )
  15              where rn= 1 
  16   /

ЭФФЕКТИВНАЯ_СТАВКА
------------------------------------------
 24 . 89  %

SQL>
...
Рейтинг: 0 / 0
02.08.2007, 09:59
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34747308
Volder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volder
Гость
тут коллеги тоже занимались эффективной процентной ставкой.
решил более пристально глянуть на этот вопрос.
что получилось - запостил в этой ветке - если кому-то потом интересно будет.

раньше ставку получал простым перебором, сейчас алгоритм поменял.
результат:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
SQL> set timing on;
SQL> 
SQL>  with t as (select to_date('01.01.2007','dd.mm.yyyy') dt, - 100  summ from dual union all
   2               select to_date('01.07.2007','dd.mm.yyyy') dt,  58  summ from dual union all
   3               select to_date('01.01.2008','dd.mm.yyyy') dt,  60  summ from dual)
   4              --
   5               select to_char((irr-rate_step)* 100 ,'999999.0000000')||' %' эффективная_ставка, iter количество_итераций from
   6               (select * from t
   7                model
   8                 dimension by (row_number() over (order by dt) rn)
   9                 measures(dt-first_value(dt) over (order by dt) dt, summ s,  0  ss,  0  irr, power( 10 ,- 6 ) rate_step,  0  disc_summ,  0  iter)
  10                 rules iterate( 10000000 ) until (disc_summ[ 1 ]< 0 )
  11                 (ss[any]=s[CV()]/power( 1 +IRR[ 1 ],dt[CV()]/ 365 ),
  12                  disc_summ[ 1 ]=sum(ss)[any],
  13                  irr[ 1 ]=irr[ 1 ]+rate_step[ 1 ],
  14                  iter[ 1 ]=iteration_number+ 1 )
  15               )
  16               where rn= 1 
  17   /

ЭФФЕКТИВНАЯ_СТАВКА КОЛИЧЕСТВО_ИТЕРАЦИЙ
------------------ -------------------
      24 . 8720000  %                248721 

Executed in  29 . 422  seconds

SQL> 
SQL>  with t as (select to_date('01.01.2007','dd.mm.yyyy') dt, - 100  summ from dual union all
   2               select to_date('01.07.2007','dd.mm.yyyy') dt,  58  summ from dual union all
   3               select to_date('01.01.2008','dd.mm.yyyy') dt,  60  summ from dual)
   4               --
   5            select to_char(irr* 100 ,'999999.0000000000000')||' %' эффективная_ставка, iter количество_итераций from
   6               (select * from t
   7                model
   8                 dimension by (row_number() over (order by dt) rn)
   9                 measures(dt-first_value(dt) over (order by dt) dt, summ s,  0  ss,  1  disc_summ,  0  irr,  1  interv/*100%*/,  0  iter)
  10                 rules iterate( 100 ) until (abs(interv[ 1 ])<power( 10 ,- 6 ))
  11                       (ss[any]=s[CV()]/power( 1 +IRR[ 1 ],dt[CV()]/ 365 ),
  12                       irr[ 1 ] = decode(sign(disc_summ[ 1 ]),sign(sum(ss)[any]),irr[ 1 ]+sign(disc_summ[ 1 ])*interv[ 1 ],irr[ 1 ]-sign(disc_summ[ 1 ])*interv[ 1 ]/ 2 ),
  13                       interv[ 1 ]= decode(sign(disc_summ[ 1 ]),sign(sum(ss)[any]),interv[ 1 ],interv[ 1 ]/ 2 ),
  14                       disc_summ[ 1 ]=sum(ss)[any],
  15                       iter[ 1 ]=iteration_number+ 1 
  16                       )
  17                )
  18                where rn= 1 
  19   /

ЭФФЕКТИВНАЯ_СТАВКА      КОЛИЧЕСТВО_ИТЕРАЦИЙ
----------------------- -------------------
      24 . 8719215393066  %                   29 

Executed in  0 . 172  seconds

SQL>

думаю, выгода такого подхода очевидна.
теперь немножко подробней:

алгоритм простой. Можно его назвать метод "сужающихся интервалов" (наверняка в математике что-то такое есть уже, умные коллеги подсказывают - "метод хорд", "метод ньютона"). Но т.к. память сумбурно отреагировала на слово "полином" - про способы их решения вообще отклика не последовало. То что в голову пришло - то и реализовал.

все оч просто. берем первоначальную ставку (IRR=0%), первоначальный интервал (interv=100%) - по сути как rate_step в первом варианте.
Далее считаем сумму дисконтированных денежных потоков к начальной дате. При ставке 0% она будет положительна, по мере увеличения эфф ставки - сумма будет уменьшается. В тот момент когда сумма станет =0 - нас и интересует значение эфф ставки.

первый шаг - прибавляем интервал. IRR стала 100% считаем сумму дисконтированных потоков. Если сумма сменила знак - то перебор. Мы уменьшаем интервал вдвое (до 50%) и делаем шаг в обратную сторону на интервал. Если при первом шаге - сумма потоков не изменила знак - то мы интервал не меняем (100%) и двигаемся в ту же сторону (IRR=200% на следующем шаге).
В итоге мы постепенно уменьшая интервал (шаг) будем прыгать вокруг нуля (то в +, то в -). пока необходимая точность не будет достигнута.

PS
У себя наложил условие
Код: plaintext
until (abs(interv[ 1 ])<power( 10 ,- 6 ))
т.е. точность эфф ставки задаем как 0.000001.
Можно задать точность на суммы потоков - например, если нам нужно посчитать эфф ставку, чтобы она давала точность сумм до копеек, можно сделать что-то вроде:
Код: plaintext
until (abs(disc_summ[ 1 ])< 0 . 01 )

надеюсь, кому-то пригодится.
...
Рейтинг: 0 / 0
23.08.2007, 12:34
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34748264
Volder
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Volder
Гость
по "методу хорд" нужно еще меньшее количество итераций для той же точности:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
SQL>  with t as (select to_date('01.01.2007','dd.mm.yyyy') dt, - 100  summ from dual union all
   2               select to_date('01.07.2007','dd.mm.yyyy') dt,  58  summ from dual union all
   3               select to_date('01.01.2008','dd.mm.yyyy') dt,  60  summ from dual)
   4                --
   5             select to_char(irr* 100 ,'999999.0000000000000')||' %' эффективная_ставка, iter количество_итераций from
   6                (select * from t
   7                 model
   8                  dimension by (row_number() over (order by dt) rn)
   9                  measures(dt-first_value(dt) over (order by dt) dt, summ s,  0  ss,  1  disc_summ,  0  irr,  1  interv/*100%*/,  0  iter)
  10                  rules iterate( 100 ) until (abs(disc_summ[ 1 ])<power( 10 ,- 6 ))
  11                        (ss[any]=s[CV()]/power( 1 +IRR[ 1 ],dt[CV()]/ 365 ),
  12                        irr[ 1 ] = decode(sign(disc_summ[ 1 ]),sign(sum(ss)[any]),irr[ 1 ]+sign(disc_summ[ 1 ])*interv[ 1 ],irr[ 1 ]-sign(disc_summ[ 1 ])*interv[ 1 ]/ 2 ),
  13                        interv[ 1 ]= decode(sign(disc_summ[ 1 ]),sign(sum(ss)[any]),interv[ 1 ],interv[ 1 ]/ 2 ),
  14                        disc_summ[ 1 ]=sum(ss)[any],
  15                        iter[ 1 ]=iteration_number+ 1 
  16                        )
  17                 )
  18                 where rn= 1 
  19   /

ЭФФЕКТИВНАЯ_СТАВКА      КОЛИЧЕСТВО_ИТЕРАЦИЙ
----------------------- -------------------
      24 . 8719394207001  %                   35 

Executed in  0 . 188  seconds

SQL> 
SQL>  with t as (select to_date('01.01.2007','dd.mm.yyyy') dt, - 100  summ from dual union all
   2               select to_date('01.07.2007','dd.mm.yyyy') dt,  58  summ from dual union all
   3               select to_date('01.01.2008','dd.mm.yyyy') dt,  60  summ from dual)
   4               --
   5            select to_char(( 1 /x- 1 )* 100 ,'999999.0000000000000')||' %' эффективная_ставка, iter количество_итераций from
   6               (select * from t
   7                model
   8                 dimension by (row_number() over (order by dt) rn)
   9                 measures(dt-first_value(dt) over (order by dt) dt, summ s,  0  ss,  0  f_a,  0  f_b,  0  f_x,  0  a,  1  b,  0  x,  0  iter)
  10                 rules iterate( 100 ) until (abs(f_x[ 1 ])< power( 10 ,- 6 ))
  11                       (ss[any]=s[CV()]*power(a[ 1 ],dt[CV()]/ 365 ),
  12                        f_a[ 1 ]=sum(ss)[any],
  13                        ss[any]=s[CV()]*power(b[ 1 ],dt[CV()]/ 365 ),
  14                        f_b[ 1 ]=sum(ss)[any],
  15                        x[ 1 ]=a[ 1 ]-f_a[ 1 ]*(b[ 1 ]-a[ 1 ])/(f_b[ 1 ]-f_a[ 1 ]),
  16                        ss[any]=s[CV()]*power(x[ 1 ],dt[CV()]/ 365 ),
  17                        f_x[ 1 ]=sum(ss)[any],
  18                        a[ 1 ]=decode(sign(f_a[ 1 ]*f_x[ 1 ]), 1 ,x[ 1 ],a[ 1 ]),
  19                        b[ 1 ]=decode(sign(f_a[ 1 ]*f_x[ 1 ]), 1 ,b[ 1 ],x[ 1 ]),
  20                        iter[ 1 ]=iteration_number+ 1 
  21                       )
  22                )
  23                where rn= 1 
  24   /

ЭФФЕКТИВНАЯ_СТАВКА      КОЛИЧЕСТВО_ИТЕРАЦИЙ
----------------------- -------------------
      24 . 8719328405865  %                   13 

Executed in  0 . 047  seconds

SQL>

PS последний пример писал "в лоб" по алгоритму
можно упростить, чтобы на след итерации не обсчитывать те же суммы заново, которые посчитаны на предыдущем шаге.
...
Рейтинг: 0 / 0
23.08.2007, 15:36
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34749527
ЭСП
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ЭСП
Гость
Да, решение интересное, но вот у нас база 9.2, а в ней model нет.
За решение большое спасибо!!!
...
Рейтинг: 0 / 0
24.08.2007, 05:07
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #34770147
Serg Kravchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Serg Kravchenko
Гость
Вот что удалось "набредить" мне для 9-ки. С функцией ЧИСТВНДОХ (она же XIRR) вроде как совпадает.
Сильно не пинать. В PL/SQL слабоват :(

-- Массив дат
type t_date_array is varray(200000) of date;
-- Массив потоков
type t_amount_array is varray(200000) of number;


function Xirr(p_date_array in t_date_array,
p_amount_array in t_amount_array,
p_guess in number default 0

) return number is
z number := 0;
step_limit number := 0;
temp number;
step number := 0.1;
d number := 0.5;
l_MaxDate date;
l_MinDate date;
srok number;
begin

l_MaxDate := p_date_array(1);
l_MinDate := p_date_array(1);

-- Первый проход: поиск макс. даты и наличия хотя бы одного минуса и плюса в потоках
for i in 1 .. p_date_array.count loop
if p_date_array(i) > l_MaxDate then
l_MaxDate := p_date_array(i);
end if;
if p_date_array(i) < l_MinDate then
l_MinDate := p_date_array(i);
end if;
end loop;

select months_between(l_MaxDate, l_MinDate) into srok from dual;

loop

temp := p_amount_array(1);


for i in 2 .. p_amount_array.count loop
temp := temp +
p_amount_array(i) /
power((1 + d), (p_date_array(i) - p_date_array(1)) / 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;

if (z = 0) then
d := d - step;
else
d := d + step;
end if;

step_limit := step_limit + 1;

exit when((round(temp * 100000) = 0) or (step_limit = 10000));

end loop;

return d;

end Xirr;
...
Рейтинг: 0 / 0
02.09.2007, 11:32
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #35327710
Viatcheslav_A
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Viatcheslav_A
Участник
Serg Kravchenko
if (z = 0) then
d := d - step;
else
d := d + step;
end if;

Вроде как знаки поменять надо?!
...
Рейтинг: 0 / 0
22.05.2008, 11:03
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
XIRR function in PL/SQL ?
    #37228790
Владимир П
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир П
Гость
Подскажите а как можно добиться результата работы Excel-евской ВСД функции?
по значениям -100, 58, 60 она выдвет 12%?
я в этом вообще не бум бум, а есть задача получить значения = ВСД*12!
...
Рейтинг: 0 / 0
22.04.2011, 14:37
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37228923
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
Владимир П,

stfw всд
...
Рейтинг: 0 / 0
22.04.2011, 15:14
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37388605
Шелухин Алексей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Шелухин Алексей
Гость
Volder, ваше решение очень красивое.
Как я понял в вашем примере берется только 3 даты с суммами.
Возможно ли сделать так, чтобы брался весь промежуток дат платежа?
Я имею ввиду 1 платеж в месяц.
К примеру первый платеж 16/06/2011 200000 , а последний 13/06/2014 5916.36?
Спасибо.
...
Рейтинг: 0 / 0
10.08.2011, 11:22
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37390161
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Шелухин АлексейГробокопателям - http://www.bugtraq.ru/forum/faq/general/smart-questions.html] RTFM
...
Рейтинг: 0 / 0
11.08.2011, 00:02
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37438807
RSA86
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RSA86
Гость
Всем привет. Возникла задача рассчитать XIRR на Oracle. Воспользовался скриптом который был приведен Volder. За что ему отдельное СПАСИБО. Однако вот не задачка. Почему то данные которые я получаю в excel при расчётё отличаются от расчётов в oracle, хотя исходные данные одинаковые. Помогите плиз. А то всю голову сломал, чтобы понять что не так. Вот данные которые я получаю в oracle:
...
Рейтинг: 0 / 0
13.09.2011, 18:59
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37438813
RSA86
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RSA86
Гость
А вот что в excel:

Данные отличаются. Это только один пример из нескольких. Почему такая разница в результатах? Плиз помогите!!!
...
Рейтинг: 0 / 0
13.09.2011, 19:01
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37438821
RSA86
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RSA86
Гость
Получается вот такая разница:
oracle - 4.74%
excel - 4.82%
Почему?
...
Рейтинг: 0 / 0
13.09.2011, 19:03
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37438827
RSA86
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RSA86
Гость
А вот формула которая используется в excel =ЧИСТВНДОХ(K2:K5;C2:C5;0,1)
...
Рейтинг: 0 / 0
13.09.2011, 19:06
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37438893
Фотография orawish
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orawish
Участник
RSA86,

знак? наверное на сходимость надо проверять не модуль суммы, а сумму модулей
...
Рейтинг: 0 / 0
13.09.2011, 19:47
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #37439485
Фотография samatom
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
samatom
Участник
RSA86,


Не совсем по теме, точнее, рассуждения около неё:
1) IRR, сделанная на модели будет работать только для "коротких" кредитов с простой ставкой. На "длинных", например, ипотека - работает очень долго. Лучше использовать какой-нибудь численный метод, типа деления отрезка пополам (на PL\SQL) - они хорошо и быстро сходятся.
2) на моей практике любой численный метод давал такое же значение, что и в Excel.
3) также доводилось встречаться с кредитными историями, где ПСК была 4000-5000%. На таких очень сложных кредитах валились все известные мне алгоритмы расчета ПСК (включая Excel), затюненные под расчет в диапазоне 0-1000%. В таких случаях приходилось строить практически вручную график и искать корни практически визуально =)

Резюмирую, выбор метода расчета ПСК необходимо делать, опираясь на
1) тип кредита (новый или существующий, карточки, срочный),
2) срок,
3) основная масса кредитов укладывается в диапазон 0-1000%, но нужно предусмотреть случаи, когда ставка зашкалит.
4) старайтесь унифицировать расчет ПСК в организации и бизнес-процессах, реализовав её как сервис АБС, иначе потом будет поздно =)

PS: По ПСК очень много проблем, которые ещё не решены законодательно. Много чего не прописано для случаев "а что если". Из 2008-У трудно однозначно вывести все возможные случаи. Я много постил свои проблемы и вопросы на авторитетных банковских форумах, но не получил ни одного ответа, не говоря уже о своих оналитегах (сами знаете, какова квалификация большинства из них).
В банках, в которых довелось видеть ПСК, всегда можно было зацепиться и докопаться до чего-либо, т.к. каждый банк отличается от другого по части работы с ПСК (оналитеги везде же разные =)).
...
Рейтинг: 0 / 0
14.09.2011, 10:41
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
XIRR function in PL/SQL ?
    #38010061
grago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grago
Гость
Подскажите, плиз, а кому-то удалось повторить ЧИСТВНДОХ? Ибо бухгалтера считают ее эталоном...
...
Рейтинг: 0 / 0
23.10.2012, 15:24
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38010239
Давно уже
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Давно уже
Гость
grago,
Давно уже у всех кому надо есть. Причем на нормальных алгоритмах, точность до десятитысячных, находится за 5-6 итераций.
...
Рейтинг: 0 / 0
23.10.2012, 16:46
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38010511
hexcept
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hexcept
Гость
нашел в загашниках такой вот г...код, мож, пригодится кому:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
-- 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
-- расчет эффективной % ставки (XIRR, ЧИСТВНДОХ в Excel)
-- по данным temporary-таблицы eff_rate_tmp;
-- hexcept@gmail.com, 2008-09
 l_eff_rate number;
begin
 select round((1/x-1),6) into l_eff_rate -- 6 знаков после запятой...
 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)) -- точность эфф.ставки задаем 0.000001
  (
   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) тестовые данные:
insert into eff_rate_tmp (dt,summ) values( to_date('01.01.2007','dd.mm.yyyy'),-100 )
/
insert into eff_rate_tmp (dt,summ) values( to_date('01.07.2007','dd.mm.yyyy'),58 )
/
insert into eff_rate_tmp (dt,summ) values( to_date('01.01.2008','dd.mm.yyyy'),60 )
/

-- 4) ЧИСТВНДОХ:
select xirr from dual
...
Рейтинг: 0 / 0
23.10.2012, 19:13
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38010526
Слава Зайцев
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Слава Зайцев
Гость
hexceptтакой вот г...кодА насколько модель оказалась быстрее PL/SQL?
...
Рейтинг: 0 / 0
23.10.2012, 19:35
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38023647
grago
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grago
Гость
Это решение было описано выше.
Увы, не совпадает с бух.эталоном - Excel-ем..
...
Рейтинг: 0 / 0
02.11.2012, 16:27
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
XIRR function in PL/SQL ?
    #38724425
_Nastia_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_Nastia_
Гость
Добрый день

Очень помог данный топик когда делала расчет ПСК, а теперь вот новый закон вышел, формула изменилась с 21.07.2014 . Кто-нибудь реализовывал ее уже?
...
Рейтинг: 0 / 0
20.08.2014, 13:09
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38724500
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
_Nastia_, Реализовывали, но пока ЦБ не даст разъяснения как ОНИ ее будут считать, все равно у всех будет по разному :) т.к. там слишком двоякие формулировки используются.
...
Рейтинг: 0 / 0
20.08.2014, 14:01
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #38863305
Фотография May12
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
May12
Участник
авторКто-нибудь реализовывал расчет ПСК на MS SQL или Java уже?
Поделитесь, пожалуйста. Добра вам.
...
Рейтинг: 0 / 0
26.01.2015, 14:58
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
XIRR function in PL/SQL ?
    #39863332
Херург
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Херург
Участник
Не врублюсь, что не так.
Есть вот такие данные:
-151012768731.12.18-12789681107.01.1949587359.4431.01.19-61323424.8101.02.19-36562446.3925.02.19-35316612.6326.02.19-36540049.5815.04.19-6895856.78723.04.19-51712892.9529.04.1951869877.0509.05.1927445324.9210.05.1945919585.2413.05.1937121868.3315.05.1977442350.6723.05.19-173754425.913.06.19-42441539.4927.06.1944388616.3111.07.19-17468777.7919.07.1984595194.2517.09.19

Функция с аналитикой выпадает с ошибкой: ORA-01428: argument '-8.8033219426' is out of range
Происходит это на строке:
Код: plsql
1.
ss[any]=s[CV()]*power(x[1],dat[CV()]/365)


Т.к. при возведении в степень отрицательного числа степень должна быть целочисленная, а здесь получается отрицательное значение пытаемся возвести в дробную степень.
Как надо поправить этот алгоритм?
...
Рейтинг: 0 / 0
18.09.2019, 14:27
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
XIRR function in PL/SQL ?
    #40121950
yon_brover
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
yon_brover
Участник
Спустя продолжительное время, выдался случай проверить функции, предложенные в этом многолетнем треде и вынести свой вердикт, какая функция самая правильная и совпадает с XIRR/ЧИСТВНДОХ в Excel. Оставлю свой результат для потомков.

Функцию, которую предложил Volder второй в посте от 23 авг 07, 15:36, а также затем повторил hexcept 23 окт 12, 19:13, я бы отверг, как не прошедшую тестирование. Как отметил Херург 18 сен 19, 14:27, при некоторых начальных данных этот численный метод расходится и возникает попытка возвести отрицательное число в нецелую степень: ORA-01428: argument '-8.8033219426' is out of range.
Есть у этой функции и другой недостаток, что например, на данных
22.12.2021-10031.12.2021101
вышеозначенная функция выдаёт результат 0.448177, в то время как XIRR/ЧИСТВНДОХ в Excel выдаёт 0.497117 (приведены результаты в долях, а не в процентах)

Куда лучше оказалась функция, которую предложил Serg Kravchenko 2 сен 07, 11:32, однако в ней был недостаток, что не вовремя делался "exit when". В доработанном виде, эта функция выглядит, как приведено ниже. Рекомендую её к использованию.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
create or replace type t_date_array is varray(200000) of date;
create or replace type t_amount_array is varray(200000) of number;

create or replace function xirr(eff_rate_dataset varchar2) return number as
  p_date_array   t_date_array := t_date_array();
  p_amount_array t_amount_array := t_amount_array();
  c_cur          sys_refcursor;
  type t_cur is record(
    dt   date,
    summ number);
  r_cur      t_cur;
  z          number := 0;
  step_limit number := 0;
  temp       number;
  step       number := 0.001;
  d          number := 0.5;
  l_maxdate  date;
  l_mindate  date;
begin
  open c_cur for eff_rate_dataset;
  loop
    fetch c_cur
      into r_cur;
    exit when c_cur%notfound;
    p_date_array.extend;
    p_date_array(p_date_array.last) := r_cur.dt;
    p_amount_array.extend;
    p_amount_array(p_amount_array.last) := r_cur.summ;
  end loop;
  l_maxdate := p_date_array(1);
  l_mindate := p_date_array(1);
  for i in 1 .. p_date_array.count loop
    if p_date_array(i) > l_maxdate then
      l_maxdate := p_date_array(i);
    end if;
    if p_date_array(i) < l_mindate then
      l_mindate := p_date_array(i);
    end if;
  end loop;
  loop
    temp := p_amount_array(1);
    for i in 2 .. p_amount_array.count loop
      temp := temp +
              p_amount_array(i) /
              power(1 + d, (p_date_array(i) - p_date_array(1)) / 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;
  return round(d, 6);
end;


Примеры использования:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
select xirr('select to_date(''22.12.2021'',''dd.mm.yyyy'') dt, -100 summ from dual union all
             select to_date(''31.12.2021'',''dd.mm.yyyy'') dt, 101 summ from dual') val from dual;

select xirr('select to_date(''03.11.2020'',''dd.mm.yyyy'') dt, -103860 summ from dual union all
             select to_date(''25.12.2020'',''dd.mm.yyyy'') dt, -85470 summ from dual union all
             select to_date(''13.01.2021'',''dd.mm.yyyy'') dt, -76067 summ from dual union all
             select to_date(''09.02.2021'',''dd.mm.yyyy'') dt, -299049 summ from dual union all
             select to_date(''30.07.2021'',''dd.mm.yyyy'') dt, 21.5 summ from dual union all
             select to_date(''31.07.2021'',''dd.mm.yyyy'') dt, 23.9 summ from dual union all
             select to_date(''16.12.2021'',''dd.mm.yyyy'') dt, 476370 summ from dual') val from dual;

select xirr('select to_date(''31.12.2018'',''dd.mm.yyyy'') dt, -1510127687 summ from dual union all
             select to_date(''07.01.2019'',''dd.mm.yyyy'') dt, -127896811 summ from dual union all
             select to_date(''31.01.2019'',''dd.mm.yyyy'') dt, 49587359.44 summ from dual union all
             select to_date(''01.02.2019'',''dd.mm.yyyy'') dt, -61323424.81 summ from dual union all
             select to_date(''25.02.2019'',''dd.mm.yyyy'') dt, -36562446.39 summ from dual union all
             select to_date(''26.02.2019'',''dd.mm.yyyy'') dt, -35316612.63 summ from dual union all
             select to_date(''15.04.2019'',''dd.mm.yyyy'') dt, -36540049.58 summ from dual union all
             select to_date(''23.04.2019'',''dd.mm.yyyy'') dt, -6895856.787 summ from dual union all
             select to_date(''29.04.2019'',''dd.mm.yyyy'') dt, -51712892.95 summ from dual union all
             select to_date(''09.05.2019'',''dd.mm.yyyy'') dt, 51869877.05 summ from dual union all
             select to_date(''10.05.2019'',''dd.mm.yyyy'') dt, 27445324.92 summ from dual union all
             select to_date(''13.05.2019'',''dd.mm.yyyy'') dt, 45919585.24 summ from dual union all
             select to_date(''15.05.2019'',''dd.mm.yyyy'') dt, 37121868.33 summ from dual union all
             select to_date(''23.05.2019'',''dd.mm.yyyy'') dt, 77442350.67 summ from dual union all
             select to_date(''13.06.2019'',''dd.mm.yyyy'') dt, -173754425.9 summ from dual union all
             select to_date(''27.06.2019'',''dd.mm.yyyy'') dt, -42441539.49 summ from dual union all
             select to_date(''11.07.2019'',''dd.mm.yyyy'') dt, 44388616.31 summ from dual union all
             select to_date(''19.07.2019'',''dd.mm.yyyy'') dt, -17468777.79 summ from dual union all
             select to_date(''17.09.2019'',''dd.mm.yyyy'') dt, 84595194.25 summ from dual') val from dual;
...
Рейтинг: 0 / 0
21.12.2021, 16:08
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #40122026
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Надеюсь, Вы это творчество в продуктив не запустили?
Оформите хотя бы как нормальную агрегатку...
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
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>

...
Рейтинг: 0 / 0
21.12.2021, 21:37
| Ответить | Цитировать | Написать  
XIRR function in PL/SQL ?
    #40122099
yon_brover
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
yon_brover
Участник
andrey_anonymous, этот код я в продуктив не запускал, а только проверил на своей локальной машине для личных вычислений.
Спасибо за вашу переработку функции в агрегатную. Пригодится потомкам.
...
Рейтинг: 0 / 0
22.12.2021, 11:58
| Ответить | Цитировать | Написать  
34 сообщений из 34, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / XIRR function in PL/SQL ?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1879662]:
 0ms
get settings:
 28ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 64ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 671ms
get tp. blocked users:
 1ms
others: 13ms
total:  815ms
созд. / загр.: 815ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]