If you're curious about how much you'll pay on your mortgage over time, creating a mortgage amortization schedule in Excel is an excellent way to visualize and understand this. An amortization schedule details how much of your payment goes towards interest and how much towards the principal each period. Here's a step-by-step guide to help you create one.
Before we dive in, ensure you have Excel installed on your computer, and you're familiar with its basic functions. Let's assume you have a loan of $250,000 at an annual interest rate of 5%, paid monthly over 30 years.
Setting Up Your Amortization Schedule
The first step is to set up your spreadsheet to calculate the monthly payments, interest, principal, and remaining balance.
In the first row, create headers for each column: 'Period', 'Start Balance', 'Monthly Payment', 'Interest', 'Principal', and 'End Balance'.
Calculating Monthly Payment
Use Excel's PMT function to calculate your monthly payment. This function accepts five arguments: rate, num_periods, present_value, future_value, and type. In our case, 'rate' is your annual interest rate divided by the number of periods per year (5%/12 for monthly payments), and 'num_periods' is the total number of periods (360 for a 30-year loan). As we're paying off the loan, 'present_value' is -$250,000.
Extending the Schedule
Next, use the auto-fill feature to extend your amortization table. Click and drag the fill handle (the small square in the bottom-right corner of the cell) down to create periods for your loan. The 'Monthly Payment' column should now display the same amount ($1,266.51) for each period.
Similarly, copy and paste the 'Start Balance' cell downward. This will be the same as the 'End Balance' from the previous period. This step ensures that the balance for each period is correctly calculated.
Calculating Interest and Principal
Now, let's calculate the interest and principal components of your payment each period.
In the 'Interest' column, use Excel's IPMT function to calculate the interest paid each period. This function also takes five arguments: rate, period, num_periods, present_value, and future_value. For the first period, 'period' is 1, and for subsequent periods, it's the cell above.
Calculating Interest
For the first period, the formula will look like this: =IPMT((5%/12), 1, 360, -250000, ). Press enter, and you'll see the interest paid in the first period. Repeat this for each period, extending the formula down as needed.
The 'Principal' column can be calculated by subtracting the 'Interest' from the 'Monthly Payment': =