Help! 401k Match Formula 


Help! 401k Match Formula  Excel 
View Answers 
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.
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 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 ...
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 ...
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 ...
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...
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
 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
 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
 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
 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
 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
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 PRETAX 96.15 05/06/2010
Doe, Jane EMPLOYEE PRETAX 96.15 05/20/2010
Doe, Jane EMPLOYEE PRETAX 96.15 06/03/2010
Doe, Jane EMPLOYEE PRETAX 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
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 PRETAX 96.15 05/06/2010
Doe, Jane EMPLOYEE PRETAX 96.15 05/20/2010
Doe, Jane EMPLOYEE PRETAX 96.15 06/03/2010
Doe, Jane EMPLOYEE PRETAX 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
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 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?
(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
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 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)
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
VLOOKUP(B4:B225,'D:\Salary\[Data.xls]AUG'!A$1:F$65536,6,FALSE))
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.
IF(SUM($D$3:D19)+($C20*0.5)<1500,$C20*0.5,SUM($D$3:D19)+($C20*0.5)1500)
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.
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
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
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,"")
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:
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.
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.
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
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!
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 Biweekly: $1000
Salary Increase Percentage: 2%
New Biweekly: $1020
Anniversary date: 11/06/2011
Effective date: 07/01/2011
What formula can I use if I have the following data to use:
Current Biweekly: $1000
Salary Increase Percentage: 2%
New Biweekly: $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.
=INDEX($B$8:$J$58,MATCH($M6,$A$8:$A$58),MATCH($L6,$C$5:$J$5))
Any help would be greatly appreciated.
Hello,
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:
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
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 ...
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 ...
Hi
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 dropdown menu for the Employee Band Field containing 5 choices: Hourly, NonHourly, 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 nonexempt, I'd like to leave the salary asis (since these salaries are already in weeklyformat); 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!
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
5.Absent
6.CL
7.EL
8.Days Payable
9.Gross Salary Due
10.PF
11.ESIC
12.TDS
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 120 people.
I tried to use the formula from http://www.excelforum.com/excelgene...estmatch.html ,but I assume that only works if the second condition is a number.
Thanks!