## Calling all stats and math gurus...monte carlo in excel or online program

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Pocket Cruiser
Posts: 153
Joined: Mon Jul 08, 2013 5:48 pm
Location: East Texas

### Calling all stats and math gurus...monte carlo in excel or online program

My FIL is a minister and his 403b is in some kind of special plan that has a guaranteed base rate of 3%, and an unlimited upside return. The most it has ever returned in its 42 year history was 14%. The CAGR since inception is 8.35% (nominal).

Since there is no downside risk and the least it would earn in a year is 3%, I imagine his SWR would probably be quite a bit more than the traditional 4%. We'd like to see simulations and results of several different withdrawal rates. I'd like for it to be the same test that the 4% rule was derived from, where the outcomes have a 95% success rate and so on...

I tried the monte carlo test on portfoliovisualizer.com/monte-carlo-simulation, but am unsure if that's the right program to use. I selected:
• Parameterized returns
• Expected return=8.35%
• Volatility=1
• Normal distribution
What volatility should I use for an investment that never has a negative return? And I've seen fat tailed distributions talked about quite a bit on this forum, but admit I've never actually read about it. Should I select fat tail or normal?

I was thinking a better way to model it would be to use a randomized range of returns between 3% and 14% over a 30 or 40 year period. Start with a 4% WR adjusted for 3% inflaton thereafter. Then do a 4.5%WR, then 5%, and so on. The program would run the simulation 5000 times (or however many times is the norm), and then tell us the probability of success for each WR.

Is PV capable of doing this? Isthere an online program out there that would model it better? Or is there a way to do it in excel?

Thanks!

EDIT: See this post from further down in the thread for more info and yearly returns on the plan.viewtopic.php?f=10&t=239093&p=3739145#p3739145
Last edited by Pocket Cruiser on Wed Jan 24, 2018 9:23 am, edited 1 time in total.

rocket354
Posts: 209
Joined: Mon Dec 14, 2015 12:31 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

So, there are a few issues.

One, how safe is the institution that offers a guaranteed minimum of a 3% return?

Two, the nominal CAGR may be 8.35% but the beginning of that 42-year period had very high inflation. What's the CAGR in real terms?

Third, and perhaps most importantly, the variance is the key. If someone could get a guaranteed 3% real every year then their 4% SWR would last about 47 years. Add in some variance, and, well, that's when the 4% rule fails by 30 years. You say it's guaranteed 3% nominal, but in a high-inflation period that could still be a negative real return.

So, you have to use 8.35% as if past performance is indicative of future returns, you have to assume an inflation rate, and you have to completely make up a variance figure. And hope whatever is backing this special fund stays solvent.

I would save time and not worry about going through so many complex calculations based on a lot of complete guesses. I also might look into diversifying some of those assets.

Tanelorn
Posts: 1552
Joined: Thu May 01, 2014 9:35 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

You can do anything in Excel, but that doesn't mean you want to unless you like a challege. This is all qualitative stuff anyway, so I figured the following were good enough for government work so to speak. In PV, use:

8.5% average
2.5% volatility
Normal distribution
Fixed dollar annual withdrawal, inflation adjusted

The intuition here is that the variance is very small for the investment fund as you describe it. By setting the volatility at about half the difference between the mean and the max/min (which are the same distance away roughly), you're saying you need a more than 2sigma event (<5% chance) to go beyond your known boundaries. After all, even if you haven't seen it in the past, there's always a chance whoever's bankrolling the 3% floor gets tired of it and sticks him with only index funds instead down the road. Fat tails makes extremes more likely; you want them to be less likely. That seems reasonable to me.

Looks like he can take 5% of the starting amount , inflation adjusted , and have a 95% chance of success. With 3% inflation and 8.5% returns, this basically says the volatility of this product is low enough you can almost just look at the real mean return (i.e. After inflation) and just take out that much each year. Take a little less and you'll be fine almost always. Take a little more, and it just won't work.

Tanelorn
Posts: 1552
Joined: Thu May 01, 2014 9:35 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

