Help with my Monte Carlo Simulation which is using Historical Monthly Real Returns.

[Anon9001]

So in my free-time I made a MCS in Excel (This MCS is for Accumulator who is investing a constant amount Annually for 30 Years) which is randomizing Historical Monthly Real Returns of NASDAQ INR, MSCI ACWI INR, MSCI USA INR, Sensex TRI and Gold INR going back to October 1985 to create future returns to be used in the simulation but I want to lower these historical real returns to match current valuations of these assets as I feel the historical returns of these assets especially NASDAQ, MSCI USA and Sensex are too high. I am thinking 1/CAPE is okay indicator of expected future return of these assets except Gold (with a caveat being that sector and accounting differences between different countries stock markets could make 1/CAPE into a noisy indicator of expected return). For Gold I will assume 0% real return as it is the common assumption that Gold will return 0% after inflation long term.

This 1/CAPE will be the AAR (Average Annual Return) of all these assets except Gold. For Gold the AAR will be 0%. Seeing as I am having only Monthly Real Returns of these assets I compound these Monthly Real Returns to create Annual Real Returns of these assets and after doing so I reduce the Monthly Real Returns of these assets such that the AAR of all these assets is equal to 1/CAPE (For Gold AAR will be 0% again). I am wondering whether what I am doing is the correct way to adjust historical returns of these assets to match current valuations of these assets or am I wrong?

I got this idea of reducing historical returns of assets to match current valuations from Ben Mathew's TPAW Spreadsheet but his MCS is randomizing Annual Real Returns of SP 500/US Bond Index going back to 1871 to create future returns to be used in the simulation whereas my MCS is randomizing Monthly Real Returns of these assets going back to October 1985 so I am wondering whether his method is still applicable.

Here is a download link to the MCS I created in Excel in case person is interested.

Thanks,
Anon9001.

[Shallowpockets]

" but I want to lower these historical real returns to match current valuations of these assets as I feel the historical returns of these assets"

Well, there you go. You want to change input data because you feel. Is that scientific? Every time MC scenario comes up people dis it according to the vagaries of the data the scenario may be based on even though they do not know the specifics of that data. So the math whizzes may create their own MC spreadsheet, which seems to also be fraught with data that is altered according to things such as feelings.
How can this be any better than a MC scenario such as Fidelity?

[Anon9001]

" but I want to lower these historical real returns to match current valuations of these assets as I feel the historical returns of these assets"

Well, there you go. You want to change input data because you feel. Is that scientific? Every time MC scenario comes up people dis it according to the vagaries of the data the scenario may be based on even though they do not know the specifics of that data. So the math whizzes may create their own MC spreadsheet, which seems to also be fraught with data that is altered according to things such as feelings.
How can this be any better than a MC scenario such as Fidelity?

I dont know about the Fidelity's MCS but considering they are US company I dont think their MCS is suitable for me considering I am Indian Investor. What I am concerned with using Historical Returns is that NASDAQ portfolios are having higher Ending Portfolio Balance than MSCI ACWI, MSCI USA and Sensex Portfolios in all Percentiles (10th-90th Percentiles) and I believe its due to the very high returns that NASDAQ gave during the Dot-Com Bubble years which I dont think will happen again as in those times NASDAQ companies were much more speculative, smaller in size than they are now.

[rkhusky]

I dont know about the Fidelity's MCS but considering they are US company I dont think their MCS is suitable for me considering I am Indian Investor. What I am concerned with using Historical Returns is that NASDAQ portfolios are having higher Ending Portfolio Balance than MSCI ACWI, MSCI USA and Sensex Portfolios in all Percentiles (10th-90th Percentiles) and I believe its due to the very high returns that NASDAQ gave during the Dot-Com Bubble years which I dont think will happen again as in those times NASDAQ companies were much more speculative, smaller in size than they are now.

MC is supposed to sample all possibilities with a given probability. How are you calculating the future probability of the NASDAQ having high returns? You have to be careful of constructing the MC to give the answer you want to hear.

[Anon9001]

MC is supposed to sample all possibilities with a given probability. How are you calculating the future probability of the NASDAQ having high returns? You have to be careful of constructing the MC to give the answer you want hear.

I personally believe Nasdaq and the Broad US Index will have similar returns due to the fact that the companies the NASDAQ is highly weighted to currently is also highly weighted in the Broad US Index although to a lesser extent. The Expected Return I am inputting for NASDAQ though is 2.8% compared to 3.8% for MSCI USA and thats due to the fact that CAPE for Nasdaq is higher than CAPE for Broad US Index.

[rkhusky]

MC is supposed to sample all possibilities with a given probability. How are you calculating the future probability of the NASDAQ having high returns? You have to be careful of constructing the MC to give the answer you want hear.

I personally believe Nasdaq and the Broad US Index will have similar returns due to the fact that the companies the NASDAQ is highly weighted to currently is also highly weighted in the Broad US Index although to a lesser extent. The Expected Return I am inputting for NASDAQ though is 2.8% compared to 3.8% for MSCI USA and thats due to the fact that CAPE for Nasdaq is higher than CAPE for broad US Index.

If you are just inputting your own expectations, you will just get your own expectations as output.

Either you use historical data or you don't. What's the probability of another Great Depression or Great Recession or Covid pandemic or world war? You either compute those probabilities or you just sample the historical record as it is.

[Anon9001]

If you are just inputting your own expectations, you will just get your own expectations as output.

Either you use historical data or you don't. What's the probability of another Great Depression or Great Recession or Covid pandemic or world war? You either compute those probabilities or you just sample the historical record as it is.

The thing is I am using historical monthly real returns of these assets. I am just reducing them to match current valuations of these assets. I believe this approach is much better than other MCS methods which are assuming a distribution like Normal/Log-Normal distribution and randomly picking returns from that distribution which would be invalid if the assets returns dont follow normal/log-normal distribution which would be the case with monthly returns. With Annual Returns the assumption is much more realistic but the sample size of annual returns for markets other than US tend to be low.

[glorat]

If you are just inputting your own expectations, you will just get your own expectations as output.

Either you use historical data or you don't. What's the probability of another Great Depression or Great Recession or Covid pandemic or world war? You either compute those probabilities or you just sample the historical record as it is.

