MONTECARLO SIMULATION (USING EXCEL?): Please I would like help to know what to do. I have the excel spreadsheet with 100 rows and 252 columns for the random normal variables. I need some intuition on how to do this on excel (or something different) and help in particular with the questions 3-7.

The exercise:

There are 7 questions. Please see the photos attached at the bottom for complete information.

GENERAL DATA: Underlying stock price $100, volatility 30% per year, risk-free rate 1% per year.

1 and 2) Simulate 100 paths (without approximation error), use the formula to find the fair price of the Standard Option.

3) Assume the option has a sweetener and compute the value of the call. Is it different that then result in part 2.

4) Now compute for an Asian Option

5) Heston Model: Standard option with stochastic volatility rather than Brownian. So=$100, volatility (sigma)=30%=0.30, sigma_bar=30=0.30 Kappa=5, Gamma=0.75, Rf rate=1%=0.01

6) Ro=-0.5 and Gamma=0.75. Recalculate prices of call options with strike prices 50, 100 and 150. Maturity= 1 year. Compare prices between Heston and Black-Scholes model.

7) Besides the stochastic volatility where rho=-0.5, the stock jumps (pure jump). Also assume Kappa=-0.1, Phi=0.05 and Lambda=0.5. Calculate prices of one-year call options with strikes 50, 100 and 150. Then compare with the prices without jumps but with stochastic volatility.

,

1. Consider a 1-year call option with strike price 100 on a non-dividend paying stock with the following parameters: $ 100 Underlying stock price Volatility Riskless rate 30% pa 1% (cc) pa Assume that the standard Black-Scholes assumptions apply, i.e. the underlying stock price follows a Geometric Brownian Motion. Assume the year has 252 business days. We wish to compute option prices by (Monte Carlo) simulation. In your simulations, assume each path has 252 days, i.e., use 252 normal shocks for each path. Use the simulated standard normal random variables in the excel file 'Assignment 2 data' under sheet 'Simulated normal variables I' for the normal shocks. Draw a total of 100 such paths. Simulated the 100 paths of stock prices without approximation error, i.e. use the formula S = Ser-)A+=; (1) = St where t = 1, ..., 252 is the 252 business days. 2. Standard option. Compute the value of a call option using the simulated paths. 3. Option with sweetener. Assume that the call has a "sweetener": If the stock return after six months is negative (i.e., the stock price six months from now is lower than today) the final strike price of the option is 90 instead of 100. Compute the value of the call with the sweetener. Is it different from the price of a standard call (why)? a 4. Asian option. Assume that the final payoff of the option is max(S- K,0) where S is the average stock price during the life of the option (the option has no sweetener). This is an average rate Asian option and has the feature that the payoff depends on the entire path of the stock price (the option is path-dependent). Compute the value of the Asian call option. Is it higher or lower than the value of the standard call option? Why? 5. Standard option, stochastic volatility. Instead of assuming that the stock price follows a Geometric Brownian Motion, we now assume that the stock price has stochastic volatility: = rdt + OtdBt dSt St do; = k(o? - )dt + yozdB; This is the Heston model. The correlation between the two Brownian motions Bt and B; is p. Assume that the base parameters are 2 So $100 0.30 a 0.30 K 7 Riskless rate 5 0.75 0.01 To price options in the Heston model we need to simulate an additional Brownian Motion, namely B/. Note that to simulate the stock price in the previous questions we simulated a Brownian Motion. Reuse those simulations for Bt. For each path, you have to simulate 252 normal random variables to simulate By. Use the simulated standard normal random variables in the excel file 'Assignment 2 data' under sheet 'Simulated normal variables II' for the normal volatility shocks. Based on two uncorrelated normal random variables z1 and 2, one can calculate two normal random variables 21 and 2 * with correlation p by zi 21 (2) (3) pz1 + V1 - pazi The stock price S1, S2, S3, ... on trading day 1,2,3,... is simulated (with a small approximation error) as follows (with A = 252): a) So = 100, o = 0.302 b) Simulate normal distributed variables zu and 2 and compute Si * So + So * r * A+ So* Vo** V * 21 o+k* (- )* A+* Vov Voza+zi o = (In the unfortunate case that of