How To Create A Ledger Account In Excel

Accounts Payable Ledger

This accounts payable ledger is an excel spreadsheet into which you can list all your business purchases made on account.

This helps you track what you owe to each vendor/supplier and you can see at a glance when you need to pay your bills.

This spreadsheet should only be used for suppliers that you have an account with.

If you make a purchase and pay for it right away, like with your bank or credit card, there is no need to enter it into this spreadsheet because you don't owe anything on it, it's all paid.

Accounts Payable Ledger

Accounts Payable Download

Download the spreadsheet to your device by tapping on the blue button below and read further down this page for help on how to use the spreadsheet.

Overview of the accounts payable ledger. Accounts Payable Ledger QuickView

What you get with this ledger

A payables sheet with 2000 formatted rows and 31 formatted columns
An examples sheet to show you how to enter your bills details and payments as well as credits
A current total of outstanding bills at the top of the sheet which updates automatically
Automatic calculations to update the totals due on each invoice after you enter payments
Filters to enable you to show only the information you want to see - paid bills can be hidden

What type of bills to use this ledger for

You will track bills received in advance of making payments, whether you pay them yourself or whether they are paid with automatic debits.

There are many different types of business with different requirements so I can't list every type of expense/bill, but this list should give you some idea of the type of bills to keep track of:

Phone: landline, mobile on account, broadband
Power bill: (for leased/rented premises or owned office) - for home office power see home office costs
Parts and supplies: for when you buy materials to manufacture items you sell
Inventory items
Tools: manufacturing, tradespeople, crafters, designers, artists...
Equipment: office equipment, manufacturing, kitchens, workshops
Business insurance
Repairs and maintenance on your office or vehicle (once again see home office costs if these are for your home office)
Food and drink (for restaurants, coffee shops, bakeries and more)
Cleaning supplies
Bookkeeper, Accountant or Consultant fees
Tax bills
Fixed Assets: plant and equipment, vehicles, office equipment

Accounts Payable Ledger
First Six Columns

First six columns of the accounts payable ledger.

When you first download the spreadsheet, save the document to wherever it is you save all your documents (see computer filing system for useful tips on filing electronic documents).

When you receive a supplier/vendor invoice enter in the following details to your spreadsheet starting in Row 3:-

Date: Enter the date that is shown on the vendor invoice
Invoice Number: Enter the invoice number that is shown on the vendor invoice
Vendor's Name: Enter the vendor's name.
Description: Enter a brief description of the type of purchase.
Total Amount: Enter the total that is shown at the bottom of the invoice.
Due Date: Enter the date that the invoice is due to be paid by - this should also be shown on the invoice, however some suppliers - more notably, very small businesses - sometimes leave this date off because they don't realize they should put it on there, in this case just enter the date you are going to pay it by (be reasonable - payment is usually due within the next 30 days or so).

This accounts payable ledger is an excel spreadsheet for recording your business purchases made on account to track what you owe to each vendor/supplier and when to pay your bills.

Accounts Payable Ledger:
Remaining Columns

Total Accounts Payable:The total of5,710.00 isautomatically calculated by Excel - it adds up the total from the 'Balance Due' Column underneath it (the screenshot is not showing all the amounts in that column).
Balance Due:For example, the Balance Due of 5,000 is made up of the total of the Bank Business Loan of 20,000 (which you can see in First Five Columns screenshot further up this page)less the payments as seen in columns title Payment 1, Payment 2 etc.
Payment Columns: Every time you make a payment to your supplier you need to enter it into the payments columns and the dates of the payments into the Dates columns, this is particularly useful if you have a large bill that you are paying off over several months.
In my example screenshot, you can see six payments of 2,500 which equals 15,000 - so 15,000 taken away from 20,000 leaves a 5,000 balance due to still be paid to the bank.

Current Date: this is auto-generated by your Excel software and it should show you the current date that you are on (it will use the date that is entered to your computer so make sure your computer is dated correctly!). If you have problems with this, simply manually over-type the date to what you want it to be.

Accounts Payable Ledger
Using from Month to Month

Every time you enter new data to the spreadsheet, remember to Save before exiting.

The recommendation is to save the spreadsheet once a month with a new name.

For example, if it is the month of May and you are entering all May dated invoices then save your spreadsheet with the name Accounts Payable - May 20xx.

When June starts save a fresh copy of this ledger and start entering your June bills and any payments you are making. Call it Accounts Payable - June 20xx

Keeping copies like this ensures that if you make any huge mistakes in your current spreadsheet you always have last month's version to start afresh again.

You may also like Accounts Payable Procedures which will give you more information on the process of managing your business bills.