It looks like you pretty much have it. Maybe just a little shift of the moving parts.
Just to define the question, is it true that on the pay period which crosses the gross pay $7000 threshold the futa tax equals the difference between $56 and the amount already paid ? ($56 - $45.58 = $10.42)
Could the FUTA tax calc be a case statement that asks the question "pseudonically" - if the sum of the gross pay => max futa wages:
then max tax - sum of taxes paid followed by OR if sum of tax paid = max tax (56) : 0
There may be rules that make this simple approach unworkable.
First reaction, these two lines need to be at the top of your list of calcs. (you must define a value in a field before you use it elsewhere)
FUTA Max Wages = Setup::Max FUTA Wages
FUTA Rate = Setup to::FUTA Tax%
Second reaction, FileMaker won't allow you to enter Field Names with % if they are to be used in subsequent calcs, so you might want to replace the '%' in those parts of the field names with 'pc' for example.
Best wishes - Alan Stirling, London UK
The picture below explains my problem.
I am afraid it doesn't - at least not to me.
FileMaker won't allow you to enter Field Names with % if they are to be used in subsequent calcs
I don't see how it's related to the original question - but in any case, it's not true.
I need the FUTA calc to:
1. accumulate the FUTA Tax liability for each pay period on Gross Wages under the FUTA Max Wages of 7,000 until it reaches the the FUTA Max Wages amount of 7,000 or (using the FUTA max tax amount of 56.00)[I've tried this both ways];
2. then stop calculating the liability and show zero for FUTA Tax;
3. and then show the excess wages amounts based on max wages of 7,000 being reached after reaching the 'Max' amount.
Right now the calc stops short of the 'Max' amount of 56.00, and doesn't calc correctly at this point as well.
I am afraid you grossly overestimate my powers of comprehension...
In general, when you have a cap on a sum, you would use a summary field to produce the actual sum, and a calculation field to calculate the capped sum (and another for the excess, if desired).
For example, in a table having fields for Category and Amount (when sorted by Category):
Min ( GetSummary ( sTotalAmount ; Category ) ; gCap )
where sTotalAmount is a Summary field [Total of Amount] and gCap holds the max value for a category.
>FUTA Tax = Round(If(FUTA Max True;1;FUTA Cal);2)
This looks like the problem. If you've maxed out the FUTA on this check, then the tax is 1. That's not right. You could simplify some of these calcs, but to keep it short, your Tax calc should be:
FUTA Tax = Round ( FUTA Rate * ( Max ( Max Wages - Gross Pay YTD + Gross Wages ; 0 ) ) ; 2 )
This also eliminates the need for the FUTA Calc field.
I was anxious to plug this in but when I did I get all zero's?
No, I don't grossly overestimate your powers of comprehension but rather my lack of ability to explain myself.
I believe there is a very simple way to get what I want but I don't know how.
Let me 'try' to explain again.
I need a FUTA Tax to be calculated against the Gross Pay per week and stop when the running tax 'total' equals 56.00.
Is this a circular reference?
Yeah, I may have an error in that calc. Let's re-write it and see if the logic makes sense:
//Gross Pay YTD is the sum of all pay including this week's. So let's subtract this week's pay and get all pay up until this one.
prevPay = Gross Pay YTD - Gross Wages ;
//what wiggle room do we have between our old pay and max wages?
availablePay = Max Wages - prevPay ;
//but let's not let that number be less than zero
availablePay = Max ( availablePay ; 0 ) ;
//which is less, available or actual pay?
taxable = Min ( availablePay ; Gross Wages )
The result of this calc is the taxable wages. Multiply that by the tax rate.
The calc is not accepted because 'List is not allowed in the function'.
I'm using a Mac does that make a difference?
There may be a typo, but that's a level of help I'm not prepared to give.
I think it's important that you understand what the calc is doing, and you should be able to re-write it yourself so it has the proper syntax.
I've been re-writing it since you mailed.
I guess I should educate myself on Let, Min & Max functions.
I've never used them all this time.
Oh geez it was just a matter of removing a semicolon!