Need Help Creating 401k Match Formula

The formula is not too hard but we need to see the data you are starting with. Do you have an Excel sheet set up with the employee's salary and 401(k) contribution? Do you need annualized, or per pay period?

Here is the calculation using annual salary and annual employee contribution.

Values as displayed

	A	B	C
1	Salary	Employee Contribution	Employer Contribution
2	$100,000.00	$ 19,500.00	$ 4,000.00
3	$100,000.00	$ 2,000.00	$ 2,000.00
4	$100,000.00	$ 3,000.00	$ 3,000.00
5	$100,000.00	$ 4,000.00	$ 3,500.00
6	$100,000.00	$ 5,000.00	$ 4,000.00
7	$100,000.00	$ 6,000.00	$ 4,000.00

Underlying formulas

	A	B	C
1	Salary	Employee Contribution	Employer Contribution
2	100000	19500	=MIN(3%*A2,B2)+50%*(MIN(5%*A2,B2)-MIN(3%*A2,B2))
3	100000	2000	=MIN(3%*A3,B3)+50%*(MIN(5%*A3,B3)-MIN(3%*A3,B3))
4	100000	3000	=MIN(3%*A4,B4)+50%*(MIN(5%*A4,B4)-MIN(3%*A4,B4))
5	100000	4000	=MIN(3%*A5,B5)+50%*(MIN(5%*A5,B5)-MIN(3%*A5,B5))
6	100000	5000	=MIN(3%*A6,B6)+50%*(MIN(5%*A6,B6)-MIN(3%*A6,B6))
7	100000	6000	=MIN(3%*A7,B7)+50%*(MIN(5%*A7,B7)-MIN(3%*A7,B7))

It is very helpful to include all of the details of your question in your first post.

The answer is going to be very dependent on what your existing Excel file looks like. Please attach a file with sample data. Obscure any private data. (See yellow banner at the top of the page.)