Rounding in Mortgage Assistant

[Marc Sherman]

On 11/10/2012 12:37 AM, Brian M. Sutin wrote:
> The current algorithm is useless if I can't make it agree with my bank.
> Could I add $0.01 extra principle to each payment?  I'm not sure how to
> do that, but it might work fix the issue for me.  At worst I could make
> it an extra entry.
>
> Your solution is definitely best, but I can't really wait for it to be
> implemented...

Here's what I do. I know what my exact mortgage payment is every 
half-month, it's a fixed amount. So I (manually) schedule a semi-monthly 
fixed payment from my bank account to my Mortgage liability account.

Then I schedule another semi-monthly transaction, with the description 
"Estimated Interest", from my Mortgage liability to my Interest expense 
accounts. This transaction uses the IPMT function: ipmt(rate:nper:pv:0:0).

The rate parameter is the periodic interest rate for your mortgage.In my 
case, my mortgage interest is compounded semi-annually, and I pay 
semi-monthly, so the formula for the periodic rate (where AR is the 
annual rate) is:

(power(1+AR,1/2)-1)/12

The nper parameter can similarly be calculated in Excel from your 
perdiodic rate (same as above), current outstanding balance (pv), and 
your fixed payments (pmt):

nper(rate, pmt, pv, 0, 0)

That scheduled iper function gets very close to the right number for 
interest which the bank charges on each semi-monthly transaction (within 
a few cents), so at any given time, my gnucash mortgage account is a 
pretty good estimate of the current outstanding balance on my mortgage. 
At the end of the year, I get a statement from my bank, and I use the 
gnucash Reconcile dialog's Interest transaction function to create a new 
"Interest estimate adjustment" transaction, which corrects the 
accumulated error over the year in my calculated interest estimates.

Whenever I make a bulk prepayment on the mortgage, or change my regular 
mortgage payment amounts, I have to re-do the ipmt secheduled 
transaction, recalculating the new NPER parameter using the new pv and 
pmt amounts. It's a pain, and it'd be nice if this were fully automated, 
but it does give me a good view of my current and future financial 
situation with the mortgage, which is the important thing.

- Marc