Implementing a sales tax strategy for Invoices

Question

Here in South Africa we have Value Added Tax (VAT) which is pretty much identical to Sales Tax and is currently fixed at 14%, but could change at any time.

I need to include VAT on invoices (which are immutable) consisting of several Invoice Lines. Each line references a Product with a Boolean property, IsTaxable, and almost all products are taxable.

I don't want to store pre-tax prices in the database, because that just makes it hard to read the real price that the customer is going to pay and everywhere I display those prices, I then have to remember to add tax. And when the VAT rate does change, for this particular business, it is undesirable for all prices to change automagically.

So I reckon a reverse tax calculation is the way to go and probably not uncommon. The invoice total is the sum of all invoice line totals, which includes any line discounts and should be tax-inclusive. Therefore the invoice total itself is tax-inclusive:

TaxTotal = InvoiceTotal / (1 + TaxRate),

where InvoiceTotal is tax-inclusive and TaxRate == 0.14

Since invoices cannot be changed once issued (they are immutable), should I:

1. Store a single Tax amount in my Invoices table that does not change? Or...
2. Store a tax amount for each invoice line and calculate the invoice tax total every time I display the invoice?

Option 2 seems safer from a DBA point-of-view since if an invoice is ever manually changed, then Tax will be calculated correctly, but if the invoice has already been issued, this still presents a problem of inconsistency. If I stick with option 1, then I cannot display tax for a single line item, but it makes managing the tax total and doing aggregate calculations easier, though it also presents inconsistency if ever changed.

I can't do both since that would be duplicating data.

• Which is the right way to go? Or is a reverse tax calculation a really bad idea?

Answer 1

Store the pre tax value in the data base, you can also store the with tax value and use that for most use cases.

Th big problem I forsee is the rounding rules for VAT on invoices.These (at least in the UK) are really strict and there is no way for your reverse calculation to get this right.

Also you need to store the tax item by item as the VAT dragons will expect you to refund exactly the tax paid if an item is returned. You really need to get hold of the local sales tax rules before you start.

My experience is that you can get dragged over the coals if your calculations are out by as little as a penny, and, if you are audited you need to be able to show how you arrived at the VAT figure so not storing anything used in your calculations will catch you out.

Answer 2

I totally agree with James Anderson! In Germany the rules according VAT calculations are as strict as in the UK.

We have to accumulate the net value by VAT percentage (we have three types: 0, 7 and 19 percent) rounded by two digits. On this rounded value we have to calculcate VAT. VAT has to be rounded by two digits and has to be showed at the invoice.

But nonetheless you can store prices including tax. It depends whether the net prices or the end prices stay unchanged when tax rises. In Germany usually B2B net prices stay unchanged but B2C end prices stay unchanged - it depends.

You can calculate it this way:

with cPriceIncludingVAT as (
    select  InvoiceNo, VATPercentage,
            PriceIncludingVAT = cast(sum(amount * price) as decimal(12,2))
    from    InvoiceLines inner join VAT on VAT.VATID=InvoiceLines.VATID
    group by InvoiceNo, VATPercentage
),
cVATcalculated as (
    select  InvoiceNo, VATPercentage, PriceIncludingVAT,
            VAT = cast(PriceIncludingVAT * VATPercentage / 
                         (1+VATPercentage) as decimal(12,2))
    from    cVATcalculated
)
select    InvoiceNo, VATPercentage, PriceIncludingVAT, VAT,
          NetPrice = PriceIncludingVAT - VAT
from      cVATcalculated;

If you save this as a view you should be able to reprint a dynamically calculated VAT value exactly. When there is an accounting system you can (and should) export exactly the same data you printed. Usually you should save values like these as field values within the database - but I understand if you'd like to have a more dynamic approach ...