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 

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 #633425 is a reply to message #633424] 
Wed, 18 February 2015 06:43 

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 #669878 is a reply to message #669840] 
Mon, 21 May 2018 10:52 

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



Goto Forum:
Current Time: Mon May 28 04:48:28 CDT 2018
