Prepare a VAT Return from Bank Statements (UK Guide)

TL;DR Bank statements are a good starting point for VAT workings, especially for small UK businesses on cash accounting, but they're not enough on their own. Convert the statements for the VAT quarter to a spreadsheet, reconcile each one to its closing balance, and tag every line as a sale, a purchase, or something outside VAT such as transfers, loans, wages, drawings and HMRC payments. For each sale and purchase, record the VAT rate and work out the VAT from the gross amount, then support every input VAT claim with a valid VAT invoice or receipt. Which transactions belong in the quarter depends on whether you use cash accounting or standard (invoice) accounting. Because VAT returns for most businesses are filed digitally through compatible software, the spreadsheet is your working paper, not the return itself. This guide is general information, not tax advice.

Many small businesses do their bookkeeping late and their VAT return later still. When the deadline arrives, the bank statements are the one complete record they have. Working from them is perfectly reasonable as long as you understand what they show, what they don't, and the checks that keep your figures defensible.

This guide covers how bank statements relate to VAT, the difference cash accounting makes, how to build VAT workings in Excel, a worked example, how to handle the lines that commonly cause errors, and how the workings fit with filing through software. It's written for UK VAT-registered sole traders and small companies. VAT rules are detailed and change over time, so confirm your treatment with HMRC guidance or an accountant.

What bank statements can and can't tell you

A bank statement tells you when money moved and how much. For VAT you also need to know:

• What the money was for, so you know whether VAT applies and at what rate.
• Whether you have a valid VAT invoice for purchases where you're claiming input VAT.
• When the tax point was, if you use standard accounting rather than cash accounting.
• Whether the transaction involves VAT at all. Transfers between your accounts, loans, drawings, wages and payments to HMRC don't.

So bank statements are a strong backbone for VAT workings, but you'll need invoices, receipts and some judgement to complete them.

Cash accounting versus standard accounting

This is the most important decision in how you use bank statements.

Cash accounting. If you're eligible and have chosen the cash accounting scheme, you generally account for output VAT when your customer pays you and reclaim input VAT when you pay your supplier. That lines up closely with your bank statements, which is why bank-statement-based workings suit cash accounting businesses so well. HMRC sets eligibility conditions, including a turnover threshold, so check the current rules.

Standard (invoice) accounting. You generally account for VAT based on invoice dates (the tax point), not payment dates. Your bank statements still help, because they show which invoices have been paid, but the quarter's figures come from your sales and purchase invoices. A customer payment received in April for a March invoice belongs in the March quarter under standard accounting.

Flat Rate Scheme. If you use the Flat Rate Scheme, you usually pay a fixed percentage of your VAT-inclusive turnover rather than calculating output minus input VAT, with limited exceptions for some capital purchases. Bank statements help you total turnover, but the calculation is different from the one below.

If you're not sure which scheme you're on, check your VAT registration details or ask your accountant before you start.

Step 1: get the statements into a spreadsheet

Gather statements for every business bank account and card for the VAT period, plus a few days either side to catch timing issues.

• If your bank offers a CSV export, use it, but keep the PDF statements as your reference for opening and closing balances.
• If you only have PDFs, convert them. SheetStatement converts PDF statements to one row per transaction and checks the balances; the free plan includes 3 pages a month, with Starter ($9), Pro ($19) and Business ($39) monthly plans for larger volumes.

Combine accounts into one table with an Account column. Our guide to combining multiple bank statements in Excel shows how.

Step 2: reconcile before you categorise

For each statement:

=ROUND(Opening + SUM(Amount) - Closing, 2)

It must be zero. A missing row could be a sale or a purchase with VAT on it, so this check matters for VAT accuracy, not just tidiness. See reconciling a bank statement in Excel if it doesn't balance.

Step 3: tag every line

Add these columns:

Type is one of: Sale, Purchase, Outside scope. Outside scope covers transfers between your accounts, loan receipts and repayments, capital introduced, drawings or dividends, wages and salaries, VAT payments to or refunds from HMRC, and other payments that aren't supplies.

