In Excel, an amortisation table is a simple sheet which details a periodic (i.e. weekly, monthly) payment on a loan. Examples of which include mortgages and car finance, and this is worked out by an amortisation calculator in Excel. Amortisation itself is the act of settling or decreasing debt through regular monthly payments.
The amortisation schedule also shows the extra interest which is being added to a loan and how it can be made smaller with each payment. It also provides other information such as a schedule of all the payments which you will need to make, enabling you to see exactly by how much each payment you make is decreasing the debt when interest is taken into account.
It can easily be made using Excel, and it is a good thing for any consumer with a loan to have. Loan Amortisation using Excel is super simple and quick.
Fully amortising loans are very common. They typically call for equal payments either monthly or annually throughout the lifetime of the loan, and the loan balance is settled following final payment.
With these types of loan, each payment consists of a principal payment and then interest added on top. Over time this slowly but surely reduces the loan balance. If extra payments are made on top of the monthly amount, the interest paid will be reduced.
How to Make an Amortisation Table in Excel
It’s very simple to do this and providing that you have all the information pertaining to your loan on-hand and ready to go, you can do it in a matter of minutes. Fire up Microsoft Excel and populate your cells like the example we have created here:
The “Loan Amount” value is the total amount borrowed, not the amount of money you have to repay. This is calculated by inserting the interest rate – 10% in this example – and the duration of the loan – 36 months – with the “Payments” section representing your minimum monthly repayments.
Populate the first three cells B1 to B3 with the information relating to your own loan and in B4, insert the following formula –
=ROUND(PMT($B$2/36,$B$3,-$B$1,0),2)
This will work out the right value for monthly repayments regardless of what values you enter in B1 to B3. This is a great way to work out how much you will be paying back over a period of time. If, for example, your loan agreement is 36 months but you want to get rid of it in 12 you can work out how much you’ll need to pay off each month to do this.
You then need to populate the columns below.
In B7, insert the opening balance and then in cell C7 type “=$B$4” and hit ENTER.
Then in cell E7 write a formula which will work out the loan’s interest amount for the balance of each new period (i.e. each month after a payment has been made.) This formula is “=ROUND($B7*($B$2/36), 2)”.
In cell D7 you need to subtract the interest value in E7 from the total payment in C7 using “=$C7-$E7”.
Finally, in cell H7 put in a formula which will take away the principal payment portion from the opening balance for the period. This is “=$B7-$D7”
Cell B8 should be used to insert a relative reference to the closing balance for your previous period. To do this, type “=$H7” and hit ENTER. Then copy cells C7, D7 and E7 and paste them over C8, D8 and E8. Copy E7 and paste it into H8.
After this, in F8, create the following formula which will tabulate the principal paid – “=$D8+$F7” and then do it again for cumulative interest in G8 – “=E8+G7”.
After doing this, complete the entire amortization schedule for your loan’s length of time by simply highlighting cells B8 to H8 and then hovering your mouse over the bottom right-hand corner of the selection. A crosshair will appear, and simply click and drag this all the way down until you have your desired number of rows. If you have done everything right, the closing balance should somewhere be £0.00 when the final payment is due.
It is important to note that the $ sign in the formulas represents relative reference and not U.S.
