Creating a mortgage amortization schedule in Excel can help you understand your loan's breakdown, track your payments, and plan your finances effectively. This step-by-step guide will walk you through the process, ensuring you grasp each concept along the way.
Before we dive in, make sure you have Microsoft Excel installed on your computer. If you're unsure, you can download a free trial or use an online version like Excel Online or Google Sheets, as the principles remain the same.
Setting Up Your Excel Workbook
First, let's set up your Excel workbook with the necessary columns to create your amortization schedule.
In Row 1, enter the following headers: 'Period', 'Start Balance', 'Payment', 'Interest', 'Principal', 'End Balance'.
Calculating Payment
To calculate your monthly payment, use the formula for an annuity: PMT(rate, nper, pv, [fv], [type]).
Assuming a 30-year mortgage at 5% interest with a $200,000 principal, your formula would be: `=PMT(5/12/12, 30*12, -200000, 0, 0)`.
Calculating Interest and Principal
For each period, calculate the interest paid using the formula: `=Start Balance * Interest Rate`.
Then, calculate the principal paid by subtracting the interest from the total payment: `=Payment - Interest`.
Creating the Amortization Schedule
Now that you've set up your workbook and calculated your payment, it's time to create the amortization schedule.
In Row 2, enter '1' in the 'Period' column. This will serve as the starting point for your schedule.
![Biweekly Mortgage Calculator in Excel with Extra Payments [Free Download] - ExcelDemy](https://i.pinimg.com/originals/37/d6/e2/37d6e2522cb76b93bad6e8f57044aebd.png)
Filling in the Schedule
In the 'Start Balance' column, enter your loan's principal amount (e.g., $200,000).
In the 'Payment' column, enter the payment amount you calculated earlier (e.g., $1,073.64).
Calculating End Balance
In the 'End Balance' column, calculate the remaining balance after each payment using the formula: `=Start Balance - Principal`.
Copy the formulas in Rows 2 and 3 down to the desired number of periods (e.g., 360 for a 30-year mortgage).
Formatting and Customizing Your Schedule
To make your amortization schedule more readable, apply some basic formatting.
Freeze the top row for easy navigation, add a border around the data, and apply conditional formatting to highlight changes in your balance.
Adding a Summary Row
At the bottom of your schedule, add a summary row to display the total interest paid and the final balance.
Use the SUM function to calculate these values, and format them as currency.
Congratulations! You've successfully created a mortgage amortization schedule in Excel. This tool will help you visualize your loan's progress and make informed decisions about your financial future. Regularly review and update your schedule to stay on track with your mortgage goals.