473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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! :)
Nov 13 '05 #1
9 1431
Select or If Then Else Statements might work, but there might be an easier way
to do it.

Michael
Nov 13 '05 #2
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
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
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
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
"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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: qurat | last post by:
Alachisoft TierDeveloper is an O/R mapping and code generation tool that helps software developers do better, more creative and useful work by reducing redundant hand coding. TierDeveloper lets you...
3
by: NoEd | last post by:
I was talking to a couple guys at one of the hospitals I consult with, and they indicated their 401K is charging them $8 per quarter and .0875% based on the value in their accounts at the end of...
3
by: thomas.porschberg | last post by:
Hi, I want to read records from a database and export it in an arbitrary format. My idea was to feed a class with a String array fetched from the database and let this class fire SAX events as...
1
by: zeeshansohail | last post by:
I have two tables named as “COMPANY and BILL_DETAIL” with the following structure. BILL_DETAIL TABLE Name Type Nullable ------------- -------------- -------- ...
1
by: assgar | last post by:
Hi I need help solving a porblem. I have a form that displays a checkbox, service code, description and dropdown with fees on each row. The fee_money and unit array only returns a...
1
by: smacky61 | last post by:
How do I summarize a report by finding the totals fees collected for each event and the grand total of all fees. The name of the report is Registered Attendees Report.
0
by: Johannes Nix | last post by:
Hi, this might be of interest for people who are look for practical information on doing real-time signal processing, possibly using multiple CPUs, and wonder whether it's possible to use...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.