Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Help! 401k Match Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am newer to Excel.

I need to create a formula that calculates an employer match and can be
modified to illustrate different employer matching amounts. It will need to
recognize what an employee is contributing, and match the appropriate amount
without going over.

I have two data columns, salary and percentage of salary employee is

View Answers     

Similar Excel Tutorials

Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Find the Min or Max value in a range and, based on that, return a value from another range. This is an advanced lo ...
Best Lookup Formula in Excel - Index and Match
A lookup using INDEX and MATCH is like a VLOOKUP without the restrictions.  Index and Match lookups offer you free ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Vlookup Partial Match in Excel
Return Vlookup results on partial matches of a cell's contents. You could type the start, end, or middle of a looku ...

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Filter Data to Show the Top 10 Percent of the Data Set in Excel - AutoFilter
- This Excel macro filters a set of data in Excel to display only the top 10 percent of that data set. This means that th
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li

Similar Topics

Ok all, I am a noob to this site! I have a problem and was wondering if any of you Excel masters can help out. I have a spreadsheet and I need to calculate the 401k the company matches up to 6%. Here is an example:

If yearly salary = $50,000 and I vest 8% of my salary then I have vested $4,000 into my 401k. The company will match up to but not more than 6% of what my 401k percentage therefore in this example they will match 6% of $50k = $3,000

So if I have 4 columns:

A = Yearly Salary
B = My 401k vestment
C = Employer 401k match (up to but not more than 6%)
D = 401k Percent vesting

How would the equation be formulated for cell "C"?

Thanks for any help.

Sincerely, T

Hi all,

I'm fairly inexperienced with advanced excel functions, so this might be a ridiculously simple problem to solve. But I have 2 worksheets and I need to populate data in one worksheet based on information found in the second. Worksheet 2 has multiple row entries for the same person, but I'm looking for the row with the total amount. I think it's a Vlookup formula but perhaps there's another way to do it.

Worksheet 1 looks like this:
Employee Name Employee Total Employer Total
Doe, Jane

The columns for employee total and employer total are blank. I need to pull the corresponding value from worksheet 2, which looks like this:

Doe, Jane EMPLOYEE PRE-TAX 96.15 05/06/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 05/20/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 06/03/2010
Doe, Jane EMPLOYEE PRE-TAX 96.15 06/17/2010
Doe, Jane EMPLOYEE TOTAL 384.60

Doe, Jane EMPLOYER MATCH 307.69 05/06/2010
Doe, Jane EMPLOYER MATCH 307.69 05/20/2010
Doe, Jane EMPLOYER MATCH 307.69 06/03/2010
Doe, Jane EMPLOYER MATCH 307.69 06/17/2010
Doe, Jane EMPLOYER TOTAL 1,230.76

So, I need a formula that will match the name from both worksheets, and populate '384.60' into worksheet 1 under the Employee Total column and '1,230.76' under the Employer Total column.

Makes sense? Any help would be appreciated!


Hello, I sure would appreciate help creating this function.

Employee's Employee's Employer's
Monthly Salary Monthly Contribution Monthly Contribution

2580 120 C2
3412 136 C3

In cells C2 C3, write an IF statement to calculate the Employer's Contribution using information in the box below.

Employers match employees' Social Security contributions up to 4 percent. That is, if an employee contributes 4 percent or less of his/her monthly salary, the employer's contribution will equal the employee's. However, if the employee contributes more than 4 percent, the employer's contribution is 4 percent.

Thank you very much in advance.


I have a pension worksheet I desperately need help with. Employees have to wait a year to get the employer match to their 401k contributions.

I have an employee hire date, and 3 criteria I need to apply to that date to verify the Employer (ER) pension match.

If the employee has completed one year of service (actually 365 days + whatever days will get them to the first of the month subsequent to completing 1 yr of service) through to the end of their 3rd year, they get an employer match of 3%. So it's 3% employer contribution, during their 2nd & 3rd years of employment.

They get a 4.5% match during their 4th & 5th years of employment. Beginning their 6th year of employment they get a 6% match (assuming they are putting in 6%).

