Cost Basis incorrect

Fred's Avatar

Fred

18 Dec, 2020 04:27 PM

I am holding APPL shares in 2 accounts and both now show erroneous Cost Basis.
I am attaching screen shots for both accounts.
The screen shots for the 1999 purchase do not show some SALE transactions, but share sales do not affect the cost basis.

I have posted this same problem in this thread, but did not get any acknowledgment from IK: https://infinitekind.tenderapp.com/discussions/problems/65686-moneydance-calculating-the-cost-per-share-incorrectly
Maybe this new post gets more traction.

  1. Support Staff 1 Posted by Maddy on 18 Dec, 2020 04:41 PM

    Maddy's Avatar

    Hi Fred,

    We are sorry to hear about the problem you have encountered.

    If you have selected the cost basis to be Average cost then the reinvested dividend is added to the cost but the increased cost is not the actual increase in value, instead it is the increase in value times 100 (e.g using the unit price of 100). Selecting Lot matching should fix the problem for you, namely the increase in value doesn't affect the cost.

    I hope this information is helpful. Please let me know if you have further questions or need any assistance.

    --
    Maddy, Infinite Kind Support

  2. 2 Posted by Fred on 18 Dec, 2020 04:46 PM

    Fred's Avatar

    Maddy,
    I do not reinvest dividends to buy new shares of that position. I cash out dividend payments.

  3. 3 Posted by Fred on 18 Dec, 2020 04:58 PM

    Fred's Avatar

    Tried to do use Lot Matching, but not sure how I should use this feature. Getting an error message. Attached are screenshot of the Lot Matching window i see for the 2001 and the 1999 position.

  4. 4 Posted by Fred on 18 Dec, 2020 05:55 PM

    Fred's Avatar

    Maddy, I see this issue only with AAPL, possibly since last stock split in August this year. Another of my holdings TSLA also had a split on same date, but no problem with the cost basis there. I am using MD 2021.1 build 2012
    F

  5. 5 Posted by dwg on 23 Dec, 2020 06:25 AM

    dwg's Avatar

    I'm a fellow user.

    I assume the screen dumps are of two separate investment accounts?

    I do not see how they could be otherwise to be honest.

    Des

  6. 6 Posted by dwg on 23 Dec, 2020 06:41 AM

    dwg's Avatar

    In the second screen shot I see 4 transactions but I see no commission details for 3:

    Jan 1 11 buy 30 @ 346.05 10389.45
    Dec 18 13 Buy 30 @ 544.72 16349.55
    Dec 18 13 Sell 30 @ 544.68 16332.16

    Is this the case?

    I've also note the following splits

    Jul 21 00 2:1
    Feb 28 06 2:1
    Jun 7 14 7:1
    Aug 31 20 3:1

  7. 7 Posted by Fred on 23 Dec, 2020 05:02 PM

    Fred's Avatar

    Yes they are from 2 different accounts.
    One is a holding which I bought in 2011 in one account and I have named the 3 related screenshots starting out with AAPL2011xxx.

    In an other account, I made one purchase in 1999 and I have named the 5 related screenshots starting out with AAPL1999xxx. There is only one (1) purchase in this account back in 1999, then a few sales over the past few years and 4 splits - as I said and you will agree, sales don’t affect the cost basis.
    F

  8. 8 Posted by Fred on 23 Dec, 2020 05:17 PM

    Fred's Avatar

    This is the holding I bought in 2011 and I have named the 3 related screenshots starting out with AAPL2011xxx.

    The commission details for this holding are as follows (I typically include the comms in the purchase price, they are so small and have thus little effect on the cost basis):

    1/13/11: Buy 30sh @ $346.05 = $10381.50, plus $7.95 commission for a total cost of $10,389.45
    12/18/13: Sell 30 @ 544.6801 = 16,340.40, plus comms of 7.95 + 0.29 for total of 16,332.16
    12/18/13: Buy 30 @ 544.72 = 16,341.60, plus comm 7.95 = total of 16,349.55
    7/19/15: Sell 110 @ 132.7762 = 14,605.38 + comm 7.95 +0.27 = total 14,597.16

    The split in August this year was 4:1, not 3:1 as you say in your post, the other 3 are as you noted, and all appear in the Security Detail screenshot.

    F

  9. 9 Posted by Fred on 23 Dec, 2020 05:37 PM

    Fred's Avatar

    dwg,
    here are some more details:

    I am using MD 2021 (2010).

    I do not reinvest the Div, I cash out Div.

    I use avg costing, but this is immaterial, when you have only a one time purchase in the account
    The screenshot I posted for my 2 AAPL holdings are all there is, no other txn.

    For the 1999 holding it is a simple one time purchase, that is only one (1) lot, a few sales in recent years and 4 splits as noted in the Security Detail screenshot:

    The number of shares currently held is shown correctly in the Security Detail.

    I see these big cost basis discrepancies only with my AAPL holdings.

    F

  10. 10 Posted by dwg on 23 Dec, 2020 09:16 PM

    dwg's Avatar

    The 3:1 was my dyslexic fingers :)

    I've had a quick look at the numbers in your second account, outside of Moneydance i.e using a spreadsheet.

    I get close to your number, I ignored commissions to make it easier, I knew they are all of minimal amounts, the overall difference is something like $30 close enough to get an idea of what is valid data.

    I have not seen Moneydance have a problem with basic calculations and of course the calculations are the same for all accounts, so if one account was wrong the expectation is all accounts would be wrong.

    What I think is happening is there is something in the data that is not visible to us. I have seen in the past a transaction that looked absolutely fine but it seemed to throw an account out, remove it and the account was fine re-entered it and the account was still fine so it was something about the particular entry, as to what I never found out.

    All I can really suggest is making a copy of the data set (the method to use is dependent on if you are syncing or not) and trying things on the copy. On reflection the overall safest way in any instance as to make a Moneydance backup and then do a restore that should ensure there are no problems. Moneydance will restore the data set to one with the existing name with a date appended.

    As it is two separate investment accounts it does imply something common and the most obvious thing is to look at the splits, that is the most obvious thing at the security level that affects cost basis.

  11. 11 Posted by Fred on 23 Dec, 2020 11:21 PM

    Fred's Avatar

    PROBLEM SOLVED !!! Hurrah
    I switched from Avg to Lot Costing and finally found out how to work that table (actually very simple, if I had read the instructions in the header, and then saw how the available shares magically adjusted to the splits as I went down the list to match each sale. Bravo, MD)

    I would never had thought that switching to lots when you have only one buy would have an influence.

    I am happy again.
    Merry Christmas, and thanks for your support

  12. 12 Posted by dwg on 24 Dec, 2020 12:20 AM

    dwg's Avatar

    Certainly on the second account you had two lots.

    Splits complicate the issue when you have sales both before and after the split as the per share cost base does change because of the split.

    Glad you got it sorted out.

    I've used lot matching and while it is not hard it can be a bit picky at times, more so when you have stock consolidations.

  13. 13 Posted by Stuart Beesley ... on 24 Dec, 2020 05:33 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    That’s an interesting point of note for others with splits. So sells overtime affect the MD calculation where splits are involved and the lots ‘guide’ MD to make the right calculation. Great news!

  14. 14 Posted by dwg on 24 Dec, 2020 09:50 AM

    dwg's Avatar

    I think the ins and outs of investment transactions and the effect they have, especially when interacting with each other makes it an almighty headache for the developers in expanding the capability in investment accounts.

    Still that has not stopped me putting in development suggestions, as I think better investment capability can only enhance the product's position in the market.

  15. 15 Posted by Stuart Beesley ... on 24 Dec, 2020 10:14 AM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    I agree. I started writing a cost basis / gains report. I’ve had to stop for now as the logic is mind bending... so I have admiration for what we already have now....!

  16. 16 Posted by dwg on 24 Dec, 2020 10:26 AM

    dwg's Avatar

    That is why I think most programs just have the very basics - take the easy way out.

    Events like Splits, returns of capital and capital calls along with lots just makes the handling of investment quite complicated, of course that is why it is desirable for software to be able to do it for us. At the moment I have resorted to having an associated spreadsheet for each set of shares I own. Quicken/Reckon does most of It but I think I found a hole in that software for when you have stapled securities (A hole Moneydance does not have)

  17. 17 Posted by davidvernonlong on 24 Dec, 2020 10:52 AM

    davidvernonlong's Avatar

    I have the same issue with splits. The strange thingis that the "Cost Basis" report was correct (I was able to verify the cost calculation independantly in Excel), whereas the Cost Base shown in the "Portfolio " report was not. Fred, did you find the same discrepancy?

  18. 18 Posted by Stuart Beesley ... on 24 Dec, 2020 01:30 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    I also have a mirror spreadsheet

  19. 19 Posted by Fred on 24 Dec, 2020 09:53 PM

    Fred's Avatar

    I didn’t need a spreadsheet to keep track of this simple one, which I properly tracked in the monthly statements from the investment brokerage firm.

    To recap the one account where I had 1 purchase (the one and only original investment, no Div reinvestments), 7 sales, and along the way 4 stock splits.

    - Buy 8 Oct 1999: 50 shares at $67.43750 for a total of $3,371.88 (not counting the $14.95 fee charged for the purchase).

    - Security Detail and Portfolio Summary show correctly the current holding of 2,172, but the cost basis should be $1,313.60 or $0.60479/sh, not $1,821.52 or 0.84/sh as MD shows.

    -Stock split occurred as follows:
    21 Jun 2000: 2
    28 Feb 2005: 2
    7 Jun 2014: 7
    31 Aug 2020: 4

    I didn’t initially follow the suggestion to switch to Lot costing from the default Average, because i have only 1 Buy lot and looking at the Lot matching window I was discouraged to pursue this because the Buys side showed that I had one lot of 1400 shares available to distribute for my 7 sale events - my most recent sale alone was 1468 shares, more than the available shares to match… .
    Well, I eventually tried the lot matching anyway, matching my first sale of 50 sh to the one Buy lot, and as I continued to match my subsequent sales always to the same one Buy lot, I noticed that Shares available number adjusted, as can be seen in my screen shots of each match.

    Interestingly, the Transaction Report shows the original purchase as 5,600 shares, which is correct when the splits are taken into account.

    My second holding of AAPL shares in a different account was resolved in the same way. There I have one original purchase, a 2nd purchase but reversed with a sale the same day, and then one more sale. In this account, the cost basis was off way more: MD showed $57.02/share, which should have been $19.46/sh !

    One can ask why is Cost Averaging the default?

    Another interesting observation: I don’t see a Cost Basis problem with my TSLA shares, where I made one buy, no Sell, and one Stock Split, and I still have it on Average Costing. Probably meaning: use Lot matching whenever you have Sell, and of course also when there is more than one Buy (or DivReinvest).

    I guess I can close this discussion. Thanks everyone.

    Merry Christmas everyone and stay safe.
    Fred

    P. s. for Stuart: Didn’t want to try your script. Not only because i got a warning from Apple, but also didn’t see what it is going to extract, if I could choose which account, which equity… . In any event, many thanks for your excellent support to the community.

  20. 20 Posted by Stuart Beesley ... on 24 Dec, 2020 10:07 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    Thanks for your explanation. Sells do of course affect total cost basis and I guess when you do sells (and then buys) before and after splits, the code can’t work out how much to adjust the total CB by. Thus lot control is working. I can confirm that what you describe without splits and avg cost basis works ok.

  21. 21 Posted by Stuart Beesley ... on 24 Dec, 2020 10:11 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    Hi Fred, yes on Macs, you get two *.dylib gatekeeper warnings when running moneybot python scripts on Mac. The developer hasn’t yet worked out how to suppress this. It’s to do with dynamic cache libraries... anyway, yes my script gives you the choice to filter the account and security (or all) plus other options. It would extract your txns to a csv that would make it easy for people (to review). You can run it and just look at the csv yourself if you like. The international was that you could post the csv and then we could easily try to reproduce your issue. S

  22. 22 Posted by dwg on 24 Dec, 2020 11:41 PM

    dwg's Avatar

    I had not considered till this point that MD might have a problems with Average Cost when there are stock splits. Certainly mathematically it can be worked out, but I can understand why lot matching may help to track the costs when you do have splits and sell transactions at various times.

    Certainly it is one to file away for future reference.

  23. 23 Posted by davidvernonlong on 25 Dec, 2020 01:09 PM

    davidvernonlong's Avatar

    Hi

    The Cost Basis report seems to calculate it correctly, but not the Cost Basis shown in the Portfolio report.

  24. 24 Posted by Fred on 25 Dec, 2020 06:19 PM

    Fred's Avatar

    Hello Stuart,
    Let’s be clear: Cost Basis per share is NOT affected by selling shares. Selling shares creates Capital Gains/Loss and affects the number of shares you hold, but the original purchase price per share is NOT affected by sales transactions. Fidelity, Schwab and Vanguard statements I receive agree with my/this view.

    Thank you again for your support and the explanation for the script; I’ll try it out next time I want investigate something.
    Fred

  25. 25 Posted by Stuart Beesley ... on 25 Dec, 2020 06:28 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    Hi. I agree, as you say, that the cost basis per share is not affected by a sell. But the total cost basis remaining on the held shares, is of course affected / reduced. Ultimately, this is what is used to calculate cost basis and gain calculations.

  26. 26 Posted by D. Smith on 18 Jan, 2021 04:35 PM

    D. Smith's Avatar

    I have no answers, only questions. I'm using a Windows 10 computer. In Moneydance I keep records of investments only. I own FCNTX in a traditional IRA, in a ROTH IRA, and in a joint WROS account with my wife. I'm American, using only $ as my currency. All three accounts show correct Current Values. Over time I have moved money in and out of one or all of these. The cost basis calculated by Moneydance is not correct on any one of them and there is no pattern to the errors as you can see below. Is there a way for me to simply enter the correct cost basis and as dividends are added and if I make buys and sells will Moneydance adjust when necessary, keeping the record correct going forward?

    This is what I have:
    FCNTX ROTH IRA: current value= $XXX
    Cost basis displayed by Moneydance when set at Average= 90% of the Current Value
    Cost basis when set at Lot Matching= 68% of the Current Value

    FCNTX Traditional IRA: Current Value= $XXX
    Cost basis displayed by Moneydance when set at Average= 131% of the Current Value
    Cost basis when set at Lot Matching= 43% of the Current Value

    FCNTX WROS JOINT ACCT: current value= $XXX
    Cost basis displayed by Moneydance when set at Average= 156% of the Current Value
    Cost basis when set at Lot Matching=30% of the Current Value

  27. 27 Posted by Stuart Beesley ... on 18 Jan, 2021 05:16 PM

    Stuart Beesley - JUST A FELLOW USER's Avatar

    FYI - We have seen/deduced that where splits are involved, the cost basis calculation sometimes gets confused... Some users have reported that when they turned LOT control on, and then matched the sells/buys in the LOT window, that the cost basis corrected itself.. Worth a try on one account in a test copy system....?

    (not support)

  28. 28 Posted by D. Smith on 18 Jan, 2021 10:51 PM

    D. Smith's Avatar

    Thanks Stuart,You may well be right. This fund did a 10:1 split in August of 2018. I will see later if I can do what you suggest, to match the sells/buys in the LOT window, when I figure out what you mean and how to do it. It looks to me like the cost basis reported for one of the three FCNTX holdings is correct in relation to my buys and no sells, but it disagrees with what the Mutual fund company reports as my cost basis.Thanks for the feedback.
        On Monday, January 18, 2021, 11:16:17 AM CST, Stuart Beesley - JUST A FELLOW USER <[email blocked]> wrote:
     
       
    |

  29. 29 Posted by davidvernonlong on 19 Jan, 2021 11:24 AM

    davidvernonlong's Avatar

    I corrected an incorrect average cost calculation which emanated from a stock split by cheating. Namely , by deleting the split and replacing it by a purchase at a very low cost per share (one penny in my example). A zero cost could not be entered. I know it worked because the cost shown in the portfolio is now the same as the “Cost Basis “ report and I checked the calculation independently. Sure you get a small rounding difference in your cash account, but that can be adjusted.
    I now have to go through my whole portfolio and do the same for all stocks where there is a split, as for tax reasons I have to use average cost not lot costing.

  30. 30 Posted by D. Smith on 19 Jan, 2021 01:33 PM

    D. Smith's Avatar

    Hi David Vernon Long,Thank you for providing a solution. I may need to follow your suggestion except that I too would have to make the same correction to numerous splits over the years, mostly in stocks. Splits, reorganizations, spin-offs, and mergers can get really complicated in determining the cost basis, at least for me, and how to enter it in Moneydance.
    D. Smith On Tuesday, January 19, 2021, 05:25:03 AM CST, davidvernonlong <[email blocked]> wrote:
     
       
    |

  31. Melissa closed this discussion on 27 Jan, 2021 03:15 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac