Posted over 9 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.

Last edit:
7 months ago
by Henning Koch
Keywords:
bill