Payroll Formula Functions

Related topics

Install/update payroll formulas

Edit Sage-maintained formulas

Edit user-maintained formulas

Troubleshoot payroll tax calculation errors during payroll entry

Perform other payroll tasks

Read the employee setup overview

Read the employee defaults overview

Functions are processes that operate on one or more arguments to produce a result. The function itself is referred to by its name. This name is followed by an opening parenthesis, followed by its argument or a list of arguments separated by commas (,), followed by a closing parenthesis. This entire “function call” is treated as a separate term during the evaluation of an expression.

An example:

A = 5 + MAX_LIMIT (10, 20) * 2

MAX_LIMIT is the name of the function. There are two arguments separated by commas: 10 and 20. The evaluation of the function is done first since it is a term. (In this case, it results in a value of 10.) Therefore, the result of this equation is that A will be set to 30.

Tip: To see examples of how to set up formulas and tables, open the sample company Bellwether Garden Supply. From the File menu, select Payroll Formulas, then User-Maintained. Select a tax that resembles what you are trying to set up and note the formula and (if applicable) the table in the example. These formulas and tables are only examples; you should verify their accuracy by checking the latest calculation rules from the IRS (www.irs.gov) or your state or local tax authority, or by making sure the values entered match company policies (for examples regarding 401K, vacation, etc.).

You must subscribe to a plan that includes payroll to access payroll formulas within Sage 50.

Arguments

There are three different types of arguments that are allowed by different functions. For each function, the proper type of argument must appear in each place in the proper order. The three types of arguments are:

  • Field: Anything that can be considered an identifier EXCEPT for any identifier that begins with EMP_.
  • Expression: Anything that could appear on the right side of an equal sign (=) in an equation. These expressions could even include other functions.
  • Relational Expression: An expression that is either the special identifiers EMP_RETIREMENT or EMP_STATUTORY, or that uses a relational operator to compare one term with another term. In either case, a relational expression results in a value of TRUE or FALSE.

Following is a list of all of the functions, their arguments, what process they perform, and what result is expected. They are listed in alphabetical order.

ABS (Expression) Absolute value. ABS(-2.5)–>2.5

ANNUAL (Field) The result is the value of the field annualized to a full-year value using the employee’s pay frequency. (See the Maintain Employees/Sales Reps window.) For example, if the employee is paid bi-weekly, the field would be multiplied by 26 to annualize it. For the Payroll Tax report, the quarterly value of the field would be multiplied by 4 to annualize it, regardless of the pay frequency of the employee.

ANNUALIZE (Expression) This does the reverse of the PRORATE function. It takes an amount and multiplies it by the pay frequency.

FRACTION (Expression) The fractional part of an expression. FRACTION(3.25)–>0.25

IF (Relational Expression, Expression, Expression) The result is the same as the result of the first expression if the relational expression evaluates to TRUE. The result is the same as the result of the second expression if the relational expression evaluates to FALSE.

INT (Expression) The integer part of the expression. INT(3.25)–>3

MAX_LIMIT (Expression, Expression) The result is the same as the result of the first expression, unless the result of the first expression is greater than the result of the second expression. In that case, the result is the same as the result of the second expression.

MINMAX (Expression, Expression, Expression) The result is the same as the result of the first expression, with two exceptions. If the result of the first expression is less than the result of the second expression, then the result is the same as the result of the second expression. If the result of the first expression is greater than the result of the third expression, then the result is the same as the result of the third expression. Obviously, for this function to work properly, the result of the second expression must be less than the result of the third expression.

PRORATE (Expression) The result is the result of the expression (assumed to be an annual amount) prorated using the employee’s pay frequency (see the Maintain Employees/Sales Reps window). For example, if the employee is paid bi-weekly, then the result is the result of the expression divided by 26. For the Payroll Tax report, the annual value of the expression would be divided by 4 to prorate it to a quarterly value, regardless of the pay frequency of the employee.

QTD (Field) The result is the quarter-to-date value of the field, including any current check. For example, if the field is ADJUSTED_GROSS, then QTD (ADJUSTED_GROSS) is the quarter-to-date adjusted gross paid to that employee.

QTD_LIMIT (Field, Expression) During evaluation, the field and the quarter-to-date value of the field are both obtained. Then, the quarter-to-date value is compared with the expression. If the quarter-to-date value is less than or equal to the expression, the result of the QTD_LIMIT function is the same as the value of the field. If the quarter-to-date value is greater than the expression, then the amount by which it exceeds the expression is subtracted from the value of the field to give the result. If the amount by which it exceeds the expression is greater than the value of the field, the result is zero.

TABLE (Expression) The value of the expression is looked up in the Table (data entered in the Withholding Tax Percentage Table Entry window.) 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 3000, and suppose that a row was found in the Table whose “Gross more than” value was 1000, whose “Withhold” value was 57, and whose “Plus%” value was 3. The result of the TABLE function would be: 57 + ((3000 – 1000) * 3 / 100), which is 57 + (2000 * 3 / 100), which is 57 + (6000/100), which is 57 + 60, which is 117. Tell me more about the TABLE function.

W_ANNUAL (Field) Like the ANNUAL function, the result is the value of the field annualized to a full year value. In this case, however, the number of weeks on the paycheck is used to obtain the factor. For example, if the employee is paid for 2 weeks, the field would be multiplied by 26 to annualize it. For the Payroll Tax report, the number of weeks worked by the employee during the quarter would be used to come up with a factor to multiply by the quarterly value of the field to annualize it. For example, if the employee worked for 10 weeks during the quarter, the quarterly value of the field would be multiplied by 5.2 to annualize it.

W_ANNUALIZE (Expression) This does the reverse of the W_PRORATE function. It takes an amount and multiplies it by the pay frequency.

W_PRORATE (Expression) Like the PRORATE function, the result is the result of the expression (assumed to be an annual amount) prorated back to the period used for the paycheck. In this case, however, the number of weeks on the paycheck is used to obtain the factor. For example, if the employee is paid for 2 weeks, then the result is the result of the expression divided by 26. For the Payroll Tax report, the number of weeks worked by the employee during the quarter would be used to come up with a factor to divide into the result of the expression to prorate it. For example, if the employee worked for 10 weeks during the quarter, the result of the expression would be divided by 5.2 to prorate it.

YTD (Field) The result is the year-to-date value of the field. For example, if the field is ADJUSTED_GROSS, then YTD (ADJUSTED_GROSS) is the year-to-date state gross paid to that employee.

YTD_LIMIT (Field, Expression) During evaluation, the field and the year-to-date value of the field are both obtained. Then, the year-to-date value is compared with the expression. If the year-to-date value is less than or equal to the expression, the result of the YTD_LIMIT function is the same as the value of the field. If the year-to-date value is greater than the expression, then the amount by which it exceeds the expression is subtracted from the value of the field to give the result. If the amount by which it exceeds the expression is greater than the value of the field, the result is zero (0).