Home → Problems →

Cash flow report with incorrect/wrong amounts when including accounts with security investments

• Edit

tguny

Jun 17, 2024 @ 04:28 PM

I have included several brokerage accounts in my cash flow report since they have a meaningful cash balance that is readily accessible. This is not the case for other investment accounts such as HSA, 529k, 401K for example so I don't include those in my CF report.
For those brokerage accounts, the CF report shows any dividend payment as income correctly. When these dividends are reinvested in full, here's what happens with the CF report.
First, the investments are displayed with the number of shares purchased and not the dollar amount, which is odd. Since the cash balance was incorrect as I ran the report for 2023, I investigated and discovered by isolating one investment in one account that the valuation for the purchases is made based on the latest value per share available in the history table of that investment. In other words, if a dividend of $1000 was paid in March 2023, and reinvested in full, but the latest price per share in the history table is $23 as of June 6, 2024, that's the number used by the report to value the expense. Depending on whether the actual purchase price in March 2023 was below or above $23 this automatically creates a difference in cash flow that should not be there. See example in file attached. This happens for all investments in all accounts included in the cash flow reports. Regardless of whther the investment comes from a dividend or an incoming cash contribution. Once again, it appears the valuation of the purchase does NOT track to the price at the time of the purchase but to the latest price available is the table. A major issue.

• Screenshot_2024-06-13_at_3.26.38 PM.png 1.11 MB

1. 1 Posted by tguny on Jun 19, 2024 @ 01:48 PM

   

   I forgot to mention the report behavior is the same for investment sales: the valuation is done based on the latest value stored regardless of the timing of the sale. In short, the CF report can't be trusted whenever accounts with sales and purchases of securities are included.

   • Edit

2. 2 Posted by tguny on Sep 05, 2024 @ 09:26 AM

   

   Closing in on 3 months...still not msg, not even acknowledging the pb

   • Edit

3. 3 Posted by Stuart Beesley ... on Sep 05, 2024 @ 10:12 AM

   

   following

   • Edit

4. Support Staff 4 Posted by Sean Reilly on Sep 05, 2024 @ 10:30 AM

   

   Hello,

   Yes, I see what you're saying. The cash flow report is using the security's price on the date of the time interval, not the date of the transaction. Ideally it would also use the actual base-currency value of the purchase amount in the report. I agree this is a bug and will try to have a fix available soon.

   Thanks,
   Sean

   --
   Sean Reilly
   Developer, The Infinite Kind
   https://infinitekind.com

   • Edit

5. 5 Posted by Stuart Beesley ... on Oct 11, 2024 @ 08:37 PM

   

   @tguny - are you able to create a small test dataset that demonstrates this issue? I’m having trouble creating the data to match your issue. Thanks.

   • Edit

6. 6 Posted by tguny on Nov 16, 2024 @ 03:31 PM

   

   How soon is soon? Another 2 months since my follow up. This is frustrating.

   • Edit

7. 7 Posted by Stuart Beesley ... on Nov 16, 2024 @ 03:54 PM

   

   Refer my post dated 11 Oct ?

   • Edit

8. 8 Posted by tguny on Nov 16, 2024 @ 04:38 PM

   

   Hi, I explained what happened and provided a spreadsheet with details. What's missing?

   • Edit

9. 9 Posted by tguny on Nov 16, 2024 @ 05:03 PM

   

   Created a simple example for 2024. See attached screenshot.
   1/ $50k deposit in bank account in Jan 24
   2/ Transfer of $15k to investment account in Feb 24
   3/ Purchase of Test security 100 shares at $100 each in Mar 24
   4/ Sale of 50 shares at $125 in Nov 24
   5/ CF report shows a cash outflow of $12,500 in Feb so clearly values at latest price available in history table based on report period (i.e., $125/share) instead of $100/share

   • Screenshot_2024-11-16_at_11.55.43 AM.png 266 KB
   • Edit

10. 10 Posted by Stuart Beesley ... on Nov 16, 2024 @ 05:22 PM

    

    Nothing as such. But I couldn’t reproduce the problem. Hence why I was asking for a test dataset that showed the issue.

    • Edit

