Running a small business means keeping accurate records – and once you become VAT registered, your bookkeeping needs to evolve to handle those extra calculations.
In Part 4 of our Excel bookkeeping series, we walk through exactly how to upgrade a simple, spreadsheet-based bookkeeping system into a fully VAT-ready one, covering invoices, invoice templates, and expenditure tracking.
If you haven’t watched the first three parts of this series yet, it’s worth starting there first:
This tutorial builds directly on those spreadsheets, so understanding the basics first will make the VAT upgrade much easier to follow.
Running a small business means keeping accurate records – and once you become VAT registered, your bookkeeping needs to evolve to handle those extra calculations.
In Part 4 of our Excel bookkeeping series, we walk through exactly how to upgrade a simple, spreadsheet-based bookkeeping system into a fully VAT-ready one, covering invoices, invoice templates, and expenditure tracking.
If you haven’t watched the first three parts of this series yet, it’s worth starting there first:
This tutorial builds directly on those spreadsheets, so understanding the basics first will make the VAT upgrade much easier to follow.
Why Use Spreadsheets Instead of Paid Software?
Before diving into the how-to, it’s worth noting the philosophy behind this system: no monthly subscriptions, no expensive software – just Excel (or free alternatives like LibreOffice or OpenOffice). This approach has worked reliably for 16 years without a single issue, and the money saved on bookkeeping software can be reinvested into other parts of the business or used to offset accountant fees.
Step 1: Duplicate Your Existing System
Before making any changes, create a new folder (e.g., “04 VAT Bookkeeping System”) and copy over your existing invoice tracker, invoice template, and expenditure tracker spreadsheets. This keeps your original files intact while you build the VAT version from a working copy.
Step 2: Add VAT Columns to the Invoice Tracker
The core of VAT bookkeeping comes down to three columns:
- Ex-VAT – the cost before VAT is applied
- VAT amount – the calculated VAT
- Inc-VAT – the total including VAT
To calculate VAT, simply multiply the ex-VAT cost by the VAT rate as a decimal:
- 20% VAT → multiply by 0.2
- 17.5% VAT → multiply by 0.175
- 5% VAT → multiply by 0.05
- 15% VAT → multiply by 0.15
The total (inc-VAT) column is simply the ex-VAT value plus the VAT amount. Once the formula is set for one row, you can drag it down to auto-calculate VAT for every invoice line, then use AutoSum to get running totals for the whole sheet — giving you an instant view of total earnings, VAT owed to the taxman, and the total invoiced to clients.
Tip: VAT rates vary by country, so simply adjust the decimal multiplier to match your local rate.
Step 3: Update the Invoice Template
Next, the invoice template itself needs subtotal, VAT, and total rows added:
- Subtotal = sum of all product/service line items
- VAT = subtotal × VAT rate (e.g., × 0.2 for 20%)
- Total = subtotal + VAT
Once these formulas are in place, Excel automatically extends them to new rows as you add more product lines – no manual copying required for existing formatted rows. This template can then be reused indefinitely: just update the invoice number, date, client details, and line items, then export as a PDF ready to email to your customer.
Step 4: Add VAT to Your Expenditure Tracker
VAT isn’t just something you charge — when VAT registered, you can often claim VAT back on business expenses too. Add the same three columns (ex-VAT, VAT, inc-VAT) to your expenditure spreadsheet.
A few practical notes from the tutorial:
- Domestic purchases typically include VAT – back-calculate the ex-VAT and VAT amounts using the same multiplier logic.
- Zero-VAT items (like postage stamps or staff wages) should have £0 in the VAT column.
- Foreign currency purchases (e.g., from the US) usually have no UK VAT applied – convert to GBP using your bank/PayPal statement and record the ex-VAT value directly.
- Use colour coding (e.g., blue for foreign payments, green for UK payments, orange for wages) to keep entries organised at a glance.
Running totals via AutoSum at the bottom of the sheet will show your total expenditure and – critically – the total VAT you can potentially reclaim.
Step 5: Track Payments and Outstanding Invoices
As customers pay their invoices, mark them in green, log the payment date, method, and reference. This makes it simple to see at a glance who has paid and who hasn’t – essential for chasing overdue payments and managing debtors at year-end.
Step 6: Year-End Archiving
At the end of your business year, create a new “Yearly Accounts” folder (e.g., “2019”) and copy in your finalised invoice tracker, invoice template, and expenditure tracker. This creates a clean archive you can hand off to your accountant, complete with:
- Every invoice sent
- Every business cost recorded
- Full VAT calculations for both income and expenses
Bonus Tips From the Tutorial
- Back up regularly. Periodically archive versioned copies of your spreadsheets (v1, v2, v3…) in case of file corruption.
- Batch your owner withdrawals. Instead of small daily withdrawals, take one larger payment monthly to simplify bookkeeping.
- Negotiate recurring costs. Renegotiate contracts like internet and phone bills annually – you have more leverage than you think.
- Don’t skimp on your accountant, but do make their job easier – the more organised your data, the less they should need to charge you for data entry.
Final Thoughts
This VAT-ready bookkeeping system won’t be a perfect fit for every business, but it’s a solid, cost-free foundation you can adapt to your own needs. By tracking invoices, expenditures, and VAT in simple Excel spreadsheets, you avoid subscription software fees while staying fully organised for tax season and accountant handoffs.
This is Part 4 of a 4-part Excel bookkeeping series for small businesses. Be sure to check out Parts 1–3 for the full invoice tracking, invoice template, and expenditure tracking foundations this VAT system builds on.
