✦Template Printable Ideas Insights

How to Make a Mortgage Amortization Schedule in Excel

By

Creating a mortgage amortization schedule can be a useful tool for tracking your mortgage payments, understanding your mortgage balance, and planning for the future. While there are numerous online calculators and software available, creating your own amortization schedule in Excel can provide a deeper understanding of the process and customization options. Here's a step-by-step guide to help you create a mortgage amortization schedule in Excel.

Before we dive into the process, ensure you have Microsoft Excel installed on your computer. Also, familiarize yourself with basic Excel functions like cells, rows, and columns, as we'll be utilizing these throughout the process.

Getting Started with Your Amortization Table

The first step in creating your amortization schedule is to set up the basic framework of your table. Open a new Excel workbook and name the first sheet "Amortization Schedule". In the first row, starting from column A, input the following headers: "Payment #", "Payment Date", "Monthly Payment", "Principal Paid", "Interest Paid", "Remaining Balance", and "Total Interest Paid".

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
Create a loan amortization schedule in Excel (with extra payments)
Create a loan amortization schedule in Excel (with extra payments)
Loan Amortization Schedule in Excel
Loan Amortization Schedule in Excel

Next, input your mortgage details in the corresponding cells. These details include your loan amount, annual interest rate, loan term (in years), and the start date of your first payment. To calculate your monthly interest rate, divide your annual interest rate by 12. For your monthly payment, you can use the mortgage calculator formula: P[ (i(1 + i)^n ) / ( (1 + i)^n – 1 ) ] , where P is your loan amount, i is your monthly interest rate, and n is your loan term in months.

Calculating the Amortization Schedule

Now that you have your basic setup and mortgage details in place, it's time to calculate your amortization schedule. Starting from Payment #2, in the "Monthly Payment" column (B), use the formula "=B2+($A2-1)*($B$1-B2)" to calculate the remaining months' payments. This formula assumes that the first payment is your starting monthly payment and that each subsequent payment decreases by the amount of principal paid in the previous payment.

Loan Amortization Schedule for Excel
Loan Amortization Schedule for Excel
loan amortization schedule excel
loan amortization schedule excel
13 Amazing Amortization Schedule Templates in EXCEL - Word Excel Fomats
13 Amazing Amortization Schedule Templates in EXCEL - Word Excel Fomats

Next, in the "Principal Paid" column (C), use the formula "=B2-D2" to calculate the principal portion of each payment. This formula subtracts the interest paid from each payment. Then, in the "Interest Paid" column (D), use the formula "=B2-C2" to calculate the interest portion of each payment.

Tracking Your Remaining Balance

To calculate your remaining balance, start with the "Remaining Balance" cell (E2). Input your initial loan amount ( Keeps track of the decreasing balance as you make each payment) Then, in the "Remaining Balance" column (E), use the formula "=E2+D2-C2" to update the remaining balance for each payment. This formula adds the interest paid to the previous remaining balance and subtracts the principal paid.

Finally, in the "Total Interest Paid" column (F), use the formula "=IF(E2<=0,"N/A",IF(E2<>"N/A","N/A",D2))" to calculate the total interest paid at the end of the loan term. This formula checks whether you've paid off your loan; if not, it calculates the total interest paid.

Loan Amortization Schedule Calculator | Plan Projections
Loan Amortization Schedule Calculator | Plan Projections
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
Create an Easy Loan Amortization Schedule in Excel & Google Sheets
the loan annotation schedule is displayed in this screenshote, with an arrow pointing
the loan annotation schedule is displayed in this screenshote, with an arrow pointing
FREE 7+ Amortization Table Samples in Excel
FREE 7+ Amortization Table Samples in Excel
How to Make Loan Amortization Schedule in Excel - ORDNUR
How to Make Loan Amortization Schedule in Excel - ORDNUR
Get the Loan Amortization Schedule Template for Google Sheets
Get the Loan Amortization Schedule Template for Google Sheets

Formatting and Reviewing Your Amortization Schedule

With your amortization schedule calculated, it's time to format and review your table. Apply border and shading to make the table easily readable. You can also convert the model to a table in Excel for easier sorting and filtering. Don't forget to hide the formula bar for a clean look.

Review your amortization schedule to ensure that the numbers appear logical and follow the correct sequence. Double-check your formulas to avoid any calculation errors. You can also add subtotals, totals, and other relevant information to your table for better visualization.

Prepaying Your Mortgage

If you plan to prepay your mortgage, you can adjust your amortization schedule to reflect this change. Remove the existing data from the table and start fresh by inputting your new, lower loan amount. Recalc

Amortization Schedule, Balloon Mortgage Calculator and Mortgage Payment Details
Amortization Schedule, Balloon Mortgage Calculator and Mortgage Payment Details
Loan Amortization Schedule Templates - Excel Word Template
Loan Amortization Schedule Templates - Excel Word Template
Loan Amortization with Microsoft Excel
Loan Amortization with Microsoft Excel