Page 2 of 2
Net from gross
Now to the question of decreasing a quantity by a percentage. If you know the Gross price including VAT and want to know the Net price without it, how do you work it out?
An attractive, but incorrect, line of reasoning is to say that as the Net price was increased by V% the Gross price should be decreased by V% and so Net=Gross*(1-V%).
This is misguided because the amount add to Net, i.e. Net*V, isn’t the same as the amount subtracted from Gross i.e. Gross*V%.
In general if you increase something by I% and then decrease the result by I% you do not get back to where you started!
If you would like a demonstration that this method is incorrect then create the small spreadsheet shown here:
The value entered into B2 is increased by the percentage in B1 and then decreased by the same percentage. You can see in the figure that in the case of 200 and 50% the final result is 150 which is very clearly not 200! If you experiment with this spreadsheet you will find that for small values and small percentages the difference between the original value and the calculated value can be quite small and this sometimes leads people to believe that the answer is approximately correct - but don't be fooled.
The correct answer depends on the rearrangement of the formula:
Net = Gross/(1+V%)
That is, if you multiply by (1+V%) to get the Gross you have to divide by (1+V%) to get back to the Net.
- In general, if you know that a value has been increased by I% and you want to calculate the original value divide by (1+I%)
Calculating Gross from Net
We are now going to enter Gross prices and calculate the Net ones.
Delete the formula in B6 and type the value 11.75 in its place
In B4 type the formula for calculating Net:
and you will see 10.00 appear.
Try out a range of values in B6 to see the formula in action.
Percentages don’t always work in exactly the way that you might think. The discussion above about the erroneous calculation of Net from Gross price should have put you on your guard. For another example consider the following question - is taking P% of something and then Q% of the result the same as taking P%+Q% of the original value?
Putting this in more practical terms if you own 10% of something and then you are given a bonus of 5% of your holding do you now own 15%?
Taking P% and then a further Q% of a value is
and this is quite clearly not the same as
To see that this is the case just try to work out some results. For example, if you have 10% of 100, you have 10, if you now ask for 5% of your holding you receive an additional 0.5. But (10+5)%, i.e. 15%, of 100 is 15. In other words if you take successive percentages of something they don’t add they multiply.
When reasoning or bargaining with percentages it is usual to speak of “another 1%” in the sense of increasing the percentage offered by adding one percent. In this case it does look as if percentages add but notice that all the percentages refer to a proportion of the same whole.
The best way to think of this is by considering slices of a pie. If you are negotiating for an additional percentage of the whole pie then certainly percentages add but if you are arguing for a percentage of the slice you have already been allocated then the more complicated formula applies.
Now consider the situation with percentage decreases. If a salesman offers you a 5% additional discount on a price that is already subject to a 10% discount, what is the total discount? The important point is to realise that there really are two possible interpretations of this offer because a 5% discount on a price that has is already subject to a 10% discount is not the same as a 15% discount on the original price. The salesman might mean his offer as a full 15% or he might actually mean a further 5% and you would need to ask for clarification.
If it is the latter then the offer is 1-(1-10%)*(1-5%) which works out to 14.5%.
As another example of how percentages do not add up in the way that you might suppose consider the following simple problem. A trader makes a 10% profit on an investment in the first part of the day but before the day is out has made a 10% loss - is the total profit zero? The common sense answer to this question is yes- the 10% gain is wiped out by the 10% loss, but this reasoning doesn’t take into account the fact that the percentages are calculated on different values.
Suppose the initial investment was $100, then a 10% gain is $10 and this makes the total investment worth $110. The following 10% loss reduces the investment by $110*0.1 or $11, making the final investment worth only $99. Hence a 10% gain followed by a 10% loss doesn’t take you back to square one. Also notice that it doesn’t matter in which order the loss or gain occurred - the answer is exactly the same, a 1% loss on the initial value.
Of course if both the percentages were quoted relative to the initial investment then the percentages would add up because 10% of $100 is always $10. Again what is important here is that you realise that there are two possible meanings and you should always discover what value a percentage change is using as its starting point.
If you would like to master percentages and interest rates see Financial Functions.
Moving to Excel 2010
Moving from an earlier version of Excel to the latest one can come as a culture shock. Here's an exploration of the differences and some new features.