Financial Modelling in Excel/VBA

 

 

PAPER CODE EXAMINER DEPARTMENT TEL
MTH222 Yi Hong  

FSA/MS

1729

 

2nd SEMESTER 2019/20

 

Individual Assignment (FINAL)

 Financial Modelling with Excel VBA

INSTRUCTIONS TO CANDIDATES

 The assignment comprises 60% weight of the final module mark.

  1. Write a report about the financial modelling topics on equities, options and fixed income assets within Excel/VBA Environment (details and guidelines attached).
  2. The report must be written in English, associated with the supportive excel files.
  3. University policy on late submission will be followed.

Introduction

 This part of the course assessment is worth 60% of the final mark in the module, and consists of a take‐home individual assignment/project, covering the three topics on equities, options and fixed-income products mainly discussed in this module.

This assignment/project aims to practice your skills in financial modelling in the various applications with their implementations in the Excel/VBA Environment.  The relevant information about the securities prices can be founded through Yahoo (http://finance.yahoo.com) or WIND terminal (accessible at Library).

 

PART I: Financial Modelling in Excel/VBA

Suppose that the information about the following financial securities is available in the market:

  • A stock‐market index, S, e.g., S&P500
  • A set of European/American options written on this stock index
  • A set of individual component stock shares (Si=1,…,n) for n listed companies, associated with the stock index.
  • A zero‐coupon government bond with the risk‐free yield, rf, e.g., the 1‐year US government bond.

In this part, you were provided with the following three scenarios, each of which takes the weight of 30%. You need analyze each of them by completing financial modelling tasks for a specific scenario.

 

SCENARIO 1 (30%):

 

Suppose that you are working on a fund of $10 million which invests in a portfolio of the S&P500 component stocks with the total number of n, e.g., Apple, HP, BP and etc. This portfolio has been constructed since January 20, 2015. On February 20, 2020, you were worried about the rapid spread of corona‐virus‐2019 disease globally, and decided to conduct a comprehensive analysis on your portfolio in case that this crisis resulted in dramatic market turmoil in the following ONE month. Based on your analysis, you may work out the optimal strategy and the potential loss in extreme market situations:

  • You are asked to collect all required financial information about component stocks and the market index from January 20, 2015 to February 20, 2020, on a monthly basis.
  • All the securities in your portfolio are selected from the component stocks of the S&P500 index, which was constructed on January 20, 2015.
  • The securities are selected from different industries, e.g., bank industry, media industry, and manufacturing industry and etc.
  • The number of stocks in your portfolio must be between 5 and 10, e.g., 5 𝑛
  • The monthly log‐returns of all the securities in your portfolio are calculated, e.g., a time series of monthly returns from January 20, 2015 to February 20, 2020 with

𝑟, ln 𝑆,ln 𝑆, , for i=1, …., n

  • The time series of log returns of the market index is also calculated in a similar way.
  • On February 20, 2020, the risk‐free rate (yield) of the government bond is quoted as 1.5% per annum, e.g., rf=1.5%.

Requirements:

  1. Given that the time series of log‐returns of those securities in your portfolio and the stock index, you apply the single‐index model or the Capital Asset Pricing Model (CAPM) to complete the following tasks (20%):
    1. Develop a VBA function CorrMatrix(Retvec) to estimate the correlation matrix among those securities you have collected (excluding the government bond) and show the results in the worksheet.
    2. Develop a VBA function CovMatrix(Retvec, imethod) to estimate the covariance matrix among those securities you have collected (excluding the government bond) and show the results in the worksheet. Note that imthod is an indicator, e.g., imethod = 0 means that the standard statistical method is used, while imethod = 1 means that the estimation is conducted based on the single‐index model.
    3. Develop a VBA function PortRetSigma(Retvec, Weightvec, iopt) to calculate the (expected) return and standard deviation of a portfolio and a single security, indicated by iopt, and show the results in the worksheet, where iopt = 0 for the portfolio and iopt =1, …, n+1 for the specified iopt‐th security.
    4. (OPTIONAL)Develop a VBA function PortVaR(Retvec, Weightvec, TimeHorizon, alpha, Type) to calculate the estimated Value‐at‐Risk of the constructed portfolio in the following method,

e.g., TimeHorizon = 1 month, where alpha specifies the confidence level, e.g., α=95% or 97% and Type indicates the absolute VaR or relative VaR. And compare your VaR estimations with the real performance of your portfolio in the following month from February 20, 2020 to March 20, 2020 and report all results in a worksheet.

  1. Given that the time series of log‐returns of those securities in your portfolio and the stock index, you apply those advanced functions/procedures in Excel to complete the following tasks (10%): e) Constructed the empirical distributions of log‐returns of individual stocks, stock index and the constructed portfolio by using Excel functions, e.g., Frequency, and plot each of them against a normal distribution with the same mean and standard derivation in a worksheet.
    1. Run the linear regression model of the time series of the log‐returns of the portfolio against the stock index, e.g., Linest/regression procedure and report all estimation results, covering the estimates and their standard errors, (adjusted) R2 and others.
    2. Select (any) two individual stocks from your portfolio plus the risk‐free government bond (so that two risky asset and one risk‐free asset) to work out the optimal portfolio that maximize the investor’s utility:

U       E 𝑟          𝐴𝜎 , with A=3.0,

and plot the capital market line (CML) by using the “Data Table” function in Excel.

 

SCENARIO 2 (30%):

Now suppose the market index, S&P500, follows a stochastic process:

dS t  (rf q dt) dW t .

S t

Now you are given the following information:

  • The current stock index on February 20, 2020 is 3,373, e.g., S0 $3373 by ignoring multiplier factor.
  • The continuous compounding interest rate is equal to 1.5% per annum, e.g., rf 5% , whist the dividend yield (q) is assumed to be zero, e.g., q=0.
  • The volatility of S&P500 is 40% per annum, e.g., 40% .
  • A European call and put on this stock index are specified with the strike price of 3,373 (e.g., K=$3373) and the time‐to‐maturity of six months (e.g., T=6 months).

Requirements:

  • Based on the information given above, you apply the numerical methods to complete the following tasks (20%):
    1. Develop a VBA function, EuropeanBSPricing(iopt, S0, K, rf, σ, T) to price two European options above, where iopt =1 indicates a call, and ‐1 indicates a put.
    2. Develop a VBA function, EuropeanOptionPricing(iopt, S0, K, rf, σ, T , nSteps, imethod) to construct a binomial tree that prices two European options above, where iopt =1 indicates a call, and ‐1 indicates a put, while imethod = 1 indicates the JR schema and ‐1 indicates the

CRR schema.

  1. Develop a VBA function, AmericanOptionPricing(iopt, S0, K, rf, σ, T , nSteps, imethod) to construct a binomial tree that prices a (call/put) American option with the same setup as those European options above, where iopt =1 indicates a call, and ‐1 indicates a put, and imethod = 1 indicates the JR schema and ‐1 indicates the CRR schema.
  1. Assume now that in order to hedge the unexpected drops in stock index in the following month due to the spread of the virus, you then take a long position in the European put option above with the quantity of 1 (e.g., nP = +1). You apply the zero‐investment principal to complete the construction of the following trading strategies (10%):
    1. Construct a delta‐neutral strategy on February 20, 2020, and analyze the performance if the stock index reaches 3400 after one month in the Excel worksheet.
    2. Construct a delta‐gamma‐neutral strategy on February 20, 2020 by using the call option specified above for the gamma hedging (∂ 𝐶/𝜕𝑆 ), and analyze the performance if the stock index reaches 3100 after one month in the Excel worksheet.
    3. (OPTIONAL)Conduct a scenario analysis on the hedging performance of two strategies in k) and l) by using the “Data Table” function with the expected index levels after one month:

S    ∈   𝑆       𝑖    20, 𝑖      0,1,2, … ,50 ,

and plot the hedging performance of these strategies in the worksheet.

 

SCENARIO 3 (30%):

Now suppose the price of the zero‐coupon government bond is subject to the risk of interest rate.

Namely, Given the zero‐coupon bond prices P(t,T) in the CIR model as follows:

T

