Random Negative Returns

What kind of random distribution are you wanting to use? How often should it go negative? When it is negative, what range do you want the random number to be within? Same questions for positive. Ultimately the answer to your question is to define what kind of "distribution" you expect from your random number and build your random number generator to match that distribution.

Something as simple as =RAND()-0.5 will return a random uniformly distributed number between -0.5 and +0.5. Roughly half the time it will be negative and half the time it will be positive. That's probably not exactly what you want, but that might make a good starting point.

Perhaps add a mutiplier to the end of yout formula. e.g

Formula:

Please Login or Register  to view this content.

Of course being a random number there is no guarantee that any of the 20 years will be negative. And of course there may be more than one negative

A workaround to that would be to hold that random number formula on a helper row and point to the helper row for the multiplicand, and then pick pick one of the 20 years net return formulae and instead of pointing to the helper row make the mutiplicand

Formula:

Please Login or Register  to view this content.

If non of the helper row cells are 0 this will force the chosen formula to be negative.
Strictly speaking I suppose that since you are now forcing a negative result and only randomly generating 19 numbers you should perhaps change the RANDBETWEEN to RANDBETWEEN(1,19)....or should that perhaos be between 1,21 since you are guaranteed at least one negative. (it's getting late here and my brain is closing down for the night......

Yes just adapt the formulaI gave you

To obtain an average of 35% of returns being negative add the multiplicand at the end

your_formula*IF(RANDBETWEEN(1,100)<=35,-1,1)