|Written by Janet Swift|
Page 1 of 4
Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.
Buy from Amazon
Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.
So you think you know the percentages?
Many financial calculations make use of the concept of `percentage’, as an interest or tax rate. Most of us know what a percentage is, and indeed have known since school days, yet this chapter is all about percentages and the way that they work.
Before you turn to the next chapter, having decided that there is nothing new here, try the following simple problems.
Don’t actually try to work them out, after all that’s what a spreadsheet is for, just try to convince yourself that you could, or could not, work them out.
Even if you can solve these problems without any difficulty are you sure you know exactly how you solved them?
One of the difficulties is that we become accustomed to manipulating percentages without being entirely sure of the rationale behind what we are doing.
This is fine until someone challenges you to demonstrate that the result you have reached is indeed fair and reasonable!
Spreadsheets make it easy
Percentage calculations are easy. They are always easy and there is never any need to be in doubt as to why or how they work.
Spreadsheets take away the need to be good with arithmetic because they will do the sums for us - no matter how complicated or extended, and never get the wrong answer.
This allows us to concentrate on the way that the calculations work rather than the petty arithmetic that they lead to.
Throughout this book we will present ways of thinking about financial calculations that make it easy for you to understand what is happening. These ways are not always the best and most efficient in actually calculating a numerical result but this seldom matters.
Before spreadsheets it was often necessary to find simple, and sometimes approximate, ways of calculating a result. If it had to be done using a calculator or worse, in the head, then it had to be efficient rather than perfectly accurate or, of even less importance - understandable.
You will not find any such tricks or approximations in this book because understanding comes top of the list of priorities.
Why, you may ask, is understanding financial calculations so important?
The answer is that only by understanding the basis for financial calculations can you have any hope of interpreting the results that they produce. Only by understanding can you hope to discover new ideas and new ways of doing things. Understanding is always, and in this case literally, worth something!
The only disadvantage of this approach is that you may find it difficult to see how some of the more ad-hoc methods that you might have been taught fit in. In most cases it is better to forget ad-hoc methods for the moment. In time you will most probably see how they derive from the more direct approach or you will simply forget about them!
Notice that it is assumed that you know how to use a spreadsheet - Excel, Google Sheets or LibreOffice say. The financial functions described in this book work on most spreadsheets perhaps with minor and trivial differences.
A percentage is simply a fraction expressed as the relevant proportion of 100.
For example, 1/2 or 0.5 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 and in general we are more familiar with dealing with percentages than their underlying fractions.
For example, a discount of 10% always sounds friendlier than a discount of 0.1.
When it comes to working with spreadsheets however there are many advantages to the fractional form of a percentage. The reason is that while most spreadsheets understand percentages and allow you to enter and use them directly it is only when you enter the fractional form of the percentage can you be 100% (or should that be 1) sure that you are doing the right sum.
For example, to find 50% of the contents of cell A1 you can simply type:
but it is important to realise that this is the same as:
This situation is rather worse when it comes to storing percentages in cells and then using them in other calculations.
For example, if B1 contains a percentage, for example 50, then the formula to find that percentage of A1 is:
The danger here is that, while the B1 seems essential to the calculation, the 100 will soon be forgotten!
Leaving out the necessary factors of 100 is a common error in percentage calculations, but one that is usually quickly spotted and corrected due to the size of the result.
A more important reason for preferring to work with percentages as fractions is that it makes formulas look simpler by eliminating the need to divide every percentage by 100.
Also, we will see in the next section, spreadsheets actually work better with fractions even when they appear to be working with percentages.