Accountio / Templates / VAT reconciliation / VAT control account

VAT control account template: quarterly reconciliation in Excel

Prove the VAT control account in the ledger to each return, every quarter, in one sheet. This VAT control account template is the Control account sheet of an Excel VAT reconciliation workbook for UK VAT returns, and it reads the return figures from the box check in the same file.

UK practices
$15
In the pack
VAT reconciliation pack
Files
3, Word and Excel
Buy the pack · $15
Global library · 110 packs

Get this pack and 109 more for $99

VAT reconciliation · this packAML complianceCharity accountsEstate administrationMTD for Income TaxP11D and payrollingUK SRS readinessReference lettersCarbon reduction plan+ 101 more

What the workbook looks like

VAT control account template
What the sheet coversRow on the Control account sheet
Four quarters side by side
✓
Period endQuarter 1 to Quarter 4, with dates taken from the Settings sheet
The roll-forward
✓
Opening balance (owed to HMRC is positive)Quarter 1 is typed in. Later quarters open with the trial balance figure from the quarter before
Everything posted in the quarter
✓
Output VAT posted in the quarter, Input VAT posted in the quarterThe VAT on sales and purchases as posted in the ledger
Cash to and from HMRC
✓
Payments made to HMRC, Repayments received from HMRCEntered as positive figures; the sheet applies the sign
Corrections and journals
✓
Error corrections posted, Other journals to the control accountError corrections and any other postings, each with its sign
Postings agreed to the ledger
✓
Closing balance worked out from the postings, Closing balance on the trial balance, DifferenceShows any gap between the roll-forward and the trial balance
Balance agreed to the return
✓
Net VAT on this quarter's return (box 3 less box 4), Earlier returns not yet paid or repaidTakes box 3 and box 4 from the Box check sheet and adds any earlier return still unpaid at the period end
A clear answer for each quarter
✓
CheckOK, or what is wrong: return not filled in, trial balance not agreeing to the postings, or the balance not agreeing to the return, within the rounding tolerance you set

When you need it

The VAT account is the link between the business records and the VAT return. HMRC sets no format for it, but it must show output tax, input tax and the tax from corrections and other adjustments. At each quarter end the balance owed to HMRC should equal box 3 less box 4 on that return, plus any earlier return not yet paid. Use the sheet when:

  • You prepare a client's quarterly VAT return and want to prove it to the ledger before it goes in
  • The VAT control account on the trial balance does not agree to the last return
  • You are closing the year and need the VAT balance on the balance sheet explained
  • A payment or repayment was posted to the wrong period
  • An error correction has gone through the current return and you need to see it in the account

How to use the VAT control account template

  1. Start on Settings. Enter the client, VAT number, the end date of the first quarter and a rounding tolerance in pounds. The four period ends and due dates follow.
  2. Fill in Box check. Enter the return as submitted for each quarter. The Control account sheet takes box 3 and box 4 from there.
  3. Enter the postings. For each quarter, the opening balance (Quarter 1 only), output and input VAT posted, payments, repayments, error corrections and other journals.
  4. Enter the trial balance figure. Type the closing balance on the VAT control account. The difference row shows any gap with the postings.
  5. Clear the Check row. Add any earlier return still unpaid at the period end. When the Check cell shows OK, the account agrees to the ledger and to the return.

Questions

What is a VAT control account?

The account in the ledger that holds VAT on sales and purchases until it is paid to or repaid by HMRC. HMRC calls the record that links the books to the return the VAT account and sets no format for it.

What should the VAT control account balance be?

At the end of a quarter, the net VAT on that return, box 3 less box 4, plus any earlier return not yet paid or repaid. If box 3 is more than box 4 the business owes the difference. If it is less, HMRC repays it.

Why doesn't my VAT control account agree to the return?

The usual reasons are a payment posted to the wrong period, a journal posted straight to the account, VAT on a transaction left off the return, or an error correction in one but not the other. The sheet shows whether the gap is between the postings and the trial balance or between the balance and the return.

How do error corrections show in the account?

An error from an earlier return corrected on the current return goes through the VAT account and into box 1 or box 4. That is allowed when the net error is £10,000 or less, or up to £50,000 if it is not more than 1% of box 6. The workbook's Errors sheet works this out.

When is the VAT return due?

The return and payment are due one calendar month and 7 days after the end of the period. The Settings sheet works out the date for each quarter.

How long must VAT records be kept?

Generally at least 6 years. The VAT account is one of those records.

Also in the VAT reconciliation pack

VAT reconciliation review checklist

See the whole pack →

Who it's for

UK practices that prepare VAT returns for clients, and finance teams that reconcile their own VAT each quarter or at the year end.

Last checked

Checked against HMRC VAT Notices 700/12, 700/21, 700/22 and 700/45 and the VAT return deadlines in November 2026.