How to Calculate 401k Match in Excel

[Guest rickmagill]

Can someone tell me how to set up an excel spreadsheet that will calculate the following employer 401k match for each employee.

50% of the first 2%

25% of the next 2%

[FAPInJax]

Well, let's presume you have the following information for each employee - deferral and compensation. I believe the following can be done in additional columns

1 Determine the % of compensation for the deferral

2 Calculate 50% of the lesser of (the percentage OR 2%)

3 Calculate 25% of the lesser of ((the percentage minus 2%) OR 2%)

4 Sum steps 2 and 3

5 Apply the percentage in 4 to compensation

That should do it - famous last words <G>

[BG5150]

Here is my Safe Harbor Match calc spreadsheet. Someone in the forums gave me the formula, I think.

You can modify it to your formula.

Safe Harbor Match Calc.xlsx