Hello,
And what are the data you already have ? I mean what are the constants you know in the formula ?
I’m looking for some assistance in deciphering the settlement formula detailed in the link below so I can put this into practical use in a spread sheet. What I am looking to achieve is being able to calculate a loan settlement value every month throughout the duration of a loan agreement so the output can be plottedon a graph. Whilst reasonably competentin Excel, this level of mathematical formula is proving a challenge. Any help is appreciated.
Best regards,
Appleman
The Consumer Credit (Early Settlement) Regulations 2004
Calculation of the amount of rebate
4.-(1) The amount of the rebate is the difference between the total amount of the repayments of credit that would fall due for payment after the settlement date if early settlement did not take place and the amount given by the following formula
If regulations need to be read, suggest you get a lawyer!
If I understand what you're trying to do, this should be quite sufficient:
To keep it short (I'm a 1 finger typer!), I'm using a loan of $1000 repayableCode:Interest calculated Interest precalculated Refund if precalculated 0 1000.00 | 1316.92 | 316.92 1 -329.23 120.00 790.77 | -329.23 987.69 | 196.92 2 -329.23 94.89 556.43 | -329.23 658.46 | 102.03 *** 3 -329.23 66.77 293.97 | -329.23 329.23 | 35.26 4 -329.23 35.26 .00 | -329.23 .00 | .00
over 4 years, at interest of 12% annually; payment works out to $329.23.
The above can be quite easily programmed; it is in Basic anyway;
I presume just as easy in Excel.
*** as example, if borrower pays off at end of 2 years, a "rebate"
of $102.03 is due if he remits $658.46; notice that this equals the
interest left which is 66.77 + 35.26 = 102.03; see the 3rd and 4th
payments in the section where interest is not precalculated.
(or simply the difference between the 2 amounts owing: 658.46 - 556.43)
I'll assume you'll follow what I did; if not, come back with questions.
Zima, you're posting on a thread that's about 18 months inactive.
Start your own thread: then it won't get lost!
Let me know what it is you'd like an example of in Basic;
calculate a loan payment?
Post a FULL PROBLEM.
Well, you seem to be asking for the coding in Basic that produced the above.Code:Interest calculated Interest precalculated Refund if precalculated 0 1000.00 | 1316.92 | 316.92 1 -329.23 120.00 790.77 | -329.23 987.69 | 196.92 2 -329.23 94.89 556.43 | -329.23 658.46 | 102.03 *** 3 -329.23 66.77 293.97 | -329.23 329.23 | 35.26 4 -329.23 35.26 .00 | -329.23 .00 | .00
It's assumed that loan amount (1000.00) and the payment (329.23) are givens;
also the number of payments (4) and the rate (12% = .12).
100 a=1000 : p=329.23 : n=4 : r = .12
110 b=p*n [that'll be the 1316.92 above]
120 FOR k = 1 TO n [means loop n times]
130 i=a*r [calculates actual interest at each payment]
140 a=a-p+i [updates actual loan balance]
150 b=b-p [updates precalculated loan balance above]
160 d=b-a [updates refund column above]
170 PRINT k,p,i,a,p,b,d [prints out as shown in above]
180 NEXT k
That's it.
I'll REPEAT: this assumes refunds are given ONLY on last day of month.
Hi - thank you for posting this, am playing with the numbers now
an example of what i'm trying to achieve is on this website Settlement Calculator
At this link (given at start of this thread):
The Consumer Credit (Early Settlement) Regulations 2004
I agree with the balances owing of 3984.00 (12th month) and 3893.17 (13th month).
Shown is settlement required after 12 payments = 4067.62.
This calculation assumes 28 out of 30 days.
At the calculator link you're using, required is 4068.91.
Negligible difference. Can't tell what is used:
are they averaging 30day and 31day months?
All I can do is suggest following; these would be the "givens":
a = amount of loan (5000)
r = APR (14%)
p = monthly payment = 134.57
n = number of payments before settlement (12)
d = days in month (30) : could also be 28 (Feb) or 31
Step 1 (get monthly rate i):
Formula: i = (1 + r/100)^(1/12) - 1
so: i = 1.14^1/12 - 1 = ~.0109788
Step 2 (get loan balance b at month n+1):
Formula: b = a(1+i)^(n+1) - p[(1+i)^(n+1)-1]/i
so: b = 5000(1.0109788)^13 - 134.57(1.01090788^13 - 1) / .01090788 = 3893.17
Step 3 (calculate settlement amount s)
Formula: s = b(1+i)^(28/d) + p
so: s = 3893.17(1.01090788)^(28/30) + 134.57 = 4067.62
Hope that helps...bonne chance
Hi, have been running the equation with some "real" data
and an issue im having is that the rate I have makes the settlement figure different from the calculator at financecalcs.co.uk
I notice this calculator does not need you to enter a rate or APR.
Is it possible to adjust the equation to mirror this?
thankyou in advance MG
p.s can start a new thread if preferred