9 Replies Latest reply on Jun 28, 2012 5:17 PM by keywords

    creating calculations to figure payroll taxes

    peteregg

      I have 2 employees that I have on payroll, both of whom are single and they use the same tax figures. I have a fed wt field with the following calculation:

       

      Case (gross less deductions ≥ 721 ; (gross less deductions - 721) *(.25) + 93.6 ;gross less deductions > 209 ;( gross less deductions-209) * (.15) + 16.8; (gross less deductions > 0) ; (gross less deductions - 40) * .1 )

       

      this works for what I have been doing except I now have a married employee that uses a different table. I need help to have the calculation different based on marital status. Is there a simple solution? or should I be doing this a different way? the same situation exists for NYS Payroll tax.

       

      Thanks

      pete

        • 1. Re: creating calculations to figure payroll taxes
          erolst

          What exactly are the differences for a married person?

          • 2. Re: creating calculations to figure payroll taxes
            peteregg

            The deductions are different but more importantly thebreaks in the taxable income are at differnt amounts and different percentages. In other words if i could calculate the same as i do now but have it calculate the same way with different numbers based on mariital status, it should work. Can i set up a case to do this or rethink the approach??

            Pete

            • 3. Re: creating calculations to figure payroll taxes
              comment

              You shouldn't be hard-coding data into calculation formulae. Set up a table (or tables) for the breaks, rates etc. and use relationships and lookups to get the appropriate numbers.

              • 4. Re: creating calculations to figure payroll taxes
                karendweaver

                Hi Peter,

                 

                I would rethink the approach, just a bit.

                 

                First, you might want to use a lookup table with the appropriate income levels and breakpoints for each marital status - you already have this info, but you are hard coding it into your calculation.  When the rates change, and you change your calculation - you will also change your previous records - not something you want.  With a lookup table, when the rates change, only new records will be affected, and you can update your tax tables all in one place.  Then you can lookup the appropriate numbers for your calculation based on status.

                 

                Next, take a look at the Let function - using Let you can set variables for different cases.  It also would be worthwhile to look at some custom functions ( http://www.briandunning.com/customfunctions is a great resource ) if you are using FMP Advanced.  With a lookup table, and some custom functions, it will be much easier to design a simpler calc that will do what you want and be a lot easier to maintain.

                 

                This is a pretty generic answer, but if you take a look at some of these options, I am sure you will have some specific scenarios that will make it easier to provide more specific help.

                 

                Karen

                • 5. Re: creating calculations to figure payroll taxes
                  peteregg

                  I have been doing it like this for a year not realizing it will be an issue. Im not too sure exactly how to do that with a table.i understand the imporrance of simplifying the actual calculation but not sure how to proceed. I can never seam to find a procedure that lays it out clear.

                  • 6. Re: creating calculations to figure payroll taxes
                    keywords

                    I agree with Karen's comments on this being a situation in which a Lookup is needed. And you should certainly look into using Let() to simplify your calculations and make them more efficient.

                     

                    As it stands:

                    Case (gross less deductions ≥ 721 ; (gross less deductions - 721) *(.25) + 93.6 ;gross less deductions > 209 ;( gross less deductions-209) * (.15) + 16.8; (gross less deductions > 0) ; (gross less deductions - 40) * .1 )

                     

                    your calc makes at least 2 and possibly 6 calls on the field gross less deductions which I'm guessing is itself a calc calling on other fields. You also have the same numbers (721 and 209) used twice each increasing the maintenance. Here is one way you could simplify it and make it more manageable, just be reducing each of these calls to one each:

                     

                    Let([

                    pay = gross less deductions ;

                    upperlimit = 721 ;

                    lowerlimit = 209 ] ;

                    Case ( pay ≥ upperlimit ; ( pay - upperlimit ) * 0.25 + 93.6 ; pay > lowerlimit ; ( pay - lowerlimit ) * 0.15 + 16.8 ; ( pay > 0 ) ; ( pay - 40 ) * 0.1 ) )

                     

                    Nevertheless, I would take on board the suggestions of others to put your actual figures in a separate table and call them from there in the calc.

                     

                    Cheers!

                    • 7. Re: creating calculations to figure payroll taxes
                      peteregg

                      How would i do that with 2 seperate tables with different limits and percentagees based on marital status? Married is one table and single is another. Every year it changes so i understand the need for simplicity.

                      • 8. Re: creating calculations to figure payroll taxes
                        robwoof

                        Just at a first guess, you would need a "Rates" table, with the following fields:

                        • Year
                        • Marital status
                        • Limit
                        • Percentage

                         

                        Enter a record for each step in the rate schedule for each marital status. From the table where you are calculating the payroll, you would create a relationship on Marital Status and Year to obtain the schedule values that apply in that year to that person, and calculate from there. If you enter the steps in the schedule in ascending order, you could use PayLimits = List ( Rates::Limit ) and TaxPerc = List ( Rates::Percentage ), and work your way through them using GetValue ( PayLimits ; x ) and GetValue ( TaxPerc ; x ) to extract the matching threshholds and their respective tax rates.

                         

                        Hope this helps.

                         

                        Rob

                        • 9. Re: creating calculations to figure payroll taxes
                          keywords

                          You should be able to store all the limits and percentages in a single table (I assume ther will only be two records in the table with all the necesary figures in separate fields), including a marital status field on that table; then link from your employee table to the relevant record with a relationship based on marital status.