Adding new Income Tax formulas for different countries.

Discussion for TimeTrex open source community developers.
mikeb
Posts: 254
Joined: Thu Jul 27, 2006 11:58 am

Adding new Income Tax formulas for different countries.

Post by mikeb » Wed Jan 10, 2007 2:41 pm

Adding tax formulas to TimeTrex for new countries is relatively straight forward.

We recommend using the Canadian payroll deduction classes as templates, as they are slightly less complex than the US classes.

*NOTE: If you decide to add tax formulas for a country please make a post here BEFORE you start, so other people know what is happening and duplication of work is avoided.

Here are the general steps:

1. Create a directory with the two letter country name in classes/payroll_deduction/, ie: classes/payroll_deduction/CR for Costa Rica.

2. Copy classes/payroll_deduction/CA.class.php to CR.class.php. This file will contain the tax formula functions at the federal level.

3. Copy classes/payroll_deduction/CA/Data.class.php to classes/payroll_deduction/CR/Data.class.php. This file will contain helper functions to get any data that the tax formulas functions require to do their calculations. Things such as tax rates for each tier/bracket.

4. If the country has several tiers/brackets of income tax rates, create a table similar to income_tax_rate and name it income_tax_rate_cr.

5. Modify classes/payroll_deduction/CR/Data.class.php so it knows how to get the data from income_tax_rate_cr properly, and also add any other static arrays of data that may be needed. This allows for easy separation of data that may change each year (or more often).

6. Modify classes/payroll_deduction/CR.class.php so it implements federal income tax formulas using data obtained from functions in Data.class.php.

7. In cases where a country may have provinces, states, or districts with custom tax formulas, add a new class for each that is necessary in classes/payroll_deduction/CR/ and implement tax formulas in those files.

8. Modify classes/modules/users/UserDeductionFactory.class.php function getDeductionAmount() under "case 100:" (federal) and "case 200:" (province/state) so it knows how to call the appropriate functions you created in CR.class.php and CR/<state>.class.php in order to obtain the final deduction amount for each pay stub. This is also where you can pass additional UserValues such as marital statuses, allowances, tax credits or anything else custom to each employee.

Final Step: Submit your code to TimeTrex (support@timetrex.com) for review and to be included in the main branch. Anyone (or company) that actively implements and maintains payroll deduction code for a country may be offered our TimeTrex Professional Edition (On-Premise) with 50 employee licenses free of charge for as long as they actively maintain their code and update the tax rates/forumlas as necessary.

We greatly appreciate any and all community support.
Mike Benoit

danda
Posts: 1
Joined: Thu Jan 11, 2007 12:41 pm
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by danda » Thu Jan 11, 2007 12:54 pm

Hi Mike,

Thank-you for the very detailed instructions. That's exactly what we needed to get started.

My company (http://osc.co.cr) plans to implement and maintain the tax policies for Costa Rica and other central american companies. We will start with CR, and move on from there. We will post again once we've completed the CR work and/or have decided which exact countries to do next.

We have also implemented basic i18n support in TimeTrex and have translated it into spanish. This is based on a slightlly older version of TT, and we are about to merge with the latest code-base. After the merge, we would be interested in working with you to get that code merged into the main tree, if you'll have it.

I'd like to thank you guys for making this quality application available as Free (as in freedom) Software. It fills a vital missing gap in the f/oss world. At OSC, we are hoping to promote TT in Central America as a way for businesses to reduce costs, and also to show the power of Free Software.

I look forward to further dialogue.

regards,

Dan Libby
Founder, President, Hacker
Open Source Consulting, S.A.

keith
Posts: 1
Joined: Fri Jan 19, 2007 2:08 am

QC taxes

Post by keith » Fri Jan 19, 2007 2:15 am

Hi Mike,

I plan to implement QC, Quebec taxes, but the federal tax deductions are different for Quebec, for example EI changes from table brackets to fixed rate of 1.46% in 2007 and the surplus is put into QPIP funds.

Should I create a new country code called QC? for separate federal and provincial calculations? I don't want Quebec to seperate from Canada, but....
:-)

Thanks,
Keith.

mikeb
Posts: 254
Joined: Thu Jul 27, 2006 11:58 am

Re: QC taxes

Post by mikeb » Fri Jan 19, 2007 10:03 am

keith wrote:Hi Mike,

I plan to implement QC, Quebec taxes, but the federal tax deductions are different for Quebec, for example EI changes from table brackets to fixed rate of 1.46% in 2007 and the surplus is put into QPIP funds.

Should I create a new country code called QC? for separate federal and provincial calculations? I don't want Quebec to seperate from Canada, but....
:-)

