Skip to content
logo Knowledgebase

How do I set up a 401K matching fringe formula?

Created on  | Last modified on 

Summary

Steps to create a fringe with a formula for 401K matching in Sage 300 CRE Payroll. I need to setup the 401K with a two tiered match. What formulas can I use to calculate 401K Match?

Description

Create a fringe with a formula to calculate 3 percent of gross pay and 50 percent of the next 2 percent of gross pay.

Cause

Resolution

Partial Match up to a limit:

To partially match a 401k Deduction up to a specific maximum. For example, matching 50% of the employees' deduction but the match is not to exceed 3% of the gross pay.

Min (Lookup (Amount [PR Check Deduct], Employee [PR Check], Period End Date [PR Check], Check Sequence [PR Check], "401K")*.5, Gross Pay [PR Check] * .03)

Full match:

Lookup (Amount [PR Check Deduct], Employee [PR Check], Period End Date [PR Check], Check Sequence [PR Check], "401K")

To fully match a 401k Deduction up to a specific percent of Gross:
For example, matching 100% of the employees' deduction but the match is not to exceed 3% of the gross pay.

Min (Lookup (Amount [PR Check Deduct], Employee [PR Check], Period End Date [PR Check], Check Sequence [PR Check], "401K"), Gross Pay [PR Check] * .03)

Tiered match:

To fully match a 401k Deduction up to a specific maximum, then partially match up to the next maximum.

This example assumes the following:

The 401K fringe ID is 401K.
Employees are eligible after 1 year of employment.
The 401K Match uses the minimum between 100 percent of the first 3 percent of gross pay, or the employee 401K deduction amount. In addition, it uses the minimum between 50 percent of the next 2 percent of gross pay, or the remaining employee 401K deduction amount.

  1. In Payroll, from the Tools menu, select Formulas and create the formulas below:

    Formula Name

    Formula

    2ofGrossPay

    Gross Pay [PR Check] * 0.02

    3ofGrossPay

    Gross Pay [PR Check] * 0.03

    401K Chk Ded

    LOOKUP (Amount [PR Check Deduct], Employee [PR Check], Period End Date [PR Check], Check Sequence [PR Check], "401K")

    Note: If multiple deductions apply to the match, add additional LOOKUPS to the formula:

    +LOOKUP(AMount [PR Check Deduct], Employee [PR Check], Period End Date [PR Check],Check Sequence [PR Check], "401KROTH")

    401Kmatch1

    MIN (3ofGrossPay [Public Formula], 401K Chk Ded [Public Formula])

    401Kmatch2

    MIN (0.5 * 2ofGrossPay [Public Formula], 0.5 * (401K Chk Ded [Public Formula] - 401Kmatch1 [Public Formula]))

    Eligible

    IF (Period End Date [PR Check] > = NEWDATE(Hire Date [PR Employee], , ,1), Yes)

     

     

      Note:  The "1" indicates 1 year. 

    401K Match

    IF (Eligible [Public Formula], 401Kmatch1 [Public Formula] + 401Kmatch2 [Public Formula])
  2. From the Setup menu, select Fringes.
  3. Create the fringe, 401K, and in the Calc method box, select Formula.
  4. In the Formula box, select 401K Match.
  5. Click Save and then click Close.

NOTE:  The structure of these formulas is very important for a correct calculation to appear.  The Brackets [ ] indicate the record the field is retrieved from.  The parens ( ) are very specific to the structure and calculation of the formulas.  On the 401Kmatch2 formula you must use Parens in the proper order.  Make sure you have parens around the portion of the formula specific to the following instruction on the formula : (401K Chk Ded [Public Formula]-401Kmatch1[Public Formula])

Related Solutions