If you want an easy way to see your loan schedule, you can create an amortization table in Microsoft Excel. We’ll show you several templates that make it easy to create this schedule so you can keep track of your loan.
What is an amortization schedule?
An amortization schedule, sometimes called an amortization schedule, shows the amounts of principal and interest paid for each of your loan payments. You can also see how much you still owe on the loan at any given time with the outstanding balance after making a payment.
This allows you to see the entire loan from beginning to end. It’s beneficial for auto, personal, and home loans, and can help you see the results of any extra payments you make or consider making.
With an amortization schedule template for Microsoft Excel, you can enter basic loan details and view the entire schedule in just minutes.
Simple loan calculator and amortization table
For almost any type of loan, Microsoft offers a handy amortization table template for Excel. It also includes a loan payment calculator if you’re thinking about paying off or refinancing your loan.
RELATED: How to use Goal Seek in Microsoft Excel
You can download the template, use it in Excel for the web, or open it from the Excel templates section on your desktop.
For the latter, open Excel, go to the Home section and select “More Templates.” Type Amortization in the search box and you will see the Simple Loan Calculator. Select the template and click “Create” to use it.
You will see a tooltip in the upper left corner of the sheet, as well as when you select the cells containing the loan details at the top. The schedule has sample data that you can simply replace.
Enter the values in the designated place at the top of the sheet, including the loan amount, the annual interest rate, the loan period in years, and the loan start date. The shaded amounts in the area below are calculated automatically.
Once you enter the above details, you will see the payment, interest and cost section update along with the schedule at the bottom. You can then easily see how much of your payment goes toward principal and interest with the ending balance after each payment is made.
To manage additional loans or use the calculator to estimate another loan, you can copy the sheet to a new tab.
Third Party Amortization Templates
If you want to go beyond the basics, take a look at this next template from Vertex42. With it, you can manage your payment frequency, compounding period, payment type and rounding along with any additional payments you make.
You also have two additional workbook tabs where you can track your payments depending on whether unpaid interest is added to the balance or accrued separately.
Start by entering the loan amount, the annual interest rate, the term in years, and the date of the first payment. Then use the dropdown boxes to select additional details.
Optionally enter additional payments in the schedule by date or use one of the tabs mentioned to manage your payments. You’ll also notice a handy Summary section in the top right for totals and estimated interest savings.
If you like the idea of a template that offers more like additional payments, payment frequency, and payment type, but appreciate a bit of visuals for your summary, this template is for you. From Spreadsheet123, this amortization schedule gives you the bonuses you want along with a convenient chart.
Enter your basic information in the Loan Terms section, and then use the dropdown lists to choose those additional details. Optionally, switch between the Amortization Schedule and Payment Schedule views, and turn Rounding on or off.
Enter your extra payments on the schedule, check out the handy summary at the top, and see a clear picture of your loan with the combo box.
Home Mortgage Calculator
Specifically for home loans, check out this template from Vertex42. You have options for fixed or variable rate loans, you can view your balance at the end of a specific year with interest and principal paid, and you can enter tax deduction details.
The top part of the template contains the information you need. Start by entering your basic mortgage information, select your compounding period and payment frequency from the dropdown lists, and view your balance for any year.
In the center of the template, you have helpful charts to take a quick look at your balance by year and interest rate history.
The amortization table at the bottom has spaces for additional payments throughout the life of the loan. You’ll also see tax-related amounts if you choose to include those details.
For additional ways to use Excel for your finances, check out these budgeting features.