Thanks,
Keith.
You shouldn't need to make QC a separate country. The provincial classes (ie: QC.class.php) extend the federal class (ie: CA.class.php) therefore you can place federal functions of the same name within the QC.class.php and it will call those instead.
Mike Benoit

fblauer
Posts: 2
Joined: Mon Apr 20, 2009 10:06 am
Location: Montreal
Contact:

Quebec

Post by fblauer » Mon Apr 20, 2009 10:14 am

Has this been implemented successfully in Quebec? I am also from Quebec, and may have several companies that are interested in using this.
Fred Blauer CA.IT, CISA
www.fblauer.com

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Mon Apr 20, 2009 12:22 pm

Unfortunately Quebec is still not supported at this time.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Tax deduction for Philippines

Post by allanregistos » Wed Oct 21, 2009 11:59 pm

I am trying to create the tax deduction for the country Philippines.
I have already done this using PHP/Mysql procedural method in another payroll software for I do not know yet to program in OOP. Any help would be appreciated.
Thanks,
Allan

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Thu Oct 22, 2009 10:58 am

No programming is necessary, download v3.0 of TimeTrex and go to Help -> Administrator Guide and read about the new Tax / Deduction calculation formulas.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Post by allanregistos » Thu Oct 22, 2009 9:27 pm

shaunw wrote:No programming is necessary, download v3.0 of TimeTrex and go to Help -> Administrator Guide and read about the new Tax / Deduction calculation formulas.
That would be awesome.
As a quick view of the formula we have in PH here:
The table:

Image

Example of taxable income:
Taxable Income = 8,341.36[Gross Pay] - 100.00[Exemption] = 8,241.36

Withholding Tax = ( (8,241.36[Taxable Income] - 6,042[Bracket] ) x 0.20[%over] ) + 354.17[Bracket Tax] = PHP 794.04

Withholding tax = 794.04

I am still testing the new tax formulas. But the annual rates given are confusing to me at least. In our scenario, we are only concern of the taxable income in any period, not based on annual projections.

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Thu Oct 22, 2009 10:48 pm

Keep in mind pay periods are variable, there is weekly, bi-weekly, semi-month, monthly, and also "custom" periods. That is why TimeTrex tries to base everything off annual amounts, otherwise if you had employees in two different pay period schedules, you would need multiple tax / deductions for each, causing way too much work.

You don't mention if the amounts in your table are monthly or not, but its pretty straight forward to calculate annual amounts in most situations.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Post by allanregistos » Thu Oct 22, 2009 11:03 pm

shaunw wrote:Keep in mind pay periods are variable, there is weekly, bi-weekly, semi-month, monthly, and also "custom" periods. That is why TimeTrex tries to base everything off annual amounts, otherwise if you had employees in two different pay period schedules, you would need multiple tax / deductions for each, causing way too much work.

You don't mention if the amounts in your table are monthly or not, but its pretty straight forward to calculate annual amounts in most situations.
Thanks for the quick reply. The table shows a calculation for Semi-Monthly. As I can see, I have more variables than the tax formulas offered by Timetrex such as the bracket(for Married/Single with 1 dependent) and the bracket tax. I have made 8 brackets in Timetrex using Advance Percent (Tax Bracket Alt.), but have no idea where to put the variables I mentioned above so the computation is not accurate in paystub.

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Post by shaunw » Fri Oct 23, 2009 8:33 am

That is the only limitation with the built-in formulas, there are no variables for marital status.

So instead you need to create brackets for each marital status and simply assign the proper group of brackets to the proper employees.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re:

Post by allanregistos » Thu Jan 14, 2010 1:02 am

shaunw wrote:That is the only limitation with the built-in formulas, there are no variables for marital status.

So instead you need to create brackets for each marital status and simply assign the proper group of brackets to the proper employees.
Hi shaunw,

I followed your instruction. I created 8 brackets of "Married/Single with 1 Dependent" and assigned that to an employee.
Here is an example of bracket 4:

Image

As you can see from the circled values, I multiplied the original values each to 12 to match the annual projection,
Annual Amount Greater Than: 4375x12=52500
Annual Amount Less Than: 6,042x12=72504
Annual Deduction Amount: 4375x12=52500
Annual Fixed Amount: 104.17x12=1250.04
This is the way I understand my formula in the previous post to be presented.
is this correct?
If this is correct, I still got no output of wIthholding tax field in the pay stub.

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Re: Adding new Income Tax formulas for different countries.

Post by shaunw » Thu Jan 14, 2010 10:28 am

Sorry, your image didn't come through, could try posting it again?

If you can include a copy of the pay stub which the calculate didn't appear on as well that would help.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Re:

Post by allanregistos » Thu Jan 14, 2010 4:09 pm

Hello, this is the post, I'm sorry about the image... I also included the output paystub.
allanregistos wrote:
shaunw wrote:That is the only limitation with the built-in formulas, there are no variables for marital status.

