After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 670731 - Future Value not working with Loan Scheduled transaction
Future Value not working with Loan Scheduled transaction
Status: RESOLVED FIXED
Product: GnuCash
Classification: Other
Component: Scheduled Transactions
2.4.x
Other Windows
: Normal normal
: ---
Assigned To: Christian Stimming
gnucash-core-maint
Depends on:
Blocks:
 
 
Reported: 2012-02-24 10:17 UTC by YLM
Modified: 2018-06-29 23:06 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Test file with broken debit-numeric entry (4.12 KB, application/x-gnucash)
2016-12-23 14:42 UTC, André Colomb
Details

Description YLM 2012-02-24 10:17:17 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").
Comment 1 YLM 2012-02-24 10:33:17 UTC
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 ?
Comment 2 André Colomb 2016-12-23 14:40:56 UTC
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.
Comment 3 André Colomb 2016-12-23 14:42:09 UTC
Created attachment 342420 [details]
Test file with broken debit-numeric entry
Comment 4 John Ralls 2016-12-28 01:09:03 UTC
(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?
Comment 5 André Colomb 2016-12-28 13:36:41 UTC
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.
Comment 6 John Ralls 2017-03-24 23:46:57 UTC
Thanks for clarifying. This has been fixed in stable and will be in the next release.
Comment 7 André Colomb 2017-03-28 18:45:20 UTC
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!
Comment 8 John Ralls 2017-09-24 22:45:16 UTC
Reassign version to 2.4.x so that individual 2.4 versions can be retired.
Comment 9 John Ralls 2018-06-29 23:06:54 UTC
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.