Calculate VAT Backwards in a Query (1 Viewer)

[Malcolm17]

Hi,

I'm trying to calculate the VAT Value and Net Value from Gross Value and VAT Rate in a query, however I cannot work out how to do this.

I have a query which is going to update and transfer a table from MS Access 2007 to MS Access 2016 once I have worked out the VAT and Net Totals.

I have the fields [Gross] and [TaxRate], however I need to work out [VAT] and [Net], I currently use 0% VAT, 5% VAT and 20% VAT in the table\query.

Gross would be say 100.00, TaxRate value is 20 (for 20% vat), so i need it to return VAT of £16.67 and Net of £83.33 - for 20% VAT
Gross would be say 100.00, TaxRate value is 5 (for 5% vat), so i need it to return VAT of £4.76 and Net of £95.24 - for 5% VAT

Many thanks in advance for any help!!

Malcolm

 

[theDBguy]

Hi. Not sure I understand your calculations. What exactly is the formula for calculating VAT?

 

[pbaldy]

Hint, the original amount (net) is:

gross divided by one plus VAT

subtract that from gross to get the VAT amount.

 

[cheekybuddha]

SELECT
  [Value] AS Gross,
  [Value] / (1 + TaxRate) AS Net,
  [Value] - ([Value] / (1 + TaxRate) ) AS VAT
FROM ...

Oops, too slow!

 

[theDBguy]

Hint, the original amount (net) is:

gross divided by one plus VAT

subtract that from gross to get the VAT amount.

Okay, thanks. That makes sense. At first, I thought there was something special about how you calculate VATs. Looks like it's the same as how we calculate sales tax. Cheers!

 

[CJ_London]

not sure how sales tax works, but in the EU VAT needs to be calculated on each invoice line, not the invoice total. Also, don't forget to round the value to 2dp - formatting it to 2dp is not the same thing. The way I usually do it is

[Value] AS Gross,
Round([Value] / (1 + TaxRate),2) AS Net,
[Value] - Net AS VAT

 

[Malcolm17]

Hi All,

Thank you for your suggestions, but I am still struggling with this one, I have uploaded a demo database, could someone please be kind enough to have a look at this for me and put me out of my misery!! On the demo database, I have tried all sorts of formula's and still cannot get it to work out correctly, it is the CalculatedTaxAmount field that I am trying to work on, I was just going to subtract Gross from either Net or Tax\VAT to give me the other value.

Maybe you guys would tackle this differently?

Many thanks,

Malcolm

 

[arnelgp]

see this one.

 

[Malcolm17]

Thank you all for your suggestions, but thank you Arnelgp for your answer, this is what I was looking for.

Very much appreciated!!

Malcolm

 

[arnelgp]

you're welcome!

 

[CJ_London]

@yomav - please do not hijack a thread - particularly one that has been answered and would appear to have only a tenuous link to your question. You should start your own thread.

When you do, clarify what you mean by 'reverse charge from a supplier' - provide an example

 

[sonic8]

I was wondering how to handle reverse charged VAT. If it’s towards a customer it’s really easy and you don’t need to mention it at all, but if we get a reverse charge from a supplier there is no easy way to do it.

Where/what is your problem exactly?
For a reverse charge invoice, you would do the VAT calculation exactly as if you yourself issued the invoice.

 

[Cotswold]

I will just add that my advice is that all of the VAT and value results should be completed as Integer calcs.( LongInteger in Access)

If you don't then there is the possibility that totalling lines of VAT, Gross and Net could be a penny out. In your example I would store 10000, 1667 and 8333 in your table as the results. When those are totalled they cannot be a penny out even if there are thousands of lines. In your display you simply correctly insert the decimal.

You should also complete VAT calcs on individual lines as stated by CJ_London #6. You should also indicate what the VAT rate is for each line. Most use a letter, as in SAGE as it uses less space. The invoice should also show the different VAT Rate and their totals. So yours will need three and possibly more in the future. So your system should be able to cope with many VAT Rates from the start. Also bear in mind that a VAT Rate has been 17.5% in the past and could easily be so again. Which may affect your table field settings.

Furthermore, your VAT Rates should be in a system table with their applicable date range to which they apply and not hardwired in code. If VAT rates are hardwired and they change your code will need to be updated every time which can be a nightmare when VAT has to be calculated for different VAT periods. For instance the VAT rate changes on the 30th Sep so all charges inclusive to the 30th will be at a different rate to those from the 1st Oct onwards. Your software should handle that automatically.

VAT is a simple concept that can become complex in practice. HMRC and accountants will be unforgiving if there are errors.