Computing a Safe Withdrawal Rate (SWR) concisely

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Mon Feb 05, 2018 3:12 pm

In the course of maintaining the Simba backtesting spreadsheet, I pondered about ways to compute a Safe Withdrawal Rate (SWR) in a very concise and efficient manner. I identified a pretty neat way to proceed, and this was implemented in the Simba spreadsheet in recent versions. I feel that this could be useful for other people, with their own spreadsheets, so let me explain the findings.

First off, for a given series of real (inflation-adjusted) returns, David Blanchett identified a mathematical formula to compute the Sustainable Spending Rate (SWR-equivalent for a single retirement cycle):

Image

For the curious minds eager to understand WHY this true, please check this blog article about the Simba spreadsheet, where I provided a small chunk of algebra to demonstrate that the Blanchett formula is indeed correct.

Now the question becomes how to compute such quantity in a very compact manner with a backtesting spreadsheet.

First, let’s ponder about terms like (1+R1), (1+R1)*(1+R2), (1+R1)*(1+R2)*(1+R3), etc. This is essentially the cumulative growth of $1 after one year, two years, three years, etc. So we can revisit the Blanchett formula, and think of the denominators as growth of $1 (t0 to end of year1), growth of $1 (t0 to end of year2), growth of $1 (t0 to end of year3), etc.

Then I realized that SSR is simply the harmonic mean of those cumulative growth quantities, divided by the number of years of the cycle. Which means that we can rewrite the Blanchett formula as:

Image

Pretty cool, isn't it?

In the same manner, one can derive a Perpetual Withdrawal Rate (aiming at conserving the portfolio in real terms, instead of depleting it like SWR does), and with a bit more algebra, one can demonstrate that this is a simple function of SWR and of the cumulative growth of the initial investment.

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Mon Feb 05, 2018 3:17 pm

In order to illustrate the point, and get concrete, I also created a simple online spreadsheet performing this type of SWR (and PWR) computation. Here is the link (feel free to share):

https://docs.google.com/spreadsheets/d/ ... sp=sharing

FactualFran
Posts: 729
Joined: Sat Feb 21, 2015 2:29 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by FactualFran » Tue Feb 06, 2018 2:48 pm

Thank you for posting the information about using the harmonic average to calculate SWR and PWR.

I will use a formula based on what you posted to have a spreadsheet calculate the SWR and PWR for each starting year. I had been manually running a macro that iterates over the starting years and uses the GoalSeek spreadsheet function to determine the initial withdrawal rate that results in the end balance being equal to a target value, such as the initial balance adjusted for inflation.

User avatar
FiveK
Posts: 5593
Joined: Sun Mar 16, 2014 2:43 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by FiveK » Tue Feb 06, 2018 4:03 pm

Very nice! Kudos to you and David Blanchett.

Interesting that the sequence of returns is not relevant for this calculation.

FactualFran
Posts: 729
Joined: Sat Feb 21, 2015 2:29 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by FactualFran » Tue Feb 06, 2018 4:26 pm

FiveK wrote:
Tue Feb 06, 2018 4:03 pm
Interesting that the sequence of returns is not relevant for this calculation.
The sequence of returns affects the Cumulative Growth values whose harmonic average is being calculated.

User avatar
FiveK
Posts: 5593
Joined: Sun Mar 16, 2014 2:43 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by FiveK » Tue Feb 06, 2018 4:56 pm

FactualFran wrote:
Tue Feb 06, 2018 4:26 pm
The sequence of returns affects the Cumulative Growth values whose harmonic average is being calculated.
Thanks - yes, once the correct spreadsheet entries are made. :oops:

GratefulinNC
Posts: 68
Joined: Sat Jan 20, 2018 2:33 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by GratefulinNC » Tue Feb 06, 2018 4:57 pm

Discussion threads like this are enough to drive me into the arms of a financial advisor. The last thing I would want to do in my senior citizen, declining years is use advanced mathematics to attempt to calculate SWRs.

Bob
Posts: 192
Joined: Mon Feb 26, 2007 5:15 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by Bob » Tue Feb 06, 2018 5:17 pm

Work and contributions to the group like this are fantastic and appreciated very much. Thank you and look forward to seeing useful comments

