Excel VAT task

  Graham ® 19:02 20 Sep 05

A friend is claiming back VAT on his self-built house. Most of the bills do not have a breakdown of price + VAT, just the total.

Another complication is the different rates of VAT going back 20 years.

At the moment he is using figures supplied for the purpose involving division and multiplication using a calculator.

Ideally, he would enter the totals and the date to produce the break-down using the correct rate for that period. So the period would have a start and end date.

Any help would be appreciated.

  VoG II 19:22 20 Sep 05

This is an ideal application for a lookup table.

See this mock-up click here with completely made-up VAT rates.

I'm assuming that the VAT rate has always changed on 1 April.

So he would have a date column, then a column with the looked-up VAT rate, then columns for the calculation.

To keep things tidy it would be better to keep the lookup table on a separate sheet, then do the lookup like this:


The syntax is:

VLOOKUP(value to lookup, range to look in, column number in the range to find a value in, True or False)

True finds the nearest match, False tries for an exact match (we don't want that).

  Graham ® 19:33 20 Sep 05

Thanks! The VAT rates are:

8% 29 Jul 74 - 17 Jun 79

15% 18 Jun 79 - 31 Mar 91

17.5% 1 Apr 91 - present date.

Excel is still beyond me, I only use the basic functions!

Would it be easy for you to email me a working model, please?

  Simsy 19:37 20 Sep 05

is quite right... a lookup table is ideal for this, at least for getting the correct vat rate for the timescale.

Does he need help with the arithmetic?

A frequently made error with VAT is calculating the incorrect amount of VAT paid...

If, for example, an inclusive price include VAT @ 17.5% you do NOT find the VAT by taking 17.5% of that figure...

Is he happy about that, or is further help needed on that aspect.

(I don't want to teach granny to suck eggs!)

Post back if more help is needed



  Graham ® 19:37 20 Sep 05

At the moment he is using:

A Date 07/03/74

B Description

C Total

D VAT Rate

E VAT Amount

  Graham ® 19:39 20 Sep 05

Thanks, no, he is using calculations supplied by VAT themselves.

  VoG II 19:49 20 Sep 05

You should have mail.

If not 'yellow envelope' me.

  Graham ® 19:52 20 Sep 05

Received mail. Many thanks once again.

  Graham ® 20:02 20 Sep 05

So if I put a new date in A, and amount in C, how do I get it to perform the calculation?

  Simsy 20:02 20 Sep 05

That you're getting this sorted OK with VoG™s help.

However, in case it proves to be of use, I wrote a small Excel addin for VAT purposes...

click here

The page explains how to use it, with variation in rates possible, and contains the, (very simple), code in case anyone wants to write it themselves.

If you don't find it useful, please excuse the post.. others searching for "VAT" and "Excel" on the forum may find it useful.



  Graham ® 20:12 20 Sep 05

Everything's useful when it comes to Excel and I! I will look at your link now.

This thread is now locked and can not be replied to.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…