Solved

Calculating VAT

Posted on 2004-03-27
5
3,471 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now