Chapter 1

Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.

 Financial Functions

 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.

1. Understanding Percentages
   Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.
   
   
2. Interest Simple and Compound
   We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment. The ideas of Present and Future Value PV and FV are introduced. 
   
   
3. Effective Interest Rates
   We explore the idea of the `effective’ annual interest rate and then on to the Effective Interest Rate/Annual Percentage Rate, the much quoted EIR or APR.
   
   
4. Introduction to Cashflow - Savings Plans
   In the first of three chapters covering the way in which interest rate affects cashflow we explore savings - but first we introduce some general ideas that apply equally to annuities and repayment loans.
   
   
5. Cashflow Continued - Annuities
   We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects 
   
   
6. Exploring Repayment Loans
   Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.
   
   

7. Present and Future Values
   The principles of present and future value apply even if the cash flow is irregular. The calculations are just a matter of breaking down the cash flow calculations into simple steps.
   
   

8. Investment Analysis
   How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.
   
   

9. Advanced Investment Analysis IRR and MIRR
   The IRR is perhaps the most complicated of the measures of the value of an investment with an irregular cash flow. Understanding exactly what it means is a good step toward making correct use of it. 
   
   

<ASIN:1871962013>

<ASIN:B07S79ZVMQ>

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.

• If you know that the cost is $75 including a 10% discount what is the undiscounted selling price?
  
  
• If you pay tax at a rate of 25% and there is tax relief on a loan subject to interest at 15% what is the effective interest rate?
  
  
• 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?
  
  
• If sales tax is 5% and the tax inclusive price is $125, what is the selling price before tax?

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. 

Simple percentages

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:

=50%*A1

but it is important to realise that this is the same as:

=A1*50/100

or:

=A1*0.5

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:

=A1*B1/100

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.

<ASIN:1420843044>