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.