search

hickeng / financial

Repository containing financial spreadsheets
Other
207 stars 24 forks source link

Incorrect formula for ESPP Pending Ordinary Income (column BB) #117

Closed kumarsm closed 8 months ago

kumarsm commented 8 months ago

In the formula, qualifiedIncome should be MIN(gainFromSale,(0.15 MIN(grantDateFMV,purchaseDateFMV))) instead of MIN( gainFromSale, (0.15 grantDateFMV))

hickeng commented 8 months ago

There's some debate about this in #15. I started with what you described but have eventually determined that it's incorrect.

Look at the references in the ESPP readme section, but TLDR; there's a paragraph in the IRS code that notes you use the grant date if the final option price is dynamic:

For this purpose, if the option price wasn't fixed or determinable at the time the option was granted, the option price is figured as if the option had been exercised at the time it was granted.

and the various descriptions of how to do this on Fairmark, etc, all use grantDate price. Unfortunately none of them explain why it's not what you described and we had to do that legwork ourselves.