User avatar
AtlasShrugged?
Posts: 589
Joined: Wed Jul 15, 2015 6:08 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by AtlasShrugged? » Wed Feb 07, 2018 8:45 am

siamond....I am going to make myself look like a blooming idiot, but heck, it would not be the first time. :oops:

I downloaded the spreadsheet. Are there user instructions? :? Because I am really not quite sure how to use this. I very much want to.

What do I enter in the yellow cells?
What do I enter in the orange cells?
“If you don't know, the thing to do is not to get scared, but to learn.”

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Wed Feb 07, 2018 9:17 am

JCE66 wrote:
Wed Feb 07, 2018 8:45 am
siamond....I am going to make myself look like a blooming idiot, but heck, it would not be the first time. :oops:

I downloaded the spreadsheet. Are there user instructions? :? Because I am really not quite sure how to use this. I very much want to.

What do I enter in the yellow cells?
What do I enter in the orange cells?
You're not alone, other folks were a tad puzzled as well. This will teach me to publish a spreadsheet without clear explanations! :oops:

This spreadsheet isn't intended as a user tool. It's just an illustration of the formulas I described in the first post, as well as a proof by example. The intent is not for you to customize, the intent is for you to look at the various formulas, and get convinced that a) it's very simple b) it works. And then, if you're so inclined, you can make your own spreadsheets using similar logic.

Now if you really want to play around, make a copy of the spreadsheet (or download it in Excel format), and replace the orange cells by some other series of real (inflation-adjusted) returns, adding more rows as needs be, and you'll get the SWR/PWR numbers. Yellow cells do not need to be changed, those are just constant values to start the math right.

Or (better!) go check out the Simba backtesting spreadsheet, as the latest version provides pre-canned extensive support for SWR/PWR analysis based on easy to customize portfolios.

EDIT: ok, I added more explanations and instructions. I hope this is more clear now! :wink:

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Tue Feb 20, 2018 1:16 am

A very valuable comment on another thread, which really needs to be captured here. Very cool.
Oicuryy wrote:
Tue Feb 20, 2018 12:47 am
siamond's formulas for calculating SSR, the sustainable spending rate of constant annual withdrawals, can also be used in calculating the ending value of constant annual contributions. Just multiply the constant contribution amount by Gn/SSR.
EDIT: for archive, let me add the full formulas for accumulation and withdrawals (courtesy of Oicuryy):
Image
Last edited by siamond on Wed Feb 21, 2018 2:58 pm, edited 1 time in total.

1nv35t
Posts: 113
Joined: Wed Dec 13, 2017 3:37 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by 1nv35t » Tue Feb 20, 2018 5:08 am

I have a real gain progression (based on actual (UK) values since 1896) that runs ...

Code: Select all

1.0000
1.0264
1.1050
1.2372
1.3126
1.3745
1.5144
1.5433
1.4197
1.5788
1.7120
1.7363
1.3798
1.5769
1.6836
1.5991
1.5784
1.5575
1.4704
1.4824
1.4003
1.2707
0.8952
0.8861
1.0969
0.9989
1.0310
1.3768
1.5341
1.6632
1.9432
2.1409
2.5087
3.0305
2.9408
2.6798
2.5510
2.4309
2.4010
2.3705
2.8615
3.2438
2.5501
2.9500
2.9520
2.6003
2.5051
2.8215
3.3452
3.7951
4.5417
4.6542
5.0908
4.8440
6.3272
6.8881
7.0860
6.9859
6.7985
7.9285
8.8593
8.9431
8.1129
9.4248
10.2369
10.3035
11.7020
11.0734
12.5295
13.3553
13.9027
13.1037
15.6730
16.3436
14.9989
14.4037
14.9809
18.9453
17.7717
13.2422
14.1524
15.4943
13.1178
13.7710
13.9392
13.6797
13.5409
15.7456
18.4852
21.4882
21.6048
24.0942
22.1028
26.0774
30.5235
23.7773
25.3310
28.9717
30.3727
28.9842
33.1726
35.5186
42.5986
47.8302
55.8251
57.1848
57.5541
55.1816
60.3583
63.7702
69.4241
69.5213
70.5429
59.2230
63.0624
65.4458
63.7676
65.0092
73.8587
83.7748
88.5009
102.5369
From which applying the calculations produced

