Correct way to calculate prices including tax in foreign currencies

I'm trying to upgrade an application so that I can sell to multiple countries. I store all of my prices in the database in GBP excluding tax up to 4dp and I need to calculate the prices in the country's currency including tax.

Do I multiply the price by the exchange rate against the price excluding tax (option 1) or do I calculate the amount including tax and then multiple by the exchange rate (option 2)? I have also added an option 3 after looking at how OpenCart calculates it which is similar to option 2 but only ever rounds when displaying it. Here are the formula's for all 3 options:

Option 1:

Round((Price * Exchange Rate) / 100 * (100 + Tax Rate))

Option 2:

Round(Round(Price / 100 * (100 + Tax Rate)) * Exchange Rate)

Option 3:

Round((Price / 100 * (100 + Tax Rate)) * Exchange Rate)

For example say I have a product with a price of 89.99. If I wanted to display that in a currency with an exchange rate of 1.5 and a tax rate of 20%. Would I say:

Option 1:

Round((89.99 * 1.5) / 100 * (100 + 20)) = 161.98

Option 2:

Round(Round(89.99 / 100 * (100 + 20)) * 1.5) = 161.99

Option 3:

Round((89.99 / 100 * (100 + 20)) * 1.5) = 161.98

I've found that OpenCart always multiplies the unrounded figures by the exchange rate at the end. For example their formula for calculating the line total is:

Round((Price / 100 * (100 + Tax Rate)) * Quantity * Exchange Rate)

So if I was to order 3 of my product's it would give:

Round((89.99 / 100 * (100 + 20)) * 3 * 1.5) = 485.95

The problem I find doing it OpenCart's way is the user will see an item price (including tax) of 161.98 and a line total of 485.95. However if I say 161.98 * 3 I get 485.94, so it doesn't sum up correctly.

It's important I get this right as you can see I'll end up with penny issues. I'd appreciate it if someone could let me know which way is correct or suggest an alternative if none are right. Thanks

Solution

Since all variables are rounded except Exchange Rate, which I would expect to usually look something like this 1.3462, we can write a test like this:

// I'm guessing you need it in PHP
$price = 89.99;
$quantity = 1;
$tax = 20;
$tax = $tax/100.0 + 1;
// $tax = 1.2
$exchangeRate = 1.5;

$totalPrice = $price*$quantity*$tax;                     // Test #1
$totalPriceRounded = round($price*$quantity*$tax,2);     // Test #2

echo $totalPrice.'<br/>'.$totalPriceRounded;

Which would have this output:

107.988   // Test #1 <--- This is amount that needs to be paid to you and Great Britain
107.99    // Test #2 <--- rounded

Now, it's obvious there is a difference in amount, so which to choose? Since you are charging an item from Great Britain, you expect to get paid the full amount you are asking for, in GBP, so lets rely on that factor. In the end, if I understood correctly, the tax the customer has to pay is tax to Great Britain.

Lets check the final prices in foreign currency:

$totalPrice = round($totalPrice * $exchangeRate,2);
$totalPriceRounded = round($totalPriceRounded * $exchangeRate,2);

echo $totalPrice.'<br/>'.$totalPriceRounded;

Which would have this output:

// Amount in foreign currency
161.98      // Test #1
161.99      // Test #2

With all that said, lets check which one of these would return you a value closest to 107.988 GBP when calculated back to GBP from foreign currency.

// To calculate the totalPrice back to price*tax*quantity in GBP
$totalPrice /= $exchangeRate;
$totalPriceRounded /= $exchangeRate;

echo $totalPrice.'<br/>'.$totalPriceRounded;

Output of which is:

// totalPrice in GBP currency
107.98666666667       // Test #1
107.99333333333       // Test #2

And when we divide by tax:

$totalPrice /= $tax;
$totalPriceRounded /= $tax;

echo $totalPrice.'<br/>'.$totalPriceRounded;

Output is:

// the amount you get in GBP currency
89.988888888889    // End result of Test #1
89.994444444444    // End result of Test #2

So as you can see, in this case, by using the bare amount of round($price*$quantity*$tax*$exchangeRate, 2) you are getting less, but by using round(round($price*$quantity*$tax,2)*$exchangeRate, 2) you get a little more which again, rounded both give the same value. In some other scenario the difference will likely be different from what we got here and might be the other way around.

CONCLUSION

If you have a fixed number of products each with fixed prices then you will be either loosing money (an insignificant amount) or earning more than you should (also an insignificant amount) over time. The outcome depends on the popularity and price of each product (i.e. will the round function round up or down), and on the way of calculation you chose. You could, depending on product popularity, manually adjust the prices of each product to minimize the loss/gain over time.
If you have products with random prices like lets say you are renting web servers and the price is $0.01/second. Then the expectation value for your loss/gain is close to zero. And the more purchases are made the closer to zero it gets. So you are not loosing anything here regardless of the approach. One time you win some one time you loose some.

In my opinion I would standardize it to round everything:

$price = 4.47;
$quantity = 1.2    // lets say liters

// Even round the price before applying tax
$totalPrice = round($price * $quantity, 2);

because if you are forced to show all the different stages of calculation then you will have to round every step.

But the most precise would be to use this as the least information is lost:

// Round once at end
$totalPrice = round($price*$quantity*$tax*$exchangeRate, 2);

In the end it all depends on how you set up your product prices or how your pricing works to be able to choose which method to go with. I'd say it doesn't really matter, I think every company simply chooses what suits them best.

Legally, some countries/cities/etc. have laws on how to calculate this, you should be looking at Great Britain laws to check on the valid procedure for your problem.