# Thread: Sliding Retention calculation Thanks: 0 Likes: 0

1. ## Sliding Retention calculation

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

 A B C D 2 Invoice Amount \$900,000 3 4 < \$200,000 10% \$20,000 5 >\$200,000 but <\$800,000 \$800,000 5% \$30,000 6 > \$800,000 2% \$2,000 7 Retention Total \$52,000 8 Formula Answer \$57,000
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  Reply With Quote

2. ## Re: Sliding Retention calculation

Try:

A B C D E F G H I J K
1 Invoice Amount \$ 900,000 Low Range High Range Percent Delta percent
\$ - \$ 200,000 10% 10% \$ 20,000
3 Retention Value \$ 52,000 \$ 200,000 \$ 800,000 5% -5% \$ 30,000
4 \$ 800,000 2% -3% \$ 2,000
Sheet1

Worksheet Formulas
Cell Formula
C3 =SUMPRODUCT(C1-E2:E4,--(C1>E2:E4),H2:H4)
H2 =G2-N(G1)
H3 =G3-N(G2)
H4 =G4-N(G3)
J2 =MIN(C1,F2)*G2
J3 =MEDIAN(C1-F2,F3-F2,0)*G3
J4 =MAX(0,C1-F3)*G4

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.  Reply With Quote

## User Tag List

\$200, \$800, 000, amount, retention 