VAT rate is the rate that applies to that line: standard, reduced, zero-rated, exempt, or no VAT (for example, a supplier that isn't VAT-registered).

Evidence is the invoice or receipt reference supporting the line. For purchases where you claim input VAT, this column should never be blank.

Keyword rules speed up tagging. Our guide to Excel formulas for categorising transactions shows how to assign types from description keywords, which you can then review by hand.

Step 4: work out net and VAT

Bank statement amounts are gross: they include any VAT. To split them, use the VAT fraction for the rate.

At a 20% rate, VAT is one-sixth of the gross amount:

VAT  =ROUND(C2 * 20 / 120, 2)
Net  =C2 - G2

At a 5% rate, VAT is one twenty-first of the gross:

VAT  =ROUND(C2 * 5 / 105, 2)

A general formula, with the rate as a percentage in column E:

=IF(OR(D2="Outside scope", E2=0), 0, ROUND(C2 * E2 / (100 + E2), 2))

Use the supplier's VAT invoice figure where you have one, rather than the formula. Invoices sometimes calculate VAT line by line, so the invoice total VAT can differ by a penny or two from a calculation on the gross amount. The invoice is the evidence, so it wins.

Mixed invoices, with some items at the standard rate and some zero-rated, can't be split with a single formula. Split the bank line into two rows using the invoice detail.

Step 5: summarise

With sales positive and purchases negative in the Amount column, a summary might look like this:

These map onto the main boxes of a UK VAT return: VAT due on sales, VAT reclaimed on purchases, net VAT, and the value of sales and purchases excluding VAT. If you trade with other countries, there are additional considerations and boxes; check HMRC guidance for your situation.

Worked example

An illustrative quarter for a small design studio on cash accounting, standard-rated, with all suppliers VAT-registered unless noted. Figures are invented.

Output VAT: 600.00 + 800.00 + 400.00 = £1,800.00. Input VAT: 240.00 + 6.00 = £246.00. Net VAT due: £1,554.00. Net sales: £9,000.00. Net purchases: 1,200.00 + 30.00 + 450.00 + 72.40 = £1,752.40.

Notice what was left out: the savings transfer, the payment of last quarter's VAT, and the salary. Including any of them would be wrong. Notice too that the illustrator and the train fare are purchases with no VAT to reclaim; they still count towards net purchases. Public transport fares in the UK are generally zero-rated, which is why the train fare carries no VAT. Check the treatment of each type of expense if you're unsure.

Lines that commonly cause errors

Card terminal and payment processor deposits. A deposit from a card processor or a platform like Stripe is usually net of fees, and may cover several sales. Output VAT is due on the full sale value, not the net deposit. The fees are a separate purchase; check the provider's invoice or statement to see whether any VAT was charged on them. See reconciling Stripe payouts to your bank statement for breaking deposits into sales and fees.

Purchases without a VAT invoice. If you don't have a valid VAT invoice or receipt, you generally can't reclaim the VAT, even if the supplier charged it. HMRC sets out limited exceptions and simplified rules for small purchases, so check them rather than assuming.

Foreign suppliers. Subscriptions and services from overseas suppliers may not include UK VAT on the invoice, and may fall under the reverse charge. This is an area where professional advice is worth having.

Mixed business and personal spending. If a business account was used for personal purchases, those lines aren't business purchases and carry no input VAT. Our guide to separating personal and business expenses helps you spot them.

Entertainment and some vehicle costs. VAT on business entertainment and on some car-related costs is often not reclaimable, or only partly. Check HMRC guidance for the specifics.

Refunds. A supplier refund reduces input VAT; a refund you gave a customer reduces output VAT. Tag them with the same type as the original transaction so the signs net off correctly.

Sense checks before you file

Once the summary is done, a few quick checks catch most mistakes.

Does output VAT look right against takings? If nearly all your sales are standard-rated, output VAT should be close to one-sixth of gross business receipts from customers. A big gap usually means a sale was tagged as outside scope, a processor deposit was booked net, or a non-sales receipt such as a loan was tagged as a sale.

Does input VAT look sensible against spending? Compare input VAT with gross business purchases. If it's close to one-sixth of everything, you've probably treated zero-rated, exempt or non-VAT-registered suppliers as if they charged VAT. If it's very low, check whether large standard-rated purchases are missing invoices.

Compare with previous quarters. Lay the last four quarters side by side: net sales, output VAT, net purchases, input VAT. A sudden swing should have an explanation, such as a big equipment purchase or a seasonal peak. If it doesn't, look again.

Check every Outside scope line over a threshold. Filter the Outside scope rows and sort by amount. Large unexplained receipts or payments deserve a second look, because a misclassified sale or purchase is the costliest error to make.

Check the evidence column. Filter purchases with input VAT and a blank Evidence column. Each one is either a missing invoice to chase or a claim to remove.

Partial payments under cash accounting

Under cash accounting, a customer who pays an invoice in instalments generates output VAT in proportion to each payment. If a £2,400 invoice including £400 VAT is paid £1,200 in this quarter and £1,200 next quarter, you'd generally account for £200 of VAT in each.

The bank-statement approach handles this naturally: each payment is a separate row, and the VAT fraction applied to each gross payment gives the right proportion, as long as the whole invoice is at a single rate. For mixed-rate invoices, apportion each payment according to the invoice's mix.

The same applies to purchases you pay in instalments. Keep the invoice reference in the Evidence column for every instalment so you can show how the VAT was split.

How this fits with filing

For most VAT-registered businesses, VAT returns are filed digitally under Making Tax Digital, using compatible software. Records generally need to be kept digitally too, with digital links between them where data moves from one place to another.

That has two practical consequences for spreadsheet workings:

• Your spreadsheet is a working paper. You'll typically either import your categorised transactions into bookkeeping software that files the return, or use bridging software that reads the figures from your spreadsheet and submits them.
• Avoid re-typing figures. Copying and pasting numbers by hand between records may not meet digital link requirements. Linking by formula, importing files, or using bridging software is generally the safer route.

If you're moving your workings into software, see our guides to importing bank statements into Xero, QuickBooks Online or FreeAgent.

Check HMRC's current Making Tax Digital guidance, or ask your accountant, about what's required for your business.

A quarterly routine

1. Convert and reconcile every business account statement for the quarter.
2. Tag each line: Sale, Purchase or Outside scope.
3. Add VAT rates and evidence references.
4. Calculate net and VAT, using invoice figures where you have them.
5. Review card processor deposits, foreign suppliers and any lines without evidence.
6. Summarise and compare with last quarter for anything unusual.
7. Move the figures into your filing software through an import or a digital link.
8. Keep the workings, statements and invoices together for the quarter.

This builds naturally on a month-end close checklist.

Disclaimer

This article is general information about using bank statements to prepare VAT workings. It isn't tax advice and doesn't cover every scheme, rate or exception. VAT treatment depends on your circumstances; check HMRC guidance or speak to a qualified accountant before filing.

Start with clean bank data

Upload your business bank statements to SheetStatement, confirm each one reconciles, and export to Excel ready for VAT tagging. Try 3 pages free each month.

FAQ

Can I do a VAT return just from bank statements?

Bank statements give you the transactions, but you also need VAT invoices and receipts to support input VAT and to know the rates. Use statements as the backbone and invoices as the evidence.

Do I use payment dates or invoice dates?

Under cash accounting, generally payment dates; under standard accounting, generally invoice dates (the tax point). Check which scheme you're registered for.

How do I calculate VAT from a gross bank amount?

At a 20% rate, VAT is the gross amount multiplied by 20/120, or one-sixth. At 5%, it's 5/105. Use the invoice VAT figure where you have one.

Should transfers and HMRC payments go on the return?

No. Transfers between your accounts, loans, drawings, wages and payments to or from HMRC are outside the scope of VAT.

Can I reclaim VAT without an invoice?

Generally you need a valid VAT invoice or receipt, with limited exceptions set out by HMRC. Check the rules for your situation.

Can I file my VAT return from Excel?

Most businesses file through compatible software. A spreadsheet can be used with bridging software that submits the figures, as long as digital record-keeping requirements are met.

Is this tax advice?

No. It's general information. Confirm your VAT treatment with HMRC guidance or an accountant.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.