... ...

Expense Form Template

Credit Card Accounting

This expense form template in Microsoft Excel can be used to track your expenses off your business credit card and is good to use if you pay bills with your credit card for your small business or blog. 

This Excel template is perfect for your small business credit card accounting … it helps you to make sure you are including all your business expenses (not just the ones off the normal bank account).


Expense Form Template

With this expense form template you can: -

  1. name all your credit card expense accounts
  2. enter in your credit card expenses and watch the credit card balance in the template update automatically, daily
  3. have it matching the actual credit card account balance showing on your credit card statement
  4. view a summary expense report which can be added to your overall business income statement (also called profit and loss report) 

Note: you need to have Microsoft Excel installed on your computer, or be subscribed to Excel online to be able to use this template.

If you need to track your small business or blog income and expenses off your normal bank account, check out my excel cash book.

Ready to download your template?

Select the one that matches the financial year-end date of your business, the download will happen immediately when you click on it - if you don't see it on your browser downloading, then check your Downloads folder on your computer:-

Year End 31 December

Year End 31 March

Year End 30 June

Year End 30 September

Open the download and save the document to your computer - give it a name of your choice, or just use the name it has already.

The template can track the details of one credit card. If you have three business credit cards, then you need to save three copies of the template.

Credit Card Accounting

I recommend that before you start entering your proper data, you save a copy of this expense form template and practice entering information – change the headings, enter some numbers into the income and expenses and see how they change the balance columns and the totals on the reports. Play around so that you get a good feel for it. 

Overview of expense form template

This template is an Excel workbook containing 16 sheets/tabs. Here is a summary of what they are and a screenshot of each - we get more into the details after that.

Sample Page

Shows you an example of a completed page of expenses and balances.

Expense Form Template Excel

Control Tab

This is where you enter in your expense names such as Stationery, Gas, etc. These are the "accounts" of your business as found in a Chart of Accounts.

12 Months Tabs

12 named sheets representing each month of the year - these are where you enter your daily expense transactions – each column has a total at the bottom and each row has a total for the income and a total for the expenses, and the daily balance.

