Home » SQL & PL/SQL » SQL & PL/SQL » Duplicating Excel "RATE" function in Oracle (Oracle 11.2)
Duplicating Excel "RATE" function in Oracle [message #633412] Wed, 18 February 2015 02:39 Go to next message
Primordial
Messages: 2
Registered: 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
type - In Arrears/Advance

I have got a working function already from here: https://community.oracle.com/thread/2180514

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.

Can anyone please assist?

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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

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 Go to previous messageGo to next message
Primordial
Messages: 2
Registered: 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 Go to previous messageGo to next message
PHILLIPM.33
Messages: 2
Registered: 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: 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 Go to previous messageGo to next message
PHILLIPM.33
Messages: 2
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: 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 Go to previous messageGo to next message
a_nawas_khan
Messages: 8
Registered: 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: 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 Go to previous message
eleny
Messages: 1
Registered: August 2021
Junior Member
my version of calculating the interest rate Smile
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: Thu Mar 28 04:33:56 CDT 2024