1 2 Previous Next 15 Replies Latest reply on Mar 18, 2012 5:19 PM by dchabot

    Need Help with Calc - Almost There I think!

    dchabot

      Greetings Everyone,


      I need your help with a FUTA payroll tax report. I've got it working - to a point!

      The picture below explains my problem.

      FUTA.png

      The futa tax should max at $56.00 but it doesn't.

       

      Don't run away when you see my calcs below. I know it needs help!

       

      FUTA Cal = Gross Pay*FUTA Rate

      FUTA Excess = If ( Gross Pay YTD ≥ FUTA Max Wages ; Gross Pay YTD-FUTA Max Wages)

      FUTA Excess Wages = If ( FUTA Max True=0;FUTA Excess ; 0)

      FUTA Max = Round(FUTA Rate*FUTA Max Wages;2)

      FUTA Max True = If(Gross Pay YTD > FUTA Max Wages;1;0)

      FUTA Max Wages = Setup::Max FUTA Wages

      FUTA Rate = Setup to::FUTA Tax%

      FUTA Tax = Round(If(FUTA Max True;1;FUTA Cal);2)

      FUTA Taxable = If (Gross Pay YTD ≤ FUTA Max Wages;FUTA Max Wages-Gross Pay YTD)

      FUTA Taxable Wages = If ( FUTA Max True=1 ; 0 ; SumGross-SumFUTA Excess )

       

       

      FUTA Tax Rate = .008 and Maximum Taxable wages is 7,000




      Thanks so much!

      Deb C.

        • 1. Re: Need Help with Calc - Almost There I think!
          usbc

          Hi Deb,

          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.

          Chuck

          • 2. Re: Need Help with Calc - Almost There I think!
            AlanStirling

            Hi Deb

             

            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

            • 3. Re: Need Help with Calc - Almost There I think!
              comment

              dchabot wrote:

               

              The picture below explains my problem.

               

              I am afraid it doesn't - at least not to me.

              • 4. Re: Need Help with Calc - Almost There I think!
                comment

                AlanStirling wrote:

                 

                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.

                • 5. Re: Need Help with Calc - Almost There I think!
                  dchabot

                  Hi Michael,

                   

                  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.

                   

                  Recap: I need the FUTA tax to calculate until the FUTA Max Wages of at a rate of .008 = x Gross Wages  is reached, then show the 'excess' non taxable gross wages after that.

                   

                  Right now the calc stops short of the 'Max' amount of 56.00, and doesn't calc correctly at this point as well.

                  • 6. Re: Need Help with Calc - Almost There I think!
                    comment

                    Hi Deb,

                     

                    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):

                     

                    cCappedTotal=

                    Min ( GetSummary ( sTotalAmount ; Category ) ; gCap )

                     

                    where sTotalAmount is a Summary field [Total of Amount] and gCap holds the max value for a category.

                    • 7. Re: Need Help with Calc - Almost There I think!
                      DavidJondreau

                      >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.

                      • 8. Re: Need Help with Calc - Almost There I think!
                        dchabot

                        David,

                        I was anxious to plug this in but when I did I get all zero's?

                        • 9. Re: Need Help with Calc - Almost There I think!
                          dchabot

                          Hi Michael,

                          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?

                          • 10. Re: Need Help with Calc - Almost There I think!
                            DavidJondreau

                            Yeah, I may have an error in that calc. Let's re-write it and see if the logic makes sense:

                             

                            Let([

                            //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 )

                            ];

                            taxable

                            )

                             

                            The result of this calc is the taxable wages. Multiply that by the tax rate.

                            • 11. Re: Need Help with Calc - Almost There I think!
                              dchabot

                              Hi David,

                              The calc is not accepted because 'List is not allowed in the function'.

                              I'm using a Mac does that make a difference?

                              • 12. Re: Need Help with Calc - Almost There I think!
                                DavidJondreau

                                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.

                                 

                                Good luck,

                                David

                                • 13. Re: Need Help with Calc - Almost There I think!
                                  dchabot

                                  I understand.

                                  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.

                                   

                                  Thanks

                                  • 14. Re: Need Help with Calc - Almost There I think!
                                    dchabot

                                    Oh geez it was just a matter of removing a semicolon!

                                    1 2 Previous Next