CAGR ==> 3.90%
SWR ==> 2.85%
PWR ==> 2.83%

When I apply that PWR to all 50 year periods however I see failures i.e. corresponding real (inflation adjusted) amount of investment remaining after applying 2.83% PWR of (where 1.0 would = 100% of the original inflation adjusted start date amount remaining and 0.0 or less = total loss) and where the 11th run yielded a figure of -92.2%) :

Code: Select all

0.620
0.626
0.418
0.042
-0.123
-0.241
-0.609
-0.584
-0.094
-0.538
-0.922
-0.875
0.502
-0.086
-0.352
0.113
0.401
0.624
1.408
1.671
2.034
3.009
8.059
9.404
6.759
7.331
9.673
8.566
5.830
3.658
2.901
2.723
1.526
0.758
1.018
1.592
2.032
2.719
3.439
4.315
2.939
2.460
4.144
3.956
4.550
4.560
5.909
6.000
4.874
3.780
3.120
3.362
3.389
4.427
2.914
2.501
2.297
2.606
3.446
2.482
1.994
2.168
3.124
1.872
1.660
1.801
1.143
1.619
1.249
1.137
1.107
1.758
3.633
Could just be errors in my spreadsheet/calculations, but thought I'd just highlight the observation.

For completeness the respective inflation and nominal yearly gain/loss percentages were

Code: Select all

-1.10	1.52
4.44	12.44
1.06	13.16
-1.05	4.98
3.19	8.06
1.03	11.31
1.02	2.95
1.01	-7.08
1.00	12.31
-0.99	7.36
0.00	1.42
3.00	-18.15
2.91	17.61
0.00	6.77
0.94	-4.13
0.93	-0.37
3.70	2.33
0.00	-5.59
-0.89	-0.09
23.42	16.59
18.25	7.30
20.99	-14.77
15.31	14.14
5.75	30.92
15.90	5.54
-9.39	-6.47
-18.73	8.53
-4.90	5.96
0.52	8.97
0.51	17.44
-2.55	7.36
-2.09	14.73
-1.07	19.51
-1.62	-4.53
-3.30	-11.88
-6.82	-11.30
-2.44	-7.03
-1.25	-2.47
-0.63	-1.89
1.27	22.25
3.14	16.93
4.88	-17.55
1.16	17.03
8.62	8.69
12.70	-0.73
3.10	-0.67
-0.50	12.07
-0.50	17.97
1.00	14.58
1.00	20.87
0.50	2.99
3.20	12.88
4.90	-0.19
3.50	35.19
3.20	12.35
12.00	15.22
6.30	4.80
1.10	-1.61
4.00	21.29
5.80	18.22
3.00	3.97
4.60	-5.11
1.80	18.26
0.00	8.62
1.80	2.46
4.40	18.57
2.60	-2.91
1.90	15.30
4.80	11.71
4.50	8.78
3.70	-2.26
2.50	22.60
5.90	10.43
4.70	-3.91
7.90	3.62
9.00	13.37
7.70	36.20
10.60	3.75
19.10	-11.26
24.90	33.48
15.10	26.01
12.10	-5.09
8.40	13.80
17.20	18.63
15.10	12.96
12.00	10.86
5.40	22.56
5.30	23.62
4.60	21.59
5.70	6.27
3.70	15.65
3.70	-4.87
6.80	26.01
7.70	26.06
9.30	-14.86
4.50	11.33
2.60	17.35
1.90	6.83
2.90	-1.80
3.20	18.11
2.50	9.75
3.60	24.25
2.80	15.43
1.80	18.82
2.90	5.41
0.70	1.35
2.90	-1.34
2.80	12.44
3.50	9.35
2.20	11.26
4.40	4.55
4.00	5.53
1.00	-15.21
2.30	8.93
4.80	8.76
4.80	2.11
3.10	5.11
2.70	16.68
1.60	15.24
0.10	5.75
1.20	17.25
0.00	11.30
And focusing in on that 11th run failure I calculate that it failed at around 34 to 35 years.

Code: Select all

Inflation	Nominal TR	Year	$ Value	Withdrawal	Adj	Count
			100.00	2.83	97.17	