11. 11 Posted by Stuart Beesley ... on Nov 19, 2024 @ 07:07 PM

    

    FYI. I’ve now reproduced your issue. It’s highlighted another question, not related to shares.

    So asking you for your opinion.

    If you have a txn for $100 but your base currency is £, and the ex rate was 2:1 = £50. But now the ex rate is 3:1. Would you expect to see £33.33 in the report for that month and the total, or £50 in both, or £50 in the month and £33.33 in the total? Depends if you want all values as of now for like for like comparison, or all valued as if the date they occurred. Also the now date could be now or the end date on the report.

    Thoughts?

    • Edit

12. 12 Posted by tguny on Nov 19, 2024 @ 07:55 PM

    
    Well, MD is meant to help manage personal finances so the right way to report is to ensure the tax impact is calculated correctly. So the correct approach is to value everything as of the time it occurs, which is the way the IRS (tax authority here in the US, seems like you may be in the UK) looks at it. In your example below, the purchase would need to be valued at 50£ per share (5000£ total) and the sale of 50 shares at 33.33£ per share (1666£).

    
    

    On Nov 19, 2024, at 2:07 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:
    
    

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) FYI. I’ve now reproduced your issue. It’s highlighted another question, not related to shares. So asking you for your opinion. If you have a txn for $100 but your base currency is £, and the ex rate was 2:1 = £50. But now the ex rate is 3:1. Would you expect to see £33.33 in the report for that month and the total, or £50 in both, or £50 in the month and £33.33 in the total? Depends if you want all values as of now for like for like comparison, or all valued as if the date they occurred. Also the now date could be now or the end date on the report. Thoughts? On Sat, Nov 16 at 09:22 AM PST, Stuart Beesley (Mr Toolbox) wrote: Nothing as such. But I couldn’t reproduce the problem. Hence why I was asking for a test dataset that showed the issue.

    Having trouble reading this? View this discussion online: Cash flow report with incorrect/wrong amounts when including accounts with security investments . To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

    • Edit

13. 13 Posted by Stuart Beesley ... on Nov 19, 2024 @ 07:58 PM

    

    As per my message.... My last question is NOT about shares... That will get fixed... This is other currency txns - e.g. buy a car in $ in Jan, but my base currency is £

    • Edit

14. 14 Posted by tguny on Nov 19, 2024 @ 08:25 PM

    
    The report I have asked a fix for is the cash flow report. This means a representation of cash paid and cash received. In your example, a $100 purchase in February would be valued in the cash flow report as an outflow as of the time of the transaction, i.e. 50£ if the exchange rate is 2:1. If I sold whatever I purchased now, in November, for the same $100, it would be valued at $33.33 given the 3:1 exchange rate. a cash flow report running for the 2024 year and organized by month should show an outflow of 50£ in February and an inflow of 33.33£ in November. Does this help?

    
    

    On Nov 19, 2024, at 2:58 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:
    
    

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) As per my message.... My last question is NOT about shares... That will get fixed... This is other currency txns - e.g. buy a car in $ in Jan, but my base currency is £ On Tue, Nov 19 at 11:55 AM PST, tguny wrote: Well, MD is meant to help manage personal finances so the right way to report is to ensure the tax impact is calculated correctly. So the correct approach is to value everything as of the time it occurs, which is the way the IRS (tax authority here in the US, seems like you may be in the UK) looks at it. In your example below, the purchase would need to be valued at 50£ per share (5000£ total) and the sale of 50 shares at 33.33£ per share (1666£). On Nov 19, 2024, at 2:07 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:  pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); } // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) FYI. I’ve now reproduced your issue. It’s highlighted another question, not related to shares. So asking you for your opinion. If you have a txn for $100 but your base currency is £, and the ex rate was 2:1 = £50. But now the ex rate is 3:1. Would you expect to see £33.33 in the report for that month and the total, or £50 in both, or £50 in the month and £33.33 in the total? Depends if you want all values as of now for like for like comparison, or all valued as if the date they occurred. Also the now date could be now or the end date on the report. Thoughts? On Sat, Nov 16 at 09:22 AM PST, Stuart Beesley (Mr Toolbox) wrote: Nothing as such. But I couldn’t reproduce the problem. Hence why I was asking for a test dataset that showed the issue. Having trouble reading this? View this discussion online: Cash flow report with incorrect/wrong amounts when including accounts with security investments . To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

    Having trouble reading this? View this discussion online: Cash flow report with incorrect/wrong amounts when including accounts with security investments . To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

    • Edit

