The 2 Best Amortization Schedule Excel Templates for 2024

Fact Checked By Cindy Wong

Let’s talk about amortization schedule Excel templates. I have a few of them already made. They help visualize the outstanding balance of a loan over time, especially when comparing basic interest loan offerings or home loans.

Amortization Schedule Spreadsheet Templates

In this article, I’ll explain the benefits of an amortization schedule and how to create one in Microsoft Excel. To make things easier, I have also created two amortization schedule Excel templates for you to download.

I’ve already done the hard work and figured out how much each scheduled interest payment will cost in your loans so you can sit back and relax in your new home.

To download your amortization schedule Excel templates, click the links below.

What Is a Loan Amortization Schedule in Excel?

Loan amortization is paying off debts like loans and mortgages by making regular payments until they are entirely paid back. The schedule shows how much interest and principal you have/will have paid off throughout the life of the loan.

In the early stages of the loan, you will mostly pay off interest, and in the later stages, you will pay off the principal loan.

An amortization formula Excel template makes creating and viewing an amortization schedule easy.

Benefits of Using an Amortization Schedule in Excel

Using an amortization Excel template with a built-in calculator has many benefits, including:

  • Easier Calculations: You can set up formulas to do all the calculations, then change the details as needed and avoid user errors.
  • Analysis: Excel makes it easy to analyze data at a glance.
  • Comparison: Using the Excel amortization schedule template, you can compare the total cost of each loan plan, including the interest and additional fees.
  • Planning: The loan calculator can help you pick the right loan or decide about refinancing.

Related Reading: Google Sheets Mortgage Calculator: Free 2024 Template

How To Create an Amortization Schedule in Excel

Making an amortization schedule in Excel from scratch isn’t as complicated as you think. Here is how I made my amortization schedule Excel template:

  1. Open Excel and create a new spreadsheet.
Amortization schedule Excel—Add the input headings.
  1. Add the following headers to cells for a basic amortization schedule Excel template:
    • Add cells for the Total Years, Payments per Year, loan Amount, and Interest Rate (fixed rate only).
Insert the table headings
  1. Now, we can start making the table to show the data.
    • Add the column labels for the Payment No., Amount, Principal, Interest, and Balance Left.
Add the payment numbers GIF
  1. For the Payment No column, write 1 in the first cell in the column, then move your cursor to the bottom right part of the cell.
    • Hold the Ctrl key on your keyboard.
    • Then, click and drag to fill the cells below until you have the total payments.
Add the amount formula
  1. To calculate the Amount, use the following Excel PMT function:
=PMT(Interest Rate / Payments per Year, Total Years * Payments per Year, Amount)

Make sure to use absolute references (by pressing F4 after you click) when referencing the input boxes at the top; they should look like the screenshot below.

Add the principal formula
  1. To calculate the Principal, use the following PPMT function:
=PPMT(Interest Rate / Payments per Year, Payment No, Total Years * Payments per Year, Amount)

In this formula, the Payment No references are in the cell in the same row.

Add the interest
  1. To calculate Interest, use the IPMT function:
=IPMT(Interest Rate / Payments per Year, Payment No, Total Years * Payments per Year, Amount)

Here, the Payment No for the formula is also referenced in the same cell and row.

Add the balance left formula
  1. To find the balance left, add the amount in Principal in the same row to the Amount.

Note: Remember to use absolute references for cells with a fixed location. If you forget, use the Excel autofill feature to fill the cells with formulas automatically.

Short on time? Don’t miss my latest recorded video on using my amortization schedule Excel templates. Click the “play” button to watch!

Related Reading: How To Calculate Google Sheets Compound Interest

Best Mortgage Amortization Schedule Excel Templates for 2024

If you don’t want to make a template from scratch, don’t worry because you can use one of mine that I’ve built already. While these are aimed at mortgages, you can customize them according to your situation by editing the number of years and loan type, such as auto loans or unsecured personal loans.

Ensure you click “Edit a copy” if you’re using the online version of Excel or “Save as” if you use an offline Excel type.

Click edit a copy

Simple Loan Amortization Schedule

Simple Loan Amortization Schedule GIF

Access Template

This amortization schedule Excel template is for fixed monthly payments. You can specify the loan conditions, and Excel will calculate everything else automatically.

When you open the loan amortization Excel template, you will see two distinct sections.

You can add your loan details in the first section. These include the Total Years, Payments per Year, loan Amount, and Interest Rate.

Once you have added all the details, the Excel spreadsheet will recalculate and show the monthly payment details. These details include the Payment number and the Amount to be paid monthly in the first two columns.

The following two columns contain the Principal and Interest amounts, which comprise the total monthly payment amount. The last column shows the total Balance Left.

I recommend using this amortization spreadsheet in Excel if you’re looking for a simple template for calculating payments.

If you need to extend the life of the loan by more than 30 years, you can do so by clicking the “30-Year Mortgage” tab at the bottom of the spreadsheet. Then, select the bottom line to click and drag it to the cells beneath.

Similarly, you can delete rows from the spreadsheet for shorter loans.

Excel Amortization Schedule With Optional Extra Payments

Excel Amortization Schedule With Extra Payments GIF

Access Template

Of course, while you take out loans for a specified time, it doesn’t mean you cannot repay your loan earlier. By making additional payments, you pay off the interest quicker and, therefore, reduce the life of your loan.

With this amortization table Excel template, I added a section where you can add and track the extra payments. It works the same way as the last template, but there is a cell for Extra Payments in the input section and a column that shows them in the table.

Additionally, you can use the input section if you have regular extra payments, or you can manually enter random extra payments into the table throughout the life of your loan.

Related Reading: Debt Snowball Spreadsheet Template + Guide

Adding a Moratorium Period to Your Template

To find the balance after a moratorium period, you can use the following syntax:

=({remaining balance}*{annual interest rate/payment frequency}*{moratorium period}+{remaining balance}

The above adds the interest that will accumulate while you are not paying off your loan.

For example, let’s consider that you have the following parameters for your moratorium period:

  • Remaining balance: $5800
  • Annual rate: 6%
  • Monthly repayments (i.e., 12 per year)
  • A moratorium period of 3 months

Here is what the formula would look like in Excel:

=($5800*6%/12*3+$5800)

You can add the formula to the Balance left column in the template whenever you take a break from making your payments.

For example, if you took a three-month moratorium period before the 10th repayment, you would replace the formula in F18 using the cell above as the first and last argument in the formula, like so:

Amortization schedule example GIF

As you can see in the GIF, the balance automatically updates for the rest of the loan period.

Frequently Asked Questions

Does Excel Have a Built-in Mortgage Calculator?

No, there isn’t an in-built mortgage calculator. However, Excel offers multiple formulas for creating one. You can also download a template from the web.

What Is the Formula for Calculating Monthly Mortgage Payments in Excel?

To calculate monthly payments in Excel, you can use the PMT formula. Here is the syntax for the formula:

=PMT(rate, nper, pv, fv, type)

The formula requires at least three parameters to work. These are:

  • rate (interest rate)
  • nper (number of payments
  • pv (current value)

You can also check a complete list of Excel financial functions.

Conclusion

The amortization schedule Excel template creates a detailed breakdown of loan payments over a defined period. It also explains how each payment is allocated between the interest and the principal.

Are you looking for a loan or figuring out the best way to pay down an existing one? Please tell us how you’re saving on your loans in the comments.

Also, if you found this useful, please check out my balance sheet templates.

Related Readings:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!