Google Spreadsheet for various Options/Warrants

[racemize]

I've got a google spreadsheet I've been using for various options/warrants.  I thought I would open it up for anyone who might want to look at various warrants/options (only BAC 2015 12's right now) and/or use it as a template for their own analysis.  Please let me know if you see any errors and I haven't checked to make sure the A versus B warrants for BAC have the right switch over point.

 

https://docs.google.com/spreadsheet/ccc?key=0AhTPR9eP5nWedEF1SGVLdllJTnBMSDMzM3lYZ2d0SlE&usp=sharing

[racemize]

I've updated this to have all the warrants I could find, and added a few other features:

 

1) it now compares different warrants/options with similar expirations (e.g., BAC A vs B, C A versus B, 10and12 LEAPS for 2015 and 2014, etc.)

2) has annual needed growth (or cost of leverage)

 

I'm missing a few bits of information, which I will be working on (this is an active spreadsheet for me):

1) proper adjustments for C warrants after reverse split (adjusted the strike price up 10x, but I'm told the shares per warrant went down to 0.1, but this gives really insane results that don't seem to be right)

2) dividend adjustments for some of the more obscure warrants (how do I find the SEC filings for warrants?  I'm not sure what to search for)

3) the strike price adjustments for ones that have dividends over their threshold (how do I find the adjustments already made?  contact the IR for the company?

 

Any suggestions or corrections for the spreadsheet are invited!  I hope this is useful for others.

[valueinvesting101]

Great Spreadsheet!!!

Thanks for sharing!

 

Can you freeze title row? :)

[yitech]

I've updated this to have all the warrants I could find, and added a few other features:

 

1) it now compares different warrants/options with similar expirations (e.g., BAC A vs B, C A versus B, 10and12 LEAPS for 2015 and 2014, etc.)

2) has annual needed growth (or cost of leverage)

 

I'm missing a few bits of information, which I will be working on (this is an active spreadsheet for me):

1) proper adjustments for C warrants after reverse split (adjusted the strike price up 10x, but I'm told the shares per warrant went down to 0.1, but this gives really insane results that don't seem to be right)

2) dividend adjustments for some of the more obscure warrants (how do I find the SEC filings for warrants?  I'm not sure what to search for)

3) the strike price adjustments for ones that have dividends over their threshold (how do I find the adjustments already made?  contact the IR for the company?

 

Any suggestions or corrections for the spreadsheet are invited!  I hope this is useful for others.

 

For C warrants, I think you should multiply the quarterly dividend threshold by 10 instead of dividing by 10.

I believe the share per warrant should stay the same. It's warrant share counts  that also got reduced by a ratio of ten to reflect the reverse-split.

[racemize]

I've updated this to have all the warrants I could find, and added a few other features:

 

1) it now compares different warrants/options with similar expirations (e.g., BAC A vs B, C A versus B, 10and12 LEAPS for 2015 and 2014, etc.)

2) has annual needed growth (or cost of leverage)

 

I'm missing a few bits of information, which I will be working on (this is an active spreadsheet for me):

1) proper adjustments for C warrants after reverse split (adjusted the strike price up 10x, but I'm told the shares per warrant went down to 0.1, but this gives really insane results that don't seem to be right)

2) dividend adjustments for some of the more obscure warrants (how do I find the SEC filings for warrants?  I'm not sure what to search for)

3) the strike price adjustments for ones that have dividends over their threshold (how do I find the adjustments already made?  contact the IR for the company?

 

Any suggestions or corrections for the spreadsheet are invited!  I hope this is useful for others.

 

For C warrants, I think you should multiply the quarterly dividend threshold by 10 instead of dividing by 10.

I believe the share per warrant should stay the same. It's warrant share counts  that also got reduced by a ratio of ten to reflect the reverse-split.

 

I've read that it multiplied the strike by 10 and reduced the shares per warrant by 10--but I am not sure at all.  I need to go read the sec filing to make sure.  I changed the dividend threshold to multiply by 10.

[racemize]

Great Spreadsheet!!!

Thanks for sharing!

 

Can you freeze title row? :)

 

Thanks!  done!

[racemize]

I've updated this to have all the warrants I could find, and added a few other features:

 

1) it now compares different warrants/options with similar expirations (e.g., BAC A vs B, C A versus B, 10and12 LEAPS for 2015 and 2014, etc.)

2) has annual needed growth (or cost of leverage)

 

I'm missing a few bits of information, which I will be working on (this is an active spreadsheet for me):

1) proper adjustments for C warrants after reverse split (adjusted the strike price up 10x, but I'm told the shares per warrant went down to 0.1, but this gives really insane results that don't seem to be right)

2) dividend adjustments for some of the more obscure warrants (how do I find the SEC filings for warrants?  I'm not sure what to search for)

3) the strike price adjustments for ones that have dividends over their threshold (how do I find the adjustments already made?  contact the IR for the company?

 

Any suggestions or corrections for the spreadsheet are invited!  I hope this is useful for others.

 

For C warrants, I think you should multiply the quarterly dividend threshold by 10 instead of dividing by 10.

I believe the share per warrant should stay the same. It's warrant share counts  that also got reduced by a ratio of ten to reflect the reverse-split.

 

Here is where I got the adjustments from (among other places):

http://www.wnd.com/markets/news/read/18394533/citigroup_effects_reverse_stock_split

 

 

[locatevalue]

I've updated this to have all the warrants I could find, and added a few other features:

 

1) it now compares different warrants/options with similar expirations (e.g., BAC A vs B, C A versus B, 10and12 LEAPS for 2015 and 2014, etc.)

2) has annual needed growth (or cost of leverage)

 

I'm missing a few bits of information, which I will be working on (this is an active spreadsheet for me):

1) proper adjustments for C warrants after reverse split (adjusted the strike price up 10x, but I'm told the shares per warrant went down to 0.1, but this gives really insane results that don't seem to be right)

2) dividend adjustments for some of the more obscure warrants (how do I find the SEC filings for warrants?  I'm not sure what to search for)

3) the strike price adjustments for ones that have dividends over their threshold (how do I find the adjustments already made?  contact the IR for the company?

 

Any suggestions or corrections for the spreadsheet are invited!  I hope this is useful for others.

 

 

Thanks for sharing, I was trying to comapre warrants to options for a long time and to switch or buy if they makesense. I have never invested in options/LEAP's other than BAC warrants.  After Eric explanation i thought of doing this kind of spreadsheet but you are ahead of me. Thank you! appreciate you sharing your effort. I will let you know if i have any input but so far you captured everything about velocity of money from other discussion.Great work!

[bobp]

Citi warrants are now ten warrants plus $106 buys 1 common. So it's like paying 5.20.The dividend threshold should be ten cents per quarter. 

 

Vly exercise price is lower now. They've adjusted for the stock dividends. And there are two - vlyww and vly.ws.

[racemize]

Citi warrants are now ten warrants plus $106 buys 1 common. So it's like paying 5.20.The dividend threshold should be ten cents per quarter. 

 

Vly exercise price is lower now. They've adjusted for the stock dividends. And there are two - vlyww and vly.ws.

 

Thanks bob, I believe the citi warrants are correct now and do correspond to what you indicated above.

 

I'll add vlww--do you know the expiration date/dividend threshold for those?  Also, do you know the current adjustment to strike and/or if the shares per warrant has changed for either of the VLY warrants?

[infinitee00]

Thanks for the sharing the spreadsheet. Great work Racemize !

 

I have a few observations and requests.

 

1. Information about LNC Warrants and price adjustments can be found <a href="https://www.lfg.com/LincolnPageServer?LFGPage=/lfg/lfgclient/abt/ivrel/warnt/index.html">here</a>

 

2. Can you please add the following Warrants

 

Coamerica Warrants (<a href="http://finance.yahoo.com/q?s=CMA-wt&ql=1">CMA-WT</a>). They have a expiration date of 11/14/2018 and a strike price of $29.40 ( unadjusted)

 

Texas Capital Bancshares Warrants ( <a href="http://finance.yahoo.com/q?s=TCBIW&ql=0">TCBIW</a>). They have a expiration date of 01/16/2019 and a strike price of $14.84 ( unadjusted). <a href="http://www.zacks.com/stock/news/31661/WFSL">Here's</a> where I found the information from.

 

Washington Federal Warrants ( <a href="http://finance.yahoo.com/q?s=WAFDW&ql=0">WAFDW</a>). They have a expiration date of 11/14/2018 and a strike price of $17.57 ( unadjusted). <a href="http://www.washingtonfederal.com/getattachment/d0779a10-ae2f-4430-96ff-69730a0ad704/.aspx">Here's</a> where I found the information from. Note that the symbol for WF has changed from WFSL to WAFD since the warrants were issued

 

Webster Financial Corp Warrants ( <a href="http://finance.yahoo.com/q?s=wbs-wt&ql=1">WBS-WT</a>). They have a expiration date of 11/21/2018 and a strike price of $18.28 ( unadjusted). <a href="http://www.marketwatch.com/story/treasury-to-sell-webster-suntrust-warrants-2011-05-25">Here's</a> where I found the information from.

 

3. Also can you please add a note explaining switch over point and velocity of leverage and how you arrived at the formulae(/s)?

 

4. Would you consider adding a price/BV column as well? If you need help adding it please let me know and I can help you with it.

 

Thanks and great work again. If I have something to add, I will add to this thread.

[racemize]

Thanks for the sharing the spreadsheet. Great work Racemize !

 

I have a few observations and requests.

 

1. Information about LNC Warrants and price adjustments can be found <a href="https://www.lfg.com/LincolnPageServer?LFGPage=/lfg/lfgclient/abt/ivrel/warnt/index.html">here</a>

 

Added and adjusted!

 

2. Can you please add the following Warrants

 

Coamerica Warrants (<a href="http://finance.yahoo.com/q?s=CMA-wt&ql=1">CMA-WT</a>). They have a expiration date of 11/14/2018 and a strike price of $29.40 ( unadjusted)

 

Texas Capital Bancshares Warrants ( <a href="http://finance.yahoo.com/q?s=TCBIW&ql=0">TCBIW</a>). They have a expiration date of 01/16/2019 and a strike price of $14.84 ( unadjusted). <a href="http://www.zacks.com/stock/news/31661/WFSL">Here's</a> where I found the information from.

 

Washington Federal Warrants ( <a href="http://finance.yahoo.com/q?s=WAFDW&ql=0">WAFDW</a>). They have a expiration date of 11/14/2018 and a strike price of $17.57 ( unadjusted). <a href="http://www.washingtonfederal.com/getattachment/d0779a10-ae2f-4430-96ff-69730a0ad704/.aspx">Here's</a> where I found the information from. Note that the symbol for WF has changed from WFSL to WAFD since the warrants were issued

 

Webster Financial Corp Warrants ( <a href="http://finance.yahoo.com/q?s=wbs-wt&ql=1">WBS-WT</a>). They have a expiration date of 11/21/2018 and a strike price of $18.28 ( unadjusted). <a href="http://www.marketwatch.com/story/treasury-to-sell-webster-suntrust-warrants-2011-05-25">Here's</a> where I found the information from.

Added, although I'm hitting the import limit on the sheet and am now having to do several dividends on my own rather than pulling them.  I think you can use multiple sheets to get around this, but that will be annoying.  Adding more warrants will be tougher going forward, but I guess I can kill all the dividend imports if I need.

 

Do you know the dividend thresholds for these?

 

3. Also can you please add a note explaining switch over point and velocity of leverage and how you arrived at the formulae(/s)?

 

Added, but here are the notes anyway:

switch over point - This is the point (future common price) at which the growth of the warrant and the growth of the common are equal.  It is derived starting with the following equation:

(shares per warrant*(future price of common - (strike price - strike adjustment))/warrant price) = future price of common / current common price

switch over point with dividend -  This is the same switch over point, but it adds in all the dividends that will be missed, up to the div threshold, from now until expiry.

needed growth - This is the total growth needed to get to the switch over point with div

needed annual growth - This is the annualized version of needed growth, which is also Eric's "cost of leverage", lower is better

velocity of leverage - I made this term up, but essentially, it let's you know how much return you will get from the warrant if the stock is 10% more than the switch over point.  Accordingly, higher is better here. 

 

4. Would you consider adding a price/BV column as well? If you need help adding it please let me know and I can help you with it.

 

Well, the sheet is mostly for looking at the warrants, and not so much for valuing the underlying companies.  I don't want it to get too cluttered/big as it is already pretty large as it is.  Perhaps I could make another sheet that shows all of the companies and some fundamental data?  In any event, I can't very well pull in the book value into this sheet as I'm hitting the import limit.

 

Thanks and great work again. If I have something to add, I will add to this thread.

 

Thanks and thanks!  I am missing quite a few SEC filings and I still need dividend adjustments/expiry dates for some of these, so any help getting that info would be really appreciated.

[infinitee00]

I just sent you a pm with a link to the modified version of the spreadsheet ( Basically, I copied your spreadsheet and modified that).

 

Here's what I did just in case anyone else is trying to implement the spreadsheet ( you may already be aware of this).

 

1. In order to overcome the limit of 50 ImportData commands/spreadsheet, I used only one ImportData command per column. Here the trick is to use the 'JOIN' built-in google function (as I found out after considerable trial and error :) ).

 

e.g. Say one wants to import the last traded price  of MSFT, IBM, BAC. One can use a single ImportData command for every ticker above

 

=ImportData("http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=l1")

=ImportData("http://download.finance.yahoo.com/d/quotes.csv?s=IBM&f=l1")

=ImportData("http://download.finance.yahoo.com/d/quotes.csv?s=BAC&f=l1")

 

That's not a big deal if one has less than 50 ticker symbols and want to just import one data point for every ticker. However one soon runs into problems as the list of companies become greater than 50 and one wants to import multiple data points for each ticker, since Google limits only 50 ImportData commands/spreadsheet. One way to overcome this limit is to spread the symbols over multiple spreadsheets and the aggregate every data point in one spreadsheet, but that soon becomes cumbersome. However, there's also two other ways to do it as I have found out

 

1. By manually adding every ticker to the ImportData function

 

=ImportData("http://download.finance.yahoo.com/d/quotes.csv?s=MSFT,IBM,BAC&f=l1")

 

However, again this becomes unwieldy as the list of symbols become large and one has to manually add the tickers.

 

2. A better way to import that data IMO is to use the 'JOIN' function.

 

Say I have 200 ticker symbols in column B of a spreadsheet ( AFAIK we can only import max. 200 for each use of ImportData/ImportHTML function). In order to import the last traded price of all the symbols, we may insert this code into the 1st cell of column C (e.g. C1).

 

=ImportData("http://download.finance.yahoo.com/d/quotes.csv?s="&join(",",B1:B200)&"&f=l1")

 

The advantage here is that we use only one ImportData function, so we don't run into the 50 function limit as fast as before. Also if we have other data points ( e.g. P/E, P/B etc) we want to import, this will automatically update all of those when we change the ticker symbol in column B.

 

The only issue with this however is sorting. If we try to sort a column in which we use the Importdata function I have seen that it messes up the data, probably because the function is used only in the first cell of the column,. I have tried several techniques but none of them seem to work.  The only way, I have found to overcome this issue is to create another sheet in the same workbook and use the importdata function in that sheet ( with the ticker symbols from the original sheet) and  then referencing the values back to the original sheet. There may be other better ways to do it but that's what I could come up with (I am not a professional programmer !! :)) )

 

So, that is why I created 2 sheets in the same workbook. Sheet1 is original sheet you created and Sheet2 is where the imports get done. Now, the user can sort the data in Sheet1 if he/she chooses to. Btw, I noticed that sorting is locked on the spreadsheet. Is there a way to allow users to sort the data?

 

I don't have a link to the SEC files or the dividend/adjustments status for all the warrants and I think crowd-sourcing this part will help. Let me know if you have any questions and thanks for updating the spreadsheet.

 

 

[racemize]

Thanks, these are very helpful.  I'll probably do something similar tonight.  (If you have time to get the JOIN function working, I'd rather just copy yours =p).

 

Edit: I'm also not sure on why sort is locked--it isn't for me on my original sheet.  Perhaps it is because you copied a read only?  Doesn't make much sense really...

 

In the past, I've written a javascript function to do custom sorting for a spreadsheet, so we could probably do that if necessary.  I don't mind just looking at the data how it is though.

[racemize]

Ok, I've got it updated again to implement the better import function.

[racemize]

Webster Financial Corp Warrants ( <a href="http://finance.yahoo.com/q?s=wbs-wt&ql=1">WBS-WT</a>). They have a expiration date of 11/21/2018 and a strike price of $18.28 ( unadjusted). <a href="http://www.marketwatch.com/story/treasury-to-sell-webster-suntrust-warrants-2011-05-25">Here's</a> where I found the information from.

 

 

