Pinterest Stumbleupon Whatsapp
Ads by Google

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 How To Use Microsoft Excel To Manage Your Life How To Use Microsoft Excel To Manage Your Life It's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.... Read More … 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 How to Visualize Your Debt & Stay Motivated While Paying It Off How to Visualize Your Debt & Stay Motivated While Paying It Off It's tough to stay motivated to keep paying off your debt, but visualizing it can make the process easier. Read More 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 The Best Personal Finance & Budget Calculators to Manage Your Spendings The Best Personal Finance & Budget Calculators to Manage Your Spendings Getting your finances in order can be tough, but having the right figures on hand makes it a lot easier. No matter you financial situation, we have the calculators you need. Read More 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:

Ads by Google

Excel Amortization Schedule -- First Steps

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:

Excel Amortization Schedule -- Categories

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:

Excel Amortization Schedule -- Table First Row

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”.

Excel Amortization Schedule -- Auto Fill Options

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:

Excel Amortization Schedule -- Table Second Row

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:

Excel Amortization Schedule -- Payoff

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.)

Amortization Planning

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 How to Get Rich: The Fastest Way to Get Out of Debt How to Get Rich: The Fastest Way to Get Out of Debt Imagine being debt free. No overdrawn balances or unpaid bills. There is a foolproof way of getting yourself out of debt. It starts with a plan and some discipline. Let's visit the other ingredients. Read More 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.

Excel Amortization Schedule -- Adjusted Payments

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.

Excel Amortization Schedule -- Change Months

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 7 Useful Excel Sheets to Instantly Improve Your Family's Budget 7 Useful Excel Sheets to Instantly Improve Your Family's Budget It's not always easy to set up a family budget, but these seven templates will give you the structure you need to get started. Read More , too.)

Get Scheduling

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

Leave a Reply

Your email address will not be published. Required fields are marked *