Forum Discussion

mreakus's avatar
mreakus
Copper Contributor
Nov 23, 2021

Tiered Formula Calculation

I am having a hard time envisioning how to prepare a formula to calculate the net earnings for a tiered calculation. The revenue is 2.5% of the closed business. Then two separate sides split the 2.5% after an override takes place. 

 

As a simple example, if someone closes $2,000,000 in business. The revenue would be $50,000 ($2,000,000 X 2.5%). From this, there would be a split between two parties 50% and 50%. 

 

The problem is we want to create a tiered schedule where the split changes as the closed business increases.

 

Below is the projected tier:

0-$2,000,000 the person earns 50% of the commissions. 

$2,000,001-$4,000,000 - person earns 60% of net commissions.

$4,000,001 and up - person earns 70% of net commissions. 

 

Any help on creating a spreadsheet where I can simply adjust the closed business to calculate the net revenue on the splits as it increases?

3 Replies

    • mreakus's avatar
      mreakus
      Copper Contributor

      Weird. When I do the real math I believe I get a revenue stream of $107,500. I'm not sure what the delta is between your formula and my manual calculation.

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        mreakus 

        =IF(D4>4000000,(D4-4000000)*0.7+2000000*0.6+2000000*0.5,

        IF(AND(D4<=4000000,D4>2000000),(D4-2000000)*0.6+(2000000)*0.5,

        IF(D4<=2000000,D4*0.5)))*0.025

         

        Unfortunately i didn't understand what you want to do. Above formula is what you are looking for i suppose.

Resources