The thing is I am using historical monthly real returns of these assets. I am just reducing them to match current valuations of these assets. I believe this approach is much better than other MCS methods which are assuming a distribution like Normal/Log-Normal distribution and randomly picking returns from that distribution which would be invalid if the assets returns dont follow normal/log-normal distribution which would be the case with monthly returns. With Annual Returns the assumption is much more realistic but the sample size of annual returns for markets other than US tend to be low.

So you're trying to use "historic monthly real returns of assets" to generate your monte carlo because you don't believe randomly picking from a normal/log-normal. Translation: You're trying to use past performance to indicate future performance of some asset. That's not only not the Boglehead way, not even Wall Street institutions use such a model.

Best practice is still to use some stochastic model - log-normal may or may not be best but at least it is stochastic and isn't relying on past performance in a dangerous way. If you want to improve, I suggest looking for a better model but going with annual returns seems counter to all the literature and experience out there.

[Anon9001]

So you're trying to use "historic monthly real returns of assets" to generate your monte carlo because you don't believe randomly picking from a normal/log-normal. Translation: You're trying to use past performance to indicate future performance of some asset. That's not only not the Boglehead way, not even Wall Street institutions use such a model.

Best practice is still to use some stochastic model - log-normal may or may not be best but at least it is stochastic and isn't relying on past performance in a dangerous way. If you want to improve, I suggest looking for a better model but going with annual returns seems counter to all the literature and experience out there.

I don’t think you understand what I am doing here. What I am doing with my MCS is randomizing Historical Monthly Real Returns which are adjusted to reflect current valuations of these assets. The main benefits of randomizing Historical Monthly Real Returns over other Monte Carlo Simulation methods is that you don’t have to assume a particular distribution of returns like Normal/Log-Normal and you can preserve the historical correlation between assets. The biggest drawback though is that if certain assets have high historical returns which are not likely to repeat in future like Bonds, NASDAQ, US Equities then the MCS which is randomizing historical returns will suggest these assets are optimal. So what I do to fix this issue is I adjust the historical returns to reflect current valuations of these assets.

The method I use to do this is I compound these Monthly Real Returns to create Annual Real Returns, then I calculate Arithmetic Mean of these Annual Real Returns (or AAR for short). This AAR is what I refer to as “Expected Return”. After doing this I reduce the Monthly Real Returns such that the AAR of all the assets except Gold is equal to 1/CAPE (For Gold specifically the AAR will be close to 0% in this case it is equal to 0.5%). The MCS is then randomizing these Valuation Adjusted Monthly Real Returns to create monthly returns to be used in the simulation.

Annual Returns are not used in the simulation. The only reason I am having these Annual Returns in the spreadsheet is to calculate the AAR of all these assets.

[glorat]

I may have originally misunderstood your purpose but you lost me in the subsequent explanation when you suggest your MCS will have "Returns ... adjusted to reflect current valuations of these assets". I don't think I've misunderstood you the second time. "Valuation Adjusted Monthly Real Returns to create monthly returns". You're using valuations to project returns. I don't think I need to repeat the dangers in doing that.

[Anon9001]

I may have originally misunderstood your purpose but you lost me in the subsequent explanation when you suggest your MCS will have "Returns ... adjusted to reflect current valuations of these assets". I don't think I've misunderstood you the second time. "Valuation Adjusted Monthly Real Returns to create monthly returns". You're using valuations to project returns. I don't think I need to repeat the dangers in doing that.

Lets go back to basics. What is 1/CAPE? Its the inverse of CAPE which means its 10 Year Earnings Yield. Using this as long term expected return of stocks is probably not bad idea due to this. Notice also that even CAPE of 100 is indicating 1% Real Return if you use 1/CAPE. This is because 1/CAPE assumes valuations stay constant hence positive real returns even at high CAPE's. If you could offer alternatives which are better which I can use instead as expected return of stocks I would use them but they are not there.

Whether you are randomizing historical returns for MCS or assuming a particular returns distribution like normal/log-normal and randomly picking returns from that dostributiom you still have to make assumption about expected return of assets you are comparing. You cant avoid making this assumption so the question then becomes are there other methods better 1/CAPE which can be used to get future expected return of stocks? I dont think so.

[abc132]

The main question is what is the purpose?

I think you would be better-off just testing every historical sequence of say 360 months. Using monthly data as the basis for Monte Carlo doesn't add anything vs annual data other than unrealistic standard deviation. It's the same reason daily data is not used.

If you can state what you hope to accomplish with monthly data that you can't get from annual data then I think you might get some better suggestions. Also why is a 1/CAPE reversion to mean better than the actual reversion to mean from using actual monthly data?

You could try annual vs monthly vs daily just to see what you are doing by selecting monthly as the basis for your data. There are good reasons people use annual data.

[glorat]

What is 1/CAPE? Its the inverse of CAPE which means its 10 Year Earnings Yield. Using this as long term expected return of stocks is probably not bad idea due to this.

Well FWIW, I think this is a bad idea.

Whether you are randomizing historical returns for MCS or assuming a particular returns distribution like normal/log-normal and randomly picking returns from that dostributiom you still have to make assumption about expected return of assets you are comparing.

Correct

You cant avoid making this assumption so the question then becomes are there other methods better 1/CAPE which can be used to get future expected return of stocks? I dont think so.

I would rather assume all assets have the same future expected return. How you pick that one number depends on what you're trying to achieve. What I would not do is assume different assets have different expected future returns based on historic data. (The most I might stretch to is assuming same future risk adjusted return, by some definition of risk)

[Raffert]

I get what you're doing. At least I think I do. But you're not doing a Monte Care simulation.

You're doing a simulation, for sure - just not a proper monte carlo simulation in the normal sense of the term. That's not a criticism of your simulation... it might make sense for you, and it might be useful to you. But just in the interest of being precise with terminology, it doesn't seem like that's a monte carlo sim. Instead it seem likes you're randomly selecting historical results, adjusting them to reflect your outlook, and using them as a sim going forward. That's not a bad thing, and it can be super useful.

It's just worthwhile to recognize that you're missing things with this method - specifically outcomes outside of historical record of returns. Tail outcomes can be huge drivers in stochastic sims and if they're not sampled in your history, they'll be missed with this method. To be clear - this is not a criticism, just something to be aware of. The benefit of a proper, appropriately designed monte carlo sim (the holy grail!) is that these tail outcomes are accounted for, along with more problematic things like mean reversion, momentum, etc. To be clear - I can't make such a monte carlo sim - but I can imagine what one would look like?

But maybe I'm mis-understanding the spreadsheet. I'm sure you know how confusing it is to decipher another persons' spreadsheet! If I'm missing something dumb, please let me know... it would not be the first time.

