need help w/ commission function

[robert burger]

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]

See one reply in response to your post in m.p.e.worksheet.functions.

Please don't post the same question in multiple groups. It only serves
to fragment your answers, and potentially wastes the time of those
replying to a question that has already been answered.

For tips on using these newsgroups effectively, see

http://cpearson.com/excel/newposte.htm