Home » SQL & PL/SQL » SQL & PL/SQL » Duplicating Excel "RATE" function in Oracle (Oracle 11.2)
Duplicating Excel "RATE" function in Oracle Wed, 18 February 2015 02:39
 Primordial Messages: 2Registered: February 2015 Location: Australia Junior Member
Hi all,

I'm trying to duplicate the Excel RATE function as an Oracle Function. The function takes known values for a finance calculation and works out a compound interest rate using an iterative calculation.

The required arguments, as per Excel are:

nper - Number of Periods
pmt - Payment amount per period
pv - Present Value
fv - Future Value

However, I can't figure out how to implement the fv (Future Value) or type (In Advance or Arrears) arguments into this function - as they dont appear to be factored into this script.

```declare
m       number := 100000; --pv
p       number := 830.33; --pmt
n       number := 15; --nper
n12     number := 12 * n;
pm      number := p / m;
i       number := pm;
old_i   number := 2 * pm;
ret_val number;
cnt     pls_integer := 0;
begin
--  while old_i > i                    /* to compute to maximum precision */
while old_i - i > 5 * power(10,-11)  /* to achieve 10 digits precision */
loop
cnt := cnt + 1;
old_i := i;
i := pm * (1 - 1 / power(1 + i,n12));
if cnt > 500 then
exit;
end if;
dbms_output.put_line(to_char(cnt)||' '||to_char(old_i)||' '||to_char(i)||' '||to_char(old_i - i));
end loop;
ret_val := 12 * i;
dbms_output.put_line(to_char(cnt)||' iterations to return '||to_char(ret_val));
end;```
Re: Duplicating Excel "RATE" function in Oracle [message #633424 is a reply to message #633412] Wed, 18 February 2015 05:09
 Lalit Kumar B Messages: 3174Registered: May 2013 Location: World Wide on the Web Senior Member
Welcome to the forum!

Can you please mention the rules for Future Value and In Advance or Arrears. It would be better if you explain in simple words.
Re: Duplicating Excel "RATE" function in Oracle [message #633425 is a reply to message #633424] Wed, 18 February 2015 06:43
 Primordial Messages: 2Registered: February 2015 Location: Australia Junior Member
Hi Lalit,

Apologies for not posting the code correctly, and thanks for the welcome.

The Future Value is what the asset is worth at the end of the finance (at the end of however many periods). A working example would be car finance. A Vehicle may cost \$100,000 to purchase, and is worth \$40,000 at the end of 36 months. Therefore the amount to be actually financed is the difference (\$60,000).

The Advanced or Arrears value is whether the payments are made at the beginning or end of the period. The result of which I believe is one months' less compounded interest, because there is no interest payable in the first period if the payment is being made at the 'end' of the period.

Now unfortunately my biggest issue is that I can't find the mathematical way to include these above arguments - I'm guessing that this is obviously a prerequisite for designing a PL/SQL version! I've scoured for days, and have come up with no explanation on the actual formula behind the Excel function. I was hoping that someone here may have seen something like this before.

Posting the code again, in the hope that I've formatted it correctly this time:

```DECLARE
m       NUMBER := 100000; --pv
p       NUMBER := 830.33; --pmt
n       NUMBER := 15; --nper
n12     NUMBER := 12 * n;
pm      NUMBER := p / m;
i       NUMBER := pm;
old_i   NUMBER := 2 * pm;
ret_val NUMBER;
cnt     PLS_INTEGER := 0;
BEGIN
--  while old_i > i                    /* to compute to maximum precision */
WHILE old_i - i > 5 * Power(10, -11) /* to achieve 10 digits precision */
LOOP
cnt := cnt + 1;

old_i := i;

i := pm * ( 1 - 1 / Power(1 + i, n12) );

IF cnt > 500 THEN
EXIT;
END IF;

dbms_output.Put_line(To_char(cnt)
||' '
||To_char(old_i)
||' '
||To_char(i)
||' '
||To_char(old_i - i));
END LOOP;

ret_val := 12 * i;

dbms_output.Put_line(To_char(cnt)
||' iterations to return '
||To_char(ret_val));
END; ```
Re: Duplicating Excel "RATE" function in Oracle [message #669840 is a reply to message #633425] Wed, 16 May 2018 05:02
 PHILLIPM.33 Messages: 2Registered: May 2018 Junior Member
I have the same problem than primordial.

Somebody solved it ?

Thanks.
Re: Duplicating Excel "RATE" function in Oracle [message #669864 is a reply to message #669840] Sun, 20 May 2018 04:49
 Barbara Boehmer Messages: 9097Registered: November 2002 Location: California, USA Senior Member
There are various financial functions, including CalcMgrExcelRATE, listed in the Oracle Hyperion Calculation Manager Designer's Guide:

https://docs.oracle.com/cd/E57185_01/CALDH/financial_functions.htm#CALDH-cmgr_mvf_635

Re: Duplicating Excel "RATE" function in Oracle [message #669878 is a reply to message #669840] Mon, 21 May 2018 10:52
 PHILLIPM.33 Messages: 2Registered: May 2018 Junior Member
```CREATE OR REPLACE FUNCTION RATE (pv IN number, fv IN number, pmt IN number, nper IN number, type IN number )
RETURN number IS rate number;
BEGIN
DECLARE
guess NUMBER(6,3) := .1;
rate BINARY_DOUBLE;
FINANCIAL_MAX_ITERATIONS NUMBER(10) := 100;
FINANCIAL_PRECISION BINARY_DOUBLE := .000001;
y BINARY_DOUBLE;
y0 BINARY_DOUBLE;
y1 BINARY_DOUBLE;
f BINARY_DOUBLE;
i NUMBER(10);
x0 BINARY_DOUBLE := 0;
x1 BINARY_DOUBLE;
BEGIN
rate := guess;
IF(ABS(rate) < FINANCIAL_PRECISION) THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;

y0 := pv + pmt * nper + fv;
y1 := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;

i  := x0 ;
x1 := rate;

WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS))
LOOP
rate := (y1 * x0 - y0 * x1) / (y1 - y0);
x0 := x1;
x1 := rate;

IF (ABS(rate) < FINANCIAL_PRECISION) THEN 	y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;

y0 := y1;
y1 := y;
i := i + 1;
END LOOP;
RETURN RATE;
END;
END;
```

--EXAMPLE

```select rate(10053.96,  -9457.25, -124.38, 6, 1)*100 FROM DUAL; --%FORMAT
select rate(10053.96,  -9457.25, -124.38, 6, 1)*12*100 FROM DUAL;-- ANNUAL RATE (X12) IN %FORMAT```

[Updated on: Mon, 21 May 2018 10:54]

Report message to a moderator

Re: Duplicating Excel "RATE" function in Oracle [message #673856 is a reply to message #669878] Thu, 13 December 2018 03:51
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Hi Phillip,

Thanks for below function it was very useful.

But When use below paramters I got ORA-06502: PL/SQL: numeric or value error.

declare
c_apr number(6,2);
begin
c_APR := STAGING.excel_rate_function(300,11836,(-1*1117874), 0, 0);
dbms_output.put_line(c_aPR);
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "STAGING.EXCEL_RATE_FUNCTION", line 46
ORA-06512: at line 4

When we use pmt value as big number i got error other wise OK.

Kindly your urgent help need on this issue.

Regards
Shahul
Re: Duplicating Excel "RATE" function in Oracle [message #673858 is a reply to message #673856] Thu, 13 December 2018 05:53
 Solomon Yakobson Messages: 3273Registered: January 2010 Location: Connecticut, USA Senior Member
Did you notice parameter sequence in Phillip's function doesn't match Excel parameter sequence (nper, pmt, pv, fv, type)? So you ended with negative payment and got Oracle error. You'd get same error in Excel.

SY.
Re: Duplicating Excel "RATE" function in Oracle [message #673859 is a reply to message #673858] Thu, 13 December 2018 07:07
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Hi,

Below code I am using the parameters in the same order of Phillip function. Even though I got error.... when I decrease pmt value to 10000 it's working fine. But pmt value 11836 is getting error. Please check.

SQL>
SQL> declare
2 c_apr_rate number(6,2);
3 c_apr_percent number(6,2);
4 begin
5 c_APR_rate := rate((-1*1115741),0,11836,299,0);
6 --c_APR_rate := rate((-1*1115741),0,10000,299,0);
7 dbms_output.put_line('APR Rate : '||c_apr_rate);
8 end;
9 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "STAGING.RATE", line 46
ORA-06512: at line 5

SQL> declare
2 c_apr_rate number(6,2);
3 c_apr_percent number(6,2);
4 begin
5 --c_APR_rate := rate((-1*1115741),0,11836,299,0);
6 c_APR_rate := rate((-1*1115741),0,10000,299,0);
7 dbms_output.put_line('APR Rate : '||c_apr_rate);
8 end;
9 /
APR Rate : .01

PL/SQL procedure successfully completed.
Re: Duplicating Excel "RATE" function in Oracle [message #673869 is a reply to message #673859] Thu, 13 December 2018 09:14
 cookiemonster Messages: 13923Registered: September 2008 Location: Rainy Manchester Senior Member
You need to learn to debug properly.
Line 46 is:
```RETURN RATE;
```
Which means the calculated rate value doesn't fi in c_apr_rate (number(6,2))
If we put a dbms_output.put_line inside the function to find out what rate is set to before it's returned then we find it is:
```Nan
```
Which goes to show that using binary_double as the datatype for a variable returned by a function that's supposed to return a number probably isn't a good idea.
If we change rate to number datatype then it errors out at line 40 instead.
I haven't bothered back-tracking further but I imagine the maths has resulted in a number that's way too big.
You could always add more dbms_output to the function to see what all the variables are being set to as it goes.
Re: Duplicating Excel "RATE" function in Oracle [message #673900 is a reply to message #673869] Sun, 16 December 2018 01:16
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Hi,

I debug and print the values of all variables and find below the result. After the 10th iteration values assigned as Nan. Kindly advise how to resolve this.

SQL> declare
2 c_apr_rate number;
3 c_apr_percent number;
4 begin
5 c_APR_rate := rate((-1*1115741),0,11836,299,0);
6 c_APR_percent := round((POWER((rate((-1*1115741),0,11836,299,0)+1),12)-1)*100,2);
7 dbms_output.put_line('APR Rate : '||c_apr_rate);
8 dbms_output.put_line('APR Percent : '||c_apr_percent);
9 end;
10 /
y=2.4232229999659313E+006 y0=-2.3728700403541535E+018 y1=2.4232229999659313E+006 f=2.3791009056258691E+012 i=0 x0=1.0000000000000001E-001 x1=1.0212202770430094E-013 rate=1.0212202770430094E-013
y=2.4232229999318626E+006 y0=2.4232229999659313E+006 y1=2.4232229999318626E+006 f=2.3791009056258691E+012 i=1 x0=1.0212202770430094E-013 x1=2.0424405540706182E-013 rate=2.0424405540706182E-013
y=2.8430084386958554E+006 y0=2.4232229999318626E+006 y1=2.8430084386958554E+006 f=8.7058267636067885E+000 i=2 x0=2.0424405540706182E-013 x1=7.2636867768495041E-003 rate=7.2636867768495041E-003
y=2.8227729146611976E+005 y0=2.8430084386958554E+006 y1=2.8227729146611976E+005 f=2.7402989851936756E-006 i=3 x0=7.2636867768495041E-003 x1=-4.1929831852213738E-002 rate=-4.1929831852213738E-002
y=2.49953988877948E+005 y0=2.8227729146611976E+005 y1=2.49953988877948E+005 f=5.0202551807446176E-007 i=4 x0=-4.1929831852213738E-002 x1=-4.7352585119897671E-002 rate=-4.7352585119897671E-002
y=1.3256220842853052E+005 y0=2.49953988877948E+005 y1=1.3256220842853052E+005 f=7.1641942299815654E-013 i=5 x0=-4.7352585119897671E-002 x1=-8.9286382146397336E-002 rate=-8.9286382146397336E-002
y=8.6622259234002995E+004 y0=1.3256220842853052E+005 y1=8.6622259234002995E+004 f=8.3463944604317041E-020 i=6 x0=-8.9286382146397336E-002 x1=-1.3663924382329959E-001 rate=-1.3663924382329959E-001
y=5.2388922014081239E+004 y0=8.6622259234002995E+004 y1=5.2388922014081239E+004 f=5.5721703142711585E-034 i=7 x0=-1.3663924382329959E-001 x1=-2.2592562597143509E-001 rate=-2.2592562597143509E-001
y=3.2645192587745543E+004 y0=5.2388922014081239E+004 y1=3.2645192587745543E+004 f=3.3601786760507955E-059 i=8 x0=-2.2592562597143509E-001 x1=-3.6256486979473468E-001 rate=-3.6256486979473468E-001
y=2.0112474347763982E+004 y0=3.2645192587745543E+004 y1=2.0112474347763982E+004 f=5.0070412807315999E-116 i=9 x0=-3.6256486979473468E-001 x1=-5.8849049576616985E-001 rate=-5.8849049576616985E-001
y=1.2445121944104143E+004 y0=2.0112474347763982E+004 y1=1.2445121944104143E+004 f=0 i=10 x0=-5.8849049576616985E-001 x1=-9.5105536556090442E-001 rate=-9.5105536556090442E-001
y=Nan y0=1.2445121944104143E+004 y1=Nan f=Nan i=11 x0=-9.5105536556090442E-001 x1=-1.5395458613270743E+000 rate=-1.5395458613270743E+000
y=Nan y0=Nan y1=Nan f=Nan i=12 x0=-1.5395458613270743E+000 x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=13 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=14 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=15 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=16 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=17 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=18 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=19 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=20 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=21 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=22 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=23 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=24 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=25 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=26 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=27 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=28 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=29 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=30 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=31 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=32 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=33 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=34 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=35 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=36 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=37 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=38 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=39 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=40 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=41 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=42 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=43 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=44 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=45 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=46 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=47 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=48 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=49 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=50 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=51 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=52 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=53 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=54 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=55 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=56 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=57 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=58 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=59 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=60 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=61 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=62 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=63 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=64 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=65 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=66 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=67 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=68 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=69 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=70 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=71 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=72 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=73 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=74 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=75 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=76 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=77 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=78 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=79 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=80 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=81 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=82 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=83 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=84 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=85 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=86 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=87 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=88 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=89 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=90 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=91 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=92 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=93 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=94 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=95 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=96 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=97 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=98 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=99 x0=Nan x1=Nan rate=Nan
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "STAGING.RATE", line 48
ORA-06512: at line 5

------Upto EMI=10468 function work properly. But >10468 will fail.

SQL> declare
2 --c_apr_rate number(6,2);
3 --c_apr_percent number(6,2);
4 c_apr_rate number;
5 c_apr_percent number;
6
7 begin
8 c_APR_rate := STAGING.excel_rate_function_test(300,10468,(-1*1115741), 0, 0);
9 c_APR_percent := round((POWER((STAGING.excel_rate_function_test(300,10468,(-1*1115741), 0, 0)+1),12)-1)*100,2);
10 dbms_output.put_line('APR Rate : '||c_apr_rate);
11 dbms_output.put_line('APR Percent : '||c_apr_percent);
12 end;
13 /
APR Rate : .0060487872075552828
APR Percent : 7.5

PL/SQL procedure successfully completed.
Re: Duplicating Excel "RATE" function in Oracle [message #673902 is a reply to message #673900] Sun, 16 December 2018 03:07
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Hi,

Again function gives wrong result for below given inputs.

SQL>
SQL> declare
2 c_apr_rate number;
3 c_apr_percent number;
4
5 begin
6 c_APR_rate := STAGING.excel_rate_function_test(348,11064,(-1*1599880), 0, 0);
7 c_APR_percent := round((POWER((STAGING.excel_rate_function_test(348,11064,(-1*1599880), 0, 0)+1),12)-1)*100,2);
8 dbms_output.put_line('APR Rate : '||c_apr_rate);
9 dbms_output.put_line('APR Percent : '||c_apr_percent);
10 end;
11 /
APR Rate : .0000000000000011903345607771922
APR Percent : 0

PL/SQL procedure successfully completed.

MS Excel gives APR Rate = 0.01 and APR percent=7.54

Please urgently help somebody to overcome from this issue.
Re: Duplicating Excel "RATE" function in Oracle [message #673903 is a reply to message #673902] Sun, 16 December 2018 03:10
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
SQL> declare
2 c_apr_rate number;
3 c_apr_percent number;
4 begin
5 c_APR_rate := rate((-1*1599880),0,11064,348,0);
6 c_APR_percent := round((POWER((rate((-1*1599880),0,11064,348,0)+1),12)-1)*100,2);
7 dbms_output.put_line('APR Rate : '||c_apr_rate);
8 dbms_output.put_line('APR Percent : '||c_apr_percent);
9 end;
10 /
y=2.2503919999996684E+006 y0=-3.7811083944844729E+020 y1=2.2503919999996684E+006 f=2.5389516763479841E+014 i=0 x0=1.0000000000000001E-001 x1=5.9516728038864166E-016 rate=5.9516728038864166E-016
y=2.2503919999993369E+006 y0=2.2503919999996684E+006 y1=2.2503919999993369E+006 f=2.5389516763479841E+014 i=1 x0=5.9516728038864166E-016 x1=1.1903345607771922E-015 rate=1.1903345607771922E-015
y=2.2503919999996684E+006 y0=-3.7811083944844729E+020 y1=2.2503919999996684E+006 f=2.5389516763479841E+014 i=0 x0=1.0000000000000001E-001 x1=5.9516728038864166E-016 rate=5.9516728038864166E-016
y=2.2503919999993369E+006 y0=2.2503919999996684E+006 y1=2.2503919999993369E+006 f=2.5389516763479841E+014 i=1 x0=5.9516728038864166E-016 x1=1.1903345607771922E-015 rate=1.1903345607771922E-015
APR Rate : .0000000000000011903345607771922
APR Percent : 0

PL/SQL procedure successfully completed.

MS Excel gives APR Rate = 0.01 and APR percent=7.54

Please urgently help somebody to overcome from this issue.
Re: Duplicating Excel "RATE" function in Oracle [message #673904 is a reply to message #673903] Sun, 16 December 2018 04:44
 Michel Cadot Messages: 68653Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Align the columns in result.

Re: Duplicating Excel "RATE" function in Oracle [message #673913 is a reply to message #673903] Mon, 17 December 2018 03:24
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Dear friends,

Anybody have idea to fix above error.

Shahul
Re: Duplicating Excel "RATE" function in Oracle [message #673917 is a reply to message #673913] Mon, 17 December 2018 15:35
 Barbara Boehmer Messages: 9097Registered: November 2002 Location: California, USA Senior Member
This may not be the only problem, but it is a place to start. If you add a bunch of dbms_output.put_line to determine where the first Nan (not a number) of any variable occurs, then determine the source of that, it appears that the problem is when 1+rate is <= 0 and therefore ln(1+rate) results in an out of range error.

So, if you change the line that says:

WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS))

to:

WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS)) AND (1 + rate) > 0

then that eliminates that error. However, I don't know how this will affect the accuracy of the results. You will need to run several tests of the function and compare to results of your excel function.

[Updated on: Mon, 17 December 2018 15:39]

Report message to a moderator

Re: Duplicating Excel "RATE" function in Oracle [message #673921 is a reply to message #673917] Tue, 18 December 2018 00:31
 a_nawas_khan Messages: 8Registered: April 2007 Junior Member
Hi,

I debug and print the values of all variables and find below the result. After the 10th iteration values assigned as Nan. Kindly advise how to resolve this.

SQL> declare
2 c_apr_rate number;
3 c_apr_percent number;
4 begin
5 c_APR_rate := rate((-1*1115741),0,11836,299,0);
6 c_APR_percent := round((POWER((rate((-1*1115741),0,11836,299,0)+1),12)-1)*100,2);
7 dbms_output.put_line('APR Rate : '||c_apr_rate);
8 dbms_output.put_line('APR Percent : '||c_apr_percent);
9 end;
10 /
y=2.4232229999659313E+006 y0=-2.3728700403541535E+018 y1=2.4232229999659313E+006 f=2.3791009056258691E+012 i=0 x0=1.0000000000000001E-001 x1=1.0212202770430094E-013 rate=1.0212202770430094E-013
y=2.4232229999318626E+006 y0=2.4232229999659313E+006 y1=2.4232229999318626E+006 f=2.3791009056258691E+012 i=1 x0=1.0212202770430094E-013 x1=2.0424405540706182E-013 rate=2.0424405540706182E-013
y=2.8430084386958554E+006 y0=2.4232229999318626E+006 y1=2.8430084386958554E+006 f=8.7058267636067885E+000 i=2 x0=2.0424405540706182E-013 x1=7.2636867768495041E-003 rate=7.2636867768495041E-003
y=2.8227729146611976E+005 y0=2.8430084386958554E+006 y1=2.8227729146611976E+005 f=2.7402989851936756E-006 i=3 x0=7.2636867768495041E-003 x1=-4.1929831852213738E-002 rate=-4.1929831852213738E-002
y=2.49953988877948E+005 y0=2.8227729146611976E+005 y1=2.49953988877948E+005 f=5.0202551807446176E-007 i=4 x0=-4.1929831852213738E-002 x1=-4.7352585119897671E-002 rate=-4.7352585119897671E-002
y=1.3256220842853052E+005 y0=2.49953988877948E+005 y1=1.3256220842853052E+005 f=7.1641942299815654E-013 i=5 x0=-4.7352585119897671E-002 x1=-8.9286382146397336E-002 rate=-8.9286382146397336E-002
y=8.6622259234002995E+004 y0=1.3256220842853052E+005 y1=8.6622259234002995E+004 f=8.3463944604317041E-020 i=6 x0=-8.9286382146397336E-002 x1=-1.3663924382329959E-001 rate=-1.3663924382329959E-001
y=5.2388922014081239E+004 y0=8.6622259234002995E+004 y1=5.2388922014081239E+004 f=5.5721703142711585E-034 i=7 x0=-1.3663924382329959E-001 x1=-2.2592562597143509E-001 rate=-2.2592562597143509E-001
y=3.2645192587745543E+004 y0=5.2388922014081239E+004 y1=3.2645192587745543E+004 f=3.3601786760507955E-059 i=8 x0=-2.2592562597143509E-001 x1=-3.6256486979473468E-001 rate=-3.6256486979473468E-001
y=2.0112474347763982E+004 y0=3.2645192587745543E+004 y1=2.0112474347763982E+004 f=5.0070412807315999E-116 i=9 x0=-3.6256486979473468E-001 x1=-5.8849049576616985E-001 rate=-5.8849049576616985E-001
y=1.2445121944104143E+004 y0=2.0112474347763982E+004 y1=1.2445121944104143E+004 f=0 i=10 x0=-5.8849049576616985E-001 x1=-9.5105536556090442E-001 rate=-9.5105536556090442E-001
y=Nan y0=1.2445121944104143E+004 y1=Nan f=Nan i=11 x0=-9.5105536556090442E-001 x1=-1.5395458613270743E+000 rate=-1.5395458613270743E+000
y=Nan y0=Nan y1=Nan f=Nan i=12 x0=-1.5395458613270743E+000 x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=13 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=14 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=15 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=16 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=17 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=18 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=19 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=20 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=21 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=22 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=23 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=24 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=25 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=26 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=27 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=28 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=29 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=30 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=31 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=32 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=33 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=34 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=35 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=36 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=37 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=38 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=39 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=40 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=41 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=42 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=43 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=44 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=45 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=46 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=47 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=48 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=49 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=50 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=51 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=52 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=53 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=54 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=55 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=56 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=57 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=58 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=59 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=60 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=61 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=62 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=63 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=64 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=65 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=66 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=67 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=68 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=69 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=70 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=71 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=72 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=73 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=74 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=75 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=76 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=77 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=78 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=79 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=80 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=81 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=82 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=83 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=84 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=85 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=86 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=87 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=88 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=89 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=90 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=91 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=92 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=93 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=94 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=95 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=96 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=97 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=98 x0=Nan x1=Nan rate=Nan
y=Nan y0=Nan y1=Nan f=Nan i=99 x0=Nan x1=Nan rate=Nan
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "STAGING.RATE", line 48
ORA-06512: at line 5

------Upto EMI=10468 function work properly. But >10468 will fail.

I tried your earlier recommendation condition but it gives wrong result.
Re: Duplicating Excel "RATE" function in Oracle [message #673935 is a reply to message #673921] Tue, 18 December 2018 06:50
 Barbara Boehmer Messages: 9097Registered: November 2002 Location: California, USA Senior Member
It looks like much of your problems may be due to having the parameters in a different order or having invalid values that would result in #NUM! in Excel, such as monthly payment that exceeds the total loan amount. I have provided a modification of the function below that puts the parameters in the same order for easier comparison with Excel, along with default values, and some formatting for easier reading. It appears to calculate things well using just the first 3 parameters, but I am not sure about fv, type, and guess.

```CREATE OR REPLACE FUNCTION rate
(nper  IN NUMBER,
pmt   IN NUMBER,
pv    IN NUMBER,
fv    IN NUMBER DEFAULT  0,
type  IN NUMBER DEFAULT  0,
guess IN NUMBER DEFAULT .1)
RETURN    NUMBER
AS
rate                     BINARY_DOUBLE;
financial_max_iterations NUMBER(10) := 100;
financial_precision      BINARY_DOUBLE := .000001;
y                        BINARY_DOUBLE;
y0                       BINARY_DOUBLE;
y1                       BINARY_DOUBLE;
f                        BINARY_DOUBLE;
i                        NUMBER(10);
x0                       BINARY_DOUBLE := 0;
x1                       BINARY_DOUBLE;
BEGIN
rate := guess;
IF (ABS(rate) < financial_precision)
THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;
y0 := pv + pmt * nper + fv;
y1 := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
i  := x0 ;
x1 := rate;
WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS))
LOOP
rate := (y1 * x0 - y0 * x1) / (y1 - y0);
x0 := x1;
x1 := rate;
IF (ABS(rate) < FINANCIAL_PRECISION)
THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
ELSE
f := EXP(nper * LN(1 + rate));
y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
END IF;
y0 := y1;
y1 := y;
i := i + 1;
END LOOP;
RETURN rate;
END rate;
/
```

The following is an example of compilation and usage with parameter values in the same order as Excel, that produced the same results in Excel.

```SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION rate
2    (nper  IN NUMBER,
3  	pmt   IN NUMBER,
4  	pv    IN NUMBER,
5  	fv    IN NUMBER DEFAULT  0,
6  	type  IN NUMBER DEFAULT  0,
7  	guess IN NUMBER DEFAULT .1)
8    RETURN	 NUMBER
9  AS
10    rate			BINARY_DOUBLE;
11    financial_max_iterations NUMBER(10) := 100;
12    financial_precision	BINARY_DOUBLE := .000001;
13    y			BINARY_DOUBLE;
14    y0			BINARY_DOUBLE;
15    y1			BINARY_DOUBLE;
16    f			BINARY_DOUBLE;
17    i			NUMBER(10);
18    x0			BINARY_DOUBLE := 0;
19    x1			BINARY_DOUBLE;
20  BEGIN
21    rate := guess;
22    IF (ABS(rate) < financial_precision)
23  	 THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
24    ELSE
25  	 f := EXP(nper * LN(1 + rate));
26  	 y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
27    END IF;
28    y0 := pv + pmt * nper + fv;
29    y1 := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
30    i  := x0 ;
31    x1 := rate;
32    WHILE ((ABS(y0 - y1) > FINANCIAL_PRECISION) AND (i < FINANCIAL_MAX_ITERATIONS))
33    LOOP
34  	 rate := (y1 * x0 - y0 * x1) / (y1 - y0);
35  	 x0 := x1;
36  	 x1 := rate;
37  	 IF (ABS(rate) < FINANCIAL_PRECISION)
38  	   THEN y := pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
39  	 ELSE
40  	   f := EXP(nper * LN(1 + rate));
41  	   y := pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
42  	 END IF;
43  	 y0 := y1;
44  	 y1 := y;
45  	 i := i + 1;
46    END LOOP;
47    RETURN rate;
48  END rate;
49  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> SELECT rate (4*12, -200, 8000) * 100 FROM DUAL
2  /

RATE(4*12,-200,8000)*100
------------------------
.770147249

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT rate (6, -124.38, 10053.96) * 100 FROM DUAL
2  /

RATE(6,-124.38,10053.96)*100
----------------------------
-45.38631

1 row selected.
```

Re: Duplicating Excel "RATE" function in Oracle [message #673936 is a reply to message #673935] Tue, 18 December 2018 07:17
 Barbara Boehmer Messages: 9097Registered: November 2002 Location: California, USA Senior Member
A little more testing shows that fv and type produce the same results as Excel as well.

```SCOTT@orcl_12.1.0.2.0> SELECT rate (6, -124.38, 10053.96, -9457.25) * 100 FROM DUAL
2  /

RATE(6,-124.38,10053.96,-9457.25)*100
-------------------------------------
.254213362

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT rate (6, -124.38, 10053.96, -9457.25, 1) * 100 FROM DUAL
2  /

RATE(6,-124.38,10053.96,-9457.25,1)*100
---------------------------------------
.257478973

1 row selected.
```

Re: Duplicating Excel "RATE" function in Oracle [message #684822 is a reply to message #673921] Mon, 30 August 2021 16:24
 eleny Messages: 1Registered: August 2021 Junior Member
my version of calculating the interest rate
```CREATE OR REPLACE FUNCTION fnc_g_div_gp(r in number, n in number, p in number, x in number, y in number, w in number)
return number
is

/* Evaluate g(r_n)/g'(r_n), where g := fv + pv*(1+rate)**nper + pmt*(1+rate*when)/rate * ((1+rate)**nper - 1)*/

t1  number;
t2  number;
g  number;
gp  number;
res  number;

begin

t1 := power((r+1),n);
t2 := power((r+1),(n-1));
g := y + t1 * x + p * (t1 - 1) * (r * w + 1) / r;
gp := (n * t2 * x - p * (t1 - 1) * (r * w + 1) / (power(r,2)) + n * p * t2 * (r * w + 1) / r + p * (t1 - 1) * w/ r);
res := g / gp;

return res;

end;

with function fnc_getRate(nper in number, pmt in number, pv in number, fv in number, type_ in number)
return number
is

/* Evaluate g(r_n)/g'(r_n), where g := fv + pv*(1+rate)**nper + pmt*(1+rate*when)/rate * ((1+rate)**nper - 1)*/
begin
declare
guess number(6,3) := .5;
rate number;
maxiter number(10) := 100;
tol number := .000001;
close_ number := 0;

iterator  number;
rn  number;
rnp1  number;
diff  number;
g  number;

begin

rn := guess;
iterator := 0;
while (iterator < maxiter)
loop
rnp1 := rn - fnc_g_div_gp(rn, nper, pmt, pv, fv, type_);
diff := abs(rnp1 - rn);
iterator := iterator + 1;
rn := rnp1;
end loop;
return rn;
end;
end;

select fnc_getRate(36,749,-2531,0,0) as rate from dual
;
```
 Previous Topic: Fire triggers on a nested_table Next Topic: Error ocurred calling a Web Service using UTL_HTTP
Goto Forum:

Current Time: Wed May 22 12:26:13 CDT 2024