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;

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. ]]>

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

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;

Somebody solved it ?

Thanks.]]>

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

]]>

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