The Working Data VAT Calculator, which is free to download can add and subtract VAT at 17.5% or 20%, for as many items/lines as you want. Continue reading for a quick overview on how to use the VAT Calculator or go straight to the download page.
The VAT Calculator has two worksheets, Adding VAT and Removing VAT. In both, the VAT amount is shown, along with the before and after totals. If you want to keep the final price, the one including VAT the same, when you change VAT rates, you use the Removing VAT tab. Open the VAT Calculator and click on the ‘Removing VAT’ tab which uses the example of a VAT rate change from 17.5% to 20%. See Spreadsheet Data 1.
Spreadsheet Data 1
To show how to make a new calculation, type ‘Set of mugs’ in cell A14 and £14.99 in cell B14, as in Spreadsheet Data 2.
Spreadsheet Data 2
Next, copy down the equations in column D, E, G and H, as in Spreadsheet Data 3.
Spreadsheet Data 3
A set of mugs is sold at £14.99. At VAT Rate 1 (17.5%), this consists of £12.76 and £2.23. At VAT Rate 2 (20%), this consists of £12.49 and £2.50. You can do over sixty thousand simultaneous calculations in this free download.
Clicking on the ‘Adding VAT’ tab shows the same as Spreadsheet Data 1, which shows two examples in place, ‘Set of Knives’ and ‘Dinner Service’.
Spreadsheet Data 4
The ‘Adding VAT’ gives you the tools to find out what prices are after VAT has been applied. For example, a ‘Set of Mugs’ costs £7.00 before VAT, how much does it cost once VAT has been added?
In cell A14 we add ‘Set of Mugs’ and in B14 we add ‘£7.00′, as in Spreadsheet Data 2.
Spreadsheet Data 5
We then copy the formulas down in columns D, E, G and H, as in Spreadsheet Data 6.
Spreadsheet Data 6
The price is £7.00. With VAT Rate 1 (17.5%), you add £1.23 to get the price (incl. VAT) of £8.23. VAT Rate 2 (20%) gives a VAT Amt of £1.40 and a Total Including VAT of £8.40. You can add as many new rows as you can fit in, that’s over sixty thousand simultaneous calculations in this free download.
Click to download this month’s spreadsheet tip, the VAT Calculator. Always remember to virus scan every file you download from the internet. If you have any requests for topics to be covered in future Spreadsheet Tips, please contact us.