Forum Discussion

Storming's avatar
Storming
Brass Contributor
Apr 14, 2020
Solved

VAT calculation

Hi, I want to calculate VAT (generally 20%) from a gross amount. I want to be able to format it to 2 dec places so that if the pence is .100 to .104 the pence stays as .10 but if the pence is .105 t...
Access 2016
  • luise983's avatar
    luise983
    Jul 19, 2024

    Your method works, but you can simplify it by using:

    ```sql
    CCur(Round([Gross Amount] * [VAT Percentage] / (100 + [VAT Percentage]), 2))
    ```

    This https://vatcalculatorsa.co.za/ method keeps it as currency and exports correctly to Excel.

luise983's avatar
luise983
Copper Contributor
Jul 19, 2024

Your method works, but you can simplify it by using:

```sql
CCur(Round([Gross Amount] * [VAT Percentage] / (100 + [VAT Percentage]), 2))
```

This https://vatcalculatorsa.co.za/ method keeps it as currency and exports correctly to Excel.

Karl_Donaubauer's avatar
Karl_Donaubauer
MVP
Jul 21, 2024

Hi,

 

A warning to you and the OP who marked your suggestion as best response:

 

You should never include the Round() function into anything that has to do with money, because it does not round commercially but uses mathematical or so called banker's rounding, i.e. 5 is rounded down to the nearest even number. This is an ancient problem of the Round() function and discussed a million times since it was introduced with Access 2000.

 

Therefore Gustav in his suggestions uses the Format() function or a user defined function to round commercially and avoids the rounding errors that happen with Round().

 

Servus
Karl
****************
Access News, Forever, DevCon
Access-Entwickler-Konferenz AEK - 19./20.10. Nürnberg

Resources