Moneyzine
/Buying a Home/Building and Using Amortization Schedules

Building and Using Amortization Schedules

Moneyzine Editor
Author: 
Moneyzine Editor
5 mins
October 4th, 2023
Advertiser Disclosure

Individuals thinking about buying a home, or even a new car, are oftentimes interested in seeing an amortization schedule for the loan. These schedules allow users to calculate, or visualize, what happens to the loan as time progresses.

In this article, we are going to first discuss how to use amortization schedules, and then talk briefly about their calculations. We've also included seven different amortization tables in spreadsheet form that users can download for free.

Using Amortization Schedules

The concept of using amortization tables has its beginnings in the accounting world. The most common reference being to straight line amortization of bonds sold at a discount or a premium. In the accounting world, to amortize a loan is to "write off," or decrease, that loan over time.

In the consumer world, amortization schedules are used to demonstrate how a personal loan, car loan or a mortgage, is paid off over time. Typically, consumers are interested in 3-year, 4-year, and 5-year amortization schedules for car loans. For mortgages, the most common schedules include those for 10-year, 15-year, 20-year, and 30-year loans.

Creating Amortization Schedules

Putting together a schedule in a spreadsheet is fairly straightforward, with the most important variables affecting the calculation being:

  • Loan Amount or Principal: this is the original amount of the loan, and is the starting point for the monthly loan calculation. As time progresses and monthly payments are made, the principal of the loan is reduced.

  • Interest Rate on Loan: this is the rate of interest charged for use of the money, and the risk of non-payment associated with the loan. This value is not the same as the Annual Percentage Rate, or APR, which includes additional costs such as mortgage points and loan origination fees.

  • Term of Loan: this is the length of time over which the loan will be paid off. This is typically stated in terms of years or months.

This process seems simple, right? In fact, to produce amortization tables or schedules, only the above three pieces of information are needed. The only other thing needed is Excel, or another spreadsheet application such as Calc, to perform the calculations.

Calculations or Formulas

From the above three pieces of information, it's possible to produce all of the calculations or formulas needed to produce an amortization table. The most common format for these tables includes columns of data containing the following information:

  • Month: this column reflects the payment of the loan over time. For example, a 36 month car loan will use an amortization table with 36 rows of information; one for each monthly payment.

  • Loan Payment: perhaps the most mysterious calculation used in an amortization schedule because it relies on a complex formula. Fortunately, Excel and other spreadsheet products have this formula built into the application. From this complex calculation comes the monthly payment on the loan.

  • Principal: this column of information shows the amount of the monthly payment that is applied towards reducing the principal of the loan.

  • Interest: this column shows the amount of the monthly payment that is associated with interest charges. The principal reduced, plus the interest charged, will always be equal to the payment amount.

  • Balance: this is usually the final calculation performed in the table. This is the remaining balance left on the loan, or the principal balance.

Amortization Schedule Downloads

Now that we've explained the elements that go into the calculation of an amortization schedule, and what the output looks like, listed below are the seven most common amortization tables available for download. Each of these tables is in an Excel spreadsheet format and can be easily imported into a variety of spreadsheet applications including OpenOffice or LibreOffice Calc.

The first three tables are those typically used for car loans, which are a shorter term than home loans:

These next four schedules are larger tables, which are better suited for use in a longer term loan such as a mortgage:

Each of these spreadsheet models allows the user to change all the variables of a loan. They are fully functional tools that even allow users to print out their schedules after inputting their data.

Online Amortization Calculations

We also have an online amortization calculator for users looking for an easily accessible alternative. Once again, this calculator allows users to vary the term of the loan, interest rate, and comes complete with instructions.

Reading Tables

Earlier, we explained the typical calculations, and columns of information appearing in an amortization table. We'll finish this article with a quick example that explains the process of reading one of the tables provided for users to download.

Example

Here we're looking at the 3 year amortization schedule. At the top of the sheet is a section that has three inputs appearing in blue. In this example, we have a $5,000 loan for three years at 6.0%. The monthly payment amount is $152.11. From this payment, $127.11 is used to lower the remaining balance, and $25.00 goes towards interest charges. The balance on the loan is calculated after the first payment, and is $4,872.89.

Notice the amortization spreadsheet also has an input labeled Extra Payment to Principal. Here's how this works: Let's say that a borrower decided they wanted to include an extra $50 in each of their monthly payments; these spreadsheets have the ability to calculate these variables too. In this example, the extra $50 is applied directly to the principal. Reading the amortization table goes like this: If the borrower paid an extra $50 per month, then the loan would be paid off in 26 to 27 months. This is the point where the Balance on the loan goes below zero.


About the Author - Building and Using Amortization Schedules


Related Content

Are You a Financial Disaster? The Real State of Your Finances
Have you ever heard the saying, “If you don’t have a goal, then you’ll wind up hitting it with amazing accuracy.”? In other words, if you have no idea where you want to be in your future life, then it really doesn’t matter what you do because wherever you end up will match your goal…which apparently was never important enough for you to think about in the first place.
March 13th, 2024
11 Steps to Transform Your Finances (For Good This Time!!)
The money comes in…the money goes out…and at the end of each month you have very little to show for all your efforts. Then, after buckling down for a bit and saving here and there, it feels like you’ve gained a little traction…until another unexpected bill comes along and takes your bank account down to nothing (or probably more likely, racks your credit card back up to the max instead of down to zero like you were fighting for). It’s time to change this. Now is the time to transform your finances.
March 11th, 2024
5 Kiyosaki Hacks to Make You Rich: Are You Missing Out?
Have you ever considered the way you think about money could be the key to building your wealth?
February 22nd, 2024
50/30/20 Budget Spreadsheet: How To Use the 50/30/20 Rule
Do you ever run out of money before the end of the month? Or stare in disbelief at your dwindling balance and wonder where it’s all gone?
February 26th, 2024
Joe Biden’s Net Worth in 2024: President’s Path to a Millionaire
Although estimates vary, Forbes most recently put the US president’s net worth at $10 million — less than one-quarter of the $41 million some Republican supporters claimed the president was worth. Biden’s net worth had gone up since he assumed office, but rather than a pay increase, Forbes’s analysts say it had to do with his real estate portfolio growing in value in recent years.
January 31st, 2024

Contributors

Moneyzine 2024. All Rights Reserved.