By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

Processing Fees based on Percentage

P: n/a
MX1
Hi,

I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a field
(e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based on a
tiered structure of business rules. For example, the rules might be:

On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees

Can anyone think of the most strategic way to do this on a single form in
order to come up with a single fee value? I'd appreciate any input.
Thanks! :)
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Select or If Then Else Statements might work, but there might be an easier way
to do it.

Michael
Nov 13 '05 #2

P: n/a
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example, you will end up
getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25
You might want to consider a stepped fee structure where they pay 1% for the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25% Total
$260.00 $2.60 $0.00 $0.00 $2.60
$440.00 $3.00 $0.70 $0.00 $3.70
$600.00 $3.00 $1.00 $0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02...
Hi,

I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a field (e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based on a
tiered structure of business rules. For example, the rules might be:

On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees

Can anyone think of the most strategic way to do this on a single form in
order to come up with a single fee value? I'd appreciate any input.
Thanks! :)

Nov 13 '05 #3

P: n/a
MX1
Thanks Stewart.

Actually, the way it works is the whole amount gets charged a fee. So for
the $1000 example, I'd need the system to loop through and see that the
first $300 gets 1% which is $3. Then we have $700 remaining. The next tier
up to $500 is .5% so that would be $200 at .5% which is $1. Now we have
$500 remaining which hasn't been billed yet. That falls into the last tier
because we've already billed o the first $500. That last tier is .25% which
gets $1.25. Total fees for this customer is $5.25

Now, let's say customer B has $450. He gets same billing amount for the
first portion up to $300 which is $3. Now there is $150 remaining which
falls in the secondary tier of .5% so that is $150 x .5% which yields .75
cents. Total feeds for this customer is $3.75.

Basically, this is a pretty neat problem to crack. The logic is not bad. I
just don't know how to code this into a nested IIF statement. Not sure how
to tell the computer to move from one tier to the next. Any thoughts or an
example on how I could do this would be greatly appreciated.

Thanks a bunch!

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example, you will end up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25 You might want to consider a stepped fee structure where they pay 1% for the first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25% Total
$260.00 $2.60 $0.00 $0.00 $2.60
$440.00 $3.00 $0.70 $0.00 $3.70
$600.00 $3.00 $1.00 $0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message

news:JMAJc.87216$IQ4.34610@attbi_s02...
Hi,

I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a

field
(e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based on a tiered structure of business rules. For example, the rules might be:

On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees

Can anyone think of the most strategic way to do this on a single form in order to come up with a single fee value? I'd appreciate any input.
Thanks! :)


Nov 13 '05 #4

P: n/a
rkc

"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02...
Hi,

I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a field (e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based on a
tiered structure of business rules. For example, the rules might be:

On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees

Can anyone think of the most strategic way to do this on a single form in
order to come up with a single fee value? I'd appreciate any input.
Thanks! :)


If you were to store your 'business rules' in a table you could probably
use a query to calculate and return a fee amount.

Nov 13 '05 #5

P: n/a
Try (watch wrapping)
Fee:
CCur(IIf([Value]<=300,[Value]*0.01,IIf([Value]<=500,(([Value]-300)*0.005)+3,
(([Value]-500)*0.0025)+4)))
This is taken from a query and will work inside a text box on a form and
report.

The IIf function is a bit of resource hog especially when nexted. Here's a
function which does the same thing

Function Fee(Optional curIn As Currency) As Currency
Dim curTemp As Currency
Select Case curIn
Case 0
curTemp = 0
Case Is <= 300
curTemp = curIn * 0.01
Case Is <= 500
curTemp = ((curIn - 300) * 0.005) + 3
Case Is > 500
curTemp = ((curIn - 500) * 0.0025) + 4
End Select
Fee = curTemp
End Function

Stewart
"MX1" <mx*@mx1.abc> wrote in message news:nGCJc.85345$MB3.60308@attbi_s04...
Thanks Stewart.

Actually, the way it works is the whole amount gets charged a fee. So for
the $1000 example, I'd need the system to loop through and see that the
first $300 gets 1% which is $3. Then we have $700 remaining. The next tier up to $500 is .5% so that would be $200 at .5% which is $1. Now we have
$500 remaining which hasn't been billed yet. That falls into the last tier because we've already billed o the first $500. That last tier is .25% which gets $1.25. Total fees for this customer is $5.25

Now, let's say customer B has $450. He gets same billing amount for the
first portion up to $300 which is $3. Now there is $150 remaining which
falls in the secondary tier of .5% so that is $150 x .5% which yields .75
cents. Total feeds for this customer is $3.75.

Basically, this is a pretty neat problem to crack. The logic is not bad. I just don't know how to code this into a nested IIF statement. Not sure how to tell the computer to move from one tier to the next. Any thoughts or an example on how I could do this would be greatly appreciated.

