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
+ 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 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
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" 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 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" 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" 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 "MX1" 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" 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" 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" 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" 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" 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" 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 Thanks. I will give this a shot. "Bob Quintal" wrote in message news:Xn**********************@66.150.105.50... "MX1" 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" 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" 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 Thanks. I'll give this a shot on Monday. "Stewart Allen" 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" 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" 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" 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" 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.