So I'm trying to determine where a salary appears on our tax tables.
We have 12 provinces and territories, each having their own tables (not including the 13th Quebec as they have a different system), and we have tables for our federal taxes. Each of these jurisdictions have additional tables, each with a specified pay period (weekly, biweekly, semi-monthly, and monthly). I'm going to use the Biweekly tables as an example here.
For the province of Manitoba, the tax table pdf will have one Federal table (several pages long) then continue with the provincial tables (again several pages long).
For your reference I've included a link to the website where this pdf is available
T4032MB - Payroll Deductions Tables - Income tax deductions - Manitoba - Effective January 1, 2013
The file name is t4032mb-26pp-13eng.pdf
On the left there are ranges of numbers, and my salary has to fall within a range of numbers before I can figure out the taxes it represents in the columns for what is referred to as Claim Codes.
The Claim Codes are simple to figure out, its the range I'm having problems with.
I'll begin with the Federal tax deductions, which begins on the first page. The from and less than range begins with 495, and the last number on that page will be 711. Each range has a difference of four.
On the next page it will be a difference of 8, then 16, then 24, then 32, then finally a difference of 40 which ends with the top range of 7271-7311
So its simple to a manual check on where the salary will cross reference the Claim Code and we'll have the tax deduction at source. What I want to do is automate this, so if I were to state my salary was 1036, the macro or udf would return a value that is the median of the From and Less than values where my salary finds itself.
My example of 1036 would find itself between 1031 and 1039 with a median of 1035. I would like to enter my salary in one cell and this value to appear next to it.
IF possible I would like to be able to call up the From and Less than ranges within 3 levels lower and three levels higher in addition to the range described above.
The next tricky part is each pay period will have a different range of numbers and each province/territory will begin their tables with different values.
If I were to imagine a udf for this, I would guess the arguments would be the province (prov as string), the pay period (pp as string), the salary (sal as currency), with maybe a select case based on the range of numbers available.
I'm not really sure where to start.
Can anyone assist with this? Thank you for any comments you may have.
-- g
We have 12 provinces and territories, each having their own tables (not including the 13th Quebec as they have a different system), and we have tables for our federal taxes. Each of these jurisdictions have additional tables, each with a specified pay period (weekly, biweekly, semi-monthly, and monthly). I'm going to use the Biweekly tables as an example here.
For the province of Manitoba, the tax table pdf will have one Federal table (several pages long) then continue with the provincial tables (again several pages long).
For your reference I've included a link to the website where this pdf is available
T4032MB - Payroll Deductions Tables - Income tax deductions - Manitoba - Effective January 1, 2013
The file name is t4032mb-26pp-13eng.pdf
On the left there are ranges of numbers, and my salary has to fall within a range of numbers before I can figure out the taxes it represents in the columns for what is referred to as Claim Codes.
The Claim Codes are simple to figure out, its the range I'm having problems with.
I'll begin with the Federal tax deductions, which begins on the first page. The from and less than range begins with 495, and the last number on that page will be 711. Each range has a difference of four.
On the next page it will be a difference of 8, then 16, then 24, then 32, then finally a difference of 40 which ends with the top range of 7271-7311
So its simple to a manual check on where the salary will cross reference the Claim Code and we'll have the tax deduction at source. What I want to do is automate this, so if I were to state my salary was 1036, the macro or udf would return a value that is the median of the From and Less than values where my salary finds itself.
My example of 1036 would find itself between 1031 and 1039 with a median of 1035. I would like to enter my salary in one cell and this value to appear next to it.
IF possible I would like to be able to call up the From and Less than ranges within 3 levels lower and three levels higher in addition to the range described above.
The next tricky part is each pay period will have a different range of numbers and each province/territory will begin their tables with different values.
If I were to imagine a udf for this, I would guess the arguments would be the province (prov as string), the pay period (pp as string), the salary (sal as currency), with maybe a select case based on the range of numbers available.
I'm not really sure where to start.
Can anyone assist with this? Thank you for any comments you may have.
-- g
