Processing Fees based on Percentage

[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! :)

[Lumpierbritches]

Select or If Then Else Statements might work, but there might be an easier way
to do it.

Michael

[Stewart Allen]

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.8721 6$IQ4.34610@att bi_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! :)

[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.wav e.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.8721 6$IQ4.34610@att bi_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! :)

[rkc]

"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.8721 6$IQ4.34610@att bi_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.

[Stewart Allen]

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.8534 5$MB3.60308@att bi_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.wav e.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.8721 6$IQ4.34610@att bi_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! :)

[Bob Quintal]

"MX1" <mx*@mx1.abc> wrote in
news:nGCJc.8534 5$MB3.60308@att bi_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.wav e.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.8721 6$IQ4.34610@att bi_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

[MX1]

Thanks. I will give this a shot.

"Bob Quintal" <bq******@gener ation.net> wrote in message
news:Xn******** **************@ 66.150.105.50.. .

"MX1" <mx*@mx1.abc> wrote in
news:nGCJc.8534 5$MB3.60308@att bi_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.wav e.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.8721 6$IQ4.34610@att bi_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

[MX1]

Thanks. I'll give this a shot on Monday.
"Stewart Allen" <sa****@NOT.wav e.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.8534 5$MB3.60308@att bi_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.wav e.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.8721 6$IQ4.34610@att bi_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! :)
>
>

[David W. Fenton]

"rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote in
news:bV******** **********@twis ter.nyroc.rr.co m:

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