Thanks a bunch!

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example, you will end
up
getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25
You might want to consider a stepped fee structure where they pay 1% for

the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25% Total
$260.00 $2.60 $0.00 $0.00 $2.60
$440.00 $3.00 $0.70 $0.00 $3.70
$600.00 $3.00 $1.00 $0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message

news:JMAJc.87216$IQ4.34610@attbi_s02... Hi,

I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a

field
(e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based
on a tiered structure of business rules. For example, the rules might be:

On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees

Can anyone think of the most strategic way to do this on a single form in order to come up with a single fee value? I'd appreciate any input.
Thanks! :)



Nov 13 '05 #6

P: n/a
"MX1" <mx*@mx1.abc> wrote in
news:nGCJc.85345$MB3.60308@attbi_s04:
Thanks Stewart.

Actually, the way it works is the whole amount gets charged a
fee. So for the $1000 example, I'd need the system to loop
through and see that the first $300 gets 1% which is $3. Then
we have $700 remaining. The next tier up to $500 is .5% so
that would be $200 at .5% which is $1. Now we have $500
remaining which hasn't been billed yet. That falls into the
last tier because we've already billed o the first $500. That
last tier is .25% which gets $1.25. Total fees for this
customer is $5.25

Now, let's say customer B has $450. He gets same billing
amount for the first portion up to $300 which is $3. Now
there is $150 remaining which falls in the secondary tier of
.5% so that is $150 x .5% which yields .75 cents. Total feeds
for this customer is $3.75.

Basically, this is a pretty neat problem to crack. The logic
is not bad. I just don't know how to code this into a nested
IIF statement. Not sure how to tell the computer to move from
one tier to the next. Any thoughts or an example on how I
could do this would be greatly appreciated.

Thanks a bunch!

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example,
you will end

up
getting less in fees if they pay more e.g. 1% of 300 = 3,
.25% of 500 =

1.25
You might want to consider a stepped fee structure where they
pay 1% for

the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25%
Total
$260.00 $2.60 $0.00 $0.00
$2.60 $440.00 $3.00 $0.70 $0.00
$3.70 $600.00 $3.00 $1.00
$0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message

news:JMAJc.87216$IQ4.34610@attbi_s02...
> Hi,
>
> I've got an invoice form that has a bunch of business rules
> behind it. Basically, I'm trying to figure out how to enter
> a single number in a

field
> (e.g. $1000) and then have the form do a bunch of math on
> that number. Here's the tough part. I need the form to
> breakdown the number based on a > tiered structure of business rules. For example, the rules
> might be:
>
> On anything up to $300 take 1% in fees
> Anything over $300 and less than $500 take .5% in fees
> Anything $500 and over, take .25% in fees
>
> Can anyone think of the most strategic way to do this on a
> single form in > order to come up with a single fee value? I'd appreciate
> any input. Thanks! :)
>
>


What you need to do is build a table with your fee scale in it,
sorted DESCENDING.

CAP_Amount basefee MarginalRate
99999999 4.00 .0025
500 3.00 .005
300 0.00 .01

Now you can get the values by querying the TOP 1 row, where
CAP_Amount < invoice_Amount, and do ONE calculation and get the
correct fee:

BaseFee + (Invoice_Amount - CAP_Amount) * marginalRate.

You could also use separate dLookup()s to return the three values.

Bob Quintal

Nov 13 '05 #7

P: n/a
MX1
Thanks. I will give this a shot.

"Bob Quintal" <bq******@generation.net> wrote in message
news:Xn**********************@66.150.105.50...
"MX1" <mx*@mx1.abc> wrote in
news:nGCJc.85345$MB3.60308@attbi_s04:
Thanks Stewart.

Actually, the way it works is the whole amount gets charged a
fee. So for the $1000 example, I'd need the system to loop
through and see that the first $300 gets 1% which is $3. Then
we have $700 remaining. The next tier up to $500 is .5% so
that would be $200 at .5% which is $1. Now we have $500
remaining which hasn't been billed yet. That falls into the
last tier because we've already billed o the first $500. That
last tier is .25% which gets $1.25. Total fees for this
customer is $5.25

Now, let's say customer B has $450. He gets same billing
amount for the first portion up to $300 which is $3. Now
there is $150 remaining which falls in the secondary tier of
.5% so that is $150 x .5% which yields .75 cents. Total feeds
for this customer is $3.75.

Basically, this is a pretty neat problem to crack. The logic
is not bad. I just don't know how to code this into a nested
IIF statement. Not sure how to tell the computer to move from
one tier to the next. Any thoughts or an example on how I
could do this would be greatly appreciated.

Thanks a bunch!

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example,
you will end

up
getting less in fees if they pay more e.g. 1% of 300 = 3,
.25% of 500 =

1.25
You might want to consider a stepped fee structure where they
pay 1% for

