hickeng / financial

Repository containing financial spreadsheets
Other
207 stars 24 forks source link

Reconcile eTrade supplement and 1099 against sheet #8

Closed hickeng closed 7 months ago

hickeng commented 8 months ago

The supplement has subtly different values (cents) for some lots in terms of cash consideration.

Relevant f8937 fragment for how to handle this:

Treasury Regulations generally provide that a shareholder who surrenders stock and receives both stock and cash in a reorganization is treated as having surrendered each share for a pro rata portion of the stock and cash received

However, looking at the numbers of AVGO received in each lot in eTrade after the fact, the conversion ratio fluctuate between lots. It hovers around 0.52090 but isn't stable at 4dp and that throws off the cash portion at a per lot level vs what eTrade have in the supplement.

Despite the per lot flux, my numbers match the 1099 to the cent for STG.... if you only look at the cash portion. If you use the gain calc from f8937 then their 1099 is overstating my STG by 7.59%.

hickeng commented 8 months ago

Supplement is just wrong.

ESPP

RSU

hickeng commented 8 months ago

See #59 - this would use 1099 as data source for vmw qty, and 1099 inputs needed for f8949.

Planning on ignoring Supplement entirely.

hickeng commented 7 months ago

Checking specific lots for the Proceeds amount, eTrade hasn't been consistent in how they calculate AVGO shares per lot vs cash per lot.

Looking at the ESPP lots for 2016-07-31 and 2017-01-31 (the largest number of shares, so amplifies any differences), we have the following values calculated for cash received:

However in the 1099-B eTrade has $13,176.42 - so using the derived ratio and NOT the same ratio used to calculate AVGO shares for the lot. eTrade shows 25.334 AVGO, but using the same ratio to get that cash amount it should be 25.335.

Running through the other lots I find the following outliers (comparison vs derived ratio for holding and derived ratio for lot):

hickeng commented 7 months ago

This is sufficiently close apparently after discussion with CPA. If I'm still concerned I can add the toggle to use dollar level rounding!