Raffert

[Anon9001]

The main question is what is the purpose?

I think you would be better-off just testing every historical sequence of say 360 months. Using monthly data as the basis for Monte Carlo doesn't add anything vs annual data other than unrealistic standard deviation. It's the same reason daily data is not used.

If you can state what you hope to accomplish with monthly data that you can't get from annual data then I think you might get some better suggestions. Also why is a 1/CAPE reversion to mean better than the actual reversion to mean from using actual monthly data?

You could try annual vs monthly vs daily just to see what you are doing by selecting monthly as the basis for your data. There are good reasons people use annual data.

Main purpose is pretty simple. I want to compare portfolios of these assets and see how well they do from 10th-90th Percentiles. If one of these portfolios does better than the others in these percentiles I would think that is good investment.

I believe 360 months is a typo right? You meant 36 months right? The issue with randomizing historical annual returns instead of monthly returns is the number of indepedent samples of Annual Returns I have from Oct 1985-2022 is 37 whereas the number of indepedent samples of monthly returns I am having from the exact same timeframe is 445. I am not sure why you are stating annual returns are way to go considering this issue of indepedent samples?

Also I am not sure what you mean by "reversion to mean"? 1/CAPE only assumes earnings growth of 0% and that the valuation of the asset stays constant. It does not require mean reversion hence why even inverse of CAPE of 100 indicates 1% real return. I am not sure also what you mean by actual reversion of mean from the monthly data.

I would advice opening the spreadsheet I feel most of the misunderstandings here is coming from people not checking the spreadsheet I linked. The screenshots I linked dont show the full spreadsheet.

[abc132]

The main question is what is the purpose?

I think you would be better-off just testing every historical sequence of say 360 months. Using monthly data as the basis for Monte Carlo doesn't add anything vs annual data other than unrealistic standard deviation. It's the same reason daily data is not used.

If you can state what you hope to accomplish with monthly data that you can't get from annual data then I think you might get some better suggestions. Also why is a 1/CAPE reversion to mean better than the actual reversion to mean from using actual monthly data?

You could try annual vs monthly vs daily just to see what you are doing by selecting monthly as the basis for your data. There are good reasons people use annual data.

Main purpose is pretty simple. I want to compare portfolios of these assets and see how well they do from 10th-90th Percentiles. If one of these portfolios does better than the others in these percentiles I would think that is good investment.

I believe 360 months is a typo right? You meant 36 months right? The issue with randomizing historical annual returns instead of monthly returns is the number of indepedent samples of Annual Returns I have from Oct 1985-2022 is 37 whereas the number of indepedent samples of monthly returns I am having from the exact same timeframe is 445. I am not sure why you are stating annual returns are way to go considering this issue of indepedent samples?

Also I am not sure what you mean by "reversion to mean"? 1/CAPE only assumes earnings growth of 0% and that the valuation of the asset stays constant. It does not require mean reversion hence why even inverse of CAPE of 100 indicates 1% real return. I am not sure also what you mean by actual reversion of mean from the monthly data.

I would advice opening the spreadsheet I feel most of the misunderstandings here is coming from people not checking the spreadsheet I linked. The screenshots I linked dont show the full spreadsheet.

I'll refer you to using daily or hourly data. You are not getting additional independent data by fragmenting it beyond annual data. 360 months is for a 30 year analysis which is typical for these types of simulations.

[Walkure]

Also I am not sure what you mean by "reversion to mean"? 1/CAPE only assumes earnings growth of 0% and that the valuation of the asset stays constant. It does not require mean reversion hence why even inverse of CAPE of 100 indicates 1% real return. I am not sure also what you mean by actual reversion of mean from the monthly data.

I wouldn't trust 1/CAPE for anything, but you do you. However, I think there is a big issue lurking in your assumptions. Let's say you start your MCS in Year n, with an index value of 100 and a CAPE of 30. You compound those monthly returns and get an annual return for the Year n+1 in one iteration of the MCS, and let's say it's a return of 6% (nominal? real?). So now in Year n+1 you have an index value of 106. If you maintain the assumption of no earnings growth (nominal? real?), CAPE is now up to something higher than 30. If you instead hold valuation constant, then you are actually assuming that earnings growth will match the realized return exactly in each subsequent year of the simulation.

In one case CAPE spins off toward infinity and in the other you're just using historical monthly data to predict future earnings growth.

[Anon9001]

I wouldn't trust 1/CAPE for anything, but you do you. However, I think there is a big issue lurking in your assumptions. Let's say you start your MCS in Year n, with an index value of 100 and a CAPE of 30. You compound those monthly returns and get an annual return for the Year n+1 in one iteration of the MCS, and let's say it's a return of 6% (nominal? real?). So now in Year n+1 you have an index value of 106. If you maintain the assumption of no earnings growth (nominal? real?), CAPE is now up to something higher than 30. If you instead hold valuation constant, then you are actually assuming that earnings growth will match the realized return exactly in each subsequent year of the simulation.

In one case CAPE spins off toward infinity and in the other you're just using historical monthly data to predict future earnings growth.

I dont think you are understanding this. This 1/CAPE is only used as Expected Return (or Average Annual Return) of Stocks in this Simulation. I reduce the historical monthly real returns such that the AAR is Equal to 1/CAPE.

I think I understand the misunderstanding here. When I stated earlier 1/CAPE is assuming valuation stays constant and earnings growth is 0% I was referring to using it as a expected long term return of equities. If earnings growth is not 0% and valuations dont stay constant the future long term return of equities can differ from 1/CAPE. You seem to think I was referring to 1/CAPE's assumption in relation to monthly/annual returns of the assets I am comparing which is not the case.

[Anon9001]

I'll refer you to using daily or hourly data. You are not getting additional independent data by fragmenting it beyond annual data.

I am not sure exactly what you mean by this? The Lag 1 AutoCorrelation of the Historical Monthly Real Returns I have ranges from -0.11 to 0.08 for all these assets I am comparing which means basically that the past 1 month return of these assets does not influence the next month return of these assets. For reference for the Historical Annual Real Returns of the same assets the Lag 1 Autocorrelation ranges from -0.22 to 0.3 which means that the past 1 year return of these assets does not influence (or weakly influence in the case of 0.3 autocorrelation) next 1 year return of these assets.

