Tax Computation

[anyaley via AccessMonster.com]

I want to compute taxes on Grosspay in a test access payroll query.

Conditions are as follows:

Grosspay is a calculated field in the query and is monthly.
It must be converted to annual-salary(salary*12) before computation based on
below conditions.The end tax result must be recoverted to monthly(annual
tax/12).
Below is the Tax Table(tbltx).
FROM = Begining range of Gross pay(begrng)
TO = End range of gross pay(endrng)
RATE = Rate applied on excess over beginning range(rate)
ADD = Additional amount after applying rate(addamt).
Example:
Gross pay= 1,500
Tax= 1500*12=18000(category 2)

(18000-12001)=5999
(5999*5%)=299.95
(299.95+240)=539.95(annual tax)
(539.15/12)=44.99(monthly tax)

Tax = 44.99(tax this month)

FROM TO RATE% ADD AMT
1 1.00 12,000 2 0.00
2 12,001 50,000 5 240
3 50,001 100,000 10 2,140
4 100,001 200,000 15 7,140
5 200,001 400,000 20 22,140
6 400,001 800,000 25 62,140
7 800,001 1,200,000 30 162,140
8 1,200,001 999999999 35 282,140

Between 1.00 and 12000=2% .
12,001 to 50,000=5% of excess over 12001 plus 240
50,001 to 1000,000=10% of excess over 50,001 plus 2,140
100,001 to 200,000=15% of excess over 100,001 plus 7,140
200,001 to 400,000=20% of excess over 200,001 plus 22,140
400,001 to 800,000=25% of excess over 400,001 plus 62,140
800,001 to 1,200,000=30% of excess over 800,001 plus 162,140
,200,001 to 999999999 35% of excess over 1,200,001 plus 282,140

 

[Michel Walsh]

SELECT x.amount, MAX( y.[ADD AMT] + (x.Amount-y.[from] ) * y.[rate%]) / 12
FROM tableOfAmounts As x INNER JOIN tableOfTaxRates AS y
ON x.amount >= y.[from]
GROUP BY x.amount



Not that I don't use the [to] column, assuming that the tax to pay
increases as the amount increase, that column is then not really required. I
think the values for that column should be 0, 12000, 50000, ... rather than
1, 12001, 50001, ...


Vanderghast, Access MVP