0.00	1.42	1906	98.55	2.83	95.72	1
3.00	-18.15	1907	78.35	2.91	75.44	2
2.91	17.61	1908	88.72	3.00	85.72	3
0.00	6.77	1909	91.52	3.00	88.52	4
0.94	-4.13	1910	84.87	3.03	81.84	5
0.93	-0.37	1911	81.54	3.06	78.48	6
3.70	2.33	1912	80.31	3.17	77.14	7
0.00	-5.59	1913	72.83	3.17	69.66	8
-0.89	-0.09	1914	69.60	3.14	66.46	9
23.42	16.59	1915	77.49	3.88	73.61	10
18.25	7.30	1916	78.98	4.58	74.40	11
20.99	-14.77	1917	63.41	5.55	57.86	12
15.31	14.14	1918	66.04	6.40	59.65	13
5.75	30.92	1919	78.09	6.76	71.32	14
15.90	5.54	1920	75.27	7.84	67.44	15
-9.39	-6.47	1921	63.07	7.10	55.97	16
-18.73	8.53	1922	60.75	5.77	54.97	17
-4.90	5.96	1923	58.25	5.49	52.76	18
0.52	8.97	1924	57.49	5.52	51.98	19
0.51	17.44	1925	61.04	5.55	55.49	20
-2.55	7.36	1926	59.58	5.41	54.17	21
-2.09	14.73	1927	62.15	5.29	56.86	22
-1.07	19.51	1928	67.95	5.24	62.71	23
-1.62	-4.53	1929	59.87	5.15	54.72	24
-3.30	-11.88	1930	48.22	4.98	43.24	25
-6.82	-11.30	1931	38.35	4.64	33.71	26
-2.44	-7.03	1932	31.34	4.53	26.81	27
-1.25	-2.47	1933	26.15	4.47	21.68	28
-0.63	-1.89	1934	21.27	4.44	16.83	29
1.27	22.25	1935	20.57	4.50	16.07	30
3.14	16.93	1936	18.79	4.64	14.15	31
4.88	-17.55	1937	11.67	4.87	6.80	32
1.16	17.03	1938	7.96	4.92	3.03	33
8.62	8.69	1939	3.30	5.35	-2.05	34
12.7	-0.73	1940	-2.04	6.03	-8.06	35
I have noticed in the past that the transition from having sustained to failure does occur quickly across a very small amount of change in SWR value. At 2% SWR for example that was a PWR having a worst case of 0.88 (88%) of the original inflation adjusted start date remaining after 50 years (that interestingly if you cost averaged into over 3 timepoints (2 calendar year) i.e. average of 3 adjacent values, improved the figure to 95% remaining).

As much as I love the genius and simplicity of the PWR calculation, perhaps its more of a reasonable approximation, but not something to totally rely upon?

getrichslowly
Posts: 73
Joined: Mon Feb 12, 2018 11:48 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by getrichslowly » Tue Feb 20, 2018 10:58 am

1nv35t wrote:
Tue Feb 20, 2018 5:08 am
I have a real gain progression (based on actual (UK) values since 1896) that runs ...

Code: Select all

1.0000
1.0264
1.1050
1.2372
1.3126
1.3745
1.5144
1.5433
1.4197
1.5788
1.7120
1.7363
1.3798
1.5769
1.6836
1.5991
1.5784
1.5575
1.4704
1.4824
1.4003
1.2707
0.8952
0.8861
1.0969
0.9989
1.0310
1.3768
1.5341
1.6632
1.9432
2.1409
2.5087
3.0305
2.9408
2.6798
2.5510
2.4309
2.4010
2.3705
2.8615
3.2438
2.5501
2.9500
2.9520
2.6003
2.5051
2.8215
3.3452
3.7951
4.5417
4.6542
5.0908
4.8440
6.3272
6.8881
7.0860
6.9859
6.7985
7.9285
8.8593
8.9431
8.1129
9.4248
10.2369
10.3035
11.7020
11.0734
12.5295
13.3553
13.9027
13.1037
15.6730
16.3436
14.9989
14.4037
14.9809
18.9453
17.7717
13.2422
14.1524
15.4943
13.1178
13.7710
13.9392
13.6797
13.5409
15.7456
18.4852
21.4882
21.6048
24.0942
22.1028
26.0774
30.5235
23.7773
25.3310
28.9717
30.3727
28.9842
33.1726
35.5186
42.5986
47.8302
55.8251
57.1848
57.5541
55.1816
60.3583
63.7702
69.4241
69.5213
70.5429
59.2230
63.0624
65.4458
63.7676
65.0092
73.8587
83.7748
88.5009
102.5369
From which applying the calculations produced

