General support discussion

[hickeng]

Opening a discussion as an anchor for general discussion about documentation, workflow, user experience, etc.

[jlaplante333]

Hi, just wondering about adding to ESPP and RSU tabs in the sheet. The dates only go up to 2022-08-31 for ESPP, (ESPP ended in 2022 right) so I am assuming the total in ESPP and total in RSU must add up to the same total correct (the OSPS total.) So if OSPS is 50 for example, ESPP total + RSU total must equal 50, correct?

The main thing why this was difficult was the missing adjusted cost basis. For me I am missing all adjusted cost basis until 08/31/2022, ie I will be paying a lot more taxes than I am supposed to. Where are the missing adjusted cost basis? Are they determined by the sheet based on other data, or do we need to calculate the price on that date and the conversion rate etc etc?

Thank you.

[hickeng]

So if OSPS is 50 for example, ESPP total + RSU total must equal 50, correct?

Assuming you're asking about the number of AVGO shares you received in exchange for surrendering your ESPP & RSU VMW shares, yes. The total AVGO associated with ESPP and RSU lots should be the total you have in OSPS.

The dates only go up to 2022-08-31 for ESPP

Yup. That was the last ESPP offering period.

Where are the missing adjusted cost basis? Are they determined by the sheet based on other data

Yes, the sheet does the calculation. The basis entering the merger is in the hidden columns for both RSU and ESPP sheets, column labelled Cost basis entering merger/VMW tax-basis per share. The Proceeds/Basis/Gain set for reporting to IRS this year is in the Form8949 section of the Summary, ESPP, and RSU sheets. See https://github.com/hickeng/financial/blob/main/usage.md#form-8949

However the basis isn't the only part that makes this difficult - there's also the fact the specific capital gain you realize in 2023 is dictated by Form8937 and there's two possible calculations where only the lesser value applies.

Beyond getting the gain calculation correct, there's also tracking the post-merger tax basis for reporting future sales, tracking imputed ordinary income from ESPP sales, etc.

[0guanhua0]

now the cost basis is average. can we pick the the stock to sell? that may reduce tax

for example we have 200 stock
2022 100
2023 100

now we sell 48% for each type. but can we sell more old stock? then it's long term gain

[hickeng]

What you're describing is a FIFO (First-In-First-Out) strategy.

See #13 for discussion about if/when this is valid. It seems to be allowed for, and the article linked here notes the IRS uses FIFO as default, and that you get to late-bind your basis allocation decision until it becomes tax relevant.

I'd talk to a CPA about if you should do this. From a mechanical perspective, set the cash/stock ratio dropdown to "Per-lot - from strategy or manual selection" and run the FIFO strategy from Custom Functions-> Apply Strategy - FIFO and the sheet will show you the adjusted STG/LTG amounts. The strategy works by adjusting the Prefer dropdown for each lot - you can do so manually if you want but you must leave sufficient lots as balance to allow the overall holding ratio to remain correct.

[schmidteng]

Any thoughts or overview guidance for adding NQSO support to complement ESPP and RSU?

[hickeng]

Sorry, I've no experience in those at all.

If you know the maths or logic for how they're handled at a tax level it shouldn't be that hard to add another sheet for them and update the consolidation sums.

If you can dig up that logic with reference material, open an issue and I'll look at adding a sheet for them.

[rainagalbiati]

Hi! I just followed the directions in turbo tax when you select "cost basis is missing" and used the form from "stock plan confirmations." Was easy enough but I didn't have that many options

[edinel]

George -- THANK YOU!
Question -- is it possible to document where the validation in B29-B32 references? I figured out the first one (it was pretty obvious. of course) but the ones in B30-32 are red right now and I am not a stud enough to parse the validations.

[hickeng]

Check out the Format->Conditional Formatting option to see the formula.

I've added notes on the validations to the doc here.
It could do with some more troubleshooting tips for when they're flagging an issue, but hopefully this is useful as a first pass.

They may well be red despite correct entry until you've entered the corresponding data into the datasheets.

[rainagalbiati]

Hi! First, George, THANK YOU!

Also, does anyone know how options fit in here? I know Broadcom just vested all of them and sold them, so does that mean tax was calculated from those at the time of sale and I shouldn't have to worry?

Thanks!

[hickeng]

There was another question about options here.

I did not have options and have no idea how they were handled or the tax implications.
As noted in that other question - if someone can dig up the logic/math for what happened with options and open an issue to track it, I'll look at adding a sheet, or at least documentation.

[pmt0002]

I know there is a spot for general capital gains on any other assets we might have had, but what about shares of VMW that were purchased on the market? How can we include those in this spreadsheet?

[hickeng]

If you held them over the merger, best option would be to add them to the RSU sheet, entering purchase date and quantity, then override the Market Value with the actual purchase price as the RSU one for the date won't be correct.

From a calculation perspective, there's no difference between shares purchased on the market vs a share from an RSU after vesting.

[pmt0002]

Thanks for the quick reply. How is the cost basis calculated then if there is no input for tax withheld since it was an outright purchase? Just want to be sure the gain calculation is reflecting the initial investment.

[hickeng]

Tax basis for RSUs isn't based on the withholding, it's the market value at the time you received it same as for market purchases.

On vest, an RS is considered income and is taxed as normal via payroll, with the dollar value of the full vest showing as income and withheld dollar value as tax paid on your W2. The withholding isn't the tax-basis for the vested shares - it's more like a forced estimated payment. If the withholding is too much or too little, the delta shows up as refund/debt when you file your taxes for the year.