1

Im setting up a payment spreadsheet for a loan just granted, and want to be able to make daily calculations of accrued interest.

The debtor has been given a payment schedule with an interest only structure, so he knows beforehand that, provided he doesnt make any unschedules amortizations, he would have to pay the same ammount of interest every month.

The lender has provided to charge a fixed ammount of interest each month, so I guees the daily accruance of interests calculated prorating the fixed ammount according to the number of days of the specific period.

Im trying to figure out in case the debotr makes an early payment and big enough to partially amortize the principal, then there should be a recalculation of the daily accrued interest for the remaing days in the period.

Can someone help me out on the llogic of how this should be done?

0

I am sure you must mean "The lender has provided to charge a fixed amount of interest each month", because the interest would be in proportion to the balance owed which decreases as the loan is paid down.

So taking an example of a $1000 loan, with 7.2% nominal annual interest compounded monthly using 30/360 amortised over 12 months.

With

principal     s = 1000
annual rate .  .  0.072
daily rate  .  .  0.072/360 = 0.0002
monthly rate  r = 0.0002*30 = 0.006

number of months  n = 12
monthly payment   d = s r (1 + 1/((1 + r)^n - 1)) = 86.62

the fixed payment amount would be $86.62

Case of a double-sized payment

For example, if the borrower makes a double payment in the 3rd payment.

The balance b after a normal 3rd payment (x = 3) would be

x = 3
b = (d + (1 + r)^x (r s - d))/r = 756.69

and you could recalculate the payments from the 3rd month and they would be the same

s = b
r = 0.006
n = 9
d = s r (1 + 1/((1 + r)^n - 1)) = 86.62

or calculating n from s, r & d

n = -(log(1 - (r s)/d)/log(1 + r)) = 9

However, with the double payment the principal s remaining is less

s = b - d = 670.07
r = 0.006
n = 9
d = s r (1 + 1/((1 + r)^n - 1)) = 76.70

and the payment amounts for the remaining 9 months would only be $76.70

Or the payment could be kept at $86.62 and the loan would be paid down sooner: Calculating n

s = b - d = 670.07
r = 0.006
d = 86.62
n = -(log(1 - (r s)/d)/log(1 + r)) = 7.945

The loan would be paid down before 8 further payments. So calculating the balance after the 7th further payment ...

s = b - d = 670.07
r = 0.006
d = 86.62

x = 7
b = (d + (1 + r)^x (r s - d))/r = 81.37

The balance after the 7th further payment is $81.37 and the final payment would be

b (1 + r) = 81.86

The final payment in the 11th month overall would be $81.86

You could do these calculations in Excel, or using a pocket calculator. It is not actually necessary to set up a Excel amortisation table, although it's a good check.

enter image description here

Case of an extra payment on any day

For example, if an extra payment of $100 is made 10 days after the 3rd regular payment.

As above, the balance b after the normal 3rd payment (x = 3) would be

x = 3
b = (d + (1 + r)^x (r s - d))/r = 756.69

After ten days of interest at the daily rate of 0.02%

s = b * (1 + daily rate * 10) = 756.69 * 1.002 = 758.20

Then the $100 extra payment is made

s = s - 100 = 658.20

In order to not reset the payment dates, backtrack 10 days of interest from the new balance and recalculate the payments for the original dates.

s = s/1.002 = 656.89

r = 0.006
n = 9

d = s r (1 + 1/((1 + r)^n - 1)) = 75.19

After the $100 payment the regular payments reduce to $75.19 for the remaining 9 payments.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.