XLS Paycheck Calculator - Tax Rate - Semi-monthly Version

MED%=IF(OAC="MED",MD_,IF(OAC="SS",MD_,0))12/12/18 JL0.0620.01450.01Gross Pay6000(Gross Pay) MED=ROUND((GROSS+Flex_Cash-Total_Flex)*MED_,2)Pay FrequencyM(M, S, B for Monthly, Semi-Monthly, and Bi-Weekly)SS%=IF(OR(OR(OR(OAC="NO",CAT="SAF",CAT="SAF2",CAT="PAT2",CAT="POF2"),CAT="PAT"),CAT="POF"),0,IF(OAC="SS",OA_,0))Retirement Code2M (Please call your HR for Ret. Code.)SS=ROUND((GROSS+Flex_Cash-Total_Flex)*OASDI_,2)1st Half (1) or 2nd Half (2)If Monthly or Bi-Weekly, leave blank. If Semi Monthly, see instructions below.EPMC DED=VLOOKUP(CAT,RETID_TABLE,3)=IF(B4="B",EPMCD*12/26,"")=IF(AND(B4="B",K6GROSS,TIER2),0,IF(TIER=1,(GROSS-EPMCD)*EPMC_,IF(AND(B4="S",TIER=2),GROSS*EPMC_,)))Federal Marital StatusS(M, S, H)EPMC=IF(B4="M",L8,IF(B4="S",L7,IF(B4="B",L6)))=IF(EPMCD>GROSS,0,(GROSS-EPMCD)*EPMC_)Number of Exemptions Fed1(1-99)SSTXG=GROSS+Flex_Cash-Total_FlexState Marital StatusM(M, S, H) MEDTXG=GROSS+Flex_Cash-Total_FlexNumber of Exemptions State1(1-99) SDI1=IF(SDI="Yes",SDIGRS*SDI_RATE,0)Additional Exemptions State0(1-99) SDIGRS=GROSS+Flex_Cash-Total_FlexSDINO(YES, NO)TXGRS1=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA1)Flex Cash Option0(Shown as FLEX CASH or COBEN CASH)TXGRS2=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA2)Total Other Flex Deductions100See instructions belowTXGRS3=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA3)Total A/R Deductions0(Account Receivable deduction (Shown as ACCT RCVBL)TXGRS4=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA4)Total-Voluntary Deductions80See instructions belowTXGRS5=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA5)Deferred Comp/TSA 0100200300500625TXGRS6=(GROSS-Total_A_R_Deductions-EPMC+Flex_Cash-Total_Flex-DCA6)NET PAY=(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA1-FTAX1-STAX1-B16-SDI1)=IF(DCA2>0,(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA2-FTAX2-STAX2-B16-SDI1),"")=IF(DCA3>0,(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA3-FTAX3-STAX3-B16-SDI1),"")=IF(DCA4>0,(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA4-FTAX4-STAX4-B16-SDI1),"")=IF(DCA5>0,(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA5-FTAX5-STAX5-B16-SDI1),"")=IF(DCA6>0,(GROSS-EPMC-OASDI-MED+Flex_Cash-Total_Flex-VOLDEDS-DCA6-FTAX6-STAX6-B16-SDI1),"")Difference in Net=IF(DCA2>0,($B$19-C19),"")=IF(DCA3>0,($B$19-D19),"")=IF(DCA4>0,($B$19-E19),"")=IF(DCA5>0,($B$19-F19),"")=IF(DCA6>0,($B$19-G19),"") FEDERAL TAXFederal Tax Withheld=(FTAX1)=IF(DCA2>0,FTAX2,"")=IF(DCA3>0,FTAX3,"")=IF(DCA4>0,FTAX4,"")=IF(DCA5>0,FTAX5,"")=IF(DCA6>0,FTAX6,"")PAYFACT=IF(B4="S",24,IF(B4="B",26,12))State Tax Withheld=(STAX1)=IF(DCA2>0,STAX2,"")=IF(DCA3>0,STAX3,"")=IF(DCA4>0,STAX4,"")=IF(DCA5>0,STAX5,"")=IF(DCA6>0,STAX6,"")FED EXM=(FEDE*FEDEXMPT)Retirement Withheld=EPMC=EPMC=EPMC=EPMC=EPMC=EPMCANNUALIZED GROSSSocial Security Withheld=OASDI=OASDI=OASDI=OASDI=OASDI=OASDIFAN1=(PAYFACT*TG1)Medicare Withheld=MED=MED=MED=MED=MED=MEDFAN2=(PAYFACT*TG2)SDI Withheld=SDI1=SDI1=SDI1=SDI1=SDI1=SDI1FAN3=(PAYFACT*TG3)Federal Taxable Gross=J13=J14=J15=J16=J17=J18FAN4=(PAYFACT*TG4)State Taxable Gross=J13=J14=J15=J16=J17=J18FAN5=(PAYFACT*TG5)ACEE RateExclusion AmountSocial Security Taxable Gross=$J$9=$J$9=$J$9=$J$9=$J$9=$J$9FAN6=(PAYFACT*TG6)000.06317Medicare Taxable Gross=$J$10=$J$10=$J$10=$J$10=$J$10=$J$10010.03750 TAXABLE GROSS0200GENERAL INFORMATIONFTG1=(FAN1-FEDEXM)030.03750FTG2=(J25-FEDEXM)040.03750This spreadsheet can be used to calculate net pay and withholding amounts for employees paid on a monthly,FTG3=(J26-FEDEXM)050.095513semi-monthly or bi-weekly basis. By changing the marital status or number of exemptions, you can project FTG4=(J27-FEDEXM)060.1317your net and withholding amounts based on these changes. The different rows of the spreadsheet show theFTG5=(J28-FEDEXM)070.125513various nets and withholding amounts based on the different deferred compensation or TSA amounts at theFTG6=(J29-FEDEXM)080.05513head of the column ($0, $100.00, $200.00, $300.00, $500.00, $625.00). You can use these amounts to compare090.08513differences if you are considering making changes to your deferred compensation or TSA withholding amount. BASE AMOUNT100.06317FBSA1=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(FTG1,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(FTG1,FTXTBLM,1),0))110.01317 FBSA2=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J33,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(J33,FTXTBLM,1),0))120.06317Specific entry instructions: FBSA3=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J34,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(J34,FTXTBLM,1),0))130.03750FBAS4=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J35,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(J35,FTXTBLM,1),0))1400Gross Pay-Enter your gross monthly pay. This amount is shown on your earnings statement/direct deposit adviceFBAS5=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J36,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(J36,FTXTBLM,1),0))150.06238as REGULAR. Do not add in FLEX CASH. FBAS6=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J37,FTXTBLSH,1),IF(FEDM="M",VLOOKUP(J37,FTXTBLM,1),0))160.06238170.06317Retirement Code - Please call your HR department for the Retirement CodeMARGINAL TAX RATE180.06317FMTR1=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(FTG1,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(FTG1,FTXTBLM,2),0))190.113171st Half (1) or 2nd Half (2)-If calculating pay for the 1st half for a semi-monthly employee or if calculating pay for the 2nd half FMTR2=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J33,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(J33,FTXTBLM,2),0))1A0.07317when an employee will not receive pay in the 1st half, enter 1 in B6 to allow the retirement exclusion (e.g. - $513) to apply appropriately.FMTR3=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J34,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(J34,FTXTBLM,2),0))1B0.11317If the employee received 1st half pay, you must enter 2 in B6 so that the retirement exclusion will not apply a second time. FMTR4=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J35,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(J35,FTXTBLM,2),0))1C0.11317If the full amount of the retirement exclusion was not applied to the first half payment, you should notFMTR5=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J36,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(J36,FTXTBLM,2),0))1D0.11317use the calculator to appropriately calculate the second half payment (e.g. first half gross = $400 and retirement FMTR6=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J37,FTXTBLSH,2),IF(FEDM="M",VLOOKUP(J37,FTXTBLM,2),0))1E0.11317exclusion = $513,  no retirement will be withheld from the first half payment). In this case, the remainder of the retirement 1F0.11317exclusion ($113) will apply to the second half payment and at this time the calculator is not programed for this calculation.  Manual BASE TAX AMOUNT1G0.11317calculations will be required, see PPM Section H for federal and state tax calculation instructions.FBST1=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(FTG1,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(FTG1,FTXTBLM,3),0))1H0.11317FBST2=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J33,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(J33,FTXTBLM,3),0))1J0.11317FBST3=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J34,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(J34,FTXTBLM,3),0))1K0.11317Social Security/Medicare-Retirement Code driven (SS = Both, Med = Medicare Only, NO = Neither)FBST4=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J35,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(J35,FTXTBLM,3),0))1L0.11317FBST5=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J36,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(J36,FTXTBLM,3),0))1M0.090Federal Marital Status-Enter any status (M=married, S=single, H=head of household) you want to check rates on.FBST6=IF(OR(FEDM="S",FEDM="H"),VLOOKUP(J37,FTXTBLSH,3),IF(FEDM="M",VLOOKUP(J37,FTXTBLM,3),0))1N0.063171P0.11317Number of Exemptions Fed-Enter the number of exemptions you want to compute federal tax with. OVER BASE AMOUNT1Q0.105317FOVR1=(FTG1-FBSA1)1R0.11317State Marital Status-Enter any status you want to check rates on. (NOTE: This calculates only California state tax.)FOVR2=(J33-J41)1S0.11317FOVR3=(J34-J42)1T0.11317Number of Exemptions State-Enter the number of exemptions you want to compute state tax with. (California only) FOVR4=(J35-J43)1U0.06317FOVR5=(J36-J44)1V0.11317Additional Exemptions State-Enter the number of additional exemptions you want to compute state tax with. (Calif. only)FOVR6=(J37-J45)1W0.113171X0.11317SDI-Enter YES if you are covered by the SDI program, enter NO if you are not. ANNUALIZED TAX1Y0.135317FTA1=(FBST1+ROUND(FOVR1*FMTR1,5))1Z0.105317Flex Cash Option-Enter the amount of flex cash option that you receive. This shows as FLEX CASH or FTA2=(J57+ROUND(J65*FMTR2,5))200.1317COBEN CASH on your earnings statement/direct deposit advice.FTA3=(J58+ROUND(J66*FMTR3,5))2100FTA4=(J59+ROUND(J67*FMTR4,5))2200Total Other Flex Deductions-Enter the sum of all flex deductions (except FLEX CASH) that you have deducted.FTA5=(J60+ROUND(J68*FMTR5,5))230.06317These are all * deductions except *TSA, *DC-457, *457, *401K, *403B, *PERSREDPST or *STRS REDST. Other Post-Employment FTA6=(J61+ROUND(J69*FMTR6,5))240.08513Benefits (OPEB), which can be identified by *CERBT if it appears on your warrant, should be included in this entry.250.125513 PAY PERIOD TAX270.08513Total A/R Deductions-Enter the sum of all account receivable deductions that you wish to use in thisFTAX1=ROUND(FTA1/PAYFACT,2)2A0.11317calculation. These show as ACCT RCVBL on your earnings statement/direct deposit advice.FTAX2=ROUND(J73/PAYFACT,2)2B0.11317(NOTE: Many account receivable deductions are one time and not ongoing. In this case you may wish to exclude theseFTAX3=ROUND(J74/PAYFACT,2)2C0.1513from the calculations.)FTAX4=ROUND(J75/PAYFACT,2)2D0.1513FTAX5=ROUND(J76/PAYFACT,2)2E0.1513Total Voluntary Deductions-Enter the sum of all other deductions you have that are not covered in the categories above.FTAX6=ROUND(J77/PAYFACT,2)2F0.1513These may include things such as credit union deductions, union dues, administrative fees etc..(excluding all *)2G0.1513 STATE TAXES2H0.1513Deferred Comp/TSA**-Use this field to enter any Deferred Compensation/TSA amount that you want to see calculationsLIE=IF(STM="S",LIES,IF(AND(STM="M",STE=2),LIEM2,IF(STM="H",LIEH,99999))))2I0.08513on. Each column represents the withholding or amounts associated with the corresponding Deferred Compensation/TSAADDALLOW=(STA*SADDALL)2J0.1513amount at the head of the column. (NOTE: Also use this field to enter PERS/STRS Retirement buy back shown as SDED=IF(STM="S",SDS,IF(AND(STM="M",STE=2),SDM2,IF(STM="H",SDH,0))))2K0.09317*PERSREDPST or *STRS REDST on your earnings statement/direct deposit advice.TXCRB=IF(AND(B10="S",B11=0),T60,IF(AND(B10="S",B11=1),U60,IF(AND(B10="S",B11>=2),V60,IF(AND(B10="M",B11=0),T61,IF(OR(AND(B10="M",B10="H"),B11=1),U61,IF(OR(AND(B10="M",B10="H"),B11>=2),V61,0))))))2L0.1513TXCROV2=IF(STE ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download