Black Scholes Option Pricing Model Explained In Excel

[@RyanOConnellCFA]

🎓 Tutor With Me: 1-On-1 Video Call Sessions Available ► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/ 💾 Download Free Excel File: ► Grab the file from this video here: ryanoconnellfinance.com/product/black-scholes-option-pricing-model-excel-workbook/

[@scotthood714]

In a real-life example, what would you use to calculate volatility?

[@beaverbridge]

This is a gold mine. No handwaving, etc. You actually just go through it step by step. No one else does this. Thank you!

[@RyanOConnellCFA]

Thank you so much! That tells you that either they don't understand the concept or they don't think the people they are explaining it to are capable of understanding 😂

[@uignireddngfiurdsgfiurdse]

Words cannot describe how excellent your content. Mad respect.

[@RyanOConnellCFA]

Thank you, that is a great complement! You've given me motivation to make the next video 💪

[@sharktank9810]

This has to be one of the most informative videos! Thank you so much!

[@RyanOConnellCFA]

My pleasure and thank you for the feedback!

[@ron3252]

This is great. You a rare ability ro explained simply complicated things.

[@RyanOConnellCFA]

You're positive feedback really means a lot to me! Thank you

[@luckylogger7594]

Thank you, I have been looking for an easy to follow excel video to explain B-S model.

[@RyanOConnellCFA]

Glad it was helpful!

[@miteshpatel6973]

Thank you for taking the time in making and sharing this video it so easy to understand and well explained.

[@RyanOConnellCFA]

Glad you enjoyed it!

[@alokchamediya3502]

Productive content & very nicely explained. Thank You...

[@RyanOConnellCFA]

Most welcome!

[@piyushrathi3208]

You made it look so easy. Great

[@jimherebarbershop8188]

Gr8 job explaining a complicated concept

[@RyanOConnellCFA]

I appreciate it Peter!

[@yves-donaldderenoncourt2468]

Great teaching 👍 Thk U ! It was so helpful!!

[@RyanOConnellCFA]

You are welcome!

[@madeleineprice1293]

Great video! I learned so much!

[@RyanOConnellCFA]

Glad to hear that!

[@julianhall6773]

Thank you so much for doing this video. Very handy indeed.

[@RyanOConnellCFA]

It is my pleasure and I'm glad it was helpful!

[@leandrofurquim2325]

Thanks for the really nice video and excellente explanation. cheers

[@RyanOConnellCFA]

Glad it was helpful!

[@johannesr4372]

Thanks!! Just what I needed for my assignment. Really good explanations

[@RyanOConnellCFA]

You're very welcome!

[@dhpdaedalusStudio]

Awesome! Clear, concise, and useful. Thanks!

[@RyanOConnellCFA]

It is my pleasure!

[@rossjenvey4448]

Thanks Ryan. This is an excellent video, and the free XL download is extremely helpful

[@rachelcopeland8428]

Wow! Love this! Thanks for sharing

[@RyanOConnellCFA]

Thanks for watching!

[@ShashankSrivastavaFinance]

Appreciate your efforts sir !!

[@RyanOConnellCFA]

Thank you Shashank!

[@SazuPoland]

Awesome dude! Thanks for your efforts!

[@RyanOConnellCFA]

It is my pleasure! Thank you for the support

[@caplongo]

🙏 I apreciate your Excel sheet and the video

[@RyanOConnellCFA]

Glad it was helpful!

[@PhilippeNadeau-kh1cl]

Thanks Ryan great tutorial! I was wondering what calculations would you use for Risk free rate and Volatility? (0.1 and 0.2 has been used repesctivly for demo purpose)

[@OurNewestMember]

Those are treated as inputs (not necessarily computations). For riskless rate, you could find the annualized return of something like a US Tbill maturing on option expiration. For volatility, you could determine the historical volatility for the stock or some other reference. Eg, for S&P500 options expiring about 3 months from today, maybe 4.9% is the riskless rate and 13% is the volatility

[@deepak2012able]

Thankyou sir 🙏, I will learn some basic things, and ask questions from you.

[@RyanOConnellCFA]

My pleasure! Feel free to ask me any questions in the comments 👍

[@danagutierrez7250]

That was awesome

[@RyanOConnellCFA]

Thank you Dana!

[@mehmetnayci8715]

Hey great video, question: what does risk free rate mean?

[@RyanOConnellCFA]

Hi @mehmetnayci8715, thanks for your question! The risk-free rate is the theoretical rate of return on an investment with zero risk, typically based on the yield of government bonds. It's a key input in financial models like the Black-Scholes option pricing model. Hope this helps explain it! Let me know if you have any other questions.

[@tonyabraham1037]

this video would have helped me so much 7 months ago when i was doing my finishing masters in financial risk management lol

[@RyanOConnellCFA]

Haha bad timing then! A lot of the videos I make are just things I'd have wanted to stumble across while I was studying

[@TableTennisLover1234]

