How To Create A Debt Payoff Plan And Amortization Schedule (Free Template!)

I'm back with another nerd post, to continue my somewhat slow going money series.  It's been a while so if you need a refresher, here's where we've been so far:

Every Dollar Has A Name
Bank Account Business
Why A Monthly Budget May Be A Terrible Idea For You
Budget Basics Part 1 - How To Create A Budget & A Free Budget Template
Budget Basics Part 2 - How To Maintain A Budget
Operation Debt Smackdown aka How We Paid Off $20,000 Of Debt Last Year

Moving right along...buckle your seat belts.

In this post, I'm going to talk about how to create an amortization schedule, which is a fancy phrase for a debt payoff plan.   Here's a screen shot of the finished product, to give you an idea what we are going for, before we break it down.  This is actually the amortization schedule I have kept for Trent's car note.  Click here for a sample template of this type of schedule.

Here are the items you'll need to have handy in order to create this schedule:

1.  Loan balance (either original or current, wherever you want to start)
2.  Interest rate
3.  Monthly payment amount
4.  Monthly payment date

Here are the steps to creating this schedule:

1.  Set up your interest rate cells
Somewhere at the top of your spreadsheet, enter your annual interest rate as a decimal, aka our 9.79% interest rate translates to 0.0979 in decimal format.   This cell is really just a reference cell for the next one you'll create, which is to divide this decimal by 12.

This translates your annual interest rate into a monthly interest amount, for use in multiplying times the balance later:

2. Set up your headings
This step is easy.  Just set up heading across the top of your spreadsheet that show Date, Payment, Principal, Interest and Balance, like so:

3. Enter your beginning balance
If you are starting this schedule for a new loan (or wanting to create a history for a loan you've already been paying on), you should enter the original principal balance of the loan.  If you are jumping in in the middle and just want to track a loan from now on, you just need to look up your current balance.  You can probably do this through an online log-in, or a call to your lender could provide this amount to you.

In this example, Trent's car started out with a total loan balance of $9,825.32, which we were able to find both on our purchase documents and our online log-in.

4.  Set up your dates column
Enter your first and second payment dates, then drag the cells down to repeat the pattern and add each payment date by month, for the whole life of the loan.  So, if you make your payment on the first of the month and your loan term is 5 years, you're going to be doing a lot of dragging, 60 cells to be exact, for 60 monthly payments.  This is if you just plan on making the monthly minimum payments.  If you plan on making extra payments here and there throughout, in order to pay off a loan early, you'd simply insert an enter line for that extra payment and enter the appropriate date.  As you can see below, we just did minimum payments for three months, then started to add in extra payments when our Operation Debt Smackdown turned its attention towards this car loan.

5. Set up your payment amount column
This one is easy since you will have the same payment each month.  Just enter the monthly payment amount and copy it down the whole length of the dates column you already set up. We'll talk shortly about how this can be changed up with additional payments if you are working on paying down a loan early. That is in addition to the regular payment and doesn't go in the payment column, so go ahead and just copy down the regular payment for the length of the loan.

Note: for steps 6-8, just set up the first row and don't drag the formulas down yet.  It won't make much sense or show any values until you have all formulas initially in place.

6. Set up your principal amount column
This one requires a formula and it will make a little more sense once the interest column is also set up.  The Principal amount column represents the amount out of each monthly payment that is actually going towards principal and knocking down the balance of your loan, verses going towards interest each month.  The formula is simply your monthly payment amount minus your interest amount:

Until you enter your interest amount formula, this will just equal your monthly payment amount.  It will adjust once you actually put a formula and value is the Interest box, aka D7 in this example.

7. Set up your interest amount column
Here is where the monthly interest rate you already calculated comes into play.  The amount of interest paid each month is found by multiplying the current balance of the note times the monthly interest rate.

Note that I used the dollar signs in the interest rate cell to fix or lock that cell in the formula so when I drag it down to copy the formula, it sticks with the interest rate amount (in B3 in this example) instead of dragging that part of the formula to B4, then B5, etc.   (Ignore the fact that the first two interest amounts don't actually calculate with this formula, if you happen to be looking that hard.  We had some catch up on interest because we bought the car in August but didn't have our first payment until October 1.)

8. Set up your declining loan balance column
This is my favorite step.  It's where you really get to see that your loan is actually going away and lessening.  It's really simple.  Just take the previous month's balance minus this month's principal payment amount.

9. Drag all these formulas down through the length of the schedule.
Just grab the corner of your first cell in each column and drag it down through the length of your schedule. If all is set up correctly, you should end up with a loan balance very close to zero at the end of the schedule.

A few final notes...

The orange highlighting you see is just my way of indicating which amounts have already been paid and where we are at in the schedule.  You can take it or leave it.

If you are going to be entering extra payments, in addition to your regular monthly payment, just enter an entire additional line with the date of the extra payment and the amount.  Just hard enter your amount in the principal column.  You do not need to enter the formula because this entire amount is principal, since it is extra. Then just drag down your declining balance formula again to account for extra principal payments like this. Or if you are consistently going to pay extra each month than the regular payment, you can just enter that amount as your monthly amount instead of the minimum payment. These are where you really see your pay off progress.  It will start to bring your actual pay off date earlier and earlier and you can then delete the extra lines and dates you had set up before.

This schedule should jive with your budget schedule.   If your budget schedule shows that on December 5th you have enough cash flow to make an extra principal payment of $300, that should be entered in your budget as well as in your amortization schedule.  You can even use formulas to link between the two if you wanted.

Ok.  I think that's it.  Lots of words.  If you hung with me for this long, I commend you.  As always, let me know if there are any questions, suggestions or further explanation needed.  Happy amortizing!