GNOME Bugzilla – Bug 670731
Future Value not working with Loan Scheduled transaction
Last modified: 2018-06-29 23:06:54 UTC
Here is an example of a scheduled transaction for a loan with a future value not equal to 0 (e.g. fv = -46666.01) : The monthly scheduled transaction has these formulas: Line 1 (bank account): pmt (0.0345/12 : 240.00 : 130000.00 : -46666.01 : 0) + 45.51 Line 2 (loan principal account): ppmt (0.0345/12 : i : 240.00 : 130000.00 : -46666.01 : 0) Line 3 (interest account): ipmt (0.0345/12 : i : 240.00 : 130000.00 : -46666.01 : 0) Line 4 (insurance account): 45.51 Each month when the transaction is created, I get this result: Line 1 (my bank account): 660.84 ==> correct Line 2 (loan principal account): 240.89 ==> not correct Line 3 (interest account): 374.44 ==> not correct Line 4 (insurance account): 45.51 ==> correct First workaround to use future value: Replace the "i" by a number (let's say "15" if it's the 15th payment), the ipmt & ppmt values are correctly calculated by GnuCash. Note that you'll have to update manually the "i" number each time the scheduled transaction is run. Second workaround: Open the "gnucash" file. Go to the corresponding gnc:transaction, here is an extract of it : <gnc:transaction version="2.0.0"> ... <split:slots> <slot> <slot:key>sched-xaction</slot:key> <slot:value type="frame"> <slot> <slot:key>account</slot:key> <slot:value type="guid">12d0da01a75eb03d77038191f07810fc</slot:value> </slot> <slot> <slot:key>credit-formula</slot:key> <slot:value type="string"></slot:value> </slot> <slot> <slot:key>credit-numeric</slot:key> <slot:value type="numeric">0/1</slot:value> </slot> <slot> <slot:key>debit-formula</slot:key> <slot:value type="string">ppmt( 0,03450 / 12,00 : i : 240,00 : 130000,00 : -46666,01 : 0 )</slot:value> </slot> <slot> <slot:key>debit-numeric</slot:key> <slot:value type="numeric">240887/1000</slot:value> </slot> </slot:value> </slot> </split:slots> ... </gnc:transaction> Remove these lines : <slot> <slot:key>debit-numeric</slot:key> <slot:value type="numeric">240887/1000</slot:value> </slot> Do this as well for the "ipmt" gnc:transaction. Then save the file. Gnucash will have to calculate the "pmt" & "ipmt" values each time you run the scheduled transaction. And the results will be correct (and will depend on the instanceCount "i").
Maybe this workaround works because the _get_sx_formula_value function (in gnx-sx-instance-model.c) checks first if there is already a calculated numeric value before parsing the string. If not, then the string function is evaluated. But I’m unsure of it. static void _get_sx_formula_value(const SchedXaction* sx, const Split *template_split, gnc_numeric *numeric, GList **creation_errors, const char *formula_key, const char* numeric_key, GHashTable *variable_bindings) { ... if ((gnc_numeric_check(*numeric) == GNC_ERROR_OK) && !gnc_numeric_zero_p(*numeric)) { /* Already a valid non-zero result? Then return and don't * parse the string. Luckily we avoid any locale problems with * decimal points here! Phew. */ return; } ... } If so, the issue will be : why the string function is not initially evaluated with a future value equal to 0, BUT is evaluated with a future value not equal to 0. Something related to the loan assistant ?
I have been bitten by this bug as well. After changing a scheduled transaction's template amount, the Future Scheduled Transactions Summary report shows the correct new value on the credit side, but the debit side still shows the first value ever entered. Looking at the data file, I can see that the template transaction has a slot for "debit-numeric", which is not updated when changing the scheduled transaction. The report then always uses that obsolete value instead of the correctly updated "debit-formula" slot. Interestingly, the "credit-numeric" value seems to get updated properly. When creating a new blank file for testing, I noticed that the "debit-numeric" slot is never even written, but only exists in some older files. I'm attaching a test case with the "debit-numeric" entry manually added, to show the report's discrepancy. To fix this, I think there must be some code that either removes or updates the "debit-numeric" value when loading an older file. Manually removing it from the XML file works for me currently. I couldn't find out what piece of the code is responsible for this, especially why it works for the credit side, but not the debit. Note that in my testing, the SX template only ever contained numeric entries, no actual formulas. Last testing was done on current stable version 2.6.15.
Created attachment 342420 [details] Test file with broken debit-numeric entry
(In reply to André Colomb from comment #3) > Created attachment 342420 [details] > Test file with broken debit-numeric entry I adjusted your file a bit so that the since-last-run dialog would fire and create a transaction, which was in balance for $302; that's also the amount shown in the template transaction. This on 2.6.15. What's supposed to be the problem?
I was talking about the Future Scheduled Transactions Summary report, which should be open in another tab of that file. It always uses the *-numeric values, but they don't get adjusted when changing the template. Having an inconsistent debit-numeric value in the file seems like a bug on its own to me. Especially because in contrast to credit-numeric, this entry is never generated for new files nor updated if already present.
Thanks for clarifying. This has been fixed in stable and will be in the next release.
Stable version 2.6.16 works for me, the damaged file was correctly scrubbed and the Future Scheduled Transactions Report is now properly balanced. Thank you very much for this timely fix. Keep up the great work on GnuCash!
Reassign version to 2.4.x so that individual 2.4 versions can be retired.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=670731. Please update any external references or bookmarks.