Daily interest calculation on a 30/360 basis, considering payment occurs before schedule — how-to in Excel?

Question

I'm 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 doesn't make any unscheduled amortizations, he would have to pay the same amount of interest every month.

The lender has provided to charge a fixed amount of interest each month, so I guess the daily accrual of interests is calculated by prorating the fixed amount according to the number of days of the specific period.

I'm trying to figure out in case the debtor 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 remaining days in the period.

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

Answer 1

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.

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.