loan amortisation with erratic payments

[apgmin]

I need a loan amortisation table with erratic payments

Many times when you loan to a friend they skip some scheduled payments in between, this is a loss to me. they may also make some more payments than the monthly payments
So the table should add the skipped payment to the principal balance as on that date/ month and so extend the tenure that the principal balance gets zero at a later date. See the below example. And reduce tenure if more payment is made

ie. skipped payment should increase the tenure and pre part payment say in a month instead of $87.92 that he is supposed to pay, he pays say $200 then this should reduce the tenure by adjusting the principal balance accordingly.

PAYMENT DATE	BEGINNING BALANCE	SCHEDULED PAYMENT
09-05-2020	$1,000.00	$87.92
09-06-2020	$920.42	$87.92
09-07-2020	$840.17	$87.92
09-08-2020	$759.26	$87.92
09-09-2020	$677.67	$87.92
09-10-2020	$595.40	$87.92
09-11-2020	$512.45	$87.92
09-12-2020	$428.80	$87.92
09-01-2021	$344.46	$87.92
09-02-2021	$259.41	$87.92
09-03-2021	$173.66	$87.92
09-04-2021	$87.19	$87.92

Like in the above table, where I have loaned $1000 @ 10% p.a.. I am supposed to get $87.92 per month for 12 months. But suppose the friend does not pay anything on 09-10-2020 or pays less or pays more than $87.99. this should be reflected in the table by increasing / reducing the tenure but keeping the monthly payment same ie. $87.92. The residual principal balance may be paid in the last instalment.

 

[yky]

I have a spreadsheet that can handle overpayment as you desire. When people pay more than the pre-calculated monthly payment, the payment schedule would end earlier while keeping the remaining monthly payments unchanged. If people underpay or skip a payment, the remaining monthly payments would all increase so to keep the load period the same as before. I do it this way because I found it cumbersome to have to add formulae to rows to increase the size of the amortization table. And people might do it several times, which means the table might need to be changed several times.

 

[apgmin]

I have a spreadsheet that can handle overpayment as you desire. When people pay more than the pre-calculated monthly payment, the payment schedule would end earlier while keeping the remaining monthly payments unchanged. If people underpay or skip a payment, the remaining monthly payments would all increase so to keep the load period the same as before. I do it this way because I found it cumbersome to have to add formulae to rows to increase the size of the amortization table. And people might do it several times, which means the table might need to be changed several times.

can you please send me the file

 

[yky]

Here is the file.

 

[apgmin]

I have a spreadsheet that can handle overpayment as you desire. When people pay more than the pre-calculated monthly payment, the payment schedule would end earlier while keeping the remaining monthly payments unchanged. If people underpay or skip a payment, the remaining monthly payments would all increase so to keep the load period the same as before. I do it this way because I found it cumbersome to have to add formulae to rows to increase the size of the amortization table. And people might do it several times, which means the table might need to be changed several times.

Can you try to make it so that the load period increases instead of payment amount and also it considers the loss of interest due to nonpayment of that monthly payment

 

[yky]

Can you try to make it so that the load period increases instead of payment amount and also it considers the loss of interest due to nonpayment of that monthly payment

Second request first. I think the interest on the non-payment is usually small because you are talking about the interest on the unpaid interest. The principle is carried over to the next month and the interest of the next month is calculated accordingly. That part doesn't bear non-payment interest. It's only the non-paid interest that would bear interest. That said, I'm not in finance business. I need to consult people in that field to see if my thinking is correct.

I don't know about the first request. I thought it very difficult to increase loan period. That's why I chose the easy way, changing monthly payment instead of loan period. I concur that for human being, it's easier to keep the monthly payment constant but for spreadsheets, it's easier to keep the loan period constant.

I wrote that macro long long long time ago, in the beginning of the last decade of the last century. Not sure I still know how to write macro.

I'll give it some thought but there is no guaranty anything useful would come out. Actually, probably nothing would come out because if I do it again, I'd do it in VBA, not macro. That means I'd need to start the whole thinking process again, not just doing some patches.

 

[apgmin]

Generally, if the payment is not made, then there are 2 scenarios.
1. the payment is totally not made
2. Less payment is made on that particular month

In both cases, the treatment will be different.

Say $100 is the monthly payment, if the person pays say $60 then the interest for 1 month will be charged on the balance $40 and not on the entire $100

The issue is if the payment amount is changed and not the payment duration, then we need to again change the bank instructions. however if the duration increases, then at the end only some more cheques need be collected from the person.

 

[yky]

Thanks. I didn't think of partial payment.

In your example of $100 monthly payment and actual payment of $60, there are two scenarios too. 1) if the interest payment is less than $60, then the extra payment (60-interest payment) goes to principle payment and principal amount is reduced somewhat, not as much as a full payment would. The interest payment for the next month is easy to calculate. The macro I wrote would generate different sheets. One of them would calculate interest by taking the principle from the previous month along with interest rate and loan period and doing the math to come up with interest payment for the next month. So, in the case, the calculation of interest for the next month is no problem, provided there is no penalty for partial payment. 2) if the interest payment is more than $60. Then, the actual payment doesn't cover the interest payment. What does one do in this case? Take the unpaid interest and add it to principle? Let's say the principle, up to this particular month, is $10000 and monthly payment is $100 in which principle payment is $20 and interest payment is $80. After receiving $60, there is $20 of interest payment unaccounted. Does the principle now goes up to $1020? I don't know.

 

[yky]

I have to admit I don't know how to make a spreadsheet with varied loan period.

 

[apgmin]

Ok, Can you make the table such that if $100 is the emi, if totally not paid less paid, then the entire $100 or the balance amount which is paid less will be added to the end of the tenure along with the amount equal to rate of interest selected for 1 month for the total $100 or the balance amount

If the amount paid is more than the EMI of $ 100 then simply the principal gets reduced from the balance principal for the excess amount paid