• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

calculate APR loan repayment using pl/sql or java or ....?

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    calculate APR loan repayment using pl/sql or java or ....?

    Hi,

    I am trying to work our Loan payment using pl/sql

    I am trying to write a procedure that takes as input :-
    1) Loan amount e.g. 5000
    2) APR rate e.g 24.9%
    3) period/installment e.g. 11 months

    And I want returned or calculated:-
    1) total cost of loan
    2) Installment amount (even spread over the period passed in).

    I cant for the life of understand the formula for the calculation. I think the formula was standardized by the OFT in the Acts

    I dont mind what procedural language its written in, cos i can always convert it to pl/sql

    cheers

    css_jay99

    #2
    The formula is:


    Payment =

    rate x loan amount x (1+rate)^number payments
    ------------------------------------------------
    ...........(1+rate)^number payments - 1

    But the rate and the number of payments have to be in the same units (i.e. both in years or both in months)

    So in my piss poor knowledge of pl/sql:

    Code:
    DECLARE r NUMBER /* Rate (APR) */
    DECLARE L NUMBER /* Loan amount */
    DECLARE n NUMBER /* number of payments IN MONTHS * /
    DECLARE p NUMBER /* Monthly payment */
    p = (r/12*L*power((1+r/12),n))/(power((1+r/12),n)-1)
    p is your monthly payment and then the cost of the loan is just

    p x n

    You can check your results in Excel by using the pmt function

    HTH

    P.S. Your result for your example above should be

    a) 5643.79
    b) 513.07
    Last edited by MrRobin; 8 May 2008, 09:33.
    It's about time I changed this sig...

    Comment


      #3
      bless,

      Is r 24.9 or 24.9/100 ?


      Carlton

      Comment


        #4
        Originally posted by css_jay99 View Post
        bless,

        Is r 24.9 or 24.9/100 ?


        Carlton

        The latter.

        0.249
        It's about time I changed this sig...

        Comment


          #5
          The value is just m=not comming out right. there must be something missing

          any clues?
          Code:
           
          create or replace
          PROCEDURE rate is
            PaymentPeriod      NUMBER(20,2) := 11; -- Months
            AprRate            NUMBER(20,2) := 0.249; -- APR
            LoanAmount         NUMBER(20,2) :=5000;
            Numerator         NUMBER(20,2)  := 0;
            Denominator        NUMBER(20,2) := 0; 
            MonthlyAmount      NUMBER(20,2) ;
            Iterator           NUMBER(20,2) := 0;
          BEGIN
              Numerator := (AprRate/12)*LoanAmount*power((1+AprRate/12), PaymentPeriod);
          
              FOR i IN 1..PaymentPeriod LOOP
                Denominator := Denominator + (power((1+AprRate/12), Iterator));
                Iterator    := Iterator + 1;
              END LOOP;
            --
            MonthlyAmount := Numerator/Denominator;
            dbms_output.put_line ( MonthlyAmount );
          
          END;

          Comment


            #6
            I don't understand the loop you are using in

            Code:
                FOR i IN 1..PaymentPeriod LOOP
                  Denominator := Denominator + (power((1+AprRate/12), Iterator));
                  Iterator    := Iterator + 1;
                END LOOP;
            ???

            Why dont you just use -

            Code:
            Denominator := power((1+AprRate/12), PaymentPeriod) - 1;
            It's about time I changed this sig...

            Comment


              #7
              Originally posted by MrRobin View Post
              The formula is:


              Payment =

              rate x loan amount x (1+rate)^number payments
              ------------------------------------------------
              ...........(1+rate)^number payments - 1

              ...
              I'm sorry to come in with a purely negative comment, but:
              I don't know the right answer, but that answer is wrong (as the question is specified).

              Your formula is right with "rate" = the interest rate per period. Interest rate is not the same as APR. I don't know the formula for APR but it is a complex formula defined in the Consumer Credit Act, and is not the same as the mathematical interest rate (whether annula or monthly, simple or compund).

              Comment


                #8
                Originally posted by expat
                I'm sorry to come in with a purely negative comment, but:
                I don't know the right answer, but that answer is wrong (as the question is specified).
                OK <tail between legs> you have a point. Sorry.
                Last edited by MrRobin; 8 May 2008, 13:10. Reason: Whoops. My bad expat
                It's about time I changed this sig...

                Comment


                  #9
                  Originally posted by MrRobin View Post
                  I don't understand the loop you are using in

                  Code:
                      FOR i IN 1..PaymentPeriod LOOP
                        Denominator := Denominator + (power((1+AprRate/12), Iterator));
                        Iterator    := Iterator + 1;
                      END LOOP;
                  ???

                  Why dont you just use -

                  Code:
                  Denominator := power((1+AprRate/12), PaymentPeriod) - 1;
                  Thanks very much!

                  That Removing the loop did the trick,

                  However, i thought that denominator part was a series hence PaymentPeriod beign 0,1,2..... ? which was why I had the loop


                  Now all I have to do now is use the model from the OFT Act to calculate my montly payments

                  MrRobin any idea if the formular you gave is UK OFT regulation?.

                  Unless the one on the OFT page can be expressed as Payment = ...



                  cheers

                  Comment


                    #10
                    Hmm! My series expansion expertise is a little dated unfortunately and the formula on that pdf kinda makes my head hurt.

                    I think the easiest thing to do would be to do lots of checks on various different loan calculators that are already on the web.

                    However, what expat said about nominal interest rates and APRs is correct (I previously rubbished his comment which I then deleted).

                    To work out the Nominal Interest Rate from the APR, the formula is:

                    power(r,1/c)*c

                    Where r is the APR, and c is the compounding interval. What the compounding interval is depends on the loan in question, but typically it is worked out monthly (c=12)... soooo for APR of 24.9%, the Nominal interest rate is 22.4% and that is the amount you should use in the calculation from the first post.

                    http://www.mercerhole.co.uk/calc/loan/
                    http://www.pem.co.uk/common/calculators/loan.html
                    It's about time I changed this sig...

                    Comment

                    Working...
                    X