401kspreadsheets
New Member
- Joined
- Mar 28, 2019
- Messages
- 4
I need help with creating the correct formula for the Employer Matching column (Column G). The employer matching formula is the lesser of 50% up to 6% of Gross Wages OR a maximum of $8,400 (which is 3% of $280,000). Once Gross Wages reach $280,000, the employer match will no longer accrue. Also, the only way the Column G should contain a value is if the Total Contributions in Column E are greater than 0. Column E's cumulative maximum is $19,000. The maximum cumulative amount in Column G should be $8,400.
The logic in Column G should be: If the total in Column B is less than or equal to $280,000, and the applicable cell in Column E is greater than 0, then calculate the employer matching up to a maxiumum total of $8,400.
The following formulas already exist
In cell E3: =IF(B3*(C3+D3)+SUM($E$2:E2)<=19000,B3*(C3+D3),19000-SUM($E$2:E2))
In cell F2: =IF(SUM(C2:D2)>=0.06,0.03,SUM(C2:D2)/2)
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
The logic in Column G should be: If the total in Column B is less than or equal to $280,000, and the applicable cell in Column E is greater than 0, then calculate the employer matching up to a maxiumum total of $8,400.
The following formulas already exist
In cell E3: =IF(B3*(C3+D3)+SUM($E$2:E2)<=19000,B3*(C3+D3),19000-SUM($E$2:E2))
In cell F2: =IF(SUM(C2:D2)>=0.06,0.03,SUM(C2:D2)/2)
A | B | C | D | E | F | G | |
1 | Pay Date | Gross Wages | 401K Pre-Tax % Election | 401K Roth % Election | 401K Total Contribution ($) | Match Calculation (%) | Employer Matching ($) |
2 | 1/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
3 | 2/1/2019 | $50,000.00 | 5% | 1% | $3,000.00 | 3.0% | $1,500.00 |
4 | 3/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
5 | 4/1/2019 | $25,000.00 | 4% | 1% | $1,250.00 | 2.5% | $625.00 |
6 | 5/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
7 | 6/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
8 | 7/1/2019 | $50,000.00 | 5% | 1% | $3,000.00 | 3.0% | $1,500.00 |
9 | 8/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
10 | 9/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $750.00 |
11 | 10/1/2019 | $25,000.00 | 5% | 1% | $1,500.00 | 3.0% | $275.00 |
12 | 11/1/2019 | $25,000.00 | 5% | 1% | $1,250.00 | 3.0% | $0.00 |
13 | 12/1/2019 | $25,000.00 | 5% | 1% | $0.00 | 3.0% | $0.00 |
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>