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: 3123
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: 8841
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 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

Previous Topic: Help required for adding patition by range in table
Next Topic: Oracle Role creation on Schema
Goto Forum:
  


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