Unique List if Random EOMONTH Dates

Closed
Macro101 - Jul 11, 2010 at 01:11 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 13, 2010 at 07:32 AM
Hello,

I've been building an excel model which looks to model a series of 33 investments. The model employs End of Month (EOMONTH) and runs 15 years forward. I hope to run Monte Carlo on the model using Oracle Crystal ball.

The problem I face is that the series of investments could be made on the same EOMONTH date between Jan 2010 and Dec 2020 and the exit date on any single investment needs to be a random number of months between 12 and 72 (i.e. 1-6 years) but no two investments can exit on the same EOMONTH date between Jan 2011 and Dec 2027.

I have tried to employ all forms of randomisation I know but unfortunately every so many runs, two dates do fall on the same EOMONTH date, leading the model to REF out preventing Monte Carlo software from working.

I think the only solution to the problem is a macro, but my macro skills are as much use as an ejector seat in a helicopter.

Has any Excel Jedi out there any suggesttions,

Cheers, Tom

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 13, 2010 at 07:32 AM
Not knowing all how this all setup is working, how about employing a dictionary object, where the key is the date. So if the date exists, you know that you have to try for some other date.