v0.1.8

Shared sheets: Google Sheets, (Office365 doesn't support iterative calculations, so dropped for now - use offline Excel attached to release)

• Dates now render in the style of 2021-Jan-31 to be utterly unambiguous under review - can still be entered in ISO format
• Default Form8949 display to be Cash Only instead of the prior combined cash and shares value
• Added a per-lot post-merger tax basis column in addition to the per-share basis, in support of reporting 2023 post-merger sales
• Support scenario where an ESPP lot is assigned to 100% cash - now includes the imputed income into the merger basis calculation
• Add summary cell for post-merger average tax basis - this is needed for some non-US tax reporting
• Added additional in-sheet doc notes for cells and columns
• Calculation and error handling changes - for neatness and unreachable edge case correctness, no impact on user values

What's Changed

• Reword basis calculation text for clarity by @kevinbuhmann in #142
• V0.1.8 staging by @hickeng in #149

New Contributors

• @kevinbuhmann made their first contribution in #142

Full Changelog: v0.1.7...v0.1.8

v0.1.7

Shared sheets: Google Sheets, Office365 Excel

Major changes to entry of data into RSU and ESPP, and allows configuration of Form 8949 value style:

• price data is now retrieved based on data from a reference sheet
• no need to insert rows for RSU or ESPP datasheets - all ranges reference the entire column below the headings.
• Form8949 Proceeds, Basis, and Adjustment can be switched between the two styles (cash & stock vs cash only) using a Tweak. See the doc.
• Adds additional documentation for Form 8949 and fractional shares.

Full Changelog: v0.1.6...v0.1.7

v0.1.6

Shared sheets: Google Sheets, Office365 Excel

BUGFIX NOTE: added a v0.1.6-b version of the binary sheets to the release assets that includes a hotfix for a formula that incorrectly skews across rows (ESPP qualified status, and RSU post-merger short term gain). Removed the old binaries to avoid mistaken use. Shared sheets had fix made in place and a comment added to cell ESPP!BA7 and RSU!AV7

Updates to ESPP post-merger basis and Form8949 columns for RSUs, and polish:

• Corrects the Form 8949 for RSUs to use (e) instead of (g) for the basis
• Completes the ESPP ordinary income calculation for post-merger basis adjustment
• Splits the ESPP post-merger basis into two columns, pure for use in tracking systems such as broker backends, or with imputed income adjustment for use on sale.
• Updates the sheet formula to be more comprehensible through liberal use of LET and variable names
• Additional documentation expansion including addition of two new known problems people are hitting
• Includes an Excel compatible sheet version, albeit without strategy logic

Full Changelog: v0.1.5...v0.1.6

v0.1.5

Shared sheet v0.1.5 is here

Substantial revision and update to documentation and allows selection of eTrade tax strategy for per-lot use, including:

• Getting Started section
• Examples for entering RSU & ESPP
• Step-by-step for adding new rows to RSU & ESPP
• Step-by-step for correcting Turbotax Proceeds and Basis for imported 1099-Bs
• In-sheet checklist for tracking progress through completion
• Various updates to in-sheet notes

Updates:

• Updated Dell 2021 Return of Capital number to match eTrade's use in 2021 and added draft Form8937 noting that estimated value
• Added effective rate calculation to estimate
• Added general mechanism for applying eTrade tax strategy from Account Settings if using per-lot approach
• Definitive answer that the merger is not a disposition so doesn't incur imputed income from ESPP bargin element

Includes bug fixes:

• Fractional share now correctly included in potential capital gain
• Other LTG was not being included in tax estimate
• Corrected ESPP basis calculation to use only grant date as per this comment

What's Changed

• Documentation improvements and Getting Started guide by @hickeng in #71
• Move instructions into repo from sheet by @hickeng in #76
• Fix potential capital gain calculation for fractional share lot by @hickeng in #78
• Fix failure to add Other LTG into tax estimate by @hickeng in #81
• Update 2021 return of captial to 1099-div value of 16.58 by @hickeng in #84
• V0.1.5 staging by @hickeng in #92

Full Changelog: v0.1.4...v0.1.5

v0.1.4

Adds step-by-step instructions for gathering necessary inputs in the repo Usage doc.
Adds Form8949 values into the sheet for use when correcting 1099-B values.

What's Changed

• Form 8949 and data collection documentation by @hickeng in #67

Full Changelog: v0.1.3...v0.1.4

v0.1.3

Fixes major bug in optimizer where it was using hardcoded values for stock/share targets instead of the values from the Necessary Inputs cells in the Summary sheet. This issue would have prevented it returning useful values for most people as the resulting number of shares to cash wouldn't balance with the deal ratio that the overall holding has to abide by.

What's Changed

• 55 import of v12 has issues with the use for fraction column by @hickeng in #56
• Optimizer uses values from Summary for number of shares to assign to cash/stock by @hickeng in #65

Full Changelog: v0.1.2...v0.1.3

v0.1.2

What's Changed

• Add structured collection of the known problems by @hickeng in #48
• Refines the per-lot optimization option by @hickeng in #51

Full Changelog: v0.1.1...v0.1.2

v0.1.1

Adds per-lot treatment mechanism in the RSU and ESPP datasheets:

• consolidated some tweaks into one that provides internally consistent behaviour
• select preference per-lot for cash or shares
• run the optimizer to automatically adjust lot preference for maximum deferred tax (see steps below)
• select "manual per-lot ratio" from Cash/stock ratio tweak to see the impact

Full Changelog: v0.1.0...v0.1.1

v0.1.0

At this point there's a reasonable option for human readable diffs going forwards. As such it makes sense to have this be the new baseline.

Features:

• enter minimal necessary info per-lot, sourced from definitive release and purchase confirmations
  • dates and purchase/market values prepopulated
  • deeplinks for download page of confirmation docs from eTrade
• cash income, short and long term captial gains per merger Form 8937
  • cash portion, realized gain, and basis adjustment per-lot
  • future potential captial gain and short/long term status
  • imputed income from qualified and disqualified ESPP sales
• tax estimates - federal and CA safe harbor thresholds, total liability, total outstanding

Necessary inputs:

• espp - shares purchased, current contributions, initial carry-forward
• rsu - shares issued
• which lot(s) your fractional share was sold from

Optional inputs for cross-check and comparison:

• number of Broadcom shares per-lot from eTrade
• rsu - shares traded (aka tax on vest)
• 2022 1040 and 2023 W2 data to support tax estimate
• filing status, deductions, other captial gain/income
• preferred Broadcom market value for calculating gain

Bugs:

• #32 - missing at least one RSU vest date

Full Changelog: https://github.com/hickeng/financial/commits/v0.1.0