Rounding down to two decimal places while calculating GST

**Ruegen** · 04-15-2015, 02:57 AM

I'm trying to work out a formula to work out our tax (Australian tax is a 10% GST)

to work out an amount of GST you divide by 11

to work out what 10% is you use the regular formula

everything has to be rounded down because GST can't be rounded up (some rule/law I don't know much about)

Anyway this works fine until I get to the total and divide by 11

I should be able to take the total and divide by 11 to get the same result as what I get with my formulas

say t = 720 /11

t now = 65.45 (rounded down I guess)

however I don't get this from my vba, any advice?

Code:

Dim gst As Double, b As Double, p As Double, y As Double, pInc As Double, tEx As Double, tInc As Double, tg As Double, gExp As Double
Dim qty As Integer


p = 6
qty = 120


'works out gst of totaled price
gst = p


Debug.Print time() & "  " & "gst 1 " & gst


gst = gst / 11


Debug.Print time() & "  " & "gst 2 " & gst


gst = Int(100 * gst) / 100


Debug.Print time() & "  " & "gst 3 " & gst




'//////////////////
'works out pre GST price
b = p
Debug.Print time() & "  " & "b 1 " & b
b = (b * 10) / 11
Debug.Print time() & "  " & "b 2 " & b
b = Int(100 * b) / 100
Debug.Print time() & "  " & "b 3 " & b


'works out total
y = b + gst


Debug.Print time() & "  " & "y 1 " & y


pInc = b


Debug.Print time() & "  " & "pInc 1 " & pInc


pInc = (pInc / 100) * 10


Debug.Print time() & "  " & "pInc 2 " & pInc


pInc = Int(100 * pInc) / 100


Debug.Print time() & "  " & "pInc 3 " & pInc


t = p * qty
tInc = y * qty
tEx = b * qty


'needs to be rounded down
gExp = gst * qty
'gExp = Int(100 * gExp) / 100




Debug.Print
Debug.Print time() & "  " & "total Inc GST 1  " & t
Debug.Print time() & "  " & "total Inc GST 2 (+) " & tInc
Debug.Print
Debug.Print time() & "  " & "total GST " & tEx
Debug.Print time() & "  " & "total price exGST " & gExp

**CJ_London** · 04-15-2015, 03:46 AM

it is because of bankers rounding. See this link and google 'bankers rounding' for more information

https://support.microsoft.com/en-us/kb/196652/

Since you always want to round down and to two dp you need to deduct 0.005 from the result before rounding e.g.

round((a/b)-0.005)

**Ruegen** · 04-15-2015, 03:58 AM

Originally Posted by Ajax

it is because of bankers rounding. See this link and google 'bankers rounding' for more information

https://support.microsoft.com/en-us/kb/196652/

Since you always want to round down and to two dp you need to deduct 0.005 from the result before rounding e.g.

round((a/b)-0.005)

cool thanks Ajax - what are the values of a & b?

**CJ_London** · 04-15-2015, 04:36 AM

whatever you want - from your first post 720 and 11

**Ruegen** · 04-15-2015, 04:46 AM

nope - edit

will try your round

**CJ_London** · 04-15-2015, 05:21 AM

be aware there are 'off the shelf' systems out there such as accounting, stock control etc which do not necessarily 'follow the rules'. So if you are trying to match to a calculated value from one of these systems you may end up being 1c out. Modify your system to match and then it won't balance against another system

The way round it is to store the calculated tax in a separate field in your invoice (one of the few occasions where this is the right thing to do!) and you can then adjust it to match

**Ruegen** · 04-15-2015, 07:36 AM

It's our system/database

I have two different values
4.95
and
6

on a button even I have

Code:


Dim x As Double, z As Double, y As Double, q As Double, v As Double
Dim f As Double, m As Double
Dim g As Double


f = 6
m = 5.45


z = gSubGst(f, 11, 10) 'done
x = gGST(f, 11) 'done
y = gCalc(f, 11, 10) 'done
q = gCalcQty(f, 11, 10, 120#) 'done
v = gSubGstQty(f, 11, 10, 120) 'done
i = gGstQty(f, 11, 120) 'done
g = gAddGSTQty(m, 10, 120)
s = gAddGST(m, 10)


Dim t1 As Currency
Dim t2 As Currency


t1 = (v + i) 'done
t2 = (x + z) * 120 'done


Debug.Print
Debug.Print
Debug.Print x & " <== x gGST"
Debug.Print z & " <== z gSubGst"
Debug.Print y & " <== y gCalc "
Debug.Print q & " <== q gCalcQty "
Debug.Print v & " <== v gSubGstQty"
Debug.Print i & " <== i gGstQty"
Debug.Print g & " <== g gAddGSTQty"
Debug.Print s & " <== s gAddGST"
Debug.Print t1 & " <== t1 "
Debug.Print t2 & " <== t2 "

and then my functions are

Code:


'function gets your gst (inc GST)
Public Function gGST(PriceIncGST As Double, GST_Div As Double) As Double
    Dim g As Double
    g = (PriceIncGST / GST_Div)
    'g = Int(-20 * (g)) / -20
    
    gGST = g
End Function


'function gets your gets price (inc GST) before GST
Public Function gSubGst(PriceIncGST As Double, GST_Div As Double, GST As Double) As Double
    Dim p As Double
    
    p = (PriceIncGST * GST) / GST_Div
    'p = Int(20 * (p)) / 20
    
    gSubGst = p
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gCalc(PriceIncGST As Double, GST_Div As Double, GST As Double) As Double
    Dim p As Double, g As Double
    
    p = (PriceIncGST * GST) / GST_Div
    p = Int(-20 * (p)) / -20
    
    g = (PriceIncGST / GST_Div)
    g = Int(20 * (g)) / 20
    
    gCalc = p + g
End Function


'function gets price (inc GST) and multiplies it by qty
Public Function gCalcQty(PriceIncGST As Double, GST_Div As Double, GST As Double, QTY As Integer) As Double
    Dim p As Double, g As Double
    
    p = (PriceIncGST * GST) / GST_Div
    p = Int(-20 * (p)) / -20
    
    g = (PriceIncGST / GST_Div)
    g = Int(20 * (g)) / 20
    
    gCalcQty = (g + p) * QTY
    
End Function


'function gets pre gst of price inc gst then muliplies by qty
Public Function gSubGstQty(PriceIncGST As Double, GST_Div As Double, GST As Double, QTY As Integer) As Double
    Dim p As Double
    
    p = (PriceIncGST * GST) / GST_Div
    p = p * QTY
    
    gSubGstQty = Int(-20 * (p)) / -20
End Function


'function gets pre gst then muliplies by qty
Public Function gGstQty(PriceIncGST As Double, GST_Div As Double, QTY As Integer) As Double
    Dim g As Double
    
    g = (PriceIncGST / GST_Div)
    g = g * QTY
    
    gGstQty = Int(20 * (g)) / 20
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gAddGST(PriceExGST As Double, GST As Double) As Double
    Dim pG As Double
    
    pG = PriceExGST * (GST / 100)


    gAddGST = Int(-20 * (pG + PriceExGST)) / -20
    
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gAddGSTQty(PriceExGST As Double, GST As Double, QTY As Integer) As Double
    Dim pGQ As Double
    
    pGQ = PriceExGST * (GST / 100)
    pGQ = Int(-20 * (pGQ + PriceExGST)) / -20
    
    gAddGSTQty = (pGQ) * QTY
    
End Function

I use Int(-20 * (p)) / -20 to move by 5 but when I change f falue from say 4.95 which works fine to say 6 I get a bad rounding affect.

I'm very stuck with this.

**Ruegen** · 04-15-2015, 07:38 AM

for one f value I change it from -20 to 20 and it works fine, but then the next f value I try has the opposite affect

**Ruegen** · 04-15-2015, 07:47 AM

using round(p,2) seems to work

Code:



'function gets your gst (inc GST)
Public Function gGST(PriceIncGST As Double, GST_Div As Double) As Double
    Dim g As Double
    g = (PriceIncGST / GST_Div)
    'g = Int(-20 * (g)) / -20
    g = Round(g, 2)
    
    gGST = g
End Function


'function gets your gets price (inc GST) before GST
Public Function gSubGst(PriceIncGST As Double, GST_Div As Double, GST As Double) As Double
    Dim p As Double
    
    p = (PriceIncGST * GST) / GST_Div
    'p = Int(20 * (p)) / 20
    p = Round(p, 2)
    
    gSubGst = p
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gCalc(PriceIncGST As Double, GST_Div As Double, GST As Double) As Double
    Dim p As Double, g As Double
    
    p = (PriceIncGST * GST) / GST_Div
    'p = Int(-20 * (p)) / -20
    p = Round(p, 2)
    
    g = (PriceIncGST / GST_Div)
    'g = Int(20 * (g)) / 20
    g = Round(g, 2)
    
    gCalc = p + g
End Function


'function gets price (inc GST) and multiplies it by qty
Public Function gCalcQty(PriceIncGST As Double, GST_Div As Double, GST As Double, QTY As Integer) As Double
    Dim p As Double, g As Double
    
    p = (PriceIncGST * GST) / GST_Div
    'p = Int(-20 * (p)) / -20
    p = Round(p, 2)
    
    g = (PriceIncGST / GST_Div)
    'g = Int(20 * (g)) / 20
    g = Round(g, 2)
    gCalcQty = (g + p) * QTY
    
End Function


'function gets pre gst of price inc gst then muliplies by qty
Public Function gSubGstQty(PriceIncGST As Double, GST_Div As Double, GST As Double, QTY As Integer) As Double
    Dim p As Double
    
    p = (PriceIncGST * GST) / GST_Div
    p = p * QTY
    
    gSubGstQty = Round(p, 2)
    'gSubGstQty = Int(-20 * (p)) / -20
End Function


'function gets pre gst then muliplies by qty
Public Function gGstQty(PriceIncGST As Double, GST_Div As Double, QTY As Integer) As Double
    Dim g As Double
    
    g = (PriceIncGST / GST_Div)
    g = g * QTY
    
    gGstQty = Round(g, 2)
    'gGstQty = Int(20 * (g)) / 20
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gAddGST(PriceExGST As Double, GST As Double) As Double
    Dim pG As Double
    
    pG = PriceExGST * (GST / 100)
    pG = Round(pG, 2) + PriceExGST
    
    gAddGST = pG
    'gAddGST = Int(-20 * (pG)) / -20
    
End Function


'calculates price (inc GST) by itself, should return same result as price
Public Function gAddGSTQty(PriceExGST As Double, GST As Double, QTY As Integer) As Double
    Dim pGQ As Double
    
    pGQ = PriceExGST * (GST / 100)
    pGQ = pGQ + PriceExGST
    pGQ = Round(pGQ, 2)
    'pGQ = Int(-20 * (pGQ)) / -20
    
    gAddGSTQty = (pGQ) * QTY
    
End Function

Rounding down to two decimal places while calculating GST

Thread Tools

Rounding down to two decimal places while calculating GST

Similar Threads

Help me! Different decimal places for text box in different row!

Formatting for 3 decimal places

Decimal Places

Report decimal places

Limiting decimal places

Posting Permissions