Setting up a Canadian mortgage in GnuCash

[Barry Ferg]

I just wanted to relate my experience setting up mortgage payments in
GnuCash.  Hopefully this will help any other users with similar
problems.

Here is the problem: Canadian mortgage interest is compounded
semi-annually.  However, the Loan Druid and associated PMT() functions
all assume that the payment and compounding periods are the same, so
they won't work.

One way to calculate a Canadian-style mortgage is outlined on this
page: http://www.hughchou.org/calc/formula.html - this calculates a
periodic rate that might be usable in a conventional PMT() call, but I
couldn't get it to work. In the end, I figured the easiest (only?) way
to solve my problem was to write some scheme code to do the
calculations in the scheduled transaction.

Lucky for me someone else had beaten me to it.  In a patch submission
from two years ago, Ludovic Nicolle had added some code to fin.scm
that implemented a much more flexible set of payment calculation
functions.  Download the patch from the attachment list for this bug
report: http://bugzilla.gnome.org/show_bug.cgi?id=168700.  To install
it, gunzip the file, go to the directory in which fin.scm is located
(/usr/share/gnucash/scm on Ubuntu) and run "patch -p2 < <patchfile>".
The patch applies cleanly on GnuCash 2.0.1.

Once the patch is installed, use the cpd_pmt, cpd_ppmt and cpd_ipmt
functions in your scheduled mortgage payment transaction the same way
as the druid uses the original functions.  (Personally I only use the
cpd_ppmt function - my bank lets me know what the total payment is so
all I need to do is split out the interest and principal amounts.)
The difference is that the new functions accept both a compounding and
payment frequency.  They also take an annual interest rate rather than
a per period rate.  Read through the patched fin.scm file for some
further documentation.

I'd love to see this patch included in a future GnuCash release.  My
situation is definitely not unique and the functions in fin.scm could
certainly stand to be extended and improved.