Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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
deferring.



Similar Excel Video Tutorials

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
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
Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
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
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th

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!

Linda


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.

Stephanie


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:

=IF(E12<TODAY()-1095,"3%",IF(E12<TODAY()-1461,"4.5%",IF(E12<TODAY()-2190,"6"%)))

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.

LTUser54
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?


Hello,

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.

Thanks


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

=IF(MATCH($I6;$M$5:$X$5;0)=1;$E6+($E6*$F6)+$G6+$H6;IF(MATCH($I6;$M$5:$X$5;0)>1;IF(COLUMNS($M5:M5)

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.



IF(SUM($D$3:D19)+($C20*0.5)<1500,$C20*0.5,SUM($D$3:D19)+($C20*0.5)-1500)


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)

Code:

=$A14+(MIN(($A14*$E$2),($E$3*$E$2)))

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.


Hi all,

I am trying to create a formula using the Index and Match functions with some nested If statements. First of all below you will find a sample of my columns that I want to match data against.
Column A Column B Column C
Grad Step Salary
1 1 17803 1 2 18398 1
18990 1 4 19579 1 5 20171 1 6 20519 1 7 21104 1 8 21694 1 9 21717 1 10 22269 2 1 20017 2 2 20493 2 3 21155 2 4 21717 2 5 21961 2 6 22607 2 7 23253 2 8 23899 2 9 24545 2 10 25191
The Column L (Grade) and Column M (BSalary) columns below represent the data I want to find in the columns above. The new salary column below contains the current formula I have created.
Column L Column M Column N
Grade BSalary New Salary 1 18398 18398 1 18600 #N/A 1 19600 #N/A
Here is the current formula under the Column N (New Salary):
{=INDEX($C$3:$C$152,(MATCH(L4&M4,$A$3:$A$152&$C$3:$C$152,0)),1)}

What I want to do is if the Grade (column L) and BSalary (column M) match the exact values in column A (Grad) and column C (Salary) give me the matching value in the INDEX($C$3:$C$152) if the grade (column L) and bsalary (column M) do not match the exact values in column A (Grad) and column C (Salary) then give me the next value in the INDEX($C$3:$C$152) that is greater than the value in column BSalary (column M) but that continues to match my grade (column L)

You more explanation is needed, please let me know.

Thanks in advance!

BC


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

Karan


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 two tables. first table on sheet1 has two columns- first for Employee Number and second for Employee Age. Second table, say on sheet2, again has two columns- first Employee Number and second Employee Salary. Both tables are sorted on Employee Number. I want to create a new table of three columns, say on sheet3, with first column as Employee Number, second as Employee Age and third as Employee Salary. First two tables may have some Employee Numbers common and some numbers may not be common. Third table that we create must list all employee numbers (without repeating the Employee Numbers common to two tables) and have both age and salary columns filled up where data is available. For example if first table has 10 entries and second also has 10 entries but only 7 have common emp number then the new table will have 13 entries as there are total 13 employee numbers between the first two tables. Out of these 13entries 7 will have both age and salary columns filled up, 3 will have only age and 3 will have only salary. In short I want to merge two tables to create a third table which will have all the data of two tables without a duplicate entry.
What wold be easiest way to handle this?
A V Veerkar


Ok so I'm trying to help the Payroll department with their 401K contributions info.

I've got it all set and the lady walks in and say, I've got guys that earn commission and if they take a percentage instead of a fixed amount, they have 2 contributions and we have to match up to 4% of their total gross earnings.

Grrr.

I attached my sample worksheet to this post.
4 Sheets
401 DED WORKSHEET is the employees contribution page
Note: The 401k (K) column is a fixed contribution
Note: The 401k (R) column is a percentage contribution
Hours is all employees, Salary and Hourly
401K is my compiled data sheet
Export is the sheet she makes the Comma Delimited file from to import into the 401K funding group or whatever they call it.

Before her latest request everything was ok. I was using the File ID # as the unique identifier to locate my data and work with it on the 401K sheet. This worked fine until she told me that each employee that earns commission will have 2 lines, base pay and a commission line. So the same File ID # is in there twice. File ID # turns out is the employee ID #

If there is a commission row for an employee it's always below the base wage row.

So them I started asking her for another unique identifier and I found out that the Voucher # is a unique identifier. However she's not really sure if she can export from ADP the Voucher # onto the Hours sheet.

Well lets say she can.

How do I get 1 persons base pay and commission pay to total on the 401K sheet so that I can total their contribution.

I don't want to use the SSN as a unique Identifier either.

I Googled excel forum and you guys were at the top of the list. Any help would be awesome.

Thanks,
Mike


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 have a sheet that has a list of salaries by level. What i'm trying to do is basically put in the employee's level and have it look up the salary but if they have a salary modifier it would add it. Something like this:

EMPLOYEE PAY LEVEL CLASS LEVEL PAY CLASS
JOHN $10.00 1 1 $10.00 A $1.50
BILL $11.50 1 B 2 $10.25 B $3.00
STEVE $10.25 2 3 $10.50
MIKE $13.50 3 A 4 $11.00

I used an index match with an if statement but obiviously it didn't work cause here i am. any help would be great. thanks


Hi
I need to acomplish this:

Employee 1

Salary Jan Salary Feb. Accum. Salary Disability
$6,000 6,000 12,000 18



To compute disability expense for this employee I need to multiply salary amount by .3% up to 9,000. In Jan the result should be $18 (6,000 x .3%) but I Feb I need to compute disability only for the 3,000 I need to reach the 9,000 unemployment limit. How can I make a formula to do this? The correct result should be $9 (3,000 x.3%). Please help.
Thanks


If I have employees making anywhere from 20,446.40 - 69,659.20 and I have a budget of 100k how can I divide this between each employee so that everyone gets a portion and is divided up for the following plans; with the employee that is making the largest salary recieving the smallest amount of the budget?
How would I include the emp only amount or the emp+child amounts in this?

I have tried =100000/sum($A$2:$A$6)*2 but this doesn't give me the amount per plan or help me to determing how the employee with a higher salary can recieve the smaller amout from the budget.

For example if:
(116.95 Plan) (261.82 Plan) (311.05 Plan) (658.62 Plan)

(Annual Sal.) Plans
20,446.40 = 116.95
24,448.20 = 658.62
34,854.60 = 311.05
44,564.80 = 261.82
62,061.48 = 116.95
54,324.20 = 261.82

Thank you. Desperately in need of help.


Hi all,
hopefully should be a nice quick one for you. I have a range of data and i am double counting the salary for some employees as they contain splits. Employee 100103 is throwing out the totals in the salary column and they should match back as a check. ie only count employee 100103 salary of 20,000 rather than 80,000.

Staff Number Split Client Dept Salary Salary 100100 No 1 Account Management 20,000 20,000 100101 No 2 Overheads 20,000 20,000 100102 No 3 Creative 20,000 20,000 100103 30% 1 Account Management 20,000 6,000 100103 30% 2 Account Management 20,000 6,000 100103 10% 3 Account Management 20,000 2,000 100103 30% 4 Account Management 20,000 6,000 100104 No 4 Creative 20,000 20,000 100105 No 4 Editorial 20,000 20,000 180,000 120,000


thanks guys!


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:

Salary

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
sheet.

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.

Anyone?

Thanx


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 ...