← All free tools & templates
Free Excel template · VAT

VAT workings template for Excel, South Africa

Zuvo Team13 November 20266 min read

Short answer: VAT payable is the VAT you charged on sales (output VAT) less the VAT you can claim on purchases (input VAT). This free Excel template adds both up for the period you choose and shows what you owe SARS, or what they owe you. It gives you the workings for your VAT201. It is not the return itself.

Download

Free download. No signup, nothing to install.
FileWhat is in itDownload
VAT workings template (.xlsx)Settings, a Sales sheet and a Purchases sheet (500 rows each), a VAT summary, and a How to use sheet. Live formulas and drop-down lists, with sample rows to overwrite.Download Excel

How to use it

  1. Settings: type your business name, VAT number, the first and last day of the VAT period, and check the rate (15%).
  2. Sales: for each invoice, type the date, number and client, choose Standard-rated, Zero-rated or Exempt, and type the amount before VAT. The VAT is worked out for you. Type credit notes as negative amounts.
  3. Purchases: type the date, supplier and the amount you paid including VAT. The VAT inside the amount is worked out for you (the amount times 15, divided by 115). Choose Yes or No for whether you can claim it.
  4. VAT summary: read the result. It only counts entries dated inside the period on the Settings sheet, so you can keep adding to the same file month after month and change the dates.

What the sample shows

Sample period 1 to 31 October 2026 (example figures)
LineAmount
Standard-rated sales, before VAT (4 entries, 1 credit note)R19,650.00
Zero-rated sales, before VAT (an export)R12,000.00
VAT charged on sales (output VAT)R2,947.50
VAT on capital goods (a pipe bender)R298.70
VAT on other goods and servicesR617.61
Total input VAT claimedR916.31
VAT payable to SARSR2,031.19

Two purchases are marked No: a client lunch, and an insurance premium that has no VAT in it. Entertainment is generally not claimable, and you cannot claim VAT that was never charged. Capital goods and other purchases are shown on separate lines because the VAT201 asks for them separately.

Things that catch people out

Let the numbers work themselves out

Zuvo works out VAT charged, VAT on expenses and VAT payable from your invoices and expenses for any period, and downloads it as an Excel workbook with live formulas that you can hand to your accountant.

Try Zuvo free for 14 days, no card

How we checked it

Every formula was recalculated with a separate calculation engine and compared with the saved figures. We then changed the inputs to test the cases the sample does not show: a sale just outside the period, entries on the first and last day, a different VAT rate, a purchase switched to claimable, and a quiet month that produces a refund. We have not opened it in every spreadsheet program. It should work in Excel, Google Sheets and LibreOffice.

Questions people ask

What is the difference between output VAT and input VAT?

Output VAT is the VAT you charge your clients on sales. Input VAT is the VAT you pay your suppliers on purchases for the business. You pay SARS the output VAT less the input VAT you can claim. If the input VAT is bigger, SARS owes you a refund.

How do I work out the VAT inside a price that already includes VAT?

Multiply the total by 15 and divide by 115. For R1,150 that is R150. The template does this on the Purchases sheet. Our VAT calculator adds or removes VAT for a single amount.

Is this the same as the VAT201?

No. The VAT201 is the return you submit to SARS. This template gives you the workings behind the main figures: output VAT, input VAT on capital goods and on other purchases, and the amount payable or refundable. Check it with your accountant before you submit.

Can I claim VAT on everything I buy?

No. You need a valid tax invoice, the purchase must be for your business, and some items are blocked, such as entertainment and most passenger cars. If you are unsure, ask your accountant.

Do I have to be VAT-registered to use it?

It is for VAT vendors. Registration is compulsory once your taxable turnover passes R2.3 million in any 12 months, and you can register voluntarily from R120,000 (these thresholds changed recently, so confirm the current position with SARS). See do I need to register for VAT.

VAT workings and general guidance, not tax advice. The standard rate is 15%. Check the rules at sars.gov.za or with your accountant before you submit a VAT201.