Hi all, I want to run a monte caro simulation to model the spread between two indices. Simple in theory simply get distributions fir the 2 indices and the correlation between them and then simulate. My problem is that the indices are not normal and cannot be accurately summerised with the 1st two moments. Does anybody know how to mode a normal distribution with non standard skewness and kurtosis in excel? Does anybody know how to fit other distributions in excel? I need to both calculate which distribution is the best fit and then sample from that distribution during the simulation. Does anybody know of any source code that can do this?

I hate to be picky, but it the distribution has a "non standard skewness and kurtosis" then it is probably not normal. ...Anyway, if you have the inverse cumulative distribution function for the distribution, you can use that in Excel, using the RAND() function as an input. ...I know you don't want the normal, but just for the sake of example, if you wanted some random draws from the standard normal distribution then you could just use:=NORMSINV(RAND())If the CDF of your distribution isn't invertable, there may be a good approximation to the inverse that you can use.As for fitting the distribution, you should probably look to another application. You could do this in Excel, but it wouldn't be easy.

Why don't you use Historical Data in your MC generation?

try gram-charlier/edgeworth expansion (or more generally cornish-fisher) to match moments.this alters just higher moments of normal dist.now sample normal dist and adjust density (or alternatively via dist. function, ie via uniform space)

You can also try using elliptical distribution functions (like hist or histogram function in MATLAB. Its functionality is also available in Excel as an add-in).Plot a cdf, and do an inv as suggested by MikeM. Hope it helps

Y not just take the actual dist of returns and bootstrap (random draws with replacement) them. You'll get a new dist that has the higher moments in it, just not in the identical order as the original.

I would not implement gram-charlier or parzen's or anything like that in excel, but maybe that's just my weakness as a vb programmer. However, if you just want to match a finite number of moments, you can do a mixture of normals pretty easily in excel with just spreadsheet functions and some patience.