Calculation of pricing where both value incl. as excl. tax are whole dollars

[AA Arens]

For a quotation I need to mention the price in USD, that is including
10% tax.
Both this value and the value without tax should be values that has no
dollar cents as rest value, like USD 30,00.

I am looking for a small tool that gives me the nearest price value
incl. tax above the value that I type in and met above condition.

Sample:
I type: 1300
Value incl tax 1320
Value excl. 1200


Excel 2003 / VB code ok.

 

[joeu2004]

For a quotation I need to mention the price in USD,
that is including 10% tax.
Both this value and the value without tax should be
values that has no dollar cents as rest value, like
USD 30,00.
I am looking for a small tool that gives me the nearest
price value incl. tax above the value that I type in
and met above condition.
Sample:
I type: 1300
Value incl tax 1320
Value excl. 1200

Suppose B1 contains the tax rate (10%), and B2 contains the minimum net
amount (1300). Then:

B3,gross(1200): =ROUNDUP(B2/(1+B1),-2)
B4,tax [*]: =B3*B1
B5,adjusted net(1320): =B3+B4

But that begs the question: why 1200 and 1320 in this example instead of
1190 and 119?

ROUNDUP(...,-1) could be used with a tax rate that is the multiple of 10%.

Conversely, if the tax rate were 13.3%, we require ROUNDUP(...,-3) instead
of ROUNDUP(...,-2).

Are you looking for a way to automatically account for the precision of the
tax rate?

If so, there might be a clever Excel formulation. But I would resort to
VBA. For example:

B3,gross(1200): =grossAmt(B2,B1)

where grossAmt is the following VBA user-defined function:

Function grossAmt(guess As Double, ByVal myRate As Double) As Double

Const minRUP As Long = 2 ' 0 or more
Dim s As String
Dim rup As Long, i As Long, c As String * 1

myRate = Round(myRate, 17)
If myRate <= 0 Then
grossAmt = WorksheetFunction.RoundUp(guess, -minRUP)
Else
' determine minimum round-up significance
s = Format(myRate, "0.000000000000000%")
rup = 17
For i = Len(s) - 1 To 1 Step -1
c = Mid(s, i, 1)
If c <> "." Then
If c <> "0" Then Exit For
rup = rup - 1
If rup <= minRUP Then Exit For
End If
Next
grossAmt = WorksheetFunction.RoundUp(guess / (1 + myRate), -rup)
End If
End Function


-----
[*] I would feel better writing =ROUND(B3*B1,0) in B4. I use ROUND to
eliminate numerical "error" due to the fact that Excel cannot represent most
non-integers like percentages in B1 exactly. It seems unnecessary with
these examples. But I wonder if there are examples where ROUND is indeed
needed.

 

[joeu2004]

PS....

Suppose B1 contains the tax rate (10%), and B2 contains the minimum net
amount (1300). Then:
B3,gross(1200): =ROUNDUP(B2/(1+B1),-2)
B4,tax [*]: =B3*B1
B5,adjusted net(1320): =B3+B4

Although that is correct, since you did not ask for tax (B4), perhaps I
should write the last two formulas as follows, which should have the same
results:

B4,tax: =B5-B3
B5,adjusted net(1320) [*]: =B3*(1+B1)

That way, you can eliminate B4 if you wish.


-----
[*] As before, I would feel better writing =ROUND(B3*(1+B1),0) in B5.
Again, we use ROUND to eliminate numerical "error" due to the fact that
Excel cannot represent most non-integers like percentages in B1 exactly. It
does not seem necessary with these examples. But there might be examples
where ROUND is indeed needed.

 

[Bart van Dongen]

PS....

Suppose B1 contains the tax rate (10%), and B2 contains the minimum net
amount (1300).  Then:
B3,gross(1200):         =ROUNDUP(B2/(1+B1),-2)
B4,tax [*]:             =B3*B1
B5,adjusted net(1320):  =B3+B4

Although that is correct, since you did not ask for tax (B4), perhaps I
should write the last two formulas as follows, which should have the same
results:

B4,tax:                     =B5-B3
B5,adjusted net(1320) [*]:  =B3*(1+B1)

That way, you can eliminate B4 if you wish.

-----
[*] As before, I would feel better writing =ROUND(B3*(1+B1),0) in B5.
Again, we use ROUND to eliminate numerical "error" due to the fact that
Excel cannot represent most non-integers like percentages in B1 exactly.  It
does not seem necessary with these examples.  But there might be examples
where ROUND is indeed needed.

Thanks joue2004. I had a heavy workload last week, but now I want to
pick it up.

I used the short version of your solution *(2nd post). What I need is
Excel shows me the next higher net (incl tax) of gross (excl tax)
value that has not rest cents for both excl or incl tax values. As
long excel indicates the next value automatically without manually
trying all the values. So, I think there should be a loop?

 

[Bart van Dongen]

PS....

Suppose B1 contains the tax rate (10%), and B2 contains the minimum net
amount (1300).  Then:
B3,gross(1200):         =ROUNDUP(B2/(1+B1),-2)
B4,tax [*]:             =B3*B1
B5,adjusted net(1320):  =B3+B4

Although that is correct, since you did not ask for tax (B4), perhaps I
should write the last two formulas as follows, which should have the same
results:

B4,tax:                     =B5-B3
B5,adjusted net(1320) [*]:  =B3*(1+B1)

That way, you can eliminate B4 if you wish.

-----
[*] As before, I would feel better writing =ROUND(B3*(1+B1),0) in B5.
Again, we use ROUND to eliminate numerical "error" due to the fact that
Excel cannot represent most non-integers like percentages in B1 exactly.  It
does not seem necessary with these examples.  But there might be examples
where ROUND is indeed needed.

Obviously my reply message was lost due to bad internet in Indonesia.

Got a busy week. I have tried the short solution (2nd post from you).

What I am looking for is that Excel automatically gives the first net
(or gross value, in that can it shows the appropriate net value) value
above the value that I input (it can also be the same value), so I
think there should be a loop?

 

[joeu2004]

I used the short version of your solution *(2nd post).'
What I need is Excel shows me the next higher net (incl
tax) of gross (excl tax) value that has not rest cents
for both excl or incl tax values.

I believe I provided a solution that works for all "integral" tax rates;
that is, tax rates like 1% to 99% with no fractional percentage.

However, my second posting might have been misunderstood because it was
incomplete. And on second thought, I believe the VBA function in my first
response should be replaced.

So let me return to your original posting....


You ("AA Arens") wrote originally:

For a quotation I need to mention the price in USD,
that is including 10% tax.
Both this value and the value without tax should be
values that has no dollar cents as rest value, like
USD 30,00. [....]
Sample:
I type: 1300
Value incl tax 1320
Value excl. 1200

For all "integral" tax rates like 1% to 99% with no fractional percentage, I
believe the following is sufficient:

B1, tax rate: 10%
B2, guess: 1300
B3,gross before tax(1200): =ROUNDUP(B2/(1+B1),-2)
B4,tax: =B5-B3
B5,net including tax(1320): =ROUND(B3*(1+B1),2)

You did not ask for B4; I provide it as a bonus ;-).

The use of ROUND in B5 is primarily to avoid arithmetic anomalies that arise
because of the way that Excel represents numbers and performs arithmetic,
namely 64-bit binary floating-point. In B5, I round to 2 decimal places,
instead of 0 as you might expect, as a sanity check in order to ensure that
the result in B3 is correct.

(Note: The use of ROUND in B5 is an important part of the solution
involving the VBA function below.)

As I indicated above, that is a "sufficient" solution. For some "integral"
tax rates, it is possible that rounding up to only -1 decimal places (i.e.
10s) in B3 would produce integer results in B4 and B5.

For example, with a tax rate of 10% and minimum guess of 1300, the minimal
solution would be 1190 before tax and 1309 including tax.

But since you expect 1200 before tax and 1320 including tax, I presume that
you want to round up the gross amount (before tax) to 100s at least.

So the unconditional formulas above should meet your needs for "integral"
tax rates (no fractional percentage).

If this does not satisfy your requirements for "integral" tax rates (no
fractional percentage), please provide a numerical example that the formulas
above do not work for.


You wrote later:

I think there should be a loop?

I believe that a conditional solution (IF expression) or a loop is indeed
needed if you want to handle non-integral tax rates (with fractional
percentage), or if you want a minimal solution.

The VBA function that I provided in my first response provided a
"sufficient" solution, but not a minimal solution.

I believe the VBA function below provides a minimal solution.

Again, if this does not satisfy your requirements for non-integral tax rates
(with fractional percentage), please provide a numerical example that the
VBA function does not work for.

-----

Function grossAmt(guess As Double, myRate As Double) As Double

Const minRUP As Long = 2 ' ***CUSTOMIZE: 0 to 15
Dim rup As Long, tax As Double

If myRate > 0 Then
For rup = minRUP To 15
grossAmt = WorksheetFunction.RoundUp(guess / (1 + myRate), -rup)
tax = WorksheetFunction.Round(grossAmt * myRate, 2)
If tax = Int(tax) Then Exit Function
Next
End If

grossAmt = WorksheetFunction.RoundUp(guess, -minRUP)

End Function