How to Create a Spreadsheet for Tracking Payments

Spreadsheets are excellent tools for calculating payments, expenses, revenue and profits. The ability to keep a simple spreadsheet or completely customize a spreadsheet with formulas and automatic calculations makes the spreadsheet a commonly used tool in business. Excel is the standard spreadsheet application, but Google Sheets and other similar substitutes exist. A payment-only spreadsheet is relatively simple to build, but existing templates exist out of the box.

The easiest and often most effective way to track payments in a spreadsheet is through an existing template. Numerous keeping-track-of-payments templates are available for free or for a small fee, and they are excellent resources for tracking outbound and inbound payments using formulas that are proven and familiar in business. A simple internet search yields an abundance of results for existing templates. Some of these templates are intended for use without manipulation, but many are set up to be customized with your company logo and custom fields that make the form look and feel unique.

The most common payment spreadsheet use is for tracking outgoing payments or bills. You can also track incoming payments for your billing. Doing this under a single spreadsheet is possible, but many individuals and businesses separate the two into separate sheets. This leaves you with final figures for payments made and payments received each month.

To create the spreadsheet, begin with the columns. Label the first column "Payment Type," the second column "Due Date," the third column "Amount," the fourth column "Deposits" and the final column "Balance."Under the first column, list all your payments due and fill in the remainder of the information in the remaining columns. Now, you have all your payments scheduled and the amounts listed.

In the "Balance" column, run a balance formula so that each subsequent payment is subtracted from the total. You can do this manually if the number of payments is low, or you can automate the process with a formula. In the first row of the final column, which is the E column, type "sum=(D2,-C2)", which is the bill amount subtracted from the Deposit column, which runs the balance to the final column.

More robust options are available for businesses that want to track outgoing payments, recurring payments, incoming payments and other expenses under one spreadsheet. You can use formulas to track every important transaction under a single spreadsheet. It ultimately depends on your needs and how many elements of a business or personal budget you want to track under one spreadsheet. A simple payment spreadsheet is ideal for monthly bills and a client payment tracker in Excel works for professional payment tracking.