TASK: Build an application in Excel/VBA to solve option prices (European options) with Monte-Carlo. Also include a stochastic volatility in the model. Plot the histogram of the outcome and calculate the probability to reach the strike.
Monte Carlo simulation treats randomness by selecting variable values from a certain stochastic model. This method has become increasingly popular due to the computational power of our current technology which allows us to implement complex mathematical experiments intended to imitate real-life systems in areas such as finance, physics, computer science, etc.
Our Monte Carlo Simulation uses geometric Brownian motion as the building block for modeling stock prices considering stochastic volatility. Thus, the periodic return which is expressed in continuous compounding is a function of two components:
1. Constant drift
2. Random shock, i.e. volatility multiplied by a randomized critical z value
To this model we added the Euler Scheme in the Heston model type for stochastic volatility and as a result the drift which was supposed to be constant becomes a random variable of time. After performing simulations, with the same process, we evaluate the payoff of the European call option at maturity for each simulation. The next step is to obtain an average payoff and discount it into the present to obtain today’s fair option price. Once the calculations are over we can then proceed to compare our price against the price suggested by the with the Black and Scholes formula. Finally, we plot the corresponding histogram for our simulated stock prices at time n to see the distribution. Based on that, we calculate the probabilities for the price to be in certain intervals to compare by simple sight whether it´s close to the strike price or not.
The Monte Carlo simulation is a well-known method to estimate Value at Risk (VaR) in regard to the asset class (i.e. basic equity or stocks). This basic simulation is just an application of the geometric Brownian motion which is a particular type of Wiener process. This stochastic process, in turn, models the random behavior of a stock price in continuous time. This geometric Brownian motion satisfies the following stochastic differential equation for continuous time and random variable:
= Stock price at time t
= Wiener process
However, our work in Excel simulates the future stock price measured in trading days. Therefore we adjust this equation for the discrete time case:
Where the new terms are:
= Change in the stock price per unit of time
= Time interval. In our case one day
= Standard normal random number
As one can note, in the right side of the equation, we have factored the stock price from the previous day letting the terms in parenthesis be the drift and shock successively.
It is important to remark that this geometric Brownian motion is a Markov process since the price of tomorrow only depends on today´s price and not the past. This property gives us some insight about market efficiency (even in its weak form), which tells that all the past information is already impounded in today´s price and the future price will depend solely on current and future events.
John Hull, in his book Options, futures, and other derivative securities, expresses our last formula as a lognormal continuously compounded periodic return of the stock (), which is verified by the Ito´s Lemma. Consequently, this lognormal random variable will be approximately normally distributed with mean and variance .That´s why we say that the price ratios are lognormal making our geometric Brownian motion a lognormal diffusion process. The equivalent (and simpler) formula that we implement in our model is as follows:
= The stochastic component, where will be our generated random variable for the stock price which will be scaled by its correspondent stochastic volatility at time t.
This formula represents the core of our simulation. In this case, we set the daily expected return to be positive because we expect the stock price to drift upward, entailing a positive expected return over time. In a basic model is completely deterministic but it becomes a random variable as well since it depends on the stochastic volatility. Furthermore, we set the initial values for the stock price and volatility before starting the simulation. To calculate the stochastic volatility, we decided to implement the Heston model. The main reason for this choice is that there is an algorithm for time-discretization and Monte Carlo simulation of a Heston-type stochastic volatility model.
This variant is called the Euler scheme, which allows us to calculate the stochastic volatility for the period in which the random stock price is generated. The formula is as follows:
= preset drifts
= Standard normal random number at time t
The algorithm is based on a careful analysis of the properties of affine stochastic volatility diffusions and is straightforward and quick to implement and execute. Tests on realistic model parameterizations reveal that the computational efficiency and robustness of the simulation scheme compares very favorably to existing methods.
Coming back to (3), the stochastic component is what we´ve been calling the random shock which will be a function of both random stock price and random volatility. That is what allows us to model the stochastic process getting different paths each time.
Implementation in Excel
To start with, we need to show how each term of (3) is set as an input in the Excel sheet.
Expected Daily Drift (mean), : First, we set the value of the annual drift or expected return of the stock in one cell, then, in another cell we set the value of the daily drift which is simply calculated by dividing the annual drift by 252, considering that a year has 252 trading days. Finally, in another cell we convert this daily drift into an “expected” daily drift by subtracting one half of the variance at time t. In simple math: daily drift - , this is some sort of geometric average and since we are using stochastic volatility, can´t be a constant but now becomes a function of t as well, beginning with as the initial value. The reason for this calculation is that the stochastic volatility is eroding returns. This is due to the key assumption of the random walk idea: that returns are expressed in percentages. For example, let´s imagine that is $100 and then goes up 10% to $110. Then goes back 10%, which nets to $99 ($110 x 90% = $99). Then it goes up 10% again, to net to $108.90 ($99 x 110% = $108.9). Finally, it goes down again 10% to net to $98.01. It may be counter-intuitive, but the principal is slowly eroding even though the average gain is 0%. That difference is about half of the variance of .
Expected Daily volatility, : First, we set the value of the annual volatility or expected risk of the stock in one cell, then; in another cell we set the value of the initial daily volatility by dividing the annual volatility by the square root of 252 trading days. The square root is because volatility scales with the square root of time while variance scales with time directly. Next, we start running (4) to get the successive volatilities but before we set the deterministic drifts in (4) and. To generate the standard normal random number at time t i.e. we use an especial Excel function called: NORMSINV (RAND ()). Just RAND provides the probability between 0 and 1 and NORMSINV translates that into the inverse standard normal cumulative distribution, that will give generally a value between -3 and 3, this number appears in the cell named “N (0, 1) Vol”, the idea of this is that we get to randomize the next´s period volatility which will result after multiplying this number by and later adding the remaining terms in (4) that are known. This process runs n times according to the number of nodes (time steps in trading days) we choose to have. Observe that all this is for just one simulation (iteration or single path).
Generated random variable for the stock price, : The process to generate is using NORMSINV (RAND ()) once again. We should usually get a random value between -3 and 3 as well and this number appears in the cell named “N (0, 1) Price”. Like this process runs n times according to the number of nodes we choose to have. Observe that all this is for just one simulation.
Once we get these three variables for a single node t, we can apply (3) to get the lognormal return (). Finally to get the simulated stock price for the next period, we simply multiply the current price at time by the exponential of the lognormal return. i.e.
The routine generates m simulations which can be thought of as paths formed by n amount of nodes. Each of these nodes will have stochastic volatility inbuilt on to them.
Since the task is to obtain the payoff of a European call option today (t=0) our next step is to evaluate every stock price that came out as a final outcome of each simulation. The evaluation is based on the formula for the European call option payoff:
= Last node or time step.
= Number of simulations.
= The value of the call option at the last node which resulted from each of the simulations or paths.
= The final stock price i.e. at the last node for each of the simulations.
= The strike price which is a given constant.
As a result, we will have obtained different payoffs. Consequently, we calculate the arithmetic mean of all the payoffs. The formula is as follows:
Since that result is at time n in the future we discount it assuming a continuously compounded interest rate. The formula is as follows:
= The European call option value at time 0 i.e. today.
= Risk-free interest rate
= Total number of nodes or time steps in each simulation.
represents our final result. The main purpose of the whole simulation was to come up with that option value at time 0. Therefore, this should be the “fair” price to pay for such security. To have a basis for comparison, we use the Black-Scholes formula to calculate the option price for the same stock with the same strike price and time n. Therefore we can see whether our result converges with Black-Scholes or not.
We have also implemented the Black-Scholes model to calculate the fair price of the stock to compare it with Monte Carlo simulation.
The formula is for the Black Scholes Model is given by:
S0 = The initial stock price
K = The strike price
σ = Volatility
The Black-Scholes formula is an equation of the market for an equity, in which the equity’s price is determined via a stochastic or random process. A Monte Carlo model is a method of estimating a value by the random generation of numbers. When used in conjunction random numbers are generated and fed into the Black-Scholes model. This model is executed repeatedly and the aggregate results are used to price the option.
The construction of both Probability Distribution and Stock Price Distribution histograms is very straight forward in Excel.
Since the prices are calculated with the code in the first sheet; all the histogram sheet does is receive the data and filter it allowing us to count the frequency by which the stock prices show up. Knowing the frequency and the amount of stock prices calculated, the next step would be to determine the probabilities and plot them using the intervals selected.
The page refreshes itself each time the simulation is executed and the histograms' speak for themselves showing the distribution of the simulated stock prices at time n. The page has the limitation of being customized for accepting 125 prices but it would only take the user a couple of adjustments to take under account as many inputs as desired.
In this project we have taken to the next level the basic Monte Carlo simulation to simulate stock prices. By considering stochastic volatility we have seen that the prices can really jump higher or lower and consequently give us a tremendous difference in payoffs for the call option.
Also in this experiment we have noticed that there is a large probability for such contract to go default. Because of that, the histogram doesn´t show a definite distribution.
When comparing the call option´s fair price calculated using the Black-Scholes formula with our method can deviate quite a bit and sometimes get very close to Black-Scholes which may mean just a shot of luck.
We have addressed this problem for a long time but concluded that it may be because of two reasons:
First, since the volatility is stochastic, it makes the lognormal return of the stock completely random even randomizing the drift which is constant in the simple model. Therefore the more nodes a single path takes, the closer to zero the stock price tends to be. This is because at some point the lognormal return is exceedingly negative making the stock price lose most of its value and for the next period it´s difficult that the simulation takes it back to a normal level since it depends on the price today. We thought that this was caused by negative random volatilities, but after making a restriction to that, we still came up with the same problem.
Second, in the Euler scheme, to calculate the stochastic volatility we introduced two new constants; Kappa and Theta; which we assigned values, but even trying with lower or higher values the volatility ends up at some point extremely high causing the phenomenon explained above.
In conclusion, the combination of three random values obtained for (3) ;which is our main equation; returns a very volatile and sometime even incoherent lognormal return at some point in the stock´s price path.
Hull, John C. Options, futures, and other derivative securities, 6th ed. Chapter 12: Wiener Processes and Ito's Lemma