Modelling stock options in ledger

[Eric Weigle]

I'm trying to figure out how to model stock options in ledger. My goals are that

  (1) individual lots are tracked separately by the grant price, and

  (2) I can track the potential value in a sane way.

This seems to work for (1) when I exercise for cash:

2014/06/01 Stock options - acquire

    Assets:Stock Options:Foo       4 FOO_OPTIONS {$100} @ $0

2014/12/01 Stock options - exercise

    Assets:Stock Options:Foo      -4 FOO_OPTIONS {$100} @ $0

    Assets:Stock:Foo               4 FOO @ $100

    Income:Transient Stock    $-400

2014/12/01 Stock options - sale

    Assets:Stock:Foo          -4 FOO {$100} @ $200

    Income:Capital Gains      $-400

    Income:Transient Stock    $400

    Assets:Checking           $400

But I can't seem to figure out a way to track what the exercise would be worth at, say, August in my example. I tried adding  a pricesdb value for FOO_OPTIONS of $150, but then it thinks I have $600 rather than $200, because it doesn't account for the $100 exercise price. All the transaction variants I've tried for FOO_OPTIONS {$} "rebase" the option, which is not what I'm hoping to do. 

I'm coming from Gnucash. What I did there was to track an account "Stock options" in dollars, and just do "change in value" transactions monthly. This achieved #2, but not #1.

Any suggestions appreciated. Maybe some magic with virtual transactions to track #1 and #2 separately?

Thanks

-Eric

[Martin Blais]

Hi Eric,

I haven't done this myself yet (I don't have options), but I think what you're doing is incorrect.

You're not meant to track the cost basis of unexercised options, e.g., what if they vest OOM?

I would track the cost of the option itself.

With the method I suggest below you can just enter price values for your particular option as the underlying moves.

For example, an ITM option close to expiration should converge closely to the payoff, so having a recent-ish database of option price values in your ledger should allow calculation of the cash equivalent.

I had a quick shot at it below, and this is probably closer to want you want (assuming ESP options, which I _assume_ an employer would automatically report the gains for on your W-2, for each exercise during the year, again, I haven't actually been in that situation so I might get some of the particulars wrong. I read up some IRS documents online to figure out what I needed, as I was curious).

I'd love to hear some feedback,

;; Notes:

;;

;; * I put the exercise price in the commodity name itself, e.g., FOO_X100, as

;;   in 'X' for strike price and '100' for 100$/share. This defines the

;;   instrument. Put the expiration date in there as well if you feel a need

;;   to do that (use the exchange codes if it makes sense to you).

;;

;; * I track the value of the option itself, not of the exercise price. When

;;   you "use" the option by exercising it, I would track that as an expense

;;   as in the below with 'Expenses:OptionsExercises' because the option 

;;   vanishes and it acts as a privilege token you've now used.

;;

plugin "beancount.ops.auto_accounts"

2014-06-01 * "Stock options - acquire"

    Assets:BorganSchmanley:FOO-X100        8 FOO-X100 {0.80 USD}

    Income:BorganSchmanley:OptionsVesting  ;; Note: Non-taxable if statutory

2014-12-01 * "Stock options - exercise"

    Assets:BorganSchmanley:FOO-X100       -4 FOO-X100 {0.80 USD} @ 120.00 USD

    Expenses:OptionsExercise            3.20 USD

    Assets:BorganSchmanley:FOO             4 FOO {120.00 USD}

    Assets:BorganSchmanley:Cash      -400.00 USD

    Income:BorganSchmanley:Gains      -80.00 USD ;; These gains to be reported on W-2 AFAIK.

2014-12-01 * "Stock options - sale"

    Assets:BorganSchmanley:FOO            -4 FOO {120.00 USD} @ 121.00 USD

    Assets:BorganSchmanley:Cash       484.00 USD

    Income:BorganSchmanley:Gains       -4.00 USD

;; In my sale example above, I assume you waited a bit before selling after

;; exercise and got a small gain/loss from it. If you did not and you sold at

;; 120.00 (same as FMV at exercise) then you would have a gain or zero.

;;

;; If you wanted to represent an exercise + sell in one transaction, you could

;; do it this way, without the stock, you'd just get the money:

2014-12-15 * "Stock options - exercise & auto-sale"

    Assets:BorganSchmanley:FOO-X100       -4 FOO-X100 {0.80 USD} @ 120.00 USD

    Expenses:OptionsExercise            3.20 USD

    Assets:BorganSchmanley:Cash        80.00 USD

    Income:BorganSchmanley:Gains      -80.00 USD ;; These gains to be reported on W-2 AFAIK.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eric Weigle]

Hi Martin-

Thanks for the response- I tried that and it mostly works.

Unfortunately, encoding the strike price in the commodity means that you need N entries in your price database for the same stock to get the value of N different grants. I also tried setting up an equivalency of the different commodities with the 'real' stock, but this gets back into it reports showing that an actual amount of income, which doesn't yet exist.

What I ended up actually doing is basically what I used to do: track estimated gross value of options in dollars. It's really what I want to know, anyway. I wrote a short python script that does the calculation given a list of grants and the price database, and spits out monthly records I copy into my ledger.

Cheers

-Eric

[John Wiegley]

>>>>> Eric Weigle <eric....@gmail.com> writes:

> Cheers
> -Eric

I can't help but appreciate someone named Weigle. ;)

John

[Martin Blais]

On Thu, Dec 25, 2014 at 6:59 PM, Eric Weigle <eric....@gmail.com> wrote:

Hi Martin-

Thanks for the response- I tried that and it mostly works.

Unfortunately, encoding the strike price in the commodity means that you need N entries in your price database for the same stock to get the value of N different grants. I also tried setting up an equivalency of the different commodities with the 'real' stock, but this gets back into it reports showing that an actual amount of income, which doesn't yet exist.

You need N entries because you actually have different commodities, that seems reasonable to me. Inserting price entries is something that can be automated with a little script so that shouldn't be a big issue IMO.

If you want to automatically derive the option prices, in Beancount something you could do is create a plugin that would automatically generate the price entries for the options you own (you can inspect the list of commodities to derive that automatically, encoding their strike price and expiration date in the commodity name itself or as metadata), automatically deriving their prices using theoretical option values (using Black-Scholes) given just the price of the underlying and the vol. You'd only need to enter a price entry for the stock, and find some way to input the vol (I would recommend inputting the vol as another commodity "VOL" or backing it out from price entries for the VIX if that's possible). So your plugin would build the price db, fetch the price of the underlying and the price of the vol, and generate new price entries. The theoretical prices shouldn't be incredibly so far from the current ones. You would sacrifice precision for convenience. Moreover, you could then compute various measures of sensitivity of your options portfolio to underlying, vol, other derivatives, etc.

What I ended up actually doing is basically what I used to do: track estimated gross value of options in dollars. It's really what I want to know, anyway. I wrote a short python script that does the calculation given a list of grants and the price database, and spits out monthly records I copy into my ledger.

Sure. 

OTOH if I were you I would _really_ want to know at least "At current vol (avg over last month), how does my portfolio of options vary for every $1 change in the price of the underlying?" Depending on your strike price the variation can be quite dramatic.

[Red Street]

This concerns Incentive Stock Options (ISOs). This is slightly different from what the OP wants, but on the same general topic. This is how I model my options, and I was looking for feedback, since I'm wondering if there's a better way. Let's assume for simplicity:

- strike price is $1.20

- market value at exercise is $2.00

- market value at sale is $2.50

1) Grant time:

2001-01-01 * Stock Grant  ; The options themselves cost $0. They simply confer the right to buy stock at the strike price. Let's assume the strike price is $1.20, as encoded in ORNG_120.

   Assets:Stock-Options:Unvested     100 ORNG_120

   Income:Stock-Options

2) Vesting time:

2002-01-01 * Stock Grant

   Assets:Stock-Options:Vested          100 ORNG_120

   Assets:Stock-Options:Unvested     -100 ORNG_120

   

3) Exercise time:

2005-01-01 * Exercise

   Assets:Stock-Options     -100 ORNG_120

   Expenses:Stock-Options  100 ORNG_120         ; We "spend" our option to be able to purchase the stock at the strike price. So it's an expense.

   Assets:Investments         100 ORNG {$1.20}

   Assets:Bank                  -$120  ;100 x $1.20

4) Sale:

2006-01-02 * Sale

   Assets:Investments                               -100 ORNG {$1.20}  @ $2.50

   Assets:Bank                                          $250

   Income:Stock-Options:Capital-Gains       -$130

Areas for improvement:

- as the OP pointed out above, there is no easy way to calculate the price of vested-but-unexercised, and unvested options

- ORNG was purchased at $1.20 at exercise time. This is suboptimal because ledger, for example, would assume $1.20 was the market price of ORNG on that date, which is untrue. Is there a way to model things better using generic bookkeeping methods?

- others?

[Martin Blais]

On Tue, Feb 3, 2015 at 9:40 PM, Red Street <redst...@gmail.com> wrote:

Areas for improvement:

If I read this right, you're essentially doing the same thing as what I suggest except

1. holding your options with no cost basis.

2. holding unvested options in an asset account. 

About (1), I forget the rules for taxation of options, but surely when they are granted to you they have a cost associated with them. This could be the cost basis you use instead of no cost basis.

About (2): It's unfortunate that these options will appear on your balance sheet.

- as the OP pointed out above, there is no easy way to calculate the price of vested-but-unexercised, and unvested options

Why couldn't you have a script that parses your ledger, extracts the options instruments, parses your commodity conversion to get the strike prices and then spit out price entries from estimated vol/underlying values gathered from the market?

Just plug in estimated numbers into this, you should get a coarse estimate:

http://en.wikipedia.org/wiki/Black%E2%80%93Scholes_model#Black-Scholes_formula

 

- ORNG was purchased at $1.20 at exercise time. This is suboptimal because ledger, for example, would assume $1.20 was the market price of ORNG on that date, which is untrue. Is there a way to model things better using generic bookkeeping methods?

1.20 is the cost basis, not the market price. Why do you say it assumes 1.20 is the market price?

[Red Street]

If I read this right, you're essentially doing the same thing as what I suggest except

1. holding your options with no cost basis.

2. holding unvested options in an asset account. 

About (1), I forget the rules for taxation of options, but surely when they are granted to you they have a cost associated with them. This could be the cost basis you use instead of no cost basis.

ISOs actually don't have any sort of a cost associated with them. If you don't use them, you lose nothing. In fact, this was one of the questions I was going to ask - if Ledger or Beancount would be okay with this, but I found that they are.

 

About (2): It's unfortunate that these options will appear on your balance sheet.

I'd actually prefer that they do (this is basically future promised income), but your pointing this out made me realize that I should probably use a different commodity for vested and unvested options, and set the price of unvested options to zero.

 

- as the OP pointed out above, there is no easy way to calculate the price of vested-but-unexercised, and unvested options

Why couldn't you have a script that parses your ledger, extracts the options instruments, parses your commodity conversion to get the strike prices and then spit out price entries from estimated vol/underlying values gathered from the market?

Just plug in estimated numbers into this, you should get a coarse estimate:

http://en.wikipedia.org/wiki/Black%E2%80%93Scholes_model#Black-Scholes_formula

Will look into this. I was thinking of a simpler (current_price - strike_price) to value them, which is what I currently use.

  

- ORNG was purchased at $1.20 at exercise time. This is suboptimal because ledger, for example, would assume $1.20 was the market price of ORNG on that date, which is untrue. Is there a way to model things better using generic bookkeeping methods?

1.20 is the cost basis, not the market price. Why do you say it assumes 1.20 is the market price?

Perhaps my understanding is not correct, but I was under the impression Ledger would assume that to be the market price of that commodity on that day without other overriding data. Also, what would happen to systems that extract the market price based on this cost basis - for example, to compute net worth on a particular day, or investment returns, or unrealized profits/losses?

[Martin Blais]

