Colorful House of Math logo
Log in

Excel Template for a Loan Amortization Schedule

Here is an instruction on how to make an overview of an amortizing loan in a spreadsheet.

Note! Remember that the principal payments are always the same amount with an amortizing loan.

Excel Instruction

Making an Overview of a Serial Loan

1.
Open a new spreadsheet and enter the values for the “loan amount”, “interest” and “repayment period”.
2.
Set up the “skeleton” for the table. You’ll need columns representing “year”, “remaining loan”, “amount of interest”, “principal payments” and “installment”. The number of rows is determined by the length of the repayment period.
3.
Enter the formulas in year 1, as well as the remaining loan in year 2.
4.
Copy the formulas of the corresponding columns all the way down to the last payment row.
5.
Sum the columns “amount of interest”, “principle payment” and “installments”.

Example 1

You are taking up a $350000 amortizing loan, with an interest of 2.1% over a twenty-year period. Make a table with an overview of the loan in Excel.

1.
The first thing you do is to enter the relevant numbers for your loan:

Spreadsheet in Excel presenting loan details

2.
The next thing you do is to make the “skeleton” of the table above the repayment of the amortizing loan. This table need the columns “year”, “remaining loan”, “amount of interest”, “principal payments” and “installment”.

You need to make a row for the years 1 through 20. Instead of making a row for all the integers manually, you can use a clever feature in Excel that does the job for you. Enter the numbers 1, 2, 3 as shown below, and then mark the cells.

Spreadsheet in Excel presenting the skeleton of loan overview

Then press and hold the little green square in the bottom right corner of cell A8 and pull it all the way down till you see the number 20. (A small gray square will count the number of rows you pull it).

3.
The remaining loan the first year is the original loan amount. You can enter $350000 in cell B6, but you have already typed the number in cell B1 in the spreadsheet. A more elegant solution is to use the functionality of the spreadsheet by typing in cell B6:

=B1

This way you only need to change the value of cell B1 if you want to change the original loan amount.

The interest amount of year 1 will be the same as the remaining loan of year 1 multiplied by the interest. To calculate the interest of year 1, enter into cell C6:

=B6*$B$2

Note! The $-sign before the letter- and number-coordinates of the cell B2, is to use the functionality of the spreadsheet that allows you to copy formulas. This will be explained more closely below.

The principal payments of an amortizing loan are always equal. You can easily calculate the amount Principal payment = Loan amount Number of installments = 350000kr 20 = 17500kr

You can write $17500 in the cell for principal payment year 1, but if you wish to change the original loan amount, you need to change the installment cell as well. Therefore, it can be wise to write the calculation of the installment in the cell of the installment D6. So, in cell D6 you write:

=$B$1/$B$3

The installment amount is the interest amount + principal payment. In cell E6, where the installment amount of year 1 is, write:

=C6+D6

You’ve now spent some time on just filling out a few cells, but soon, you’ll experience the spreadsheets magic! The last part is to enter the remaining loan of year 2 is going. The calculation goes as follows:

Remaining loan after 2 years = Remaining loan after 1 year Installment year 1

In cell  B7, write:

=B6-E6

Spreadsheet in Excel presenting details for the first year for the loan

Spreadsheet in Excel presenting formulas used

4.
This is when the fun starts! You are now to copy the formula for remaining loan by marking B7. Then you pull the small green square and pull it all the way down to the 20th year. The way Excel thinks is that the formula in the marked cell is to be copied and performed in the cells below that belong to the same column. Excel sees that the formula in the marked cell consists of subtracting the number in the cell that’s one cell up and two to the right, from the number in the cell above.

Spreadsheet in Excel presenting formulas for further loan calculations

Note! Every row depends on the row above, so you may notice that the numbers in your spreadsheet could be incorrect until you are done with all the columns.

Now you can copy the formula inD6 in all the cells below in the installment column. The difference between the installment column and the remaining loan column is that you always want to refer to the columns B1 and B3 in the installment column. To let Excel know that you always want to use these two cells instead of moving downwards, write the $-sign before the letter- and number coordinates of the cells. So again, mark cell D6 and pull it down to the 20th year.

Now you can see that the numbers in the remaining loan column are correct.

Spreadsheet in Excel presenting development of the loan

Do the same with the columns for amount of interest and principal payment. That is, mark C6 and pull it down to year 20, and then mark E6 and do the same. Notice that in the column for amount of interest you want to use the remaining loan column in the same row, but always the same interest (B2). Therefore, use the $-sign when referring to the interest in the formula in C6, but not when referring to the remaining loan.

Spreadsheet in Excel presenting all calculations for the loan

Spreadsheet in Excel presenting formulas used

5.
Finally, it can be good to sum the amount of interest, installments and principal payment respectively cell C26, D26 and E26. In  C26, write:

=SUM(C6:C25)

Do the equivalent for the columns for installment and principal payment. The final spreadsheet will then look like this:

Spreadsheet in Excel presenting complete loan calculations

And with the formulas:

Spreadsheet in Excel presenting formulas used

The sum of the installments should be equal to the loan amount, and you can see that this is the case here. The sum of the interest amount will equal to what you have paid in interest, which is $77175, and the sum of the principal payments are all the money you have returned to the bank, which is $427175. In other words, an amortizing loan of $350000 cost you $427175 after 20 years.

Want to know more?Sign UpIt's free!
Next entryWhite arrow pointing to the right
Excel Template for a Fixed-rate Mortgage

Excel Template for a Loan Amortization Schedule

Economy iconEconomyStatistics iconStatistics