Two everyday questions:

• If the price including sales tax is $125, what is the price without tax?

• You are offered a 10% reduction on an item that has just increased in price by 10% - are you still paying the old price?

Discover the answers, and how to work them out using a spreadsheet.

• If the price including sales tax is $125, what is the price without tax?

• You are offered a 10% reduction on an item that has just increased in price by 10% - are you still paying the old price?

I have to confess that these are trick questions. You need to respond to the first one by asking "what is the rate of the sales tax?" And the second one is designed to catch you out - the correct answer is "No" which you may find surprising. Later on we'll discover how to avoid this common misconception.

Legacy arithmetic

As you are reading this on iProgrammer you are obviously a computer user and you almost certainly have used a spreadsheet to work out answers to questions like those posed above. However, many of you will remember the pre-spreadsheet age when you had to rely either on a pocket calculator or on working things out, either in your head or on paper, using simple arithmetic.

One legacy of the pre-computer era was that we developed various rules of thumb to work things out - and these have tended persist despite the fact that we now have the tools to do the calculations in a more direct manner. For example, one method of finding VAT at the 17.5% rate from the gross amount was to multipy by 47 and divide by 7. When I first came across this I was somewhat horrified that anyone would still be using such an indirect and cumbersome way of performing percentage (i.e. decimal) arithmetic and pointed it out to a colleague as an example of how not to proceed in the computer age. Her response was unexpected - she had recently been advised to use the very same method. On checking I discovered to the practice to be very widespread - my accountant even used it to check my spreadsheet-originated calculations!

The calculation 47/7 for the17.5% VAT rate can be considered perfectly correct in that it will provide the right answer. So why do I object to it Looking back to my training (psychology and statistics) I find the calculation to be reliable but not to have face validity - in other words the method used does not appear to be related to the question - which is how to derive the VAT element get from a sum that includes VAT. Moreover, as any accountant in the UK knows only too well, the VAT rate can change so what is required is a method that can accommodate this fact. In the UK the temporary reduction in the rate of VAT to 15% is about to end and the previous rate of 17.5% will be restored so what better time than to consider an efficient way to do VAT arithmetic using percentage arithmetic and a spreadsheet.

Percentage arithmetic with a spreadsheet

A percentage is simply a fraction expressed as a proportion of 100. For example, 1/2 is 50% because 50 is half of 100. The fraction and its corresponding percentage are simply two different ways of talking about the same thing. In general we tend to encounter percentages rather than their underlying decimal fractions. For example, a discount of 10% always sounds friendlier than a discount of 0.1.

When it comes to working with spreadsheets, however, you need to be able to recognise the fractional form of a percentage. Even though you type 50% into a cell what is stored in the cell and used in subsequent calculations is 0.5, a fact that can quickly be verified by formatting the cell to show as General - i.e. as the numeric value in the cell without a display format.

If you apply a general format to this cell it will display as 0.5

Using a spreadsheet makes it to enter a fraction and display it as a percentage. That is, if you format a cell or range to display as percentage then entering a fraction such as 0.5 will result in 50.00% being displayed. This can be the cause of confusion if you are not aware of exactly what is happening.

If you are used to dividing by 100 when calculating percentages you need to remember that the spreadsheet has already done this for you. For example, if you enter 75% then 0.75 is stored in the cell but, as long as a percentage format applies, it will display as 75%. The danger is that you will make the mistake of including 100 in all formulae that make use of the apparent percentages.

In order to avoid confusion adopt the following rules consistently:

• Always enter a percentage value with a trailing percentage sign.
• Always format cells that contain percentages to show as percentages
• Always calculate using percentages as if they were decimal fractions

A sales tax calculator

Type labels as shown below into a spreadsheet. As tax rates can change so we should always treat them as variables in a spreadsheet. Type the rate of sales tax you want to use into B4. In the example we are using 17.5%, the UK VAT rate from January 2010.

To make sure that everything is working type in 10 as the net price to be calculated and type in two formulas:

=B3*B4 into B5 to calculate the amount of VAT to be added to Net Price

=B4+B5 into B6 to calculate the gross (VAT included) price

You will see that 1.75 appears in B5 and 11.75 in B6.

You will need to format B4, B5 and B6 to display as Currency

Then change the entry in B4 to contain different net prices and see how the formulas work out the VAT and gross each time.

Percentage increase/decrease

Working out a percentage is easy. What is slightly more difficult is to work out is the result of increasing or decreasing a value by a given percentage.

As long as you think about it in the right way even this isn’t difficult. If a value increases by I% then you have the original value plus I% of it:

  value + value *I%

or more simply

   value*(1+I%)

Using this formulation you should now find it very easy to work out the effect of VAT on net price. If the VAT rate is V% then the Gross price is simply

   Net*(1+V%)

To try this out delete the entry in B6 and type in its place:

    =B4*(1+B3)

As before try different amounts of Net in B4 to see the formula in action.

<ASIN:0470044039>