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