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.
Table of Contents
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:
- Open Excel and create a new spreadsheet.
- 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).
- 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.
- For the Payment Nocolumn, 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.
- 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.
- 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.
- 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.
- To find the balance left, add the amount in Principalin the same row to the Amount.
Note: Remember to use absolute referencesfor 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.
Simple Loan Amortization Schedule
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
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 3months
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:
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:
- How To Remove Dashes in Excel (3 Easy Ways)
- A Guide to Excel Dashboard + Dashboard Templates
- How To Swap Cells in Excel [Easy 1 Minute Guide]
