1 2 3 Previous Next 43 Replies Latest reply on Jan 26, 2012 4:17 PM by comment

    How to Round numbers?

    dchabot

      Good day to all.

       

      I have a payroll database and am really struggling with rounding of numbers.

      The calculations involve the usuall fed, fica etc.

       

      If the fica calc is .042 * Gross Pay, should I round that calc to three, and then when the paycheck is created,

      do I round that number to two?

       

      I had all the beginning calculations rounding to the largest number of digits needed

      and in paychecks rounding to 2.

       

      Or should I round everything to two from the start?

       

      What I'm finding is the net pay, of all things is not coming out???

      Gross Pay - Fed - Fica - State - Deductions = Net Pay (but net pay is always off)

      It's always off 4 cents. The taxes and deductions seem perfect, so I'm stumpted.

       

      Does Filemaker go about this differently?

       

      For example if I round the full digits, all the way through to paycheck, and then have paycheck 'show' only 2 digits, it works.

       

      You'd think I answered my own question, but I have to wait until next payrun to see if my statement works.

       

      Thanks

       

      Message was edited by: dchabot I can't see where I can make this post a question so I can give ratings to the wonderful people that help me????

        • 1. Re: How to Round numbers?
          Mike_Mitchell

          I generally let the calculation carry all digits until I get to the final answer, then round. YMMV.

           

          Mike

          • 2. Re: How to Round numbers?
            datastride

            I would HIGHLY recommend you round each calculation to two digits. For example, you want the FICA deduction to be two digits to ensure that Gross Pay minus each of the deductions equals Net Pay (with all discrete amounts being rounded to two digits. Also, you want the total of all FICA deductions for all employees to total exactly what the sum of the two-digit numbers calculated for each employee.

             

             

             

            Do not carry any additional digits or you are just asking for trouble. Trust me. I’ve been doing accounting software for over 35 years.

             

             

             

            Peace, love & brown rice,

             

            Morgan Jones

             

             

             

            FileMaker + Web:  Design, Develop & Deploy

             

            Certifications: FileMaker 9, 10 & 11

             

            <http://www.onepartharmony.com/> One Part Harmony 

             

            Austin, Texas • USA

             

            512-422-0611

            • 3. Re: How to Round numbers?

              Hi dchabot,

               

              I suggest that you seek the advice of a payroll or tax professional who are up on the current rules (it can vary from state to state).  Once the rules are pinned down, we can verify or correct your calculation process accordingly. 

               

              Payroll and taxes are complex and Federal and State fines can bust you, not to mention law suits from disgruntled employees.  The process should be scrutinized by professionals and clearly documented in Employee contracts.  Because of the ever-shifting rules, most businesses (if they do not have a fully-qualified Payroll Accountant) hire out to payroll services whose fees are minimal.   Tracking the rules, charts, labor laws and such can be a constant headache.

               

              Here is the first place to start:  http://www.irs.gov/pub/irs-pdf/p15.pdf

               

              This is one area you do not want to get wrong and it really is a business rule and not a programming one. 

              • 4. Re: How to Round numbers?
                comment

                In any case, you should NOT round unless you have a good reason to. An example of a good reason is when calculating check amounts: you want to round these to the nearest cent, otherwise the total of the check amounts will not match the total charged to your bank account.

                • 5. Re: How to Round numbers?
                  dchabot

                  Michael,

                  I'd have to agree with you because that's exactly what is off the net pay.

                  So, I should remove ALL rounding calcs and just let the numbers fall?

                  Paychecks will print showing the truncated 2 decimals ONLY?

                   

                  Is it advisable to carry all these decimal places into the General Ledger?

                  • 6. Re: How to Round numbers?
                    datastride

                    That is absolutely the wrong approach. Round each calculation to two digits and you will find the paycheck will balance.

                     

                     

                     

                    Accounting is done with two decimal places. Period.

                     

                     

                     

                    Peace, love & brown rice,

                     

                    Morgan Jones

                     

                     

                     

                    FileMaker + Web:  Design, Develop & Deploy

                     

                    Certifications: FileMaker 9, 10 & 11

                     

                    <http://www.onepartharmony.com/> One Part Harmony 

                     

                    Austin, Texas • USA

                     

                    512-422-0611

                    • 7. Re: How to Round numbers?
                      psijmons

                      I agree with Morgan, when doing this type of financials, round to cents.

                      Except when you need to use currency rates, which are usually 4 -6 decimals.

                      • 8. Re: How to Round numbers?
                        Stephen Huston

                        Morgan's right.

                         

                        Unless you want to track fractions of cents which you can't actually pay thru a bank, rounding is necessary at all levels.

                         

                        Only Exception: We had a situation where a client wanted to see tax amounts per line-item on invoices, and we had to show it to 3 places so that the final tax amount on the invoice was within 1-cent of the cummulative shown or the client's accounting dept got upset of the mismatch. Still, the final tax amount had to be rounded, and that rounding had to be done before totalling the various invoice subtotal amounts to avoid problems.

                         

                        Round everything to the nearest cent before doing more calcs on the figures.

                        • 9. Re: How to Round numbers?
                          comment

                          Morgan Jones wrote:

                           

                          That is absolutely the wrong approach. Round each calculation to two digits

                           

                           

                          You keep saying that, but you are not providing any reasons. Moreover, you are not providing a solution to situations where the results will NOT balance due to accumulation of rounding errors.

                          • 10. Re: How to Round numbers?
                            datastride

                            Michael,

                             

                             

                             

                            Here is the issue:

                             

                             

                             

                            Each discrete amount involved in the calculation of a particular employee’s paycheck must be rounded to cents. If all earnings amounts are rounded to cents and then totaled to calculate gross pay, the gross pay will, obviously, automatically be rounded to cents. If all deductions (federal withholding, FIC, Medicare) are rounded to cents and then the net pay is calculated by subtracting each of these deductions from the gross pay, the net pay will automatically be rounded to cents.

                             

                             

                             

                            Now here is an important detail: You are not just calculating a single employee’s gross and net pay, you must also deal with the fact that the total for all employees of, say regular pay, needs to be expressed in cents, as this will be posted to a particular general ledger account. Just as will the total of overtime pay for all employees. And as the total of all federal withholding. So you don’t want any of these amounts to be anything other than two decimals. And you need all amounts to cross foot.

                             

                             

                             

                            Think of a spreadsheet with columns labeled:

                             

                             

                             

                            ·         Regular Pay

                             

                            ·         Overtime Pay

                             

                            ·         Gross Pay

                             

                            ·         Federal Withholding

                             

                            ·         FICA

                             

                            ·         Medicare

                             

                            ·         Net Pay

                             

                             

                             

                            Imagine a row for each employee, with the details of that employee’s paycheck running across the spreadsheet.

                             

                             

                             

                            Now image that there are ten employees and thus ten rows of paycheck detail.

                             

                             

                             

                            Finally imagine that each of the 7 columns that contain the paycheck amount fields (Regular Pay, Overtime Pay, Gross Pay, etc.) are summed to produce a row of totals.

                             

                             

                             

                            “Cross footing” means that in the totals row, all of these are true:

                             

                             

                             

                            ·         Total Regular Pay + Total Overtime Pay = Total Gross Pay

                             

                            ·         Total Gross Pay – Total Federal Withholding – Total FICA – Total Medicare = Total Net Pay

                             

                            ·         And finally (and this is the “cross foot” part), the Total Net Pay must also be EXACTLY equal to the sum of all employees’ Net Pay.

                             

                             

                             

                            These won’t be true if some of the discrete amounts for a given employee have more than two decimals.

                             

                             

                             

                            You will not have numbers that cross foot unless each and every discrete amount involved in the calculation of each paycheck is expressed as 2 decimals. (You can round or truncate. It doesn’t matter. It only matters that no number has more than two decimals.)

                             

                             

                             

                            Does this help?

                             

                             

                             

                            Peace, love & brown rice,

                             

                            Morgan Jones

                             

                             

                             

                            FileMaker + Web:  Design, Develop & Deploy

                             

                            Certifications: FileMaker 9, 10 & 11

                             

                            <http://www.onepartharmony.com/> One Part Harmony 

                             

                            Austin, Texas • USA

                             

                            512-422-0611

                            • 11. Re: How to Round numbers?

                              I agree with you, Michael.  They are decisions which should be made by Management at each data-change juncture and then documented (even possibly appearing in footer of report).  Otherwise data can be misinterpreted as rounding is applied in some places and not others; or for some reports which skews the bottom line.  And, if it involves taxes or Payroll  then a professional should be consulted.

                               

                              dchabot, I used to know all the regs but that was 12 years ago.   I have seen payroll and accounting programs created by programmers (none in FM yet, thank goodness) which were illegal because they (the programmer or business owner) made the decision to round an employee's time which, ahem, is illegal.  It used to NOT be!  When did THAT rule change?  Oh, they change ...

                               

                              Here is one example of the rules on withholding for income tax (if you use the rounding percentage method, which it appears you do since there is question on it) and this was two minute search:

                              IRS Form 1036 (revised Dec 2011):

                              At the time this notice was prepared for release, the rate for the employee’s share of social security tax was 4.2% and scheduled to increase to 6.2% for wages paid after February 29, 2012. However, Congress was discussing an extension of the 4.2% employee tax rate for social security beyond February 29, 2012.  Check for updates to this publication.

                               

                              When employers use the Percentage Method Tables for Income Tax Withholding, the tax for the pay period may be rounded to the nearest dollar.  (If rounding is used, it must be used consistently.)  Withheld tax amounts should be rounded to the nearest dollar by dropping amounts under 50 cents and increasing amounts from 50 to 99 cents to the next higher dollar.  For example, $2.30 becomes $2 and $2.50 becomes $3.

                               

                              Lock down the rules at every juncture not only to protect the developer and business but also to protect the employee.  If nothing else, spend an hour researching and then have the Owner pick up the phone and call a payroll service (whose only purpose for existing is to stay up on these rules and keep a business in compliance).   There is absolutely nothing more serious than Payroll and an Employee's money.  Even IRS falls into third place here. 

                               

                              Message was edited by: LaRetta - replaced word rounding with percentage

                              • 12. Re: How to Round numbers?

                                Morgan Jones wrote:

                                 

                                (You can round or truncate. It doesn’t matter. It only matters that no number has more than two decimals.)

                                 

                                It doesn't matter?  Oh, but it does.

                                • 13. Re: How to Round numbers?
                                  datastride

                                  LaRetta,

                                   

                                   

                                   

                                  From an accounting standpoint, whether after calculating FICA as .0565 X Gross Pay, it does not matter whether you truncate or round to the nearest cent, as either will satisfy the IRS. And you ABSOLUTELY MUST NOT express this amount with more than two decimals. It is simply and unequivocally wrong to express it as a number with more than two decimals, as your general accounting and quarterly tax reports could very well be out of balance.

                                   

                                   

                                   

                                  Now if you are determining how to bill a customer for widgets, you may choose to round to the nearest cent – or you may want to always round up. This is a management decision. But in the end, a customer cannot pay you a fraction of a cent. So you will need to round each line item charged to cents so that it can be properly posted to the general ledger.

                                   

                                   

                                   

                                  There is no management decision to be made about the fact that accounting is not (traditionally) performed with fractions of cents.

                                   

                                   

                                   

                                  Peace, love & brown rice,

                                   

                                  Morgan Jones

                                   

                                   

                                   

                                  FileMaker + Web:  Design, Develop & Deploy

                                   

                                  Certifications: FileMaker 9, 10 & 11

                                   

                                  <http://www.onepartharmony.com/> One Part Harmony 

                                   

                                  Austin, Texas • USA

                                   

                                  512-422-0611

                                  • 14. Re: How to Round numbers?
                                    comment

                                    Let me give you a much simpler example: consider only 2 columns, Amount and Tax (7%) and two rows, both containing an amount of $100.90.

                                     

                                    Summing the (rounded) tax column will give $14.12 - but the tax report (and the accompanying check) needs to show $14.13.

                                    1 2 3 Previous Next