[GNC] Mortgage & Loan Repayment schedule tool

[Stephen M. Butler]

On 1/11/20 12:26 PM, Michael or Penny Novack wrote:
> Do not fault the tool if it does not agree with the bank. I have
> written these things. There are simply too many assumption about how
> to do the calculation, where to round off, etc. << I ended up with a
> fancy version that allowed instructions where to adjust that could get
> agreement to a penny BUT it was not strictly speaking a "function" (a
> calculation) but a process using a repeated "trial and error" approach
> not unlike Newton's Method --- lenders frequently charge for an
> "amortization table" and I wanted to have that to be able to make
> additional payments of principle*.
>
> As for escrow amount calculation, this is really tricky. The bank will
> set an amount to fund an amount that will not only be sufficient to
> pay the taxes and insurance bills over the year but will do so on a
> "cash flow" basis. In other words, the fund will always have enough in
> it to meet these bills when they are due. The bank will recalculate
> ever year but based on the dates of the expected bills will tend to
> fluctuate more wildly than you expect**. I never wrote one of these,
> but again, a good candidate for repeated "trial and error*"
>
> Michael D Novack
>
>
> * If the amount you send in is the exact amount of the principle
> portion of the following payment then you can cross that payment off
> -- in effect you are jumping closer to the end of the mortgage.
> Especially when interest rates are high, as they were at the time, in
> early years you can cheaply buy time off the end of the mortgage.
>
> **( In other words, given an initial balance (in escrow) and making a
> series of payments at specified dates (the tax and insurance due dates
> and amounts) what is the least monthly input (or biweekly if that's
> what the mortgage payments are) that will prevent the escrow amount
> from going minus.) 


In my experience, some banks use a standard 30 day month and 360 day
year and are more easily matched by monthly calculations.  Other banks
use the actual number of days between payments (and whoa is you if the
post office delays a day or two in delivery) and adjust the interest due
accordingly.  Those are impossible to predict in advance.

So, I end up building a rough standard transaction and update it monthly
based on statements from the bank.  Sometimes I can update the scheduled
transaction before it gets applied (those that assume a standard 30 day
month or 1/12 of the annual interest rate).  Others I have to update the
posted transaction after the fact.

Your bank might do something different.

--Steve

-- 
Stephen M Butler, PMP, PSM
Stephen.M.Butler51 at gmail.com
kg7je at arrl.net
253-350-0166
-------------------------------------------
GnuPG Fingerprint:  8A25 9726 D439 758D D846 E5D4 282A 5477 0385 81D8