This project aims to simulate prices of individual stocks and portfolios based on geometric Brownian motion (GBM) and pricing factor model in Excel and VBA.

Stocks:

In this project, you arbitrarily choose 5 stocks from the current components of Dow Jones. The current components can be found on this website:

https://en.wikipedia.org/wiki/Dow_Jones_Industrial_Average.

Data:

1.    Assume that today is December 1, 2023.

2.    Stock data: download monthly historical price data of 5 stocks you have picked.

3.    Fama-French 5 factor data: download monthly market excess returns (Mkt-RF),size factor

(SMB), book-to-market factor (HML), operating profiitability factor (RMW),investment factor (CMA) and risk-free rates (RF) from Kenneth French’swebsite.

4.    You decide the sample period and briefly discuss the reason in the report. Save the raw data of stock prices and pricing factors in the Excel file.

Empirical Analysis:

1.    Geometric Brownian motion model for portfolio value: Assume that price processes of 5 individual stocks follow geometric Brownian motion (GBM).

1)   The investor constructs an equal weighted portfolio today (01/12/2023) and holds it for

one year (till 30/11/2024). The initial investment is $100,000. There is norebalance during the year.

What is the mean and standard deviation of the portfolio value one year later? Answer the question based on 2000 Monte Carlo simulations.

You also decide how to measure stock returns.

Conducting simulations in Excel spreadsheet is time consuming, especially when the

number of simulations is large. In this practice, you may create the return variables and

estimate model parameters in Excel spreadsheet or using VBA codes. But you must write a  VBA sub procedure named GBM_sim to generate 2000 simulations of the portfolio value   one year later (on 30/11/2024) and write simulation results in the spreadsheet. You design the details of the sub procedure. You are recommended to include some comments in the

VBA code to help TAs understand them.

2.    Pricing factor model for individual stocks:

1)   You simulate the prices of 5 stocks by assuming that the excess returns of each stock follow Fama-French 5-factor model. Five factors are introduced in the seminal paper by Fama and French (2015). The paper is posted on course Moodle. The construction of this factor is also provided on French’s website: http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/Data_Library/ff  5  factors_2x3. html

(a)     Estimate the 5-factor model for each individual stock and report the intercept (a) coefficient, slope coefficient of pricing factors (βMKT, βSMB, βHML, βRMW, βCMA).  You may finish this question in Excel spreadsheet or using VBA codes.

(b)    Simulate the prices of each stock one year later (on 30/11/2024) based on the 5-

factor model. You write a VBA sub procedure named Factor_sim to generate 2000

simulations of prices of each stock. Write 2000 simulated prices in the spreadsheet, then calculate the mean and standard deviation of simulated prices of each stock.  Note: if you consider the correlations between pricing factors and write the codes correctly, you will get 5/100 bonus points.

Deliverables:

1.    Write a short report detailing your data source, how you choose the sample period, assumptions you make (if any), and answer the questions. You don’t need to do extra analysis that is not

required in the questions. The report must have a cover sheet with the group number and names of members.

2.    Put the raw data, the detailed empirical analysis, and VBA code in an Excel file. You should

design the worksheets so that the results are displayed clearly. You can create sub and

function procedures to help answer the questions. You can directly use the VBA code that we introduced in the lectures.  Pleaseadd proper comments in your VBA code.

3.    Submit your report and Excel file on course Moodle. Name your Excel file with your team number (e.g., “Team01.xlsm”). One team only submit one report.

4.    All the students must fill in the peer evaluation form and submit it on Moodle before the due time.

Grading criteria:

1.    Data should be downloaded correctly, and raw data should be saved in the Excel file.