Calculating the Binomial & Black-Scholes-Merton Price with the Excel Spreadsheet BlackScholesMerton Binomial10e.xlsm
The Excel spreadsheet BlackScholesMertonBinomial10e. xlsm can be found in the ‘Course Materials and Activities’ Section on your Blackboard. It calculates both the binomial and Black–Scholes–Merton option pricing models. The Black–Scholes–Merton model provides European call and put prices, as well as the delta, gamma, theta, vega, and rho, known as the option “Greeks.”
The following example will show you how to calculate an option price using the BlackSholesMerton spreadsheet.
Assume that you want to find the Black–Scholes–Merton price of the option illustrated in the book’s chapter 5, the DCRB June 125 call. The DCRB stock is at $125.94, the exercise price is $125, the continuously compounded risk-free rate is 4.46 percent, the standard deviation is 0.83, and the time to expiration is 0.0959 based on 35 days to expiration. There are no dividends on the stock.
The Excel sheet below contains a section labeled “Inputs:” Each cell that will accept inputs has a double-line border, and the values are in blue when viewing the spreadsheet. Output cells have a single-line border. Enter the values in the appropriate cells. For greater accuracy, enter the time to expiration as a formula: “ 35/365.” Leave the dividend yield and the discrete dividends blank or insert zeroes. For the risk-free rate, select “continuous” from the pull-down list. The risk-free rate can be entered with or without decimals, that is, as “0.0446” or “4.46.” Volatility and the dividend yield can also be entered in this manner. The spreadsheet automatically calculates the new values anytime an input is changed. Observe your answers in the section labeled “Black-Scholes-Merton Model.”
Given the following information:
The underlying stock price is $47, and the exercise price is $43. The risk-free rate is 2.78 percent continuously compounded, and the standard deviation is 0.75. The time to expiration is 20 days based on 365 days a year. And the stock is not expected to pay dividends.
1- Calculate the price of a Put Option using the Binomial (1 step and 200 steps) model and the BlackSholes model.
2- Discuss the reason behind the differences between the prices calculated and which is the closest to the real value of the option
3- If a Put Option with the same characteristics is currently priced at $2 and you are considering such an investment decision, will you be inclined to buy or sell (long or short) the Put Option, and why?
Kindly provide your results with the cover sheet in a Word file format.
All the best