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

[prl]

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
>