I think what you are saying only makes sense if I were comparing rolling returns of these assets as the number of indepedent samples does not increase when you go from yearly to monthly and then to daily returns due to usage of overlapping periods when constructing rolling returns.

[abc132]

I'll refer you to using daily or hourly data. You are not getting additional independent data by fragmenting it beyond annual data.

I am not sure exactly what you mean by this? The Lag 1 AutoCorrelation of the Historical Monthly Real Returns I have ranges from -0.11 to 0.08 for all these assets I am comparing which means basically that the past 1 month return of these assets does not influence the next month return of these assets. For reference for the Historical Annual Real Returns of the same assets the Lag 1 Autocorrelation ranges from -0.22 to 0.3 which means that the past 1 year return of these assets does not influence (or weakly influence in the case of 0.3 autocorrelation) next 1 year return of these assets.

I think what you are saying only makes sense if I were comparing rolling returns of these assets as the number of independent samples does not increase when you go from yearly to monthly and then to daily returns due to usage of overlapping periods when constructing rolling returns.

Daily data is essentially random - half of the days will be up and half will be down. If we used daily data we would get many more sequences of negative or positive years. 1/8th of the time we would get 3 negative years in a row, 1/16th 4 negative in a row, 1/32 5 in a row, and 1/64th 6 negative years in a row. Yet historical data over 100 years only has 3 negatives in a row. This suggests the year to year results are not random.

If we go to monthly data we have the same issue - many more sequences of negative (and positive) annual periods in your model. Even annual data will have this issue, but at least 2/3 of years will be positive. What you want to do is use annual data and grab blocks of annual returns to best make predictions. What I do is
1) start with a random year's data (stock, bonds, inflation).
2) update your portfolio value according to that year's returns
3) use an 85% chance of grabbing the next year of historical data

Step 3 will grab 3 year chains on average and will help limit the odds of too many negative years.
4) correct this data set to CAPE if you wish

You may as well use random (mean, standard deviation) data with the method you are using. Historical monthly values are not capturing anything. You would be much better off with a data set that does not produce sequences of overly negative or positive results, which is what steps 1-3 above will help to do (but not completely fix compared to historical data). You can set the chance to 100% in step 3 to also test vs historical data.

[Anon9001]

You may as well use random (mean, standard deviation) data with the method you are using. Historical monthly values are not capturing anything. You would be much better off with a data set that does not produce sequences of overly negative or positive results, which is what steps 1-3 above will help to do (but not completely fix compared to historical data). You can set the chance to 100% in step 3 to also test vs historical data.

Thanks for replying! I finally understand now what you are saying. You are suggesting this because there is evidence that past 3-5 year returns of asset has negative autocorrelation with future 3-5 year returns of asset so if I were to randomize monthly returns data I would have this "problem" of ignoring this mean reversion and to a lesser extent if I was randomizing annual returns data. This is a valid concern but I have issues with looking at historical data for this. The main issue I am having is that the only market which is having 100 years of data is US market and I am wondering whether its a good idea to generalize this to other markets like Sensex, MSCI ACWI (although due to the high US weightage its probably not a bad assumption).

Also another issue I am having is you are suggesting me to randomize annual returns of which I only have 37 samples of so I am not sure this is a good idea due to this. What if my sample size of annual returns is too small to capture the worst and best outcomes of the asset? That is a valid concern I am having and I am not sure that using annual returns is superior to monthly returns due to this smaller sample size even though it will be more "realistic".

[abc132]

You may as well use random (mean, standard deviation) data with the method you are using. Historical monthly values are not capturing anything. You would be much better off with a data set that does not produce sequences of overly negative or positive results, which is what steps 1-3 above will help to do (but not completely fix compared to historical data). You can set the chance to 100% in step 3 to also test vs historical data.

Thanks for replying! I finally understand now what you are saying. You are suggesting this because there is evidence that past 3-5 year returns of asset has negative autocorrelation with future 3-5 year returns of asset so if I were to randomize monthly returns data I would have this "problem" of ignoring this mean reversion and to a lesser extent if I was randomizing annual returns data. This is a valid concern but I have issues with looking at historical data for this. The main issue I am having is that the only market which is having 100 years of data is US market and I am wondering whether its a good idea to generalize this to other markets like Sensex, MSCI ACWI (although due to the high US weightage its probably not a bad assumption).

Also another issue I am having is you are suggesting me to randomize annual returns of which I only have 37 samples of so I am not sure this is a good idea due to this. What if my sample size of annual returns is too small to capture the worst and best outcomes of the asset? That is a valid concern I am having and I am not sure that using annual returns is superior to monthly returns due to this smaller sample size even though it will be more "realistic".

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.

If you are evaluating the 10% percentile (worst case) the answer for say safe withdrawal rate will be more pessimistic (lower) than the historical value. From there I suggest you play around with the model parameters and look at the range of outcomes rather than trying to decide on specific returns. I do this by taking historical stocks and bonds -1% (or -2%, -3%) for each year. That is to see what happens if stocks and bonds return less than historical on average.

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. Your CAPE based correction is interesting but I'm lukewarm on whether that adds anything if you are just looking at the 10th percentile results. If you were evaluating average net worth I would consider the CAPE correction as useful. Net worth calculations benefit from filtering out of the unrealistically good/bad sequences. One could consider only the 10th to 80th percentile results for average net worth or they could do some CAPE sort of correction to help correct these unrealistic sequences.

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

[Anon9001]

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.

[abc132]

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

