Randseq

Repeatable Random Numbers

FSRANDSEQ

The random number generator in Excel rand() gets a seed value from the system timer and then generates a sequence of random numbers from that. Thus each time a spreadsheet with rand() it is opened a new sequence starts. Thus it is very unlikely that you would ever get the same sequence of random numbers. Sometimes this is very inefficient since you may want to test a model for parameter changes, and difference in out come could be due to sampling difference. To compensate for this you might increase sample size significantly for both simulations and then see what the difference is. This should work, but is costly. A way to account for this is to run simulation with same exact sequence of random numbers. FSrandseq(seed) allows you to do this. But in order for that to happen you must plan properly. The first is obvious; you must use the same seed value for both simulations (as well as for all occurrences of the function in the spreadsheet), in fact that may be the easy part. The second is that you must design the spreadsheet so the calculation sequence does not change.

Excel has a set of rules that determine the way the sheet is recalculated. If the spreadsheet does not change, the order of calculation will not change. But if anything is changed except the value(s) of a constant in the sheet you run the chance that the order of calculation will change. If there are more than one FSrandseq(seed) in the spreadsheet the order in which they are called may change, which would swap the order in which random number are generated. Remember that the seed value determines the start of the sequence, the last number in the sequence is used to seed the random generator for the next number. The best thing to do is to avoid this by not changing the spreadsheet between runs. It is important to design the spreadsheet so that only parameter values need to be changed.

As an example you may have two estimates for the standard deviation of a variable. Set up the spreadsheet so that the mean is one cell and the standard deviation is another as constants. In the example below the process uses the FinPak function: FSnorminv to simulate price, given the mean price of $7.00 and ad standard deviation of .50 (yellow box). The FSnorminv third input parameter (Cell B7) is optional but in this case the use of FSrandseq(1234) allows us to repeat the exact same sequence of random numbers to see see what the impact would be if the standard deviation was changed to $1.00. You may use any whole number as a seed, do not change seed values within the same spreadsheet since this may create unpredictable results.

Any difference that you observe between the two sets outcomes will be due to the change in parameter (standard deviation) not the sequence of random numbers. If you use FSrandseq(seed) in your spreadsheet the output from FinPak will supply a series of random numbers, if the FSrandseq(seed) were same these numbers should match exactly, not almost. Each time the simulation is rerun the FSrandseq(seed) is restarted with the seed value. If you wish for some reason to force this sequence to reset at other times, use the reset command on the FinPak tool-bar: Note you can press this as often as you wish; each time the sequence will begin over again on the next re-calculation of the spreadsheet.

Output sheet will contain a check value you help you determine whether two runs of a spreadsheet used the same sequence of random numbers.