So instead you need to create brackets for each marital status and simply assign the proper group of brackets to the proper employees.
Hi shaunw,

I followed your instruction. I created 8 brackets of "Married/Single with 1 Dependent" and assigned that to an employee.
Here is an example of bracket 4:

Image
The paystub:
Image

As you can see from the circled values, I multiplied the original values each to 12 to match the annual projection,
Annual Amount Greater Than: 4375x12=52500
Annual Amount Less Than: 6,042x12=72504
Annual Deduction Amount: 4375x12=52500
Annual Fixed Amount: 104.17x12=1250.04
This is the way I understand my formula in the previous post to be presented.
is this correct?
If this is correct, I still got no output of wIthholding tax field in the pay stub.

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Re: Adding new Income Tax formulas for different countries.

Post by shaunw » Thu Jan 14, 2010 5:23 pm

Okay, I think its because your annual deduction amount is too high. That value is designed to reduce the employees taxable income by that amount. In the US this would be used as an allowance for the number of children you have or something. So setting it to 52500 basically means the employee must make 52500+<Amount Greater Than> before they fall within that bracket.

The way the calculation works is essentially this:

Pay Period Total Gross * Pay Periods = Annual Wage

Annual Wage - Annual Deduction + Annual Fixed Amount = Taxable Income

The taxable income then must fall between the greater than and less than values for that bracket to be calculated.

Try it without any Deduction/Fixed Amount values and see if that works, then I recommend adding each one back in one by one to make sure you get the proper result.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Thu Jan 14, 2010 5:25 pm

Hi,

I have this new output of paystub. What I did is I edited the salary wage to make the effective date back to january 1, since I added the employee at this date Jan 04.

Image

According to the table in my previous post, withholding tax must be 114.175:
Tax = (Gross Income - (SSS Contri+Philhealth+Pagibig) - Bracket) x percent + Tax bracket
Tax = (5000 - (333.3+125+100) - 4375) x 15% + 104.17
Tax = (4441.7 - 4375) x 15% +104.17
Tax = (66.7) x 15 % + 104.17
Tax = 10.005 + 104.17
Tax = 114.175
but the output is 60.09 which is incorrect .

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Thu Jan 14, 2010 5:33 pm

shaunw wrote:Okay, I think its because your annual deduction amount is too high. That value is designed to reduce the employees taxable income by that amount. In the US this would be used as an allowance for the number of children you have or something. So setting it to 52500 basically means the employee must make 52500+<Amount Greater Than> before they fall within that bracket.

The way the calculation works is essentially this:

Pay Period Total Gross * Pay Periods = Annual Wage

Annual Wage - Annual Deduction + Annual Fixed Amount = Taxable Income

The taxable income then must fall between the greater than and less than values for that bracket to be calculated.

Try it without any Deduction/Fixed Amount values and see if that works, then I recommend adding each one back in one by one to make sure you get the proper result.
Ok, I posted a reply w/o yet reading this post. I will post the result after doing what you said here.

Many Thanks,

Allan

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Thu Jan 14, 2010 8:22 pm

Hi Shaunw,

I have now the correct value of tax as shown:

Image

Thank you so much for your help.
There will be another round of tests for the TimeTrex payroll as it will be configured for more than 50 employees.
I found out that it is more simple to configure a monthly pay period than semi-monthly, not for the tax computations but for other deductions such as stated in the paystub, philhealth, sss and pagibig. because these deductions requires only to be done at the end of every month, not on every pay period.
Is there any solution somehow in this forum...
How can I do this?

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Re: Adding new Income Tax formulas for different countries.

Post by shaunw » Fri Jan 15, 2010 10:24 am

We never recommend setting up pay periods that are not actually correct, that is likely to cause you more problems in the long run.

Tax / Deductions happen every pay period, but in most cases its the end result that is what matters, not how often something occurs. For example if philhealth of $100 is only required once a month, for the employee to be deducted $50 on a semi-monthly pay period schedule doesn't make much difference.

You could try using Recurring PS amendments which can have a monthly frequency, but they don't have the advanced calculation methods that Tax / Deductions do.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Fri Jan 15, 2010 6:07 pm

shaunw wrote:We never recommend setting up pay periods that are not actually correct, that is likely to cause you more problems in the long run.

Tax / Deductions happen every pay period, but in most cases its the end result that is what matters, not how often something occurs. For example if philhealth of $100 is only required once a month, for the employee to be deducted $50 on a semi-monthly pay period schedule doesn't make much difference.

You could try using Recurring PS amendments which can have a monthly frequency, but they don't have the advanced calculation methods that Tax / Deductions do.
Thank you so much for your help.