CAGR ==> 3.90%
SWR ==> 2.85%
PWR ==> 2.83%

When I apply that PWR to all 50 year periods however I see failures i.e. corresponding real (inflation adjusted) amount of investment remaining after applying 2.83% PWR of (where 1.0 would = 100% of the original inflation adjusted start date amount remaining and 0.0 or less = total loss) and where the 11th run yielded a figure of -92.2%) :

Code: Select all

0.620
0.626
0.418
0.042
-0.123
-0.241
-0.609
-0.584
-0.094
-0.538
-0.922
-0.875
0.502
-0.086
-0.352
0.113
0.401
0.624
1.408
1.671
2.034
3.009
8.059
9.404
6.759
7.331
9.673
8.566
5.830
3.658
2.901
2.723
1.526
0.758
1.018
1.592
2.032
2.719
3.439
4.315
2.939
2.460
4.144
3.956
4.550
4.560
5.909
6.000
4.874
3.780
3.120
3.362
3.389
4.427
2.914
2.501
2.297
2.606
3.446
2.482
1.994
2.168
3.124
1.872
1.660
1.801
1.143
1.619
1.249
1.137
1.107
1.758
3.633
Could just be errors in my spreadsheet/calculations, but thought I'd just highlight the observation.

For completeness the respective inflation and nominal yearly gain/loss percentages were

Code: Select all

-1.10	1.52
4.44	12.44
1.06	13.16
-1.05	4.98
3.19	8.06
1.03	11.31
1.02	2.95
1.01	-7.08
1.00	12.31
-0.99	7.36
0.00	1.42
3.00	-18.15
2.91	17.61
0.00	6.77
0.94	-4.13
0.93	-0.37
3.70	2.33
0.00	-5.59
-0.89	-0.09
23.42	16.59
18.25	7.30
20.99	-14.77
15.31	14.14
5.75	30.92
15.90	5.54
-9.39	-6.47
-18.73	8.53
-4.90	5.96
0.52	8.97
0.51	17.44
-2.55	7.36
-2.09	14.73
-1.07	19.51
-1.62	-4.53
-3.30	-11.88
-6.82	-11.30
-2.44	-7.03
-1.25	-2.47
-0.63	-1.89
1.27	22.25
3.14	16.93
4.88	-17.55
1.16	17.03
8.62	8.69
12.70	-0.73
3.10	-0.67
-0.50	12.07
-0.50	17.97
1.00	14.58
1.00	20.87
0.50	2.99
3.20	12.88
4.90	-0.19
3.50	35.19
3.20	12.35
12.00	15.22
6.30	4.80
1.10	-1.61
4.00	21.29
5.80	18.22
3.00	3.97
4.60	-5.11
1.80	18.26
0.00	8.62
1.80	2.46
4.40	18.57
2.60	-2.91
1.90	15.30
4.80	11.71
4.50	8.78
3.70	-2.26
2.50	22.60
5.90	10.43
4.70	-3.91
7.90	3.62
9.00	13.37
7.70	36.20
10.60	3.75
19.10	-11.26
24.90	33.48
15.10	26.01
12.10	-5.09
8.40	13.80
17.20	18.63
15.10	12.96
12.00	10.86
5.40	22.56
5.30	23.62
4.60	21.59
5.70	6.27
3.70	15.65
3.70	-4.87
6.80	26.01
7.70	26.06
9.30	-14.86
4.50	11.33
2.60	17.35
1.90	6.83
2.90	-1.80
3.20	18.11
2.50	9.75
3.60	24.25
2.80	15.43
1.80	18.82
2.90	5.41
0.70	1.35
2.90	-1.34
2.80	12.44
3.50	9.35
2.20	11.26
4.40	4.55
4.00	5.53
1.00	-15.21
2.30	8.93
4.80	8.76
4.80	2.11
3.10	5.11
2.70	16.68
1.60	15.24
0.10	5.75
1.20	17.25
0.00	11.30
And focusing in on that 11th run failure I calculate that it failed at around 34 to 35 years.

