wilmott.com

Hi guys,I am upto a task of modelling the stock returns with the famous stock price model. (dS_t)/S_t =µtdt + σdBt where S_t is the stock price at time t.From this formulation it comes out that the stock prices follow a lognormal distribution.Now i am interested to make a probabilistic statement about total stock price upto time t,i.e. sum of lognormal random variables.How can I find the distribution of the same to calculate the percentiles.I have done simulations of the total sum and used thesample percentile as an approximate estimate to population percentile.But i am not satisfied with the procedure.Can you guys suggest something?N.B.The only software I can use for all purposes is MS excel 2007.

its not clear what you mean by total ( for a continuous process). If you mean you want the probability distribution of the sum of stock prices at n observation times in the future, then you might want to google asian options

QuoteOriginally posted by: arnablostbeingHi guys,I am upto a task of modelling the stock returns with the famous stock price model. (dS_t)/S_t =µtdt + σdBt where S_t is the stock price at time t.From this formulation it comes out that the stock prices follow a lognormal distribution.Now i am interested to make a probabilistic statement about total stock price upto time t,i.e. sum of lognormal random variables.How can I find the distribution of the same to calculate the percentiles.I have done simulations of the total sum and used thesample percentile as an approximate estimate to population percentile.But i am not satisfied with the procedure.Can you guys suggest something?N.B.The only software I can use for all purposes is MS excel 2007.This question sounds a bit confused. You sum (normal) log returns to get the total return, so future stock prices are log normal. There is no need to sum log-normal random variables. I will add that the distribution of the sum of log-normal random variables is difficult to compute, to say the least.

Thanx for the responses I got.Let me clarify on the question.Suppose X_t be the stock price at a time point t.When it is modeled by the stock price model (Ito's process) it follows a lognormal distribution.Now I am interested in making confidence statements about X_1+X_2...+X_n.How should i proceed?If you want to know why I need this, then here is the background:I am upto a task where I need to model some business volume which has the same property as the stock prices,i.e. the relative rate of increase and volatility are dependent on the volume at that point of time.X_t is volume in year t.dX_t/X_t =mu_t*X_tdt + sigma*X_t dB_t, B_t is brownian motion.Now i am interested in the total volume after n years.I hope this clarifies the problem.

I'm still confused.Is X_t the total volume from time 0 to time t, or is the total volume ?

It sounds like X_n is the volume in year n and he wants the distribution of total volume sold after N years. You can look at the literature on Asian options, which deals with this sort of problem. The simplest things people do is to solve for the mean and variance analytically and then plug these into a handy distribution (usually normal or lognormal), but this will not be precise, especially in the tails. If N is moderately large, the central limit theorem will make the distribution of the sum look more like a normal, but it takes a while. It is an honestly hard problem, and MC may be the most reliable way to go.

QuoteOriginally posted by: bearishIt sounds like X_n is the volume in year n and he wants the distribution of total volume sold after N years.If this is true, why is the model in continuous time?QuoteThe simplest things people do is to solve for the mean and variance analytically and then plug these into a handy distribution (usually normal or lognormal), but this will not be precise, especially in the tails.This would be a good place to start.

Thanx all...the model is a continuous model but we want the total volume at year endings which is just the sum of X_t s at t=1,2,3...n.So no integration only summation.I have used the monte carlo simulation.Mean and variance estimates are reasonable not t he percentile estimates.In literature there is no exact solution to the distriution of sum of lognormals.

Try to be smarter with your MC to get the percentiles or try 4 moments approx a la Milevsky and Posner.

QuoteOriginally posted by: arnablostbeingThanx all...the model is a continuous model but we want the total volume at year endings which is just the sum of X_t s at t=1,2,3...n.So no integration only summation.I have used the monte carlo simulation.Mean and variance estimates are reasonable not t he percentile estimates.In literature there is no exact solution to the distriution of sum of lognormals.I suspect your distribution can be determined exactly with a little algebra. Write out exactly the random variable you want the distribution for, in the case n=3.Now write it in terms of the independent increments x_j = log S(j)/S(j-1).What is the characteristic function for that sum?Invert it to get your distribution.Generalize to all n.If this is not clear, post your steps up until the part where you get stuck.p.s. This might be a lot harder than I suggested.

I have dealt with sum of lognormals before for leakage prediction. The sum of lognormals is not really closed form, but under certain condition the inverse is close to a Gamma. So a few finance researchers model it as inverse Gamma distribution. You can capture the parameters from moments easily.