We recently got change on our tax and this has affected only the range to which 385.00 and less is the non taxible amount.The current table shows this,
Range CoefficientA CoefficientB K7,000.00 0 K0.00 K18,000.00 0.22 K1,540.00 K33,000.00 0.3 K2,980.00 K70,000.00 0.35 K4,630.00 K250,000.00 0.4 K8,130.00 K999,999,999.00 0.42 K13,130.00
Now with the new 1 the table is currently as follows,
However, Im confuced as to what cofficientB represents and most likely its probably why there a no diffrences while tax is calculated.Could someone help me on this one.The new table has been amended according to the new range tax send this month.This is how the module looks like at the moment [
Range CoefficientA CoefficientB K9,000.00 0 K0.00 K23,000.00 0.22 K1,540.00 K33,000.00 0.3 K2,980.00 K70,000.00 0.35 K4,350.00 K250,000.00 0.4 K7,850.00 K999,999,999.00 0.42 K12,850.00
'*****************************************
'
' Use this module for tax computation only
'
'*****************************************
Option Compare Database
Option Explicit
Function Get_Tax(vEmpno, vPayRef, vGross, vDep) As Double
Dim vTax1, vTax2, vSumTaxSeparate
Dim sqlst, vAmount, vDays, vLoop, vCtr
Dim rst As Recordset
Dim vDailyRate
Dim ctr, vFNDays
Dim vMaxSickDays, vRatePerHr
Dim vMaxDays
Dim vTotalTax
vFNDays = DLookup("[TaxFnDays]", "systemsettings")
' sum up the tax separated items
vSumTaxSeparate = DSum("[Amount]", "qryjoballocation", "taxseparate='1' and Payreference='" & vPayRef & "' and [empcode] ='" & vEmpno & "'")
If IsNull(vSumTaxSeparate) Then
vSumTaxSeparate = 0
End If
' total gross - separated tax
vGross = vGross - vSumTaxSeparate
'computer tax set on regular tax
vTax1 = Compute_Tax(vGross, vDep)
'individually compute for tax separated items
vTax2 = 0
sqlst = "select * from tblFNJobsAndRates where (taxseparate='1' and Payreference='" & vPayRef & "' and empcode ='" & vEmpno & "');"
Set rst = CurrentDb.OpenRecordset(sqlst)
If Not (rst.EOF) Then
Do While Not rst.EOF
vAmount = rst!HrsWorked * Val(rst!Rate)
vDays = rst!Count
vDailyRate = vAmount / vDays
' vTaxArray = 0
'number of days specified in entry
'check if sick or leave pay
'get the total amount for the vl/sl and compute tax on a per day basis
'get the total number of leave/sick days and base the tax from the total amount
vMaxDays = 0
vRatePerHr = 0
If rst!PayRate = "4" Or rst!PayRate = "3" Then 'vl and sick leave
If rst!PayRate = "4" Then
vMaxDays = DLookup("[SickLeaveAllowed]", "tpm_personal", "empcode='" & vEmpno & "'")
vRatePerHr = Val(rst!Rate)
' vRatePerHr = DLookup("[RatePerHour]", "tpm_personal", "empcode='" & vEmpno & "'")
vTotalTax = Compute_Tax_wDays(vMaxDays, 8 * vRatePerHr, vDep, vFNDays)
vTax2 = vTax2 + ((vTotalTax / vMaxDays) * vDays)
Else ' vl leave
vMaxDays = DLookup("[paidLeaveAllowed]", "tpm_personal", "empcode='" & vEmpno & "'")
vRatePerHr = Val(rst!Rate)
'vRatePerHr = DLookup("[RatePerHour]", "tpm_personal", "empcode='" & vEmpno & "'")
vTotalTax = Compute_Tax_wDays(vMaxDays, 8 * vRatePerHr, vDep, vFNDays)
vTax2 = vTax2 + ((vTotalTax / vMaxDays) * vDays)
End If
Else
If vDays > vFNDays Then
vTax2 = vTax2 + Compute_Tax_wDays(vDays, vDailyRate, vDep, vFNDays)
Else
vTax2 = vTax2 + Compute_Tax(vAmount, vDep)
End If
End If
rst.MoveNext
Loop
End If
rst.Close
Get_Tax = vTax1 + vTax2
End Function
' number of days, rate per hour, dependants, 11 - use for fnday
Function Compute_Tax_wDays(ppDays, ppDailyRate, ppDep, ppFNDays) As Single
Dim arrayAmount(1 To 26), arrayTax(1 To 6)
Dim vTaxArray
Dim ctr, vLoop
Dim pDays, pDailyRate, pDep, pFNDays
pDays = ppDays
pDailyRate = ppDailyRate
pDep = ppDep
pFNDays = ppFNDays
If pDays = 0 Then
Compute_Tax_wDays = 0
Exit Function
End If
vTaxArray = 0
vLoop = Int(pDays / pFNDays)
ctr = 1
Do While pDays > pFNDays
arrayAmount(ctr) = pFNDays * pDailyRate
arrayTax(ctr) = Compute_Tax(arrayAmount(ctr), pDep)
vTaxArray = vTaxArray + arrayTax(ctr)
pDays = pDays - pFNDays
ctr = ctr + 1
Loop
arrayAmount(ctr) = pDays * pDailyRate
arrayTax(ctr) = Compute_Tax(arrayAmount(ctr), pDep)
vTaxArray = vTaxArray + arrayTax(ctr)
Compute_Tax_wDays = vTaxArray
End Function
Function Compute_Tax(pGross, pDependants) As Single
Dim N, sqlst, coA, coB, GrossTax
Dim ttax As Recordset
Dim DepRebate, NetTax
Dim tens, ones
If IsNull(pDependants) Or pDependants = "" Then
pDependants = 0
End If
If pDependants > 3 Then
pDependants = 3
End If
If IsNull(pGross) Then
pGross = 0
End If
' this routine will accurately compute the tax to the nearest toea
' the amount should always be an odd number to get a correct tax
' only applicable for amounts less than 800
If pGross < 800 Then
tens = Int(pGross)
ones = pGross - tens
If ones > 0 Then
tens = tens + 1
End If
If (tens Mod 2) = 0 Then
tens = tens + 1
End If
pGross = tens
End If
'step 1. calculate income N
'=============================
N = (pGross * 26) - 200
'step 2. calculate gross tax
'===========================
sqlst = "select * from tblTaxResidentRates where range >= " & N
Set ttax = CurrentDb.OpenRecordset(sqlst, dbOpenSnapshot)
ttax.MoveFirst
coA = ttax!CoefficientA
coB = ttax!CoefficientB
ttax.Close
GrossTax = (N * coA) - coB
'step 3. calculate dependant rebate
' values are hard coded because the formula also changes when values are changed
'==================================
If pDependants = 0 Then
DepRebate = 0
Else
If N >= 0 And N <= 7800 Then
DepRebate = 45 + (30 * (pDependants - 1))
ElseIf N >= 7800.01 And N <= 18500 Then
DepRebate = GrossTax * (0.15 + (0.1 * (pDependants - 1)))
ElseIf N >= 18500.01 Then
DepRebate = 450 + (300 * (pDependants - 1))
End If
End If
NetTax = GrossTax - DepRebate
If NetTax < 0 Then
NetTax = 0
End If
'step 4. calculate fortnight tax
'===============================
Compute_Tax = Format(NetTax / 26, "#0.00")
End Function
] I only have limited time to do this and I have been working night and day to figuring this out.Kindly assist as I badly need help and my time is running out.