Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

azalea4va
I recently switched to gnucash and had trouble setting up my mortgage account because I had made extra principle payments in the past.  I search of this board revealed others have had the same problem.  So I wrote some scheme functions that take care of the problem.  This works for situations where one is paying a constant amount each month, but that amount may be different from the original monthly payment and/or the principle left is off schedule.

Problem review: gnucash is setup to deal with loans where payments are made according to the original amoritization schedule.  But if the principle ever gets out a whack with respect to whatever is determined by that schedule, gnucash does not correctly compute how a payment gets divided into principle and interest because the existing gnucash functions assume the balance at any point are what the original schedule would have, not what the now adjusted balance is.

There are four "variables in loan calculations: starting principle, interest rate, term (number of payments), and monthly payment.  Given any 3, the fourth can be computed.  Given those four values, one can compute how any month's payment will be split between interest and principle.

I put into my "home" directory the file "config.user" below with the necessary functions. I then used the mortgage assistant to create the loan account.  I specified the balance as the balance currently and the term as the number of months left required to pay off the loan.  I then went to Actions->ScheduledTransactions->ScheduledTranasactionsEditor, highlighted the loan, and clicked on Schedlued->Edit=>TemplateTransaction.  I adjusted the formulas there accordingly:

pmt( 0.03 / 12.000000 : 110 : 150000.00 : 0 : 0 )
  became (added the "i" and 1950 arguments)
pmtFixed( 0.03 / 12.000000 : i : 110 : 150000.00 : 0 : 0 : 1950)

ppmt( 0.03 / 12.000000 : i : 110 : 150000.00 : 0 : 0 )
  became (added the 1950 argument)
ppmtFixed( 0.03 / 12.000000 : i : 110 : 150000.00 : 0 : 0 : 1950)

ipmt( 0.03 / 12.000000 : i : 110 : 150000.00 : 0 : 0 )
  became (added the 1950 argument)
ipmtFixed( 0.03 / 12.000000 : i : 110 : 150000.00 : 0 : 0 : 1950)

An item of note (from the comment in config.user).

;; In real life, mortgage payments are made in distinct monthly payment. In gnucash
;; those payments are processed with a precision to the nearest penny.  In math,
;; they are processed with greater precision.  This can cause function using pure
;; math to get out of sync (by a penny at a time) with what happens in a gnucash
;; register.  These differences can be resolved by adjusting the results of the
;; functions below to the nearest penny and using the recursive function below
;; to compute the balance at any point (and not the non-recursive one). BUT,
;; I appear to have found a bug in gnucash.  When one uses the schedule editor,
;; the editor complains about parsing the functions when recursion is included.
;; So the solution below uses the non-recursive function for computing balance
;; after 'payNum' payments.  It has been my experieince (limited) that after getting
;; everything setup in the schedule editor, I can change config.user to use the
;; recursive function and everything runs smoothly.  Just set config.user back to
;; using balFixed2 temporarily if you ever need to go back into the schedule editor.

config.user
Reply | Threaded
Open this post in threaded view
|

Re: Loan/Mortgage payments with "adjusted" principal (eg after an extra principle payment), SOLVED

azalea4va
Oops, that should be "principal", not "principle". Forgiveness (for that and surely others) requested from the spelling police.
Reply | Threaded
Open this post in threaded view
|

Re: Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

Mike or Penny Novack-3
In reply to this post by azalea4va
On 7/14/2017 1:18 PM, azalea4va wrote:

>
> There are four "variables in loan calculations: starting principle, interest
> rate, term (number of payments), and monthly payment.  Given any 3, the
> fourth can be computed.  Given those four values, one can compute how any
> month's payment will be split between interest and principle.
Sorry, but this is NOT true. Those are enough to calculate the
APPROXIMATE amounts but not the exact amounts. There are simply too many
other assumptions being made when constructing an amortization table and
no way to assume that any two people doing ti will make the same
choices. Examples:

a) method? << by "present value" of series of "rents" or by "trial and
error" >>
b) where will rounding take place?
c) how will the final payment be figured?

Michael D Novack
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

azalea4va
Sorry, I missed this reply.  So a little late, but ...


Mike or Penny Novack-3 wrote
> a) method? << by "present value" of series of "rents" or by "trial and
> error" >>