I also might look into diversifying some of those assets.
It's worth knowing more about the structure of the investment, what credit risks are involved, etc, but if it's half as good as it sounds, I don't know why you would recommend selling out of it. In the limit of low volatility, it's almost an 8% bank account with a little noise that goes both ways but not too far down. Who wouldn't want that in today's environment? If inflation goes nuts, the guy can always cash it out since he's retired and put it in some outside fixed income product that pays a competitive rate.

InsuranceQuant
Posts: 3
Joined: Mon Jan 22, 2018 6:04 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

Hi, its usually hard to do montecarlo simulations in excel exactly the way you want to without writing VBA script, but you should look at the data tables functionality. It will allow you to cycle through thousands of scenarios with a simple refresh. You can use it with a RAND function to simulate a monte carlo...

https://support.office.com/en-us/articl ... 542a5ea50b

inbox788
Posts: 5693
Joined: Thu Mar 15, 2012 5:24 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

Pocket Cruiser wrote:
Tue Jan 23, 2018 10:19 pm
The most it has ever returned in its 42 year history was 14%. The CAGR since inception is 8.35% (nominal).

Since there is no downside risk and the least it would earn in a year is 3%, I imagine his SWR would probably be quite a bit more than the traditional 4%. We'd like to see simulations and results of several different withdrawal rates. I'd like for it to be the same test that the 4% rule was derived from, where the outcomes have a 95% success rate and so on...
I think you're barking up the wrong tree. I doubt your model and parameters is accurately projecting potential future performance.
Does the plan disclose how they guarantee 3%? Is it via investment/hedging or insurance (i.e backing by the company)? Do they report annual returns for the 42 years? How did the plan perform in 2008? Have they altered their investment style (likely yes)? Do they describe their investment style? How active is it? If very active, you'll find next to impossible to model or replicate. If they change style slowly, you could try to find a similar investment to use as a proxy. No matter what you choose, there'a good chance using a total market return with a conservative AA gets just as good an estimate. How would you even check or grade the accuracy even if you had all the data? The actual market result was just one of thousands of possible outcomes, and it's pretty much a random walk if you believe that. So you just happen to get lucky with one prediction model to the random walk over another model. How does that help you predict the future?

msk
Posts: 949
Joined: Mon Aug 15, 2016 10:40 am

### Re: Calling all stats and math gurus...monte carlo in excel or online program

I don't see how anyone can guarantee a 3% minimal return in a new investment today, with such low interest rates currently. Perhaps that was guaranteed way back in the 1970s/1980s? All guaranteed stock investments that I am aware of start off by putting the principal in a bond, e.g an x-years bond back in those days would yield a hefty rate, >3%. The excess interest is then invested in options to generate returns higher than the guaranteed minimum. Since Tier 1 bonds currently do not even yield 3%, e.g. for 10 years, how can one guarantee 3%

Valuethinker
Posts: 36735
Joined: Fri May 11, 2007 11:07 am

### Re: Calling all stats and math gurus...monte carlo in excel or online program

Pocket Cruiser wrote:
Tue Jan 23, 2018 10:19 pm
My FIL is a minister and his 403b is in some kind of special plan that has a guaranteed base rate of 3%, and an unlimited upside return. The most it has ever returned in its 42 year history was 14%. The CAGR since inception is 8.35% (nominal).
As per others, check on that! It's risky- -they can't promise 3% when risk free 10 year Treasury bonds are yielding 2.5%. Past performance does not predict future performance, etc.

Normally "guaranteed" returns like that are achieved through derivatives -- option strategies. And thus, there is the chance of total failure.
Since there is no downside risk and the least it would earn in a year is 3%, I imagine his SWR would probably be quite a bit more than the traditional 4%. We'd like to see simulations and results of several different withdrawal rates. I'd like for it to be the same test that the 4% rule was derived from, where the outcomes have a 95% success rate and so on...
In his situation, I would stick with 4%.

