• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

Solver and random output

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:


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


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!
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.
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?
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.


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.
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?
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
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