Tiered Rebate

Hello Excel Forum Community,

I need help creating a workbook that will calculate a rebate whose structure contains 6 tiers after the user inputs aggregate spend. See example below. Your help is GREATLY appreciated!

Aggregate Spend / Percentages Not Cumulative
<$100,000 / 0%
$100,001 - $1,000,000 / 1%
$1,000,001 - $2,500,000 / 2%
$2,500,001 - $5,000,000 / 3%
$5,000,001 - $10,000,000 / 4%
≥ $10,000,001 / 5%

You can do a straight VLOOKUP in Col A to bring back the rebate percentage in Col B, and do whatever multiplication you want from there.
You'll need to change the typical FALSE to TRUE, and format your lookup results as a percentage for clarity.

Col A:
1
1,000,001
2,500,001
5,000,001
10,000,001

Col B
0%
1%
2%
3%
4%
5%

Pete

delete post

Originally Posted by jomaor1

Hi,

Follow attachment.

Cheers!

This solution helps calculate the maximum rebate threshold only and ignores the intermediary tiers that make this tricky. For example, if I have $2,000,000 in spend, logic would be as follows:

$1,000,000 * 1% (Tier 1) + $1,000,000 * 2% (Tier 2) = $10,000 + $40,000 = $50,000