Loan amortisation


Board Regular
Mar 1, 2010
Office Version
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

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.


Active Member
Oct 28, 2015
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.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics