need a whole dollar calculation

[Ronald Roberts]

I have an application that needs to calculate an answer
in whole dollars. There are 2 values and sales tax used
in the calculation. The first value is course fee, which
is non-taxable. The second value is text fee and is taxable.

The problem:
A user will enter a new record. In this record they will
enter the course fee and the text fee. If the user checks
the tax status as taxable, the program will calculate the
sales tax. If the total cost is not in whole dollars, the
program will adjust the text fee up to 5 times until the
total cost is a whole dollar amount. About 95% of the time,
the program will find a whole dollar answer. What I’m looking
for is a calculation that will find a whole dollar answer 100%
of the time and stay within a reasonable range of the original
text fee, say plus or minus 5-10 cents.

The program cannot just drop the cents of the text fee or the
sales tax answer for 2 Reasons.
1) The correct taxes must be paid based on the text fee amount.
2) Rounding the text fee to whole dollars may still result in a
sales tax amount that Is not in whole dollars.


Any help is greatly appreciated

Ron

 

[John Vinson]

I have an application that needs to calculate an answer
in whole dollars. There are 2 values and sales tax used
in the calculation. The first value is course fee, which
is non-taxable. The second value is text fee and is taxable.

The problem:
A user will enter a new record. In this record they will
enter the course fee and the text fee. If the user checks
the tax status as taxable, the program will calculate the
sales tax. If the total cost is not in whole dollars, the
program will adjust the text fee up to 5 times until the
total cost is a whole dollar amount. About 95% of the time,
the program will find a whole dollar answer. What I’m looking
for is a calculation that will find a whole dollar answer 100%
of the time and stay within a reasonable range of the original
text fee, say plus or minus 5-10 cents.

The program cannot just drop the cents of the text fee or the
sales tax answer for 2 Reasons.
1) The correct taxes must be paid based on the text fee amount.
2) Rounding the text fee to whole dollars may still result in a
sales tax amount that Is not in whole dollars.

These are CONTRADICTORY REQUIREMENTS, unless you're saying that the
test fee must be adjusted in such a way that the fee plus the tax
comes to a whole dollar amount.

If that is what you want, then no iterative process is necessary -
simply divide the (whole dollar amount) fee by (1+tax rate) to get the
fee which will tax out to that amount.

John W. Vinson[MVP]

 

[John Nurick]

Hi Ronald,

As John Vinson says, the problem as stated is insoluble, but if you're
willing to live with rounding errors and allow the adjustment to be as big
as necessary rather than limit it to 5 or 10 cents, some simple algebra will
do the job:

Public Function AdjustedTextFee(CourseFee As Currency, TextFee As Currency,
_
TaxRate As Double, Taxable As Boolean) As Currency

Dim curTargetTotal As Currency

If Not Taxable Then
'no adjustment required
AdjustedTextFee = TextFee

Else
'Modify next statement if a different method of rounding is required
'(e.g. always round up rather than rounding to nearest whole dollar)

curTargetTotal = Round(CourseFee + TextFee * (1 + TaxRate), 0)

AdjustedTextFee = (curTargetTotal - CourseFee) / (1 + TaxRate)

End If

End Function

 

[Ronald Roberts]

Hi Ronald,

As John Vinson says, the problem as stated is insoluble, but if you're
willing to live with rounding errors and allow the adjustment to be as big
as necessary rather than limit it to 5 or 10 cents, some simple algebra will
do the job:

Public Function AdjustedTextFee(CourseFee As Currency, TextFee As Currency,
_
TaxRate As Double, Taxable As Boolean) As Currency

Dim curTargetTotal As Currency

If Not Taxable Then
'no adjustment required
AdjustedTextFee = TextFee

Else
'Modify next statement if a different method of rounding is required
'(e.g. always round up rather than rounding to nearest whole dollar)

curTargetTotal = Round(CourseFee + TextFee * (1 + TaxRate), 0)

AdjustedTextFee = (curTargetTotal - CourseFee) / (1 + TaxRate)

End If

End Function

Thanks to both.
I may have to live with the larger adjustment.

Ron