need help with commission function

[RTB]

Hello all,
could really use some help with this

I need the function to look at B1 (discount%) and subract this amount,
if any, from each tier%, then calc. the fee amount.

Example:
A B C D
1 client1 0.25% 400,000 11,000
2 client2 0 300,000 9,000
3 client3 0.10% 450,000 13,050
etc.

In this example my Tiers are 3%,2.5%,2.25%,2%,1.75% and my dicount for
client #1 is 0.25%, client #2 is 0% and client #3 is 0.10%.

Given Tier1 is =<499,999.99, client #1 would pay 11,000
((3%-0.25%)*400,000), client #2 9,000(3%*300,000) and client #3
13,050((3%-0.10%)*450,000)

Code so far:

Function fee(Assets)
' quarterly fee
Const Tier1 = 0.03
Const Tier2 = 0.025
Const Tier3 = 0.0225
Const Tier4 = 0.02
Const Tier5 = 0.0175

' calculates annual management fee
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * Tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * Tier2 + (Assets - 1000000)
*
Tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 +
_
(Assets - 2000000) * Tier4
Case Is >= 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 +
_
3000000 * Tier4 + (Assets - 5000000) * Tier5
End Select
End Function

If anyone has a suggestion for the best methodology, please feelfree
to comment.

Thanks,
RTB

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(C1>{0,500000,1000000,2000000,5000000}),
(C1-{0,500000,1000000,2000000,5000000}),
{0.03,-0.005,-0.0025,-0.0025,-0.0025})-(B1*C1)

see

http://www.mcgimpsey.com/excel/variablerate.html

for more on how this works, and alternatives using tables.

 

[RTB]

JE McGimpsey,
Thank you for your reply! I also appreciate your friendly advice on
how to use newsgroups. I will apply suggestion and guideline #3!
Thanks again.
p.s. thanks for the links
Robert