Introduction to loan amortization

1. Introduction to loan amortization

Hi! My name is Brent Allen. I'm a CPA and I've been working with spreadsheets for over 15 years, including 6 years in real estate. I'm excited to get you started on loan amortization dashboards in spreadsheets!

2. What is a loan amortization table?

A loan amortization table details the relationship between periodic interest and principal payments between a borrower and a lender over the life of a loan. The schedule must also be finite; that is, the loan must have a start and end date. Calculators, like the one we will be building in this course, help to determine how much interest rates and loan length impact payments.

3. Where are loan amortization tables used?

The traditional case where a loan amortization schedule is used is on a mortgage. A mortgage provides both a borrower and a lender consistency in the payments that they make over time. An amortization schedule would not work for a credit card. One month the balance might be $1000, the next month $3000, and there might be no consistency in how much the borrower is paying. One month might be the minimum, the next month the borrower might pay off the balance in full.

4. Key elements of an amortization table

In order to create an amortization table, there are several items that we need to have in order to create the calculation of the installment payments. First, we need the interest rate - typically expressed as a nominal annual rate. We don't need to have the inflation rate to calculate real interest. We need to know the amortization period of the loan; that is, how long it will take to pay off completely, and how frequently payments are being made - monthly, weekly, bi-weekly... these are a few examples. Normally, payments are made at the end of a period. Finally - we need to know how much is actually being borrowed. And it doesn't matter what the money is being borrowed for. It could be a house, a car, a student loan - anything, really! While inflation rates and credit worthiness could impact the interest rate on the loan, the schedule can be made if the rate is 1% or 100%, so knowing anything not directly related to the loan is not required.

5. Interest example

For most of this course, we will be dealing with simple interest. This is calculated by taking a balance, multiplying by the annual interest rate, then prorating the interest for proportion of the year. There is no need to worry about calculations such as interest compounding.

6. Monthly payment example

A monthly payment is made up of 3 elements. First is the interest; that is the amount which is paid to the lender every period. Second is the principal; that is the amount that gets paid off the loan every period. This makes up the monthly payment, which remains the same throughout the loan.

7. Paydown example

Every period, a portion of the payment will go towards the principal, and a portion will go towards the loan. The principal payment will make the closing balance smaller.

8. Paydown example over time

As the closing balance gets smaller, the portion of the payment which goes to principal gets larger. The final principal payment will reduce the closing balance to zero, ending the loan.

9. Let's start amortizing some loans!

Now that we have all the terms down, let's start making some loan schedules! In the exercises that follow, you'll use your prerequisite knowledge of spreadsheets to see how loan schedules actually work.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.