Setting up a mortgage and transactions entered in arrears (4 numbered questions)

[Tommy Trussell]

Peter...

On Mon, May 2, 2011 at 11:55 PM, Peter Underwood
<peterunderwood1 at gmail.com> wrote:
> I am trying to convert my personal accounting to GnuCash from Excel
> Spreadsheets with self-written macros
...
>
> I was happily entering information from January 1st, 2011. However, when I
> entered the mortgage details in the Mortgage/Loan Assistant, I got a
> complete fail.
...
>
> I read Help and Website guides. I tried setting this up using the
> Mortgage/Loan Assistant. I understand that the fees and other closing costs
> are going to be entered as expenses. But that is in 2005 and my accounts
> start January 1st, 2011.
>
> Question 1: As I can't "invent" a mortgage/escrow etc as at January 1st,
> 2005, how do I enter information so that the scheduled transactions will
> show as the actual figures?
>
> At different times in the life of the mortgage, the sum required for escrow
> changes. The Assistant assumes I know the split between taxes and insurance
> whereas in fact the only time I know them is when they are due and paid out
> of escrow and a recalculation is done by the lender.
>
> Question 2: How do I enter the escrow details without knowing the split
> between tax and insurance in the monthly payments?
>
> Question 3: How do I enter the various amounts in arrears to bring the
> escrow payments up to date at the start of my new (GnuCash) accounts?
>
...
>
> Question 4: If I fail to enter a transaction of any kind, how do I "insert"
> it at the correct date and then re-balance the accounts?
>
> I really want to use GnuCash, but if I can't get these things started and I
> can't find "how-to" answers, all the time I have spent setting up the other
> accounts/sub-accounts will have been wasted :(

If I understand your question 4, the answer is you can enter and/or
edit whatever transactions you need in the correct accounts.

The answer for the other questions, I think, might be that you COULD
just use the assistant and have it generate ALL the transactions since
you created the mortgage since 2005, edit them to reflect reality, and
rebalance your bank account to clear the transactions that occurred
before you started using GnuCash. To do this you must review your
mortgage statements and enter all the escrow money paid out and in.

HOWEVER I will tell you what _I_ did...

The Mortgage/Loan Assistant (formerly called a "druid") is not "smart"
enough to handle some important things, such as making additional
payments on principal. It also doesn't help you when there are the
(inevitable) changes to escrow payments, etc.

It CAN help you set up a few of the accounts you are interested in,
and get you started. After that, you will want to modify the recurring
transaction occasionally (such as when the Escrow payments change,
often once or twice a year).

I suggest you can put in FIXED principal and interest payments and
IGNORE the fact that they are not correct each month; remove the
formula in the scheduled transaction and insert a fixed amount that
closely approximates the amounts, making sure the TOTAL is correct
(the principal plus interest plus escrow). Assuming you have a fixed
rate, the mortgage company or bank tries very hard to make the total
fairly consistent from month-to-month, and for your purposes you want
the bank transaction to always be correct when balancing the bank
statement, whether you pay by a draft or writing a check.

Every three or six months (however often your lender sends you an
updated statement) go back and update the interest and principal
amounts to reflect reality. You can even "balance" the principal
account if you want, but you are more likely to want to balance your
Escrow account, as well as enter any payments that have been made from
it (usually insurance, property taxes, and whatever else gets paid out
of Escrow).

This is the strategy I used for my mortgages -- it would have been
nice if the assistant could have handled the additional principal
payments I made each month (which cut many years off of the life of
the mortgage), but it didn't, and I didn't find it too burdensome to
update the amounts each time I got a statement.

The reason I suggested you might not need to balance the principal
account "to the penny" with each statement is that in just about any
instance it matters more what the mortgage company says the amount is
... when you get ready to payoff, you will get a payoff quote based on
a certain date, and after you pay them they will ultimately refund any
Escrow and any Principal overage, and THAT is where you will want the
Escrow and Principal accounts to balance to zero. Before that, a
ballpark amount is probably good enough. And for tax purposes each
year, it matters more what the mortgage company reports to the
government than what you think you paid, so the interest payments
don't need to balance, either.


I know this may not be what you were hoping to hear... it WOULD be
cool if the mortgage assistant could handle all this stuff properly. I
don't know whether it would be best to model it on the HUD statement,
however... in my experience (based on only two mortgages in my
lifetime so far) the HUD statement reflects the situation at the time
of the sale (a one-time event) but the ongoing payments get reviewed
whenever the mortgage gets sold to another bank or the Escrow is
readjusted based on its outpayments.

SO balance the past transactions whenever you get a new statement, and
occasionally readjust future scheduled transactions to match where you
are on your individual mortgage payment schedule.

> Any ideas, suggestions will be gratefully received! And if I am asking the
> question in the wrong way and someone actually sees my questions this time,
> please advise me how to ask them :)

I think your questions were fine. Since you have been using a homebrew
spreadsheet, you probably know a lot more about the "proper" way to do
this anyway, so you might consider specifying how the mortgage
assistant could be improved. Last I remember the discussion was that
the GnuCash backend doesn't (yet) have an important function that
would be needed for scheduled transactions to handle additional
principal payments.

There may be some existing bugs and/or feature requests you could vote
for... and maybe you even have the coding skills necessary to push the
envelope yourself...

> Thanks.
>
> Peter

I hope that helps.

              --Tommy Trussell

> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>