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

Peter Underwood peterunderwood1 at gmail.com
Tue May 3 00:55:22 EDT 2011


One more try as I must have asked the question badly the previous two times
which elicited not one response...

I am trying to convert my personal accounting to GnuCash from Excel
Spreadsheets with self-written macros as Microsoft make it harder and harder
to bring macros up to the next version of Office.

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. To explain:

The house purchase/mortgage was closed in 2005. The U.S. system for the
start of a mortgage is based on a HUD Settlement Statement. This includes a
lot of stuff irrelevant to accounts now, but the mortgage itself does apply.
Now it should be simple because it's a fixed rate mortgage. However, the
Assistant seems to assume that there is simply a capital sum borrowed,
interest and escrow whereas in fact mortgages generally don't start like
that on a HUD statement which might go something like this:

Deposit or earnest money.
+ Principal amount of new loan.
= Total Paid by/for Borrower

Cash at Settlement from or to Borrower consisting of:
Gross Amount due from borrower (this is going to be the capital value needed
for the tangible asset account)
- Total Paid by/for Borrower (see above)
= Cash from Borrower

But then there are fees to set up the mortgage (all paid at settlement to
the lender):
Loan Origination Fee.
State Residential Loan Fee.
Flood Fee.
Administration Fee.

Then the interest is not as simple as GnuCash wants it to be. Despite being
a fixed interest rate paid monthly, the HUD Statement will show that the
first amount of interest is actually paid at settlement and pays the
interest until the end of the month.

Then escrow is somewhat similar in that (a) there are advance amounts to be
paid towards Insurance and Taxes i.e. 3 months of Hazard Insurance and 3
months of Property taxes. (b) Buyer's share of Property taxes from
Settlement Date to the end of the Tax Year.

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?

If I could manually enter transactions in arrears, I could probably manually
bring the mortgage transactions up to the start date despite that being
tedious. But when I try to "insert" a transaction at an earlier date, it
doesn't affect the results of the scheduled transactions set-up by the
Assitant so the current information is not correct. This "insertion" of a
transaction(s) is needed most months for a variety of reasons. For example,
a bank charges an unexpected fee and I don't find out until a number of
transactions have been entered at later dates. Or a Credit Card company
automatically withdraws from the bank less than the scheduled amount. These
need to be entered later but to appear on the account on the date they
occurred. I tried simply entering the transactions and putting the earlier
date, but they remain out of date order.

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 :(

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 :)

Thanks.

Peter


More information about the gnucash-user mailing list