Skip to content
logo Knowledgebase

Prevent 401K and Roth from going past the limit

Created on  | Last modified on 

Summary

How do I create a formula in Sage 300 CRE to calculate combined 401K and Roth contributions up to a limit?

Description

Overview

You have employees who participate in both a 401K and Roth deduction. You don’t want the combination of both deductions to exceed the annual limit.

The included formulas allow the 401K limit to include the 401K ROTH limit in its calculations. When the employee's combined 401K amounts go over the limit, the 401K deduction calculates half the difference to the limit. The 401K ROTH calculates the remaining amount.

Use the attached report to copy the formulas to public formulas. Update the formulas as needed to fit your specific setup.

 NOTE: Change the Over 50 and Under 50 Deduction limits. The formulas don’t have the current year limits. 

Important support information

Sage Support can't provide tax advice or assist with business decisions for your company. Support also can't assist with creating custom formulas or modifying reports. We provided the formulas and report below as examples and guidelines. 

  • If you need assistance with tax-related questions, contact your tax professional.
  • If you need assistance with formulas or report modifications, contact your Sage Consultant or Sage Expert Services (SES).
  • See the IRS website for updated information about 401K/Roth limits.

Resolution

CAUTION: Sage support can't assist with third-party products, hardware, report customizations, or state and federal tax questions. Refer to our Scope of Support for more info. Contact your Sage business partner, network administrator, or accountant for assistance.

Download the report and copy the formulas

Use the following steps to set up Payroll for combined 401K and 401K ROTH amounts.

Considerations

  • When you open the 401kRothFormulas report, several warning messages will appear, and the report will appear blank. This is normal. Continue with the steps after you click OK on each warning message.
  • When you copy the formulas, you must type the formula name exactly the same as the original formula.
  • If you copy a formula that references other formulas, copy the referenced formulas to the same location. If you don’t, you'll receive a missing formula error when you open the copied formula.

Download and copy

Attachment: 401kRothFormulas.rpt

  1. Download and save the attached report.
  2. Copy the formulas from the report to your formulas.
    1. Launch Report Designer.
    2. Go to File and select Open Design.
    3. Select the downloaded report design and click Open.
    4. From the Tools menu, select Formulas.
    5. Select the formula to copy. If necessary, click Index to view different formula types.
    6. Click Copy.
    7. Enter a name for the new copied formula.
    8. Select the formula type for the copied formula:
      • Public Formulas if you need to use this formula on other reports.
      • Design Formulas if you need to use this formula on the report you currently have open.
    9. Click OK and then Close.

Set up deductions

  1. Go to Payroll, Setup, Deductions.
  2. Set up a deduction called 401K.
  3. Click Save and Close.
  4. Go to Payroll, Setup, Deductions,
  5. Set up a deduction called 401KROTH.
  6. Click Save and Close.

Add the deductions to employees

  1. Go to Payroll, Setup, Employees.
  2. For each applicable employee:
    1. Click the Deducts button.
    2. Add the 401K and 401KROTH deductions, if needed.
    3. Select Formula as the Calc Method.
    4. Select the 401K and 401K_ROTH formulas.
    5. Save changes.

Formula examples

In the Formula examples below, select fields from records where appropriate instead of typing the field and record names. For example, in "LOOKUP (YTD Deduction [PR Employee Deduct]..." the YTD Deduction is a field you select from the PR Employee Deduct record. For assistance with creating formulas, refer to the "How to create a formula" attachment below or read Help topics about formulas.

401K/Roth formulas

Formula name

Formula

Notes

401K

IF((YTD_401K [Public Formula] + YTD_401KR [Public Formula] + 401K_CALC [Public Formula] + 401KR_CALC [Public Formula]) <= 401K_LIMIT [Public Formula], 401K_CALC [Public Formula], MAX(ROUND(((401K_LIMIT [Public Formula] - (YTD_401K [Public Formula] + YTD_401KR [Public Formula])))/2), 2), 0))

401K: This formula calculates the 401K amount using the nested formulas listed below. If the YTD amount of the 401K and 401K ROTH is below the limit, it uses the 401K_CALC formula to calculate the normal 401K deduction. When the amount goes over the limit, it either doesn't calculate, or it calculates half the difference of the limit. The 401K Roth calculates the other half, so it withholds about the same amount for each.

