So I want to generate series of 26 random numbers many times, and I want their average of each series to be 5. I’d like for the numbers to be negative as well as positive, how do I do that? Rand() doesn’t seem to work well with generating negative nos with equal probability as positive nos.
a*rand()+b will generate random numbers in the interval (b, a+b)
What sort of distribution do you need? Rand() produces uniform random variables. However, you can do various things with the formula to produce normal, log-normal or other distributions.
Also, do you strictly need the average of each 26-unit set to equal 5? Or does the expected value just equal 5?
ohai Wrote: ------------------------------------------------------- > What sort of distribution do you need? Rand() > produces uniform random variables. However, you > can do various things with the formula to produce > normal, log-normal or other distributions. It’s refreshing to see your comment. It drives me crazy when distribution is not considered in random number generation.
Go to Mr. Excel…those people are amazing.
maratikus Wrote: ------------------------------------------------------- > ohai Wrote: > -------------------------------------------------- > ----- > > What sort of distribution do you need? Rand() > > produces uniform random variables. However, you > > can do various things with the formula to > produce > > normal, log-normal or other distributions. > > It’s refreshing to see your comment. It drives me > crazy when distribution is not considered in > random number generation. maratikus I now see the point you were trying to make in the other thread. Didn’t know this tid bit about the rand() formula in excel
Thanks for your responses, I used =(RAND()*(7–7)±7+5)/100+1 to create random values with expected value of 5, so the arithmetic mean of a 26 no. series would roughly equal to 5. But as pointed out, this series is uniformly distributed, but for portfolio returns, I suppose we should use a lognormal dist. Should I do LOGINV(probability,5, standard deviation)?
you need a multivariate normal distribution, not uniform or lognormal
Palantir, It depends on what you’re doing. It can get tricky. First, for one variable and a one period horizon, you could simulate with =loginv(rand(),mu,std)-1. The mu and std in both of these cases should be from the log changes in prices. If you have one variable and more than one horizon, you would need to do =norminv(rand(),mu,std) for many periods, sum them up and then take exp(X)-1 (definition of mu and std is unchanged). If I were doing this for one period, I would take the latter approach even for that since they are basically the same thing since loginv is just a transformation of norminv. (also, if you want prices you would leave off the -1 and multiply by the last price). To extend above to more than one variable, it is much easier to work in something like R or Matlab that has the functionality built in already. You would basically want to use the multivariate versions of the above distributions. If you want to do it in excel, the easiest way is to do the norminv for each of the individuals series and then use mmult to multiply that array by the cholesky decomposition. Then convert back to arithmetic returns. The problem with excel is that it doesn’t have a built-in function for cholesky decomposition. I googled and found these: http://www.quantcode.com/modules/mydownloads/singlefile.php?lid=468 http://david-moriarty.com/Cholesky.html But not sure if any good or not.