Hi Ryan, great video! Could you please tell us the formula to use in excel to value that same option out in the future? For example, what would the value of that option be in 10 days if price, volatility, dividend and interest rate remain the same? How will time decay affect it using excel formulas. Thanks for any help or resources you can provide.

[@RyanOConnellCFA]

Certainly! To value an option in the future using the Black-Scholes model in Excel, you'll need to adjust the 'time to expiration' (T) variable to account for the 10-day period. You can do this by subtracting 10 days from the original expiration date and then recalculating the option price using the same Black-Scholes formula. The effect of time decay will be reflected in the reduced 'T' value, showing how the option's value decreases as it gets closer to expiration. Also, I just put out a video this morning explaining "The Greeks" using Excel, and the time decay is captured by theta which I explain in that video. You can find that here if interested: kzbin.info/www/bejne/m4iWoo2fqbKBb8Usi=vMTX_hr0WOIMLAMs

[@randommarko]

Hi Ryan, many thanks for your very insightful content. I noticed, that in the formula, when T is being increased from 3 to 4, the put value actually decreases. I am interested in rather long term options.. Is that to be expected due to the formula design? Cheers

[@RyanOConnellCFA]

That is very unusual! Typically, the option price should increase as the time to maturity increases. Perhaps the opposite is possible for a deep in the money put as the price of the option could decrease if the stock price rises over time

[@DaveWhitfield-x1t]

Great video but I am being really dense - how do you get the volatility symbol in excel?

[@monu284]

Awesome Sir Great

[@RyanOConnellCFA]

My pleasure!

[@47grams]

sorry im trying to understand the math behind why you put 10% as your risk free rate? is that just based on your own risk management or is there a calculated math behind the choice? new to this method but I love your video and how you explain the formula.

[@RyanOConnellCFA]

Hey there! I just chose it at random. If you want to be more precise, you can use a long term US Treasury Rate like the 25 year. You can Google that at any given time and go with that one. The risk free rate is often assumed to be the long term treasury rate as it is believed to be the worlds least risky cash flow

[@adwwd7098]

Heyy can you please explain, how do you consider the volatility rate, is it fixed or should I consider the Implied volatility ?

[@RyanOConnellCFA]

In the Black Scholes model, the volatility rate can vary depending on your approach. While I used a fixed rate for simplicity in the video, in real-world scenarios, it's often more accurate to use implied volatility, which reflects the market's forecast of a stock's potential movements and can be derived from current option prices.

[@adwwd7098]

@@RyanOConnellCFA Thank you for your time to reply the comment

[@RyanOConnellCFA]

@@adwwd7098 My pleasure!

[@lee871]

great videoI think it important to mention that you should calculate your risk free rate based on continuously compounding not simple interest rate. Would also be good to price it with BOPM and calculate the Greeks

[@RyanOConnellCFA]

Thank you, that is a good thing to remember about the risk free rate! I briefly discussed that when using Excels =exp() formula. Here is my video on the binomial option pricing model as you requested: kzbin.info/www/bejne/d6bOe2Sdecp4qNk Also, I will have videos coming out in the future on the greeks

[@lee871]

@@RyanOConnellCFA Thanks Ryan.. would be interesting to see your explanation on that.. better to go for nodes above 4

[@RyanOConnellCFA]

@@lee871 Sure thing, I'll take a look at it. It's definitely one that I think people would appreciate

[@ateeqrehman842]

Hey why do we take mean 0 and s.d 1 for normal distribution

[@kegomania]

Because those are the defining features of normal distributions

[@RyanOConnellCFA]

The standard normal distribution has a mean of 0 and a St. Dev of 1 as the previous commentor pointed out

[@Al-wt5kf]

Is the volatility here implied volatility

[@Fj8282haha]

Is it More profitable to sell 10 0.01 Detal contract or 1 0.1detla all other figures given equal? Say during a contango and backwardation period? Thx

[@RyanOConnellCFA]

Great question! This is my best educated guess for the answer to this question. While the total delta exposure is the same in both scenarios (0.1), there are additional factors that could influence profitability. In periods of contango or backwardation, the impact might come from changes in implied volatility or skew, which are not captured by delta alone. Also, selling 10 contracts of 0.01 delta might entail more transaction costs than selling 1 contract of 0.1 delta, as most brokers charge per contract. Furthermore, having 10 contracts instead of 1 allows for more flexibility in adjusting your position. If the market moves against you, you could close part of the position instead of the entire thing.

[@arghyapathak2497]

Every input all this model is static or fixed ,but my question how the volatility is getting calculated for that strike and that ultimately determine the value of the price of that strike ? But how volatility is getting calculated ???

[@RyanOConnellCFA]

Watch this short 5 minute video to see how you would calculate the volatility (also known as standard deviation) of an asset: kzbin.info/www/bejne/a2emmHmDpMaEjNE

[@arghyapathak2497]

