Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Proc Oracle that is similar to Excel FV() Function

Luiz AraujoDec 14 2011 — edited Dec 16 2011
Hi Guys.

I need to use in Oracle a function that shows me the same results that FV() financial function in Excel.

Using these parameters in Excel I got the result -29%

Rate: 1591,66
NPer: 18
PMT: 0,90083


I've been search on the web and I creat a function based on Newton-Raphson method. I'll post the source-code below.


CREATE OR REPLACE FUNCTION BI_F_FV(Rate IN NUMBER, PMT IN NUMBER, Nper IN NUMBER) RETURN NUMBER
AS

VAR_X NUMBER(12,10):= 1.50;
VAR_Y NUMBER(12,10):= 0.00;

BEGIN
WHILE TO_NUMBER(VAR_Y - 1) * 10000 <> TO_NUMBER(VAR_X - 1) * 10000 LOOP
VAR_Y:= VAR_X;
VAR_X:= VAR_Y - ((POWER(VAR_Y , (Nper + 1)) - (POWER(VAR_Y , Nper) * (PMT/Rate + 1)) + PMT/Rate) / (((Nper + 1) * POWER(VAR_Y , Nper)) - (Nper * (PMT/Rate + 1) * POWER(VAR_Y , (Nper - 1)))));
END LOOP;

RETURN (VAR_X-1);

END BI_F_FV;


But when I use the same parameters tha I had put in Excel, it shows me 0 instead -29. Anyone have a sample of FV() function in Oracle or can help me to solve this problem?

Regards.
Luiz Araujo
This post has been answered by Solomon Yakobson on Dec 16 2011
Jump to Answer

Comments

Luiz Araujo
No one? :(
Centinul
Luiz Araujo (a.k.a. Buchu) wrote:
Using these parameters in Excel I got the result -29%

Rate: 1591,66
NPer: 18
PMT: 0,90083
Can you provide the Excel formula exactly? Maybe I'm misinterpreting your decimal/comma seperator but when I do this:
=FV(1591.66,18,0.90083)
I definitely don't get -29%. Actually I'm surprised you are getting a percentage because I thought the FV() function returned the future value (a currency amount).
Solomon Yakobson
Answer
Centinul wrote:
Can you provide the Excel formula exactly? Maybe I'm misinterpreting your decimal/comma seperator but when I do this:
=FV(1591.66,18,0.90083)
I definitely don't get -29%. Actually I'm surprised you are getting a percentage because I thought the FV() function returned the future value (a currency amount).
MS Excel: FV Function
--------------------------------------------------------------------------------

In Excel, the FV function returns the future value of an investment based on an
interest rate and a constant payment schedule.

The syntax for the FV function is:

FV( interest_rate, number_payments, payment, PV, Type )

interest_rate is the interest rate for the investment.

number_payments is the number of payments for the annuity.

payment is the amount of the payment made each period.

PV is optional. It is the present value of the payments. If this parameter is omitted,
the FV function assumes PV to be 0.

Type is optional. It indicates when the payments are due. Type can be one of the following values:

Value Explanation 
0 Payments are due at the end of the period. (default) 
1 Payments are due at the beginning of the period. 

If the Type parameter is omitted, the FV function assumes a Type value of 0.
It could be easily done in 11.2 using recursive subquery factoring. For eaxmple:
=FV(7.5%/12,2*12,-250,-5000,1)
In Excel produces $12,298.46 
variable pct number
variable pmnt_cnt number
variable pmnt_amt number
variable init_amt number
variable pmnt_type number
begin
    :pct := 7.5/12;
    :pmnt_cnt := 2*12;
    :pmnt_amt := -250;
    :init_amt := -5000;
    :pmnt_type := 1;
end;
/
with r(
       pct,
       pmnt_cnt,
       pmnt_amt,
       init_amt,
       pmnt_type,
       pmnt_num,
       total_amt
      ) as (
             select  :pct,
                     :pmnt_cnt,
                     :pmnt_amt,
                     :init_amt,
                     :pmnt_type,
                     1 pmnt_num,
                     :init_amt + :pmnt_amt + round((:init_amt + :pmnt_type * :pmnt_amt) * :pct / 100,4)
               from  dual
            union all
             select  pct,
                     pmnt_cnt,
                     pmnt_amt,
                     init_amt,
                     pmnt_type,
                     pmnt_num + 1,
                     total_amt + pmnt_amt + round((total_amt + pmnt_type * pmnt_amt) * pct / 100,4)
               from  r
               where pmnt_num <= pmnt_cnt
           )
select  trunc(total_amt,2) total_amt
  from  r
  where pmnt_num = pmnt_cnt
/
 TOTAL_AMT
----------
 -12298.46

SQL>
Now OP definitely needs to explain his numbers
Rate: 1591,66
NPer: 18
PMT: 0,90083
Rate of 1591,66%???
Payments of $0,90083???

SY.

Edited by: Solomon Yakobson on Dec 16, 2011 2:59 PM
Marked as Answer by Luiz Araujo · Sep 27 2020
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 13 2012
Added on Dec 14 2011
#excel
3 comments
1,685 views