Forum Discussion

Antonette_Hunter's avatar
Antonette_Hunter
Copper Contributor
Jun 13, 2023

Need help with formula

I need help writing a formula with the following attributes. To summarize, the first 2% of an employee's contribution gets a 100% employer match, (2%:2%). The next contributions up to 7% gets 50% employer match. Match maximizes at $9,000 when employee's earnings reach a maximum of $200K or 4.5%. 

EE ContributionER Match
1.00%1.00%
1.50%1.50%
2.00%2.00%
2.50%2.25%
3.00%2.50%
3.50%2.75%
4.00%3.00%
4.50%3.25%
5.00%3.50%
5.50%3.75%
6.00%4.00%
6.50%4.25%
7.00%4.50%
7.50%4.50%
8.00%4.50%
8.50%4.50%
9.00%4.50%
9.50%4.50%
  
Max earnings $            200,000
Max ER match $                9,000

 

excel
Formulas and Functions
  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Jun 21, 2023

    Antonette_Hunter See the attached workbook.  If you vary the Employee Earnings in cell C2, you can see how high earnings can impact the Employer Contribution Percentage.  The formulas in column E do not consider Employee Earnings; the formulas in column F do.  Here's the formula in F3:

    =LET( tier1, MIN(A3,2%),
    tier2, MAX(MIN(A3,7%)-2%,0)*0.5,
    tier3, MAX(A3-9.5%,0)*0,
    MIN(tier1+tier2+tier3, 9000/$C$2)
)

    Of course, the calculation of tier3 is for demonstration only; the multiplication by 0 cancels its contribution.

     

     

    • Antonette_Hunter's avatar
      Antonette_Hunter
      Copper Contributor
      Jun 21, 2023

      SnowMan55 thanks so much for the formula. How do I apply it by employee based on their earnings with maximum of $200K?

       

      Antonette

      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Jun 22, 2023

        Antonette_Hunter That formula is for the effective percentage (for the entire year), which you may use as-is, replacing the A3 with the cell containing the employee-selected contribution percent, and replacing $C$2 with the cell containing the employee annual earnings.

         

        If you instead need to compute the potential total employer contribution amount, you can use either of these formulas (replacing references as noted above):

        =LET( tier1, MIN(A3,2%),
    tier2, MAX(MIN(A3,7%)-2%,0)*0.5,
    tier3, MAX(A3-9.5%,0)*0,
    MIN(tier1+tier2+tier3, 9000/$C$2) * $C$2
)
…or to avoid unnecessary computation for tier3:
=LET( tier1, MIN(A3,2%),
    tier2, MAX(MIN(A3,7%)-2%,0)*0.5,
    MIN(tier1+tier2, 9000/$C$2) * $C$2
)

         

        But if you need the employer contribution amount for any particular payroll, that value is going to depend on the number of payroll periods per year, year-to-date employee earnings, year-to-date employer contributions, whether employees can change their contribution percentage during the year, and possibly other considerations.  I.e., the computational rules could be complicated, and I do not have all that information.

         

Resources