I am not sure what ""present value" of series of "rents"" means, but the
answer is by math.  This is just a mathematical calculation.  As was
indicated in the code provided.  I did not specify, but the math was based
on 30/360 calculations, the one most often used in determining mortgage
payments.


Mike or Penny Novack-3 wrote
> b) where will rounding take place?

I addressed this in the file I provided.  There is one and only one correct
answer mathematically.  But both gnucash rounds to the nearest penny, and a
bank does as well.  They may not round in the same manner.
I have given two solutions. First, there is a recursive function where the
payments are rounded at each payment. Second there is the exact math
solution that is then rounded at the monthly payment to be made.  I expected
the former to be what I would need to be consistent with my bank but it has
turned out the latter has been what has worked.  So yes I agree there could
be some minor variations that result in fractions of a penny differences,
all of which are "correct" answers.  If your lender uses something
different, it could require making a penny adjustment every few payments, or
make a minor change in the code, for example change the code so instead of
rounding, it truncates or ceilings. I cannot think of two many different
ways this can be done, assuming 30/360 calculations. /(Note there is a bug
in gnucash 2.6 that causes a problem with using the recursive solution, see
the comment in the code to see how to work around that bug.)/  



Mike or Penny Novack-3 wrote
> c) how will the final payment be figured?

To be honest, I forget (because I do not care).  Did I add the code to to
make the final payment the amount due at that time?  Or does the code just
generate the same old payment amount?  I do not care because this is
something for the schedule system so it handles the hundreads of payments
that will be coming out. It is designed to eliminate all the work of dealing
with those hundreds of payments.  If it handles the one final payment
correctly or not, I do not care.  If you do, plug it in, run a trail case to
see how it handles the entire series of payments, and see what happens.
I'll leave that to you, since you can do it as easily as I can and by doing
it yourself get an answer you have more confidence in.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

Mike or Penny Novack-3
On 8/12/2018 12:58 PM, azalea4va wrote:
> Sorry, I missed this reply.  So a little late, but ...
I should perhaps have indicated that writing this sort of thing used to
be "my line of country". I have written these things.
> Mike or Penny Novack-3 wrote
>> a) method? << by "present value" of series of "rents" or by "trial and
>> error" >>
> I am not sure what ""present value" of series of "rents"" means, but the
> answer is by math.  This is just a mathematical calculation.  As was
> indicated in the code provided.  I did not specify, but the math was based
> on 30/360 calculations, the one most often used in determining mortgage
> payments.
OK --- The periodic payments are called "rents" and dependent on the
"discount rate" (the mortgage interest rate in this case) that series of
future payments has a PRESENT value. So ONE way of doing the math is to
take the payment amount to be 1.0000... (number of decimal places not
going to be agreed) and discounting each by the interest for the
interval. That gives a number you can divide into the starting principle
amount.

Another way for a program to address the problem is "trail and
error"where starting with an initial guess as to what the payment would
be this is adjusted until the "best fit" results << see my question
about "final payment" >>


> Mike or Penny Novack-3 wrote
>> b) where will rounding take place?
> I addressed this in the file I provided.  There is one and only one correct
> answer mathematically.
Not true. Depends, for example, on how many decimal places in the
calculations and whether only final rounding or rounding at multiple
places during the calculations. Since you and the bank will not be in
agreement won;t get the same answer. Again I will refer to the alternate
"trial and error" method of calculating the payment which MIGHT give
better results.
>
>
> Mike or Penny Novack-3 wrote
>> c) how will the final payment be figured?
> To be honest, I forget (because I do not care).  Did I add the code to to
> make the final payment the amount due at that time?
Again, there are choices here. Was the calculation made so that there
was NO additional (small) payment at the end? Was the calculation such
that the final payment would be as close as possible but not more than
the rest of the payments? What do you do IF the choice is between a
final payment much smaller than the others or going over a small amount
if the payment amount is 1 cent higher?

Do these issues explain why (in practice) my "amortab" programs (create
an amortization table for a loan amount, interest rate, and number of
payments) were usually written to use the "trial and error" method? << I
might get a first approximation by calculating the "present value" and
THEN seeing if small adjustments up or down gave a better fit to all the
desired conditions. >> For understanding what I mean by "trial and
error" methods look up something like "Newton's Method" -- I was NOT
referring to "non-math" but an algorithm, a PROCESS,  guaranteed to
converge on a best approximation of a value.

