Tiered Commission Calculation

**Rushmore** · 09-30-2012, 03:28 PM

I'm having trouble creating a commission calculation that is based on three criteria;1) recurring revenue 2) one time fees 3) bonuses.
The commission schedule is paid on a tiered rate for the recurring revenue + 1% of the one time fees. For example; $900,000 in recurring revenue and $25,000 in one time fees would be paid per the schedule below at ($840K*.025)+($60,000*.0275) + (25K*.01).

Recurring Revenue Schedule
$0-$840,000 = 2.5%
$840,001 - $1,050,000 = 2.75%
$1,050,001 - $4,200,000 = 3%
$4,200,000 = 3.5%

Thanks for the help!

**Ace_XL** · 09-30-2012, 04:13 PM

For recurring revenue use..

=SUMPRODUCT(--(A1>{0,840000,1050000,4200000}),--(A1-{0,840000,1050000,4200000}),{0.025,0.0025,0.0025,0.005})

Add the 1% one time fees and the bonuses to the sum calculated above

**Rushmore** · 09-30-2012, 06:58 PM

Genius! Thank you so much that worked!! However, can you explain the last portion of the formula to help me understand the logic behind {0.025,0.0025,0.0025,0.005})? If the commission tiers are 2.5%, 2.75%, 3% and 3.5% how did you come up with your string?

**Ace_XL** · 10-01-2012, 12:18 AM

The last portion of the SUMPRODUCT function takes into account differential (or incremental) rates to arrive at the tiered structure.

Hence, as in your example all of 900,000 is multiplied by 2.5% and then the excess of 60000 (900000 less 840000) is multiplied by the differential rate i.e. 0.25%. Then they are summed together.

The differentail rates can be negative too giving you a sliding scale structure.