Payment Schedule Template
- 4'446 Downloads
- 23 KB File Size
- March 23, 2019 Updated
- 0 Number of comments
- Template Contents
- The Monthly Simple Payment Schedule
- The Monthly Detailed Payment Schedule
- Tips for Getting the Most Out of Your Excel Payment Tracker
- Stay Current on Your Bills with this Excel Monthly Payment Tracker
Do you wish you had a simple way to track your monthly payments? Are you tired of forgetting about bills and incurring late fees and penalties? This payment schedule template in Excel will help you pay your bills on time. Use it, and never miss a payment again!
It’s important to note that this payment tracker is not an amortization table. Rather, it’s a way to help you budget your payments and meet your financial goals.
You can quickly change the year when you edit this template. This way you can continue using this Excel payment tracker year after year.
There are two worksheets included in this document:
- Monthly Simple - Keep track of your bills with this simple monthly tracker so you never miss a payment.
- Monthly Detailed - See when your bills are due, how much your minimum payment is, and how much you pay, with this more detailed version of the payment tracker.
These are both standalone worksheets. Spend some time looking them over and pick the one that will be most useful for you to use.
Before you proceed with either, please take some time to gather information about your bills. You need to know:
- The name of each business that you need to pay,
- The minimum payments for each bill,
- When each bill is due, and
- Whether each bill is automatically paid.
If you don’t know these details off the top of your head, go ahead and track them down. You may need to look at old statements, contact your lender, or review past payments from your bank account.
When you have this information, you are ready to get your Excel spreadsheet setup. Below, you will find directions for each of the worksheets.
The Monthly Simple Payment Schedule
Designed with simplicity in mind, the Monthly Simple Worksheet has only the minimum amount of information you need to pay your bills on time. There’s not a lot of clutter to distract you from the important data.
This template works well if you:
- Forget to make payments on a bill,
- Want to avoid late payments and charges,
- Typically pay the same amount on each bill every month, or
- Are new to tracking bill payments.
Prepare the Monthly Simple Worksheet for Your Payment Schedule
This is the simple version of our payment schedule. If you need a more robust and detailed version, please see our Monthly Detailed Worksheet, below. Our Monthly Simple Worksheet is designed for people who aren’t used to budgeting in this way, and who keep getting dinged with late charges. If that sounds like you, this Excel template can help you stay current.
There are two main sections in this worksheet: Payment and Payment Status. They are color coded to make things even easier for you. The Payment section is dark grey. The Payment Status section is light grey.
The Payment section has five columns. To help you visualize how to use it, a sample payment schedule is on the template.
Customize Your Monthly Payment Tracker
The first column is simply a numbered list. If you have more than 25 items to add, you can right click and insert an extra row. If you add a row in the middle of the payment schedule, you will need to update all the numbers below.
The second column on this payment schedule is labeled ‘Pay To,’ and is where you write down the name of each business you need to pay each month.
To maximize its usefulness, ensure your payment schedule accounts for both regular bills and debts. Regular bills include phones, garbage collection, grocery shopping, and car insurance. Debts could include your mortgage, student loans, money owed, credit cards, or payment agreement plans for medical debt.
Once you have entered each bill you need to pay, it’s time to move onto the third column. Appropriately labeled “Due,” this is where you record the due date for each bill. If a bill is due on the fifth of the month, simply enter the numeral 5. If a bill is due on the 20th, enter 20.
In the fourth column, mark if the bill is automatically paid. For instance, some student loan payments are set to come out of your bank account on a certain date. Other people put their credit cards on auto payment.
If any of your bills are automatically paid, put the date the payment comes out of your account in this column. For bills that you manually pay, you can leave this column blank or enter an X in the cell (this is what the sample shows.)
The fifth column shows how much you need to pay. I recommend recording your minimum payment here. When you look at the bottom of this column, you can see how much money you have scheduled to pay in a given month. This adds the amounts from this column together.
How to Use the Monthly Simple Worksheet for Your Payment Schedule
Now that you have recorded your payment terms, your payment schedule is ready to use.
In the lighter gray Payment Status section, find the current month. In this column, you enter your payment date. If you paid your student loan bill to Navient on January 3, go to the January column and enter the numeral 3 in the row with Navient. Here’s an example for you:
Notice that the three turned red. This means your payment was late. This happened because this Excel workbook uses conditional formatting to give you visual clues. And if the payment date in the month column is after the due date, it turns red.
If you look across the Navient row, you can see that it was due on January 2nd. Depending on payment terms, you may now be responsible for a late fee.
Continue documenting the bills you pay in the correct monthly column. If your automatically scheduled payment went out correctly, go ahead and mark it off as well.
When you look down at the bottom of each monthly column, you see a total. This cell will calculate how much money you paid on bills that month. If you paid everything, it should match the total in column E. If there is a difference, check your spreadsheet again to see which bill you still need to pay.
The Monthly Detailed Payment Schedule
If you like to be more meticulous with your budget than our Monthly Simple Worksheet allows, then you’ll probably feel more satisfied using our Monthly Detailed Payment Schedule. It’s a more comprehensive budgeting tool, allowing you to see important details such as if you’ve paid more or less than normal from month to month.
Again, this is not for bill amortization. It simply allows you to track the monthly transactions for your bills payable.
The detailed payment tracker is a good fit if you:
- Pay bills that vary from month to month,
- Want to pay extra on your bills, or
- Desire a more detailed record of your financial progress.
Prepare the Monthly Detailed Worksheet to Track Your Bill Payments
If you click to the Monthly Detailed payment tracker template, here is what it looks like:
If you’ve used our Monthly Simple Payment Schedule, or if you simply read its instructions, you’ll see that our Monthly Detailed Payment Schedule is very similar. The first column is simply a numbered list. You can add additional rows if you need to.
The second column is the Payee. It’s where you write the name of the company you need to pay. Again, you can use this to track both monthly bills (ie: sewer payment, cell phone, internet, cable) and your debts (ie: credit card, car payment, student loan).
Once you have the companies entered into Column 2, it’s time to add some details for your first month. Though this starts in January, you don’t have to wait until next year begins to get started. You can move across the spreadsheet to begin in the current month.
For each month, there are four columns:
- DD (due date),
- Amount (amount due),
- PD (paid date), and
- Paid (amount paid).
Go ahead and enter in the due date and amount for each of your bills. You can use the minimum payment amount, so you always make sure to pay at least that much.
If these details will stay the same from month to month, go ahead and copy and paste them to save time. If you will be paying a different amount each month, it may be best to manually enter these numbers each month.
For the current month, enter in each payment as you make it. You want to record the date of each payment, as well as the amount you paid.
In the example above, the bank loan payment was made on January 4th. The amount paid was $2.00. You will notice that both the 4 and the 2.00 turned red.
This is due to Excel conditional formatting. If you pay the bill late, or pay less than the minimum amount listed, the number will turn red. This helps call your attention to it. You may need to pay additional late fees as a result, so make sure you are familiar with your borrower terms and conditions.
At the bottom of each column, you can see the monthly total that you were supposed to pay. You can also see the total that you actually paid. Ideally, the amount you actually paid will be equal to or higher than the amount you were supposed to pay.
A big difference between our Monthly Detailed Payment Schedule and our Monthly Simple is your ability to record actual payment amounts. If you are trying to get out of debt, and want to do extra payments on your student loans, you can simply change the amount paid.
Doing this allows you to get an accurate picture of your financial situation. You can see what you were supposed to pay and what you actually paid.
Customize Your Monthly Payment Tracker
Like in the Monthly Simple worksheet, you need to enter your bills, your due dates, and how much you need to pay (for complete directions, see the section “Customize Your Monthly Payment Tracker,” above).
How to Use the Monthly Detailed Worksheet for Your Payment Schedule
Whenever you pay bills, take a moment to update your payment tracker. Simply find the row with the bill you paid and find the corresponding month column. Enter the date you paid the bill in the PD column, and the amount you sent in the Paid column.
If you were late on your payment, or didn’t send the minimum amount, the numbers will turn red. Your goal is to not have any red numbers throughout the year.
Tips for Getting the Most Out of Your Excel Payment Tracker
This payment schedule template can help you avoid late payment fees. To help you get the most out of this, here are four tips.
1. Look for Patterns in Your Bill Paying
Getting your bills recorded will help you to look for patterns. Are you paying your bills in full, on time every month?
Do you struggle in certain seasons? Is one bill consistently late?
You can use this information to make informed decisions about your payment methods. If you have trouble with one bill, contact the business and ask if you can move the due date or change the payment terms.
Some companies are willing to work with their customers by changing payment methods or dates. Others are not and require you to stick to the original payment agreement. Remember that it doesn’t hurt to ask!
As you analyze your situation, you may realize that you forgot about a periodical bill. Sometimes money coming out of your account unexpectedly can lead to financial disasters. If this sounds familiar, this next tip will help.
2. Track All of Your Payments
It’s important to have all your payments on your payment tracker. Otherwise, something will come up and you will forget about it.
So, spend some time thinking this through. You may have some bills that are due periodically and not monthly. For instance, if you’re an Amazon Prime member, your membership fee is typically due once each year. Similarly with license and registration fees - if you own a car, these only come due once each year.
You have a couple of options for this. The first turns it into a monthly expense by dividing your total payment by 12.
If you choose this option, be sure to set this money aside until the bill comes due. By putting aside 1/12 of the payment each month, you will have the total ready when it’s time to pay up.
You can also opt to only pay that bill annually. While you won’t be using it on a monthly basis, it’s still important to include it in your bill tracker. Otherwise by the time it comes due, you may have forgotten about it.
To prevent this, you can simply add these items to your spreadsheet, underneath all of your regular monthly bills. You can even add notes to help yourself remember the terms.
Here’s an example that shows bills added on a quarterly, semi-annual, and annual basis:
Feel free to use these templates a bit creatively so they really help with your personal finances.
Taking time to add the due dates to these bills now will help you be ready when they come due. Check your bill tracker before creating your budget each month, so nothing catches you off guard.
Below are a few common expenses that often come with a non-monthly payment schedule. They are easy to forget when you setup your spreadsheet. Of course, not everyone will have each of these bills, but reading through the list might help you think of something you missed:
- Insurance (Auto, Life, Identity, Health, Renter’s, Homeowners, Long-Term Care, Disability),
- Memberships (Amazon, Software, Professional),
- Registration fees (Pets, Auto),
- Union dues,
- Tax preparation fees, and
- Homeowner Association Fees.
Taking the time to add these to your payment tracker will give you a more accurate picture of how much you need to pay.
3. Make It a Habit
It takes time to setup your payment tracker. It also takes time to make updating it a habit.
Think through your week. Is there one day that works best to pay the bills and take care of financial paperwork? Setting aside some time each week can help you make financial tracking an important habit in your life.
Tracking your bills doesn’t have to be boring. Get yourself a cup of your favorite drink and put on some soft background music. Then sit down and get it knocked out.
The first few times may take a while. You might even wonder what you got yourself into. But, if you keep doing it, you will find that recording your payments takes less time. Soon, it will be part of your money management routine.
4. Print a List of Your Bills
This tip won’t work for everyone, but if you enjoy crossing items off a list, this can help motivate you to pay your bills.
Click onto the Monthly Simple worksheet from this Excel template. Make sure you have your list of payees, your due dates, and amount filled in.
Then, print it out. The conditional formatting won’t be there on the hard copy, but it is still useful.
You will have a printed sheet that looks like this:
Now, you can keep this with your checkbook. When you make a payment to one of the companies, put a check mark in the corresponding month.
When you pay everything for one month, you would have a list of checks all the way down. That way you know you don’t miss any.
Then, you can update your digital tracker with all the details on a monthly basis. And, if you still prefer taking notes with a pen and paper, this can be a good exercise to help you transition to electronic record keeping.
Stay Current on Your Bills with this Excel Monthly Payment Tracker
This monthly payment schedule template can help you stay current on your bills and get out of debt. Whether you use the Monthly Simple worksheet or the Monthly Detailed one, you will have the data you need to pay your bills on time.
We are sorry 🙁
Help us improve!
How we can improve this template?