Inconsistent Cost Basis amounts between different reports

Henry Chan's Avatar

Henry Chan

17 Nov, 2021 04:15 AM

As mentioined in another thread https://infinitekind.tenderapp.com/discussions/investments/5573-cost-basis-incorrect, the amounts shown in the Cost Basis report and the Portfolio report and the Securities Detail view can be different. I have encountered several instances of this now.(in MD 2022.2 (4060)).

The synopsis is that Cost Basis report shows the correct cost basis amounts (for a security with splits and sells before and after the splits), verified independently with Excel. Valuation is on default average cost basis. Whereas both the Portfolio Report and the Securities Report shows a different cost basis amount. I cannot reconcile how this latter amount was calculated.

My question is that if the Cost Basis report can show the correct amount, why do the Portfolio Report and the Securities Details show a different amount? Can MD make the latter shows the same amount as the Cost Basis report please?

  1. 1 Posted by Henry Chan on 17 Nov, 2021 07:01 AM

    Henry Chan's Avatar

    Attached are some screenshots to illustrate the issues:
    (1) - Securities Detail view - Cost Basis $5,078.37
    (2) - Securities Detail view - Cost Basis $5,078.37
    (3) - Portfolio Report - Cost Basis $5,078.37
    (4) - Cost Basis Report - Remaining Total $4,889.04
    (5) - Independent Excel verification - $4,889.04
    All calculations are based on Average Cost basis.
    4:1 stock split occurred 2005.04.08

  2. 2 Posted by Stuart Beesley ... on 17 Nov, 2021 08:12 AM

    Stuart Beesley (Mr Toolbox)'s Avatar

    I agree with you.. In the meantime, one solution that normally works where splits are concerned, is to switch to lot matching and match up the buys and sells. This often corrects the CB..

  3. 3 Posted by Henry Chan on 17 Nov, 2021 11:31 PM

    Henry Chan's Avatar

    I have not tried the Lot Matching method yet, since I need to use Average Cost for tax purposes. However, I have tried to effect the stock split by alternative means for an otherwise equivalent stock "Auckland I A L - h" (the previous stock is "Auckland I A L - e":
    Method B: "Buy" the additional 1200 shares from the stock split at $0.
    Method C: "Buy" the additioanl 1200 shares from the stock split at $0.01 (in total, not unit price).

    The results are:
    (B1) Security Details view ... Remaining Cost Basis $112,849.79 ... obviously wrong since MD will impute a share price of $100 rather than $0 for a $0 "Buy".
    (B3) Portofilio Report ... Cost Basis also $112,849.79
    (B4) Cost Basis Report ... Running Total balance $4,889.04 ... remains correct ;)

    (C1) Security Details view ... Remaining Cost Basis $4,889.05 ... now correct other than the extra $0.01 introduced by the notional "Buy" at $0.01 for all the additional 1,200 shares from the split.
    (C3) Portfolio Report ... Cost Basis also "correct" at $4,889.05
    (C4) Cost Basis Report ... Running Total balance $4,889.05 also "correct"

    Conclusion from all the above.
    1. Cost Basis Report remains correct for all methods of processing the stock split.
    2. A viable work around to get "correct" Cost Basis in the Portfolio Report and Securities Detail view is to use the notional "Buy/Sell" method rather than the "Stock Split" method in MD, but the notional amount cannot be $0. (I have used $0.01 as the total value for the transaction - not unit price.) This should apply at least for Average Cost method. The use of Lot Matching may also work, but I have not tried it, and Lot Matching is not acceptable for tax reporting in my case anyway.

    These two questions remain:
    1. Can the Portfolio Report and Security Detail view adopt the (correct) calculation used in the Cost Basis Report please?
    2. Why would MD insist on defaulting to a unit price of $100 rather than $0 when the total transaction value is in fact $0? (Is this by design or is this a bug?)

  4. 4 Posted by Henry Chan on 18 Nov, 2021 12:18 AM

    Henry Chan's Avatar

    Tried to do another tweak to get rid of the extra $0.01 from method C by:
    Method D: Method C + adding another notional "Sell" transaction for 0 shares for $0.01.

    Results:
    (D1): Security Details view ... Remaining Cost Basis $4,889.04, correct without the notional $0.01 difference.
    (D3) Portfolio Report ... Cost Basis also correct" at $4,889.04
    (D4) Cost Basis Report ... Running Total balance $4,889.05 remains with the $0.01 difference.

    Conclusion:
    Still a messy workaround. While the Security Details view and Portfolio Report are now correct, we are now still left with an introduced difference to the Cost Basis Report. On balance, I don't believe this last tweak is necessary in the scheme of things.

  5. 5 Posted by Henry Chan on 18 Nov, 2021 01:03 AM

    Henry Chan's Avatar

    Impact on price charts:

    Stock split method: Prices are correctly shown, including the automatic adjustment made to pre-split prices to align with post-split prices.

    Notional buy_sell method: Prices are distorted, with the pre-split prices remain unadjusted original prices making them non-comparable to post-split prices. Also chart is distorted with the notional $0.01 transaction.

    Conclusion:
    On balance, I believe I will stick with using the MD stock split function rather than the notional buy_sell workaround. To obtain the correct Cost Basis, I will use the Cost Basis Report and discard the incorrect information shown in the Portfolio Report and Security Details views.

    Would appreciate if IFK could please respond to my two queries:
    1. When can we expect the CB reporting in Portfolio Report and Security Details view be fixed to align with the correct Cost Basis Report calculation?
    2. Why does $0 or 0 share transactions trigger a $100 unit price in the system, rather than $0 unit price?

  6. System closed this discussion on 17 Feb, 2022 01:10 AM.

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