Excel Template for a Loan Amortization Schedule (2024)

1.

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

See Also

Create a loan amortization schedule in Excel (with extra payments)Optimizing with Excel Solver: Student Loan PaymentsMastering Formulas In Excel: How To Calculate Loan Payment FormulaExcel Tips: Analyzing Loans in Excel

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 cellA8 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 cellB6, 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 cellB1 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 cellC6:

=B6*$B$2

Note! The $-sign before the letter- and number-coordinates of the cellB2, 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

$$\begin{array}{llll}\hfill \text{Principalpayment}& =\frac{\text{Loanamount}}{\text{Numberofinstallments}}& \hfill & \\ \hfill & =\frac{350000\text{kr}}{20}& \hfill & \\ \hfill & =17500\text{kr}& \hfill & \end{array}$$

You can write $$17500$ 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 installmentD6. So, in cellD6 you write:

=$B$1/$B$3

The installment amount is the interest amount + principal payment. In cellE6, 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:

$$\text{Remainingloanafter2years}=\text{Remainingloanafter1year}-\text{Installmentyear1}$$

In cell B7, write:

See Also

How to Schedule Your Loan Repayments With Excel Formulas

=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 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.

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 columnsB1 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 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 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.

5.

Finally, it can be good to sum the amount of interest, installments and principal payment respectively cellC26, 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:

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 $$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.