Loan Schedule

A loan schedule is a table that describes how we should repay (clear) the loan we have received. The table lists the number of payments we must make, the dates of those payments, and the amount to be paid on each date. In addition, the table also states how much of each payment is for repayment of the principal, and how much is for payment of interest.

In the following example, we present a loan schedule in which all of the payments are equal.

Example:

We have received a $100,000 bank loan at 10% annual interest, which is to be repaid in five equal payments at the end of each year. The bank clerk calculates a loan schedule for us (see table). We must repay $26,380 each year (column 4).

Year Amount of Principal at Beginning of Year Amount of Interest for the Year Amount of Annual Payment Amount for Interest Amount for Principal Amount of Principal After Annual Payment
(1) (2) (3) (4) (5) (6) (7)
1 100,000 10,000 26,380 10,000 16,380 83,620
2 83,620 8,362 26,380 8,362 18,018 65,602
3 65,602 6,560 26,380 6,560 19,820 45,782
4 45,782 4,578 26,380 4,578 21,802 23,980
5 23,980 2,398 26,380 2,398 23,982 -2

 

How to Read the Loan Schedule Table

Look at the first row. At the beginning of the year, the principal is $100,000 (column 2). 

The interest payment for the first year totals $10,000 (10% of the remaining principal) (column 3). 

The annual payment totals $26,380 (column 4), of which $10,000 is for interest (columns 3 and 5), and the remaining $16,380 (column 6) is for repayment of the principal.

Payment of $16,380 reduces the principal at the end of the first year to $83,620.

The amount paid annually is $26,380 (column 4).

The explanation for the second row is the same as for the first row, except that the balance of the principal at the beginning of the year is only $83,620, and the amount of annual interest paid on the balance of the principal is only $8,362.

A loan schedule in which the payments are equal is called an Amortization Schedule. Amortization Schedules are used extensively for mortgages.

 

Comments:

1. The sums in the table are rounded up to whole Dollars, and the remainder at the end of the fifth year is therefore $2 (the bank collected $2 more from us). Had the sums not been rounded off, the balance would have been exactly 0.

2. In this article, you will not learn how the bank clerk calculated the amount of the annual payment in the Amortization Schedule.  We will note, however, that he has written a booklet from which, depending on the loan terms, he derives the figures in the loan schedule.

 

DIY Spreadsheet

Click below to download a loan amortization table spreadsheet, that would enable you to calculate your own loan payments.

[gfs_button url=”http://gfs-upload-media.s3.amazonaws.com/DIY_Finance/spreadsheets/DIY_Loan_Amortization_Table.xlsx” target=”_self” existing_buttons=”gfs-main-download-button”]Download Spreadsheet[/gfs_button]

 

Test Yourself!


 

You may also be interested in getting some financial education, you can start by reading this great article on Investments For Beginners and move on from there to one of our leading Online Finance Courses. In any case, getting educated in the financial world can only benefit you especially in the long run.