Is there any reason these are trading so high?  This has the highest needed growth on the whole sheet due to the price of the warrant.  Are you sure about the strike price?

[infinitee00]

Here is the prospectus supplement for the treasury auction. I think the strike price is correct.

 

http://www.sec.gov/Archives/edgar/data/801337/000119312511155539/d424b7.htm

 

I found out what the problem is. If you look at column F of the spreadsheet where you use Google Finance to import the current stock price. The value in some cells did not get imported at all, including Webster financial. Once you get the correct price you will see that the growth rate is comparable to other financials.

[racemize]

for some reason, all the dividends getting pulled from yahoo are wrong--I think it is a yahoo issue for today, but can anyone confirm?

[racemize]

I have taken a good chunk of today and gotten all the SEC filings for the warrants in the spreadsheet, so if you are looking for prospectus's for warrants, I probably have them! 

 

Almost everything is up to date, I'm just missing the following info:

BPFH - I need the adjustments to strike and shares/warrant, I've sent an email asking for it, but no response

VLY-WT  - I need the adjustments to strike and shares/warrant, again sent an email, but no response

VLYWW - the prospectus I have is from a merger and does not specify a dividend threshold (maybe there isn't one), but I don't have that value.  I also need the adjustments to the strike and shares/warrant -- email sent, but no response

 

Looks like dividends starting pulling correct data again!

 

Edit: found SBNY -- looks like the threshold is 0.00

[stahleyp]

race, props to you for this and sharing with all of us. you're a good man.

[racemize]

race, props to you for this and sharing with all of us. you're a good man.

 

Trying to give something back to the community, as it is has benefited me way too much my favor so far!

 

BTW, the bottom portion models options versus warrants, and is very interesting.  I'm still messing with it, but you can test a bunch of different situations with it.  Once I've gotten more confident in what I think the answer is, I'll make a post in the BAC leverage thread.  I may try to get ERICOPOLY to take a look first too.

[ValuableBehavior]

Wow! Just found this. Add another thank you to the pile

[racemize]

A few updates:

 

1) I've modified the "switch over point with div" calculation--it now uses the missed yield rather than the raw dividends.  Note that for the warrants, it is only the missed yield up to the threshold, since it gets the remaining dividends via adjustments.  This should be more accurate.

 

2) I've got needed growth with and without the div adjustment now, so that you can see what the cost of leverage is with and without the dividend adjustments--in some cases, you'll want to know one or the other, depending on how you are thinking/what model you are using.

 

3) I've added a ratio of the velocity of leverage to needed growth--the higher the better.  I'll note that BAC-As happen to be the worst out of everything on the sheet at this point. 

 

4) There is a new sheet called "Options Strategy".  The top portion has a LEAPS+common section that is attempting to model Eric's strategy with the calls.  The top portion also has the warrants+common with a model of their performance (and a comparison to the LEAPs+common).  These are kept at the same input leverage for apples to apples comparison purposes.  All inputs are marked in blue.  From discussing this with Eric, it is most accurate to not adjust the strike price/shares per warrant of the warrants, though there is some noise in the model. 

 

The bottom portion compares the LEAPS (reinvesting on large drops) versus the warrants.  This model is much rougher and also is not comparing the same amount of leverage/invested capital. 

 

This whole options strategy section is quite complicated, so it may not be that valuable without a long explanation.

[locatevalue]

 

Racemize, Once again thanks for sharing..

Option Startegy is very good and trying to understand it going through it now, but need more time. You seems to be very good with spread sheets. What you do for living(just curious!), I am guessing something related to software, I am a programmer by day job but wont be able to touch you on this front working with spread sheets! Great Work!

[racemize]

 

Racemize, Once again thanks for sharing..

Option Startegy is very good and trying to understand it going through it now, but need more time. You seems to be very good with spread sheets. What you do for living(just curious!), I am guessing something related to software, I am a programmer by day job but wont be able to touch you on this front working with spread sheets! Great Work!

 

Thanks!  I'm actually a patent agent with a degree in chemistry (though I write software patents), but I've always been around and messed with computers.