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
Answered in 15 minutes by:
3/21/2018
Carter McBride, Financial Advisor
Category: Finance
Satisfied Customers: 1,041
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 1 month 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 1 month 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, Financial Advisor
Category: Finance
Satisfied Customers: 1,041
Experience: Adjunct Professor
Verified
Carter McBride and 87 other Finance Specialists are ready to help you
Customer reply replied 1 month 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?
Ask Carter McBride Your Own Question
Carter McBride, Financial Advisor
Category: Finance
Satisfied Customers: 1,041
1,041 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

Financial and Legal Consultant

2,608 satisfied customers

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

Megan C

Certified Public Accountant (CPA)

2,218 satisfied customers

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

Manal Elkhoshkhany

Tutor

1,920 satisfied customers

More than 5000 online tutoring sessions.

JKCPA

CPA

844 satisfied customers

Bachelors degree and CPA with Accounting experience.

Eric

Financial Manager

559 satisfied customers

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

R. Klein, EA

Accountant

480 satisfied customers

TurboTax Expert. QuickBooks Certified Pro Advisor

Dimitry K., Esq.

Attorney

332 satisfied customers

NASD Licensed Rep, 1997-2001, Business Attorney

< Previous | Next >

Related Finance Questions
Bryan, in continuation to my previous questions about LLC, I
Hello Bryan, in continuation to my previous questions about LLC, I would like to figure out if it is really necessary to do an LLC for a rental home. I believe the liability is less with an LLC. For e… read more
Bryan Mullis
Managing Partner
Masters of Accountancy
27 satisfied customers
California C corporation went out of business in 2015 but
California C corporation went out of business in 2015 but neglected to dissolve with Secretary of State. As a result, minimum corporation tax is due California for 2016 and 2017. Non-stockholder, form… read more
NPVAdvisor
CFP Licensee and Practitioner
Master's Degree
180 satisfied customers
I recently started my own LLC (single-member) and I have a
I recently started my own LLC (single-member) and I have a consulting business with no real write-off's. I was going to lease a new vehicle in hopes to create a write-off, and was wondering if I shoul… read more
LEV
Retired
Bachelor's Degree
14,896 satisfied customers
My question is I am 150000 in debt can you tell me what
my question is I am 150000 in debt can you tell me what would be the best way to get out of my hardship? … read more
ABC Accounting Group
Bachelor\u0027s Degree
828 satisfied customers
If I pay for a condo which I intend to rent with funds from
If I pay for a condo which I intend to rent with funds from home equity loan, is the interest on the home equity loan deductible as expense on Schedule E? … read more
abci168
Principal
27 satisfied customers
I am in the process of shutting down my company and filing
I am in the process of shutting down my company and filing for corporate bankruptcy. It will take a few months to shut down. Can I pay myself \$10,000 throw the process or once I file will I have to re… read more
NPVAdvisor
CFP Licensee and Practitioner
Master's Degree
180 satisfied customers
I have been advised by financial experts to make a Town home
Hello I have been advised by financial experts to make a Town home rental property as an LLC so their is safety in terms of liability involved while renting the house out. The rent will be \$3000. Howe… read more
Bryan Mullis
Managing Partner
Masters of Accountancy
27 satisfied customers
I pay myself from my company primarily through S-Corporation
I pay myself from my company primarily through S-Corporation payments. I record the payments on my tax returns. In the past sometimes I pay myself through Quicken and I deduct the taxes. I pay off all… read more
LEV
Retired
Bachelor's Degree
14,896 satisfied customers
Lane. Still following up on the SS exemption question—I a
Hi Lane. Still following up on the SS exemption question—I a 501(c)3 church automatically exempt from SS withholding for it's employees or does each individual employee need to opt in or out? Is there… read more
NPVAdvisor
CFP Licensee and Practitioner
Master's Degree
180 satisfied customers
I am a member of a family irrevocable trust originated by my
I am a member of a family irrevocable trust originated by my parents. They are both deceased and left oldest son as trustee. There are total of 8 children who were original beneficiaries.My question i… read more
Bryan Mullis
Managing Partner
Masters of Accountancy
27 satisfied customers
We are Canadians living in US w/perm residence as spouse was
We are Canadians living in US w/perm residence as spouse was transferred to US for work ...after working for company in Cayman Islands for 8 years. We have funds in Pension Plan in Cayman and have rec… read more
TaxRobin
Vocational, Technical or Trade School
1 satisfied customers
I NEED HELP IN GETTING A PENSION LOAN CAN YOU HELP ME PLEASE
I NEED HELP IN GETTING A PENSION LOAN CAN YOU HELP ME PLEASE ?? … read more
Mark Anderson
Attorney
Doctoral Degree
10 satisfied customers
In continuation to my previous question, what if there are 2
Hello, in continuation to my previous question, what if there are 2 members within a family under the LLC, such as husband/wife or father/son. can the LLC be included in the tax returns of both partne… read more
Bryan Mullis
Managing Partner
Masters of Accountancy
27 satisfied customers
My tax preparer who had done my taxes for nearly ten years
my tax preparer who had done my taxes for nearly ten years has made an error which the its is charging me for. I have not yet approached the person. Are they liable for this error or even if I have be… read more
Carter McBride
Financial Advisor
1,041 satisfied customers
What are the consequences of retiring at age 60 or 61 after
Second opinion] What are the consequences of retiring at age 60 or 61 after working at the same job continuously for 11 years? … read more
LEV
Retired
Bachelor's Degree
14,896 satisfied customers
Not duplicate -- I need to book the following scenario in
Not duplicate -- Hello, I need to book the following scenario in Quickbooks: Customer Invoiced for \$1,000. They paid us \$1,000 using pay pal but we only received \$970.70 after fees. How do I apply thi… read more
Marcia Mitrano
MBA
62 satisfied customers
We are a LLC based in DE, owned by two non-resident aliens.
We are a LLC based in DE, owned by two non-resident aliens. We sell services (no physical product) to US and International customers. As LLC, we tax our US-based income, not non-US income. (We then ta… read more
Barbara
Enrolled Agent, Paralegal
4,943 satisfied customers
I am in the process of saving plan for first home purchase
Hi I am in the process of saving plan for first home purchase with a known company.They have advised that if I can prove that I demonstrate some saving in 3 months time, they will provide a pre-loan a… read more
Mark Anderson
Attorney
Doctoral Degree
10 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