Anon9001 wrote: ↑Wed Dec 07, 2022 11:48 am
abc132 wrote: ↑Tue Dec 06, 2022 1:50 pm
Using variable length sequences of years will produce data that are worse and better than historical. The results will include cases that are too negative and too positive compared to what is realistic (some will have 10 negative years in a row). I think having worse sequences is covered by this method.

I think the chains of sequences produce enough new independent sequences to not be over-fit to past sequences. Your results are always going to be a function of the mean that you use, so you just have to decide what range of means you think is reasonable to consider.

You can take what you agree or disagree with here to form a model you believe will give useful results.

Interesting so I am thinking of doing what you are suggesting now that I know that even if I were to use blocks of Annual Returns I would still get worse and better outcomes than Historical but I am not sure of how to randomize Historical Returns with a 85% chance of grabbing Next Year’s Historical Return in Excel. Could you by chance link your spreadsheet so I can find out how to do what you are suggesting?

Also I would like to clarify that this MCS I made is for a Accumulator who is having a 30 Year Time Horizon and that I am interested in look at the Ending Portfolio Values from all the Percentiles not just the 10th Percentile. Sorry for the confusion I should have clarified this better.

Thanks For Your Help,

Anon9001.

I'm glad my comments were considered helpful.

For convenience I use VBA which is a programming language included with Excel. In Excel you can go to View and then Macros and select Create. If you can't see the Macros option you may need to google "how to enable macros Excel". My Excel file has columns for the year, stock, bond, and inflation values for each year. I found these online but you could also grab the "Simba spreadsheet" Excel values. The VBA program reads in the values from the Excel sheet to load the stocks, bond, and inflation values into arrays. The starting year is determined from a random integer, and then a random number and an IF statement give the 85% chance of grabbing the next year.

The tools are grabbing values from Excel cells, using if statements, loops, generating random numbers, and returning calculated values to cells in the Excel spreadsheet. VBA will assume a variable type so you probably want to declare every variable at the beginning of the code as either integer or double.

Examples of declaring variables. The first row has integers. The second row has SP500 which is an array of 100 double precision numbers. The third row has BondPercent with is a single value that is double precision.

Sub RetireMonteCarlo()

Dim i, y, z, YearsToDeath, YearsReported, YearsToRetire, Goalreached, Row As Long

Dim SP500Rate(100) As Double

Dim BondPercent As Double

End Sub

*Here is another snippet of code that comes after declaring the variables. These variables would also all be declared at the beginning like the examples above. A single quote is followed by comments that that are ignored. The Cells command is how you grab values from Excel or put values in Excel cells. The Cells commands are putting my code values and placing them in Excel cells - saving the values I used to perform a run into Excel so I can see the results and the values used to get those results. I change the value of row in the code if I want to keep my results from the prior run. *

Row = 6 'This is where data will be stored in the excel file - be careful not to overwrite data'

correlation = 85# '85% chance performance will follow prior year, 15% random year picked'

Runs = 1000000

Cells(Row, 18) = Goal

Cells(Row, 19) = Runs

Cells(Row, 20) = Penalty

Cells(Row, 21) = Age

Cells(Row, 22) = correlation

*The For loop is grabbing values from Excel. The data starts in wow 4 of Excel. Excel uses (row, column) for 2D arrays. *
For i = 4 To 93

SP500Rate(i - 3) = -1 * Penalty + ((Cells(i, 4) + Cells(i, 5)) / Cells((i - 1), 4) - 1) 'PENALTY worse than historical

DividendRate(i - 3) = Cells(i, 6)

BondRate(i - 3) = Cells(i, 7) - 1 * Penalty '7 is treasury rate 8 is bond performance

CPIRate(i - 3) = Cells(i, 9)

Next i

*This is the code for a random number. Y is the value I will be grabbing from the arrays of data for each year. *

z = Int(100 * Rnd + 0.5)

If (z <= correlation) Then

y = y + 1

If (y > 90) Then y = 1

Else

y = Int(90 * Rnd + 0.5)

End If