loan/mortgage repayment via sched xactions: feedback request

Derek Atkins warlord@MIT.EDU
03 Jul 2002 07:47:41 -0400


Josh,

I'd just like to point out something that completely slipped my mind
until reading this.  Before I do, nice work -- a very well-thought-out
plan.  Having said that...

> Reference
[snip]
> * IPMT( rate, per, nper, pv, fv, type )
>   IPMT calculates the amount of a payment of an annuity going towards
>   interest. Formula for IPMT is:
>   IPMT(per) = - principal(per-1) * interest_rate
>   where:
>   principal(per-1) = amount of the remaining principal from last period.
[snip]
> * PPMT(rate, per, nper, pv [, fv, type] )
>   PPMT calculates the amount of a payment of an annuity going towards
>   principal.
>   PPMT(per) = PMT - IPMT(per)
>   where: PMT is payment
>          IPMT is interest for period per

What I forgot to mention is that IPMT really uses the _current
principal balance_, not the _expected_ principal balance.  In other
words, if you pre-pay (over-pay) principal, your interest will be
lower than expected.  This means the SX cannot be run in a vacuum; it
needs to be able to look at the current balance of the
"loan-principal-account" so it can compute the actual interest.

Another thing that I didn't think about is how people want to use
this.  I see people doing something like this:

Desc/Memo      |             Account         |       Credit   |       Debit
---------------+-----------------------------+----------------+--------------
Repayment      | Assets:Bank:Checking        |                | 2000.00
Escrow         | Assets:Loan_Escrow_acct     | 241.26         |
Interest       | Expenses:Loan_Name:Interest | =IPMT(I,n,N,P) |
PMI            | Expenses:Loan_Name:Misc     | <pmi_value>    |
Principal      | Liabilities:Loan_Name       | <NET>          |

FreqSpec = 1 month
-----------------------------------------------------------------------------

In other words, I'm sending them a check for $2000.  The escrow
account get's 241.26 (the "escrow amount"), the Interest is computed
off the current principal balance (which means it needs a hook into
the account), PMI, if any, is again a 'fixed' value, and all the
rest is applied to principal.

> Priorities, Plan
> 
> The above represents an "ideal" set of extensions to the SX framework to
> enable multiple "enhancement"-level functionalities.  Therefore, the
> following is the prioritized schedule, with annotations:
> 
> 1. Functions [PMT, [IP]PMT] in exp_parser; implicit variables [n].

Note that you really don't need implicit variables per-se.  The
functions are iterative, not recursive, so you only need 'n' in terms
of looking forward (i.e. future transactions).  PMT is a constant
unless the interest rate changes, IPMT is computed off the actual
current balance, and PPMT is just PMT-IPMT.  (Note that I don't know
how PMT is re-computed on Adjustable Rate loans in the face of
over/pre-paid principal).

> 2. [Visual-only] SX grouping
> 3. Loan-repayment creation Druid
> 4. SX-only static vars
> 5. SX-only periodic vars
> 6. SX-group vars, var_frames
> 
> After the completion of item 4, the feature can safely be called "finished".
> Items 5 and 6 only serve to increase the robustness of the facility and make
> the user's life slightly easier, at the cost of making _my_ life harder. :)

Sounds like a reasonable plan to me.

-derek
-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord@MIT.EDU                        PGP key available