R
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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!