Church of England (Anglican/ Episcopalian) ministers have the longest life expectancy at age 65 of any occupational group in England. It's killing the Church, the cost of the pensions. And their spouses live a long time too (women who enter the priesthood are a more recent phenomenon and tend to have had previous careers etc.).

If your FIL & MIL are 65, you probably have to assume 35 year life expectancy (at least for MIL).

I tried the monte carlo test on portfoliovisualizer.com/monte-carlo-simulation, but am unsure if that's the right program to use. I selected:
• Parameterized returns
• Expected return=8.35%
• Volatility=1
• Normal distribution
What volatility should I use for an investment that never has a negative return? And I've seen fat tailed distributions talked about quite a bit on this forum, but admit I've never actually read about it. Should I select fat tail or normal?

I was thinking a better way to model it would be to use a randomized range of returns between 3% and 14% over a 30 or 40 year period. Start with a 4% WR adjusted for 3% inflaton thereafter. Then do a 4.5%WR, then 5%, and so on. The program would run the simulation 5000 times (or however many times is the norm), and then tell us the probability of success for each WR.

Is PV capable of doing this? Isthere an online program out there that would model it better? Or is there a way to do it in excel?

Thanks!
I am guessing you've tried Firecalc?

There are add-ins? Does Solver do it? @Risk or Crystal Ball were the industrial strength ones I have seen.

Tanelorn
Posts: 1552
Joined: Thu May 01, 2014 9:35 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

You guys are likely thinking about the guarantee wrong. Usually the way these pension plans guarantee returns is by taking the difference from someone else, i.e. The company profits or the public taxpayer, not by being good at investing. The SEC is all over people for promising guaranteed returns from risky investing, so I expect it really is guaranteed - the question is by whom?

Here's one such example that has guaranteed 8.25% for the past 30 years (although newer investors only get a guaranteed 7% recently, that's the minimum "allowed by law"). They just tax people to take the difference - must be nice.

https://www.trsnyc.org/memberportal/Inv ... ReturnFund

Strayshot
Posts: 403
Joined: Thu Mar 05, 2015 8:04 am
Location: New Mexico

### Re: Calling all stats and math gurus...monte carlo in excel or online program

I had previously used a product called “Crystal Ball” to do Monte Carlo simulation in excel, but that was a decade ago. Apparently the product is still around and owned/sold by Oracle so that might be a viable choice depending on cost (I have no idea how much it costs now).

Pocket Cruiser
Posts: 153
Joined: Mon Jul 08, 2013 5:48 pm
Location: East Texas

### Re: Calling all stats and math gurus...monte carlo in excel or online program

Thanks for the help.

For those interested, this is the the organization that runs the plan, https://pensionfund.org.
This is the resource book for the TDRA (tax deferred retirement account), https://pensionfund.org/uploads/TDRA_Me ... e_Book.pdf
And here are the returns since inception. I had to email them to get this information. The resource book only had the past 10 years.
1976 1.06
1977 1.09
1978 1.0854
1979 1.0871
1980 1.0994
1981 1.1081
1982 1.1284
1983 1.115
1984 1.1095
1985 1.1022
1986 1.1042
1987 1.0996
1988 1.086
1989 1.0897
1990 1.1105
1991 1.0884
1992 1.1129
1993 1.0955
1994 1.1
1995 1.075
1996 1.1431
1997 1.134
1998 1.1054
1999 1.1101
2000 1.1242
2001 1.06
2002 1.06
2003 1.06
2004 1.11
2005 1.135
2006 1.072
2007 1.1139
2008 1.0745
2009 1.0425
2010 1.0375
2011 1.053
2012 1.0363
2013 1.064
2014 1.1175
2015 1.07
2016 1.035
2017 1.0355
Geomean 1.0887

inbox788
Posts: 5693
Joined: Thu Mar 15, 2012 5:24 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

From the looks of the annual plan, it seems like a good pension plan, though I'm a total novice and just interested in learning.

