Option valuation with Monte Carlo simulation (Excel) transcript

hi everyone and welcome again to nettle the go-to place to learn about business finance economics and much much more please don't forget to subscribe to our channel and click the bell notification button below so that you never miss fresh videos and tutorials you might be interested in many things to our current patreon supporters and youtube members for making this video possible and would also greatly appreciate if you consider supporting us as well so let's check the link in description or click the join button below for more details my name is saba and today we're going to apply monte carlo simulation to value options both call and put options and also discuss some useful heuristics in terms of option value formation so let's start with a simple case let's assume that we are valuing a call option and a put option with a strike price of 50 pounds per share for example and let's assume that the center price that is the current price of the underline is slightly below the strike at around 49.6 pounds per share for example then to do our monte carlo simulations we need to make some assumptions about annual return and annual volatility of the underlying of our stock price for example and that can be estimated from historical data that can be estimated using a wide range of different forecasting techniques uh let's keep it simple for now and say that our annual return is 10 and our annual volatility is 20 that's something that you can expect of a broad stock market index for example then we need to uh take into account the maturity of our option and uh let's say that the current date is 20th of january 2022 and the option maturity date the expiry date is 15th of april 2022. that means that we can uh calculate the time of our option contract um in terms of years to bring our return and volatility to the relevant frequency for our monte carlo simulations using the difference between the maturity date and the current date divided by 365. we can see that the time of the option in question is 0.23 years approximately then we can use that to calculate the return and the volatility for the time period in question using the simple formulas 1 plus annualized return to the power of time minus 1 gives us the expected return for the period of 2.24 and the volatility can be scaled using the square root of time giving us volatility for the period of roughly 10 percent then let's do 1 000 separate monte carlo simulations using random numbers random number generations in excel so let's start with the simulation one and then add one to it and go all the way down for 1000 simulations here is a simple trick that you can do to simplify and fasten this particular procedure we can use this field on the top left to get into the cell that we need we know that we start in cell a16 meaning that for a thousand simulations we need to go to the cell a here we can put any value into the cell go up and fill the whole column with simulation numbers then we can simulate our return using the normal distribution and the excel built in random number generator normal inverse of a random number between 0 and 1 of random probability and then input our expected return our mean and our volatility our standard deviation for the period we can enforce this formula and bottom line click it all the way down to have a wide range of different simulated returns then for our price we can simply apply this particular simulated return onto this enterprise so we get this enterprise lock in the row here and times it by one plus the simulated return then we can bottom like it all the way down as well and simulate payoffs of our long column long put options uh at each and every of the thousand simulated prices so for the long call we need to keep in mind that our call options are exercised out of the money when the price of the underlying is in excess of the strike price so that we can beneficially execute our call option uh buying the underline at the strike and selling it at a greater price at the market meaning that our payoff function this is gross payoff would be the maximum of the difference between the simulated share price and the strike price with strike price locked row wise and zero here we see that if the price of the underline is 43.9 pounds per share it's below the strike so we allow our call option to lapse and our gross payoff is zero in the similar fashion we can enforce this throughout our simulations and get our payoffs for each and every of the thousand for the put option the logic is the reverse put options are exercised in the money when the price of the underline is below the strike so that we can beneficially exercise our option buying the underline at a lower market price and then exercising our optionality and selling it at the strike price to our counterparty meaning that the gross payoff of the put option here would be the maximum of the difference between the strike row locked as well minus the simulated price and zero here we see that if the uh price of the underline is 57.95 a pounds per share we cannot beneficially exercise our put option so we allow it to lapse meaning the gross payoff is zero we can enforce it throughout and see that if for example the price is 46.91 pounds per share then we can beneficially exercise it and get a gross pay over three pounds nine bets and as you might already have noticed when we use the random number generator each and every time we enforce any function in excel all of these random numbers are recalculated meaning that these particular observations change every single time we input something yields our spreadsheet which is a pretty nice touch and now we can calculate the simulated monte carlo option values for the call and the port but just averaging over our 1000 simulated payoffs that is the break even option premium that would satisfy the no profit condition on average for our call option the premium equilibrium premium of the call option in this particular case would be 2.35 and for the put we can drag it across and see that the put option has a value a break-even premium of 1.47 pounds per share and here we obviously need to keep in mind that this particular simulation is sensitive towards the random numbers that go towards the calculations but we can nevertheless use this approach to quite robustly value different option contracts or even strategies option spreads options portfolios and test some of the heuristics in terms of option valuation we know for example that for a higher volatility of the underlying both calls and puts should be more valuable so we can check this by increasing the volatility to 30 percent and indeed seeing that the break-even premiere the fair values for both call and put go up considerably we can also check that the maturity um in terms of time until expiration also affects value in a very predictable way longer term options are more valuable than shorter term options both goals and puts so if we for example go one year further into the future our option value goes up considerably and if we go until the 15th of february only unlike in the previous simulation when we went all the way until 15th of uh april we see that the values did go down quite a bit but returning to our baseline case we can also test that lower volatility uh yields lower uh option premium lower option value so for example if volatility is 10 then both uh values go down quite a bit and finally we can test we can illustrate the relationship between call and put value and the strike price for example if our strike price goes up to 60 pounds per share for example then we can expect that the food option will become more valuable and the call option will become less valuable and that's indeed the case that we can see here and if we go the other way around if our strike price goes down we see that the call option becomes much more valuable than the put option that we have got over here and that's all there is for valuing options using monte carlo simulations and testing some useful valuation heuristics for european option contracts please leave a like on this video if you found it helpful in the comments below i need to see any further suggestions for videos and business finance or economics you would like me to record and please don't forget to subscribe to our channel consider supporting us on patreon thank you very much and stay tuned