# Solver and random output

#### Christian

I am working on a project that generates twelve months of random returns subject to certain constraints such as annual return, annual volatility, and upper and lower limits for any single month return. Other constraints are a skewness of +- .03 and a kurtosis >= 2 and <= 4

To do this, I am using the Solver tool and VBA. I have no problems generating random numbers, and my constraints are matched, however after running the program a few times I see an output that repeats the same numbers. For example:

At first I might get something like this:

14.77%
3.55%
-8.00%
-8.00%
3.52%
3.44%
3.57%
3.50%
3.53%
0.08%
0.96%
3.51%

Which is generally acceptable. However, often I will get a return stream like this one:

-0.79%
14.81%
-8.00%
-8.00%
3.31%
3.31%
3.31%
3.31%
3.31%
3.26%
3.30%
3.30%

Which repeats 3.31% too often. Does anyone know why this could be, and if there is a way to limit the number of occurences of values that fall within a certain range of each other?

Thank you!

#### IlyaKEightSix

Hmmm...I believe that's an issue with your random number generator. I forget the exact algorithm from my monte carlo class but it was proven with number theory that some absolutely ridiculous way of generating random numbers would have a full period...if I have my notes on my laptop at home, I'll post up the relevant information...if not, I'll have to email my professor about them.

#### Uncle Max

Looks like it's trying to compensate for a high volatility at the beginning by generating flat numbers at the end. That's why amounts are almost the same. And the reason that it's around 3% all the time might be in constrains.

In general, I don't think generating this kind of thing using solver is a good idea. Why don't you use just some kind of stochastic process instead?

#### Christian

This tool needs to be written for Excel. Is it possible/reasonable to to stochastic volatility models in Excel?

This data stream will be used as a rough estimate for a much bigger project, so keep in mind that I am not looking to kill a squirrel with a rocket launcher here.

#### alain

##### Older and Wiser
do the numbers need to follow a certain distribution?

#### alain

##### Older and Wiser
From the data that you posted at the beginning it looks like the series will have similar outputs regardless. Even when you say it's ok, the two series are very very similar (far from random). Look into the process you are using. As Max said, your constraints are limiting the output.

#### Christian

do the numbers need to follow a certain distribution?

Normal is the goal, using skew and kurtosis as constraints. However we are talking about 12 values here - not enough to give a real approximation to a normal distribution.

Perhaps I will relax the constraints slightly to get a better variation of numbers. However, after many times running this stream, often times the same value will stay in the same place. For example, +10% will be the third value in the series 10 times in a row. Any ideas about this?

#### doug reich

##### Some guy
=NORMINV( RAND(), 0, 1 )

will give you random values from a normal distribution. (You probably want to copy paste-as-values once you get a few.)

... although rereading your email, that won't give you what you want, will it?

#### ExSan

try to generate not only 12 random numbers, but maybe more than 40, then filter the repeated numbers as well as the negative outcomes if that is the case

#### Christian

I found the problem was that too much of the volatility was occuring between the first few data points. To smooth the returns a bit I constrained the cumulative volatility of the first six months to roughly 50%.

The data comes out much nicer and the problem of repeated values is much less of one now. Thank you everyone for the help (but please continue to discuss the topic if you can contribute to the Excel/optimization problem)!

#### Bastian Gross

##### German Mathquant
Hey Christian,

Excel has (well known) problems with fractional digits, especially with more than 16 digits. So there are many examples like:
If the Microsoft-program sum 0.05, -0.07, 0.02 und 0.00, then the result will be -3,46945E-18, but obviously the result should be zero.

It's a problem about format: "standard" vs "number"

Excel bug calls for wrong number
So falsch rechnet Excel

Replies
39
Views
156K
Replies
4
Views
16K
Replies
19
Views
8K
Deleted member 2387
D
Replies
0
Views
2K
Replies
17
Views
4K