[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.

I feel it would better if I could see the spreadsheet and macro myself as I dont think I have full understanding just by looking at it here. Couldn't you upload it to some site?

Thanks and Regards,
Anon9001.

[Anon9001]

I am wondering you said earlier this method will create "3 Year Chains on Average" and I am wondering is this different to just randomizing 3 year returns of these assets? I am not sure whether they are different and if I am correct I might try this simpler method.

[abc132]

I am wondering you said earlier this method will create "3 Year Chains on Average" and I am wondering is this different to just randomizing 3 year returns of these assets? I am not sure whether they are different and if I am correct I might try this simpler method.

It's based on probability. There is a (1-0.85) 15% chance the next year is random, a (1-0.85^2) 28% chance of grabbing 2 years, (1-0.85^3) 39% chance of grabbing 3 year in a row, (1-0.85^4) 48% chance of 4 years in a row. I guess my current version is 4 year chains on average. I think I left it at 4 year average sequences with 3 negative years are also likely to include a positive year. I like the combination of much different sequences (short chains) and similar sequences (long chains). I feel this gives the best combination of a rhymes with historical but may be different than historical.

You can try whatever you like, but some basic tests would be to compare your results vs using purely historical and trying 2 or 4 years instead of 3. My guess is that the 10% percentile outcome it won't matter too much. If you use CAPE correction I think you will get something like historical results regardless of how you construct the pieces.

I'm not posting full codes or code results anymore on this site but I would answer questions if you do try the programming route. Someone reading might have a purely Excel solution. I think it could be solved just with Excel (no VBA) so maybe a google search for Excel Monte Carlo Code.

Best of luck!

[Walkure]

I may have originally misunderstood your purpose but you lost me in the subsequent explanation when you suggest your MCS will have "Returns ... adjusted to reflect current valuations of these assets". I don't think I've misunderstood you the second time. "Valuation Adjusted Monthly Real Returns to create monthly returns". You're using valuations to project returns. I don't think I need to repeat the dangers in doing that.

Lets go back to basics. What is 1/CAPE? Its the inverse of CAPE which means its 10 Year Earnings Yield. Using this as long term expected return of stocks is probably not bad idea due to this. Notice also that even CAPE of 100 is indicating 1% Real Return if you use 1/CAPE. This is because 1/CAPE assumes valuations stay constant hence positive real returns even at high CAPE's. If you could offer alternatives which are better which I can use instead as expected return of stocks I would use them but they are not there.

Whether you are randomizing historical returns for MCS or assuming a particular returns distribution like normal/log-normal and randomly picking returns from that dostributiom you still have to make assumption about expected return of assets you are comparing. You cant avoid making this assumption so the question then becomes are there other methods better 1/CAPE which can be used to get future expected return of stocks? I dont think so.

This gets to my critique. Maybe I can clear up the misunderstanding. As you say, it's the inverse of the 10 Year Earnings Yield. So you rectify all the monthly returns in your historical series so they distribute around this CAPE-adjusted expected return. Great. So now you start your MCS and you run a years' worth of rectified monthly returns. You compound them and generate an annual return for the first year. Still good so far. But here's where it gets tricky. You've simulated a realized return, but you haven't simulated any earnings growth. So now starting year n+1 of the simulation, year n-10's earnings data drops off and year n's is added, generating a new CAPE. You now have to re-rectify all the monthly data using this updated 1/CAPE factor, reflecting the new forward expected return, before generating year n+2's realized return. But you don't have year n's earnings data, because it's just a simulation, and whatever you could put for that is a thumb on the scale.
So you say, we'll just assume that it's the same as the earnings back in n-10 and never change the 1/CAPE factor over the 30 years of each simulation, but now you have decoupled the returns from actual earnings, which defeats the entire purpose of scaling expected return to CAPE in the first place.

[Anon9001]

This gets to my critique. Maybe I can clear up the misunderstanding. As you say, it's the inverse of the 10 Year Earnings Yield. So you rectify all the monthly returns in your historical series so they distribute around this CAPE-adjusted expected return.

It seems like we are talking in different languages so let me state clearly this time. I reduce the historical monthly real returns by a certain percentage such that that the Average Annual Return or "Expected Return" of these assets equal to 1/CAPE rather than their Historical Mean. 1/CAPE will equal to long term return of asset if real earnings growth is 0% and valuations stay constant. If these assumptions are voilated long term return can be higher or lower than 1/CAPE.

I am not sure what exactly you are stating but its clearly not what I am doing. I repeat what I said earlier that I am not making the monthly returns equal to 1/CAPE. I am only reducing them such that their long term expected return (AAR in this case) is equal to 1/CAPE.

Thanks.

[Anon9001]

I feel like you are getting this entirely jumbled because you think of 1/CAPE's assumption of 0% real earnings growth/valuations staying constant as being applicable to short term returns of the asset (monthly/annual returns) when I was referring to using 1/CAPE as Expected Return of Assets. If valuations dont stay constant/earnings growth is greater than 0% the future long term return could be higher or lower than 1/CAPE.

[Anon9001]

It's based on probability. There is a (1-0.85) 15% chance the next year is random, a (1-0.85^2) 28% chance of grabbing 2 years, (1-0.85^3) 39% chance of grabbing 3 year in a row, (1-0.85^4) 48% chance of 4 years in a row. I guess my current version is 4 year chains on average. I think I left it at 4 year average sequences with 3 negative years are also likely to include a positive year. I like the combination of much different sequences (short chains) and similar sequences (long chains). I feel this gives the best combination of a rhymes with historical but may be different than historical.

You can try whatever you like, but some basic tests would be to compare your results vs using purely historical and trying 2 or 4 years instead of 3. My guess is that the 10% percentile outcome it won't matter too much. If you use CAPE correction I think you will get something like historical results regardless of how you construct the pieces.

I'm not posting full codes or code results anymore on this site but I would answer questions if you do try the programming route. Someone reading might have a purely Excel solution. I think it could be solved just with Excel (no VBA) so maybe a google search for Excel Monte Carlo Code.

Best of luck!

I think I will just move to randomizing annual returns and reduce all them by a certain percentage such that the Mean of these Annual Returns is equal to 1/CAPE as this method you are suggesting seems to be too complicated for me to duplicate. Main issue I have with randomizing 3 year returns for me is that I only have 14 indepedent samples of 3 year returns which I dont think is enough to see best and worst outcomes of assets I am comparing so I am using annual returns for that reason.

Thanks.

[abc132]

It's based on probability. There is a (1-0.85) 15% chance the next year is random, a (1-0.85^2) 28% chance of grabbing 2 years, (1-0.85^3) 39% chance of grabbing 3 year in a row, (1-0.85^4) 48% chance of 4 years in a row. I guess my current version is 4 year chains on average. I think I left it at 4 year average sequences with 3 negative years are also likely to include a positive year. I like the combination of much different sequences (short chains) and similar sequences (long chains). I feel this gives the best combination of a rhymes with historical but may be different than historical.

You can try whatever you like, but some basic tests would be to compare your results vs using purely historical and trying 2 or 4 years instead of 3. My guess is that the 10% percentile outcome it won't matter too much. If you use CAPE correction I think you will get something like historical results regardless of how you construct the pieces.

I'm not posting full codes or code results anymore on this site but I would answer questions if you do try the programming route. Someone reading might have a purely Excel solution. I think it could be solved just with Excel (no VBA) so maybe a google search for Excel Monte Carlo Code.

Best of luck!

I think I will just move to randomizing annual returns and reduce all them by a certain percentage such that the Mean of these Annual Returns is equal to 1/CAPE as this method you are suggesting seems to be too complicated for me to duplicate. Main issue I have with randomizing 3 year returns for me is that I only have 14 indepedent samples of 3 year returns which I dont think is enough to see best and worst outcomes of assets I am comparing so I am using annual returns for that reason.

Thanks.

That should be good enough for most decision-making purposes as long as your ignore the edge really bad and really good outcomes. I agree with you that we don't have enough independent data to produce an overly accurate model. Even with more independent data I think we would still be chained to the future not necessarily looking like the past which can quickly defeat someone trying to make too precise of a prediction.

[Anon9001]

That should be good enough for most decision-making purposes as long as your ignore the edge really bad and really good outcomes. I agree with you that we don't have enough independent data to produce an overly accurate model. Even with more independent data I think we would still be chained to the future not necessarily looking like the past which can quickly defeat someone trying to make too precise of a prediction.

I just looked at the AutoCorrelation of Shiller S&P Data going back to 1871 for Annual Returns and for 3-5 Year Returns also and there is essentially no AutoCorrelation even for 3-5 year returns. I am wondering considering this whether your method of essentially randomizing 3-5 year returns is actually a good idea considering there is no AutoCorrelation of returns even for longer duration returns like 3-5 year returns. Earlier I thought past 3-5 year returns had negative autocorrelation with future 3-5 year returns as there was a ancient study by Fama proving such but after looking at the data on my own I cant confirm their conclusions.





EDIT: Fixed error I made earlier with charts.

[abc132]

That should be good enough for most decision-making purposes as long as your ignore the edge really bad and really good outcomes. I agree with you that we don't have enough independent data to produce an overly accurate model. Even with more independent data I think we would still be chained to the future not necessarily looking like the past which can quickly defeat someone trying to make too precise of a prediction.

I just looked at the AutoCorrelation of Shiller S&P Data going back to 1871 for Annual Returns and for 3-5 Year Returns also and there is essentially no AutoCorrelation even for 3-5 year returns. I am wondering considering this whether your method of essentially randomizing 3-5 year returns is actually a good idea considering there is no AutoCorrelation of returns even for longer duration returns like 3-5 year returns. Earlier I thought past 3-5 year returns had negative autocorrelation with future 3-5 year returns as there was a ancient study by Fama proving such but after looking at the data on my own I cant confirm their conclusions.





EDIT: Fixed error I made earlier with charts.

Remember that I also run 100% historical data with full 30-year (or however long I am analyzing) chains. I do this by setting my correlation to 100% instead of 85%. Running a large number of runs (1,000,000+) is a computationally inefficient way to test the ~70 unique historical sequences of 30 years but it works.

I would suggest you start off with purely historical analysis (30 year chain) and investigate whatever interests you. Then you try your model and see how it compares to history for the variables you care about. If it is much different the model is likely no good. If it rhymes with history it may be a decent model. I suggest comparing the average, the 10% worst case, and the SWR from historical runs and from the new model.

As to 3-5 year segments not being predictive over the next 3-5 years, none of the methods (CAPE 10, etc) are predictive over the short term. Even a 10 year chain won't tell you about the next few years. You get to choose between long chains that just repeat historical and figuring out how to get something that rhymes with history but it not just a repeat of history.

If I am super honest we really can't predict the future with any meaningful accuracy. I think 3-5 year chains while ignoring the best 5% and worst 5% of cases could be good enough. In my case I validated the model with correlation = 100% (historical runs) and then tried 95%, 90%, all the way down to 70% correlation. I settled on the correlation that rhymed with history (85% correlation, ~4 year chains). If the average is 4 years then 27% of the chains will be at least 8 years and 14% will be at least 12 years. 39% will be in the 1-3 year range. What you are trying to prevent is getting too many 3 year negative chains from the worst set of years we have. There is only an 11% chance of grabbing a 3-year sequence and imagine 3% of 3-year historical sequences were consecutively negative. Getting two really bad 3 year sequences in a row would be a (11%)*(11%)*(3%)*(3%) = 0.001% chance. The longer chains help protect you from overly negative results because a 4+ year chain always has some positive years. The shorter chains allow the results to rhyme with but not repeat history. Getting the right ratio of long and short chains will give you a pretty good model. That's why I don't use a fixed length chain.

If you settle on a fixed length you could also go longer than my average chain. I would look in the 5-8 year range if the shorter chains bother you. There is justification for more than I am using since you lack the long chains.

[Anon9001]

If you settle on a fixed length you could also go longer than my average chain. I would look in the 5-8 year range if the shorter chains bother you. There is justification for more than I am using since you lack the long chains.

Thanks for replying to my doubt about this but I am wondering about what to do about Bond returns in this simulation. In my domestic market India I have bond returns data going back to March 1998 (10 Year Government Bond Yield from which I create monthly/annual bond returns using Shiller's method. More info:viewtopic.php?t=347365) which is not useful to me because the other assets in my MCS is having returns data going back to April 1979.

I am wondering whether it is good idea to just assume the Annual Real Bond Returns are following a Normal Distribution and hence using the NORM.INV and RAND function (In this function I use the standard deviation of historical real annual bond returns as the standard deviation but for the mean I use the current real 10 year bond yield instead of the historical mean) in Excel to generate Annual Real Bond returns. I found the Excess Kurtosis and Skewness of Historical Real Annual Bond Returns to be both 0.66 but considering the sample size of Real Annual Bond Returns I am having is only 24 I made sure to calculate the Z Score of Excess Kurtosis and Skewness and the Z Score for both is less than 1.96 which is indiciating to me that the normal distribution is a good fit for real annual bond returns but I would like to get clarification on this.

Another downside of this method I can think of is not keeping the historical correlation of domestic bonds with these other assets but I found the Historical Correlation of Domestic Bonds with other assets is near 0 regardless of whether Monthly or Annual Real Returns is used for the calculation of correlation so I assume this method is a good idea right? Am I correct in assuming that using this method I am essentially assuming domestic bonds have 0 correlation with other assets.

Thanks.

[abc132]

If you settle on a fixed length you could also go longer than my average chain. I would look in the 5-8 year range if the shorter chains bother you. There is justification for more than I am using since you lack the long chains.

Thanks for replying to my doubt about this but I am wondering about what to do about Bond returns in this simulation. In my domestic market India I have bond returns data going back to March 1998 (10 Year Government Bond Yield from which I create monthly/annual bond returns using Shiller's method. More info:viewtopic.php?t=347365) which is not useful to me because the other assets in my MCS is having returns data going back to April 1979.

I am wondering whether it is good idea to just assume the Annual Real Bond Returns are following a Normal Distribution and hence using the NORM.INV and RAND function (In this function I use the standard deviation of historical real annual bond returns as the standard deviation but for the mean I use the current real 10 year bond yield instead of the historical mean) in Excel to generate Annual Real Bond returns. I found the Excess Kurtosis and Skewness of Historical Real Annual Bond Returns to be both 0.66 but considering the sample size of Real Annual Bond Returns I am having is only 24 I made sure to calculate the Z Score of Excess Kurtosis and Skewness and the Z Score for both is less than 1.96 which is indiciating to me that the normal distribution is a good fit for real annual bond returns but I would like to get clarification on this.

Another downside of this method I can think of is not keeping the historical correlation of domestic bonds with these other assets but I found the Historical Correlation of Domestic Bonds with other assets is near 0 regardless of whether Monthly or Annual Real Returns is used for the calculation of correlation so I assume this method is a good idea right? Am I correct in assuming that using this method I am essentially assuming domestic bonds have 0 correlation with other assets.

Thanks.

I think you really need the historical correlation between assets so I would not randomly generate fixed income returns. Think of bonds in 2000 returning 8% and in 2019 returning 2%. It would have been impossible for those returns to switch (2% in 2000 and 8% in 2019) because bond and stock prices are related. They compete for the same money. I think of three factors (stocks, bonds, inflation) and all three are related. If you randomly generate stock returns I would look for historical pairing of inflation and bond returns.

[Anon9001]

I think you really need the historical correlation between assets so I would not randomly generate fixed income returns. Think of bonds in 2000 returning 8% and in 2019 returning 2%. It would have been impossible for those returns to switch (2% in 2000 and 8% in 2019) because bond and stock prices are related. They compete for the same money. I think of three factors (stocks, bonds, inflation) and all three are related. If you randomly generate stock returns I would look for historical pairing of inflation and bond returns.

Thanks for replying despite your arguement making sense to me I think I will stick with this approach though as the Annual Returns Correlation between Bonds and these other Assets (28 Samples) is not statistcally significant so due to this I cant actually conclude that the correlation between Annual Real Stock,Gold and Bond Returns is not 0.

I have another issue now. Whether 1/CAPE should be used as the Geometric or Arithmetic Mean of Annual Returns. Considering 1/CAPE is 10 Year Earnings Yield I think the Geometric Mean of Annual Returns should be equal to 1/CAPE as Arithmetic Mean doesnt incorporate Compounding of Annual Returns so due to this its not a indicator of Long Term Return of Asset. Its more appropriate as indicator of Short Term Return of Asset (in this case Yearly Return). Right now I reduce the Annual Returns by a certain percentage such that the Arithmetic Mean of these Annual Returns is equal to 1/CAPE.

Thanks.

[abc132]

I think you really need the historical correlation between assets so I would not randomly generate fixed income returns. Think of bonds in 2000 returning 8% and in 2019 returning 2%. It would have been impossible for those returns to switch (2% in 2000 and 8% in 2019) because bond and stock prices are related. They compete for the same money. I think of three factors (stocks, bonds, inflation) and all three are related. If you randomly generate stock returns I would look for historical pairing of inflation and bond returns.

Thanks for replying despite your arguement making sense to me I think I will stick with this approach though as the Annual Returns Correlation between Bonds and these other Assets (28 Samples) is not statistcally significant so due to this I cant actually conclude that the correlation between Annual Real Stock,Gold and Bond Returns is not 0.

I have another issue now. Whether 1/CAPE should be used as the Geometric or Arithmetic Mean of Annual Returns. Considering 1/CAPE is 10 Year Earnings Yield I think the Geometric Mean of Annual Returns should be equal to 1/CAPE as Arithmetic Mean doesnt incorporate Compounding of Annual Returns so due to this its not a indicator of Long Term Return of Asset. Its more appropriate as indicator of Short Term Return of Asset (in this case Yearly Return). Right now I reduce the Annual Returns by a certain percentage such that the Arithmetic Mean of these Annual Returns is equal to 1/CAPE.

Thanks.

I believe you are correct that you want the compounded (geometric) return to match.

[Anon9001]

I believe you are correct that you want the compounded (geometric) return to match.

Thanks for clarifying I recently want the Annual Real Bond Returns to fit a Log-Normal Distribution instead of a Normal Distribution so I assume I just need to take the Exponent of the NORM.INV function (The NORM.INV function is with Probability equal to "RAND()", Mean equal to "Curent 10 Year Bond Real Yield", Standard Deviation equal to "Historical Standard Deviation of Annual Real Bond Returns").

This will create Log-Normally distributed values which are all positive so I assume to convert them into returns I just need to subtract 1 from them? The doubt I am having is whether just subtracting 1 from the log-normal values will convert them into returns.

Thanks for all your help,
Anon9001.

[abc132]

I believe you are correct that you want the compounded (geometric) return to match.

Thanks for clarifying I recently want the Annual Real Bond Returns to fit a Log-Normal Distribution instead of a Normal Distribution so I assume I just need to take the Exponent of the NORM.INV function (The NORM.INV function is with Probability equal to "RAND()", Mean equal to "Curent 10 Year Bond Real Yield", Standard Deviation equal to "Historical Standard Deviation of Annual Real Bond Returns").

This will create Log-Normally distributed values which are all positive so I assume to convert them into returns I just need to subtract 1 from them? The doubt I am having is whether just subtracting 1 from the log-normal values will convert them into returns.

Thanks for all your help,
Anon9001.

You are using something like "=1+NORM.INV(RAND(),7%,14%)" to generate one year of stock or bond returns. 1.089 would be an 8.9% return. If you had a sequence of ten years of returns you would take 10 of these and multiply them together to get 10-year returns. As an example, an answer of 1.56 in Excel cell C13 would be a 56% 10-year return. You would get the average annual return (a number like 5.67%) from "=EXP(LN(C13)/10)-1" where C13 is the cell that has the 10 year returns above.

If you want each year to obey CAPE10 predictions you could use "=1+NORM.INV(RAND(),1/CAPE10,14%)" directly in your model. This should do what you want. You do not want to force 10 year stock returns to be 1/CAPE10 because you can never have a negative 10-year return. The same would be true for your bonds. For bonds you could use "=1+NORM.INV(RAND(), current 10-year bond return, historical standard deviation)" directly in your model.

I am not sure if I answered your question, but I hope this helps.

[Anon9001]

You are using something like "=1+NORM.INV(RAND(),7%,14%)" to generate one year of stock or bond returns. 1.089 would be an 8.9% return. If you had a sequence of ten years of returns you would take 10 of these and multiply them together to get 10-year returns. As an example, an answer of 1.56 in Excel cell C13 would be a 56% 10-year return. You would get the average annual return (a number like 5.67%) from "=EXP(LN(C13)/10)-1" where C13 is the cell that has the 10 year returns above.

If you want each year to obey CAPE10 predictions you could use "=1+NORM.INV(RAND(),1/CAPE10,14%)" directly in your model. This should do what you want. You do not want to force 10 year stock returns to be 1/CAPE10 because you can never have a negative 10-year return. The same would be true for your bonds. For bonds you could use "=1+NORM.INV(RAND(), current 10-year bond return, historical standard deviation)" directly in your model.

I am not sure if I answered your question, but I hope this helps.

There is a misunderstanding here but I did find the answer to my question from checking the archive of late Peter Ponzo's Tutorials specifically this one. Basically he is suggesting to take the Exponent of NORM.INV function and then subtract 1 to get Returns which follow a Log-Normal Distribution so looks like I was correct.

Note also that I am not suggesting to add 1 to the NORM.INV function to generate Annual Stock/Bond returns because adding 1 to the NORM.INV function would mean all the values are positive so we are generating Annual Gain Factors (1+Return) by doing so.

If there were no correlation between the portfolio components, Normally distributed gains for each component could be generated according to the following prescription:

Pick a number x between 0 and 1.

Determine α where F(α) = x and F is the Normal, cumulative probability function
(whose values lie between 0 and 1, eh?) and looks like this:

Starting at the mean M, go either left or right a distance αV
where V is the Standard Deviation and α is that random multiplier.

You're now at some Normal gain, M + αV, so convert to a log-Normal Gain:
Gain = EXP(M + αV ) - 1
where (remember?) α is a number generated according to the scheme described above and
x is a uniformly distributed random number (or probability), between 0 and 1

Now generate an annual gain for a two-component portfolio according to:
Gain = [ EXP( Ms + α Vs ) - 1 ] P + [ EXP(Mb + βVb ) - 1 ] (1 - P)
where P is the stock fraction.

[abc132]

You are using something like "=1+NORM.INV(RAND(),7%,14%)" to generate one year of stock or bond returns. 1.089 would be an 8.9% return. If you had a sequence of ten years of returns you would take 10 of these and multiply them together to get 10-year returns. As an example, an answer of 1.56 in Excel cell C13 would be a 56% 10-year return. You would get the average annual return (a number like 5.67%) from "=EXP(LN(C13)/10)-1" where C13 is the cell that has the 10 year returns above.

If you want each year to obey CAPE10 predictions you could use "=1+NORM.INV(RAND(),1/CAPE10,14%)" directly in your model. This should do what you want. You do not want to force 10 year stock returns to be 1/CAPE10 because you can never have a negative 10-year return. The same would be true for your bonds. For bonds you could use "=1+NORM.INV(RAND(), current 10-year bond return, historical standard deviation)" directly in your model.

I am not sure if I answered your question, but I hope this helps.

There is a misunderstanding here but I did find the answer to my question from checking the archive of late Peter Ponzo's Tutorials specifically this one. Basically he is suggesting to take the Exponent of NORM.INV function and then subtract 1 to get Returns which follow a Log-Normal Distribution so looks like I was correct.

Note also that I am not suggesting to add 1 to the NORM.INV function to generate Annual Stock/Bond returns because adding 1 to the NORM.INV function would mean all the values are positive so we are generating Annual Gain Factors (1+Return) by doing so.

If there were no correlation between the portfolio components, Normally distributed gains for each component could be generated according to the following prescription:

Pick a number x between 0 and 1.

Determine α where F(α) = x and F is the Normal, cumulative probability function
(whose values lie between 0 and 1, eh?) and looks like this:

Starting at the mean M, go either left or right a distance αV
where V is the Standard Deviation and α is that random multiplier.

You're now at some Normal gain, M + αV, so convert to a log-Normal Gain:
Gain = EXP(M + αV ) - 1
where (remember?) α is a number generated according to the scheme described above and
x is a uniformly distributed random number (or probability), between 0 and 1

Now generate an annual gain for a two-component portfolio according to:
Gain = [ EXP( Ms + α Vs ) - 1 ] P + [ EXP(Mb + βVb ) - 1 ] (1 - P)
where P is the stock fraction.

I think we are doing the same thing. The equation for Ms+aVs is the same thing as NORM.INV(RAND(),Ms,Vs) - it just finds a normally distributed value for a mean and standard deviation. I think they use this formulation for the option to have alpha and Beta correlated. Stock returns are correlated to bond returns when a relationship between alpha and beta is created.

If I multiply ten years of returns I get (1+yearly_return)^10 for portfolio growth. The natural log of this ln((1+yearly_return)^10) which is 10*ln(1+yearly return) from properties of natural logs. If you go through the math I think you can show that the average yearly_return is exp(NORM.INV(RAND(),Ms,Vs))-1. Once again, I think the alpha and beta formulation are just for the ability to create a correlation between stock and bond performance.

[Anon9001]

If I multiply ten years of returns I get (1+yearly_return)^10 for portfolio growth. The natural log of this ln((1+yearly_return)^10) which is 10*ln(1+yearly return) from properties of natural logs. If you go through the math I think you can show that the average yearly_return is exp(NORM.INV(RAND(),Ms,Vs))-1. Once again, I think the alpha and beta formulation are just for the ability to create a correlation between stock and bond performance.

I can see where the confusion is coming from here. Peter Ponzo's Formula (i.e. (exp(NORM.INV(RAND(),Mu,Sigma)-1)) creates Random Returns which are Log-Normally Distributed not Log Returns.

His formula is taking the Exponent of Normally Distributed Variables which creates a Log-Normal Variables or Log-Normal Gain Factors which are always Positive so he then subtracts 1 from these Log Normal Gain Factors to get Random Returns which follow a Log-Normal Distribution or stated alternatively only Log of these Random Returns follow a Normal Distribution.