What exactly are the differences for a married person?
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??
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.
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.
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.
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:
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.
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.
Just at a first guess, you would need a "Rates" table, with the following fields:
- Marital status
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.
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.