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 <de...@ihtfp.com>
Sent: Fri Feb 18 14:50:22 EST 2022
To: Alan Schold <asch...@q.com>
Cc: gnucash-user@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.butle...@gmail.com
kg...@arrl.net
253-350-0166
-------------------------------------------
GnuPG Fingerprint: 8A25 9726 D439 758D D846 E5D4 282A 5477 0385 81D8
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
