# Thread: Actuarial Loan Settlement equation

1. ## Actuarial Loan Settlement equation

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

2. ## Re: Actuarial Loan Settlement equation

Hello,

And what are the data you already have ? I mean what are the constants you know in the formula ?

3. ## Re: Actuarial Loan Settlement equation

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:

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
To keep it short (I'm a 1 finger typer!), I'm using a loan of $1000 repayable 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.

4. ## Re: Actuarial Loan Settlement equation

Moo,

Thanks for looking in on this....The constants will be monthly repayment, loan duration, term remaining and original loan amount.....Thanks.

6. ## Re: Actuarial Loan Settlement equation

Hi Wilmer - can you help me understand how to program this,
any chance you can post the "basic" code plz

thanks Zima

7. ## Re: Actuarial Loan Settlement equation

Let me know what it is you'd like an example of in Basic;
calculate a loan payment?

Post a FULL PROBLEM.

8. ## Re: Actuarial Loan Settlement equation

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
Well, you seem to be asking for the coding in Basic that produced the above.

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.

9. ## Re: Actuarial Loan Settlement equation

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

10. ## Re: Actuarial Loan Settlement equation

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

11. ## Re: Actuarial Loan Settlement equation

Hi Wilmer - thank you, very much appreciated

12. ## Re: Actuarial Loan Settlement equation

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?