Buy, Sell, Long, short, put options, call options, overvalued options, undervalued options…its madness I’m telling you, madness! That’s exactly how I felt when I first started trading options. I knew I liked the idea of them but with the losses starting to mount up I didn’t see how anything positive would result from continuing on. However, thankfully I did and as a result I’m here today to tell you, keep going! What you’ll find with each an every loss is a small lesson learned. Learn enough of them, survive, and you’ll be watching those profits stack up soon enough.

Ok, that’s enough pep talk for today. Let’s discuss a quick and simple strategy to find undervalued options. Black-Scholes! For a by the book definition of Black-Scholes before we dive into the reason for this post visit Investopedia.com. This will prepare you better than I could.

Using the Black-Scholes Pricing Calculator to find Undervalued Options

That’s right, that name you’ve probably heard at least a thousand times since you started trading but haven’t really taken the time to use or even refer to. That no good, awful pricing model that makes it’s way into every financial book ever written. Yeah, the very same one. However, take heart because using it isn’t quite as painful or mind numbing as you may think.

Using excel, we can quickly an efficiently view several different strikes to determine if they’re over or under valued. So, here we go.

Step 1 – Building the Black-Scholes Calculator in Excel

This is the most time consuming step. Once you have the calculator constructed you’ll be able to use it anytime quickly. Below is a step by step list to building the spreadsheet for review. Many of the steps below can quickly be copied from the image here.

Step by Step Instruction to build the Black-Scholes Calculator in Excel

• In Cell A1 enter the Ticker Symbol you’ll want to review. I’m using Starbucks or ticker: $SBUX
• In Cell A2 enter “Strike”
• In Cell B2 enter “Ask Price”
• In Cells A3:A8 Create a list of different strike prices for Starbucks. I prefer near the money.
• In Cells B3:B8 Create a list of the ask prices relative to those strike prices. If you create a list of call options be sure to select that in the type of option drop down later. Same for put options.
• In Cell C2 enter “Type”
• In Cell C3 enter “Call Option”
• In Cell C4 enter “Put Option”
• In Cell G1 enter “Strike Price”
• Click to highlight cell H1, In the top ribbon click “Data”, then click “Data validation” in the data tools section. In the allow drop down box select “List”. In the source box enter the following formula: =$A$3:$A$8
• In Cell G2 enter “Type of Option”
• Click to highlight cell H2, In the top ribbon click “Data”, then click “Data validation” in the data tools section. In the allow drop down box select “List”. In the source box enter the following formula: =$C$3:$C$4
• In Cell G3 enter “Stock Price”
• In Cell H3 enter the current share price. In this case, Starbucks is “$104.60”
• In Cell G4 enter “Time to Expiration”
• In Cell H4 enter the number of days to expiration divided by 365. In this case I will enter: =67/365
• In Cell G5 enter “Risk Free Interest Rate”
• In Cell H5 enter the current annual risk free interest rate. Just google it whenever you’re reading this. In this case I’ll enter “1.13%”
• In Cell G6 enter “Volatility”
• In Cell H6 enter the current annualized volatility. Google it. I’ll use “32.69%”
• In Cell G7:G16 copy the following

ln(S0/K)
(r+σ2/2)t
σ√t
d1
d2
N(d1)
N(d2)
N(-d1)
N(-d2)
e-rt 

Continue below to finish the Calculator and find that next great undervalued stock primed to move!

• In Cell H7 enter the following formula: =IFERROR(LN($H$3/$H$1),”na”)
• In Cell H8 enter the following formula: =($H$5+($H$6^2)/2)*$H$4
• In Cell H9 enter the following formula: =$H$6*SQRT($H$4)
• In Cell H10 enter the following formula: =IFERROR((H7+H8)/H9,”na”)
• In Cell H11 enter the following formula: =IFERROR(H10-H9,”na”)
• In Cell H12 enter the following formula: =IFERROR(NORM.S.DIST(H10,TRUE),”na”)
• In Cell H13 enter the following formula: =IFERROR(NORM.S.DIST(H11,TRUE),”na”)
• In Cell H14 enter the following formula: =IFERROR(NORM.S.DIST(-H10,TRUE),”na”)
• In Cell H15 enter the following formula: =IFERROR(NORM.S.DIST(-H11,TRUE),”na”)
• In Cell H16 enter the following formula: =EXP(-$H$5*$H$4)
• In Cell G17 enter “Black Scholes Options Price”
• In cell H17 enter the following formula: =IFERROR(IF($H$2=”Call Option”, $H$3*$H$12-H1*$H$16*$H$13, IF($H$2=”Put Option”, H1*$H$16*$H$15-$H$3*$H$14,”na”)), “na”)

Whew, that was exhausting to write but hopefully you were able to follow along and create the spreadsheet shown above. This will enable to you to quickly, anytime, enter a few pieces of information and begin looking for undervalued call options or undervalued put options. Should you ever want to add additional strikes for review just change the data validation settings to include every every strike.

Step 2 – Evaluating the Black-Scholes Price against the Current Ask Price

To view the call options select them from the drop down associated with type of option. Then select the strike you want to view in the H1 drop down and you’ll be shown the corresponding Black-Scholes price for that option.

If the price listed is higher for than the asking price for that contract then you’ll know its undervalued and as such a good candidate to buy. However, if the market isn’t favorable to move in the desired direction no amount of undervalue will save the trade from losing value.

Step 3 – Analysis & Placing the Trade

Move through each strike you’ve added to the sheet and find a contract that offers the most attractive value. You’ll want to conduct additional research to be sure the position will be successful but you can move forward confidently that you’ve found the most lucrative undervalued option.

Conclusion

The steps above discuss exclusively how to identify an undervalued option you may consider buying. The same information can also be used to find overvalued options that you may consider selling. To do that, simply look for the Black-Scholes price that is lower than the BID price and you’ll have a potentially lucrative trade ready to go.

The theory behind this approach to valuing options is simple. If the pricing model calculates the price as being fairly traded at a higher value then the asking price should move higher to meet the theoretical options price, Thus, gaining value for the buyer. Similarly, if the pricing model suggests the price of an option should be lower then the BID price will fall to meet the theoretical value. Therefore, losing value for the seller. Remember, buyers want the price to move higher and sellers would prefer the price to move lower.

Well that’s all for now. I hope you enjoyed this short post and were able to find some value for your own portfolio by finding all those undervalued options ripe for the picking.

God bless,

jeff

Want to take your trading to the next level. Check out this useful Trading Analysis & Journal Spreadsheet. Complete with the Black-Scholes calculator discussed above.