This is a computer-based exam. There are 4 questions in total. Please read the instruction of each question on this exam paper carefully, and finish the questions in the Excel file.

When you answer questions in Excel, you must include the intermediate calculation results in the worksheet. Results without calculations get 0 point.

During the exam, you can get access to the internet. However, exchanging any type of messages (such as emails, instant messages, chatting) with others is strictly prohibited. Printed materials, electronic               devices except your computer are also prohibited during the exam.

Please submit the Excel file on course Moodle before the due time (5:10p.m.). Late submission will not be accepted.

Worksheet “AMZN”, “JNJ”, “WMT” contain monthly stocks data of Amazon, Johnson & Johnson, Walmart from December 2015 to December 2019. Data are downloaded from Yahoo!Finance.

Worksheet “Riskfree” contains monthly data for 4-week Treasury Bill rates from 2015 to 2019. Data are downloaded from St. Louis Federal Reserve website.

Worksheet “FF3 factor” contains monthly returns data of Fama- French 3 factors (market factor “MKT- RF”, size factor “SMB”, book-to-market factor “HML”), as well as risk-free rates “RF” are downloaded   from Kenneth R. French’s website. Note that only the data between 12/2015 and 12/2019 are listed in the table.

Use these data to answer Question 1 to 3.

Question 1 (30 points).

Go to Worksheet “Q1”. Use the raw data in Worksheet “AMZN”, “JNJ”, “WMT” and “Riskfree” to answer the following questions. You can use Excel functions, or your own VBA function/sub         procedures to generate results.

(1) Calculate monthly log returns of three stocks (AMZN, JNJ, WMT) from January 2016 to December

2019. Report the results in highlighted range of Columns D:F.

(2) Estimate the inputs values, including expected returns, sample variance-covariance matrix, sample correlation matrix of three stocks using the monthly data in part (1). Report the results in highlighted   ranges of Columns I:K.

(3) At the beginning of 2020, you construct the portfolios based on the parameters estimated in part (2).

If there are no constraints in the investment, use the analytical solutions to find the weight vector of optimal risky portfolio (P) and global minimum variance portfolio (GMVP). Report the results in           highlighted ranges of Columns M:O.

(4) Now consider the case with short-sales constraints. Find an envelope portfolio with expected return 1.8%. Report the weight vector of this portfolio in Range R4:R6.

Record a macro of the Solver setting that finds this envelope portfolio. Name the sub procedure as "Envelope" and store it in Module "Q1".

(5) After the optimal risky portfolio is constructed in part (3), investor holds the portfolio for 1 year. The initial investment in this portfolio is $1000.

Assume that stock returns of three stocks follow the Geometric Brownian Motion (GBM). Discrete-time version of Geometric Brownian Motion for log-return is 

 →+  = ln ( +) − ln ( ) =  + √, ~(0,1)

Stock price over any  period of time is

 +  =  exp( + √) ,  ~(0,1)

What is the average value of your portfolio investment after 1 year? Answer this question based on 30 simulations.

The VBA function Cholesky is given in Module “Cholesky_fn”.

Please put all the intermediate calculation results in this worksheet. If you need to estimate other             inputs, also put them in this worksheet. You can generate results using Excel functions or VBA functions.

Question 2 (15 points) Go to Worksheet “Q2”. Now you estimate the Fama- French 3-factor model of JNJ stock based on the monthly data from 2016 to 2019.

Fama- French three factors and risk-free rates are stored in Worksheet “FF3 factor”.

(1) In Column D, report the monthly excess returns of JNJ from 2016 to 2019. The log returns data of JNJ are as you calculated in Question 1(1).

In Column E:G, report the monthly returns of three factors, which will be used in the following regression.

(2) Run the regression and estimate Fama- French 3-factor model of JNJ stock. The factor model is

  =  + 1, + 2 + 3   +  

where  is excess return of JNJ stock,  ,   is market excess return, SMB is size factor, HML is book-to- market factor.

In Range J3:J7 (highlighted), use Excel functions to return the regression results, including , 1, 2, 3, residual standard error.

In Range J9:J14 (highlighted), estimate mean and standard deviation of monthly returns of three factors.

Question 3 (25 points) Now you simulate the excess returns of JNJ stock using Fama- French 3-factor model that you have estimated in Question 2.

The factor model is

  =  + 1, + 2 + 3   +  

where  is excess return of JNJ stock,  , is market excess return,   is size factor,   is book- to-market factor at month t.

We assume that

•     three factors  , SMB and HML are independent.  is the firm-specific shock and is independent to all the factors. These four random variables follow the Normal distributions.

•      ~ (1, 1), where 1  is the mean of market factor, 1  is the standard deviation of market factor.

•      ~ (2, 2), where 2  is the mean of size factor, 2  is the standard deviation of size

factor.

•      ~ (3, 3), where 3  is the mean of book-to-market factor, 3  is the standard deviation of book-to-market factor.

•      ~ (0, ), where SD is the standard deviation of firm-specific shock.

VBA function simfactorthat we introduced in class returns the simulated excess return of individual stock based on a factor model (up to four factors). It is given in Module “Q3”.

Now you write a VBA sub procedure named FactorSimulation in Module “Q3” that conducts the time-series simulation of JNJ stock excess returns based on Fama- French three factor model.

The sub procedure should do the following things:

•     In Question 2, you have estimated all the parameters of this factor model. Read these parameters into the sub procedure.

•     Use VBA function simfactorto simulate excess returns of JNJ stock for 10 times and write them in Range D3:D12 of Worksheet “Q3”.

You can decide the other details in this sub procedure as long as above requirements are satisfied.

Question 4 (30 points). Go to Worksheet “Q4”. This question aims to use different option pricing models to calculate Facebook (NASDAQ: FB) stock option price, and implied volatility.

•     We choose a call option written on this stock with strike price K = $175, and maturity date          January 26, 2018. The option valuation date (today) is January 12, 2018. The market price of FB stock is $179.23. The market price of this option is $6.23. (Information is listed in Excel as well.)

•     Daily market data of Facebook stock in the past 1 year are downloaded from Yahoo!Finance and saved in Worksheet “FB”.

•     Facebook stock never paid dividends.

•     Daily 4-week Treasury Bill rates, 3-month Treasury Bill rates in the past 1 year are downloaded

from St. Louis Federal Reserve website and saved in Worksheet “Tbill_FB”.

(1) Complete the input table in range D12:D19 (highlighted) for the calculation of this option based on Binomial option models. You can decide how to measure returns from the given FB stock data, and       method to estimate inputs.

(2) In VBA Module “Q4_2”, VBA function binomialCRR is given to calculate European/American option price based on CRR binomial option model.

Now in this module, write a new VBA function named binomialJRto calculate European/American option price based on JR (Jarrow and Rudd) binomial option model.

JR (Jarrow and Rudd) Binomial Tree:

JR Binomial option model is a binomial tree that is also widely used. The difference between JR and CRR  tree is the definition of (1) probability to move up, p; and (2) "up" and "down" price multipliers, u and d. Specifically,