Variable Percentage Rate

[BobDobbs23]

Hello, all.

Here's the situation:

$0 - $180,000 investors get 75%
$180,000 - $450,000 investors get 50%
$450,000 - $900,000 investors get 0%
$900,000+ investors get 30%


Based on this link, I was able to come up with this formula, where D8 is gross revenue:

=SUMPRODUCT(--(D8>{0;180000;450000;900000}), (D8-{0;180000;450000;900000}), {0.75;-0.25;-0.75;0.3})

For example, if you have $250,000 gross revenue, investors receive $170,000.


Where this all breaks down is after $450,000. Any ideas?

All help will be greatly appreciated.

Thanks much.

 

[CWatts]

I'm having difficulty with your example given I don't have a background in finance, however, based on your paragraph of parameters if "$180,000 - $450,000 investors get 50%" then wouldn't a $250,000 get $125,000?

Based on that logic, I used this formula:

=F10*LOOKUP(F10,{0,180000,450000,900000},{0.75,0.5,0,0.3})

It may not work for you, but perhaps it will get you started and you can modify it to suit your needs.

 

[BobDobbs23]

Hey, CW.

Thanks for the suggestion, but investors get 50% of everything after the first $180,000 and up to $450,000. Up to $180,000, they get 75%.


For example:

$180,000 x .75 = $135,000
$70,000 x .5 = $35,000

$135,000 + $35,000 = $170,000

 

[BobDobbs23]

Here's the solution:

=SUMPRODUCT(--(D6>{0;18;45;90}*10000), D6-{0;18;45;90}*10000, {0.75;-0.25;-0.5;0.3})