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.
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.
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.
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
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.
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.