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 year1, as well as the remaining loan in year2.
- 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”.
Example1
You are taking up a $ amortizing loan, with an interest of 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:
- 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.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 $ in cell
B6
, but you have already typed the number in cellB1
in the spreadsheet. A more elegant solution is to use the functionality of the spreadsheet by typing in cellB6
:=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 year1 will be the same as the remaining loan of year1 multiplied by the interest. To calculate the interest of year1, 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
You can write $ in the cell for principal payment year1, 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 cellD6
you write:=$B$1/$B$3
The installment amount is the interest amount + principal payment. In cell
E6
, where the installment amount of year1 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 year2 is going. The calculation goes as follows:
In cell
B7
, write:=B6-E6
- 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 wayExcel
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.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 in
D6
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 columnsB1
andB3
in the installment column. To letExcel
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 cellD6
and pull it down to the 20th year.Now you can see that the numbers in the remaining loan column are correct.
Do the same with the columns for amount of interest and principal payment. That is, mark
C6
and pull it down to year20, and then markE6
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 inC6
, but not when referring to the remaining loan. - 5.
- Finally, it can be good to sum the amount of interest, installments and principal payment respectively cell
C26
,D26
andE26
. InC26
, write:=SUM(C6:C25)
Do the equivalent for the columns for installment and principal payment. The final spreadsheet will then look like this:
And with the formulas:
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 $, and the sum of the principal payments are all the money you have returned to the bank, which is $. In other words, an amortizing loan of $ cost you $ after 20 years.
Excel Template for a Fixed-rate Mortgage