P&L (short for profit and loss report (also known as income statement)

This is the summary report that tells you what your business profit or loss is, based on the data you entered into the monthly sheets. It updates automatically.

balance sheet

This information should be combined with your overall business Balance Sheet

entering data to the expense form template

Sample Page

The sample page of this expense form template shows you how to enter in your information, including:-

  • the date of the transaction 
  • the description (keep it brief) 
  • and a reference number, if you want to put a reference number on your paper receipt and on your expense form so that you easily track which receipt matches each transaction.

control page

This page is linked to all the months and the two report sheets. If you change Head 5 (for example) renaming it to T-Shirt Stock Purchase this will change the column header for Head 5 on every month.  

It will also update the account names on the P&L or Balance Sheet. The light yellow shaded cells are linked to the Balance Sheet. The green shaded cells are linked to the P&L.

Here is a before and after image:-

Control Page before Renaming Heading
January Before Renaming Heading
Control Page after Renaming Heading
January after Renaming Heading

monthly pages

Income Columns

There are two “Payments Received” income columns and one “Other Funds” income column. Why? These represent funds that are not actual income from selling items or services, but are funds received from activities such as: 

  • payments from the business bank account – the ending total of this column is linked to the Balance Sheet under Liabilities as "Business Bank Account Transfers" 
  • payments from your personal bank account – the ending total of this column is linked to the Balance Sheet under Equity as "Owner’s Capital" 
  • or cashback rewards if you have that type of credit card – the ending total of this column is linked to the Balance Sheet under Liabilities.

Expenses Columns

There are also four “Other Funds Paid Out” columns that are linked to the Balance Sheet for activities such as:-

  • Asset purchases 
  • Loan Repayments (because these are not a deductible business expense so they don’t go on the P&L)
  • Transfers to the Business Bank Account
  • Owner’s Drawings – for those times when you spend business funds on personal expenses such as groceries or school fees – anything that you buy that is personal and not used in the business.


The credit card balance

The Sample page shows how the credit card balance changes with each transaction in this expense form template.

This is different to how it works for a business bank account, where the balance would increase with a payment received and decrease for a purchase of something.

Money Received Decreases the Balance

If it is money received into the credit card account then the balance on the expense form template decreases. 

For example, if your credit card balance is $1,040, and you make a payment against the credit card off your business bank account of $1,000, the credit card balance will go down to $40. 

That is all that is left owing to the credit card company now. This is how it will show in this expense form template.

Money Spent Increases the Balance

If you buy something the credit card balance will increase.

For example, say your credit card balance is $1,195 and you pay for a top-up for your cell phone of $45, the credit card balance goes up to $1,290. This is how it will show in the expense form template.

On the expense form template, the credit card balance shows as a normal value if it is a balance you owe the credit card company (example $250.00), but it will show as a negative red value if you have paid too much on to your credit card (example -$50.00).

which report does the credit card balance go on?

The credit card balance is shown on the Balance Sheet under Liabilities in an account called Credit Card Balance Owing, because any balance on the credit card that is owed to a credit card company is like a loan from them. 

All loans are a liability because it is money you owe to another business. In saying that, some bookkeepers will include the balance under the Assets section of a Balance Sheet as a negative balance, which is perfectly acceptable.

credit card limit

Some people make the mistake of thinking they must show their credit card limit amount on their reports but this is not the case.

The credit card limit has no effect on credit card accounting and should not be included in the business financial records.

Only the amount that you spent that is owed to the credit card company is what should be shown.

delays in processing time

It can often take up to 5 or more days for a transaction to be processed and cleared on your credit card. 

Say you bought a laptop on 31 January for $800, if you use Internet Banking and check your credit card details, you will most likely find that even though the credit card balance has increased by $800 (and even that might take a few days to occur), when you look at the details on the credit card transaction page there may be no details yet - it might just say Electronic Transaction or something similar and not yet show the name of the business you bought the laptop from. It will show it once the amount clears.

So how should you handle this in your expense form template?

  1. If the amount of the purchase has been deducted off your credit card from the total balance on the date of purchase, then enter it into the expense form template on that date. This will keep your credit card balance accurate to the date. 

  2. If the amount of the purchase has not yet been deducted from the total balance, then leave it off until the date that it does change the balance.

Your aim is to make sure that the expense form template balance matches the credit card account balance, so whether you access it through internet banking, or you wait for a paper statement to arrive, make sure the template matches it.

You can use the last column Headed R to mark each transaction as Reconciled as you check them off your Credit Card Statement. R stands for Reconciled, C stands for checked, X stands for checked and the dash can mean anything you want! Select any one that you fancy using. 

what to do if your credit card statement ends in the middle of the month (not on the last day)

The expense form template for each month runs from the 1st of the month to the 31st (or 30th) of the month.

However, a lot of the time Credit Card Statements end with a closing balance on a 15th or something similar.

Don’t panic!

Still use all the actual dates of your transactions and simply mark as reconciled all the transactions up to the 15th of the month. All this means is that you have to wait a few weeks before seeing a finalized P&L figure for the previous month.

profit and loss report on the expense form template

This report is basically made up of the totals of your Income, minus the totals of your expenses. This report will most likely show a loss because in most cases you will not have your income being paid into your Credit Card – your customers would usually pay into either your business bank account or your Paypal or Stripe account, or give you a check which you will deposit to your bank account. 

You need to add the totals on this P&L to the overall P&L of your business that is calculated from the transactions off your business bank account. 

If you use our Excel Cash Book template for the transactions off your business bank account, you will already have a Profit and Loss being calculated to which you can add these Credit Card amounts.

Balance Sheet

This report is made up of all transactions that are not a normal business income or expense. 

We’ve explained some of them already further up this page and they include things like Owner’s Drawings and Capital, Loan repayments, Asset purchases and so on. 

It also includes the Current Year Earnings, which is the Profit or Loss amount calculated within the Profit and Loss report.

The Total Net Assets must equal the Total Equity exactly. If it doesn’t then something has gone awry.

If you need a hand getting this right, use the Contact form to email me and tell me what the problem is - include a copy of your data template if you can (if you don’t want to that’s fine) so that I can help you get the balances correct.

What to do if you also use your personal credit card for both business and personal expenses

If you use your personal credit card for business expenses (as well as all your personal stuff), don’t use this expense form template to record the credit card transactions and balance. 

Why? 

Your business does not ‘own’ this credit card debt, you do (even if you are a sole trader) and so it should not be included in the business accounts. 

However, you will still need to bring in the business expenses off that personal credit card to your business accounts. So, this is what you should do: -

If you are using bookkeeping software for your accounts, you would do a journal entry to get these transactions in to your accounts.

  1. On your credit card statement, highlight the business expenses, and add up the total of those business expenses. Ignore all your personal stuff.

  2. If you use our Excel Cash Book to track your income and expenses off your bank account, go to that cash book now. I am going to show you a little trick to bring in these expenses. OR

  3. If you also use a business credit card and are tracking its expenses in our expense form template described on this page, go to that template now.

  4. Go into the month related to your business expenses that you calculated in step 1 above.

  5. In the ‘Owner’s Capital’ column on the last day of the month, enter in the total amount of the business expenses off your personal credit card for the month. (They do not have to be entered on the exact date they occurred on your personal credit card).

  6. In that same row, go to the expenses side of the cash book and split out that amount into the various expenses. See the screenshot below. This example assumes that the business owner spent $500 off her personal credit card for four different business expenses of $250, $35, $125 and $90. (See example of this in the image below).

  7. Have a look at the Bank Balance (if using the Excel cashbook) or the Credit Card Balance (if using the Expense Form Template). It should not have changed – the balance should still be the same as the day before. That’s what you want. These transactions do not affect your actual bank account balance or business credit card balance so you don’t want them affecting the balances in the templates.

  8. The way I’ve shown you how to do this will allocate the business expenses to your profit and loss report, and will allocate the amount of personal funds you used (off your personal credit card) on the Balance Sheet under Capital.

how to tell if your credit card is a business one or a personal one

You may get tripped up if you are a sole proprietor and are using a credit card in your own name not your business name.  So here is one way to identify if your credit card can be termed a business one or personal one:

  1. It is a business one if it is a new one and you got it specifically to use for your business and if you use it mostly for business (you can still have some personal expenses coming off it which you can put to the Owner’s Drawings account but try to limit these). 

  2. It is a personal credit card if you already had it prior to starting your business, and it already has a balance owing on it from all your personal expenses and you got it originally to use just for personal purchases.
    If you want to ‘change’ it to be used as just a business one you can by doing the following: -

    a)  simply pay off the balance owing to bring the balance back down to $0.00 (before the start of the financial year would be best, otherwise do it before the start of a new month so that the opening credit card balance in your accounts can be $0.00)

    b)  then going forward use it mostly for business purchases. If you do too many personal transactions, you will be wasting a lot of time having to enter those into the accounts as ‘Owner’s Drawings’ – that’s why it is always best to keep personal credit cards and spending as separate as you can from business ones (the same goes with your bank accounts).

Share this page

Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.