Page 11 graph shows AA 40 US 15 Int 25 FI 20 Alternate. Without know what this alternate is, I'll assign it 50/50 to stocks and bonds, so I get a 65/35 AA and would use that to estimate future returns. The historical funding adequacy shows it's fully funding except for 2008, which saw about 30% reserves drop to -10% (130% down to 90% or about a 30% drop which is consistent with a 60/40 type AA in 2008). They look to be about 115% funded today, so may experience another shortfall in the next major crash.

Some questions I have are who's funding the pension. Seems like it's from donations. Who's backing the pension if it falls short of reserve requirements? There are potential situations where the market correction doesn't fix a shortfall like 2008, and someone has to step in or guarantees will not be honored. Unless I missed something, there are only a few hundred participants, which could be a risk if you have just a few extra outliers.

I did some quick back of the napkin excel calculations summarized below. The first column is the difference between US Total Market 1987-2015 from PortfolioVisualizer compared to SP500 returns I pulled from https://seekingalpha.com/instablog/6052 ... -1928-2015

Note the bottom line is -0.1%, which means the simple average performance difference between total market and SP500 is small, most years less than 2%, a few above 3% and highest in 1998 at -5.1%.

In the right column, I compared 1987-2015 PortfolioVisualizer 60/40 portfolio against the Pension and got 0.9%. The pension underperformed the 60/40 portfolio by almost 1%. You can see the performance varied quite a bit. Not shown, but I also compared to a 40/60 portfolio and the pension was 0.2% better performance, but the performance still varied. My interpretation is that the risk of the portfolio is similar to a 60/40 (or maybe more aggressive due to alternate investments), yet the return is like a 40/60. The 1% downside difference is comparable to active funds, but you do have the benefit of minimum 3% guarantee.

BTW, it's strange to have 3% returns in last 2 years when the market is double digits.

Code: Select all

``````-3.2%	-7.8%
0.8%	4.7%
-3.4%	13.4%
-3.0%	-11.2%
2.2%	16.7%
1.6%	-3.0%
0.6%	0.7%
-1.5%	-11.2%
-1.4%	21.2%
-1.7%	-0.3%
-2.1%	9.0%
-5.1%	6.9%
2.9%	3.0%
-1.5%	-14.2%
0.9%	-9.2%
1.0%	-15.3%
3.0%	14.4%
1.8%	-1.8%
1.2%	-9.0%
-0.1%	3.8%
0.0%	-5.3%
-0.5%	-27.7%
2.8%	15.3%
2.3%	9.1%
-1.1%	-1.7%
0.4%	7.7%
1.2%	12.7%
-1.1%	-2.0%
-1.1%	-6.7%
5.0%
10.5%
-0.1%	0.9%
``````

dbr
Posts: 27207
Joined: Sun Mar 04, 2007 9:50 am

### Re: Calling all stats and math gurus...monte carlo in excel or online program

Strayshot wrote:
Wed Jan 24, 2018 8:03 am
I had previously used a product called “Crystal Ball” to do Monte Carlo simulation in excel, but that was a decade ago. Apparently the product is still around and owned/sold by Oracle so that might be a viable choice depending on cost (I have no idea how much it costs now).
I am in the same position having used Crystal Ball in previous employment also some time ago. Out of curiosity I did look recently and I think a person can buy that add-on for something like \$600 or \$800, if I recall correctly.

As to the statistical problem, that would take some thinking. I wouldn't have an answer off hand. But then I am a pseudo-guru and not a real one.

Oicuryy
Posts: 1266
Joined: Thu Feb 22, 2007 10:29 pm

### Re: Calling all stats and math gurus...monte carlo in excel or online program

A monte carlo simulation might be a fun excel exercise. But I wouldn't bet my retirement on the results.
• Covert those nominal annual returns to real returns.
• Treat them as samples from a lognormal probability distribution and calculate their mean and standard deviation.
• Use the formula on this Wikipedia page to convert the mean and standard deviation to the mu and sigma parameters of the underlying normal distribution.
https://en.wikipedia.org/wiki/Log-normal_distribution
• Use this excel formula to generate a random one-year return. Build a table of random returns with 5000 rows and 30 or 40 columns.