Creating a mortgage amortization schedule in Excel can help you understand and manage your mortgage payments more effectively. This detailed guide will walk you through the process, ensuring you have a clear understanding of your mortgage's breakdown.
Before we dive into the steps, let's briefly understand what a mortgage amortization schedule is. It's a table that shows how much of each mortgage payment goes toward interest and how much goes toward principal. It also shows the remaining balance of your loan after each payment.
Setting Up Your Excel Workbook
To start, open a new Excel workbook and name it "Mortgage Amortization Schedule". In the first row, starting from column A, enter the following headers: "Period", "Payment", "Interest", "Principal", and "Balance".
Formatting your headers is crucial for readability. Select the headers, click on the "Home" tab, then "Format as Table". Choose a style you like, and ensure the "My table has headers" box is checked. This will apply formatting to your headers and allow you to sort and filter your data easily.
Entering Your Mortgage Details
In the first row under the headers, enter your mortgage details. In cell A2, start your period count (e.g., "1"). In cell B2, enter your total monthly mortgage payment. In cell C2, enter your annual interest rate (as a decimal). In cell D2, enter your loan amount. Leave cell E2 blank for now.
To calculate the interest and principal portions of your payment, use the following formulas in cells C2 and D2 respectively: `=B2*C2/(1+(C2/12))` and `=B2-C2`. The interest and principal amounts will now appear in cells C2 and D2.
Creating the Amortization Schedule
Now, let's create the amortization schedule. In cell B3, enter the formula `=B2`. This will copy the total monthly payment down the column. In cell C3, enter the formula `=C2`. This will copy the interest amount down the column. In cell D3, enter the formula `=D2`. This will copy the principal amount down the column.
To calculate the remaining balance, in cell E3, enter the formula `=D2-($D$2*($A3-1))`. This formula subtracts the cumulative principal payments from the original loan amount. Copy this formula down to the last period of your mortgage.
Customizing Your Amortization Schedule
Your basic mortgage amortization schedule is now complete. However, you can customize it to suit your needs. For instance, you can add extra columns to show the effects of making extra payments or changing your interest rate.
Making Extra Payments
![Biweekly Mortgage Calculator in Excel with Extra Payments [Free Download] - ExcelDemy](https://i.pinimg.com/originals/37/d6/e2/37d6e2522cb76b93bad6e8f57044aebd.png)
To see how extra payments affect your mortgage, add a new column for "Extra Payment". In the first cell under this header, enter the amount you want to pay extra each period. Then, in the "Payment" column, add this extra amount to your regular payment. In the "Principal" column, adjust the formula to account for the extra payment.
For example, if you're paying an extra $100 each month, in cell B4, enter `=B3+100`. In cell D4, adjust the formula to `=D3+($B4-C4)`. Copy these formulas down the columns as needed.
Changing Your Interest Rate
To see how changing your interest rate affects your mortgage, add a new column for "New Interest Rate". In the first cell under this header, enter the new interest rate (as a decimal). Then, in the "Interest" column, adjust the formula to use this new rate.
For example, if you're changing your interest rate to 4% (or 0.04 as a decimal), in cell C4, adjust the formula to `=$B4*$E3*$F4/(1+(($E3*$F4)/12))`. Copy this formula down the column as needed.
Creating a mortgage amortization schedule in Excel can provide valuable insights into your mortgage and help you make informed decisions about your payments. With this guide, you're well on your way to understanding and managing your mortgage effectively. Happy calculating!