401K Match calculation formula

[pseeley4]

I got assigned a 401K conversion project because I had previous experience in funding my old employers 401K plan. Everything was formated to calulate the match on my old employers spreadsheet and all I had to do is fund the match. I now am with a new employer who wants me to create this calculation although I have no experience in creating fomulas in excel.

I need to get a formula that I can use in an excel spreadsheet to calculate 50% of the first 6% of compensation for a maximum of 3% match.

Thanks
Patricia

[kork13]

I'm good with this sort of thing, I worked up a simple one that should do the job for you. I'll try to describe it, then feel free to ask questions if you need to. I'll save the file, so if you'd like i can just email it to you as well.

In cell A1, type "Name". In B1, type "Salary". In C1, type "Contribution". In D1, type "Match". <<<These are your column headings.

In cell A2, type a person's name. B2, enter their salary. C2, enter the amount they are contributing. D2, enter the following: =IF(C2>=(B2*0.06),(B2*0.03),(C2*0.5)) <<<By referencing those cells, you can copy that formula down as many times as necessary, and it will do all the thinking for you. The amount that comes up in D2 is the amount your employer should pay as the match.

If that's confusing, I can just email you the file if you wouldn't mind providing an email address.

[pseeley4]

Thank you. I think that will work. I would love to understand the format of the formula. What action do the commas do. I understand the parenthesis but couldn't figure out how to get the what if.

You don't know how this has been bothering me that I couldn't get this to work. I have been working on this for over two weeks.

I may need to ask you help again in the future to get up a year to date match calculation but I haven't gotten that far yet with this project. Would it be a problem contacting you again? If so, would you prefer I contact you through this website or can you provide an email address.

Thanks again. I'll be getting a good night sleep tonight knowing that this has been resolved.

[kork13]

Glad I could help. the commas simply form the structure of the "if (this), then (that)" of the formula.

basically, before the first comma is the condition, so it's telling the computer: "if the contribution is greater than or equal to 6% of the salary..."

the next part is the affirmative result, or what happens if that condition is true (i.e., contribution is more than 6%). "...then report out 3% of the salary as the match."

the last part is the negative result, for when the condition is false (i.e., the contribution is less than 6%). "...then report out 50% of the contribution as the match."

I'd be happy to help however I can. you should be able to email me through my profile on here, then once we're off these forums you can just email me directly. feel free to drop a line anytime and i'll help as well as possible.

[mrpaseo]

Excel is my favorite program but I am in no way as smart as you are on it. How did you learn these formulas? Trial and error or a class?

Ray

[tripods68]

Kork13,

This is great. I was following along to brush up on "what if" and "vlook" on my desktop. I have a similar project; its based on teacher's salary, years of service, longevity pay, and benefit factor formula. I'm still trying to put together the formula table calculation.

[kork13]

mrpaseo, it was some of both.... I was introduced to some of it in an operations research class in college, then from there sort of went the trial and error way to get more...

tripods, unfortunately i'm not extremely familiar with the vlookup functions, but i've found that microsoft's help application is generally enough to work through something with enough tries. as with pseeley, if you'd like to give some details on what you're trying to do i'd be more than happy to help if i can. Right now my job isn't very demanding (about to the point of boring), and now i'm looking at a very wet weekend thanks to Fay.... so i've got plenty of time if you'd like some help.

[pseeley4]

My company just informed me that the match shouldn'tl stop when the
401K deduction stops unless it has meet the maximum of 3% of compensation. According the the calculation you gave me, once the deduction stops the match will too. Can you revise the calculation to include this new data? The match per year is 3% of compensation with a maximum of $6900.

Thanks.

[kork13]

i don't think i understand.... do you mean that the company will always pay the 3% regardless of what the individual contributes?

[pseeley4]

If an enployee has a salary of $5000 per semimonthly payroll (24 pays per year) and contributed 50% of pay they would reach the IRS maximum contribution level of $15,500 in 7 pays. The employee would receive $930. instead of $3600 (50%of the first 6% fo compensation) because the deduction would have stopped in the 7th payroll for the year. I need the match to continue. Ccalculating on the compensation instead of the deduction will make this senerio happen.