home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Archive Magazine 1995
/
ARCHIVE95.iso
/
discs
/
pipeline
/
4_06
/
Iteration
/
ReadMe
< prev
Wrap
Text File
|
1991-02-17
|
4KB
|
80 lines
%OP%JUN
%OP%DP0
%OP%DFT
%OP%PL0
%OP%HM0
%OP%FM0
%OP%BM0
%OP%LM4
%OP%RIY
%CO:A,72,72%
%C%Lump Sums & Monthly Payments
%C%by Malcolm Brown
[Note from Gerald L Fitton: PipeDream does have financial functions
(have a look at pages 157 to 160 of the user guide) which could have
been used to solve Malcolm's problem more elegantly. However, I have
included his contribution because I believe it to be an example of the
use of PipeDream's iteration facility which is neither too simple to be
useful nor too difficult to understand what is happening. Generally,
mathematicians use iteration only if a formula contains the wanted
variable 'implicitly' in such a way that the formula can not be
'inverted' (solved) to obtain an 'explicit' solution for the wanted
variable. Quadratic equations can be solved 'explicitly' but quintics
can not. If you send me a solution to Malcolm's 'Amortisation Annuity'
problem using PipeDream's built in financial functions then I will
include in on one of the quarterly PipeLine discs. A 'Repayment
Schedule' (showing just how much is still owed at any time) could be
useful for working out unfinished 'Hire Purchase' calculations. - GLF]
It recently occured that two related problems presented themselves
which may reappear and so a computer solution seemed worthwhile. A
family member was in need of a loan and money was available. It seemed
sensible to make the loan through the family. But...
The sum, if it were to remain in the bank would attract interest. The
loan would be repaid in monthly instalments which presumably would not
acrue so much interest. We simply wanted to break even - assuming a
static interest rate.
The requirement, therefore, was simply to calculate what monthly
repayment would be required to match the (principle + interest) at the
end of the term. [This is the classic 'Amortisation Annuity' problem -
Have you an elegant solution for publication? - GLF]
The first problem was to calculate the compound interest (A10 on the
loan file). Why don't spreadsheets have a built in function for this
formula? I remember calculating this at school 30 years ago but I
could not remember the formula and it took some prising out of the
local library.
The second was how to calculate the monthly repayment (B10) so that the
value (C10) equaled the return (A10). So that at the end of the loan
period the bank balance would look exactly as it would have done if
nothing had happened. What a frustration! Iteration is not the
easiest process to get right - the thing would not move at all or
diverged violently. Eventually I abandoned all my principles and read
the manual. There it was - page 266 - the solving of simultaneous
equations. And so (B10) and (F12) check each other until they come to
some amicable arrangement. I had to use the SGN (F10) as the modifier;
just using the 'Variance' (E10) brought violent fluctuations. It
worked, but it took hours, 5000 iterations. This was no good - I could
do it on paper quicker! Some sort of guess would have to be made.
The third problem was what figure to put in. Eventualy I settled on
calculating the simple (interest + principle) and dividing by the term
in months - (E8). I hope this does not reveal some ghastly flaw but
this proved too high, so the whole thing is multiplied by 0.94 to get a
near guess for all variations.
Out of interest - pun intended - the amount paid by the borrower, their
saving and interest paid is also calculated.
What a lot of words for 15 cells of calculation.
The lump sum versus monthly payments calculation only needed an answer
to the nearest year and so did not need to be so sophisticated. It
could be made more sophistaced by computing the actual break-even
figure and then calculating the time, or, even better, exporting it to
PresenterII (which I haven't got) and producing a graph.