
#11




Quote:
I dont know anything about the randomness for the simulations though. 
#12




Quote:
__________________
ADoubleDot: I'm an actuarial icon. **** Juan. 
#14




For those serious about pseudo random number generation, a good free starting point is the Interest Rate scenario generator spreadsheet created by the Academy's Economic Scenario Work Group (ESWG), which contains a VBA random number generator (http://www.actuary.org/life/scenarios.asp). Of course, that's only a small part of the spreadsheet, but I found it interesting to look through.
Last edited by echo; 07172009 at 10:24 AM.. Reason: Corrected  does NOT uses Mersenne Twister algorithm 
#15




Rnd [the VBA implementation in Excel] is seeded. That's a good point. Obviously, it's not volatile, as it's within VBA code itself, and you have to specifically run that. [Yes, you can have it run on certain actions, like ThisWorkbook_Change, but then you did it on purpose to yourself].
Given that, I'm going to go test this dang thing on my own. I have a separate PRNG I want to test [related to PBA stuff in the U.S.] By the way, I do use RAND(). It's mainly for demo spreadsheets, for educational purposes. I don't really want to be going to the trouble of incorporating addins such as NtRand [more on that later], or adapting something from Numerical Recipes, if all I'm doing is demonstrating simulation techniques. And lastly, while Pseud is a great poster, if you want to make some nice references to him regarding generating new Pseuds, there are several threads in NAT and the Reef that would be more appropriate. Just a suggestion. [I know I'm not always serious, but I intend this to be a serious thread on Monte Carlo modeling.] 
#17




Quote:

#18




One also has to be very careful about running out of randomness. I had a colleague who spent some number of weeks trying to debug a monte carlo simulation, only to eventually find out the randon number generator wasn't "big" enough for the number of simulations. It was starting to create patterns, which was screwing up the distributions of the results, or something like that. I don't know how good the excel rand is in this respect probably if you are doing something in excel, you don't have so many experiments to worry about it.

#19




Quote:
It is true that one shouldn't be doing heavyduty Monte Carlo modeling in Excel, no matter the PRNG. But I've done it, and sometimes you gotta use the tools at hand. Also [looking at the Academy interest rate generator above], sometimes someone has already done the work for you in Excel. So it can make sense to take advantage of that. 
#20




One of the problems I have with Excel is that it is not programmed to check numerical stability. Certain mathematical operations lead to loss of precision or spurious precision, and under an iterative algorithm (as is often the case with Goal Seek or formulas that calculate off of other cells), the result of certain calculations could lose significance. Excel will give you an answer, but how many digits of that answer are known to be correct? If the calculation is performed with higher precision, how many digits will change? These are questions that Excel is not designed to address. Mathematica has builtin checking and uses known stable algorithms wherever possible to avoid losing precision. It very often errs on the conservative side, and of course it is slower because of this checking. And occasionally you have to have a better mathematical understanding of how to properly implement a particular calculation. But the result is that you have a much greater degree of confidence in the answer.
Indeed, I would say that most any arbitraryprecision computing environment has to do some of this checking that Excel does not.
__________________
Spoiler: 
Tags 
data science, excel, predictive analytics, prngs, pseudorandom numbers, rand, random 
Thread Tools  Search this Thread 
Display Modes  