the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25%
Total
$260.00 $2.60 $0.00 $0.00
$2.60 $440.00 $3.00 $0.70 $0.00
$3.70 $600.00 $3.00 $1.00
$0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message

news:JMAJc.87216$IQ4.34610@attbi_s02...
> Hi,
>
> I've got an invoice form that has a bunch of business rules
> behind it. Basically, I'm trying to figure out how to enter
> a single number in a
field
> (e.g. $1000) and then have the form do a bunch of math on
> that number. Here's the tough part. I need the form to
> breakdown the number based on

a
> tiered structure of business rules. For example, the rules
> might be:
>
> On anything up to $300 take 1% in fees
> Anything over $300 and less than $500 take .5% in fees
> Anything $500 and over, take .25% in fees
>
> Can anyone think of the most strategic way to do this on a
> single form

in
> order to come up with a single fee value? I'd appreciate
> any input. Thanks! :)
>
>


What you need to do is build a table with your fee scale in it,
sorted DESCENDING.

CAP_Amount basefee MarginalRate
99999999 4.00 .0025
500 3.00 .005
300 0.00 .01

Now you can get the values by querying the TOP 1 row, where
CAP_Amount < invoice_Amount, and do ONE calculation and get the
correct fee:

BaseFee + (Invoice_Amount - CAP_Amount) * marginalRate.

You could also use separate dLookup()s to return the three values.

Bob Quintal

Nov 13 '05 #8

P: n/a
MX1
Thanks. I'll give this a shot on Monday.
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
Try (watch wrapping)
Fee:
CCur(IIf([Value]<=300,[Value]*0.01,IIf([Value]<=500,(([Value]-300)*0.005)+3, (([Value]-500)*0.0025)+4)))
This is taken from a query and will work inside a text box on a form and
report.

The IIf function is a bit of resource hog especially when nexted. Here's a
function which does the same thing

Function Fee(Optional curIn As Currency) As Currency
Dim curTemp As Currency
Select Case curIn
Case 0
curTemp = 0
Case Is <= 300
curTemp = curIn * 0.01
Case Is <= 500
curTemp = ((curIn - 300) * 0.005) + 3
Case Is > 500
curTemp = ((curIn - 500) * 0.0025) + 4
End Select
Fee = curTemp
End Function

Stewart
"MX1" <mx*@mx1.abc> wrote in message news:nGCJc.85345$MB3.60308@attbi_s04...
Thanks Stewart.

Actually, the way it works is the whole amount gets charged a fee. So for the $1000 example, I'd need the system to loop through and see that the
first $300 gets 1% which is $3. Then we have $700 remaining. The next

tier
up to $500 is .5% so that would be $200 at .5% which is $1. Now we have
$500 remaining which hasn't been billed yet. That falls into the last

tier
because we've already billed o the first $500. That last tier is .25%

which
gets $1.25. Total fees for this customer is $5.25

Now, let's say customer B has $450. He gets same billing amount for the
first portion up to $300 which is $3. Now there is $150 remaining which
falls in the secondary tier of .5% so that is $150 x .5% which yields ..75 cents. Total feeds for this customer is $3.75.

Basically, this is a pretty neat problem to crack. The logic is not bad. I
just don't know how to code this into a nested IIF statement. Not sure how
to tell the computer to move from one tier to the next. Any thoughts or

an
example on how I could do this would be greatly appreciated.

Thanks a bunch!

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz...
There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function

Although that fee structure you'd given might be an example, you will

end
up
getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25
You might want to consider a stepped fee structure where they pay 1%
for the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25% Total
$260.00 $2.60 $0.00 $0.00 $2.60
$440.00 $3.00 $0.70 $0.00 $3.70
$600.00 $3.00 $1.00 $0.25 $4.25

Stewart
"MX1" <mx*@mx1.abc> wrote in message

news:JMAJc.87216$IQ4.34610@attbi_s02...
> Hi,
>
> I've got an invoice form that has a bunch of business rules behind
it. > Basically, I'm trying to figure out how to enter a single number in a field
> (e.g. $1000) and then have the form do a bunch of math on that number. > Here's the tough part. I need the form to breakdown the number based on
a
> tiered structure of business rules. For example, the rules might

be: >
> On anything up to $300 take 1% in fees
> Anything over $300 and less than $500 take .5% in fees
> Anything $500 and over, take .25% in fees
>
> Can anyone think of the most strategic way to do this on a single

form in
> order to come up with a single fee value? I'd appreciate any input.
> Thanks! :)
>
>



Nov 13 '05 #9

P: n/a
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in
news:bV******************@twister.nyroc.rr.com:
If you were to store your 'business rules' in a table you could
probably use a query to calculate and return a fee amount.


And it's exactly the kind of process that benefits from being
wrapped in a class module.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.