+ Reply to Thread
Results 1 to 7 of 7

Calculating a tiered 401k Match

  1. #1
    JK
    Guest

    Calculating a tiered 401k Match

    Hello,

    I need to create a formula that will calculate a tiered 401k match based
    upon the amount being contributed.

    The match formula is 100% on the first 3% of an employee's contribution, and
    50% on the next 2% contributed.

    The columns I have are 1) Salary, 2)Percent contributed.

    Any and all help is greatly appreciated.

  2. #2
    JE McGimpsey
    Guest

    Re: Calculating a tiered 401k Match

    One way:

    A1: Salary
    B1: % contributed
    C1: =(MIN(B1,3%)/2 + MIN(B1,5%)/2) * A1


    In article <D6E0A010-047A-4448-B95D-ACA0910FA8A6@microsoft.com>,
    JK <JK@discussions.microsoft.com> wrote:

    > Hello,
    >
    > I need to create a formula that will calculate a tiered 401k match based
    > upon the amount being contributed.
    >
    > The match formula is 100% on the first 3% of an employee's contribution, and
    > 50% on the next 2% contributed.
    >
    > The columns I have are 1) Salary, 2)Percent contributed.
    >
    > Any and all help is greatly appreciated.


  3. #3
    JK
    Guest

    Re: Calculating a tiered 401k Match

    Thanks JE,

    But, when I input that formula it appears to simply give me 4% of the salary
    regardless of the percentage column. Any thoughts? Thanks!

    "JE McGimpsey" wrote:

    > One way:
    >
    > A1: Salary
    > B1: % contributed
    > C1: =(MIN(B1,3%)/2 + MIN(B1,5%)/2) * A1
    >
    >
    > In article <D6E0A010-047A-4448-B95D-ACA0910FA8A6@microsoft.com>,
    > JK <JK@discussions.microsoft.com> wrote:
    >
    > > Hello,
    > >
    > > I need to create a formula that will calculate a tiered 401k match based
    > > upon the amount being contributed.
    > >
    > > The match formula is 100% on the first 3% of an employee's contribution, and
    > > 50% on the next 2% contributed.
    > >
    > > The columns I have are 1) Salary, 2)Percent contributed.
    > >
    > > Any and all help is greatly appreciated.

    >


  4. #4
    JE McGimpsey
    Guest

    Re: Calculating a tiered 401k Match

    Did you fill in your percentages in column B?

    Here's my results:

    A B C
    1 100 0% 0.00
    2 100 1% 1.00
    3 100 2% 2.00
    4 100 3% 3.00
    5 100 3.2% 3.10
    6 100 4% 3.50
    7 100 5% 4.00
    6 100 6% 4.00


    In article <824C5187-C30F-4D6C-83B7-28D16CA15F76@microsoft.com>,
    JK <JK@discussions.microsoft.com> wrote:

    > Any thoughts?


  5. #5
    JK
    Guest

    Re: Calculating a tiered 401k Match

    Thanks, I had the formatting wrong. This is great, I appreciate your help!

    "JE McGimpsey" wrote:

    > Did you fill in your percentages in column B?
    >
    > Here's my results:
    >
    > A B C
    > 1 100 0% 0.00
    > 2 100 1% 1.00
    > 3 100 2% 2.00
    > 4 100 3% 3.00
    > 5 100 3.2% 3.10
    > 6 100 4% 3.50
    > 7 100 5% 4.00
    > 6 100 6% 4.00
    >
    >
    > In article <824C5187-C30F-4D6C-83B7-28D16CA15F76@microsoft.com>,
    > JK <JK@discussions.microsoft.com> wrote:
    >
    > > Any thoughts?

    >


  6. #6
    Registered User
    Join Date
    03-06-2020
    Location
    NY, NY
    MS-Off Ver
    2017
    Posts
    1

    Re: Calculating a tiered 401k Match

    formula for 33% of deferrals up to 6% of compensation

  7. #7
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,125

    Re: Calculating a tiered 401k Match

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1