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

[css_jay99]

Ok, i managed to solve the problem.

Client finaly gave me their nominal rate of interest (or whatever) which simplified the equation to:

LoanAmount = ((Premium - Deposit) * (1+Rate))
so i did not have to use the APR rate at all in the calculations

.
Thanks very much guys

css_jay99

[css_jay99]

Yes you have the APR which is great, but you need to work out the nominal interest rate to then calculate the monthly payment.

So step 1 is use the fomula in my last post to work out nominal rate

Step 2 is use the formula in my first post (but use the nominal rate, not the APR) to work out the monthly payment and the cost of the loan

I'll give that a try now and let you know

cheers

css_jay99

[MrRobin]

Yes you have the APR which is great, but you need to work out the nominal interest rate to then calculate the monthly payment.

So step 1 is use the fomula in my last post to work out nominal rate

Step 2 is use the formula in my first post (but use the nominal rate, not the APR) to work out the monthly payment and the cost of the loan

[Gonzo]

I don't think that I am going to be able to help a lot, but from my memory, the APR will include any charges, such as arrangement fees, evened out over the repayment period. Those will be in addition to the interest charges.

Have you allowed for those?

[css_jay99]

Not sure where this leaves me, But more detail.

I am building a Payment module (pricing/payment & authorisation....., payment collection)

I already have the APR amount hence dont need to work it out at all.

My client sells a servicet(e.g. car for £5000). You have the option of paying in full or pay a deposit amount (i.e 10% = £500) and the remainder (£4500) over x months installments (e.g. 11months) with APR of 24.9% applied.

So the only variable not known/to be calculated is the Monthly payment OR full cost of the loan.

If only i could break that formula down ... but its been 2 decardes since I passed Applied Maths at A levels .

Looks like i better have my indemnity insurance up to date .

Any mathematicians out there ?

css_jay99

[MrRobin]

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

[css_jay99]

I don't understand the loop you are using in

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

???

Why dont you just use -

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

[MrRobin]

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.

[expat]

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

[MrRobin]

I don't understand the loop you are using in

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

???

Why dont you just use -

Denominator := power((1+AprRate/12), PaymentPeriod) - 1;

[css_jay99]

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

any clues?

 
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;

[MrRobin]

bless,

Is r 24.9 or 24.9/100 ?


Carlton

The latter.

0.249

[css_jay99]

bless,

Is r 24.9 or 24.9/100 ?


Carlton

[MrRobin]

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:

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

[css_jay99]

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