Creating an amortization schedule in Excel is a powerful way to track and understand the breakdown of your loan payments over time. This schedule helps you see how much of each payment goes towards your principal and interest, and how your equity in the property grows. Adding extra payments to your schedule can accelerate your payoff and save you significant interest. Let's dive into how to create an amortization schedule in Excel and incorporate extra payments.
Before we start, ensure you have Microsoft Excel installed on your computer. For this guide, we'll use Excel 2016, but the process is similar in other versions. You'll also need to understand basic Excel functions and have some familiarity with loans and amortization.
Setting Up Your Amortization Schedule
To create an amortization schedule, you'll need to input some key information about your loan. This includes the loan amount, interest rate, loan term, and the frequency of your payments.
Here's how to set up the initial part of your schedule:
- In cell A1, type "Loan Amount" and in cell B1, enter your loan amount (e.g., $200,000).
- In cell A2, type "Interest Rate" and in cell B2, enter your interest rate (e.g., 0.05 for 5%).
- In cell A3, type "Loan Term (years)" and in cell B3, enter your loan term (e.g., 30).
- In cell A4, type "Payment Frequency" and in cell B4, enter the number of payments you make per year (e.g., 12 for monthly payments).
Calculating Payment Amount
The payment amount is the total amount you'll pay each period. You can calculate this using the formula:
=PMT(B2/B4, B3*B4, -B1)
This formula uses the interest rate, loan term, and loan amount to calculate your payment amount. The result will be the total amount you'll pay each period.
Calculating Amortization Period
The amortization period is the total number of periods over which the loan will be repaid. You can calculate this using the formula:
=B3*B4
This will give you the total number of payments you'll make over the life of the loan.
Creating the Amortization Table
Now that you have the initial setup complete, it's time to create the amortization table. This table will show you the breakdown of each payment over the life of the loan.
Here's how to create the table:
- In cell A6, type "Period".
- In cell B6, type "Payment".
- In cell C6, type "Interest".
- In cell D6, type "Principal".
- In cell E6, type "Balance".
Filling in the Table
Now, you'll use Excel's fill handle to automatically fill in the rest of the table. Here's how:
- In cell A7, type "1".
- In cell B7, enter the formula for the payment amount you calculated earlier.
- In cell C7, enter the formula for the interest for the first period:
=B2*B1*(1-(1+B2)^(-B4*B3)) - In cell D7, enter the formula for the principal for the first period:
=B7-C7 - In cell E7, enter the formula for the balance after the first period:
=B1-D7 - Drag the fill handle (the small square in the bottom-right corner of the cell) down to copy these formulas for the rest of the periods.
Formatting the Table
To make your table easier to read, you can format it as a table in Excel. Here's how:
- Select the range of cells containing your table.
- Go to the "Home" tab in the ribbon.
- Click on "Format as Table".
- Choose a table style and check the "My table has headers" box.
- Click "OK".
Incorporating Extra Payments
Making extra payments on your loan can help you pay it off faster and save on interest. Here's how to incorporate extra payments into your amortization schedule:
Let's say you want to make an extra payment of $100 every year. Here's how to adjust your schedule:
Adjusting the Payment Amount
First, you'll need to adjust the payment amount to reflect your extra payments. To do this, you can use the PMT function again, but this time, you'll include the extra payment in the total payment amount.
=PMT(B2/B4, B3*B4, -B1, -100*(B3-1)/B4)
This formula includes an additional argument for the extra payment. The extra payment is $100, and it's made every year, so we divide it by the payment frequency (B4).
Adjusting the Amortization Period
Since you're making extra payments, you'll pay off your loan faster. You can calculate the new amortization period using the formula:
=B3*B4-COUNTIF(B7:B1000,">100")
This formula subtracts the number of extra payments you're making from the total number of payments. In this case, you're making one extra payment every year, so you'll pay off your loan one year early.
Updating the Table
Now, you'll need to update your table to reflect your extra payments. Here's how:
- In cell B7, enter the new payment amount you calculated.
- In cell D7, enter the formula for the principal for the first period with extra payments:
=B7-C7 - In cell E7, enter the formula for the balance after the first period with extra payments:
=B1-D7 - Drag the fill handle down to copy these formulas for the rest of the periods.
Your table should now show how your extra payments will reduce your loan balance faster and save you money on interest.
Creating an amortization schedule in Excel and incorporating extra payments can be a powerful tool for understanding and managing your debt. It can help you see the impact of your extra payments on your loan balance and total interest paid. By using this tool, you can make informed decisions about your loan and work towards paying it off faster. So, why not give it a try and start saving on your loan today?