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:

Dow Jones Industrial Average (DJIA) index is a price-weighted measurement stock market index of 30 prominent companies listed on stock exchanges in the United States.

In this project, you arbitrarily choose 4 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 January 1, 2023.

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

3.    FF3+Momentum 4 factor data: download monthly market excess returns (Mkt-RF), size factor (SMB), book-to-market factor (HML), momentum factor (MOM) and risk-free rates (RF) from Kenneth French’s website.

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 4 individual stocks follow geometric Brownian motion (GBM).

1)   The investor constructs a “best” portfolio today (01/01/2023) and holds it till the end of

2023. The initial investment is $10,000. There is no rebalance during the year.

What is the annualized mean and standard deviation of the portfolio value at the end of 2023? Answer the question based on 1000 Monte Carlo simulations.

You decide how to construct the “best” portfolio and write down the reason in the report. 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 parameters in the spreadsheet. But you should write a VBA sub procedure named GBM_sim to generate 1000 simulations of the portfolio value at the end of 2023 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 codes to help TAs understand them.

2.    Pricing factor model for individual stocks:

1)   You simulate the prices of 4 stocks by assuming that the excess returns of each stock follow single index model.

Specifically, the investor purchases one share of each stock today (01/01/2023) and holds them till the end of 2023. What are the annualized mean and standard deviation of the prices of 4 stocks at the end of 2023? Answer the question based on 1000 Monte Carlo simulations.

You should write a VBA sub procedure named CAPM_sim to generate 1000 simulations of stock prices for the four stocks at the end of 2023.

In this practice, you may create the return variables and estimate parameters in the spreadsheet. But you should simulate stock prices in the sub and write simulation results back to the spreadsheet.

2)   Assume that the excess returns of each stock follow FF3+Momentum 4-factor model. The first three factors are Fama-French three factors, and the fourth factor called “momentum” is introduced in the seminal paper by Jegadeesh and Titman (1993). The paper is posted on course Moodle. The construction of this factor is also provided on

French’s website:

Kenneth R. French - Detail for Monthly Momentum Factor (Mom) (dartmouth.edu)

Estimate the 4-factor model for each stock and report the intercept (a) coefficient, slope coefficient of each pricing factor (FMKT , FSMB , FHML , FMOM ). For each stock, answer the following questions:

(a) Is 4-factor model preferred over CAPM model?

(b) Do you want to include all the four pricing factors in the model? If not, which factors do you want to use to explain the excess returns of individual stock?

(c) You repeat the simulation of year-end stock prices for each stock as in part (1) based on the 4-factor model. You write a VBA sub procedure named Factor_sim to generate 1000 simulations of year-end stock prices for each stock. Write 1000 simulated prices in the spreadsheet, then calculate the annualized mean and standard deviation of simulated  prices for each stock.