Amortization Schedules


Part of being a smart consumer is doing your own calculations prior to making a large purchase, especially one that requires long term financing. Over the course of the loan how much interest will I pay beyond the principal amount? If I budget to make larger payments than recommended, how much can I save on interest alone? Amortization schedules are a great, user friendly, visual tool that can help the consumer to calculate their monthly, quarterly, or semi-annual payments after taking out a loan, and most importantly set the consumer up to save a lot of money. Knowledge is empowering so lets get started!

Historical Overview and Importance Of

Amortization calculations first arrived in the mortgage industry post Depression 1930’s. At the turn of the Depression, when the financial prospects  began to look up, the Federal Housing Administration was seeking to boost residential construction by offering affordable payment plans to the middleclass, earning citizens. The amortization calculations offered purchasing power with longer term loans and more affordable interest rates (HUD Historical Background).

Prior to knowledge of amortization calculations, many borrowers were left in the shadow’s of the lenders. Now that we have such accessible technology and user friendly guides, we as the consumers calculate just as a lender would do. If the borrower has done their part, this access to knowledge allows no room for scams or misunderstanding about the terms of the loan.

As you’ll see in the lesson to follow, amortization calculations have expanded out of the mortgage industry and can by applied to any loan. With the dawn of the PC and the World Wide Web becoming a commonplace, users everywhere are able to share technology. This collaboration has led to the formation of free templates for download such as the amortization template we will use.

Tools/Technology Required

  • A high speed internet connection
  • Basic Microsoft Excel data input skills (Entering Data Into Excel)
  • Microsoft Excel 2003 or later


  1. Students will review basic financial terminology in preparation for the loan amortization tutorial.
  2. Students will view loan amortization tutorial to learn proper usage of an amortization schedule template and how to alter values for desired outcomes all using Microsoft Excel.
  3. Students will download their own loan amortization excel spreadsheet at Vertex42: The Guide to Excel in Everything.
  4. Students will choose a dream purchase that must be financed and illustrate their payment plan by using the amortization schedule. Examples include but are not limited to; first car, first home, rental/vacation home, dream vacation, student loan payback, or business start-ups.
  5. As a result of this lesson, students will have gained  the skills necessary in understanding the purpose and applicability of an amortization schedule in their own life.

1. VOCABULARY REVIEW – Financial Terminology

Loan Amount – Total amount of money borrowed.

Annual Interest Rate – The percent interest accrued per year on the duration of the loan.

Term of Loan – The amount of years you have to pay off the loan. Is their a required deadline provided by your lender or financial institution? Do you have a personal goal to have this loan paid off prior to the deadline?

First Payment Date – When the payment cycle is set to start or when you make your first payment.

Payment Frequency – How often payments are going to be made; weekly, monthly, quarterly, semi-annually, or annually.

Compound Period – This is most often equal to your payment frequency. This is the number of times the interest on the loan will be compounded; a monthly payment frequency is compounded 12 times per year.

Payment Type – This states at what point in time you will make your payments; at the beginning or end of the payment period.

Estimated Interest Savings – This figure states how much money will be saved in interest alone, when the loan is payed off quicker than set out in the original parameters.

2. Video Tutorial

Next, view the Loan Amortization Tutorial provided by a directory of free videos to help you learn about personal finances. We will use the exact template demonstrated in this video.


3. Download Your Own Template

After viewing the tutorial, download your own spreadsheet by visiting Vertex42: The Guide to Excel in Everything. Scroll down the page and click download now and follow the directions as prompted. This free template download is only compatible with Microsoft Excel 2003 or later.

4. Your Turn: Financing Your Dream

Using the amortization template you just downloaded, fill in the top portion of the template (loan amount, term of loan, interest rate, payment frequency, etc.). If you would like to alter the amount of your payments or enter any additional payment, adjust your values accordingly and notice what else changes after excel makes the calculations.

How long do you want the duration of the loan to last? Where can you afford to cut corners? What happens if you make a few additional payments?


I want to buy a $15,000 car. I also want to have this fully paid off in two years. The following spreadsheets are 1) purchase of the car without a down-payment and 2) purchase of the car with a $5,000 down-payment. Notice I save over $600 in interest alone when having the down-payment. (down-payment = initial addition payment)

Loan Without Down-Payment


Loan With Down-Payment



1) Car Loan Without Down-Payment

2) Car Loan With Down-Payment



5) Learning Outcomes

Hopefully by illustrating the amortization schedule in application to a goal or dream purchase, the student has gathered information essential to the feasibility of their purchase. By knowing how much one can afford in payments per period will show them the duration of the loan and how they need to budget in other aspects of their financial lifestyle in order to afford the purchase. Again, this template is very basic and with excel doing the calculations, the user can quickly make adjustments and see how their altercations have affect the outcome of the loan.

Further Reading

Free Amortization Calculator — This link is a calculator that shows only simplified calculations compared to our template, but may be a great tool for quick reference.

How Car Loan Interest Works — This link provides details on common interest rates and explains the difference between simple and compound interest.

Excel Templates — This was the source of the amortization template used in the lesson. This site offers numerous, user friendly, excel templates available for free download regarding personal finances and lifestyle.






Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *