Finance

Have finance questions? Ask a financial expert online.

Ask an Expert,
Get an Answer ASAP!

This answer was rated:

Need excel formula for 401k match of 50% up to 4% of

salary., so if contribution amount...
Need excel formula for 401k match of 50% up to 4% of salary., so if contribution amount changes, the total column of the match will not go above the 4% limit, Example if salary is 80,000 and contribution is 10% or $8000, the match total of $1600 will not go up if they change their contribution to 20% since it is already at the 4% cap. However if the cap has not been reached and they change the contribution % would like the match total to reflect the new total.
Show More
Show Less
Ask Your Own Finance Question
Answered in 15 minutes by:
3/21/2018
Carter McBride
Carter McBride, Financial Advisor
Category: Finance
Satisfied Customers: 1,311
Experience: Adjunct Professor
Verified

Hey! I'm Carter, I'm a CPA and can help you with this.

If we set up cells, A1: employee name, B1: employee contribution %, C1: Employee Salary, then in row 2, we have our first employee, the formula would be (C2*B2)*.5... that gives us the employee contribution and 50%.

So, then let's add an IF calculation to make it:

=if(C2*B2)*5<(C2*.04), (c2*b2)*.5, (C2 *.04). So, we're saying here, if 50% of the employee contribution is less than 4% of wages, then use 50% of employee contribution. If it's larger, then use 4% of salary.

Let me know if you need anything else. If not, please leave a rating. Thanks!

Ask Your Own Finance Question
Customer reply replied 4 months ago
Hi, Thank you for your response. However, the formula comes back with an error when I try with the =if...

Here you go. I fixed it for you. There was a space between C2 and *.04

Ask Your Own Finance Question
Customer reply replied 4 months ago
Sorry but that does not seem that it is correct. I have attached the spreadsheet again where under tab sheet 2 I had my original formula where it takes the contribution amount and if I were to change the % contribution the match also changes but it exceeds the 4% cap of salary.
I am looking for the formula to change with the contribution amounts, however not to exceed the 4%

You'll have to do it on a payroll basis. So on your sheet 2, I added dolumn D, which is pay periods. In the one I attached, he does 2% the first 5 pay periods and 10% the next 12.

If you go to column K, it calculates what the match should be during this pay period. You'll have to add a new row each time someone changes their contribution percentage. Column N is possible match they could have had to date. the total is the Total Actual Match to date.

See if that works for you. I just don't see a way to track it without separating out tiers of when they change contributions.

Ask Your Own Finance Question

Quick update.

Carter McBride
Carter McBride, Financial Advisor
Category: Finance
Satisfied Customers: 1,311
Experience: Adjunct Professor
Verified
Carter McBride and 87 other Finance Specialists are ready to help you
Ask your own question now
Customer reply replied 4 months ago
Hi Carter, the spreadsheet you attached did not have the breakdown as stated. However, I was finally able to create a formula that is what I was looking for. Using the IF plus the MIN functions.
Thank you for your help.
Was this answer helpful?

How JustAnswer works

step-image
Describe your issueThe assistant will guide you
step-image
Chat 1:1 with a financial professionalLicensed Experts are available 24/7
step-image
100% satisfaction guaranteeGet all the answers you need
Ask Carter McBride Your Own Question
Carter McBride
Carter McBride
Carter McBride, Financial Advisor
Category: Finance
Satisfied Customers: 1,311
1,311 Satisfied Customers
Experience: Adjunct Professor

Carter McBride is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

I really was impressed with the prompt response. Your expert was not only a tax expert, but a people expert!!! Her genuine and caring attitude came across in her response...

T.G.WMatteson, IL

I WON!!! I just wanted you to know that your original answer gave me the courage and confidence to go into yesterday's audit ready to fight.

BonnieChesnee, SC

Great service. Answered my complex tax question in detail and provided a lot of additional useful information for my specific situation.

JohnMinneapolis, MN

Excellent information, very quick reply. The experts really take the time to address your questions, it is well worth the fee, for the peace of mind they can provide you with.

OrvilleHesperia, California

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

< Previous | Next >

Meet the Experts:

Rakhi Vasavada

Rakhi Vasavada

Financial and Legal Consultant

2,609 satisfied customers

Graduated in law with Emphasis on Finance and have have been working in financial sector for over 12 Years

Megan C

Megan C

Certified Public Accountant (CPA)

2,222 satisfied customers

Licensed CPA, CFE, CMA, CGMA who teaches accounting courses at Master's Level

Manal Elkhoshkhany

Manal Elkhoshkhany

Tutor

1,933 satisfied customers

More than 5000 online tutoring sessions.

JKCPA

JKCPA

CPA

844 satisfied customers

Bachelors degree and CPA with Accounting experience.

Eric

Eric

Financial Manager

559 satisfied customers

Loan servicing, counseling and real estate expert. Foreclosure Expert and Financial Planner.

R. Klein, EA

R. Klein, EA

Accountant

480 satisfied customers

TurboTax Expert. QuickBooks Certified Pro Advisor

Dimitry K., Esq.

Dimitry K., Esq.

Attorney

332 satisfied customers

NASD Licensed Rep, 1997-2001, Business Attorney

< Previous | Next >

