----------------------------------------
Apr
i L
28500.00
28343.30
28164.65
May
28196.88
28040.17
27861
.53
June
27893.76
27737.05
27558.41
July
27590.63
27433.93 27255.29
August
27287.51
27130.81
26952.17
September
26984.39 26827.69 26649.04
October
26681.27
26524.57
26345.92
November
26378.15
26221.44
26042.80
December
26075.03
25918.32
25739.68
January
25771.90 25615.20
25436.56
February
25468.78
25312.08
25133.44
March
25165.66
25008.96
24830.31
Year
2
3
End-af-year
baLance
24862.54
24705.84
24527.19
•
•
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
A
I B I C
REPAYMENT
TABLE
=============
Yea
r
D
2
E
3
Exampes
•
Figure
5,8
The
repayment
table
If
you
have a mortgage, type
in
your own figures Don't spend too much time over the
results tor the first
few
years - they make rather depressing reading
l
[C15]
"REPAYMENT
TABLE
[C16]
rept("=".Len(c15))
[B18]
"Year
[C18]
row=co
L
()
-2
[columns C
to
AK)
[B19]
row=rept("-".widthO+1)
[columns B
to
AKj
[B20]
coL=month(rowO-20+$mnth.startl
Irows 20
to
31]
These entries
set
up the headers for the table: now
we
must add the formulae that
will
calculate the values.
We
start with the
first
Item
which
IS
the
Initial
amount
due.
It
is
calculated by adding the
first
year's interest
to
the amount of the loan.
[C20]
mor.loan*(1+mor.int)
Then the
rest
of the
first
row
IS
calculated
by
subtracting the yearly payment and adding
the interest for the current year These values should not be calculated beyond the year
in
which the loan
IS
repaid and
we
allow for this by using the if( ) function.
If
the year
number (given by col(
)-2)
is
greater than
the
term of the mortgage, zero
IS
placed
In
the cell.
[D20]
row=if«col()-2»$mor.term.0.(c20-$ann.rep)*(1+$mor.int))
[columns D
to
AKI
The remainder
at
the table can be filled with a single formula.
We
fill
the
first
cell with
a formula which
Just
subtracts
the
monthly repayment from the amount
In
the
cell
above.
Again
we
use the if( ) function
to
prevent the calculations extending beyond the year
in
which the loan
is
repaid.
[C21]
if
« co
10
-2)
>$mo'r.
te
rm.
O.
c20-$mon.
rep)
You
can then use the Echo command
to
copy the formula from cell
C21
to
the range
C21AK31.
Ech
0 • eeL L c
21
•0 v
err
an9e c
21
: a
k31
We
can now complete the table by adding a
final
row
to
give the outstanding balance
at the end of each year
It
is
probably a good idea
to
add a copy of the
year,
from row
18,
for easy reference.
[B33]
row=year.term
[columns B
to
AK]
[A35]
"End-of-year
ba
Lance
[C35]
row=i
f « co l 0
-2)
>$mor.
term.
O.
c31-$mon.
rep)
[columns C
to
AKI
12/84
31
To Next Page
Loading...
Need help?
General
