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".
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.
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.
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