In this exercise, use MS Excel to perform a Monte Carlo simulation to valuc a European call option on a non-dividend paying stock (Tesla). The option has 1 month remaining until expiration and the strike price is K - $700. Cutently, the stock price S - $725. so a call option would be "in-the- money." The risk free interest rate is currently 1.65% per year and the annual standard deviation of the stock returns is estimated at 85.73%. In your simulation, you will generate stock prices over 21 trading periods until maturity using the following process for stock price changes from day t to dayt+A1 +: Sit9 = S explo At + VAZ 2 Where, S = stock price on dayt o annual standard deviation of return T = time to maturity in years At-time interval in years rannual risk free rate z = standard normal random variable with mean= 0 and variance = 1.0 This version of the formula is the same as the one introduced in class. Parameters are presented in annual terms (eg, r is the annual interest rate) but transformed into daily equivalents by multiplying with our At of cine trading day. Also note that exp[x] implies the natural log base, e, raised to the [x] power, are . 1. Make a histogram of the distribution of stock prices at maturity of the option (last column). using the Histogram tool from the Analysis Toolpak ard-in. You'll have to create a cell range that contains the boundaries of the bins to be used. 2. Once you have determined the values of a European call and put with strike equal to 700, compare your Monte Carlo values to the standard Black-Scholes values (using Derivagem). Explain any discrepancies. Make a histogram of the distribution of aption payoffs for the European paut caption. 3. Use Monte Carlo simulation to compute prices for the remaining twelve exotic options (listed in Excel file): Asian put the payoff depends on the difference between the strike price and the average stock price during the life of the option instead of merely the price ut expiration, as with European options). Assct or nothing call: the payoff is either the value of the stock at expiration, or 0 if the option expires out of the money Cash or nothing call: the payoff is either K (700) at expiration, or if the option expires out of the money Lookback call: i. Floating-strike: choose any stock price tbat occurred during the life of the option as your strike price. (Which price will the call option holder choose to get the maximum payott?) i. Fixed-strike: chciase any stock price that occurred during the life of the option to detennine the payoff. (What would he optimal?) Knock-out call: option cases to exist (payoff=0) if the stock price falls below $650 (or rises above $800, if the barrier is above the current stock price) at any point during the life of the option. Otherwise, the payoff is equal to that of a regular call option. Knock-in call (barrier=5650): option only exists if the stock price falls below 650 at any point during the life of the option, otherwise the payoff = 0. If the option exists, the payoff is equal to that of a regular call option. a Answer these questions somewhere in the Excel file and submit your file so that I can check your work You are free to check your solutions against prices computed using DerivaGen, but this is not required . risk-Leutral stock price distribution at expiration which can be used to generate the expected payof Using this simulation of stock prices over time allows you to generate the log-normally distributed from the option. In turn, the expected payoff can be discounted to the present at the risk free interest rate to give the current value of the option. Your simulation of stock price changes will be driven by random realizations from the standard total random number generator found in the Data tabs under "data analysis." Select random mumber generator from the menu. Then in the next menu, select the ponnal distribution. Make sure the parameters are set al mean and std dev -1. Then select 'output range - ZSA$3 to return the output on a separate sheet named "Zs". Leave all other inputs blank. Ilit ok. Your spreadsheet will generate a 1000 x 21 matrix of random draws from the standard normal distribution. This will enable you to conduct 1000 trials.cach generating a random price path over 21 days Using the price generating function above you should generate a 10XX)x21 cell block of prices. Ench row represents a simulated path of the stock price over 21 days. Make a histogram of the distribution of stock prices at maturity of the option (day 21), using the Histogram tool from the Analysis Topluk add-in. You'll have to create a cell range that contains the boundaries of the trans to be used From the last column (day 21 price), you can evaluate the exercise value of the European option and the call premium. Do the same for a European put Finally, use the stock price paths that you simulated to price various exotic options. You can find more details on these options in the slide deck. In this exercise, use MS Excel to perform a Monte Carlo simulation to valuc a European call option on a non-dividend paying stock (Tesla). The option has 1 month remaining until expiration and the strike price is K - $700. Cutently, the stock price S - $725. so a call option would be "in-the- money." The risk free interest rate is currently 1.65% per year and the annual standard deviation of the stock returns is estimated at 85.73%. In your simulation, you will generate stock prices over 21 trading periods until maturity using the following process for stock price changes from day t to dayt+A1 +: Sit9 = S explo At + VAZ 2 Where, S = stock price on dayt o annual standard deviation of return T = time to maturity in years At-time interval in years rannual risk free rate z = standard normal random variable with mean= 0 and variance = 1.0 This version of the formula is the same as the one introduced in class. Parameters are presented in annual terms (eg, r is the annual interest rate) but transformed into daily equivalents by multiplying with our At of cine trading day. Also note that exp[x] implies the natural log base, e, raised to the [x] power, are . 1. Make a histogram of the distribution of stock prices at maturity of the option (last column). using the Histogram tool from the Analysis Toolpak ard-in. You'll have to create a cell range that contains the boundaries of the bins to be used. 2. Once you have determined the values of a European call and put with strike equal to 700, compare your Monte Carlo values to the standard Black-Scholes values (using Derivagem). Explain any discrepancies. Make a histogram of the distribution of aption payoffs for the European paut caption. 3. Use Monte Carlo simulation to compute prices for the remaining twelve exotic options (listed in Excel file): Asian put the payoff depends on the difference between the strike price and the average stock price during the life of the option instead of merely the price ut expiration, as with European options). Assct or nothing call: the payoff is either the value of the stock at expiration, or 0 if the option expires out of the money Cash or nothing call: the payoff is either K (700) at expiration, or if the option expires out of the money Lookback call: i. Floating-strike: choose any stock price tbat occurred during the life of the option as your strike price. (Which price will the call option holder choose to get the maximum payott?) i. Fixed-strike: chciase any stock price that occurred during the life of the option to detennine the payoff. (What would he optimal?) Knock-out call: option cases to exist (payoff=0) if the stock price falls below $650 (or rises above $800, if the barrier is above the current stock price) at any point during the life of the option. Otherwise, the payoff is equal to that of a regular call option. Knock-in call (barrier=5650): option only exists if the stock price falls below 650 at any point during the life of the option, otherwise the payoff = 0. If the option exists, the payoff is equal to that of a regular call option. a Answer these questions somewhere in the Excel file and submit your file so that I can check your work You are free to check your solutions against prices computed using DerivaGen, but this is not required . risk-Leutral stock price distribution at expiration which can be used to generate the expected payof Using this simulation of stock prices over time allows you to generate the log-normally distributed from the option. In turn, the expected payoff can be discounted to the present at the risk free interest rate to give the current value of the option. Your simulation of stock price changes will be driven by random realizations from the standard total random number generator found in the Data tabs under "data analysis." Select random mumber generator from the menu. Then in the next menu, select the ponnal distribution. Make sure the parameters are set al mean and std dev -1. Then select 'output range - ZSA$3 to return the output on a separate sheet named "Zs". Leave all other inputs blank. Ilit ok. Your spreadsheet will generate a 1000 x 21 matrix of random draws from the standard normal distribution. This will enable you to conduct 1000 trials.cach generating a random price path over 21 days Using the price generating function above you should generate a 10XX)x21 cell block of prices. Ench row represents a simulated path of the stock price over 21 days. Make a histogram of the distribution of stock prices at maturity of the option (day 21), using the Histogram tool from the Analysis Topluk add-in. You'll have to create a cell range that contains the boundaries of the trans to be used From the last column (day 21 price), you can evaluate the exercise value of the European option and the call premium. Do the same for a European put Finally, use the stock price paths that you simulated to price various exotic options. You can find more details on these options in the slide deck