Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Location
    DFW Texas
    Posts
    1

    Calculation Help

    Help, help, help!!!

    I am trying to put together a payroll program in Microsoft Access. I have come upon a problem that absolutely evades me. In calculating the Tier1 Railroad Retirement Tax (.062), there comes a point that tax is no longer withheld. That maximum withholding amount is $5,840.40. I have been able to stop the calculations after the maximum, but the problem is the amount between the last pay period and the time the maximum is achieved. For example:



    YTD Previous Period Tier 1 = $5,750.00 Gross pay this period = $2,350.00 (2,350.00 X .062 = 145.70) $5,750.00 + 145.70 = $5,895.70 ($55.30 over the maximum)



    By this calculation, too much tax is withheld this pay period. The maximum to be withheld this period can only be $90.40



    What is the calculation that would take this “in-between” into account?

  2. #2
    DMoody007 is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    4
    If it were me, I would tackle as follows:

    myMax = 5840.40 (load as constant or from table if variable based on some consideration)

    TotalLastPay = 5750 (total paid in since last pay period, pulled from record)

    CurrrPayTax = 2350 * .062 = 145.70 '****Calc from current pay period

    If TotalLastPay = myMax then
    myDeduction = 0 '*****Max already Reached, no further deductions
    else
    '****Assumption, no TotalLastPay is > myMax, otherwise need more traps to catch exceptions
    Select Case TotalLastPay + CurrPayTax
    case <myMax
    myDeduction = CurrPayTax '****Less means you can take total as calculated
    TotalCurrPay = TotalLastPay + CurrPayTax '****New Total Paid for next month
    case =myMax
    myDeduction = CurrPayTax
    TotalCurrPay = TotalLastPay + CurrPayTax '****New Total Paid for next month
    case >myMax
    myDeduction = myMax - TotalLastPay '****Should be 90.40 per your example
    TotalCurrPay = myMax
    end select
    end if

    Hope this helps.

  3. #3
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Here you go:

    Code:
    CurTax = CurPay*.062
    Overage = YTDTax+CurTax-5840.40
    If Overage > 0 then CurTax = CurTax-Overage
    Using your values:
    Code:
    CurTax = 2350.00*.062 = 145.70
    Overage = 5750.00 + 145.70 - 5840.40 = 55.30
    If 55.30 > 0 then CurTax = 145.70 - 55.30 = 90.40

    Cheers,

    Jeff

  4. #4
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Did I really just respond to a 2005 post? Not my night.

  5. #5
    DMoody007 is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Posts
    4
    I realized that last night but didn't want to admit it.
    Not alone.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculation is off by a few cents...
    By Jule in forum Reports
    Replies: 3
    Last Post: 05-01-2006, 01:06 PM
  2. Calculation
    By thestclair in forum Reports
    Replies: 1
    Last Post: 04-29-2006, 11:03 AM
  3. Report Calculation
    By thestclair in forum Reports
    Replies: 2
    Last Post: 03-28-2006, 12:23 PM
  4. Date Calculation within same fields
    By mslieder in forum Queries
    Replies: 3
    Last Post: 01-26-2006, 10:08 AM
  5. Subform vs main form calculation
    By fadone in forum Forms
    Replies: 17
    Last Post: 12-21-2005, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums