Monte Carlo Simulation for index random returns

Hey people,


I want to run a Monte Carlo simulation with the following aim: see the impact of missing a certain number of random days in a market index over 20 years.
I have the daily returns over that period handy and would like the simulation 10,000 times with missing 5,10,15,20,25... random days and going into a fixed deposit instead during the missed days. Then finding out what my average return and risk would be compared to not missing those days.
I am new to trying a Monte Carlo simulation in excel and just did a tutorial on doing a dice simulation. That was easy enough, but didn't help too much with my above problem.


So 20 years is 5027 rows (weekends are excluded). see table so the first column contains the dates (each day on the index), the second column is the closing price of the index, the third column is the gain or loss for the days (based on today and yesterday's ending value) and the 4th column is a simple start at 100 and add each day's gain/loss.
so i need that exact table (which goes until Aug 2017 which is 5027 rows) with random days left out and then seeing what the final value is in the 4th column when a set number of days are randomly removed. So then i can compare the average ending value when 5 random days are removed, simulated over 10000 times, with the ending value when no days are removed.


Here is the study i'm trying to duplicate for context pages 11 and 16 explain the results and a bit of the methodology respectively. I am using days, instead of months as in the above, due to the index i'm looking at not having that long track record. rest is identical to the above.


Any help would be amazing, also let me know if it's too much of a project to tackle.

This is a cross post, here is another place it's posted

Divide and conquer, is what I like to do. I am not sure I understand exactly what you want to do, so I may get some of these steps wrong:

1) Start with daily returns
2) Select your random dates using your favorite "choose several random integers without repeats" algorithm (if you don't have one, this is a common question on these forums and elsewhere, so we should be able to help develop this algorithm if needed).
3) Make a "copy" of the daily returns with those random dates changed as appropriate (I am not knowledgeable enough in financial/investment calculations to know exactly what this calculation should look like. I assume that you do.)
4) Compute risk/return based on the new copy of the daily returns. Again, I don't know what this calculation should look like, but I am assuming that you do.
5a) Repeat 10k times. On the rare occasion that I do Monte Carlo type simulations, I try to arrange my 2 through 4 calculations in a single row/column and pay careful attention to relative and absolute references. If I do so well, then I can perform 10k calculations by simply copying that row/column 10k times. This is illustrated in this example that I put together (https://www.excelforum.com/tips-and-...l-problem.html ). In this example, you can see that I put all of the data and calculations needed for a single "play" of the game in one row. Then, to get 10k (or whatever) plays I need, I simply make that many copies of the initial row.
6) Additional formulas as needed to summarize the results of the 10k copies of the simulation.

With the process broken down into steps like that, what parts of this do you need help with?

It would be easy enough to do, but would just require the data and a few hours of programming. We have a commercial services forum where you could pay to get help. (I don't post there.)

I am not sure I understand what you are doing. The formula you give for column C C3=C2*(1+B2) seems simple enough to copy down. Can you give more detail. Exactly what about column C are you having difficulty with? Is that formula incorrect for each iteration? Or is it more to do with a "summary statistic" of column C?

I glanced through the report, but did not attempt to read it thoroughly. Nothing in the Methodology or Random timers sections jumped out at me as a detailed description of how to replicate the calculations. It appears to be more of a summary report of the findings without the detail needed to fully replicate the analysis. Perhaps the authors of the report are assuming that any reader who wants to replicate the analysis already knows how to replicate the analysis? I cannot see how the 5 random numbers feed into the Growth and Return columns.

@MrS: You strike the selected days or months and see how it affects returns. They give examples of how you do if you were out of the market on the N worst periods.

My ignorance is showing, I know, but I guess I am not sure what it means to "strike" a day or "make a day disappear" from the record. I could guess, but I assume you know what that means. So it looks like we are at step 3 -- make a copy of the daily data (make a copy of columns A:C) with the the chosen random days removed.

As I suggested, I would try to get this done in either rows or columns. Right now, it looks like your source data is in columns, but your random numbers are in rows. Probably the first thing I would do would be to transpose one of those -- Either transpose A:C so that the raw data is arranged in the first three rows of the sheet, or transpose E:J So that the random numbers for each simulation are in columns. (My first choice would probably be to transpose A:C, if it matters). Move the E:J data into the first 6 columns, so the source data starts in H, and the simulations start in row 5.

3) With the source data in rows across, then you would need a formula to make a copy of the data with the appropriate data stricken. Like I say, I am not exactly sure what that looks like, but it is probably a simple if function to determine if the day in row 1 is one of the random numbers in columns B:F. A MATCH() function might work. If the MATCH() function returns N/A, then the day is not one of the random numbers. If the day returns something, then that day should be stricken. Maybe something like =IF(ISNA(MATCH(H$1,$B5:$F5,0)),H$2,what does it mean to remove) [note the mix of relative and absolute references so that the calculation should copy easily. Then make a copy of the column C (row 3) calculation (I would move it off to the right into those empty columns so it fits on the same row). Or maybe you don't need to make a copy of column B, maybe it will be sufficient to make a copy of column C (now row 3). =IF(ISNA(MATCH(...),current daily return calculation,placeholder for not performing calculation as if this day did not exist). Then copy row 5 down.

Others would probably do it differently (shg indicates that he might use a VBA loop to make those copies of columns B and C with days stricken, and then summarize the results of 10k loops). I don't know which is better. That is the spreadsheet programming structure I would probably use. One still needs to fill in those parts of the IF() function, but it should be fairly straightforward once those are known.