401K_ROTH

IF((YTD_401K [Public Formula] + YTD_401KR [Public Formula] + 401K_Check [Public Formula] + 401KR_CALC [Public Formula]) <= 401K_LIMIT [Public Formula], 401KR_CALC [Public Formula], MAX((

401K_LIMIT [Public Formula] - (YTD_401K [Public Formula] + YTD_401KR [Public Formula] )), 0))

401K Roth: If the combined 401K and 401K ROTH totals don't meet the limit, it uses the 401KR_CALC formula to calculate the normal deduction amount. When the totals go over the limit, it either doesn't calculate, or it calculates the remainder of the amount up to the limit. You must calculate the 401K Deduction before this deduction for it to function correctly.

 

Lookups for the YTD Amounts

Formula name

Formula

Notes

YTD_401K

LOOKUP(YTD Deduction [PR Employee Deduct], Employee [PR Check], "401K")

Provides the YTD 401K deduction amount from the employee setup.

YTD_401KR

LOOKUP(YTD Deduction [PR Employee Deduct], Employee [PR Check], "401KROTH")

Provides the YTD 401K ROTH deduction amount from the employee setup.

 

401K and 401K Roth Formula Calculations

Formula name

Formula

Notes

401KR_CALC

(LOOKUP(Amount [PR Employee Deduct], Employee [PR Check], "401KROTH") / 100) * Gross Pay [PR Check]

Takes the employee percentage from the employee's 401K ROTH deduction and multiplies it by the check gross. This calculates the amount of 401K ROTH for the employee's check.

401K_CALC

(LOOKUP(Amount [PR Employee Deduct], Employee [PR Check], "401K") / 100) * Gross Pay [PR Check]

Takes the employee percentage from the employee's 401K deduction and multiplies it by the check gross. This calculates the amount of 401K for the employee's check.

 

401K Check Amount Lookup

Formula name

Formula

Notes

401K_Check

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

Provides the amount of 401K that calculates on the employee's check. It uses this in the calculation to determine the employee's YTD amount for the check.

 

401K Limit

Formula name

Formula

Notes

401K_LIMIT

IF(Current Year [PR Payroll Controls] < NEWDATE(Birth Date [PR Employee], 0, 0, 50)OR Birth Date [PR Employee] = 0,401K_UNDER_50_LIMIT [Public Formula],401K_OVER_50_LIMIT [Public Formula])

Compares the employee's birth date to the current year in payroll. If the employee turns 50 during the year, it will calculate the limit using the value in 401K_OVER_50_LIMIT formula. When the employee is less than 50, it will calculate the limit using the value in the 401K_UNDER_50_LIMIT formula. This also occurs if the birth date on the employee record is blank.

Select Add to find PR Payroll Controls.

401K_OVER_50_LIMIT

24500
* As of 2018

When the limit changes for employees over age 50, this allows you to modify the amount without changing any other formulas.

401K_UNDER_50_LIMIT

18500
* As of 2018

When the limit changes for employees under age 50, this allows you to modify the amount without changing any other formulas.

 

Possible adjustments

The formula examples listed above assume the 401K and 401K_ROTH calculates as a percentage of the employee's gross earnings.

If you have a different setup, modify the 401KR_CALC and 401K_CALC formulas to calculate according to the employee's specific 401K and 401K ROTH setup.

Use a field on the employee setup to designate the calculation method for the employee.

For example, enter a value of "Flat" or "Gross" in the MISC 1 field on the Entry Info tab. Add an IF statement in the 401KR_CALC and 401K_CALC formulas that uses different calculations based on the MISC 1 value.

Formula name

Formula

401K_CALC

IF(LOOKUP(Misc 1 [PR Employee], Employee [PR Check]) = "Gross", (LOOKUP(Amount [PR Employee Deduct], Employee [PR Check], "401K") / 100) * Gross Pay [PR Check], IF(LOOKUP(Misc 1 [PR Employee], Employee [PR Check] ) = "Flat", LOOKUP(Amount [PR Employee Deduct], Employee [PR Check], "401K")))

For employees with different calculation methods for the deductions, use a separate field (for example MISC 2) to designate the calculation in the 401KR_CALC formula.

If you have different calculation methods for employees, contact your Sage Consultant to determine the best way to implement these formulas.

Related Solutions