Welcome, fellow members of the Turtle Pond.
This repo is going to hold all my spreadsheets (santized), research, and conclusions for the financials around the VMW->AVGO acquisition. Click on the burger menu on the top right of the readme pane for a table of contents.
By insistent recommendation of colleagues/friends that they want to be able to pay something, I haved enabled Sponsor for the repo (❤️). Don't feel obligated - my goal is to provide support... and to get peer reviewed in turn to improve my filing correctness! Basically standard Open Source motive.
Donations will go towards getting CPA validation of the calculations I'm using and answering questions needing professional expertise. I'll try to keep those issues curated such that the description is sufficiently clear and coherent to be passed directly to a CPA, but it'll be best effort. If there's questions you want answered that aren't addressed in the README and in that labelled set, please open an issue with the cpa label.
This repo is free for non-commercial use. Giving it to your CPA for your individual filing is fine - if they want to use it at any scale they should contact me.
This section is a quick reference for gathering critical data and what a CPA needs to be able to calculate basis information and gain/loss/tax.
There is a Getting Started section in the discussions forum.
All of this information should be in eTrade (see Known Problems if any is unavailable). Log into your eTrade account and follow the steps here. The instruction links below jump to specific steps. If you're logged into eTrade, the links provided deeplink into the appropriate eTrade pages where possible.
By the end of it you should have:
- ESPP Purchase Confirmations (PDFs) - instructions
- RSU Release Confirmations (PDFs) - instructions (same as above)
- Transaction log from 2023-11-21 to some time later, but at least after 2023-11-28 (screenshot or csv) - instructions
- Stock Plan Benefit History spreadsheet (xlsx) - instructions
- eTrade 1099-B (PDF) - instructions
- eTrade Supplement (PDF) - instructions (same as above)
Your CPA needs all of the information collected in the prior section, along with all the following if the conditions apply:
- If you held VMware shares over the Broadcom merger
- Form 8937 for the merger
- If you held VMware shares since before 2021-10-29 (the most recent Dell dividend)
- Form 8937 for the 2nd Dell distribution
- If you held VMware shares acquired via owning Dell shares during the 2021 Dell dividend
- Form 8937 for the Dell recapitalization
- If you held VMware shares since before 2018-12-27 (the first Dell dividend)
- Form 8937 for the 1st Dell distribution
If you needed to give your CPA any of the Dell distribution/recapitalization forms, let them know that the eTrade Supplement does not include the necessary basis adjustment. Also let them know that you're uncertain whether "imputed ordinary income from the ESPP bargain element" will be correctly reported via W2.
Please let your CPA know that they are free to use the spreadsheet to aid with your individual filing, but if they want to use it at any scale they should contact me.
There is a shared version of the spreadsheet on Google Sheets. Go here, select File->Make a copy, then you can start entering data. If you want to use the binary from the repo, see Install.
This entails:
- entering number of VMW shares and:
- RSUs - many vest dates are already populated, but you may need to add
Market Value Per Shareif yours is absent - ESPP - almost everyone has the same offering periods, so only
Current Contributionsfrom the confirmations and your initialPrevious Carry Forwardvalue are needed
- RSUs - many vest dates are already populated, but you may need to add
- details from eTrade transaction log - instructions
- Shares (exchange for cash)
- Shares (tender payment)
and that's it.
For completeness you should figure out which lot your fractional AVGO share came from and check the appropriate tickbox, but omitting this will have only a minor effect on things if you've any significant number of shares. You should do this if using the sheet for filing, but if using it for estimation, exploration, double-checking CPA figures, etc then it's non-critical.
Optionally you can add validation inputs used to sanity check against transcription errors, and W2/1040 information to inform a ballpark tax estimate.
Optionally you can add other sources of income to improve the tax estimation.
If you sold AVGO shares post-merger but in 2023, you can use the post merger sale of AVGO tweak to set the price at which you sold and generate the necessary basis and imputed income (ESPP) for filing.
Very, very optionally there is a tweaks section that you can play with to change the way the sheet works. This goes from minor things like chosing which Broadcom Fair Market Value to use (both Mean and Close seem to be confidently permitted) to major items like changing the per-lot ratios for cash/shares (note #13 if playing with this).
The sheet is still being actively refined and, while it's slowed, people are still finding issues to be fixed. The timeline has a stable version that's as vetted as viable towards the end of March. I'll be tagging a v1.0.0 that I will use for filing my extension in April.
The primary outputs from the sheet are:
Critical:
- Per-lot tax basis and gain data needed for Form 8949 to be filed with your tax return. Used to either populate a Form 8949 directly, or to correct 1099-B imports. Information relevant to now, such as this data, is tinted in pale blue.
- Per-lot tax basis, needed when selling AVGO shares post-acquisition. Information related to post-acquisition sale of shares is tinted in pale green.
Informational:
- Long and short term capital gains for 2023 and potential future values
- Ballpark tax liability estimate for Federal and California for making estimated payments
Taxes are due for the 4th quarter on January 15th of the next year... so 2024-01-15 was the deadline for paying any estimated taxes resulting from the merger.
If you've not met safe harbor thresholds then look at reducing penalty and interest amounts by filing using Annualized Income Installment method.
There's prelim step-by-step here but screenshots are on a different computer so that's it for tonight.
The problems we as VMW holders know of are collected here, along with groups impacted, and impact assment. All are works in progress:
- $0 cost basis and incorrect adjustment in eTrade Supplement
- the cascade consequences of this single issue account for the bulk of this repo
- incorrect code ‘N’ in Form 8949 generated by TurboTax
- treatment of proceeds as dividends
- substantially impacting non-US residents
- fractional share sold at incorrect price
- missing stock confirmations in eTrade
- missing lot information on eTrade 1099-B
- ... if you know of others, please open an issue or pull request
Using github project for planning - see Timeline
v0.1.4 - 2024-02-28
Gets the basics of Form8949 / cost-basis entry for TurboTax in place, adding Form 8949 sections in Summary and RSU/ESPP datasheets.
Additionally:
- fixed up Turbotax 1099-B import using the sheet Form8949 as test.
- various column renames for consistency, precision, and clarity.
- added Close value for AVGO FMV and noted where it's stated it's acceptable (costbasis.com)
- added ratio option for "pure" ratio from eTrade transaction log to see where that value has been used by eTrade.
- add section for import of 1099-B values - currently an unused placeholder for pending import
v0.1.3 - 2024-02-28
Fixes use of hardcoded test values in the per-lot optimizer logic which would have meant almost no-one trying it would have got a valid final ratio of stocks/cash.
v0.1.2 - 2024-02-22
Refines the per-lot treatment option and adds an input for a "date of sale" for post-merger AVGO to assess changes from Short Term Gains to Long (RSU), and from disqualified to qualified (ESPP) as the lots age.
If you put in a date prior to 2024 then it'll roll the gains from AVGO sale into the tax estimate... but remember that's a very naive estimate and check the logic in the sheet both works for your case and is sufficient for purpose.
The Tweak is found as a dropdown below the Fractional Share Values and contains dates of interest:
- last year - this let's you see a tax estimation
- today - calculate with todays date for LTG/STG and qualified/disqualified
- a series of dates on which RSUs transition from STG to LTG
On the same row there is a share price, set to AVGO live by default that you can overwrite with any postive value.
v0.1.1 - 2024-02-20
Adds per-lot treatment mechanism in the RSU and ESPP datasheets:
- select preference per-lot for
cashorshares - run the optimizer to automatically adjust lot preference for maximum deferred tax (see steps below)
v0.1.0 - 2024-02-18
Initial tagged release because the foundation is there:
- human readable diffs are sufficient for reviewing changes before commit
- basic inputs are complete - minimal input data needed and easily sourced from downloadable confirmation docs
- basic outputs are complete - per-lot cash amounts, gain, old adjusted vmw basis, and new avgo basis are calculated considering short/long term gain and ESPP qualified/disqualified status
- all planned changes are enhancements
The spreadsheet is exported from Google Sheets. I've tried opening it in Excel but it's not happy. If there are easy tweaks to make it comptiable that'll be my first change. Until then, import into Google Docs.
- spreadsheet for ESPP & RSU basis and realized gain cacluations
- I STRONGLY recommend that if you need to add rows to RSUs you append at the bottom of the data grid instead of preserving date ordering. This is so that it's easy to copy/paste into the reference sheet when it's updated, then re-append your custom rows at the end.
- IRS Form 8949 - this is what we need to file with taxes. See #1 for generation of values.
I've inserted missing RSU rows in date order once, but I'm not doing it again unless I come up with some magic way of loading/copy/pasting RSU inputs that factors in the additional rows. It's just too much of a pain to do the transcription of inputs from old version to new version.
For now I've coloured the Release Date for the added rows in a slightly darker pre-populated colour to indicate which are new. It helps.
See #9 for tracking a more general solution.
I'm opening issues to track questions and feature requests. Feel free to do the same.
If it's also applicable to me, I'll get to them (no latency guarantees). If not it'll be best effort.
This is the form used to report "Sales and Other Dispositions of Captial Assets". It's split into Short Term and Long Term gain sections, with a radio button (well, checkbox but radio button is the required behaviour) to record how it intersects with the 1099-B. If you need to use multiple radio buttons, then you must submit additional instances of the form (attaching the one with code Z in column (f) first). I'm working through the details of this in #1.
I used costbasis.com to get a comparison, and it gave me one that I agree with, but it didn't explain how that value was reached or provide reference links. I've not been able to find absolute references but I've worked through it from first principles and have the same values. The following reasoning is promoted from my working in #1.
This isn't added into the spreadsheet yet - I want to have human readable diffs for commits before that - but here for visible reference. I strongly suspect that the value of basis from eTrade 1099-B, which goes in column (e), will be incorrect. That means the values I calculate will need to go into column(f) along with appropriate code into column(e) which I've not yet extracted from the separate instructions.
I've derived the following from first principles with the following axioms:
- realized gain and adjusted avgo basis are inflexible values dictated by f8937
- f8949 proceeds is inflexible as an actual dollar value credited & fmv of shares received
- must pay tax on realized gain
- must pay tax on deferred gain
- difference between proceeds and basis reported on f8949 must equal realized gain
The only flexible value we can adjust to reconcile the above is the reported f8949 basis.
This logic is written agnostic of per-lot or per-vmw. I prefer normalized per-vmw, but we just need to use matching values for vmw_basis, avgo_fmv, and cash_recieved:
# known without calculation
f8949_proceeds = cash_received + avgo_fmv
# the alternate gain calculation from f8937
# translating it, this is also the real economic value received: total consideration - true basis
alternate_gain_calc = cash_received + fmv_avgo - vmw_basis
# the approximate threshold for per-share vmw basis (adjusted for dividends) where we switch clauses is
# 0.2520 * 0.521 * 979.50 = 128.601
if cash_received < alternate_gain_calc {
# composite vmw basis was lower than avgo fmv, ie. we've got a capital gain (avgo_fmv-vmw_basis) from the share consideration that is deferred.
# gain mandated by f8937 does not include deferred gain.
f8937_gain = cash_received
# the deferred gain from vmw->avgo conversion must still be realized in the future. Adjustment
# to avgo_basis is the way this is accomplished. This is rolled into the mandated f8937 basis adjustment.
# we need the 2023 f8949 to reconcile correctly in the future against the deferred gain resulting from the
# inflexible avgo_basis.
# we must realize the cash_received as gain therefore, with f8949_proceeds fixed as cash_received:
# f8949_basis = 0
# but we're deferring avgo_fmv - vmw_basis gain to the future, so we must not pay tax on it now, therefore
# f8949_basis += avgo_fmv - vmw_basis
f8949_basis = avgo_fmv - vmw_basis
} else {
# composite vmw basis was higher than avgo fmv - no deferral
# gain mandated by f8937
f8937_gain = alternate_gain_calc
# that's our true gain, so we should be ok with an avgo_basis of avgo_fmv... but f8937 says the adjust avgo basis must be:
# avgo_basis = vmw_basis - cash_received + f8937_gain
#
# This is still okay, as that simplifies
# avgo_basis = vmw_basis - cash_received + cash_received + fmv_avgo - vmw_basis
# avgo_basis = fmv_avgo
f8949_basis = vmw_basis
}
# adjusted avgo basis for future sale - this is mandated by f8937
avgo_basis = vmw_basis - cash_received + f8937_gainLinks into the IRS webiste:
The ESPP discount is considered ordinary income and should be reported on your W2 when you sell the shares. In the past it has shown up on VMW W2's in Box 14 Other, labelled as ESPP. However, the proportion of the discount treated as ordinary income vs long term capital gain depends on whether the ESPP shares are qualified or disqualified.
The recognition of ordinary income is not a consideration for lots exchanged for shares or mixed shares/cash. IRS tax code Section 424(c)(1)(b) explicitly excludes Section 356 exchanges from being considered as a disposition and 356 is what's noted in Form 8937 as governing the exchange. A big thank you to @alkom for tracking down the specific section references.
There's discussion in #15 regarding specifics - below is a distilation of current understanding of terms/impact relating to ESPP:
- disqualified - sold within either 1 year from purchase date (when you got the shares aka exercise) or 2 years from grant date (when the ESPP period started aka offering).
- qualified - held for 2 years past grant and 1 year past purchase
- option price - the Fair Market Value at ESPP grant date, minus the 15% discount
- market price - the Fair Market Value of the stock on the date of purchase
- offer price (my term) - the lesser of the market price and the grant date price
- purchase price - the amount you actually paid for it
- bargain element - the ESPP discount,
market price - purchase price
Qualified vs disqualified proportion of discount considered ordinary income and reported on W2:
- disqualified -
market price - purchase price- the entire discount, 15% plus any increase in share price over the offering period, is treated as income. - qualified -
offer price - purchase price- just the 15% reduction on offer price is treated as income. Anymarket price - offer pricedelta due to share price change over the offering period is treated as captial gain (long term because you've inherently held for a year).
These forms detail tax handling for an event. This includes qualified/unqualified amounts from dividends, how to adjust cost-basis, how to calculate gain that must be realized, etc. These are pulled from the Broadcom Invester Relations site.
The acquisition form mostly uses non-imperative language, which leaves a lot of optionality for other treatments. My personal plan is to use the "generally ..." guidence absent a strong endorsement from a CPA for a different treatment being valid.
Links to the relevant Form 8937's:
- Broadcom Acquisition (from repo)
- Dell recapitalization 2021 - (from repo)applies if you received VMWs by virtue of owning Dell shares during the spin out.
- Dell distribution 2021 (from repo)
- Dell distribution 2018 (from repo)
a shareholder ... is treated as having surrendered each share for a pro rata portion of the stock and cash received, based on the fair market value of such surrendered share, unless the terms of the exchange provide otherwise and are economically reasonable.
If a holder of VMware Common Stock acquired different blocks of shares ... at different times or different prices, any gain or loss may be determined separately for each block of shares ... . Any such holder should consult their tax advisor regarding the manner in which the Cash Consideration and Stock Consideration should be allocated among different blocks of shares of VMware Common Stock surrendered.
Per my reading, this explicitly states that we have the option to chose not to use a pro-rata approach to calculating the cash/stock split. I've an issue to collect data on when we're allowed to make this choice.
The first quote is referencing the FMV of a VMW share at the time of acquisition. This should be fixed at $192.48 (?) based on knowledge at time of execution. I've an issue to follow up on this.
... a holder ... who received a combination of ... Stock and cash ... will recognize gain (but not loss) for U.S. federal income tax purposes in an amount equal to the lesser of (1) the sum of the amount of the cash ... and the fair market value of the Broadcom Common Stock received in exchange ..., minus the holder’s adjusted tax basis in ... VMware Common Stock surrendered..., and (2) the amount of cash received for such share of VMware Common Stock.
We recognize gain, per lot, on the lesser of:
cash portioncash portion + FMV AVGO - VMW basis
You'll recognize the cash unless your VMW basis was higher than the FMV of the AVGO received.
The concise version of future tax basis of RSUs, calculated per lot, is:
- basis prior to merger - cash received + gain recognized
Gain recognized is the lesser of (per Form 8937):
- cash received
- cash received + FMV AVGO received - basis prior to merger
That simplifies to the lesser of:
- basis prior to merger
- FMV AVGO received
Yahoo Finance market data for the period in question
Fair market value generally is the price at which property would change hands between a willing buyer and a willing seller, neither being under any compulsion to buy or sell and both having reasonable knowledge of the facts.
... tax law does not specifically prescribe ... the fair market value ... for purposes of calculating any gain recognized .... One reasonable approach is ... the mean ... trading price on November 22, 2023, which is $979.50 .... Other approaches ... may be appropriate. You should consult your tax advisor to determine what measure of fair market value is appropriate.
I have an issue open to determine the knwoledge available to our hypothetical buyer/seller pair and add other FMV options to the dropdown in the spreadsheet.
A holder of VMware Common Stock who received cash in lieu of a fractional share ... will generally be treated as having received such fractional share and then as having received such cash in redemption of the fractional share. Gain or loss generally will be recognized based on the difference between the amount of cash in lieu of the fractional share and the tax basis allocated to such fractional share.
This details sequential steps of:
- receive fractional share
- sell fractional share
I'm interpreting that as needing to calculate the gain for the fractional share sale (step 2) using the new cost basis, post conversion. You'll have also seperately recognised gain on the conversion of the fraction (step 1). If the fraction was attributed against an ESPP share this implies recognizing the ordinary income associated with the fraction given it's a sale.
The amount paid for a fractional share of Broadcom Common Stock was based on the closing price of Broadcom Common Stock as reported on the NASDAQ on November 21, 2023, which was $981.20.
eTrade did not use the value explicitly specified in f8937 for the fraction. I do not know what consequence "based on" has in this sentence. I've an issue to determine handling for this.