It's actually a 100% ER match for a 3% EE (employee) contrib during the 2nd & 3rd years, 150% ER match for a 3% EE contrib during the 4th & 5th years, and a 6% ER match for a 3% EE contribution from the beginning of the 6th year. The ER match is only up to a 3% EE contribution.

So if they were hired on 1/15/01 and are making a 3% personal contrib, they get a 3% match from 2/1/02 to 1/31/04, they get 4.5% match from 2/1/04 to 1/31/06, and 6% match from 2/1/06 onward. I hope this is not too hard to follow.

I tried to come up with a formula that will work, but didn't get too far. With cell E12 having Date of Hire I used:


but it returns 3% in the formula cell, even with a DOH of 4/1/01. Obviously I don't have the date ranges entered. For the 3% ER match I think I need have >365<1460 days, for 4.5% match >1461<2189 days, and today()<2190 days (for the 6% match)

Any help is welcome. Thanks.

Boston, MA

I'm trying to use the following formulas to get the answers for employee, employee total, and future value. Every time I use the formulas I get the error #Name?. Formulas Im supposed to use are :
(for employee) = Annual_Salary * Percent_Invested /12, (for employer total) =IF(Percent_Invested < Company_Match, Percent_Invested * Annual Salary /12, Company_Match * Annual_Salary /12), (for total) =sum(c10:c11), and (for future value) =-FV(Annual_Return/12, 12 * Years, Total)

B3:B13(Employee Name to Future Value)
C3:C13(Paul Morimoto to answer for future value)

Here's what I have:

Employee Name Paul Morimoto Annual Salary $82,000.00 Percent Invested 6.00% Company Match 3.00% Annual Return 6.50% Years 30 Monthly Contribution Employee #NAME? Employer #NAME? Total #NAME? Future Value #NAME?


I was asked by a friend to help with an Excel question but soon found myself in over my head. Any help or suggestions would be appreciated.

My question is tax related. An employer agrees to pay his employees a set amount after tax (net salary) e.g. $12800. The employer must remit tax to the government based on the gross salary. If I have the net salary and the tax brackets but am at a loss to find a formula to solve for gross salary for any level of net salary. The tax brackets are as follows:

0% on the first $10,000
15% on from $10,000 to %13,000
30% on anything over $13,000

I have been experimenting with the goal seek function but have not made any headway.


Hi all,

I have built a formula which is working fine, but i was wondering if you could provide me with a more elegant one. Here is the idea and the formulas i used:

I have a budget sheet where all employees are stored with some basic data and some salary data.
Basic data are columns A to D (A5= id, B5= Name, C5= Department, D5=Position)
Salary data are columns E to H (E5=Salary, F5=Raise (%), G5= Bonus, H5= Employer Contribution). Columns M5 to X5 are months, Jan to Dec. Column Z5 =Total Cost (summing months)

What i want to do is to calculate the total cost for each employee throughout the year, based on the numbers entered in Salary Data columns. Using the following formula ( E6+($E6*$F6)+$G6+$H6 ) i calculate the cost of one employee . The tricky part is that i wanted somehow to calculate the cost IF midyear the company decides to alter the strategy and for example instead of 2% raise, decrease it to 1,75% starting from March for example.

So i added three helper columns. Column I with a data validation list with months. Column J with the new percentage. Column K where i calculate how many months the cost will be calculated with the original Raise% (F6) and how many with the new one (J6).

Cell K6 = MATCH(I6;$M$5:$X$5;0)-1. If in I6 i choose March from the data validation list, K6 gives 2 which corresponds to January and February where the original Raise% will be applied, whereas the rest 10 months (starting from March) will be using the amended Raise% in cell J6

Cells M6 to X6 holds the following formula which gives me the result i want


Hi all..

i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow

the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.

Data files will contain of these:
a:Employee ID
b:employee name
c:Employee salary/Overtime

Master files will contain of these:
a:Employee ID
b:Emplyee Name
c:Employee Nett Salary (that will be dragging from Data files)

is there any formula that i can used to combined with my vlookup formula?

appreciate any help! thanks!!

I'm trying to create a IF function to stop at $1500 for 401k employer match. To make it simple the variable in column D is constant but in reality it's based on 50% match amt up to $1500 of amount contribute(% of gross pay). The accumulation of matching is a rolling balance. If the match amt is less 1500, then match amt. But False, the left over amt equal 1500 then STOP there onward.


Okay - I'm fried and this one's confusing the heck out of me. It's for determining the company matching amount of an employees 401K defferal amount.

The legalese says this about calculating the company match:

(Employer shall match 100% of each participants deferral contribution, not exceeding 3% of the participants compensation, plus 50% of each participants deferral contributions in excess of 3%, but not in excess of 5% of the participants compensation).

So, I don't easily have access to the participants compensation number, but I do have the participants deferral percentage and deferral amount.

Please help me calculate this so I can save my job and my sanity.

I have a table containing data of the National Insurance contributions that the employer must make on behalf of its employees. See table below. The contribution that must be made is based on the salary of the employee. For example, if the salary is 10,000 per month the contribution payable is different to that for an employee with salary of 1,000 per month.
I need to be able to enter a salary in a cell, then a formula takes the salary, look between the lower limit and upper limit of earnings to determine which Earning class is appropriate. Once the correct class is located, the appropriate employer's contribution is returned.

I am using the vlookup function, but by itself it does not yield the results. I would appreciate some help in suggesting a function that could used in conjunction with the vlookup. I would be happy to for help. Thanks

Range of earnings Earnings Class Lower level Monthly Earnings Upper level Monthly Earnings Employer's Weekly Contribution I 433 692.99 8.58 II 693 952.99 12.54 III 953 1256.99 16.84 IV 1257 1559.99 21.46 V 1560 1906.99 26.4 VI 1907 2296.99 32.02 VII 2297 2686.99 37.96 VIII 2687 3076.99 43.9 IX 3077 3509.99 50.16 X 3510 3942.99 56.76 XI 3943 4376.99 63.36 XII 4377 4810.99 66.66

Hi guys - fantastic site. Very informative.

Need some help. I've got a table as shown below. I'm trying to find a formula that will return value A for a given salary figure. I will then repeat the same formula for value B.

For instance, if i key in a salary of 2,415.00, the formula should return 42.85 for value A, or 12.25 for value B. ... for 2,630.00 --> A=46.35 ...etc

Can someone pls help me. I'm a relatively intermediate user of excel so pls feel free to get technical if need be.

Thanks a lot!

Salary Range (max)
Employer (A)
Employee (B)
TOTAL 2,400.00 41.15 11.75 52.90 2,500.00 42.85 12.25 55.10 2,600.00 44.65 12.75 57.40 2,700.00 46.35 13.25 59.60 2,800.00 48.15 13.75 61.90 2,900.00 49.85 14.25 64.10 3,000.00 51.65 14.75 66.40

hello excel experts!
here i ve one tax data,i need to apply tax according to salary and tax rate,i use if and (and) formula ,also vlookup formula but when i drag it it give me correct value but give in every row,i want it should give me value according to tax rate otherz tax amount should give blank same problem with vlookup give correct answer but in 1st row only.

i want tax rate*salary in specific row not values in every row,i ve also applied f4 cell name as salary.
kindly solve this easy problem i couldnt figure out how?

A B C D E F 4 SALARY 500,000 5 FROM TO TAX RATE TAX AMOUNT 6 - 100,000 0% 55,000 55,000 7 100,001 200,000 1% 55,000 - 8 200,001 300,000 3% 55,000 - 9 300,001 400,000 7% 55,000 - 10 400,001 500,000 11% 55,000 - 11 500,001 1,000,000 15% 55,000
Spreadsheet Formulas Cell Formula D6 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E6 =IFERROR(VLOOKUP ($F$4,A6:$C$11,3) *F4,"") D7 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E7 =IFERROR(VLOOKUP ($F$4,A7:$C$11,3) *F5,"") D8 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E8 =IFERROR(VLOOKUP ($F$4,A8:$C$11,3) *F6,"") D9 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E9 =IFERROR(VLOOKUP ($F$4,A9:$C$11,3) *F7,"") D10 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E10 =IFERROR(VLOOKUP ($F$4,A10:$C$11,3) *F8,"") D11 =IF(AND (SALARY>=$A$6,SALARY=$A$7,SALARY=$A$8,SALARY=$A$9,SALARY=$A$10,SALARY=$A$11,SALARY>=$B$11) ,SALARY*$C$11,"") ) ) ) ) ) E11 =IFERROR(VLOOKUP ($F$4,A11:$C$11,3) *F9,"")

