Posted about 8 years ago. Visible to the public. Repeats.

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:

Copy
invoice_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:

Copy
invoice_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:

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:

Copy
net = 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:

Copy
gross = 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:

Copy
create_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:

Copy
class 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:

Copy
class 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:

Copy
class 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

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Henning Koch
Last edit:
almost 3 years ago
by Emanuel De
Keywords:
bill
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more