Newsletter December 2009
Spreadsheet Tips - VAT Calculator for Adding VAT and Removing VAT - Free Download. Fast and easy to use VAT Calculator which gives you Total amount before and after VAT, plus the VAT amount, starting from either position. The VAT Calculator is a free download from Working Data, which allows you to perform as many simultaneous calculations as you can fit on a spreasheet.

VAT Calculator - Adding VAT and Removing VAT - Free Download
On January the 1st, 2010, the VAT rate is reverting back to 17.5% from 15%. The Working Data VAT Calculator, which is free to download can add and subtract VAT at 17.5% or 15%, 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.

VAT Calculator - Adding VAT

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. Open the VAT Calculator and click on the 'Adding VAT' tab, Fig 1, which shows two examples in place, 'Set of Knives' and 'Dinner Service'.

Fig 1



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 £4.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 '£4.00', as in Fig 2.

Fig 2



We then copy the formulas down in columns D, E, G and H, as in Fig 3.

Fig 3



The price is £4.00. With VAT Rate 1 (15%), you add 60p to get the price (incl. VAT) of £4.60. VAT Rate 2 (17.5%) gives a VAT Amt of 70p and a Total Including VAT of £4.70. You can add as many new rows as you can fit in, that's over sixty thousand simultaneous calculations in this free download.
..back to top

VAT Calculator - Removing VAT

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, which uses the example of a VAT rate change from 15% to 17.5%. See Fig 4.

Fig 4



To show how to make a new calculation, type 'Set of mugs' in cell A14 and £7.95 in cell B14, as in Fig 5.

Fig 5



Next, copy down the equations in column D, E, G and H, as in Fig 6.

Fig 6



A set of mugs is sold at £7.95. At VAT Rate 1 (15%), this consists of £6.91 and £1.04. At VAT Rate 2 (17.5%), this consists of £6.77 and £1.18. As with the Adding VAT tab, you can do 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.
..back to top