Now Updated for 2020/2021
Track your income and expenditure with this simple bookkeeping spreadsheet template in excel for self employed and sole trader small business owners.
If you handle your own small business accounts, then an excel spreadsheet is an easy way to track all basic financial information that you’ll need.
The simple accounts spreadsheet template includes:
- An income and expenditure template;
- UK tax calculator (updated for 2020/2021) so you know roughly how much self employment tax and national insurance you will owe;
- Key information you’ll need to fill in your self assessment tax return.
It only requires basic excel skills.
Who’s this Free Bookkeeping Spreadsheet for?
I’ve designed this template with UK sole traders and self employed small business owners in mind who:
- Have a business turnover of £85,000 or less;
- Are looking for a simple free way to track their self-employment income and expenses;
- Are not VAT registered (you’ll need to follow the guidelines of making tax digital instead);
- Want to start budgeting for their tax bill and payment on account;
- Are looking for a way to get familiar with basic bookkeeping;
- Want to make tax time as simple as possible.
I’m really pleased to announce that this product has now been replaced with a brand new and improved version!
I’ve created an unlocked version of the bookkeeping spreadsheet with additional features such as cash flow forecasting, pre-set key metrics and budget performance tracker.
With an incorporated expense claim form and mileage tracker you’ll be making bigger tax savings and budgeting for better savings in less time than ever.
Guide to Setting Up the Bookkeeping Spreadsheet Template
The spreadsheet is designed to be used for 12 months of activity at a time.
It works best if you fill it out a new version from 1 April each year because this will match the self assessment tax year.
Read through each of the steps below so you can personalise the accounts template and know which tabs you need to fill in.
Step 1: The Business Details Tab
Start by heading over the Business Details tab and fill out ‘Your Business Details’ and ‘Your Personal Details’.
These will be really handy when completing your self assessment tax return. So complete all the boxes now to make things easier later on.
Your Business Details
Add some general details about your business, along with a brief description of what you do.
Accounts Start Date
If you are newly self-employed, then use the date that you registered as self-employed.
Or if you have filed a tax return previously, check the dates that you used in your last tax return.
Opening Bank Balance
Enter the opening balance of your business bank account on your accounting period start date.
HMRC guidance states that keeping your business and personal banking separate, so if you haven’t opened a business bank account then consider doing so.
Not just is it preferred by HMRC but it will make managing your business finances and you’ll get more value out of using this bookkeeping spreadsheet.
If you do not have a business bank account, then just leave this box blank.
Your Business Income & Expenses
When you are self employed, you need to track your business income and expenditure because those are the numbers you’ll need for self assessment.
I have listed out the types of income and expenses that self employed and sole trader business owners are most likely to need in the template.
But if you have other income and expenditure, that you feel do not fall into any of the categories, then these can be added in the rows highlighted in grey.
I would recommend you don’t create unnecessary categories to keep things simple. The ones I have created are in line with self assessment, so should make it easy for you to fill in your tax return.
Your Personal Details
Fill out your name, address and details of your tax codes.
If you are missing anything here such as your NI number or UTR number then request them now, you’ll need them at tax time.
The amount of tax you is based on all your earnings during the tax year.
So for the tax calculator to make an accurate estimate of what you’ll need to pay on your self-employment earnings, you’ll need to include details of all your other income and any tax deducted.
For example, if you are employed and self-employed then you’ll need to enter your gross earnings and tax deducted at source (excluding National Insurance).
If you are unsure how much tax you pay across the year then make a reasonable estimate. For example by taking your last payslip and multiply the amount of tax you pay in one month by 12.
Step 2: The Bank Statement Tab
This is the tab you need to be filling out on a regular basis.
Take your bank statements and enter transactions from the start of your accounting period into the spreadsheet.
You’ll need to enter the following details:
- Date of the transaction;
- Details of the transaction such as name or description of what you bought;
- Category this is selected from the drop-down list, which is generated from the business details tab;
- Amount of the transaction from the bank account. (Note: enter all amounts as a positive).
To make things easier, you could download your bank statement as a .csv file. Then you can copy and paste the information into the bookkeeping spreadsheet.
Step 3: Cash Collected Tab
If you get paid in cash by your customer and do not deposit it into your business bank account, then enter the amounts in this tab.
This amounts will be picked up as part of your business income automatically in the accounts spreadsheet.
It’s important not to log money you have banked into your business account otherwise you’ll duplicate your income and pay more tax!
Step 4: Cash Expenses Tab
It’s so easy to forget about cash expenses – those are the little bits and bobs you pay for with your own money when you are out and about.
I’ve added a cash expenses tab in the template so you can log these.
Just make sure you always get a receipt when you pay for something in cash and don’t forget to add it to the spreadsheet.
Step 5: Mileage Claim Tab
If you use your car for business reasons then you can claim back a certain amount to cover the fuel and wear and tear.
This is known as the Business Mileage Allowance and means you can claim a fixed amount for each mile your travel.
I’ve included a business mileage claim tab which you can fill out. It contains all the information you need to log as per HMRC guidelines.
Start by entering the pence per mile you wish to claim as part of your business If you aren’t sure what rate to use and which trips are eligible as a tax deduction, then read this guide about business travel when you are self employed.
Step 6: Business Results Tab
This tab is automatically generated from the information you enter into the bank statement, cash collected, cash expenses and mileage tabs.
It is an income and expenditure spreadsheet summarising everything you earn and spend
This is the most powerful part of the spreadsheet.
Shortly after the end of each calendar month, take a look at how your business has performed.
- Have you made money or lost money in the month?
- Were there unexpected costs?
- Is one form of income that is going really well for you?
- Were all your costs as you expected or are their things you can cut back on?
Invest time in reviewing how you and your business are performing and how you can get more efficient and make more profit.
Step 7: Dashboard Tab
Again, the dashboard is automatically generated for you.
It is a simple summary of your business results month by month, as well as your tax estimate.
Although the results of the UK tax calculator are an estimate, I would still recommend that set aside the amount in a deposit account ready for tax time.
I hope you enjoy using your bookkeeping spreadsheet.
I have tried to incorporate all the essential things you’ll need to manage your own self employed small business finances and budget for your taxes.
Good luck using it and don’t forget to check back each tax year to get your updated version of the accounts template.
New Here? These are my most popular resources:
- The Ultimate Bookkeeping Spreadsheet – Organise your money, track the numbers that matter and stay on top of your cash;
- FREE Guide: 11 Common Small Business Setup and Tax Mistakes – Are you worried your business isn’t set up correctly? Use this guide to identify whether you’re making the 11 most common financial pitfalls I’ve seen when it comes to setting up a business and taxes, as well as finding out how you can put them right;
- Sole Trader or Limited Company? – Download my free calculator to check which business structure would help you to pay less tax;
- FREE Business Expenses Cheatsheet – Check what you can and can’t claim as an expense against your taxes.
- 6 Core Elements of Self-Employment Taxes – Confused by taxes? Worried you’re missing something when it comes to tax allowances and reliefs? These easy to follow 6 mini-guides will have you on top of the tax side of things in no time at all.