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]

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

By "to the end of the tenure" do you mean to the very end, eg. if the tenure is two years, are you talking about the month of 24th? I don't know why you'd do it that way. I'd add unpaid interest immediately to principle and calculate interest accordingly. If you add it to the end, you are missing all the interest, or more accurately, interest on interest, you could collect.

I created the following table, a loan of 2,000 with an interest rate of 9% and an emi of 100. The tenure is calculated to be 22 months. If you look at the formula of interest, it's very easy. You take printicple and multiply it by interest rate and there is your interest. An emi contains two portions, principle payment and interest payment. If the borrower follows the schedule and pays the first emi, which, in this case, is consisted of principle payment of 85 and interest payment of 15, then, the principle for the next month is 2000-85 = 1915. You take 1915 and multiply it by the interest rate to get the interest for the next month.

When the borrower defaults, you don't add all of the 100 to principle. You may add the unpaid interest to the principle (as I said earlier, I'm not in the finance or banking business. So, this point needs confirmation from experts in those fields) but I don't think you can add unpaid principle to principle. Take the following table as an example. If, in the first month, the borrower defaults, I don't think you can add 100 to the principle and make it 2100. At most, I think, you can add 15, the unpaid interest, to principle and make it 2015. Let's consider an extreme case in which the borrower doesn't pay anything for the first 3 months. In that case, the borrower would owe 2000 plus the accumulated interst for three months. The point is the principle is still 2000 (I'm treating separately the unpaid interest which is added back to principle. Even though the effective principle, which is the original principle plus accumulated unpaid interest, is now larger than 2000, the original principle is still 2000).

Another point is if the borrower skips the first three payments, he/she now owes, not 2300, but 2000 + 15.00 + 14.36 + 13.72 + 3 months of interest on 15.00 + 2 months of interest on 14.36 + one month of interest on 13.72.

Back to your request, adding unpaid amount to the end of tenure, if you use the program I wrote and enter principle, emi, and interest rate, you can then play with the resulting spreadsheet. I tried it and entered 0 in emi column, the unpaid amount was added to the last month. Is this what you are after?

Sheet3
	A	B	C	D	E	F	G	H	I
12		Due	Interest	Monthly	Principal	Interest	Remaining	Yearly	Yearly
13		Date	Rate (%)	Payment	Payment	Payment	Principal	Principal	Interest
14	1	Jul-2020	9.000	100.00	85.00	15.00	1,915.00
15	2	8/1/2020	9.000	100.00	85.64	14.36	1,829.36
16	3	9/1/2020	9.000	100.00	86.28	13.72	1,743.08
17	4	10/1/2020	9.000	100.00	86.93	13.07	1,656.16
18	5	11/1/2020	9.000	100.00	87.58	12.42	1,568.58
19	6	12/1/2020	9.000	100.00	88.24	11.76	1,480.34	519.66	80.34
Sheet1

Cell Formulas
Range	Formula
A14:A19	A14	=ROW()-13
C14	C14	=H4
D14:D19	D14	=MP
E14:E19	E14	=D14-F14
F14	F14	=C14*AB/1200
G14	G14	=D4-E14
H14:H19	H14	=IF(MONTH(B14)=12,SUM(E3:E14),0)
I14:I19	I14	=IF(MONTH(B14)=12,SUM(F3:F14),0)
B15:B19	B15	=DATE(YEAR(B14),MONTH(B14)+1,DAY(B14))
C15:C19	C15	=C14
F15:F19	F15	=C15*G14/1200
G15:G19	G15	=G14-E15