Barrier option valuation: Monte Carlo and historical simulations (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 that bell notification button below so that you never miss fresh videos and tutorials you might be interested in many things star current patreon supporters and youtube members for making this video possible and would also greatly appreciate if you consider supporting us as well so please share the link in description and click the join button below for more details my name is saba and today we're investigating quite a challenging yet rewarding topic that is how to value barrier options that is knock in and knock out options the player structure which we have investigated in one of the most recent videos using simulations that is monte carlo simulations and historical simulations by the way shout out to stephen huntley for suggesting this series of videos here we have got a quite established us stock which is apple and its stock price dynamics for the past five years and will seek to value both knock in and knock out calls and puts using a monte carlo simulation which is a parametric procedure as well as historical bootstrap simulation so buckle up and enjoy the ride for the barrier option valuation we first need to figure out what the sensor price that is the current price of the underline is and here we can simply refer to the most recent price at the month end of may 2022 which is 149.64 we need to also keep in mind with the strike of our barrier options uh is and let's assume that it's quite close to the spot price you can change it later uh without performing any further calculations which is uh the nice property of the template we're building so let's stick with 150 so far and let's uh input the barriers for our puts and calls again most commonly the puts have a barrier that it's below the strike so let's say 135 that would be the barrier for both the knock in and knock out puts so down and in and down and out and for the uh barrier calls you most commonly have an upper barrier that's higher than the strike so let's say 165 that would be a barrier for both our up and in and up and out calls we also need to input the maturity of our option contracts and let's say 21 days a month again uh this also can be changed without any uh hassle uh what we also need to keep in mind is that we'll estimate both of our simulation procedures using historical data so we'll need to use logarithmic returns for a number of reasons that are quite relevant in option valuation and that means that we'll have to calculate logarithmic daily returns so the logarithm of the ratio of consecutive prices enforcing it throughout and that means that we can calculate our average daily return again one of the nice properties of logarithms is that you can apply the arithmetic average to them without any loss of consistency and sample standard deviation of such log returns and finally we need to know what our sample size is that is just counting our logarithmic returns and now let's proceed to the main part of our video which is how to simulate uh and use the simulation to extract the fair values the justified premier for each of the four option contracts that we have in our hands well unlike with vanilla options where you only care about the final exercise price where you only care about the final price of the underline at maturity that then uh fully dictates what the payoff of the option contract is uh our knock in and knock out options also care about the path the underlying price took throughout the maturity period throughout the lifetime of the option contract because let's say the price of the underlying for the knockout call even if it is like 160 at the end of the period however it reached something like 170 during the period the option would already become inactive so you could not exercise it at the end of its lifetime which is something to keep in mind which is also the reverse for example something like a knock in put even if the price at the very end of the period is something like 140 which is above the barrier and that may hint uh towards it being uh inactive but if at some point during the period the price crossed the 135 barrier for example it has been like 130 for a period of time the option contract would become active and stay active and you could exercise it at the end even if the price at the end is not below the barrier so to keep that in mind we'll need to simulate the full path of our underlying price and then figure out what the maximum and the minimum price over the course of the period has been to determine whether our knock in and knock out options are active or inactive so let's start with the easy bit let's start with simulating the path of the underlying price movement we'll start by inputting our center price as the price at day zero because well at day zero nothing has yet happened and in every consecutive day we scale the previous underlying price by the exponent of a simulated log return and that is another nice property of log returns that comes in handy for such simulations if you plug it into the exponential function you get back one plus regular holding period return which is very convenient and handy mathematically so we plug in the normal uh inverse distribution norm.if our probability is a random number between zero and one so just the rand function in excel and as our mean and standard deviation we plug in the average we have estimated locking it throughout as well as the volatility we have estimated locking it throughout and that allows us to estimate the path the underlying price takes throughout the lifetime of the option so here for example in this simulation the price has first dropped slightly and then it started a rally reaching 175 as its highest and ending up 169 which is quite relevant for our barrier option pricing which is also quite neat in terms of the monte carlo simulation with random numbers is whenever you do anything with cells all of the simulations uh recalculate automatically meaning that you always have a new simulation uh taking place on your spreadsheet if you don't want that you can at the end of your calculations just save and paste all of your simulated prices as values to avoid the um your excel spreadsheet to recalculate the simulations every single time however what we are interested in now is to figure out what the last price is and we can see that it changed because the simulation has run again the maximum price the all-time highest price throughout the contract's lifetime and the lowest the minimum price throughout the contract's lifetime and now we need to um estimate what these prices imply for the payoffs of our barrier options and here we estimate the gross payoffs only as the purpose of our simulations is to estimate the fair value which is the fair premium that those options would theoretically entail so first we need to investigate the gross payoff of a knock-in call we need to check whether the underlying price at some point during the options lifetime broke through the up barrie of 165 so if our maximum price is below the up barrier which is 165 then the option remains inactive meaning that it's gross payoff is zero however if it is active if it has become active then the conventional long call logic applies the maximum of zero as well as the difference between the last price again we exercise it at the end so last price it is not the max price minus the strike which is over here so here we see in this particular simulation given that the max price is below the barrier even though the last price is above the strike the knock-in call remained inactive so we couldn't exercise it and gained a payoff of zero for the knockout call we can copy this formula and tweak it slightly reflecting the fact that if the max price is greater or equal to the up barrier so if this barrier has been reached at some point then the option will become inactive and otherwise it would be a conventional long pole so here quite conveniently we have got a case a simulation where our maximum price is below the barrier and our last price the at the end of the lifetime at maturity is above the strike meaning that we can exercise our knockout call beneficially for a gross payoff of two point sixteen dollars per share for the knock input um the uh idea is very similar we need to check if the minimum price of the underlying throughout the period is below or at the down barrier that we need to log and in that case the option becomes active meaning that we can input the conventional payoff of a long put contract which is the maximum of zero and the difference between the strike and the last price at the end of the contract's lifetime and zero otherwise because if the down barrier is not reached the option remains inactive and we cannot exercise it no matter what so here we see that in this case the stock went on a very pronounced bullish rally and the uh knock input had no chance of being activated whereas the knock in call given the fact that the upberry has been well breached returns a hefty 44.95 dollars per share for the knock out call then we can copy this formula and tweak the logic of it as the knock output would be active only if the minimum price is above the down barrier as it's a down and output and it becomes inactive when the down barrier is reached and then quite handily we can just bottom right click all of these formulas all the way down and we see that and that's a good sanity check that in every single instance only one of the options has a positive gross payoff that is a signal that we've done everything correctly and we can integrate use our thousand simulations to figure out what the fair values of these um options would be in terms of the parametric log normal simulation so the fair value of the knocking call would be just the average of the gross payoffs it delivers on average and we can enforce this logic throughout all four option types as well as we can proceed to our next simulation which would be a historical bootstrap and here we have got 1237 simulations that we can do and well you might ask why well remember we have got 1258 observations 1258 returns and a 21 period to estimate uh the path of the underlying four so using overlapping 21 day periods we can effectively bootstrap the returns of apple and look at all possible scenarios that happen historically to the price dynamics of apple and see what it would mean for a hypothetical barrier option being held or purchased at some point in time of the last five years keeping that in mind we can still stimulate our price at day zero as the center price here 149.64 dollars per share however here instead of simulating return we would take it from the observed returns in the sample using the beauty of the index function so we scale our previous price by the exponent of the index and here we'll need to refer to the full range of historical returns locking them throughout and to determine which of them do we choose well we can put this particular index of the simulation as our first identificator and here we need to lock the column as we want to drag it across but as we drag it down we want it to select a different subsample plus the number of a day that we select from this overlapping bootstrapping period and lock the row here as we don't want to change it to drag it down but do want it to change as we drag it across and then we close the appropriate number of parentheses and perform the simulation and what is quite handy is that everything else that we've got is the same so we can copy this selection of cells and paste them here resulting in a new calculation for gross payoffs of our barrier options in a particular uh bootstrapped 21d window and bottom like click all the way down for the historical simulation and finally figure out what the fair value of the barrier options given our historical bootstrap simulation is again dragging it around and we can see that the values are quite similar meaning that well both approaches could theoretically work however the difference that i observe immediately is that the values of knock-in options particularly knock-in calls is higher for the parametric simulation and the value of the knock-out calls is lower in the parametric simulation which means that historically it has been the case that a very explosive volatility that brought share prices uh away through the barrier were less frequent than you would assume and that actually reflects a nice property of volatility of stock prices which is that they are thin peaked and heavy-tailed so there are instances when the share price movement is absolutely massive but there are very few instances where it is medium volatility basically and that uh reflects the fact the theme peaked distribution of asset returns reflects the fact that historically knockout calls are more beneficial and more valuable than you would assume here whereas uh the reverse is true for uh knock out put interestingly enough and that reflects negatively skewed uh distributions of returns uh meaning that um well it's more likely that the bearish movement that you could see in stock prices would persist and if we play around with our input data for example if we uh assume our down barriers and up barriers are much more strict so like a hundred and two hundred here we would see that no value is extracted from knocking calls or ports in a parametric simulation however they would have non-zero value in the historical simulation just because the heavy-tailed nature of um real-world asset return distributions allows for that and we all obviously can play around um other inputs for example increase the strike by a bit and lead to very similar uh cases than with conventional option valuation with calls becoming less valuable and puts becoming more valuable and you could also uh change your average returns and your volatility to scale the simulations accordingly the main downside of this particular method is that it is quite uh computationally demanding you need to perform well thousands of simulations potentially to execute your task and for the parametric simulation you do assume normality which or log normality which is a questionable assumption to say the least and your results also change from execution to execution so sometimes uh and especially if you have got a few simulations and your result can be very noisy you can make a sub-optimal investment decision based on some realization of your random number generator seed to say the least for historical simulation uh these uh particular limitations uh are avoided however uh you should also keep in mind that five years is a very long time and the more precise you want your simulation to be the longer back in time do you need to venture and uh again that might uh provide you with the risk that the data you pick from like 2017 will be practically relevant for potential movements of the apple stock price in so choose wisely and acknowledge the assumptions of both of these methods and that's all there is for valuing barrier options with simulations please leave a like on this video if i'm helpful in the comments below i'm going to see any further suggestions for videos in 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