Sliding Retention calculation

[Chris2579]

Hi

I'm trying to create the following formula for a sliding retention calculation based on an invoice amount as follows:-

1. If the current amount invoiced is less than $200,000 retention value is 10%
2. If the current amount invoiced is between $200,000 and $800,000 the retention value is $200,000 @ 10% and the balance at 5%
3. If the current amount invoiced is over $800,000 the retention value is $200,000 @ 10% and $600,000 5% and the balance at 2%

For example

[TABLE="width: 285"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Invoice Amount[/TD]
[TD]$900,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]

<​

[/TD]
[TD]$200,000[/TD]
[TD]10%[/TD]
[TD]$20,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]

>$200,000 but <$800,000​

[/TD]
[TD]$800,000[/TD]
[TD]5%[/TD]
[TD]$30,000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]

>​

[/TD]
[TD]$800,000[/TD]
[TD]2%[/TD]
[TD]$2,000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Retention[/TD]
[TD]Total[/TD]
[TD]$52,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Formula Answer[/TD]
[TD][/TD]
[TD]$57,000[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]IF((C2)<=B4,C2*C4,IF(AND((C2)>B4,(C2)<=B5),(B4*C4)+(C2-B4)*C5,IF((C2)>B6,((B4*C4)+((C2-B4)*C5)+(C2-B6)*C6))))

Formula above is sort of there works for anything under $800,000 but over $800,000 doesn't give the right answer

Any Help much appreciated

Thanks








[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J	K
Invoice Amount	Low Range	High Range	Percent	Delta percent
Retention Value

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 900,000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$ -[/TD]
[TD="align: right"]$ 200,000[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$ 20,000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"] $ 52,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 200,000[/TD]
[TD="align: right"] $ 800,000[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]-5%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 30,000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 800,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]-3%[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2,000[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=SUMPRODUCT(C1-E2:E4,--(C1>E2:E4),H2:H4)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=G2-N(G1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=G3-N(G2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=G4-N(G3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=MIN(C1,F2)*G2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=MEDIAN(C1-F2,F3-F2,0)*G3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=MAX(0,C1-F3)*G4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Build your rate table in E1:H4. Columns E:G should be obvious, add column H, and put the H2 formula in and copy down. Then the C3 formula should give you the answer you want. The J2:J4 formulas I only put in as a check, but you can just sum them up, or combine them in a single formula if you want, and you won't need the H column.

Let us know if this works for you.