Example: A Tax Formula That Uses a Table

This topic walks you through an example of creating a federal tax formula in Sage 50 that uses the Table function. You can enter table data (such as Federal Income Tax wage/rate tables) using the Table button on the User-Maintained Payroll Formula window. Then, you can use the Table data you've entered in formulas that calculate tax amounts.

While the rates and syntax used may be accurate at the time this was written, they will change. If you choose to create your own tax formulas, then you must research www.irs.gov or other sites/publications to make sure you have up-to-date rates and limits. This example is intended to show: a) how to use some of the functions and formulas in Sage 50's payroll area; but more importantly, b) a method to use for creating tax formulas.

(missing or bad snippet)

In this example, we are creating a Federal Income Tax (FIT) for the 2003 tax year for Single taxpayers. The exact functions and syntax used here will not work in all cases for other types of taxes (or even for FIT Single in subsequent years).

The first step is to find the current instructions and rates for the tax you are creating. As an employer, you should receive a copy of IRS Publication 15-T containing all the federal withholding tables. If you do not receive this publication, it is available in pdf format on www.irs.gov.
Read the section about the tax you're creating.
Enter the table rates from Publication 15-T. They should look something like this when you're done.

Now we have to create the formula that will use this table data to calculate the tax.

Tip: To set up these variables, you have to know a little about the commands and syntax Sage 50 uses. Look up formulas, tax table formulas in the Sage 50 Help index. You can also get an idea of how to set up your formulas by opening one of the sample companies and looking at the formulas used there (under File, Payroll Formulas, User Maintained). Note that these, too, are just examples or starting places; it's up to you to verify their accuracy. But they should give you some ideas.

We know that we want to use the information we entered in the table and the information we enter throughout the payroll year for the employee to compute the FIT amount. We also know we'll have to factor in federal allowances and any additional withholding. (These amounts are stored in the Maintain Employee record, on the Withholding Info tab.)

As stated in the Tip above, the best place to start would be to view a formula used in the sample company Bellwether. We'll look at the formula there for FIT Single (at the time this was written) and explain each segment of the formula.

ANSWER = -PRORATE (TABLE (ANNUAL (ADJUSTED_GROSS) - (EMP_FEDERAL_ALLOWANCES * 2450))) - EMP_FEDERAL_EXTRA

First, the minus sign at the beginning of the formula ensures the tax will be subtracted from gross pay. Make sure to mark the Type as a deduction.
The PRORATE function divides the final amount by the pay frequency you've entered for the employee on the Pay Info tab of Maintain Employees/Sales Reps to give the amount to be deducted for each pay period. For example, if the employee is paid bi-weekly, then the result of the expression will be divided by 26.
The TABLE function tells the program to look at the table where we've entered data. This is done by searching the list of values in the “Gross more than” column, starting at the bottom and working up, until a value is found that is not greater than the value of the expression. If no such value is found, the result is zero (0). If a value is found, the result is the amount found on the same row in the “Withhold” column plus the amount that the value of the expression exceeds the “Gross more than” value multiplied by the amount found on the same row in the “Plus%” column divided by 100. For example, suppose the value of the expression was 50,000. In our table, that would fit in the second row (Gross more than 26,900 but less than 57,450). The result of the Table function would be:

Withhold Value+((result-Gross more than)*Plus%/100)

3,637.50+((50,000-26,900)*28/100)

3,637.50+(23,100*.28)

3,637.50+6,468

10,105.50 = the TABLE value

The ANNUAL function is the opposite of the PRORATE function being used on the entire expression. ANNUAL is applied to the employee's ADJUSTED_GROSS to annualize it to a full-year value. For example, if the employee is paid bi-weekly, then the amount would be multiplied by 26.
The ADJUSTED_GROSS is the result of the gross and the values in all deduction fields added together.
The EMP_FEDERAL_ALLOWANCES and EMP_FEDERAL_EXTRA values are stored in the Maintain Employee record, on the Withholding Info tab.
"2450" is the amount allowed for each federal allowance. This amount may change from year to year; currently, you would find it in I.R.S. Publication 15-T.

What do you want to do next?

Learn more about payroll formulas

See an example of a formula that doesn't use a table