Black Scholes Option Pricing Model Explained In Excel
Вставка
- Опубліковано 19 чер 2024
- Get ready to dive deep into financial modeling with 'Black Scholes Option Pricing Model Explained In Excel'. This step-by-step tutorial will take you through the entire process, from declaring the Black Scholes inputs, to calculating the critical D1 and D2 values, all the way to valuing both Call and Put options. Plus, we'll delve into the crucial implications of the Black Scholes Model to give you a comprehensive understanding of this invaluable tool. Join us and enhance your skill set, whether you're a beginner or just need a refresher on the Black Scholes model in Excel
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfinance.com/produ...
Chapters:
0:00 - Declare the Black Scholes Inputs
1:46 - How to Calculate D1
2:54 - How to Calculate D2
3:24 - Value a Call Option
5:36 - Value a Put Option
7:51 - Implications of the Black Scholes Model
*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC
🎓 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/
In a real-life example, what would you use to calculate volatility?
This is a gold mine. No handwaving, etc. You actually just go through it step by step. No one else does this. Thank you!
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 😂
Thank you, I have been looking for an easy to follow excel video to explain B-S model.
Words cannot describe how excellent your content. Mad respect.
Thank you, that is a great complement! You've given me motivation to make the next video 💪
This is great. You a rare ability ro explained simply complicated things.
You're positive feedback really means a lot to me! Thank you
This has to be one of the most informative videos! Thank you so much!
You made it look so easy. Great
Thank you for taking the time in making and sharing this video it so easy to understand and well explained.
Great teaching 👍
Thk U ! It was so helpful!!
Thanks!! Just what I needed for my assignment. Really good explanations
You're very welcome!
Productive content & very nicely explained. Thank You...
Most welcome!
Thank you so much for doing this video. Very handy indeed.
It is my pleasure and I'm glad it was helpful!
Awesome dude! Thanks for your efforts!
It is my pleasure! Thank you for the support
Appreciate your efforts sir !!
Thank you Shashank!
Gr8 job explaining a complicated concept
I appreciate it Peter!
Great video! I learned so much!
Glad to hear that!
Awesome Sir Great
🙏 I apreciate your Excel sheet and the video
Glad it was helpful!
Thankyou sir 🙏,
I will learn some basic things, and ask questions from you.
My pleasure! Feel free to ask me any questions in the comments 👍
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
this video would have helped me so much 7 months ago when i was doing my finishing masters in financial risk management lol
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
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)
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
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.
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
perfecttttttttttttttttttttt
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
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: ua-cam.com/video/AukJ1gDeErw/v-deo.html
Also, I will have videos coming out in the future on the greeks
@@RyanOConnellCFA Thanks Ryan.. would be interesting to see your explanation on that.. better to go for nodes above 4
@@lee871 Sure thing, I'll take a look at it. It's definitely one that I think people would appreciate
You might set the Risk Free Rate to 0.0 and see if the Call price and Put price match when the Strike = Underlying
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.
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: ua-cam.com/video/eW3qZitMN9c/v-deo.htmlsi=vMTX_hr0WOIMLAMs
Hey great video, question: what does risk free rate mean?
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.
Heyy can you please explain, how do you consider the volatility rate, is it fixed or should I consider the Implied volatility ?
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.
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
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.
hi...can u tell me how to calculate SPEED (known as DgammaDspot or “the gamma of the gamma”) in excel .is it possible ???
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
Hey why do we take mean 0 and s.d 1 for normal distribution
Because those are the defining features of normal distributions
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.
What is the formula you are using to come up with that?
Hey can you make video on how to study Quantitative Methods CFA LEVEL 1? PLEASE and when are you going to upload the video on "Will AI take CFA's Jobs?"
Hey Gaurav, I'm not sure about the timing of the "Will AI take CFA's Jobs?" as I havent shot a talking head style video like that in over 2 years so there are couple of things I need to think through first. As for how to study Quantitative Methods for Level 1, I'm not sure I can make a full video out of that. I personally would use the Kaplan Schweser Notes for that if you prefer to learn by reading or Mark Meldrum if you prefer to learn via video.
@@RyanOConnellCFA Ok, Thank you so much and do you have your Instagram handle or twitter so that I can DM you for any such quires
@@Gaurav_Shrivastav It is my pleasure! No, I do not have any other social media besides LinkedIn but I never go on LinkedIn. Feel free to write any questions you have in the comments of my videos as there are likely many people with the same question as you!
@@RyanOConnellCFA ok, Thanks 😊
I want to use this formula for cryto but the probleme is to find the volatility
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
Strangely call option is more expensive than put in both cases when strike is below and above underlying price...
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
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
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.
WTF to the complexity of the BS model