Chapter 2

We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment.

 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>

The idea of borrowing money for a specified rate of interest or earning interest on an investment.is something that we are all familiar with.

Interest is a percentage, but one that has a time-based component. Interest is calculated and paid at a regular intervals and this makes its behaviour rather more varied than a simple static percentage.

Interest - a percentage rate

Many financial arrangements are specified in terms of interest which is a percentage of the total per time period.

Interest is a percentage rate - so many percent per month, so many percent per year and so on. It is a rate in the sense of something that involves the passage of time - miles per hour, kilometres per second and 10% per month are all rates.

In the days before legislation tightened up on how interest rates were quoted, it wasn’t uncommon to find quotes of 10% interest, but without any mention of the time period involved - and 10% per day is a very different amount of money from 10% per annum.

Thus there are two important components of any interest specification: 

1. the percentage to be paid
2. the time period governing how often it is paid 

This view of percentage as a rate makes clear some of the difficulties in store for us.

For example, if you can make a return of 1% per month, 3% every quarter or 11% per annum, which is the better investment?

A small bank loan is offered at 20% per annum, but a credit card load costs only 2% per month which is better?

Clearly converting between interest quoted for different time periods is something that we are going to have to examine. But first we need to look at the way that interest is calculated.

Lenders and borrowers

Interest is paid on deposits and charged on loans.

These two situations are in fact identical from the point of view of calculating interest.

In each case there is an investor/lender who provides the lump sum - the principal - and a borrower who pays interest on the loan/investment.

It doesn’t really matter if the borrower is in fact called a bank, an investment trust or John Smith, the cashflows are the same.

If the principal is $M and the interest rate is I% the interest due each payment period is simply:

=$M*I

Notice that we are not considering the repayment of the loan nor the accumulation of interest.

If the principal is a loan then it is assumed that the whole principal will be paid back as a lump sum sometime in the future - i.e. it is an interest only loan. If the principal is an investment then the interest is paid out to the investor and not reinvested.

The key factor is that the interest is paid in such a way that the value of the principal, i.e. $M, remains constant over time.

In this case the amount of interest paid in each time period is also constant and this results in a very easy to manage situation - simple interest. 

Present and Future Value

It is common practice to use the terminology Present Value, or PV, for the sum of money involved at the start of a loan or investment and Future Value, or FV, for the final balance.

In other words, FV is what results after interest has acted on PV.

This jargon applies equally to investments or loans:

• In the case of an investment the amount of money that is deposited or invested is the PV and the final balance is the FV
  
  
• In the case of a loan the sum borrowed is the PV and the amount finally paid back is the FV

Other terms, such as principal, are used for PV but for the remainder of this book PV and FV will be used to denote the value before and after the action of interest respectively.

Notice that the relationship between PV and FV depends on the type of situation we are considering.

For example, in the case of simple interest of I% over n interest bearing periods the FV is given by:

FV=PV+PV*I*n

or:

FV=PV*(1+I*n)

You should be able to recognise this as just increasing the PV by I*n%.

Comparing simple interest

In the situation where interest is paid on a PV that does not change over time, it is very easy to compare different interest rates.

For example, if a deposit pays 2% interest per month then over a 12-month period the total amount paid in interest is simply:

=12*PV*0.02

or:

=PV*0.24

This implies that to receive 2% per month is equivalent to receiving 24% per annum.

This same reasoning applies to any interest rate over any time period.

• All we have to do to compare the rates is to convert them to the equivalent rate per annum

For example, 10% paid every six months, i.e. two interest bearing periods per annum, is equivalent to a rate of 0.10*2, i.e. 20% per annum.

In other words, for simple interest rates converting between different periods really is just a matter of multiplying by the ratio of the periods.

For example:

• 0.5%, i.e. half a percent, paid daily is equivalent to 0.5*365% or 185% per annum
  
  
• 1% paid bi-monthly is the same as 0.5% paid monthly
  
  
• a 50% return over 10 years is equivalent to 5% per annum

Notice that for all of these examples to be correct the situation must correspond to simple interest, i.e. the interest calculated is not added to the PV.

<ASIN:/0230348114>

<ASIN:1118510100>

<ASIN:0735672431>

<ASIN:0789748576>

<ASIN:0470242051>