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.
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.
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.
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.
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.
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.
- Extra Payment Mortgage Calculator - Compares making extra payments to investing.
- 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.
- Home Equity Loan Amortization Calculator
- Auto Loan Amortization Calculator
- Amortization Calculator (web-based)
Loan Amortization Resources and References
You can also find a free excel loan amortization spreadsheet in Microsoft's very own directory of free Office templates. They offer templates in Word, PowerPoint, Publisher, and of course Excel. Browse for templates at Microsoft.
Microsoft has at least 5 different templates related to Excel loan amortization calculations. Some of them use some pretty fancy Excel formulas for creating the amortization table and making it possible to manipulate the schedule by including extra payments.