MTH222 Financial Modelling with Excel VBA
Final Project Coursework
2023-24
Instructions
1. There are three parts: Equities (30 marks), Options on Equities (25 marks) and Options on Bonds (45 marks).
2. Work on and submit one Excel template file ’MTH222 CW WorkBook.xlsm’.
3. Fill in name and ID in the cover sheet.
4. Develop macros and functions in Module1, 2 and 3 for Part1, 2 and 3, respectively.
5. The output should be in the blue areas in the answer worksheets.
6. You are free to develop macros and/or functions, but not allowed to do calculations on the worksheets outside blue areas.
7. Please do not change the layout of worksheets.
11 Equities (30 marks)
In this part, you will assess the performance of an active stock fund in the Chinese market by studying its relationship with three indices: the market, size and value factors in China. In Sheets(”Part1 - data”) is the data with data descriptions, and each student is assigned one fund.
Answer the following questions in Sheets(”Part1 - answer”).
1. Report the summary statistics of the data. Use the sample period of fund returns for all the data. (5 marks)
2. Conduct the full sample investment analysis by estimating parameters in the fol lowing model. (5 marks)
rxi,t = α + βi,m · rxm,t + βi,s · SMBt + βi,v · HMLt + i,t,
(1)
where
• rxi
is the excess return on fund i
• rxm is the excess return on the market
• SMB is the size factor
• HML is the value factor.
3. Conduct the investment analysis in rolling windows. The size of the window is 60 months. Report the estimated coefficients with their t-values. (5 marks)
4. Assess the performance of the fund based on the results from Questions 1–3. The fund information can be found in two separate files ’Fund EVL MainInfo.xlsx’ and
’Fund EVL MainInfo[DES][xlsx].txt’. (15 marks)
22 Options on Equities (25 marks)
In this part, you will calculate the value of a stock option using Monte Carlo simulations. Suppose today is 10 March 2023, and the price of underlying stocks and options is observed at the end of the trading day. In Sheets(”Part2 - data”) is the data with data descriptions, and each student is assigned one stock option. The continuous compounding interest rate is 5% per annum. Assume the dividend yield is zero.
Answer the following questions in Sheets(”Part2 - answer”).
1. Calculate the implied volatility of the option using the Newton-Raphson method. (10 marks)
• Use this implied volatility for the following Questions 2 and 3.
• If you have not done Question 1, please use σ = 20%.
2. Calculate the option value from simulations with different numbers of trials (nsim) using Monte Carlo simulation with antithetic variables (MC) and with quasi-random sampling (QMC). (5 marks)
3. Comment on the results from Question 2 and compare the two simulation methods. (10 marks)
33 Options on Bonds (45 marks)
In this part, you will price a bond option by the Vasicek short rate model and by the method of matching the term structure. Suppose you are in June 2021. The option to be priced is a European call option on a 10-year zero coupon bond with a strike price of 0.6 × Par, where Par = 1, and the option time to maturity is 4 years.
Answer the following questions in Sheets(”Part3 - answer”).
1. Estimate the discrete Vasicek model in the real world using the historical data of monthly 3-month Treasury bill rate in Sheets(”Part3 - data”). (5 marks)
rt+1 − rt = α + β · rt + t+1.
(2)
2. Using the parameter estimates from Question 1 and assuming the price of risk is
−1.2, find the parameter values in the risk-neutral world. (5 marks)
dr = a · (b − r) · dt + σr · dz.
(3)
• Use these parameter values for the following Questions 3–5.
• If you have not done Question 2, please use a = 0.18, b = 0.09 and σr = 2%.
3. Calculate the yield curve in June 2021 implied by the Vasicek model, and compare it with the yield curve in the data. (10 marks)
4. Calculate the option value by using the Vasicek model. (5 marks)
5. Calculate the option value by matching the term structure. Construct a binomial tree with lognormally distributed interest rates where the time step is one year.
Assume the volatility is constant over time and is equal to σr. (5 marks)
6. Compare bond option pricing using short rate models and using term structure matching. (15 marks)