Forum Discussion
Need help with formula
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.
SnowMan55 thanks so much for the formula. How do I apply it by employee based on their earnings with maximum of $200K?
Antonette
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.
- Thanks again.
Antonette