Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.
Getting out of debt isn’t easy — but having the right tools at your disposal helps a lot. Using an amortization schedule is a great way to see how your payments affect your debt and what effect a different monthly payment will have on how much you owe, both in principal and interest.
This article will walk you through creating a custom amortization schedule in Excel. You can use Excel to manage your entire life… so why not use it to manage your debt, too?
What Is an Amortization Schedule?
First, let’s get clear on exactly what we’re talking about here. An amortization schedule details each payment you make over the life of the loan and shows you the updated principal and interest after each periodic payment.
It’s basically a step-by-step walk-through of the life of the loan that shows payments, interest, principal, and how long it will take to pay off the entire balance.
The benefit of using an amortization schedule is that you can see exactly how long it will take to pay off your loan with a given monthly payment. If you decide to commit some extra money to paying off your loan, the schedule will show you how much sooner you’ll pay off the loan, as well as how much money you’ll have saved on interest.
Creating Your Own Amortization Schedule
For the purposes of demonstration, we’ll create a relatively simple amortization schedule in Excel (you could also use Google Drive, or one of the online amortization calculators available). Let’s look at a sample student loan: we’ll say $40,000 with an interest rate of 6.8%, paid off over five years. Let’s get that into the spreadsheet first:
To get the payment value, use the following formula:
=ROUND(PMT($B2/12, $B3, -$B1), 2)
The PMT (“payment”) function takes the principal balance, interest rate, and number of months in the loan, and spits out the payment you’ll need to make to pay it all off. In the case above, 60 payments of $788.28 will cover the $40,000 balance as well as the accrued interest.
Now, let’s get that into a more useful table. Here are the following categories we’ll use for this particular amortization schedule:
If you want a simpler table, you can just include the Period, Payment, Cumulative Principal, Cumulative Interest, and Ending Balance columns; you can also replace the cumulative sections with the non-cumulative ones.
Now, let’s start filling in the table! Here are the formulas you should use for each of the columns:
- Period: month and year of first payment
- Beginning Balance: =$B$1
- Payment: =$B$4
- Principal: =C7-E7 (this won’t show anything meaningful until you’ve also filled the next column)
- Interest: =ROUND(B7*($B$2/12), 2)
- Cumulative Principal: =D7
- Cumulative Interest: =E7
- Ending Balance: =B7-D7
In our example, we end up with this:
Next, use auto-fill to put in as many dates as you’d like (you can do all 60 to start, or just the first few to see how it works). Once you’ve dragged the auto-fill box down, select the options box and select “Fill Months”.
Now we can fill in the second row, which will let us automatically populate the rest of the schedule. Here’s what to put in each column (an asterisk on the column name indicates you can auto-fill it from the row above):
- Beginning Balance: =H7
- Payment*: =$B$4
- Principal*: =C8-E8
- Interest*: =ROUND(B8*($B$2/12), 2)
- Cumulative Principal: =D8+F7
- Cumulative Interest: =E8+G7
- Ending Balance: =B8-D8
Here’s what that looks like in our example spreadsheet:
Now, you can just use auto-fill to fill in the rest of the rows. Select cells B8 through H8, and auto-fill down as far as you want! I’ve filled it in through the five years that it will take to pay off the loan:
When the Ending Balance column reaches $0, the loan is paid off! (You may notice that the principal paid at the end is listed as $40,000.05, which is $0.05 more than the original balance — it’s likely due to the rounding used by Excel. It’s not going to throw anything off.)
Now that you see how the amortization schedule works, we can take a quick look at how it can help you pay off your debt faster. Few things are more motivating than seeing your debt decrease more quickly, and you can use the schedule to see how much more quickly you can get to debt-free.
There are a number of cool things you can do with amortization schedules, but we’ll just look at a couple basic things you can do here. First, let’s take a look at how increasing your monthly payment will affect how long it will take to pay off your loan and how that affects the amount of interest you pay.
To make this change, all you need to do is change the number in the “Payment” box. Let’s see what happens when we increase the monthly payment to $800.
As you can see, bumping the payment up to $800 will get the loan paid off a month earlier. You also pay a little less in interest.
Another simple thing you can change to see how your payment plan could be tweaked is changing the number of months in your plan. By changing this number, the Payment field will be updated to tell you how much you need to pay each month to get your loan paid off in this amount of time.
Let’s change ours to 40 months instead of 60.
To get the loan paid off in 40 months, you’ll need to pay $1,120.43 monthly. The entire table has been updated, so you can see how much you’ll pay in interest with this new payment amount.
(Be sure not to miss these other great Excel spreadsheets for your budget, too.)
Now that you’ve seen how to create an amortization schedule and use it to figure out how to minimize the amount of interest you pay, it’s time to get started! Get your loan information, put it into the spreadsheet, and start figuring out how you can get out of debt faster.
Have you created an amortization schedule? Did you find it useful? Share your thoughts in the comments below!
Image Credits: KUMOHD/Shutterstock