Calculating true interest – the spreadsheet

How was the interest rate in Figure 8 calculated?  Unfortunately, there is no known formula for calculating interest rate directly, by substituting all the other known variables, i.e. the principal and the repayment schedule.  The only way available to us is by trial and error.  This is why comparison exercises between various loan products have proved so difficult before the advent of computers. 

Fortunately, spreadsheets have come to the rescue.  Every serious student of finance should familiarise themselves with this vital tool, which fortunately is very easy to learn and use.

The disk attached to this book includes all the key examples in spreadsheet format.  Some spreadsheets are essential to achieve a solution, for example when comparing any two repayment schedules, in order to identify which one is the best value for money.

What is a spreadsheet?  
For the sake of any spreadsheet novices, it consists of a matrix of cells – rather like a noughts and crosses game but with many more cells, similar to the tables shown earlier.  Letters along the top, and numbers down the side, are used to refer to each cell.  So A1 is the top left hand corner, A2 is the next cell down and B2 the next cell right and so on.

You can then enter figures, text or mathematical formulae in any cell.  But the components of a formula can be referred to by their cell addresses.  If you enter say the number 5 in cell A1, and enter 4 in cell A2, then cell B2 could contain the formula A1 x A2, which will display 20, i.e. 5 x 4.  If you change cell A1 from 5 to say 8, all the other cells are automatically recalculated and B2 will instantly display 32, which is the product of A1 and A2, 8 x 4. 

Spreadsheets contain dozens of really useful tools and features that enable you to construct a complex schedule quite quickly.  They are fun to build and invaluable for getting fast and meaningful results.  

Goal Seek
So back to how this interest rate in Figure 8 was calculated.  One of the tools available to a spreadsheet user is Goal Seek (as called in Microsoft’s latest spreadsheet named Excel; it is called Backsolver in Lotus 123).  This is a totally automated trial and error process.  We need to find an interest rate that amortises the final capital to zero after 12 months given the fixed monthly payment.  Goal Seek will do this in a flash. 

The hard way is to simply enter a succession of trial figures, recalculating each time and then improving your next trial figure, until you reach the end capital you want – usually zero but not always.  The formal way of doing this is called iteration.  This is a structured trial and error process that constantly recalculates a given formula, making finer adjustments to the required parameter on each recalculation, until a result of sufficient accuracy is achieved.  Goal Seek does this automatically.