Finance 30233, Fall 2019 Name

Finance 30233, Fall 2019

Name________________________

M. J. Neeley School of Business, TCU

S. Mann

Assignment 04. Due Date: Thursday, December 5, 2019.

Pick a stock paying at least a 2.00% annual dividend.

1. Use the Mann spreadsheet "Fin30233_F2019_Monte Carlo Call.xlsm" to estimate the value of a January 2021 at-the-money call for that stock: i) Run the Monte Carlo estimation "NO dividends" 10 times and record the option value each time. ii) The spreadsheet should calculate the mean and standard deviation of the estimated option values (this is using the formula that ignores dividends). iii) Run the "WITH-dividend" Monte Carlo estimation 10 times and record the option value...the spreadsheet should record the mean and standard deviation of your ten estimates once you have "input" the ten estimates. iv) Present your results in a table showing: a. Your inputs; b. The results for the ten trials without dividends; c. The results for the ten trials with dividends. d. The results should be the option values for each trial, the average of the 10 trials, and the standard deviation of the 10 trials. e. The Black-Scholes Merton values ? with and without dividends - for the call options. To calculate the theoretical (Black-Scholes-Merton) values, refer to the instructions that follow.

Note: You'll need to decide what volatility to use. You will be "close" to market prices if you use the code from assignment 1 to estimate the implied volatility for "at-the-money" calls. The "error" will be that you are using an implied volatility formula that does not adjust for dividends. You could also use an implied volatility from Yahoo finance. If you don't care about matching market prices, just use 30% (but be aware that your prices won't match market prices).

Adding option pricing to Excel, using VBA (The vba code is on Mann's course website):

1. 1.

2.

How to use the VBA editor in Excel:

If the Developer tab is not available, do the following to display it:

Click the File tab.

2.

Click Options, and then click Customize Ribbon.

3.

In the Customize Ribbon category, in the Main Tabs list, select the Developer check box,

and then click OK.

To set the security level temporarily to enable all macros, do the following:

1.

On the Developer tab, in the Code group, click Macro Security.

2.

Under Macro Settings, click Enable all macros (not recommended, potentially dangerous

code can run), and then click OK.

Note To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros.

For more information about how to change the settings, see Change macro security settings in Excel.

1.

On the Developer tab, in the Code group, click Visual Basic.

2.

If needed, in the Visual Basic Editor, on the Insert menu, click Module.

Note Modules are automatically created for all sheets in the workbook.

3.

In the code window of the module, type or copy the macro code that you want to use.

4.

To run the macro from the module window, press F5.

5.

In the Visual Basic Editor, on the File menu, click Close and Return to Microsoft Excel when

you finish writing the macro.

Additional tips that may be relevant:

i) To get to the VBA editor, use Excel help and search for "vba editor" and choose "Create a macro by using VBA": - click on the "Office Button" and then click on the "Excel Options" tab; - click on the "popular" tab; - check the box next to "Show Developer Tab" in the ribbon; (you may need to save the file as a "macro-enabled workbook" first); - On the "Developer" Toolbar, click the "Visual Basic" button, this should give you the Visual Basic Editor.

ii) Once at the Visual basic Editor, click "Insert/Module". - This should give you a blank screen saying "Code".

iii) Type in the code (or copy and paste!). iv) Some hints:

- The comments preceded by apostrophes are ignored by VBA, those are "comments";

- The VBA editor requires all of a line of code to be on the same line, it is not smart enough to read down to the next line, and it will make the code red if it does not like it.

- The code starts with "Function" followed by the name of the new function and the arguments to the new function; the code ends with "End Function".

v) When done with a function, click on "Debug/Compile VBA Project" vi) Now click on "File/Save (workbook name):

- You will need to save it as a "macro-enabled workbook". vii) You will find your function by using the "insert function" key (fx) and looking for

"user-defined" functions. - You may need to close the workbook and re-open it (after saving it!) in order for

the function to show up.

The vba code (available to copy from Mann's course website):

Function scm_d1(S, X, t, r, sigma) scm_d1 = (Log(S / X) + r * t) / (sigma * Sqr(t)) + 0.5 * sigma * Sqr(t) End Function Function scm_BS_call(S, X, t, r, sigma) scm_BS_call = S * Application.NormSDist(scm_d1(S, X, t, r, sigma)) - X * Exp(-r * t) * Application.NormSDist(scm_d1(S, X, t, r, sigma) - sigma * Sqr(t)) End Function Function scm_BS_put(S, X, t, r, sigma) scm_BS_put = scm_BS_call(S, X, t, r, sigma) + X * Exp(-r * t) - S End Function Function scm_BS_call_ISD(S, X, t, r, C) high = 1 low = 0 Do While (high - low) > 0.0001 If scm_BS_call(S, X, t, r, (high + low) / 2) > C Then high = (high + low) / 2 Else: low = (high + low) / 2 End If Loop scm_BS_call_ISD = (high + low) / 2 End Function Function scm_BS_put_ISD(S, X, t, r, P) high = 1 low = 0 Do While (high - low) > 0.0001 If scm_BS_put(S, X, t, r, (high + low) / 2) > P Then high = (high + low) / 2 Else: low = (high + low) / 2 End If Loop scm_BS_put_ISD = (high + low) / 2 End Function

Function scm_d1_div(S, X, t, r, div, sigma) scm_d1_div = (Log(S / X) + (r - div) * t) / (sigma * Sqr(t)) + 0.5 * sigma * Sqr(t)

End Function Function scm_BS_call_div(S, X, t, r, div, sigma)

scm_BS_call_div = S * Exp(-div * t) * Application.NormSDist(scm_d1_div(S, X, t, r, div, sigma)) - X * Exp(-r * t) * Application.NormSDist(scm_d1_div(S, X, t, r, div, sigma) - sigma * Sqr(t)) End Function Function scm_BS_put_div(S, X, t, r, div, sigma)

scm_BS_put_div = scm_BS_call_div(S, X, t, r, div, sigma) + X * Exp(-r * t) - S * Exp(-div * t) End Function Function scm_BS_call_div_ISD(S, X, t, r, div, C)

high = 1 low = 0 Do While (high - low) > 0.0001 If scm_BS_call_div(S, X, t, r, div, (high + low) / 2) > C Then

high = (high + low) / 2 Else: low = (high + low) / 2 End If Loop scm_BS_call_div_ISD = (high + low) / 2 End Function Function scm_BS_put_div_ISD(S, X, t, r, div, P) high = 1 low = 0 Do While (high - low) > 0.0001 If scm_BS_put_div(S, X, t, r, div, (high + low) / 2) > P Then

high = (high + low) / 2 Else: low = (high + low) / 2 End If Loop scm_BS_put_div_ISD_div = (high + low) / 2 End Function

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download