Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
I am trying to create a formula that will create the following: Column E
Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. I have tried IF....but I receive an error. Please help! Thank you for your assistance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
One way:
H2: =IF(DATEDIF(C2,DATE(2008,12,31), "y") < 21, 0, (MIN(E2*0.05, G2) + MIN(E2*0.03, G2)) / 2) In article , KristiM wrote: I am trying to create a formula that will create the following: Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. I have tried IF....but I receive an error. Please help! Thank you for your assistance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
Thank you for so much for the response. I will try this formula tonight for
my spreadsheet. "JE McGimpsey" wrote: One way: H2: =IF(DATEDIF(C2,DATE(2008,12,31), "y") < 21, 0, (MIN(E2*0.05, G2) + MIN(E2*0.03, G2)) / 2) In article , KristiM wrote: I am trying to create a formula that will create the following: Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. I have tried IF....but I receive an error. Please help! Thank you for your assistance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
On May 15, 1:00*pm, KristiM wrote:
I am trying to create a formula that will create the following: *Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. *Also no match if employee is under 21 *of age at 12/31/08. Does the following work for you: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%))) I took the liberty of assuming you really want to calculate age based on the end of the current year, not always 12/31/2008. Also, I assume you mean no match over 5% (3% + 2%). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
No match over 4% since 50% of 2% will be 1% with 3% at 100% total 4% match
awarded. "joeu2004" wrote: On May 15, 1:00 pm, KristiM wrote: I am trying to create a formula that will create the following: Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. Does the following work for you: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%))) I took the liberty of assuming you really want to calculate age based on the end of the current year, not always 12/31/2008. Also, I assume you mean no match over 5% (3% + 2%). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
After clarification the match should be calculated from Column G Emp
contribution not Column E Compensation. I will need to include Column D elected percentage in the formula. If the Column D (elected percentage) is less than 3% calculate at 100$, but if column D is more than 3%, then 2% is at 50%. Should the formula be: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,D2*3%) + 50%*max(0, min(G2-D2*3%,D2*2%))) "joeu2004" wrote: On May 15, 1:00 pm, KristiM wrote: I am trying to create a formula that will create the following: Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. Does the following work for you: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%))) I took the liberty of assuming you really want to calculate age based on the end of the current year, not always 12/31/2008. Also, I assume you mean no match over 5% (3% + 2%). |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
401k Match
On May 26, 8:36 am, KristiM wrote:
After clarification the match should be calculated from Column G Emp contribution not Column E Compensation. I will need to include Column D elected percentage in the formula. If the Column D (elected percentage) is less than 3% calculate at 100$, but if column D is more than 3%, then 2% is at 50%. I assume that "100$" is a typo, and you mean 100%. Also, you do not say what happens when the elected percentage is __equal__ to 3%. I will assume you mean "less than or equal to" 3%. It appears that you are trying to compute a matching percentage, not a matching amount. Frankly, I don't know why you don't simply compute the matching amount. It is simple, namely: E2*min(D2,3%) + 50%*E2*max(0,D2-3%) But if you insist on computing a matching percentage, try the following: min(D2,3%) + 50%*max(0,D2-3%) If you understand the second formula, note that the first expression can be simplified to: E2 * (min(D2,3%) + 50%*max(0,D2-3%)) Plug whichever expression fits your need into the IF formula below: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, expression) Does that answer your question? ----- original posting ----- On May 26, 8:36 am, KristiM wrote: After clarification the match should be calculated from Column G Emp contribution not Column E Compensation. I will need to include Column D elected percentage in the formula. If the Column D (elected percentage) is less than 3% calculate at 100$, but if column D is more than 3%, then 2% is at 50%. Should the formula be: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,D2*3%) + 50%*max(0, min(G2-D2*3%,D2*2%))) "joeu2004" wrote: On May 15, 1:00 pm, KristiM wrote: I am trying to create a formula that will create the following: Column E Compensation, Column G Emp contribution, and C Date of birth mm/dd/yyyy. The match 100% of 1st 3% and 50% of next 2% No match over 4%. Also no match if employee is under 21 of age at 12/31/08. Does the following work for you: =if(datedif(C2,date(year(today()),12,31),"y") < 21, 0, min(G2,E2*3%) + 50%*max(0, min(G2-E2*3%,E2*2%))) I took the liberty of assuming you really want to calculate age based on the end of the current year, not always 12/31/2008. Also, I assume you mean no match over 5% (3% + 2%). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function to calculate a 401k rate of return? | Excel Worksheet Functions | |||
Calculating a tiered 401k Match | Excel Worksheet Functions | |||
formula to calculate a 401K company match? | Excel Worksheet Functions | |||
401k formula | Excel Worksheet Functions | |||
Help! 401k match formula | New Users to Excel |