This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am trying to run a Monte Carlo simulation on a collection of term insurance policies. The mortality factor for each duration through the end of the level term period needs to be tested against a random number. If the random number comes in less than the mortality factor, the policy has a death in that year. If not, the policy lives and we test the next duration. I am trying to run each policy for 10,000 simulations, though I could potentially reduce that number depending on how results converge.
I have previously posted a similar question here and was provided a solution using Generate Rows. This solution solved my problem at the time and could be used here again as well. However, I am running a large number of policies through the simulation. Each policy, for each duration, for 10,000 scenarios...that's a lot of rows and I wouldn't be able to run this particular block through the same flow in a timely manner.
To combat this, I designed my current flow to have one record for each policy number, with 30 durations worth of mortality factors as fields in my data. I would like to be able to run an iterative macro for 30 iterations, with each iteration testing the current mortality factor, [currentmort] in the workflow, against a random number. For the first iteration, [currentmort] should equal . The macro would determine which records are deaths in the first duration and output those. For records that live, the current duration would increment up by one and [currentmort] would update to field . And so forth, until the 30 iterations are done. I could then setup some sort of macro to run this flow 10,000 times.
I've attached my current macro workflow, which is incomplete, but a decent start. When set up this way, I think the core of my trouble comes down to being able to dynamically swap values in a column. To make [currentmort] = the field heading for whichever duration I'm testing. But I'm open to better ways to solve this problem. I'm not sure if this is the best way to attack this...just trying different approaches than the generate rows solution, which probably won't work in this case.
Here's an example of what one record of data currently looks like:
I'm also including my current non-macro workflow and a few records of data. This flow shows the output I desire. Each policy number will have one record per simulation. I'm open to any approach that gets me to something that looks like this.