How to create mortgage SX that will handle sporadic extra payments?

[David Carlson]

On Thursday, 7/18/2013 8:24 PM, prl wrote:
> Hi, Stephane.
>
> Something similar to this problem was looked at in the "Setting up
> mortgage payments" topic in the forum in January this year.
>
> I have a similar kind of mortage account to what you describe:
> variable interest, fixed regular payments, able to make advance
> payments at any time, and able to draw against advance payments at any
> time. Interest is calculated on a daily basis and debited monthly in
> the mortgage account. I have also taken the option to pay fortnightly
> rather than monthly, so that my repayments don't correspond to the
> dates interest is debited, and I have two months each year where I
> make three, rather than two, repayments. My interest rate can also
> potentially change at any day of the month, and need not be aligned to
> either a repayment or an interest debit.
>
> To get a calculated approximation to the interest due, the SX would
> need to have access to an account-balance-at-date function or
> something similar. In the discussion in January, I was told (by Derek,
> as it happens) that while there wasn't such a function in Gnucash, it
> wouldn't be hard to implement one.
>
> If that was the case, it would be possible to get an approximate
> interest amount for the month with an SX that estimated the interest
> as current-balance-at-transaction-date * monthly-interest-rate. The
> actual interest amount would need to be adjusted when you received
> your statement.
>
> I simply read the interest payment from my monthly statement and put
> it into Gnucash manually. That means I have no independent check of
> whether the amount is correct, though. However, mortgage interest
> payments and interest rates generally vary so slowly (in the absence
> of any really large advance payments) that the interest charge for the
> previous month is a fairly good predictor of the current month's
> interest.
>
> You could, then, make the interest payment value in the SX a fixed
> amount and review it every three or six months, or after any large
> pre-payment (or draw-down). That would probably be nearly as accurate
> as using the end-of-month balance and interest rate and would be a
> more conservative (i.e. larger) estimate than using the end-of-month
> calculation. Again, the actual interest amount would need to be
> adjusted when you received your statement.
>
> Using the previous month's interest as an estimate of the current
> month's gives some verification of once-off errors in interest
> charges, but it doesn't give any indication of small or systematic
> errors.
>
> To get an accurate monthly interest value for the kind of mortgage in
> question would probably involve some fairly heavy-duty Scheme
> programming.
>
> Unfortunately, this kind of mortgage doesn't seem to be common enough
> in the countries where most of the developers live.
>
> On the simpler matter of reporting on which loans are contributing to
> what interest costs, in Gnucash you can set up whatever accounting
> structure you like for interest expenses. So you could set up separate
> Expense:Interest:Mortgage and Expense:Interest:Car) accounts (and
> perhaps others, like Expense:Interest:CreditCard. Of course, if you
> want to have these reported correctly for the whole history of the
> loans, you'd need to change the Expense:Interest account for all the
> transactions imported from Quicken. If the transactions in Quicken
> have been imported with a consistent Description or Memo field, then
> it's easy to isolate them using Search. Once you've done that, it's
> easy, though tedious, to change the Expense account entry for the
> transaction.
>
> Cheers,
> Peter
>
> On 19/07/13 00:38, Derek Atkins wrote:
>> Hi,
>>
>> kart <stephane.cyr1 at gmail.com> writes:
>>
>>> Hello,
>>>
>>> I have been using Quicken for 16 years. Few months ago I decided to
>>> switch
>>> to GnuCash. I imported my 16 years of Quicken data into GnuCash. This
>>> exercise made me realize I made some mistakes in Quicken that I now
>>> have
>>> corrected in GnuCash.  So far I like GnuCash.
>>>
>>> I have few questions related to loans.
>>>
>>> In Quicken all my loans where setup to transfer the interest amounts
>>> into
>>> the account Expense:Interest.  In Quicken I did not specify sub
>>> accounts
>>> like Vehicle and Mortgage. I would like to know the Interest amount
>>> I spent
>>> for a given loan. I tried with different reports but wasn't
>>> successful. I
>>> believe it should be possible with a filter with the 'from' account.
>>> Would
>>> you please explain me how to setup a report for this.
>>>
>>> The bank allows me to make additional payments which are reducing the
>>> outstanding principal on my mortgage. With Quicken I was simply
>>> creating a
>>> transaction to transfer money from the savings account to the mortgage
>>> account. This was reducing the current principal amount and Quicken was
>>> properly calculating the P/I split for all subsequent payments.
>>> Quicken was
>>> actually tracking well the loans and a small adjustment of few
>>> dollars was
>>> required once or twice a year. I would like to achieve the same with
>>> GnuCash. How can I make GnuCash to recalculate properly the P/I
>>> split for
>>> subsequents payments following an extra manual payment?
>>>
>>> My mortgage with the bank is with a variable interest rate with a fix
>>> payment amount. The bank is advising us one or two weeks ahead prior
>>> changing the interest rate. In Quicken I was using the loan editor
>>> and I was
>>> scheduling the interest rate change which allowed Quicken to properly
>>> calculate the P/I split for subsequent payments. I believe in
>>> GnuCash the
>>> interest rate can be changed for SX by editing the SX, going to the
>>> Template
>>> Transaction tab and changing the interest rate in the pmt, ppmt and
>>> ipmt
>>> formulas. Is it the proper way to change the interest rate? Is there
>>> another
>>> way of doing this? If the interest change occurs in between to
>>> payments, is
>>> it possible for GnuCash to make the proper calculation of P/I split
>>> since
>>> the last payment with the old interest rate and calculate the P/I
>>> split with
>>> the new rate for the remaining days until the next payment?
>>>
>>> Thank you in advance for your help.
>> Alas, the GnuCash SX system does not handle pre-payments.  The
>> amortazation is determined ahead of time and the P/I split is not
>> computed on the real account balance but instead based on the original
>> principal amount and which payment this is.
>>
>> What this means is that if you ever have extra principal then all the
>> P/I splits from then on will be incorrect, and there's really no way to
>> fix that.  You'll just have to manually adjust the splits after you get
>> the correct P/I splits from your statement.
>>
>> This has been a long-standing issue.  To fix it requires a
>> "balance-as-of-date" function that can get put into the SX, as well as a
>> way of specifying the account-in-question.  Alas, this is a lot of work
>> and nobody has offered to work on it.
>>
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>> -derek
>>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
It is possible but not trivial to build a custom spreadsheet to
calculate interest using what is hopefully an independent implementation
of the same rules that your lender put into your loan agreement.  You
can implement it in such a way as to accommodate unscheduled payments.

I did that recently to check on a credit card lender that I thought
might be calculating interest incorrectly.  I found that there are
several possible sources of error that may cause my calculations to
differ from the lenders.  The types of error might be in counting days
if daily interest is used, rounding errors vary depending on the
technical number types used (floating point has several definitions, and
there are numerous variations on integer and decimal number types, all
with different types and amounts of error), and which program is making
the calculations to name a few.  It certainly is not as simple as what
we learned in high school.

Even if GnuCash gets 'fixed' as Derek notes above, and if you can write
a formula that can be implemented in a GnuCash scheduled transaction, it
will probably still disagree with the lender because different internal
methods of making the calculations are used.  GnuCash uses a very
accurate internal method of calculation.  That method is different than
methods used in most other software, but it still is not error free.  In
fact, some definitions of error even disagree with GnuCash's definition
of error, yet each method is as accurate as stated by the respective
(and highly respected) developers.

You will never get the lender to divulge the nitty-gritty of the
calculations that he used, other than that he is right and you are wrong.

If your calculation matches within several cents per month, that is
about as good as you will get.  Thus users should plan on making manual
adjustments, possibly every month.

David C