VAT calculations with two VAT rates

In December of 2008, the Chancellor of the Exchequer reduced VAT from 17.5% to 15%. This created a lot of work for IT people, but didn't seem to do much else. They must know something we don't. At least, it would be better if that were true. In any case, we now have books with two VAT rates in them.

VAT Calculations with two VAT rates - Download VAT Calculator

In our taxi company, Working Night 'n' Data, it's been a busy few months. Now it's time for the manager, Frankie North, to put together the calculations for the VAT man. Unfortunately, the VAT rate for December and January was 15%, whilst it was 17.5% for November. This makes it a little more involved to calculate the VAT in-take for these three months. However, with a spreadsheet tip, we can cut down the time required. Spreadsheet Data 1 shows the compiled sales results for November '08 through to January '09.

Spreadsheet Data 1

The first thing Frankie needs to do is to determine how much the sales are, excluding VAT. He does this using the following equation. ('Total incl.VAT' means Sales Total including VAT, 'Total excl.VAT' means Sales Total excluding VAT)

VAT rate is determined as a number between 0 and 1, where 0% = 0, and 100% = 1.

Therefore, 17.5% is 0.175.

For November, with 17.5% VAT rate, the equation reads as :-

To represent this in the spreadsheet we go to cell C6 which will contain the Total excl. VAT amount for November.

We enter the equation =B6/1.175 as in Spreadsheet Data 2.

B6 is the Total incl. VAT.

Spreadsheet Data 2

Hitting return gives us our answer, £6,895.32, as in Spreadsheet Data 3.

Spreadsheet Data 3

To get the VAT amount, we subtract cell C6 from cell B6. In cell D6 we enter the formula =B6-C6 See Spreadsheet Data 4.

Spreadsheet Data 4

Hitting return gives us the VAT amount, £1,206.28, as in Spreadsheet Data 5.

Spreadsheet Data 5

We write in the equations for December and January. The VAT rate for these two months, however, was 15%, so the equation to determine the Total excl. VAT has to change. Instead of dividing by 1.175, as we did for November, we divide by 1.15. Therefore the equation in cell C7 is =B7/1.15. Spreadsheet Data 6 shows the completed table.

Spreadsheet Data 6

In Row 9, Frankie adds a TOTAL row. He uses the SUM function to add the totals for each category together. See Spreadsheet Data 7.

Spreadsheet Data 7

Spreadsheet Data 8 shows the the final table that Frankie has prepared. This shows the Total (incl.VAT), the Total (excl.VAT) and the VAT itself. Now he knows what his turnover was, how much is his and how much he owes for VAT.

Spreadsheet Data 8

If you would like us to cover any specific area in future spreadsheet tips, please contact us.

..back to top