Calculating Quarterly Royalty Amounts on Cumulative Annual Tiered Royalty Rates

[edarce]

A company gets paid, on product sales one quarter in arrears, the following royalties:

Less than $100M in annual sales: 4.7%
From $100M to $200M: 6.6%
From $200M to $400M: 7.5%
From $400M to $1,500M: 9.4%
More than $1,500M: 9.3%

Sales in a given year are:
Q1: $43M
Q2: $47M
Q3: $55M
Q4: $62M
Total = $207M

Thus, the company's Q4 royalties royalties are calculated like this:
Total sales in first three quarters is 145, so 45 of the Q3 sales will be calculated at the next tier
So, $10M x 4.7% = 0.47, plus $45M x 6.6% = 2.97
So, the company receives royalties of $3.44M (=0.47 + 2.97) in Q4.

Now, how in the heck do you structure a group of Excel functions to calculate that???

Just so we're clear, in Q1 of the following year, the company would report royalties of:
($55M x 6.6%) + ($7M x 7.5%) = $4.155M

Further, in Q2 of the following year, the royalty tiers reset and the royalty is simply calculated from the sales in the previous quarter.

Really hoping someone can come through and solve this for me!
Many, many thanks!!

(P.S. - This is a real company and all the figures are accurate)

 

[pplstuff]

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"]207[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]0.047[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4.7[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]0.066[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]6.6[/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[TD="align: right"]0.075[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.525[/TD]
[/TR]
[TR]
[TD="align: right"]1500[/TD]
[TD="align: right"]0.094[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1500[/TD]
[TD="align: right"]0.093[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

c2:=MIN(C1,A2)
c3:=MIN(C1-C2,A3-A2)
c4:=MIN(C1-SUM(C2:C3),A4-A3)
c5:=MIN(C1-SUM(C2:C4),A5-A4)
c6:=C1-SUM(C2:C5)

d2:=b2*c2
(fill down)

You can break it into quarters however you please, but this might give you some ideas.