Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
MMF 1914H: Final Assignment
This is the only assignment for this course and will cover all three sections discussed in the lecture:
Financial Data, Excel VBA and MATLAB.
Part 1 of this assignment requires managing, analyzing, and displaying data, as graphs and tables, in
Excel and MATLAB.
Part 2 requires creating formulas and VBA functions in Excel that solve the Black Scholes equations.
Part 3 requires doing some mathematical analysis and writing some MATLAB code. To complete the
assignment, you will need to use some MATLAB functions that were not discussed in class. Use your
research skills to learn how they work.
Submit your answers as Excel workbooks, Word documents and MATLAB code as appropriate. Make
sure that the names of the various files are descriptive of the questions that they answer, e.g.
Part1Question2.
Wherever the question refers to Excel, submit your answers as an Excel workbook that includes a
separate worksheet for each answer, with the individual worksheets named appropriately to identify the
associated question.
The short answer part of each question referring to Excel should be entered in a Text Box placed near
the top of each associated worksheet and all Charts created should also be placed near the top of the
associated worksheet, so that all parts of the answer are at least partially visible without scrolling down
or searching the worksheet.
Wherever a question refers to MATLAB, copy and paste any charts and tables created in MATLAB into a
Word document, along with all related short answers.
You can enter the solution to the question 3.1 in a Word document using the Equation Editor
(Insert->Equation).
Finally, collect all the answer files into a single zip file. Give that zip file your name, last name then first,
followed by “MMF1914H”, so that your work can be easily identified. For example,
BerdeklisPeterMMF1914H.zip would be the name of my submission.
The time stamp of the email will determine whether the assignment has been submitted on time.
Good luck!
Page 2 of 5
Part 1: Financial Data (10/25 points, 2 points each)
1. Log on to Yahoo Finance (finance.yahoo.com)
Download all available daily historical New York Stock Exchange (NYSE) data for IBM.
Load the data into Excel and create a time series chart that includes the Close price and the
Adjusted Close price.
How are the Close and Adjusted Close prices related? For a complete answer you may have to
compare the downloaded data to the data as Yahoo presents it on their web page.
IBM stock was split in 1999. What was the split ratio?
IBM paid regular dividends in that year. What per share dividends were paid during 1999?
What was the average per share dividend paid in the periods before and after the split?
2. Bank of Montreal stock (BMO) trades both in Canada and the US, in Canadian and US dollars,
respectively. From Yahoo, download the Canadian Composite quote data (BMO.TO) and the US
Composite quote data (BMO) for the period Jan 1, 2019 through Dec 31, 2020 and create
O/H/L/C/Volume charts for both time series in Excel.
Why are the data different lengths? What fraction of the total North American share volume of
BMO shares traded in 2020 was traded in the US and Canada? What was the average daily
trading volume (ADV) of BMO in each market?
3. Download closing price data for BMO.TO (Bank of Montreal) and GS (Goldman Sachs) from
Yahoo, for the period Jan 1, 2007 through Jan 1, 2011.
Load the data into MATLAB and plot both time series on a single, 2x1 figure using the subplot
command. Both plots should be titled, and the x-axis ticks should be formatted as dates.
Save the figure and paste it in a Word document.
4. Calculate the daily log returns for each series.
For each security, create a 2x2 figure in MATLAB with the subplot command and plot the
histogram of the log returns for each calendar year 2007, 2008, 2009 and 2010. Each histogram
should use 30 bins and be titled with the year and the stock ticker (BMO or GS). Save the figures
and paste them into a Word document.
Page 3 of 5
5. Using the log return data for each security, create a table in MATLAB of the means and standard
deviations for each year 2007, 2008, 2009, and 2010. How do the returns and standard
deviations of the 2008 and 2009 data compare to the 2007 and 2010 data? Why are they
different?
Part 2: Excel VBA (5/25 points)
1. Download the “Black Scholes 2021” spreadsheet from the class website. The spreadsheet has
implemented the calculations for the Option Value as a formula in a cell and as a function in
VBA. Complete the calculations for the Delta, Gamma, Theta and Vega of the option, using both
spreadsheet formulas and by developing VBA functions. Make sure that all the solutions are
correct for both calls and puts, depending on the “Call or Put” user parameter, as they are for
the Option Value calculations. Enter the functions in the cells indicated, as well as the
difference between the spreadsheet and VBA calculations. The equations for each calculation
are copied as figures in the spreadsheet for your reference.
Part 3: Numerical Option Pricing (10/25 points, 2-3-1-4)
1. In a Cox-Ross-Rubenstein binomial option model, the probability of a price increase is given by
= {} =
(?)??
?
. Show that for 0 < < 1 to be satisfied, then ? <
2
(?)2
. Show
that this equation satisfies both limits, i.e. for both 0 < and < 1.
2. A Discrete Barrier option loses its value if the security price at expiry exceeds a limit at one or
more specified times. For example, a European Discrete Barrier call option may have a strike at
$20 with a barrier price at $25. If the security price is $23 at expiry then the call value is $3, but
if the security price is $25.50 the call is worthless. See the attached graphs of the intrinsic value
at expiry for greater clarity.
Adapt the binomial model that we developed in class to price a European Discrete Barrier
option. The CRROptionPricer code is available on the class website.
Adjust the model to handle both calls and puts. The barrier should only be applied at expiry.
For a call (put), the barrier price must be greater (less) than the strike price. The model should
verify that this condition is met and return an error if it is not.
3. A Discrete Barrier option is different from a Continuous Barrier or Knock-Out option. A Knock-
Out option loses its value if the stock price exceeds the knock-out price at any time during the
Page 4 of 5
life of the option, not just at expiry. This makes the option path-dependent. What numeric
method is most appropriate for path-dependent options?
4. Using the CRR binomial model developed in class, calculate the option price for an American call
option with the following parameters:
Security Price: 100 Strike Price: 105
Risk Free Rate: 2% Dividend Yield: 1%
Volatility: 20% Years to Expiry: 1.0
Repeat the calculation, increasing the number of lattice steps each time from 20, 40, 60, etc. to
1000.
Plot the calculated price against the number of steps. How many steps are required before the
calculated price converges to within $0.01?
Use the tic/toc Matlab functions to measure the time required for each calculation and plot that
data also.
Perform this exercise using both the CRROptionPricer (“vector version”) and the
CRROptionPricerForLoops (“for loops version”) functions.
Plot the elapsed times for the vector version and the for loops version on a single graph. How
do they compare?
(Note, the results of this test will vary depending on your personal computer. MATLAB is much
better at for loops than it used to be. Observe and report your findings.)
These exercises should all be done by creating a series of Matlab scripts. Include all the script
code files in your submission.