I have a spreadsheet template for making 401K projections.
It has a wide variety of inputs, including different employer matches, which are calculated uniquely to each plan.

My current template works great for most plans, which only have a single match, ie, "= X% of the first X%".

For example, a common match is 60% of the first 5%. Here is how I calculate that with formulas:
MatchRate1 = 60%
MatchRange1 = 5%

Contribution rate = employee option, any integer between 1% and 100%
(subject to contribution limitations, which is a whole separate issue)



which is equivilent to:

= (Contribution rate) + MIN ( ( Contribution Rate * MatchRate1 ) , ( MatchRate1 * MatchRange1) )

The problem is, some 401 K plans have SEVERAL different matches... like this:
"100% of the first 5%, 60% of the next 3%, and 25% of the next 2%"

MatchRate1 = 100%
MatchRange1 = 5%
MatchRate2 = 60%
MatchRange2 = 3%
MatchRate3 = 25%
MatchRange3 = 2%

How would I build my equation for that?

I was thinking nested MIN() statements, maybe...
Or maybe nested IF()s?

Thanks for any help.

Hello All,

We are trying to make an excel Sheet which computes the monthly salary of the employees on the basis of the amounts mentioned in the master sheet and number of days present. Everything works like a charm except that we are not able to revise the pay scale.

Our Master Sheet consists of Name of Employee as Row Headers and Component of Salary as Column Headers. The Table Row and Columns have been named using Ctrl + Shift + F3.

Now whenever we want to compute a particular salary component of any employee in the monthly salary sheet we simply use the Indirect Function.

Now the problem is if we want to change the salary of an employee in the middle of the year, how to go about it so that the salary of the previous month remains undisturbed and the new payscale comes into effect from current month onwards.

Your replies would be a life saver

Thanks in Advance


I'm working on budgets (salaries). I have one main spreadsheet with all employee names listed - other spreadsheets are for each department. I need to bring the total salary from the department spreadsheets and would like to do this by looking up (or matching?) the names in the column from the main sheet to the department sheets and then putting the salary from the department spreadsheet to the main spreadsheet. In addition, I have several that have their salary split between two or more departments. On my department spreadsheet I have a column labeled % of salary for this department. On the main spreadsheet, I want to figure out a formula that will sum the percentages for a certain person (so I make sure I don't go over 100%).

For example - Joe Smith's name is on the main employee spreadsheet. He is also listed on 3 other department spreadsheets, with % of salary listed as 30% for department 1, 30% for department 2, and 40% for department 3. On the main spreadsheet I need to add up Joe's percentages from the department spreadsheets.

Can someone help me? Thanks!

I have a question related to employee salary calculation. We are changing to a July 1 effective date for all. I have an employee whose anniversary date is November 6. The boss wants to pay them so their check increases on July 1 by an amount adjusted so that their total salary for 2011 will be the same as they would have been if their salary increase had begun on their effective date.

What formula can I use if I have the following data to use:
Current Bi-weekly: $1000
Salary Increase Percentage: 2%
New Bi-weekly: $1020
Anniversary date: 11/06/2011
Effective date: 07/01/2011

I'm trying to set up a formula for our work salary. Currently every person's salary has to be manually selected based on their years worked and education. I'm trying to find a way to have the salary selected automatically based on information as it is updated. It would really be great if I could add a few columns with start date and degree date and use it to determine the length of time that should be used for their education salary scale. Please see the attached file.