P t T( , )  EQ[et r dss 1]  eA t T( , )B t T r t( , ) ( ),

where

2(e(T t ) 1)

B t T( , )          a e)( (T t )  1)       2 (

A t T( , )  ln( a e2)(e(a(T t)()T t )1)/2          2)2ab/2

(

 a2  22

 

and the CIR short‐rate model is specified as follows:

dr t          a b       r t     dt      σ   𝑟 𝑡 𝑑𝑧

Accordingly, the zero‐coupon yield to maturity y(t,T) is given by:

y t T( , )  1 ln(P t T( , )) [A t T( ,         )  B t T r t( ,  ) ( )]

T t                                         T t              

Requirements:

  1. With the setup of a=0.23, b=0.08,σ=0.06 and r0 =2% on February 20, 2020, you are asked to complete the following tasks (20%):
    1. Develop a VBA function CIRZerobondPriceYield(a,b,sigma, r0, t0, Tot) to calculate the timezero (t0) price and yield of a zero‐coupon bond, given the initial short‐rate of r0, with a set of time‐to‐maturities (Tot) of

Tot = [1,2,3,4,5,6,7,8,9,10,…, 29, 30]

  1. Develop a VBA function BDTShortRate(Yieldvec, Sigvec, r0, Tot, Delt), based on a vector of yields against time to maturity, Yieldvec collected from n), to construct a binomial tree for modeling the dynamics of short‐term rates, where Sigvec indicates the volatilities of shortrates across various time‐to‐maturities (Tot) and Delt indicates the time interval. Report your results in the worksheet.
  2. (OPTIONAL)Develop a VBA function BDTBondOption(iopt, K, Tot_o, Yieldvec, Sigvec, r0, Tot, Delt) to calculate the prices of European calls or puts on the zero‐coupon bond with a set of time‐to‐maturities (Tot_o), where iopt =1 indicates a call, and ‐1 indicates a put, while

K indicates the strike price (a single value) and Tot_o presents the set of time‐to‐maturities (a vector) of this option written on a 30‐year government bond, and other parameters are specified in the same way of o). Report your results in the worksheet (by setting initial parameters).

  1. Within the initial short rate of r0 =2% in the CIR model (which is different from the risk‐free rate, a zero‐coupon yield), you are asked to complete the following tasks (10%):
    1. For a set of zero‐coupon bonds with time to maturities of

Tot = [0.5, 1, 2, 3, 5, 7, 10, 12, 15, 18, 20, 25, 30],

work out the series of yields and forward rates of these bonds. Plot them in Excel accordingly.

  1. Use the “Data Table” function in Excel to conduct a scenario analysis for different initial short‐term rate r0:

r0 ={ 1%, 2%, 4%, 5%, 8%}

and the set of time‐to‐maturities specified in q). Plot the results in Excel accordingly.

 

PART II: Reporting

Based on your analysis on the three scenarios specified above, we need write a short‐summary in a separate file to accomplish the following requirements:

Requirements (10%):

  1. Summarize the results obtained from VBA functions and Excel worksheets. For example, you can mention the key steps or the most important results in each scenario.
  2. Assess the features and difficulties of financial modelling towards equities, options and fixed‐income products. For example, although binomial tree is used for options on equity and options on zero‐coupon bonds, you may comment on the difficulties of this method in these two situations.
  3. The summary is provided with the maximum of 600 words, e.g., roughly speaking, 200 words for each scenario.

Individual Assignment Guideline

 

This individual assignment/project assesses Learning Outcome A‐E.

 

Note that you need:

  • Provide a full coverage of results in Excel, associated with those VBA functions developed in VBE.
  • Show a short report about the project, including the summary of those results obtained from financial modelling processes towards the three scenarios with the maximum of 600 words.

 

As the outcome from your final project, you are expected to submit THREE excel files (one excel file for one scenario), associated with a short report. The deadline of the assignment submission to ICE is at 5pm on June 21, 2020 (Week 15). You may download all the required financial information via https://finance.yahoo.com/ or WIND terminal at Library.

 

Requirements on VBA code

  • There should be no logic mistakes/errors, and the correct results can be returned by invoking in Excel
  • The VBA programme is easy to read with a clear and well‐organized structure
  • Supportive comments/interpretations are provided to explain the role/purpose of code blocks

 

Requirement on summary report

  • Your analysis has to be your own, demonstrating your own ideas and independent and critical thinking (and not those of somebody else).
  • A brief description of the project is needed

In the first section, your work should contain a formal introductory section that provides an overview of the project, including the title of the project, like “A review on Financial Modelling towards Various Scenarios”, the data collection and preparation, securities selection and the main goals that this project aims to achieve.

  • Comments on results (with the maximum 600 words)

In this section, you need summarize the results obtained from VBA functions and Excel worksheets. Also, you can briefly assess the features and difficulties of financial modelling towards equities, options and fixed‐income products. It is very important for you to understand the advantages/disadvantages of each modelling method in specific asset classes, which will help to improve your skills in investment management in practice.

 

Submission:  each student submits only THREE excel files and ONE report. On the first page of the report, please list your name and student ID.

 

Note: the procedure how to run regression in Excel is attached in Appendix C

APPENDIX A Assessment Form for MTH222 Individual Assignment

Student ID

 

                                               

 Max  Points Examiner (100%)
Marks for Compulsory Questions
VBA Code
1. Code Quality 20
2.   Programme design/structure 10
3.  Accuracy of programme outputs 20
4. Use of data structure and algorithms in coding 10
5. Supportive

comments/interpretations

10
6. Illustrations and accessibilities in

Excel

20
Report Presentation*
7. A brief description of the project 5
8. Presentation of report (including writing style, grammar, use of graphics and tables)  5
Total Marks for Compulsory Questions 100
Total Marks for Optional Questions 15
Total Marks for Final Report 100

 

* Items 7‐8 will be evaluated using the following:

  1. Quality and usefulness of data and information gathered;
  2. . Use and application of relevant methods and techniques; III. Clarity and feasibility of assumptions in the analysis.

 

Notes:

  • The full mark is 100 for those compulsory questions;
  • The full mark of the three optional questions is 15, each of which has value of 5 marks;
  • Extra marks will be given ONLY when the optional questions are answered correctly;
  • The upper limit on the final mark in this project is 100.

 

APPENDIX B Stock Prices from Yahoo Finance

You may download share prices from Yahoo Finance at https://finance.yahoo.com. I here show the details for six companies for daily prices and one stock market index (S&P500):

1) Apple:

https://finance.yahoo.com/quote/AAPL/history?period1=1509465600&period2=1539273 600&interval=1d&filter=history&frequency=1d

(see the attached file and use the daily adj close price for your project which includes all the dividends) 2) HP:

https://finance.yahoo.com/quote/HP/history?period1=1509465600&period2=153936000 0&interval=1d&filter=history&frequency=1d

  • Walmart

https://finance.yahoo.com/quote/WMT/history?period1=1509465600&period2=1539360 000&interval=1d&filter=history&frequency=1d

  • BP

https://finance.yahoo.com/quote/BP/history?period1=1509465600&period2=1539360000 &interval=1d&filter=history&frequency=1d

  • Shell

https://finance.yahoo.com/quote/Shell/history?period1=1509465600&period2=15393600 00&interval=1d&filter=history&frequency=1d

  • Target

https://finance.yahoo.com/quote/TGT/history?period1=1509465600&period2=15393600 00&interval=1d&filter=history&frequency=1d

  • S&P500 https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC

You may follow the similar steps to get the share prices for other companies. Please let me know if you have other problem.

 

APPENDIX C

Instruction on Procedure of Regression in EXCEL

Step 1. Prepare your data

 

 

Step 2. Choose File‐> Options‐> Add‐Ins and click “GO” button to install Regression Add‐In package

 

 

 

Step 3 Click “OK” to install “Analysis Tool pak” and “Solver Add‐in”

Step 4 Choose Data‐>Data Analysis to activate the tool box

 

Step 5 Fill in all required inputs, e.g., Ys and Xs

 

Step 6 Obtain the regression results in a new worksheet

 

 

========================== END =================================