Loan amortisation

[apgmin]

When we give a loan to a friend, the payments are at most erratic, some months they repay the exact emi, some months they skip all together and some months the pay excess.

now most of the loan amortisation tables show the month of payment, emi amount, principal deducted, interest deducted and balance principal.
Now in these tables we cannot insert the actual; amount we received, So if the month is skipped or paid excess or less, there is o effect on the subsequent months emi or increase in tenure.
Can some one make a table where in we can mention the actual amount repaid by the person in a particular month and if theer is variance in the emi payable and the amount paid the table recalculates from that point onward by increasing the tenure or emi amount

 

[Steve_R]

The web is awash with free Amortization templates. Find one that allows additional payments. When less than the required amount is paid, enter a negative number.

Try the Fourth down in the link below. Don't enter a number against 'Optional Extra Payments' and fill in variations as they occur, replacing the formula with a number in the 'Extra Payment' column. For example, if the template calculates $500 is the required payment, and the person pays $400, enter '-100' in the additional payments column.

28 Tables to Calculate Loan Amortization Schedule (Excel) ᐅ Template Lab
It is old. You may be able to find something you prefer.

I dislike that template because it doesn't allow interest rate changes over time, which is standard procedure, so suggest you look for another if that is a problem in your situation.
Also, interest rates don't always change on a convenient date that fits with the simple mathematics of an amortization sheet. In other words, don't rely on a simple Excel Amortization to check your bank is doing the right thing. Any Excel templates I found give ballpark figures only.

Alternatively, the bulk of the mathematics will already be done in most templates and you can insert columns and revise the mathematics to take care of interest variations and payment variations.

 

[apgmin]

THANKS A TON.
However, I checked with the table given by my bank and found a anomaly, I note that they deduct the interest part first from the payment and balance from principal so as the tenure progress, the interest amount deducted from the emi per month reduces and the principal deducted amount reduced increases
any table you know of that does this?