Code: Select all

Inflation	Nominal TR	Year	$ Value	Withdrawal	Adj	Count
			100.00	2.83	97.17	
0.00	1.42	1906	98.55	2.83	95.72	1
3.00	-18.15	1907	78.35	2.91	75.44	2
2.91	17.61	1908	88.72	3.00	85.72	3
0.00	6.77	1909	91.52	3.00	88.52	4
0.94	-4.13	1910	84.87	3.03	81.84	5
0.93	-0.37	1911	81.54	3.06	78.48	6
3.70	2.33	1912	80.31	3.17	77.14	7
0.00	-5.59	1913	72.83	3.17	69.66	8
-0.89	-0.09	1914	69.60	3.14	66.46	9
23.42	16.59	1915	77.49	3.88	73.61	10
18.25	7.30	1916	78.98	4.58	74.40	11
20.99	-14.77	1917	63.41	5.55	57.86	12
15.31	14.14	1918	66.04	6.40	59.65	13
5.75	30.92	1919	78.09	6.76	71.32	14
15.90	5.54	1920	75.27	7.84	67.44	15
-9.39	-6.47	1921	63.07	7.10	55.97	16
-18.73	8.53	1922	60.75	5.77	54.97	17
-4.90	5.96	1923	58.25	5.49	52.76	18
0.52	8.97	1924	57.49	5.52	51.98	19
0.51	17.44	1925	61.04	5.55	55.49	20
-2.55	7.36	1926	59.58	5.41	54.17	21
-2.09	14.73	1927	62.15	5.29	56.86	22
-1.07	19.51	1928	67.95	5.24	62.71	23
-1.62	-4.53	1929	59.87	5.15	54.72	24
-3.30	-11.88	1930	48.22	4.98	43.24	25
-6.82	-11.30	1931	38.35	4.64	33.71	26
-2.44	-7.03	1932	31.34	4.53	26.81	27
-1.25	-2.47	1933	26.15	4.47	21.68	28
-0.63	-1.89	1934	21.27	4.44	16.83	29
1.27	22.25	1935	20.57	4.50	16.07	30
3.14	16.93	1936	18.79	4.64	14.15	31
4.88	-17.55	1937	11.67	4.87	6.80	32
1.16	17.03	1938	7.96	4.92	3.03	33
8.62	8.69	1939	3.30	5.35	-2.05	34
12.7	-0.73	1940	-2.04	6.03	-8.06	35
I have noticed in the past that the transition from having sustained to failure does occur quickly across a very small amount of change in SWR value. At 2% SWR for example that was a PWR having a worst case of 0.88 (88%) of the original inflation adjusted start date remaining after 50 years (that interestingly if you cost averaged into over 3 timepoints (2 calendar year) i.e. average of 3 adjacent values, improved the figure to 95% remaining).

As much as I love the genius and simplicity of the PWR calculation, perhaps its more of a reasonable approximation, but not something to totally rely upon?
You calculated the PWR of a portfolio beginning in 1986. That only works for 1986 and not other years. The PWR is startpoint specific.

I think what you want to do is iterate through all N-year periods in your data set, and calculate the individual SWR/PWR for each period. Then you calculate the min/max/mean/median of the distribution of SWR/PWRs. If it was me, I would probably want to know the min SWR/PWR across all 30-year periods. With 122 years that's 92 SWRs. The lowest SWR is the most interesting one.

(Since PWR means perpetual that implies infinite data, so to estimate it with finite data, I think you just need to calculate what withdrawal rate allows you to maintain your original inflation-adjusted balance at the end of the period.)

But we warned. Just because a strategy works in backtesting doesn't guarantee it will work in the future.

1nv35t
Posts: 113
Joined: Wed Dec 13, 2017 3:37 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by 1nv35t » Tue Feb 20, 2018 2:21 pm

getrichslowly wrote:
Tue Feb 20, 2018 10:58 am
You calculated the PWR of a portfolio beginning in 1986. That only works for 1986 and not other years. The PWR is startpoint specific.

I think what you want to do is iterate through all N-year periods in your data set, and calculate the individual SWR/PWR for each period. Then you calculate the min/max/mean/median of the distribution of SWR/PWRs. If it was me, I would probably want to know the min SWR/PWR across all 30-year periods. With 122 years that's 92 SWRs. The lowest SWR is the most interesting one.
Ah! Thanks.