@RyanOConnellCFA thank you,I have seen upper mentioned video ,as you have mentioned ,Are you saying here your input volatility is the annual standard deviations return (here .2)of the underlying stock ??if so then this volatility is nothing but a historical volatility of the underlying stock,right ??

[@RyanOConnellCFA]

@@arghyapathak2497 This is the most common methodology to computer volatility. There are different approaches but using the historical standard deviation is the most common and also very straight forward

[@alijhi]

You might set the Risk Free Rate to 0.0 and see if the Call price and Put price match when the Strike = Underlying

[@mr.cm007]

hi...can u tell me how to calculate SPEED (known as DgammaDspot or “the gamma of the gamma”) in excel .is it possible ???

[@RyanOConnellCFA]

Sorry, I'm not able to tell you off the top of my head as I'd have to do a lot more research on it

[@FirstLast-km5ko]

Keep in mind this is a European call and put option model. One additional thing is if you take the formula =norm.s.dist(d1,True) you will be given the hedge ratio (delta) which means that if the stock price increases by $1 the value of the call option will increase by this returned amount.

[@amirisma7972]

I want to use this formula for cryto but the probleme is to find the volatility

[@RyanOConnellCFA]

To apply the Black-Scholes model for valuing crypto options, estimating volatility can indeed be challenging due to the highly volatile and unpredictable nature of cryptocurrencies. A common approach is to calculate historical volatility using past price data of the specific cryptocurrency, or you could use implied volatility derived from the prices of existing crypto options if available

[@thomsontang1317]

Been struggling with d1, don't understand how come I use the same formula but have different outcome. It's always 0.3846, seems to be half of your answer.

[@RyanOConnellCFA]

What is the formula you are using to come up with that?

[@PawtasticSignals]

perfecttttttttttttttttttttt

[@_Mikekkk]

Strangely call option is more expensive than put in both cases when strike is below and above underlying price...

[@justinhodges5462]

Its linked to the risk free rate. The higher the risk free rate the bigger the difference. When rates drop puts get more expensive and calls less… but calls always more $$ than puts

[@RyanOConnellCFA]

Thank you for pointing that out Justin!

[@HumeWebb-r8f]

Hernandez Eric Smith James Martinez David

[@AaronLloyd-Jones]

The Black and Scholes equation is wrong: The Black and Scholes (risk-neutral) premium is the first moment of the option expiry for an asset that has all risk and no market return (the risk-neutral measure), that which has been debased of market return (by holding portfolio returns fixed flat at r). This idiotic asset (the risk-neutral measure) is stochastically dominated by bonds in that bonds have the same return (r) but without the risk whilst it is stochastically dominated by stocks since stocks earn market return for the equivalent amount of risk: bonds have LOWER RISK for the SAME RETURN as the debased market asset (the risk-neutral measure) whilst stocks have HIGHER RETURN for the SAME RISK as the debased market asset (the risk-neutral measure) Either way, the 'risk-neutral measure' is totally idiotic and stochastically dominated by all non-redundant asset classes. It is not deep and it is not abstract. All it is is the market asset without return (which is then used to price the derivative and so is wrong and inaccurate). If a trader wants an option, then he must not take an offsetting position that nullifies the option position. There is nothing risk-neutral about that. An option premium must have a mean mu in the drift term, otherwise it is wrong... wrong for derivatives and wrong for efficient and non-communist finance. nb: I had to say 'no risk' when I sat several of the courses in undergraduate (almost two decades ago). It was clear as day to me then that it was inaccurate (and proved by me definitively now more than one decade ago). I debunk Black and Scholes fully here: drive.google.com/file/d/1drOy89roxTawddpbFv03MEgrNSRwPRab/view?usp=drive_link here is new theory for markets (crystal ball formula): drive.google.com/file/d/1POgaFZxaXpGPbxDh8p9IHP_Kr2-VXok5/view?usp=drive_link PhD examiner report 3: drive.google.com/file/d/1z2Cflnp1uQ059GIonv2lzfqOj0EcMXrv/view?usp=drive_link PhD examiner report 2: drive.google.com/file/d/1K07G377R0ZSUs9ax6EXAzYealrjbo2vS/view?usp=drive_link PhD examiner report 1: drive.google.com/file/d/1BXwbk-uFrQDH_es_T5FiIJOnJ_42oA0q/view?usp=drive_link

[@RyanOConnellCFA]

Hi @AaronLloyd-Jones, thank you for sharing your extensive thoughts and resources. The Black-Scholes model, while foundational in the pricing of options, does indeed make simplifying assumptions, such as the risk-neutral valuation, which might not fully capture real-world complexities. Your critique highlights important considerations, and alternative models or adjustments are always valuable in broadening our understanding and improving financial theories. I appreciate the depth of your research and will take a look at your links for a more detailed perspective.

[@samlau2852]

WTF to the complexity of the BS model

[@RyanOConnellCFA]

It is definitely pretty complex