Related Finance Questions
I own a business (S-Corp) and last year had about $170K in
I own a business (S-Corp) and last year had about $170K in profit, and contributed about $30K to a SEP. For 2017, I'm planning to do the same, HOWEVER, I am also starting a traditional job in Dec in w… read more
Barbara
Barbara
Enrolled Agent, Paralegal
5,662 satisfied customers
I want more details on how I can maximize my deductions with
I want more details on how I can maximize my deductions with my sole proprietor business. I want to open a solo 401k for myself, and need more info on the calculations.… read more
Barbara
Barbara
Enrolled Agent, Paralegal
5,662 satisfied customers
Need help evaluating a job offer vs. the current earnings
Need help evaluating a job offer vs. the current earnings and potential at the pre-IPO company where I am today. What dollar value I need to show to the offering company to make the move worthwhile fi… read more
TaxAttorneytoHelp
TaxAttorneytoHelp
Owner
Doctoral Degree
2,542 satisfied customers
Have a 401k with a match. The match is up to 4% of the gross
Have a 401k with a match. The match is up to 4% of the gross wage. The employee earned $100k. They are over 50 so they contributed the max of 23k. So the match is 4000.00 Do they continue to pay the m… read more
PDtax
PDtax
Owner
Master\u0027s Degree
1 satisfied customers
My employer has a 401k plan and ESOP. I am 57 years old.
My employer has a 401k plan and ESOP. I am 57 years old. What is the maximum contribution limit for 2015? Is it 53K, or is it $59k for me? Also, Is there a requirement to contribute fully to each sour… read more
PDtax
PDtax
Owner
Master\u0027s Degree
1 satisfied customers
What 401k plan should I contribute to if my employer doesnt
What 401k plan should I contribute to if my employer doesn't match my contributions. I would like to retire when I am 55 ( I am 42 now). I can contribute $1375 per month which adds up to $17500 maximu… read more
NPVAdvisor
NPVAdvisor
CFP Licensee and Practitioner
Master's Degree
180 satisfied customers
I have an S-Corp and a Solo 401K. Ive been told that you
I have an S-Corp and a Solo 401K. I've been told that you cannot make just employer contributions if employee contributions are zero. You have to make employee contributions and then the employer has … read more
keeperumiami
keeperumiami
Sr Financial & Tax Consultant
Bachelor\u0027s Degree
1 satisfied customers
I know that I can contribute $17,000 a year into my 401K since
I know that I can contribute $17,000 a year into my 401K since I am over 50. Since I will become eligible for my companies 401K (no matching) in the 3rd QTR of this year is % limit to the amount taken… read more
DanielleCPA
DanielleCPA
Certified Public Accountant
Bachelor's Degree
76 satisfied customers
I am I 57 year old Single person I will make about $52,000
I am I 57 year old Single person I will make about $52,000 W2 reportrd income this year I already contributed $6,000 to my ROTH IRA Starting in July I will be eligible for my Companies 401k plan They … read more
Dave CPA
Dave CPA
Master's Degree
69 satisfied customers
Im a small business owner. I have a single-member LLC business
I'm a small business owner. I have a single-member LLC business (meaning I just file my business stuff on my personal taxes like a sole proprietor would) and I have one full-time employee. We have a 4… read more
Bill
Bill
Master's Degree
2,278 satisfied customers
I am 30 years old and trying to determine my savings needs
I am 30 years old and trying to determine my savings needs and my 401k percentages. I am not sure about how the employer contributions work. Can you please explain this to me as simply as possible. Co… read more
JKCPA
JKCPA
CPA
Bachelor's Degree
844 satisfied customers
I am trying to understand what the following means Company
I am trying to understand what the following means: Company X will make matching contributions in an amount equal to 100% of the first 4% of employee compensation. All matching contributions are 100% … read more
datalog
datalog
Vice President
Bachelor\u0027s Degree
81 satisfied customers
i make over 100k a year and my company matches dollar for dollar
i make over 100k a year and my company matches dollar for dollar up to 10% of my pay and i also want to contribute 10% of my salary . which brings me to a total contribution of 20,000 dollars in savin… read more
RD
RD
Self Employed
Master's Degree
1,164 satisfied customers
please show me how to setup my excel worksheet to do these
please show me how to setup my excel worksheet to do these functions or please setup an excel worksheet to do these functions and email to me: initial principal + xx% Bonus = plus annual compounded in… read more
Milan Vaishnav
Milan Vaishnav
Consulting Technical Analyst
Bachelor's Degree
536 satisfied customers
I am self employed with a single-member LLC - I want to set ...
I am self employed with a single-member LLC - I want to set up a self employed 401K plan with a 1 for 1 matching component. Is the matching rules the same as the profit sharing rules where the maximum… read more
LEV
LEV
Retired
Bachelor's Degree
14,896 satisfied customers
What is the average 401K employer match for the Retail ...
What is the average 401K employer match for the Retail Industry in the Northeast Region with Revenues of at least $500 million.39294.711602581… read more
LEV
LEV
Retired
Bachelor's Degree
14,896 satisfied customers
I am attempting to find data related to the percentage of ...
I am attempting to find data related to the percentage of 401k plans (in the U.S., or I can be more specific geographically, if necessary) in which the employer offers a matching contribution. Also, I… read more
Dave Z
Dave Z
Self-Employed
Bachelor
418 satisfied customers
My Employer is switching 401k plans, He pays 65% of the ...
My Employer is switching 401k plans, He pays 65% of the first 4% of my salary how does this compare with other 401k plans? Also do I have to reinvest into the new 401k plan or can I transfer that mone… read more
Wendy Reed
Wendy Reed
Master Tax Advisor Enrolled Agent
Master's Degree
60 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x