Invoices: How to properly round and calculate totals

While it might seem trivial to implement an invoice that sums up items and shows net, gross and vat totals, it actually involves a lot of rules and caveats. It is very easy to create invoices where numbers don't add up and a few cents are missing. A missing cent is a big deal for an accountant, so it is important for your invoices to list correct numbers.

Note that this is not legal advice. Also note that while this note has a number of code examples in Ruby and MySQL, the concepts apply to all programming languages and data stores.

When to round

There are exactly two spots where your invoice logic should round money amounts:

1. When calculating the total of an invoice item from quantity and unit price (and sometimes discount). Most vendors will apply standard rounding here. Some vendors always round down item totals in order to demonstrate exceptionally good business practices to their clients.

2. When calculating the amount of VAT from the sum of all item totals. Fiscal authorities require you to apply standard rounding here.

All other totals in your invoice will be some sum or difference of these two rounded numbers, so no further rounding is necessary.

Don't pass around unrounded values

The rounding of money amounts is not a matter of presentation, and should never happen in a view. Whether an amount is rounded or not is an important part of your model logic.

When a money amount is going to be rounded (e. g. an item total), your model should always return or store this amount as a rounded value. No other model should ever see the original value before rounding.

Once you round, use the rounded value for all further totals

The following code is wrong because it sums up unrounded item totals, and then rounds the sum:

Copyinvoice_total = items.sum do |item|
  item.quantity * item.unit_price
end.round(2)

When printed on an invoice, the numbers produced by the code above will not always add up. The correct way is to sum up the rounded item totals:

Copyinvoice_total = items.sum do |item|
  item_total = (item.quantity * item.unit_price)
  item_total.round(2)
end

The numbers produced by this code will always add up. Also note how the total does not require further rounding, because it is a sum of rounded values.

Don't ever use the float data type in Ruby or MySQL

The data types Float (Ruby) and FLOAT (MySQL) are not suited for calculating or storing money amounts. You need to understand the following two notes or you have no business implementing invoices or do any kind of arithmetic in Ruby:

• BigDecimal arithmetic in Ruby
• How DECIMAL columns deal with numbers exceeding their precision or scale

Even if you are aware of the limitations of Float, it is very easy for Float values to creep back into your code. Whenever you mix Floats with BigDecimals, the result will be a Float. A common mistake is to properly do all the arithmetic using BigDecimals, and then use a Float constant like VAT_RATE = 0.19 to calculate the VAT rate. That constant should be defined as VAT_RATE = BigDecimal('0.19') instead.

Know if you are selling net or gross prices

It is up to a vendor whether she wants to list her prices as net or gross. In the B2B world, prices are usually listed as net. When a vendor is mostly selling to private customers, prices are listed as gross.

You must know how prices are listed in the particular application you are working on (if in doubt, ask your client). It affects how the net, vat and gross totals are calculated from each other.

If prices are listed as net, the gross total is calculated from the net total:

Copynet = items.sum(&:total)
vat = (net * VAT_RATE).round(2)
gross = net + vat

If prices are listed as gross, the net total is calculated from the gross total:

Copygross = items.sum(&:total)
vat = (gross * (1 - (1 / VAT_RATE))).round(2)
net = gross - vat

Don't show VAT amounts for individual items

Fiscal authorities do not require you to show the amount of VAT for each individual invoice item. All you need is a proper VAT total for the complete invoice.

Because of the rounding rules above, it is also extremely difficult to show VAT amounts for individual amounts while still making these numbers add up to the total VAT. Don't go there.

Use consistent wording where possible

Try not to randomly use "sum", "amount", "price" and "total". Just pick one term you like ("total" is fine) and stick with it.

Also some clients will require you to list all sorts of subtotals on your invoices: Before rebate, after rebate, after rebate but before tax, etc. It is very hard to come up with good names for those subtotals, so don't be too hard on yourself here.

Caching totals

It can be useful to cache totals in the database, e. g. to sum up many invoices through SQL. As we learned in "Don't pass around unrounded values", these totals should be rounded properly before storage. Don't ever store unrounded values.

Also note that when your invoice model is using a before_save or before_validation callback to sum up item totals and store that value in an attribute, you need to skip those items that are #marked_for_destruction?. Otherwise you will include items that have been ticked for deletion in a nested form. See ActiveRecord: When aggregating nested children, always exclude children marked for destruction.

Best practice example

Here we will use what we learned above in order to create a correct invoice implementation.

We require one table to store invoices, and another table to store invoice items:

Copycreate_table :invoices do |t|
  t.timestamps
end

create_table :items do |t|
  t.integer :invoice_id
  t.decimal :unit_price, :precision => 10, :scale => 2
  t.integer :quantity
  t.timestamps
end

Note how we are using a DECIMAL column rather than FLOAT column for the item's unit price.

Now we implement our Item model:

Copyclass Item < ActiveRecord::Base

  validates_numericality_of :unit_price, :quantity
  
  def total
    (unit_price * quantity).round(2)
  end
  
end

Note how when asked for its total, the item returns the total with rounding already performed. It never returns an unrounded total.

Assuming that prices are listed as net prices, we implement our Invoice model like this:

Copyclass Invoice < ActiveRecord::Base

  VAT_RATE = BigDecimal('0.19')

  has_many :items
  
  def totals
    totals = {}
    totals[:net] = items.sum(&:total)
    totals[:vat] = (totals[:net] * VAT_RATE).round(2)
    totals[:gross] = totals[:net] + totals[:vat]
    totals
  end

end

Note several things:

• The VAT_RATE constant is a BigDecimal, not a Float.
• The invoice has a single method totals which returns net, vat and gross totals in a single hash. Code that calls your invoice usually requires all these values together (e. g. to print an invoice), so we don't want to go through our items multiple times.
• Because invoice items already round their totals, we only need to round once more: When computing the VAT rate.

If our application is listing prices as gross prices, our Invoice model changes like this:

Copyclass Invoice < ActiveRecord::Base

  VAT_RATE = BigDecimal('0.19')

  has_many :items
  
  def totals
    totals = {}
    totals[:gross] = items.sum(&:total)
    totals[:vat] = (totals[:gross] * (1 - (1 / VAT_RATE))).round(2)
    totals[:net] = totals[:gross] - totals[:vat]
    totals
  end

end