1

Consider a non-dividend paying stock subject to risk-free force of interest, r = 0.045 as well as volatility, σ = 0.24 and time horizon three months, i.e., T = 1/4 for a stock with current price S0 = $45. Recall that dSt = St(rdt + σdBt) where {Bt}t≥0 is standard Brownian motion. Furthermore, recall that Brownian motion is Gaussian with mean 0 and variance t. That is, Bt ∼ N (0, t) for each t fixed, 0 ≤ t ≤ T. It follows that we can exploit this normality, thus leading to the discretisation Sti − Sti−1 = Sti−1(r∆t + σ√∆t) for ∆t = ti − ti−1 where we assume that is a random draw from standard Gaussian distribution, i.e. ∼ N (0, 1) (4)

Given our time horizon of 3 months, we would like to simulate prices for ∆t = 0.005, we simulate iteratively. In other words, after the first time period, we get that St1 − S0 = S0(r∆t + σ√ ∆t)

Where we repeat until the final time step, T = 0.25.

Your task is to simulate 100 such sample paths.

Attempt at Question:

So I understand that we must use the formula St1 − S0 = S0(r∆t + σ√ ∆t) for ∆t=0.005 so i created an excel spreadsheet and subbed in the values and went up by 0.005. I have attached an image of my excel spreedsheetenter image description heret.

but I only got 50 samples instead of 100. Im a little confused with the random draw from the standard gaussian distribution as that is multiplied by the volatility in the formula. Does that alternate between 0 and 1 and i have to do it for both 0 and 1 to get two seperate data sets that will add up to 100. Thanks for any help.

James M
  • 11

1 Answers1

1

You are asked to produce a time series of simulated prices at times from $T=0$ years to $T=0.25$ years at intervals of $\Delta T = 0.005$. So each time series will contain $51$ values (if we count the values at both ends).

You are then asked to repeat this $100$ times, so you get $100$ time series with $51$ values in each one.

A random variable that follows a standard Gaussian distribution $N(0,1)$ can take any real value, but the distribution of its values will have a mean of $0$ and a variance of $1$.

gandalf61
  • 15,326
  • Thanks for your reply i think i understand. So what I have already done is essentially 1 time series from 0.005 to 0.25 with the random variable being 1. I am now asked to do this another 99 times but changing the random variable to any number that is real? Is there a function on excel that will do this? Thanks again for your help – James M Sep 19 '19 at 14:07
  • @JamesM In Excel the formula =NORM.S.INV(RAND()) will return a random number with a $N(0,1)$ distribution. Note that the random number will change each time you recalculate the spreadsheet, so you should copy and paste the values in your time series if you want to refer to them later. – gandalf61 Sep 19 '19 at 14:36
  • thanks mate this all makes sense, just one more quick question is there a function i can use on excel to generate 100 samples or as they change would i need to copy and paste these values 100 times to another spreedsheet. – James M Sep 19 '19 at 14:50