I re-ran a 30/70 SCV/10 year T type asset allocation for all 50 year periods (UK data/inflation), calculating the SWR/PWR ...etc. for each and the min PWR across all of the PWR's indicated 1.99%. Using my spreadsheet that calculates all 50 year actuals using a choice of 1.99% SWR had the worst case having 98.72% of the original inflation adjusted start date amount still available at the end of the 50 years. For both measures the worst case was 1900 to 1949 inclusive. Adjoining runs also had similar results being shown i.e how cost averaging in over 3 time-points/2 years would have uplifted the worst case (that obviously reflects a peak-trough era). So near as seems to fit/work very well.

Out of interest, my figures indicate that the average and median cases had approx 4 times more in inflation adjusted terms, which adds a further 2.8% annualised real gain on top of that (near) 2% SWR figure. (Max case had 11.1 times more (4.83% annualised real)). They are however gross figures. Discount basic rate (most common) historic dividend taxation and bond interest taxation and the worst case dropped to 1.2% PWR (with the typical average/median case seeing 1.6% annualised real in addition to that) - excluding any capital gains taxation and trading costs. Using a 2% net SWR and the worst case saw just 13% of the original inflation adjusted amount remaining after the 50 years (average case had around 30% more (0.5% annualised).

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Tue Feb 20, 2018 2:33 pm

getrichslowly wrote:
Tue Feb 20, 2018 10:58 am
You calculated the PWR of a portfolio beginning in 1986. That only works for 1986 and not other years. The PWR is startpoint specific.
Yup. Well, 1896, not 1986! SWR/PWR is dependent on the start point and the end point.
getrichslowly wrote:
Tue Feb 20, 2018 10:58 am
I think what you want to do is iterate through all N-year periods in your data set, and calculate the individual SWR/PWR for each period. Then you calculate the min/max/mean/median of the distribution of SWR/PWRs. If it was me, I would probably want to know the min SWR/PWR across all 30-year periods. With 122 years that's 92 SWRs. The lowest SWR is the most interesting one.
Yes. Although I would argue that the 10% lowest percentile (or something like that) conveys more information than the min (which is a single data point in history after all, very unlikely to repeat itself).

If you check the Simba backtesting spreadsheet, all this math is pre-canned for you for an arbitrary portfolio. Including the percentile math. And if you hone on the 0%, or 50%, or 100% percentile, you get the min, median and max.

User avatar
grayfox
Posts: 4933
Joined: Sat Sep 15, 2007 4:30 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by grayfox » Wed Feb 21, 2018 12:09 am

siamond wrote:
Mon Feb 05, 2018 3:12 pm
In the course of maintaining the Simba backtesting spreadsheet, I pondered about ways to compute a Safe Withdrawal Rate (SWR) in a very concise and efficient manner. I identified a pretty neat way to proceed, and this was implemented in the Simba spreadsheet in recent versions. I feel that this could be useful for other people, with their own spreadsheets, so let me explain the findings.

First off, for a given series of real (inflation-adjusted) returns, David Blanchett identified a mathematical formula to compute the Sustainable Spending Rate (SWR-equivalent for a single retirement cycle):

Image

For the curious minds eager to understand WHY this true, please check this blog article about the Simba spreadsheet, where I provided a small chunk of algebra to demonstrate that the Blanchett formula is indeed correct.
That was formula was derived by gummy, a.k.a Peter Ponzo, about 10 years ago. He called it a Magic Sum. He is a retired Math Professor up in Canada. It's been used in various Boglehead spreadsheets al least since 2010. I downloaded a spreadsheet from bobsfinancialwebsite (bob90245) about that time that calculated what he called the Maximum Withdrawal Rate MWR. I even updated it in 2016.

Here's an archive of Gummy's website: http://www.finiki.org/wiki/Gummy-stuff
See a Magic Sum and Sensible Withdrawal Strategies here

Gummy's work is priceless. He already figured all this stuff out years ago, and was willing to share it with anyone who was interested.

On the wayback machine, I found the download page for bobsfinancialwebsite.com. At the bottom is a download for an Excel file for Maximum Withdrawal Rate. That's the file I downloaded in 2010. Unfortunately, the wayback doesn't seem to have archived the zip file.

Here's a Oct 2010 BH post by Oicurvy that presents and discusses the formula: viewtopic.php?p=857618#p857618

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Wed Feb 21, 2018 1:21 am

grayfox wrote:
Wed Feb 21, 2018 12:09 am
That was formula was derived by gummy, a.k.a Peter Ponzo, about 10 years ago.
Cool. Thanks for the historical tidbits. Mathematics do not age! :wink:

User avatar
Oicuryy
Posts: 1245
Joined: Thu Feb 22, 2007 10:29 pm

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by Oicuryy » Wed Feb 21, 2018 11:04 am

grayfox wrote:
Wed Feb 21, 2018 12:09 am
That was formula was derived by gummy, a.k.a Peter Ponzo, about 10 years ago. He called it a Magic Sum.
After all these years I finally realize it is just a present value calculation. The sustainable spending amount is the withdrawal amount that makes the sum of the future values of the cash flows, including the initial value of the portfolio, equal to zero. The sustainable spending rate (SSR aka SWR) is the sustainable spending amount divided by the initial value of the portfolio.

I once wrote an excel macro to calculate the gMS of returns.
viewtopic.php?p=36154#p36154

Gummy even added it to his copy of the Simba spreadsheet.
http://www.financialwisdomforum.org/gum ... /Simba.htm

Ron
Money is fungible | Abbreviations and Acronyms

getrichslowly
Posts: 73
Joined: Mon Feb 12, 2018 11:48 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by getrichslowly » Fri Feb 23, 2018 6:49 pm

siamond wrote:
Tue Feb 20, 2018 2:33 pm
Yes. Although I would argue that the 10% lowest percentile (or something like that) conveys more information than the min (which is a single data point in history after all, very unlikely to repeat itself).
Stock market variance is theoretically infinite so actually the worst market drawdown in human history probably lies ahead of us, not behind us. With ultrathin left tails, what Nassim Taleb calls Black Swans, nobody sees them coming if they are merely sampling historical data.

getrichslowly
Posts: 73
Joined: Mon Feb 12, 2018 11:48 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by getrichslowly » Fri Feb 23, 2018 6:55 pm

1nv35t wrote:
Tue Feb 20, 2018 2:21 pm
i.e how cost averaging in over 3 time-points/2 years would have uplifted the worst case (that obviously reflects a peak-trough era). So near as seems to fit/work very well.
I wish more people used this approach in their studies. In reality no one buys their entire portfolio in one lump sum, but naturally DCA over a lifetime of 20-40 solid working years. So the threat of retiring at the top is not really a threat because if you retired at the top, you are retiring with more wealth than you would otherwise have. The only risk if you retire prematurely because of inflated asset prices exaggerating your true wealth. For this reason I will only retire early if I can target a 2-3% SWR. So a more realistic study would be over the investor's entire lifetime including both the accumulation and withdrawal phase. The problem there I guess is the shortage of nonoverlapping 60-year periods in stock market history.

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Fri Feb 23, 2018 7:30 pm

getrichslowly wrote:
Fri Feb 23, 2018 6:49 pm
siamond wrote:
Tue Feb 20, 2018 2:33 pm
Yes. Although I would argue that the 10% lowest percentile (or something like that) conveys more information than the min (which is a single data point in history after all, very unlikely to repeat itself).
Stock market variance is theoretically infinite so actually the worst market drawdown in human history probably lies ahead of us, not behind us. With ultrathin left tails, what Nassim Taleb calls Black Swans, nobody sees them coming if they are merely sampling historical data.
Sure. I agree. I don't think our respective statements are contradictory, actually.

User avatar
siamond
Posts: 4186
Joined: Mon May 28, 2012 5:50 am

Re: Computing a Safe Withdrawal Rate (SWR) concisely

Post by siamond » Wed Feb 28, 2018 6:23 pm

Here is a cool article, which plays with the same kind of equations (slightly generalized), and derives a sequence-of-returns risk indicator from cumulative growth and SWR numbers:
https://www.cfapubs.org/doi/pdf/10.2469/faj.v73.n4.5

(not suitable for general consumption! it is quite heavy with equations and algebra!)

Post Reply