Microsoft Excel logo on a green background

If you want an easy way to view the schedule for your loan, you can create an amortization table in Microsoft Excel. We’ll show you several templates that make creating this schedule easy so that you can track your loan.

What Is an Amortization Schedule?

An amortization schedule, sometimes called an amortization table, displays 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 a payment is made.

This allows you to see the entire loan from start to finish. It’s beneficial for auto, personal, and home loans, and can help you see the results of extra payments you make or consider making.

With an amortization schedule template for Microsoft Excel, you can enter the basic loan details and view the entire schedule in just minutes.

Simple Loan Calculator and Amortization Table

For most any type of loan, Microsoft offers a handy amortization table template for Excel. It also includes a loan repayment 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 templates section of Excel 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’ll see the Simple Loan Calculator. Select the template and click “Create” to use it.

Create button for the Microsoft Excel template

You’ll see a tool tip in the top 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.

Simple Loan Calculator template with a tool tip

Enter the values in the designated spot at the top of the sheet including loan amount, annual interest rate, loan period in years, and start date of the loan. The shaded amounts in the area beneath are calculated automatically.

Simple Loan Calculator loan details

Once you enter the above details, you’ll 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 the principal and interest with the ending balance after you make each payment.

Simple Loan Calculator Excel template

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 basic, take a look at this next template from Vertex42. With it, you can manage your payment frequency, compound period, payment type, and rounding along with extra payments you make.

You also have two additional workbook tabs where you can track your payments depending on if unpaid interest is added to the balance or accrued separately.

Vertex42 Loan Amortization Schedule template

Start by entering the loan amount, annual interest rate, term in years, and first payment date. Then, use the drop-down boxes to select the additional details.

Dropdown boxes for additional loan details

Optionally enter extra payments into the schedule by date or use one of the mentioned tabs to manage your payments. You’ll also notice a handy Summary section on the top right for totals and estimated interest savings.

Loan details and summary at the top of the template

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 those bonuses you want along with a convenient chart.

Spreadsheet123 Loan Amortization Schedule template

Enter your basic information in the Loan Terms section and then use the drop-down lists to pick those additional details. Optionally, switch between the Amortization Schedule and Payment Schedule views, and turn Rounding off or on.

Dropdown boxes for additional loan details

Enter your additional payments in the schedule, check out the handy summary at the top, and see a clear picture of your loan with the combo chart.

Loan Amortization Schedule template top with chart

Home Mortgage Calculator

Specifically for mortgage loans, take a look at this template from Vertex42. You have options for fixed or variable rate loans, can view your balance at the end of a specific year with interest and principal paid, and can enter tax deduction details.

Vertex42 Home Mortgage Calculator template

The top of the template is packed with the information you need. Start by entering your basic mortgage information, select the compound period and payment frequency from the drop-down lists, and see your balance for any year.

Home Mortgage Calculator loan details and summary

In the center of the template, you have handy charts for a quick glance at your balance by year and interest rate history.

Home Mortgage Calculator charts

The amortization table at the bottom has spots for additional payments throughout the life of the loan. You’ll also see tax-related amounts if you decide to include those details.

Home Mortgage Calculator amortization section

For additional ways to use Excel for your finances, look at these functions for budgeting.

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »