Solved

Calculating VAT

Posted on 2004-03-27
5
3,479 Views
Last Modified: 2011-10-03
Hello there
i am trying to get a formula to calculate vat. what the formula should do is that whne i enter amount which includes vat and tab on to the next field the formula should automactically calculate the amount exc vat and the vat price. please help me anyone
0
Comment
Question by:ksunnyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 84 total points
ID: 10694029
Just register one type of amount and use in queries and on forms the calculation like:
SELECT AmountIncludingVAT, [amount]/((100+7)/100) AS ExAmount, [ExAmount]*((100+7)/100) AS [InAmount]
FROM Table1;

In a form field use for getting the ex value:
=[amount]/((100+7)/100)

Best probably to store the VAT percentage in a global variable or table, thus making a change easy.
Most flexible (but hardest to use) will be to have a table with the tax rate and the date when it started, thus allowing historical calculations when the VAT rate changes...

Nic;o)
0
 
LVL 8

Assisted Solution

by:jb79uk
jb79uk earned 83 total points
ID: 10694046
If the total price is held in a field called TotalAmount then the formulae would be:

For the amount excluding VAT:  =TotalAmount * (1/1.175)

For the VAT:  = TotalAmount * (.175/1.175)

This example assumes the current standard UK VAT rate of 17.5%.  Nico's point about storing the VAT rate is a good one.
0
 
LVL 12

Assisted Solution

by:monosodiumg
monosodiumg earned 83 total points
ID: 10694291
There are VAT codes witha  rate other than or 0% or 17.5%. You might want to add a combo for the VAT code and put the VAT rate into one fo the column and use that as the source of the VAT rate in the above calculations.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question