Michael D Novack
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

azalea4va

>> I addressed this in the file I provided.  There is one and only one
>> correct
>> answer mathematically.
>
> Not true.Depends, for example, on how many decimal places in the
> calculations and whether only final rounding or rounding at multiple
> places during the calculations.

This was a terminology problem.  When I said mathematically, I meant using
exact math.  That is not looking how a lender would implement but as a pure
math problem.  In a pure math, there is no rounding, it is just a
calculation.


> Again, there are choices here.

yes there are choices, probably an infinite number of them.  Anybody can
invent any weird scheme for how this stuff gets process.  I do not know
about the rest of the wrold but here in the US almost all "legit" lending
institutions (banks, etc)  sue one of three schemes: 30/360, actual/365, and
actual/360.  Of those, for things like mortgages, 30/360 is the norm.  So as
I said in my reply (but did leave out in my original post), my answer was a
solution for a 30/360 loan, again because that is what my bank uses as do
most bank in the US.  Si I agree there are lots of ways to compute loans,
but this was a 30/360 solution and with that said only minor variations in
how rounding occurs are possible.


> Depends, for example, on how many decimal places in the
> calculations and whether only final rounding or rounding at multiple
> places during the calculations.

All financial institutions use computers and are going to perform the
calculations using at least 64-bit accuracy.  If you use more (or even only
32-bit), that will make hardly any difference.  The rounding issue only
ocurs when that computer value must be translated into currency (in my case,
US dollar/cents).  I can only think of two options of when this would occur,
as I detailed previously.  Yes other rounding scenarios can be invented, but
they have no place in real life "legit" lending scenarios.

Note the use here is to create a scheduled entry in gnucash.  That is, have
gnucash autmatically add an entry into one's gnucash account each month for
each payment.  Although one could use it to generate an amoritization table,
that was not its primary purpose.


> Do these issues explain why (in practice) my "amortab" program .. were
> usually written to use the "trial and error" method?

Yes but again, that is not the type of loan I was creating a solution for. A
perhaps interesting aside.  I am a computer science professor whose career
was at liberal arts universities.  One example I would give of the value of
a liberal arts education was it teaches people different approaches to
looking at problems and that provides a better "tool kit" for problem
solving.  A mathematician can take your problem and generate a solution
figuringing out the equation.  A computer scientist can write a script and
have it plug in millions of values until one of them generates the correct
result. To radically different thought processes on how to arrive at an
answer.

One thing trail/error requires is knowing what the end result must be.  So
yes it works if you are trying to get a payment that leaves a final payment
of 0 (smething I have never seen in a "primary" loan agreement). I do not
see how trail/error could be used for a "normal" 30/360 loan because after a
trial I do not see a way to evaluate if the result was an error or not
(except to compare to the actual math answer, which eliminates the need for
trail/error).  If your response is one compare the result to the
amortization schedule provied by tha bank, then there is no need for any of
this, if I have that schedule I can just use that to determine what happens
each month.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

Mike or Penny Novack-3
On 8/13/2018 11:29 AM, azalea4va wrote:
>>> I addressed this in the file I provided.  There is one and only one
>>> correct
>>> answer mathematically.

I am surprised the moderators have not stepped in.

If you want to continue this, I suggest off list.


Michael D Novack
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

peter_cd.cn
In reply to this post by azalea4va
I am in a similar boat as you and glad that I found your solution.  When I
try to use your function, I get an error in the "Edit Schedule Transaction"
window.  Basically saying GNUCash doesn't know the function "imptFixed" and
other "*Fixed" functions.

"An error occurred while processing imptFixed( )".

I am using a MacOS and put the user.config under /Volumes/Macintosh
HD/Users/[username]/Library/Application Support/GnuCash

Another question, when setting up a new Loan with the Mortgage Assistant
program, I should use the current balance, not the original loan amount.
What about the term left?  I have a 180 months loan, and it has 163 month
left(based on default payment schedule).  Should I put 163 or calculated the
month by my "adjusted" monthly payment value.

Thanks,



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] Loan/Mortgage payments with "adjusted" principle (eg after an extra principle payment), SOLVED

peter_cd.cn
I think this part is answered, rename "config.user" to "config-user.scm"

Still have questions for: the loan balance and remaining months.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.