BS Option Pricing Model Spreadsheet Tutorial

[@ErykMroczny]

Mr Bracket, thank you so much for your video, I was failing my college module until I watched this video, which explained everything. Thank you!

[@kevinbracker]

You're welcome...glad I could help.

[@tawalters35]

Exemplary. I use Excel Real Time Data functions to populate my spreadsheets and needed an easy to graph the current value of complex spread trades. I have spent a lot of time building the graphs but have not been happy with my results. Your work really helps. Thank you so much for your fine explanations and spreadsheets.

[@femitex]

Outstanding job, much appreciated

[@mattflores3121]

Thank you sir for the great video !

[@mannysingh1838]

Thank you for spreadsheet.

[@tabasdezh]

Thanks Kevin for sharing

[@its.blessings171]

thank you so much! Would there be any changes to this spreadsheet if I am calculating a European option instead?

[@kevinbracker]

It's actually based on the european model where the option can not be exercised early. There are a few (very few) instances where American options would be priced differently due to dividends paid during the time the option is outstanding.

[@spencerkanen5975]

Hi Kevin is the volatility "Historical" and if historical is that a 1 year or 30 day or some other lookback.

[@kevinbracker]

The volatility is more "hypothetical" rather than estimated. You can get implied volatility from the model, which is essentially the market estimate of future volatility. I personally would use a 30-day lookback AND try to think about if anything (such as an earnings announcement) is going to be coming up and think about how that might impact it.

[@dawsonvogel6455]

Dr. Bracket, I was unable to attend your Investments II course while I was going to school. Will you ever be using this channel to provide information on real estate investing?

[@kevinbracker]

Dawson -- Good to hear from you. I had a little incident (just a little brain cancer -- YIKES!) pop-up right before Christmas that has put me on the shelf for awhile. Brain surgery on Christmas day for the win! The good news is that it is hopefully in the rear-view mirror (still will need to be monitored going forward) and should be in the "all-clear" mode by sometime in July-August. That said, I'll plan to be adding some RE Investment views going forward!

[@BB-ok1jt]

Theta is a per day measure so need to divide it by 365?

[@kevinbracker]

Theta is the annual measure. So, if the theta is -78.03, the option should lose (1)(78.03/365) or about $0.21 over the next day assuming all other variables stay the same. Looks like you have the concept right!

[@dodgingdurangos924]

@@kevinbracker Hey, Professor Bracker. I'm glad you made this video because it reminded me of a small error I found when I used your first video of the spreadsheet as a template to build my calculator. I thought you would've noticed by now, but I see you haven't changed a thing. I compared your results with the those of reputable online calculators, including the OIC's (not suggesting you are any less reputable). Everyone and everything checks out, except for your theta calculation didn't match up with theirs. To be fair, I don't know who's is accurate, but I tweaked your theta calculation to match the others. If I remember correctly, I excluded the 365 days input.

[@alexdurden2745]

hello, Kevin awesome video, very helpful, can you please show us the excel formula?

[@kevinbracker]

You should be able to download the spreadsheet from my Substack page (kevinbracker.substack.com/p/black-scholes-option-pricing-model). It's in the section titled "A Video Tutorial and the Spreadsheet" right above the KZbin link.

[@alexdurden2745]

@@kevinbracker Awesome, thank you I appreciate that.