[GNC] mortgage calculator

[Stephen M. Butler]

By  fussing with the ipmt formula I was able to accommodate a regular 
extra payment.  I went into Libre Office to recalculate the number of 
months using the larger monthly payment.  Then adjusted the number of 
payments to an integer and recalculated the required monthly payment.

The wizard will setup the scheduled transactions and will use the ipmt 
function to generate the interest amount.

ipmt( .04490 / 12.00 : i : 240.00 : 51,630.34 : 0 : 0 )

The only thing you can't mess with in the function is the 'i' which is 
the month number for which this entry is being calculate.  The fields 
are separated by colon (:).  Here are the entries as I understand them:

Field 1:  in my case .04490 / 12.00.  This is the interest rate per 
period.  In my case I have an annual 4.49%.

Field 2:  The 'i' that I don't know how GnC calculates it.  In some 
cases I have thought it was a month or so off but haven't tried to add 
or subtract an offset value to see if that is possible.

Field 3:  The number of payments to be made.  If you divide the annual 
interest by 12 then you are doing this monthly and this would be the 
number of months.  If you want bi-weekly or semi-monthly you will need 
to adjust both the first field and the count in this field.

Field 4:  The loan amount.  In this case I threw away the old loan 
schedule in GnC and created a new one to shorten my loan down to 20 
years remaining (I think it was at 25 or 27 years).  So this would be 
the loan balance at the time you started making extra payments.

Field 5:  The ending value of the loan.  Most of the time this is zero 
as you plan to pay everything off.  If you have a balloon payment at the 
end, then enter the balloon amount here.

Field 6:  It is called 'type'.  I think this is whether the interest is 
pre-paid or post-paid.  The standard appears to be '0'.   I suspect the 
other type is '1' but could not determine based on a quick inspection of 
fin.scm

Note that certain payment values will result in a fractional number of 
payments.  I haven't tried to see if the function will work with a 
non-integer value for the total number of payments. So if you insist on 
paying an even (full) amount (non-fractional), you may not be able to 
generate the exact interest according to your bank.  I've been lucky 
with the above as I accepted a monthly payment that included pennies.

Setting up the scheduled transaction -- in my case I copied the old loan 
scheduled entries.  Then modified them to fit the new circumstances.

On the split for the bank payout amount, I hard carded the actual 
payment to be made: (326.97 in my case).

On the split for the interest amount I entered the function: ipmt( 
.04490 / 12.00 : i : 240.00 : 51,630.34 : 0 : 0 )

On the split for the principal amount I entered the payment minus 
interest:  326.97 - ipmt( .04490 / 12.00 : i : 240.00 : 51,630.34 : 0 : 0 )

If I rounded my payment up to $327.00 then the interest amount would 
slowly diverge from what the bank generates.  But, as it now is, the 
bank will occasionally post the payment a day or so later than I 
predicted and the interest amount will be off.

Now, this is all predicated on the fact that you will be making the same 
payment each time and not varying it each month.  It also based on 
getting the number of payments to be non-fractional.

Note that the loan wizard doesn't record your monthly payment. Instead 
it used the ppmt function (with same fields as the ipmt function).

Hope this helps.  Go Forth and experiment.  [although fin.scm is written 
in Scheme and not Forth]



On 2/18/22 12:13, D. via gnucash-user wrote:
> I manually edit each mortgage entry as well. I use the mortgage statements to reconcile the final amounts.
>
> I mean, there aren't that many transactions to consider. I don't see this as much of a burden.
>
> David
>
>
> -------- Original Message --------
> From: Derek Atkins <derek at ihtfp.com>
> Sent: Fri Feb 18 14:50:22 EST 2022
> To: Alan Schold <aschold at q.com>
> Cc: gnucash-user at gnucash.org
> Subject: Re: [GNC] mortgage calculator
>
> HI,
>
> On Fri, February 18, 2022 2:42 pm, Alan Schold via gnucash-user wrote:
>> Is there a way to get the mortgage repayment module to account for
>> regular extra monthly payments? Now the calculations proceed as if I'm
>> just paying the basic mortgage amount, and I have to recalculate the
>> principal balance each month. The calculator is only right once!
> Sorry, no.
>
> This is a long-standing limitation of the calculator (and trust me, it's
> been hitting me forever!!).
>
> The MAIN issue is the lack of a "GetBalanceAsOfDate()" function usable
> within the scheduled transaction (SX) functions, and tied into the SX
> system where it can supply the date as part of the SX firing methods.
> Adding it is, unfortunately, not as straightforward as you would think,
> which is why nobody has done it, yet.
>
> Me, I just periodically go through and update the P/I splits from my
> mortgage and loan statements.
>
> Sorry,
>
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
> -derek
>


-- 
Stephen M Butler, PMP, PSM
Stephen.M.Butler51 at gmail.com
kg7je at arrl.net
253-350-0166
-------------------------------------------
GnuPG Fingerprint:  8A25 9726 D439 758D D846 E5D4 282A 5477 0385 81D8