Money - and how to handle it
Tags: database, development, javascript, php
Published: 2021-08-11 05:57:53

---
title: Money - and how to handle it
published: true
description: The obvious choice for handling money in an application is not the correct one.
tags: database, development, javascript, php
cover_image: https://www.horuskol.net/assets/images/posts/20210802-money.jpg
canonical_url: https://www.horuskol.net/blog/2021-08-02/money-and-how-to-handle-it/
---

Many years ago, one of my first tasks as a developer was to make some changes to an application that my company's salesmen used to estimate the price for customer projects.

After I started digging around, the second thing I noticed (after seeing all the comments were in German) was that the prices of everything (hardware, software, engineering time) were stored as integers and were divided by 10,000 when presented to show the price in &#163; (British pounds).

I asked my boss about this, and I was told that "this was how sales and accounting software deals with money".

After learning this, I quickly worked out why.

## Why not float (or double, or real)?

Almost all modern computers store data as binary information, and perform binary mathematics. This is fine for whole numbers (up to a limit), since any number can be represented as a combination of various powers of 2. For example, the value 23 is stored in <span class="whitespace-nowrap">8-bit</span> memory as `00010111`, which is `16 + 4 + 2 + 1` or `2^4 + 2^2 + 2^1 + 2^0`.

This would be great if all numbers are whole numbers. However, we needed to be able to perform mathematics and handle numbers that have fractional components - such as, 0.1, 1/3, sin(45&deg;), or the square root of 2. There were a few different approaches to this problem, but since the 1980s almost every computer and programming language uses the same solution - the [IEEE 754 standard] - and this is the basis for the float and double types used in various programming languages, also known as floating-point types.

Floating-point types are not perfect, the limitations of binary storage creates some minor issues, but for most applications they are good enough.

Consider this, though:

```php
echo 0.1 + 0.2; // 0.3
echo (0.1 + 0.2) === 0.3; // nothing
```

```javascript
console.log(0.1 + 0.2); // 0.30000000000000004
```

These seem a little harmless, and could be fixed by using rounding functions, although this can be a little cumbersome:

```php
echo round(0.1 + 0.2, 1) === 0.3; // 1
```

```javascript
console.log(Math.round(10*(0.1 + 0.2))/10); // 0.3
```

There can also be problems with rounding some numbers:

```php
echo round(0.4999999999999999); // 0
echo round(0.49999999999999999); // 1

echo 0.4999999999999999; // 0.5
echo 0.49999999999999999; // 0.5
```

```javascript
console.log(Math.round(0.4999999999999999)); // 0
console.log(Math.round(0.49999999999999999)); // 1
```

These discrepancies may seem small, but do enough financial mathematics - add and subtract values, perform discounts and apply tax calculations - and eventually you'll get tripped up, and receive a call or email from your head of finance or clients or customers (or maybe all three). 

## Fixed-point types

Some languages, including SQL, have a fixed-point type. These allow for decimal values that are store with the same precision as integers.

Unfortunately, if you are using standard PHP or JavaScript (the `Number.toFixed()` method is for presentation and outputs a string), these fixed-point types will be cast to floating-point types, and end up with the same precision errors you are trying to avoid.

They also have limitations in that you have to specify the size and precision of the fixed-point numbers you want to use. For example, in MySQL, setting a column to `DECIMAL(5,2)` will let you store values from -999.99 to 999.99, but you will encounter errors if you try to set the field to 10,000 or 0.001. This means that you need to be aware of the range and precision you need before using it.

There is a [PECL package][PECL Decimal] that provides Decimal support to PHP, and also a [library for JavaScript][decimal.js].

## Money as an integer

So, to avoid all these floating-point precision issues, you should store and work with money values as integers.

There's a couple of ways to do this, such as having a dollars and cents column (or pounds/pence or whatever). However, the simplest approach is to have a single column/value and use a precision factor to convert the number as a money value for presentation.

Depending on how you're accessing the database you can cast the value into a simple Money object, which handles the presentation and so on. You could even wrap or extend this with Currency object that would handle currency symbols and localised formatting (sometimes the currency symbol precedes the value, sometimes it comes after - some countries write numbers like `1,234.56`, some `1 234.56`, and others `1.234,56`).



[IEEE 754 standard]: https://en.wikipedia.org/wiki/IEEE_754
[PECL Decimal]: https://pecl.php.net/package/decimal
[decimal.js]: https://mikemcl.github.io/decimal.js/