Loan Amortization Table in Excel
Believe it or not, a loan amortization spreadsheet was the very first Excel template I downloaded from the internet. Since then, I've discovered the great boost in productivity that can come from not having to start from scratch, and hopefully this page will help you get a head start. This page lists the best places to find an Excel amortization spreadsheet for creating your own amortization table or schedule.
Loan Amortization Table - Templates
If you want a spreadsheet for creating an amortization table for a loan or mortgage, try one of the calculators listed below. There are some of my most powerful and flexible templates. A feature that makes most of the Vertex42 amortization calculators more flexible and useful than most online calculators is the ability to include optional extra payments. And of course with a spreadsheet, you can save your results.
Amortization Schedule ▶
This spreadsheet lets you choose from a variety of payment frequencies, including Annual, Quarterly, Semi-annual, Bi-Monthly, Monthly, Bi-Weekly, or Weekly Payments. It only works for fixed-rate loans and mortgages, but it is very clean, professional, and accurate.
Home Mortgage Calculator ▶
Creates an amortization table for BOTH fixed-rate and adjustable rate mortgages. This one is by far the most feature-packed of all my amortization calculators. It has has been refined and improved over years of use and feedback received from both professionals and every-day home buyers.
Simple Interest Loan Amortization ▶
This may seem similar to the regular loan amortization schedule, but it is actually very different. This spreadsheet is for creating an amortization table for a so-called "simple interest loan" in which interest accrues daily instead of monthly, bi-weekly, etc.
Simple Amortization Chart ▶
Learn how to create a simple amortization chart with this example template.
Creating an Amortization Table
My article "Amortization Calculation" explains the basics of how loan amortization works and how an amortization table or "schedule" is created. You can delve deep into the formulas used in my Loan Amortization Schedule template listed above, but you may get lost, because that template has a lot of features and the formulas can be complicated.
To get started, I would recommend downloading the Simple Amortization Chart template.
If you are wanting to create your own amortization table, or even if you just want to understand how amortization works, I'd recommend you also read about Negative Amortization. In that article, I explain what happens when a payment is missed or the payment is not enough to cover the interest due.
See Also:
More Free Loan Amortization Spreadsheets
Listed below are other spreadsheets by Vertex42.com that use an amortization table to both display results and perform calculations.
Balloon Payment Loan Calculator ▶
This template is unique in that the amortization table ends after a specified number of payments. The final payment, or balloon payment, is the amount required to pay off in full.
Line of Credit ▶
For a revolving line of credit (such as a credit card or HELOC), interest normally accrues daily, so this spreadsheet is like the "simple interest loan" calculator except that it allows you to include additional draws besides the initial loan amount.
Adjustable Rate Mortgage (ARM) Calculator ▶
I created this one prior to the home mortgage calculator listed above, but this one may be easier to dig into if you are interested in the formulas.
- Extra Payment Mortgage Calculator - Compares making extra payments to investing.
- Home Equity Loan Amortization Calculator
- Auto Loan Amortization Calculator
- Amortization Calculator (web-based)
You can also find a free excel loan amortization spreadsheet by doing a search in Excel after going to File > New. Some of them use creative Excel formulas for making the amortization table and a couple allow you to manipulate the schedule by including extra payments. The new online Microsoft template gallery doesn't have as many loan-related templates as the old gallery, but you can still find a few in the Financial Management category.