On Tue, Feb 3, 2015 at 10:28 PM, Red Street <redst...@gmail.com> wrote:

If I read this right, you're essentially doing the same thing as what I suggest except

1. holding your options with no cost basis.

2. holding unvested options in an asset account. 

About (1), I forget the rules for taxation of options, but surely when they are granted to you they have a cost associated with them. This could be the cost basis you use instead of no cost basis.

ISOs actually don't have any sort of a cost associated with them. If you don't use them, you lose nothing. In fact, this was one of the questions I was going to ask - if Ledger or Beancount would be okay with this, but I found that they are.

Interesting. Beancount doesn't currently allow a zero cost basis but that's mainly a constraint in input. I wonder if anything would break if we allowed it. I think it might make sense. 

About (2): It's unfortunate that these options will appear on your balance sheet.

I'd actually prefer that they do (this is basically future promised income), but your pointing this out made me realize that I should probably use a different commodity for vested and unvested options, and set the price of unvested options to zero.

 

- as the OP pointed out above, there is no easy way to calculate the price of vested-but-unexercised, and unvested options

Why couldn't you have a script that parses your ledger, extracts the options instruments, parses your commodity conversion to get the strike prices and then spit out price entries from estimated vol/underlying values gathered from the market?

Just plug in estimated numbers into this, you should get a coarse estimate:

http://en.wikipedia.org/wiki/Black%E2%80%93Scholes_model#Black-Scholes_formula

Will look into this. I was thinking of a simpler (current_price - strike_price) to value them, which is what I currently use.

You mean min(0, current_price - strike_price).

You could also use a simple binomial approximation.

  

- ORNG was purchased at $1.20 at exercise time. This is suboptimal because ledger, for example, would assume $1.20 was the market price of ORNG on that date, which is untrue. Is there a way to model things better using generic bookkeeping methods?

1.20 is the cost basis, not the market price. Why do you say it assumes 1.20 is the market price?

Perhaps my understanding is not correct, but I was under the impression Ledger would assume that to be the market price of that commodity on that day without other overriding data. Also, what would happen to systems that extract the market price based on this cost basis - for example, to compute net worth on a particular day, or investment returns, or unrealized profits/losses?

I don't know how Ledger's price database works.

[Matthew Harris]

On Thursday, December 11, 2014 at 10:07:55 PM UTC-8, Martin Blais wrote:

;; Notes:

;;

;; * I put the exercise price in the commodity name itself, e.g., FOO_X100, as

;;   in 'X' for strike price and '100' for 100$/share. This defines the

;;   instrument. Put the expiration date in there as well if you feel a need

;;   to do that (use the exchange codes if it makes sense to you).

Note that because the commodity name is limited to 12 characters, the strike price barely fits. ("SYMB_123.45" is 11 characters already.)

 

2014-06-01 * "Stock options - acquire"

    Assets:BorganSchmanley:FOO-X100        8 FOO-X100 {0.80 USD}

    Income:BorganSchmanley:OptionsVesting  ;; Note: Non-taxable if statutory

2014-12-01 * "Stock options - exercise"

    Assets:BorganSchmanley:FOO-X100       -4 FOO-X100 {0.80 USD} @ 120.00 USD

    Expenses:OptionsExercise            3.20 USD

    Assets:BorganSchmanley:FOO             4 FOO {120.00 USD}

    Assets:BorganSchmanley:Cash      -400.00 USD

    Income:BorganSchmanley:Gains      -80.00 USD ;; These gains to be reported on W-2 AFAIK.

2014-12-01 * "Stock options - sale"

    Assets:BorganSchmanley:FOO            -4 FOO {120.00 USD} @ 121.00 USD

    Assets:BorganSchmanley:Cash       484.00 USD

    Income:BorganSchmanley:Gains       -4.00 USD

I'm a little confused. If the strike price is $100, what's the $0.80 amount?

[Martin Blais]

I could increase the max size for currency names it's an arbitrary choice

--

[Martin Blais]

0.80 is the cost basis of the option

To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+unsubscribe@googlegroups.com.