Counters and lag operators are accessory functions to help you building and managing Monte Carlo simulations models.
A counter is a function that returns an increasing value every time that spreadsheet is calculated. A calculation of the spreadsheet is fired when the users modified any cell value, when the user hit the “F9” key or when a new simulation is sampled. You can enter a counter by the “sCounter” function. The first argument of this function gives a number identification for the counter and the second sets counter step size.
A lag operator stores the value of one or more cells and displays it in the next simulation. Lag operators can be used in a chain, allowing that the values of past simulations are used during the current simulation. A lag operator is called using the “sLag” function. The first time that this function is called it returns the “#NUM!” value since it doesn’t have any value stored. Pressing F9 a few times the error value will soon disappear.
The spreadsheet below shows how to use counters and lags to generate a Monte Carlo simulation with antithetic scenarios.
A |
B |
C |
D |
E |
F |
|
1 |
|
|
|
|
|
|
2 |
Antithetic simulation example: |
|
|
|
|
|
3 |
|
|
|
|
|
|
4 |
Simulation counter |
3 |
=sCounter(1,1,,1) |
|
|
|
5 |
Normal random |
-1.22341 |
=sRAND_StandardNormal() |
|
|
|
6 |
Antithetic normal random (lagged) |
0.379419 |
=-sLag(B5) |
|
|
|
7 |
|
|
|
|
|
|
8 |
|
|
|
|
|
|
To restart the counters and clear the lag operators, just click on the button .