BM10.1 – Creating an Amortization Table

Chapter 10, Lesson 1

In this lesson students will:

  • understand the process of amortization for a loan involving equivalent payments
  • learn the process of setting up an amortization table by hand
  • determine the size of a final payment which may have a different size than other equal-sized payments
  • determine the total amount of interest paid on a loan involving equal-sized payments
  • learn the process of setting up an amortization table using Google Sheets

Amortization Fundamentals

In this lesson we will explore how to set up a table to determine detailed information regarding the payments of an annuity. The process of spreading out a loan into equal-sized payments over the term of the loan is called amortization. When we have a fixed rate loan, we can determine how much of our equal-sized payment goes toward the interest portion of the loan, and how much goes toward to paying down the principle amount of the loan. We will explore this process of breaking down our equal-sized payment by using an amortization table.

Creating an Amortization Table by Hand

When setting up an amortization table by hand, we will use five columns to track all of our data. In general, we move from left to right when filling out a row; except the final row we will move from right to left. The five column headings are as follows:

  • Payment Number – this number will track the number of equal-sized payments. Sine we have been using N (or n) to do this in our class, we will continue to use this variable. N begins at zero, with all entries blank except the principle amount. N will continue to increase until the loan is paid off in full with our principle balance at zero dollars.
  • Payment Size – this number will show the size of the equivalent payment. Since we have been using PMT to do this in our course, we will continue to use this variable. Quite often, our final equivalent payment will be smaller compared to the rest of our payments. We will explore in full how to determine the size of this different-sized payment below.
  • Interest Portion – this number will show us how much of our payment goes toward paying the interest accrued on the loan during the payment period. As the principle balance is paid off, we accrue less interest, so we expect the interest portion of our payment to decrease over the term of our loan.
  • Principle Portion – this number will show us how much of our payment goes toward paying off the principle amount of the loan during the payment period. As the principle balance is paid off, we accrue less interest, so we expect the principle portion to increase over the term of our loan.
  • Principle Balance – this number represents the amount that is left to pay on our loan after we have made the equivalent payment. When N=0, the principle balance will equal the principle amount borrowed. As the number of payments increases, we expect the principle balance to decrease to zero.

The easiest way to understand the procedure for setting up an amortization table is to work through a few examples, which we will do in the following two videos.

Creating an Amortization Table Using Google Sheets

Coming Soon!

%d bloggers like this: