Take the 2-minute tour ×
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It's 100% free, no registration required.

I am building a simple stochastic model for learning purposes in excel. I took daily data for the SPY since 1/1/1993. I computed the daily log returns and found that the SPY has had an average daily return of $μ=.034$ and a daily volatility of $σ=1.22$ . So using a stochastic model,

$$dS_t=S_t μ dt+S_t σdW_t,$$

and Ito's derived solution to the SDE,

$$S_t/S_{t-1}= e^{(μ dt+σdW_t)}$$

Since, I computed daily returns and daily volatility and not yearly returns, the scaling factor, $dt$ can be ignored. I used the norminv(rand(),0,1) to generate the wiener process (mean 0, variance of 1). I am actually not sure about this point?? Should I make the the standard deviation time increment? So if I am simulating daily prices, for 10 days out, the variance will $sqrt(1/10)$ for the first day, $sqrt(2/10)$... and so forth.

I am also trying to generate a 2 standard deviation (fan) chart for the forecast, such that 95% of the paths lay within the bands. However, I noticed my bands got extremely big and small, on both sides fairly quickly. I simply calculated the returns using the following formulas:

$$S_t/S_{t-1}= e^{(.034-2*(1.22))}$$ and $$S_t/S_{t-1}= e^{(.034+2*(1.22))}$$

Why are the bands so large? Any ideas? I really appreciate your help.

share|improve this question
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.