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.

Known Problems

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
• ... if you know of others, please open an issue or pull request

Install

To just use the sheet directly from Google, go here, File->Make a copy then you can start entering data. If you want to use the binary from the repo, instructions are below.

Install is more "import", but there's some fixup required because of a Sheets bug I haven't got a workaround for as yet.

There's a binary sheet attached to the releases, suitable for import into Google Sheets.

1. Download the latest sheet
2. Create a new Google Sheet - open this in new window
3. Go to File->Import->Upload->Browse - this will open a system file selection box. Select the downloaded sheet.
4. Choose Replace Spreadsheet, and select Import data
5. FIXUP: there's a Sheets import bug (#30) that drops the checkbox validation from Use for fraction column in ESPP and RSU sheets:
   1. Menu Data->Data validation, then Add rule at the bottom of the right side pane that opens.
   2. Set Apply to range to ESPP!N7:N26, Criteria to Tick box. Click Done.
   3. Repeat (2) but with Apply to range as RSU!J7:J84
   4. If ESPP!N5 is displaying #REF!, replace the cell formula with =COUNTIF(N7:N26, TRUE)
   5. Repeat (4) but for RSU!J5, replacing with =COUNTIF(J7:J84, TRUE)
   6. (the bottom sums are just for convenience and because people expect totals at the bottom - fix them up with the same formula if you care)
6. Import the AppScript (needed for running lot optimization)
   1. In the sheet, Extensions->App Scripts and copy the .gs files from the repo worksheet directory.
   2. Either reload the spreadsheet, or run the common.gs:onOpen function using the AppScript UI
   3. Menu Custom Functions->All balance to trigger auth prompts
   4. Accept the authorization prompts - like self-signed website certs, you need to look at the small links below the main warning and text to proceed.
7. Run the Custom Functions->Optimize per-lot (avgo basis) function - you'll be
   1. This sets the preference for each lot to cash or shares and you'll see the impact if choosing manual per-lot ratio in the Tweaks.
   2. If you want to make changes to the sheet, then export those changes for a PR, use the Custom Functions->Export Workbook (Censored). This will write json to a Google Drive location and is the mechanism I use to construct the json in the repo. These are intended for easy visual review of diffs. Well, easy compared to doing it as a spreadsheet.

Release Status

Using github project for planning - see Timeline

Using github project for planning - see Timeline

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 cash or shares
• run the optimizer to automatically adjust lot preference for maximum deferred tax (see steps below)

There's a binary sheet attached to the releases, suitable for import into Google Sheets.

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

This repo is free for non-commercial use. If you want to use any of it commercially, contact me.

Contents

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.

On "Upgrades"

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.

On use of Issues

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.

Form 8949 (to be filed with taxes)

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.

Form 8949 - (instructions)

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_gain

Links into the IRS webiste:

• IRS instructions for Form 8949
• IRS Form 8949

References

ESPP

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.

Terms:

• 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
• 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. Any market price - offer price delta due to share price change over the offering period is treated as captial gain (long term because you've inherently held for a year).

Reference

Form 8937

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)

Pro-rata vs other allocation of cash/share split

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.

Realized gain calculation

... 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 portion
• cash portion + FMV AVGO - VMW basis

You'll recognize the cash unless your VMW basis was higher than the FMV of the AVGO received.

Basis calculation - post-acquisition sale of AVGO

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

On Fair Market Value

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.

Fractional share sale

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:

1. receive fractional share
2. 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.