How to Create an Amortization Schedule Using Excel Templates

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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