Mortgage
Payment The Hard Way: This
is the formula for a monthly mortgage payment (M) from the amount
borrowed (Principle) over (n) periods at an interest rate (i) per
period:
M = P [ i(1 + i)^n ] / [ (1 + i)^n – 1 ]. It is more easily
forgotten than remembered. This example is for readers who wonder about
unexpected tasks that T/Maker might handle. Most will not be attempting
to construct a table at this level of complexity. But hopefully they
can get the idea of how it works. Skip it if you like.
I
do know the payment accounting that is done each month. Interest is
added. The
payment is subtracted. That yields the remaining balance for the next
month. And so it goes for every period. T/Maker is programmable. With
the terms of a mortgage and a given monthly payment, it could do the
math. If the final balance ended up negative by more than a trivial
amount, it would mean the payment was too large. If it ended up too
positive, it would mean the payment was too small. Something called a
binary search is a smart way to deal with this type of situation.
First we need to find a low and a high estimate for the correct payment
(discussed later), then we take the average of the two, and do the
math. If the remaining balance is too negative the payment was too
much. We change the high estimate to the average we just used and start
over with a new average. If the balance is too positive, the payment
was too little. We change the low estimate to the average instead and
try again. If the balance was a trivial amount from zero, the payment
is right and we stop repeating loops. The term "Binary" is used because
the range of possibilities is cut in half with each iteration.
We will have three sorts of stages in this process: 1) Start Up:
calculate the correct value as in the previous example to compare with
this approach and set some nicely named memories with initial values.
2) Prep Next Guess: Prepare memories with values to run through all
payments and establish our next payment guess. 3) Run and Evaluate: Do
all payments, evaluate the remaining balance, and take the appropriate
action. We will do one trail for each stage which makes it easier to
see what is going on. A trail is a very flexible item.
Trail
1:
 
Startup:
When T/Maker begins calculating all memories are effectively zero. In
the first step, Pass is a memory which will be zero at the first
execution of the trail. A When Clause is used with the "not equal"
comparison to see what the story is. This test will fail since Pass is
zero, so the rest of step 1 will not be done. T/Maker will jump to step
2. Later when Pass is not zero, the rest of the step will be done and
T/Maker would EXit this TRAIL (EXTRAIL) and jump to step 1 of next
trail, namely, 2.
This
trail is meant to be executed only once. Steps 2 through 5 calculate
the correct mortgage payment in the way of the previous example. Step 6
puts the borrowed amount in the memory Amount. Step 7 divides the
interest per period by 100 so it is a rate as opposed to a percentage.
Step 8 puts the number of periods in the memory Months. In truth, it
could be quarters or years or whatever. Perhaps a memory named Periods
would have been more to the point.
Trail 2:
Prep
Next Guess: The first two steps of trail 2 are only done the first time
trail 2 is executed. Otherwise, the Low and High estimates for the
Payment are controlled in the third stage. The Low payment is
calculated to only pay back something less than the full amount of the
loan. Banks don't generally offer that. The High payment would pay back
the interest on the full amount every month plus the Low payment. This
would probably be called "sharking."
Step 3 displays the Low payment. Step 4 calculates and displays the
Payment based on the average of Low and High. Step 5 displays the High
payment.
Step 6 sets the memory Remains to full Amount (this is before the first
payment is made).
It also sets the Time to 1 (think of this as the current month/period).
And it adds 1 to the memory Pass. This makes sure we won't do the
one-time steps again and serves to count the total number of different
payment amounts that were tried before the correct payment was found.
Trail
3:
Step
1 calculates the remaining amount including new interest.
Step 2 subtracts the next payment from the remaining amount.
Steps 3 and 4 simply display memory values of interest,
Step 5 Regardless of the Time, If what remains is already less than
minus 5 the payment is too high. So the High value is reset to the
current Payment and the table is RESTART(ed) from the beginning.
However, this time Pass is not equal to zero so various steps will be
skipped.
Step 6 tests if the final payment was just made. If not, this trail is
RETRAIL(ed) which means T/Maker will jump back to the first step for
this trail and advance another time period.
Step 7 is only tested after all payments are made. It checks if the
remaining amount is greater than 5. If so the Payment was too low, so
the Low value is reset to the current Payment and the table RESTART(ed).
Otherwise it is mission accomplished! The amount remaining is between
minus and plus $5.
If there were a trail 4, T/Maker would have started on it.
Don't lose sight of the benefit that this example offers an oppotunity
for students to learn something interesting independent of this problem
solved. This can be done with many other subjects: statistics,
probabilities, etc. For
the record, no trail yet has used a
cell name except the example of how to use a cell name!
|
My Relevant Personal History: Six unlikely circumstances over a dozen years led me to create a means for in-document calculations specified in arithmetic. I published T/Maker which included that in 1979 and a Windows math-only part in 1991.
In 2020 a
system update killed both. Jumping ship to an algebra
spreadsheet would have been a
betrayal of decades of ideas, fond memories, and surprising success. I
decided to resurrect and expand the still unique syntax
and emphasize it for an ignored
group -- people with no knowledge of algebra. If I sometimes
sound like a madman on a mission, I will admit I am on a mission and
might actually be the one person in the world who seems predestined to
create
this page, at this time, with this approach. The history is there even though this does sound somewhat
like madness. Sketch
from Programmers At Work,
Microsoft Press, (c) 1983
The Quadratic:
