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
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
How to Quickly Find Data Anywhere in Excel
Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & ...
Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & ...
How to use the Vlookup Function in Excel
Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it. The Vlook ...
Full explanation of the Vlookup function in Excel, what it is, how to use it, and when you should use it. The Vlook ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
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,"")
Hello all,
I have a spreadsheet with two worksheets. The first is a list of employee data, with a unique employee in each row, city, and salary. There is a blank column to the right, for geographic differential (cities can be more or less expensive to live in.) The cities are standardized across the company, so they can work as a lookup value.
The second is a list of cities, and the different geographic salary multipliers in each. The geographic multipliers are different for different salaries. In Atlanta, for instance, people making around $20k see pay increased by 4.3%. and people making $90k see a lower differential of 1.0%. I should note that differentials can be negative as well. In the attached spreadsheet, the differentials should be 4.6% and 4.0%, respectively.
What I want to do is find the bracket that each employee's salary is closest to, using a standard mathematical round, and return the percentage by which the salary should be increased or decreased. I know that I need arrays, index, and match, but this is the most complicated formula I have ever built and it's making my head spin. Any help is, as always, much appreciated!
Excel Help.xlsx
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.
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
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 I'm new to this forum.
In Sheet1  I have a spreadsheet containing 5 employees containing basic employee details (eg Names, Base Salary and Grade).
In Sheet2  There are five different salary ranges (columns as follows: MINIMUM SALARY, MAXIMUM SALARY, GRADE). The unique identifier is the Column "GRADE" which helps identifier which employee's salary is relevant to the list of salary ranges. Sheet1 also contains each employee's own grade.
in Sheet1, I wish to output:
Whether each employee's Base Salary is above the salary range threshold using the salary ranges from Sheet2
Which is the best formula to use in order to get the best output?
Many thanks
mramo
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 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
What wold be easiest way to handle this?
A V Veerkar
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 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
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
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.
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
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.
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.