+ Reply to Thread
Results 1 to 4 of 4

Variable Commission Rates

  1. #1
    Registered User
    Join Date
    05-12-2004
    Posts
    4

    Variable Commission Rates

    I am trying to build a waterfall calculation for commissions. I can't get the partial commissions in months when he is at two rates. Can anyone help? See attached. I am looking for a formula to copy down and use for multiple sales people based on the same criteria.

    Greatly Appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Variable Commission Rates

    You can dispense with the various rows and have the total in one row if preferred... eg using your sample file add the following:

    Please Login or Register  to view this content.
    To get total commission say in row 21

    Please Login or Register  to view this content.
    If incremental monthly amounts preferred then subtract the prior YTD balance from the above:

    Please Login or Register  to view this content.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Variable Commission Rates

    If it's a standard "complex tiered" commission, meaning the first 80,000 is always at 15%, the next amount up to 150,000 is at 20% (but the original 80k is still only 15%), then this standard tiered formula will do that all in one cell, no charts needed:

    =SUMPRODUCT(--(B$14 > {0,80001,150001,400001,750001}), B$14 - {0,80001,150001,400001,750001}, {0.15,0.05,0.02,0.03,0.05})

    If it's a "basic tier" meaning the entire commision is calculated at the total amount (so a sales volume of 100,000 would generate a 20% commission on the whole amount), then this would do that, again in one cell:

    =B$14 * (LOOKUP(B$14, {0,80001,150001,400001,750001}, {0.15,0.20,0.22,0.25,0.30}))
    Last edited by JBeaucaire; 08-25-2009 at 02:46 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Variable Commission Rates

    If you prefer to keep the chart onscreen and easily editable, then I would lose the "MAX" column and just work from the "Tier Starts" range. This sample sheet shows both types of commission structures:
    Last edited by JBeaucaire; 08-26-2009 at 04:05 AM.

+ 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