Our pay period is semi-monthly based.

Actually, there are only two deductions that we wanted to be deducted at the end of every month, SSS and Philhealth, (for Pagibig is constant and not based on brackets) because it was based not on the total gross of every pay period, but of the total gross per month. Yes, you are correct, that it doesn't make a difference if I am going to do the deduction for each pay period. However, like tax deduction, SSS and Philhealth are also based on brackets. Example, for the beginning of the month, an employee's total gross is 1240. Then the employee will be going to be deducted by the value of 33.30 according to the table below. At the end of the same month, the same employee have this total gross: 2240, to be deducted by this amount: 66.70. His total SSS contribution for this month now will be 33.3 + 66.7 = 100, hence SSS Contribution is a government institution and contributions are done once a month only. If I am going to add all his total gross at the end of the same month it will be: 1240+2240=3480, and 3480 is to be deducted according to the table by: 116.70, therefore, I've got two different values, a different SSS monthly contribution of 100 when doing the deduction each pay period, and SSS monthly contribution of 116.7 if the deduction is done at the end of the month. So, I think it is important for me to do the deduction at the end of every month.

Do you have any idea if I can do this through accrual accounts like withdraw and deposit principle?

SSS Table:
Image

Many thanks...
Allan

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Re: Adding new Income Tax formulas for different countries.

Post by shaunw » Fri Jan 15, 2010 7:12 pm

I don't think your calculations are quite correct there, you need to convert the table to match whatever system you decide to use. You can't fit a semi-monthly deduction system in to a monthly bracket table without doing some conversions.

To use the Tax / Deduction system you would need to convert all the values to annual amounts anyways, so over the course of the year everything should work out in theory, assuming that will work from the government standpoint. So on the first pay period of the month whatever the employee makes would be multiplied by 24 pay periods (semi-monthly), then compared to the table you specified multiplied by 12x (12 months/year).

I don't see how accrual accounts would help in this situation.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Thu Jan 28, 2010 10:20 pm

shaunw wrote:I don't think your calculations are quite correct there, you need to convert the table to match whatever system you decide to use. You can't fit a semi-monthly deduction system in to a monthly bracket table without doing some conversions.
To use the Tax / Deduction system you would need to convert all the values to annual amounts anyways, so over the course of the year everything should work out in theory, assuming that will work from the government standpoint. So on the first pay period of the month whatever the employee makes would be multiplied by 24 pay periods (semi-monthly), then compared to the table you specified multiplied by 12x (12 months/year).

I don't see how accrual accounts would help in this situation.

Hi shaunw,

Yes, I converted the table to match the semi-monthly deduction system I am currently using with regards to all deductions except SSS and Philhealth.
It's not acceptable in our situation that SSS and Philhealth is to be done in each pay period, regardless if the total is the same for the whole month. I think, I need a total for both 1st pay period and second pay period gross incomes (valid only for semi-monthly pay period schedule) of which TimeTrex doesn't allow me to do or doesn't have the feature yet. Then there would be no pay stub account (total monthly gross), to base the calculation at the end of every month. So I guess I will resort to coding which is pretty hard, or can you offer some advice?

Thank you,
Allan

shaunw
Posts: 7360
Joined: Tue Sep 19, 2006 2:22 pm

Re: Adding new Income Tax formulas for different countries.

Post by shaunw » Fri Jan 29, 2010 9:28 am

You could try having the Tax / Deduction system calculate the brackets each pay period and deposit the amounts into an accrual pay stub account, (so nothing is actually deducted at this point) then setup a Recurring PS Amendment that only occurs once a month withdraw 100% of the accrual and deduct it from their pay stub, thus restarting the cycle.

allanregistos
Posts: 41
Joined: Wed Oct 21, 2009 11:56 pm
Location: Philippines
Contact:

Re: Adding new Income Tax formulas for different countries.

Post by allanregistos » Sun Jan 31, 2010 11:17 pm

shaunw wrote:You could try having the Tax / Deduction system calculate the brackets each pay period and deposit the amounts into an accrual pay stub account, (so nothing is actually deducted at this point) then setup a Recurring PS Amendment that only occurs once a month withdraw 100% of the accrual and deduct it from their pay stub, thus restarting the cycle.
Hi Shaunw,

Thanks for your support. Your advice is possible only if there is no maximum limit of SSS and Philhealth contributions. As we can see in the table, there is a maximum limit of Php500 that an employee can contribute to SSS regardless of how big his total gross income for the whole month. For example, my total gross is Php 9,500 for the 1st pay period, my SSS will be partially Php316.7. Multiply this value by two I will get Php 633.4 which is beyond the Php 500 limit, not to mention the Employer share. I think I should post a feature request on this at the appropriate section of this forum?

Thanks...
Allan

Locked