Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Worksheet “ NFLX” contains monthly stocks data of Netflix Inc. (NASDAQ: NFLX) listed on NASDAQ from December 2012 to December 2020. Data are downloaded from Yahoo!Finance.

Worksheet “ FF4 Factors” contains monthly returns data of Fama-French 3 factors (market factor “Mkt-RF”, size factor “SMB”, book-to-market factor “HML”), momentum factor “Mom” , as well as monthly risk-free rates “RF” . Data are downloaded from Kenneth R. French’s website.

Momentum factor is defined as the difference between monthly average return on the high prior    return portfolio and the monthly average return on the low prior return portfolio. The momentum   factor refers to the tendency of winning stocks to continue performing well in the near term. Four- factor model including FF3 factors and momentum factor is widely used in the industry.

Use these data to answer Question 1.

Question 1.

Go to Worksheet “Q1”. Use the raw data in Worksheet “ NFLX” and “ FF4 Factors” to answer the following questions.

(1) In columns C:G (highlighted range), calculate monthly data of four factors and risk-free which are ready for the multivariate regression (to estimate four-factor model).

(2) In column H (highlighted range), construct monthly log returns of NFLX stock from January 2013 to December 2020.

(3) In column I (highlighted range), construct monthly log excess returns of NFLX stock.

(4) Estimate the sample average, standard deviation, and correlation matrix of four pricing factors (Mkt-RF, SMB, HML, Mom). Report the results in highlighted ranges in columns L:O.

(5) Run the time series regression and estimate four-factor model for NFLX stock. The factor model is

where Re  is excess return of NFLX stock, is market excess return, SMB is size factor, HML is book-to-market factor, Mom is momentum factor.

In range L14:L24 (highlighted), use Excel functions to return the regression results, including a , β1 , β2 , β3 , β4 , residual standard error and t-statistics of hypothesis tests on each coefficient.

Note: You should NOT use Data Analysis Toolpak to generate regression results. You must enter one formula in each cell to immediate return the desired value.

(6) VBA function simfactor was discussed in the lecture. Please write a VBA function

simfactor_new that simulates the stock price of individual stock whose log excess return satisfies the multifactor model with up to 5 factors.

Note: This function should return stock price, not excess return. Please determine the arguments of this VBA function.

where S0  is the current stock price, Tis time to maturity, r is the continuously compounded risk- free rate, q is the continuous dividend yield, and σ is the volatility of the underlying stock.

In this question, we value an “arithmetic average price ” Asian put option. Its terminal payoff at expiration date is

Question 3. In Module Q3write a function procedure called skew1that takes one argument  datarange. This function should calculate the skewness of stock returns, given a series of  adjusted closing prices (datarange). The equation to compute skewness of n stock returns {x1, x2, ⋯ , xn } is given below:

where is log return based on adjusted closing price, is the arithmetic average of n returns.

In cell B24, you calculate skewness of log returns given the stock adjusted closing PRICE data in range B2:B22 using your VBA function skew1.

Question 4. 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, 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 binomialJR to 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,

where r, q, σ, and δt have the same definition as CRR model.

(3) Use CRR and JR Binomial models to calculate this call option price (based on the parameters that you estimate). Report results in cell D23, D24 (highlighted).