15. 15 Posted by Stuart Beesley ... on Nov 19, 2024 @ 08:56 PM

    

    Yes. Perfect. That’s my view too thanks

    • Edit

16. 16 Posted by tguny on Nov 19, 2024 @ 09:12 PM

    
    
    

    Thierry.

    
    

    On Nov 19, 2024, at 3:56 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:
    
    

     pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); }

    // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) Yes. Perfect. That’s my view too thanks On Tue, Nov 19 at 12:25 PM PST, tguny wrote: The report I have asked a fix for is the cash flow report. This means a representation of cash paid and cash received. In your example, a $100 purchase in February would be valued in the cash flow report as an outflow as of the time of the transaction, i.e. 50£ if the exchange rate is 2:1. If I sold whatever I purchased now, in November, for the same $100, it would be valued at $33.33 given the 3:1 exchange rate. a cash flow report running for the 2024 year and organized by month should show an outflow of 50£ in February and an inflow of 33.33£ in November. Does this help? On Nov 19, 2024, at 2:58 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:  pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); } // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) As per my message.... My last question is NOT about shares... That will get fixed... This is other currency txns - e.g. buy a car in $ in Jan, but my base currency is £ On Tue, Nov 19 at 11:55 AM PST, tguny wrote: Well, MD is meant to help manage personal finances so the right way to report is to ensure the tax impact is calculated correctly. So the correct approach is to value everything as of the time it occurs, which is the way the IRS (tax authority here in the US, seems like you may be in the UK) looks at it. In your example below, the purchase would need to be valued at 50£ per share (5000£ total) and the sale of 50 shares at 33.33£ per share (1666£). On Nov 19, 2024, at 2:07 PM, Stuart Beesley (Mr Toolbox) <[email blocked]> wrote:  pre { width: 92%; margin: 10px 2%; padding: 5px 2%; background: rgb(239, 239, 239); border: 1px solid rgb(214, 214, 214); } blockquote { margin-left: 0px; padding-left: 1em; border-left-width: 5px; border-left-style: solid; border-left-color: rgb(204, 204, 204); } // Please reply above this line ================================================== From: Stuart Beesley (Mr Toolbox) FYI. I’ve now reproduced your issue. It’s highlighted another question, not related to shares. So asking you for your opinion. If you have a txn for $100 but your base currency is £, and the ex rate was 2:1 = £50. But now the ex rate is 3:1. Would you expect to see £33.33 in the report for that month and the total, or £50 in both, or £50 in the month and £33.33 in the total? Depends if you want all values as of now for like for like comparison, or all valued as if the date they occurred. Also the now date could be now or the end date on the report. Thoughts? On Sat, Nov 16 at 09:22 AM PST, Stuart Beesley (Mr Toolbox) wrote: Nothing as such. But I couldn’t reproduce the problem. Hence why I was asking for a test dataset that showed the issue. Having trouble reading this? View this discussion online: Cash flow report with incorrect/wrong amounts when including accounts with security investments . To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page Having trouble reading this? View this discussion online: Cash flow report with incorrect/wrong amounts when including accounts with security investments . To unsubscribe and stop receiving emails from https://infinitekind.tenderapp.com, visit this page

    • Edit

17. 17 Posted by Stuart Beesley ... on Nov 20, 2024 @ 05:05 PM

    

    I've been working on the developer in this... See attached before/after screen shots..

    How does this (after) look?

    • before.png 176 KB
    • after.png 174 KB
    • Edit

18. System closed this discussion on Feb 19, 2025 @ 05:10 PM.

19. Stuart Beesley (Mr Toolbox) re-opened this discussion on Sep 20, 2025 @ 01:51 PM

20. 18 Posted by Stuart Beesley ... on Sep 20, 2025 @ 01:51 PM

    

    Keeping this open

    • Edit