Running 100 Iterations of a Random Generator using a Formula

[matthewlouis]

Before I post the spreadsheet I have already built using the XL2BB posting process, I would like to know if what I want is even possible. The generator using 51 years of stock and bond market data and monthly inflation (CPI). A monthly income is deducted from the account balance, then the remaining funds get the historical random return. Then you start over in month 2, adjust the monthly income by the random CPI, deduct it from the Month 1 ending balance, add the monthly random return for month 2, and so on . . . Of course, I can hit F9 and it recalculates to show me the results. What I want to do run 100 iterations at one time and summarize each of the results in a table showing how many times the money ran out between age 60 and 70, 71-74, 75-79, 80-84, 85-89, 90-94, 95-100, and the last one is never run out.

I have never learned to code with VBA, strictly a formula guy. Is this even possible using formulas? If it is, I can upload the spreadsheet in XL2BB.

Thanks in advance!

 

[AhoyNC]

Maybe this link will be of help.

 

[Eric W]

If you can set up your scenario with formulas, you can use Data Tables. See here:

Data Tables in Excel

Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table in Excel.

www.excel-easy.com

Based on what you've said, you'd want to create a table with 480 rows (years 60-100 times 12 months). Put your starting amount in the first row. Set up the formula in each row to subtract out your monthly amount, then look up the return for that month and apply that to the remainder. Each successive row would use the previous row as a starting point. Then you'd set up another formula to find where the amount goes below 0. Then the data tables tool would run the formulas for various monthly amounts and compile a table.

 

[matthewlouis]

Fantastic! Yes, I have already set up the spreadsheet I just didn't how to run the 1000 simulations and compile. The video is very helpful! Thank you for the link, Eric W.!

 

[matthewlouis]

Eric, I reproduced the exact spreadsheet on the video. However, my sheet gets to the 1000 iterations forumula using the data table, I put in in, and I ponly get changes in the $$ in the Simulation Iteration column from Years 1 to 4. But starting year 5, it's the same number all the way to 1000. (?) Is there a setting in excel for iterations I need to change? I currently have 100 in the File > Options > Formulas > Iterations.

Thanks in advance!

 

[matthewlouis]

Never mind, fixed it!

 

[smozgur]

Never mind, fixed it!

@matthewlouis - Please mark the post that answered your question instead of your feedback/comment post.
Your last post doesn't contain an answer, so, if you have an answer then please post it, and then you can mark it as a solution as well. This way, it will help future readers to get the solution faster. Thanks.

 

[matthewlouis]

Got it, thanks!

 

[smozgur]

Got it, thanks!

And you marked your own comment post as the solution again. So I unmarked it, once again.

Let me explain. Post #2 and post #3 might have helped you. In that case, select one of those, the one which helped most.
Or, you might have found your own solution. If this is the case, then post your own solution and then mark that post.