Unique List if Random EOMONTH Dates

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.