Need help with formula evaluating progressive rebate against 2 criteria

[CJNed]

Hi - I'm trying to model what a tiered, progressive, rebate would look like against two different contracts (old/new) rebate percentages.  The new contract (higher rebate %) would benefit from the tier level earned on the old contract. 

It doesn't look like I can attach the file, so I've added a snip. 

I can calculate the total progressive rebate against the new contract with the following formula :
=SUMPRODUCT((E9<=$D$12:$D$19)*(E9>$C$12:$C$19)*(E9-$C$12:$C$19)*$I$12:$I$19)+ SUMPRODUCT(((E9>=$D$12:$D$19)*($D$12:$D$19-$C$12:$C$19))*$I$12:$I$19)

I can calculate the total progressive rebate against the old contracts with the following formula:
SUMPRODUCT((E7<=$D$12:$D$19)*(E7>$C$12:$C$19)*(E7-$C$12:$C$19)*$H$12:$H$19)+ SUMPRODUCT(((E7>=$D$12:$D$19)*($D$12:$D$19- $C$12:$C$19))*$H$12:$H$19)

What I can't figure out how to do is jump from one model to the other, without losing the tier and not overpaying on the tier that has both rebates.  My table calculates what I want, but I can't apply the table to a large volume of contracts over may years.  I need to simplify the table calculations into a forumla that calculates within a single cell.

[#2]

@CJNed As there are only 8 tiers, how about using "brute force-calculation" formulas to calculate rebates?  Specifically for cell L29:

=$I$12   * MAX( 0, MIN($B$12, $B$12-MAX(0, MIN($B$12, B29-0)), C29, C29+B29-0 ) )
 + $I$13 * MAX( 0, MIN($B$13, $B$13-MAX(0, MIN($B$13, B29-$D$12)), C29, C29+B29-$D$12 ) )
 + $I$14 * MAX( 0, MIN($B$14, $B$14-MAX(0, MIN($B$14, B29-$D$13)), C29, C29+B29-$D$13 ) )
 + $I$15 * MAX( 0, MIN($B$15, $B$15-MAX(0, MIN($B$15, B29-$D$14)), C29, C29+B29-$D$14 ) )
 + $I$16 * MAX( 0, MIN($B$16, $B$16-MAX(0, MIN($B$16, B29-$D$15)), C29, C29+B29-$D$15 ) )
 + $I$17 * MAX( 0, MIN($B$17, $B$17-MAX(0, MIN($B$17, B29-$D$16)), C29, C29+B29-$D$16 ) )
 + $I$18 * MAX( 0, MIN($B$18, $B$18-MAX(0, MIN($B$18, B29-$D$17)), C29, C29+B29-$D$17 ) )
 + $I$19 * MAX( 0, MIN($B$19, $B$19-MAX(0, MIN($B$19, B29-$D$18)), C29, C29+B29-$D$18 ) )

Copy the formula as needed.

The inner MAX and MIN functions are used to calculate the Old Contract Spend amounts by tier (the same calculations as in cells F12 through F19). The outer MIN functions imitate the calculations in cells G12 through G19; their arguments ensure that the New Contract Spend amounts by tier are no greater than:

1. the Tier Size (a better name than Tier Range);
2. the Tier Size minus the Old Contract Spend amount in that tier;
3. the entire New Contract Spend Amount; or
4. the sum of the Spend Amounts minus all the monies spent or spendable in lower tiers (so this could be a negative number, but the outer "MAX( 0, …" keeps that from being a problem).

Your sample calculations for Old Contract Rebates in 2023 and 2024 apparently do not follow the same calculation rules as in the upper block.  Especially note that the rebates in cells K30 and K31 have the same values, but are based on Old Contract Spends that differ by $500,000.  See the attached workbook for my calculations.

[#3 · Accepted solution]

@SnowMan55 alternative option:

=SUMPRODUCT($B$12:$B$19*(D30>=$D$12:$D$19)
             -$B$12:$B$19*(B30>=$D$12:$D$19)
             -(B30-$C$12:$C$19+1)*(B30<$D$12:$D$19)*(B30>=$C$12:$C$19)
             +(D30-$C$12:$C$19+1)*(D30>=$C$12:$C$19)*(D30<$D$12:$D$19),
            $I$12:$I$19)

line 1 add tier size if total > tier max

line 2 subtract tier size if Old > tier max

line 3 subtract portion left from Old on that tier

line 4 add portion from total on that tier

line 5 multiply by %

Note that I agree with @SnowMan55  that the numbers you have for the Old Contr. are not right.  My formula for that portion is 

=SUMPRODUCT($B$12:$B$19*(B29>=$D$12:$D$19)
             +(B29-$C$12:$C$19+1)*(B29<$D$12:$D$19)*(B29>=$C$12:$C$19),
            $H$12:$H$19)

similar in concept to the above.

And lots of kudos to @SnowMan55  for re-creating that sheet (although I tweaked a couple formulas I thought weren't done how intended)

Lastly I added a number 1 for the min on the first Tier for consistency and for making calculations easier (i.e. that -$C$12:$C$19+1 in my formulas) but could have used +$B$12:$B$19-$D$12:$D$19 instead.

BTW i realized it would be a huge help here to define those ranges with names so the updated formula (I only used in the 2025 part of the table is:

=SUMPRODUCT(TierSize*(J29>=TierMax)
             -TierSize*(H29>=TierMax)
             -(H29-TierMin+1)*(H29<TierMax)*(H29>=TierMin)+
             (J29-TierMin+1)*(J29>=TierMin)*(J29<TierMax),
            NewPercent)

[#4]

@SnowMan55 @mtarler

Thank you, both, for your help!! I was spinning on this for so long! Additionally, thank you for catching my error with the old contract calculation! @mtarler, I am using named ranges in my master file, I actually removed them so to share the formula since I couldn't figure out how to attach my sample file. :)

Thanks again!!