What should the calculated value be if either the annual salary or 401K contribution are blank?
George, thanks for asking. The default values of Annual Income and Employer Match are both 0. So the calculated value is 0 if either field is untouched or left blank.
Some of what you post isn't consistent, so it might be best if you posted the Excel formula that you're using.
I was concerned about that. I will post my Excel workup in a few hours when I'm at my desk.
Thank you for looking into this.
George, hope this works,
C4 = 401k Contribution ($6,000, per my example)
C3 = Annual Income ($80,000, per my example)
First we test if the 401k Contribution is > than 3% of Annual Income =IF(C4<(C3*0.05),C4. If not, we simply match 100% of the contribution. But, if the 401k Contribution is > than 3% of annual income, we multiply the difference between 3% and 5% by .5, and then add 3% of Annual Income to the result.
The 18000 maximum contribution limit is actually not relevant. We only need concern ourselves with 5% of Annual Income, as this is the matchable amount. My form won't allow a contribution greater than 18000.
There is probably a cleaner way to write that formula. But, did it on the fly.
> First we test if the 401k Contribution is > than 3% of Annual Income =IF(C4<(C3*0.05)
That formula is calculating 5%, not 3%, so I'm still unsure what's correct.
Thank you for your patience! I did make a mess of that last effort.
Here is the updated and [better] proofed and reproofed formula.
A1 = Annual Income
A2 = 401k Contribution
- Test if 401k Contribution is < than 3% of Annual Income. If TRUE, calculated value is equal to 401k Contribution
- If FALSE, calculated value equals ((401k Contribution - (Annual Income * 3%)) * .5) + (Annual Income * 3%)
In short, If the contribution is more than 3% of income, we half difference of the contribution and 3% of income and add back the 3% of income.
I really appreciate your patience!
After sleep, I came up with this. Seems to work.
//A1 = Annual Income
//A2 = 401k Contribution
var A1 = getField("Income").value;
var A2 = getField ("401(k)").value;
var A3 = A1*.03;
var A4 = A1*.05;
var A5 = A1*.02;
//Contribution less than 3% of income?
if(A2<(A1*0.03)) event.value = A2;
//Contribution is less than 5% of income?
else if (A2<A4) event.value=((A2-A3)*.5)+A3;
//Contribution is greater than 5% of income
else if (A2>A4) event.value= (A3)+(A5*.5);