Project 8- Finance (Monte Carlo Simulation)
mharpermontes
project8.docA. Can Inci
FIN 465
Innovations in Contemporary Finance
Project 8: Monte Carlo Simulations
In this project, you will do some Monte Carlo Simulations using stochastic processes in continuous time.
The stochastic process will be the geometric Brownian motion, which is standard for stock price processes. Therefore, the stock price process is:
S1 = S0 exp(
m
ˆ
dt + σ dz).This means the stock return process is:
Δ ln(S) =
m
ˆ
dt + σ dz.Here
m
ˆ
and σ are the annual mean stock return and annual standard deviation of your stock.In the first step you have to calculate
m
ˆ
and σ. You can use project7MCsimulation.xls file MuSigma worksheet for this purpose.To do so, first download last 5 years’ adjusted closing prices for your stock from finance.yahoo.com. (Go back to December 2013) Copy paste the date and the adjusted closing prices to MuSigma worksheet. Keep the most recent 1201 prices so that you will have 1200 returns.
Next, find log daily returns:
=LN(price end of today / price end of yesterday).
Find these log returns for each day over the last 5 years. MuSigma worksheet should already have the formula. Make sure you cover the range of prices you have copy-pasted.
1. Find the mean of these daily returns. Annual return,
m
ˆ
, is that mean multiplied by 252 (assuming there are 252 trading days in a year. Again, MuSigma worksheet has the relevant formulas. There may be intentional mistakes. Make sure the formulas are correct.2. Find the standard deviation of these daily returns. Annual standard deviation, σ, is this result multiplied by square root of 252. MuSigma worksheet has the relevant formulas. Again, make sure they are correct.
Make sure you record these results in your report.
Next, obtain a price path with Monte Carlo simulation utilizing project7MCsimulation.xls file LogNormPriceSimulation worksheet. The macro programs in the excel file calculates next day’s stock price based on a stochastic process. The price is calculated for the next 250 days, i.e, for the next year. The graphical representation of the next year’s price movement is plotted.
First change mean (cell: L7) and sigma (cell: L8) to the annual values you have calculated in the MuSigma worksheet above. Also change current price (cells: L6) to the most recent price of your stock.
The first macro program you will run is called pricePathSimulation.
With ALT+F11, go to the macro program, and correct the model first!
You can run the macro program with Ctrl+A. Run the macro. Copy Paste the graph into your report and provide a 1-paragraph explanation of what this graph is.
Next you will repeat this 1000 times. Do this by running the macro program called ‘runmany’. This macro is activated with Ctrl+B. Each of the 1000 times, monte carlo simulation will give a price path. The last price in this price path will be your stock’s price 1 year later. What I want you to do is to record the 1-year ahead price for each of these 1000 simulations, and then find the average of these 1000 prices. That is the price projected by Monte Carlo simulation for this stock one year later. Run the macro with Ctrl+B, see the Simulated values recorded after each simulation. The average of these 1000 simulations is reported in cell N11.
Make sure you include in your report:
1. Printout of your macro program
2. Printout of the price path graph for the 1000th simulation.
3. Printout of the Excel file LogNormPriceSimulation worksheet portion: J3 : P11
4. One paragraph describing the entire process and what you have done.