The salary in column P is the one I want to be updated automaticly based on columns L and M. I'm trying to use the formula below but it comes back with incorrect data even though it works on some other spreadsheets that I've used.


Any help would be greatly appreciated.


I am an average user of excel 2010 but not a pro. I am working on a payroll spreadsheet which obviously has various employee salary grades. Each employee's net salary is calculated on this sheet and depends on their salary grade. The salary grade tells me what amount the gross salary would be, how much deductions for tax and health insurance e.t.c.

What i'm trying to do is to get excel to automatically fill out the blanks for me as soon as the salary grade is entered. I want fields such as the gross salary, rate of tax, health insurance and all the fields that derive their initial values from the salary grade to be filled out automatically and then i can compute the rest on my own.

Is there a way i can handle this?

Any help will be appreciated. Thanks.

I have the following columns:

Month, Account, Category, Payment, Amount

For example (in order by month) I have the
following data:

Jan, 9041, Salary, 2100, $ 400
Jan, 9041, Salary, 2101, $ 400
Jan, 9041, Salary, 2102, $ 400

In another sheet I have columns:

Jan, Feb, Mar, etc

and row:


What I want to do is look down
the data from the first sheet,
find the Salary data for 'Jan'
and sum the result ,in this case $ 1200,
into the cell next to Salary in the other

I think it might involve Vlookup,
or Index, and IF, and Match, and Sum,
I just don't have a clue how
to put it all together.



Dear Friends,

I need your help.

Every month I make salary sheet for our company. here everybody having variety of salaries (from 100 to 5000 ). And I divide those salary into 10 , 20, 50, 100, 200, 500 & 1000

Example: See the attachment

Employee Name Salary 1000 500 200 100 50 20 10 5 2 1
A 1654 1 1 0 1 1 0 0 0 2 0
B 854 0 1 1 1 1 0 0 0 2 0

Please give me any formula to do this automatically when I enter that amount ...


Need help to correct my formula. I try using this formula in order to plot the contribution of the employer based on the salary of an employee:
=INDEX(contri table!$F$5:$F$33,MATCH($A4,TRIM(LEFT(contri table!$B$5:$B$33,FIND("-",contri table!$B$5:$B$33)-1))*1,1)) but it always gives me a #N/A result, what seems to be problem? Hope you could enlighten me bout this.

Thank you.

I'm in the process of a writing a worksheet that calculates an employee's severance package based on various criteria. I'm having trouble writing a formula that will perform a specific caluclation based on a field called Employee Band. For example, there is a drop-down menu for the Employee Band Field containing 5 choices: Hourly, Non-Hourly, Exempt, Executive Band, and Senior Executive Band. If the employee is hourly, then I'd like to multiply the salary entered by 40 (since these salaries will be entered as an hourly rate); if the employee is non-exempt, I'd like to leave the salary as-is (since these salaries are already in weekly-format); and if the employee is either exempt, executive band, or senior executive band, I'd like to divide the salary entered by 52.1667 (since these salaries will be entered as a yearly salary). What is the best way to achieve this?

Thanks in advance for the help!

Hi watersav,

please also help me to create the salary slips by a data sheet am attaching the data sheet and the format of the salary slip. But want all the salary slips to be populate in different sheets.Please create all the sheets i know you can do this.

The below are the fields which i want to populate from the data sheet to the salary slips. Each employee salary slip should get populated in the different sheet. Attached is the data sheet and the format of the salary slip. Please help as i have found you as a genius in Excel

1.Date of Joining (D6.OJ)
2.Name of Employee
3.Total Days
4.Days Worked
8.Days Payable
9.Gross Salary Due
13.Other Deductions
14.Net payable.

Hi everyone!

I am trying to match employee information between two different excel sheets. Currently, both sheets have firm identifier codes for each employee that match exactly. In addition, the both sheets have employees' full names that can have minor differences between each other (e.g. "John M. Doe" and "John Doe"). How can I create a formula that uses two criteria where the first one needs to be an exact match (firm identifier) and the other one a closest match (employee name)?

For each firm there exist information only for 1-20 people.

I tried to use the formula from ,but I assume that only works if the second condition is a number.