## Variable Percentage Withdrawal (VPW)

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

### Re: Variable Percentage Withdrawal

Just a quick thought about the "20%" cap, and extended lifetime, etc. I modeled it in my own Excel sheet, this seems to work fine.

Say we assume that whatever the age, we should account for a minimum of 10 years ahead of us. Then in the regular VPW calculation table (without any cap), take the life expectancy (say end-age = 100) minus 10 (i.e. minimum remaining lifetime to account for). Look up the corresponding withdrawal rate (in my example, with your market return assumptions, this is 11.5%). Now make a copy of the VPW table, capping withdrawals by 11.5% (instead of the old 20% cap), and use this 2nd table as reference in the backtesting math.

This is all an indirect way to convey a very simple idea: at 90 (and 91, and 92, etc), reset the VPW PMT math every year to account for 10 more years of spend, based on whatever is left of your capital by then.

This seems to give more proper semantics to such cap, derived from real-life, and also more directly linked to the core mechanics of the VPW model.

This would also allow to not make people enter overly long life expectancy numbers in the model because of the nagging question "but what if I live longer - and still want to make some annual gifts to my heirs?".

Finally, if you make the value "10" a parameter (well, this would be the input parameter replacing the old "20%" as the rest can be easily computed), one can force it to something else, including zero. And then we can use VPW as a strictly fixed-duration planning tool in a very intuitive manner, while ALSO allowing to use VPW as a lifelong tool.

PS. of course, such logic could be extended to use a full life expectancy table as a function of the current age, but then it's more work...

Posts: 62771
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

### Re: Variable Percentage Withdrawal

longinvest wrote:
LadyGeek wrote:- The conditional formatting rules are still there. I'm not sure what they are for.
I used black conditional formatting because I don't know how to make the cells disappear. The idea is that:
• Shiller's Historical Data doesn't have International returns. The US/International allocation is ignored when Shiller is selected. I use black formatting to show that this data is ignored.
• The retirement start year has no meaning, when Random data is selected. I use black formatting to show that the retirement year is ignored.
• There are a few cells that I change to red when the user inputs invalid values. Example: a Stock allocation higher than 100% or lower than 0%.
Do I really have to document this into the spreadsheet?
OK, now I understand. The idea is to let the user know that the data is invalid when the choice is selected. One option is to put a =if((condition),"<-- not used","") to the right of the cell, but that takes up too much room and looks awkward. Try this:

Instead of color or making it disappear, use border formatting to create an "X" across the cell. The user sees the data crossed out - the intent is clear. It's quite effective, there's no need to document.

Change the conditional formatting from "noir" to a new style: normal text, borders --> line arrangement --> select both diagonals to form an "X".
longinvest wrote:...It doesn't seem appropriate to put this stock split on the VPW Table sheet. I made an exception for the portfolio size, because you asked for it and it didn't matter much. I should probably put it back on the back-testing sheet.

I think that it is better to keep all back-testing-specific inputs out of the VPW Table sheet. The VPW Table sheet should remain as simple as possible. It only needs 2 pieces of data: Stock percentage in Asset Allocation, and Portfolio Depletion Age.
OK
longinvest wrote:
LadyGeek wrote:Global suggestion: It's very easy for someone to accidentally overwrite a cell formula. Change the cells needing user input to bold blue. I use this formatting technique a lot.
There's no danger of unwanted modifications; all cells are locked against modification, except the ones meant to be modified by the user. The modifiable cells have a green heading. I thought it became obvious once you start playing with it. It seems not.

I can try adding a special border to modifiable cells, just to make it even more obvious. Anyway, if you can't modify a cell, you can't overwrite its formula.
Good point. If I know how to disable the protection, I proceed at my own risk. Yes, the green was not obvious. Perhaps you can place a blank green cell at the top containing "User input" as a guide.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

LadyGeek wrote:OK, now I understand. The idea is to let the user know that the data is invalid when the choice is selected. One option is to put a =if((condition),"<-- not used","") to the right of the cell, but that takes up too much room and looks awkward. Try this:

Instead of color or making it disappear, use border formatting to create an "X" across the cell. The user sees the data crossed out - the intent is clear. It's quite effective, there's no need to document.

Change the conditional formatting from "noir" to a new style: normal text, borders --> line arrangement --> select both diagonals to form an "X".
I don't know about that... I actually like the way longinvest did it, this was very intuitive to me. I'd rather see a black cell than a number being crossed in red. As I would start asking myself plenty of weird questions otherwise, and this is just not necessary.

technovelist
Posts: 3276
Joined: Wed Dec 30, 2009 9:02 pm
Contact:

### Re: Variable Percentage Withdrawal

This sounds very interesting. Would it be hard to add an option for a gold allocation?
In theory, theory and practice are identical. In practice, they often differ.

Posts: 62771
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

### Re: Variable Percentage Withdrawal

siamond wrote:
LadyGeek wrote:Instead of color or making it disappear, use border formatting to create an "X" across the cell...
I don't know about that... I actually like the way longinvest did it, this was very intuitive to me. I'd rather see a black cell than a number being crossed in red. As I would start asking myself plenty of weird questions otherwise, and this is just not necessary.
Maybe black text on black to totally wipe it out, then. There's no need to belabor this minor point further, I'll leave it for longinvest. I appreciate the opportunity to offer a different perspective.

Update 21-Aug: I found the problem. In Linux / LibreOffice, the text is displayed as white - and is why I suggested the alternative border formatting. In Windows 7 / MS Excel, it works as siamond describes - black text on a black background.

MS Excel sets the conditional format text color to "automatic" which LibreOffice misinterprets as "white." I forced the conditional format text to "black" within MS Excel. LibreOffice is now displaying the text in black. Problem solved. (24-Aug: longinvest - to clarify, can you please put this in as a bug-fix? Thanks.)
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

boknows
Posts: 122
Joined: Mon Jan 28, 2013 3:11 pm
Location: Fairfax, VA
Contact:

### Re: Variable Percentage Withdrawal

Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
35 - Married - Aiming for FI/ER in early 40s.

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

boknows wrote:Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
Have you read the first post of this thread?....I think he explains his exact methodology in the calculation. I would expect that the percentage at age 40 would be the same, whether you were starting out or at a midpoint. Remember, it is the Percentage of available balance. It is looking at the Plan End rather than where you are at in the plan. Change the plan End and you'll see the Percentage vary with the same age.

boknows
Posts: 122
Joined: Mon Jan 28, 2013 3:11 pm
Location: Fairfax, VA
Contact:

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:
boknows wrote:Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
Have you read the first post of this thread?....I think he explains his exact methodology in the calculation. I would expect that the percentage at age 40 would be the same, whether you were starting out or at a midpoint. Remember, it is the Percentage of available balance. It is looking at the Plan End rather than where you are at in the plan. Change the plan End and you'll see the Percentage vary with the same age.
I read the whole thread, and have been studying the spreadsheet. I was just clarifying the formulas that I was reading. And since this process has evolved over the course of the thread, I was just making sure it was actually as I read it.
35 - Married - Aiming for FI/ER in early 40s.

rmark1
Posts: 359
Joined: Sat Jun 16, 2007 7:43 am

### Re: Variable Percentage Withdrawal

'JoMoney, I looked at the RMD tables. Wow! They are much less agressive than Canada's RRIF rules. RRIF rules force you to withdraw 7.38% of your portfolio at age 71 (up to 20% per year at age 94+).'

If you compare the tables in IRS publication 590, RMD essentially assumes joint life expectancy with a spouse 10 years younger.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

boknows wrote:Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
It is also a function of the 'expected returns' for the asset classes involved. And that part is a bit of tricky business for a User Interface like cFireSim. It wouldn't seem quite right to ask the end user about it. And yet, this is an important input parameter which changes the shape of the withdrawals (albeit not the end result, i.e. spend it all!).

As to the 'exact same calculation', this is true if we use VPW as a fixed-duration withdrawal plan. Now, you'll get something that starts changing in the last few years of an extended lifetime if you do something like what I suggested here: http://www.bogleheads.org/forum/viewtop ... 0#p1781593.

This is where what you seem to have in mind (use an algorithm, not a table) would indeed be valuable, as the formula would adjust itself based on the remaining life expectancy, whatever it is.

rmark1
Posts: 359
Joined: Sat Jun 16, 2007 7:43 am

### Re: Variable Percentage Withdrawal

You might want to look at Heblers simplest withdrawal plan at analyzenow.com

Divide portfolio annually over remaining IRS life expectancy, spending the lesser of the new annual division or the previous years division plus inflation.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

siamond wrote:
boknows wrote:Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
It is also a function of the 'expected returns' for the asset classes involved. And that part is a bit of tricky business for a User Interface like cFireSim. It wouldn't seem quite right to ask the end user about it. And yet, this is an important input parameter which changes the shape of the withdrawals (albeit not the end result, i.e. spend it all!).

As to the 'exact same calculation', this is true if we use VPW as a fixed-duration withdrawal plan. Now, you'll get something that starts changing in the last few years of an extended lifetime if you do something like what I suggested here: http://www.bogleheads.org/forum/viewtop ... 0#p1781593.

This is where what you seem to have in mind (use an algorithm, not a table) would indeed be valuable, as the formula would adjust itself based on the remaining life expectancy, whatever it is.
I was reflecting more on the choices to make for a nice user-friendly interface to a backtesting tool, and I suddenly realized something. We could simply use something like 'Initial Portfolio', 'Initial Spend' and 'Years to Model'. Exactly like Firecalc and co do for regular withdrawal models.

Initial Portfolio is obvious. Years to Model is straightforward, this would map to the (fixed) duration of the VPW plan (it really doesn't matter at what age you start).

Initial Spend is less obvious. Let me explain. One could compute an Initial WR from Initial Portfolio (say \$1M) and Initial Spend (say \$4k), this is 4%. Then use it to override the 'expected returns' math (which happens to usually be between 3% and 5% anyway). And then the end user can play around with the Initial Spend while doing a backtesting projection, and will see that the shape of the spend over the years will vary depending on the market gyrations, the AA used for backtesting market returns, and... the initial spend, of course.

Then it all becomes very intuitive, removing the need for any 'future expected returns' guess, removing all the business about life expectancy, while allowing the end user to pick an initial spend to his liking. Possibly more aggressive to begin with, or less aggressive, or whatever suits his/her fancy.

In an amusing reversal of thinking, the 'expected returns' math could then be viewed as an 'offline' advice of sorts to choose the initial spend. But for the average user, I would not bother, just let them play around with the usual 3% to 4% math. VPW auto-adjusts itself anyway.

PS. I modeled this idea in my own Excel sheet, a "VPW-Easy" model if you wish, works well. To make it even more intuitive, the spend of the very first year could be forced to be the exact initial spend, ignoring the VPW math for the 1st year. A bit artificial, and by no mean necessary.
Last edited by siamond on Sat Aug 24, 2013 9:46 am, edited 2 times in total.

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

LadyGeek wrote: Update 21-Aug: I found the problem. In Linux / LibreOffice, the text is displayed as white - and is why I suggested the alternative border formatting. In Windows 7 / MS Excel, it works as siamond describes - black text on a black background.

MS Excel sets the conditional format text color to "automatic" which LibreOffice misinterprets as "white." I forced the conditional format text to "black" within MS Excel. LibreOffice is now displaying the text in black. Problem solved. (24-Aug: longinvest - to clarify, can you please put this in as a bug-fix? Thanks.)
Thanks LadyGeek. OK, I'll fix this.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

boknows wrote:Just for some clarity: Is the VPW calculation simply a function of Age and AA? For instance, I unprotected the cells of your sheet and expanded the VPW table out to age 25-99, just for giggles, and it seems to give the exact same calculation at Age 40 as the table did when 40 was the starting point.

Just trying to figure out the exact Withdrawal Calculation and all it's variables, rather than rely on a static table.
Hi boknows.

Mathematically, VPW is based on a mortgage repayment schedule, given a number of years and an interest rate. The withdrawal percentages are computed by dividing the payment by the balance. This view of it might be appealing to mathematically inclined people, but it is probably a little too dry for the average investor.

In the spreadsheet, I use an end-of-plan age to automatically calculate a table that can be used at any prior age to get a withdrawal percentage for that age. I also ask for an asset allocation to calculate an approximate interest rate for the amortization schedule. This seems more intuitive for the average investor. I'm open for suggestions to improve that.

Remember that, when I first exposed my idea in this thread, I was met with much resistance to the idea. I used this new presentation to get people interested into looking deeper into the VPW idea.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

rmark1 wrote:You might want to look at Heblers simplest withdrawal plan at analyzenow.com

Divide portfolio annually over remaining IRS life expectancy, spending the lesser of the new annual division or the previous years division plus inflation.
The idea is neat. If I understand well, each year you calculate the withdrawal amount by selecting the minimum of two values:
• Your current portfolio balance divided by your life expectancy (in the RMD table).
• Last year's withdrawal amount increase by inflation.
It is a clear improvement over Constant-Dollar Withdrawal, by eliminating the possibility of failure. Unfortunately, it still suffers from a high likeliness to leave big pile of unspent money at the end.

Markets get really into low dips, sometimes. Locking-in the depressed inflation-adjusted spending amount of the dip forever afterward, as this method proposes, seems overly harsh, to me.

VPW requires the same high flexibility in spending, as the withdrawals follow the market into peaks and dips, but it leads to actually spending the whole capital over the selected period of time (while not failing before the end). The difference is that spending amounts grow back after the dips.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:
rmark1 wrote:You might want to look at Heblers simplest withdrawal plan at analyzenow.com

Divide portfolio annually over remaining IRS life expectancy, spending the lesser of the new annual division or the previous years division plus inflation.
The idea is neat. If I understand well, each year you calculate the withdrawal amount by selecting the minimum of two values:
• Your current portfolio balance divided by your life expectancy (in the RMD table).
• Last year's withdrawal amount increase by inflation.
Well, I was reading the write-up from the Autopilot author (Henry Hebeler): http://www.marketwatch.com/story/put-re ... 2013-07-24

And... he refers to the 'Planners' model. Which sounds quite close to what you suggested, longinvest, isn't it?

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

siamond wrote: I was reflecting more on the choices to make for a nice user-friendly interface to a backtesting tool, and I suddenly realized something. We could simply use something like 'Initial Portfolio', 'Initial Spend' and 'Years to Model'. Exactly like Firecalc and co do for regular withdrawal models.

Initial Portfolio is obvious. Years to Model is straightforward, this would map to the (fixed) duration of the VPW plan (it really doesn't matter at what age you start).

Initial Spend is less obvious. Let me explain. One could compute an Initial WR from Initial Portfolio (say \$1M) and Initial Spend (say \$4k), this is 4%. Then use it to override the 'expected returns' math (which happens to usually be between 3% and 5% anyway). And then the end user can play around with the Initial Spend while doing a backtesting projection, and will see that the shape of the spend over the years will vary depending on the market gyrations, the AA used for backtesting market returns, and... the initial spend, of course.

Then it all becomes very intuitive, removing the need for any 'future expected returns' guess, removing all the business about life expectancy, while allowing the end user to pick an initial spend to his liking. Possibly more aggressive to begin with, or less aggressive, or whatever suits his/her fancy.

In an amusing reversal of thinking, the 'expected returns' math could then be viewed as an 'offline' advice of sorts to choose the initial spend. But for the average user, I would not bother, just let them play around with the usual 3% to 4% math. VPW auto-adjusts itself anyway.

PS. I modeled this idea in my own Excel sheet, a "VPW-Easy" model if you wish, works well. To make it even more intuitive, the spend of the very first year could be forced to be the exact initial spend, ignoring the VPW math for the 1st year. A bit artificial, and by no mean necessary.
siamond, this is a brilliant idea.

It makes the inputs of the VPW calculation tab a little more complex (but probably easier than guessing an expected return), as we now have to ask for more numbers:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion age
It is still not so easy to select 2 and, specially, 4.

I've read your propositions to smooth plan end, including using RMD tables. Personally, I am far more in favor of my initial proposition (that I removed later, as it seemed to obstruct the presentation of the VPW idea). Here's what I was proposing: you select a depletion period and also a depletion percentage. So if you selected to deplete 50% of your portfolio over 30 years, the withdrawal percentages were adjusted based on applying VPW to 50% of the portfolio and applying Constant-Percentage on the other 50% (thus, preserving the long-term value of this portfolio portion, as long as the constant-percentage is not too aggressive).

At the risk of making the calculations a little more complex, I could modify the VPW Table sheet to ask for the following values:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion percentage
5. Depletion age
This would allow a person to get some comfort of not depleting the whole portfolio, in case she survives to the depletion age. Selecting a depletion percentage of 100% would be equivalent to pure VPW. Selecting a depletion percentage of 0% would be equivalent to pure Constant-Percantage Withdrawal.

What do you think?
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:siamond, this is a brilliant idea.

It makes the inputs of the VPW calculation tab a little more complex (but probably easier than guessing an expected return), as we now have to ask for more numbers:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion age
It is still not so easy to select 2 and, specially, 4.
Thanks. Yes, I've been circling around such views from the very beginning, but it *finally* gelled in my mind this morning, triggered by Bo's questions.

What I was saying was that all you need to ask is:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement DURATION (aka 'Years to Model')
I believe we can forget about the retirement age and depletion age, as only the delta between those two matters. I carefully checked my assumption with the PMT formula this morning, and I believe this is correct (as cut-throat put it as some point in time, VPW at its core is essentially a fixed duration withdrawal plan).

Now I agree with you that it is tricky to select the initial withdrawal amount. Like in any withdrawal model. The beauty of VPW though is that, whatever you choose, you get the same end result (spend all your money). Now I believe that you were playing around with an expected market return because you felt that, ON AVERAGE, this would make the returns more evenly distributed over the retirement years. Trouble is VPW being a direct function of market gyrations, the spend over the retirement period is always quite hectic, and such average perspective becomes pretty much a moot point. Is that a fair statement?

The other thing is that, by letting the end user choose an initial spend, they can more freely choose to spend more money in the early years, or the latter years, or whatever skew derived from their personal goals would call for. So... overall... It seems simpler to let the end user choose his initial spend (or WR), and also more powerful in a way! Nice double-kicker, isn't it?

PS. this being said, don't lose all the work and wisdom you put in the expected return formula. I'd say, just make it advisory in your Excel sheet...
Last edited by siamond on Sat Aug 24, 2013 1:29 pm, edited 1 time in total.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:I've read your propositions to smooth plan end, including using RMD tables. Personally, I am far more in favor of my initial proposition (that I removed later, as it seemed to obstruct the presentation of the VPW idea). Here's what I was proposing: you select a depletion period and also a depletion percentage. So if you selected to deplete 50% of your portfolio over 30 years, the withdrawal percentages were adjusted based on applying VPW to 50% of the portfolio and applying Constant-Percentage on the other 50% (thus, preserving the long-term value of this portfolio portion, as long as the constant-percentage is not too aggressive).

At the risk of making the calculations a little more complex, I could modify the VPW Table sheet to ask for the following values:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion percentage
5. Depletion age
This would allow a person to get some comfort of not depleting the whole portfolio, in case she survives to the depletion age. Selecting a depletion percentage of 100% would be equivalent to pure VPW. Selecting a depletion percentage of 0% would be equivalent to pure Constant-Percentage Withdrawal.

What do you think?
Now that I properly understood what you meant... --- sorry for those of you who read my first -inadequate- reaction to the smoothing point.

When you said 'Constant-Percentage', you meant CDW, not CPW, right? Let me assume this is the case, otherwise, there is no smoothing whatsoever!

Ok, I gave it a try. I quickly figured out that this is indeed providing a nice smoothing effect, but with a steep peak in the last few years of retirement. So I tweaked my PMT formula to impose a 10 years minimum on the remaining lifetime (as discussed in a previous post). Ah, this is better.

Well, from the various cycles I went through, the results are indeed quite good. The spending stays variable, of course, but much less hectic. The end portfolio does remain significant, with a fairly variable level of depletion, depending on the starting year. And when playing with the depletion %, you do get more spend vs less portfolio (or the reverse way around), as expected.

I think you are onto something here... One might argue this is closer to a retirement plan than a withdrawal method , but... fact is this seems to work quite well.

PS. now I'm pondering what would happen when combining VPW with my 'retire again & again' method...

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

longinvest wrote: It makes the inputs of the VPW calculation tab a little more complex (but probably easier than guessing an expected return), as we now have to ask for more numbers:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion age
It is still not so easy to select 2 and, specially, 4.
So, it appears that by adding an initial withdrawal amount negates the 'need' for a 'valuation type of backtesting'. Correct? In other words you adjust your initial withdrawal percentage to smooth out your withdrawals, no matter what the valuations are.
Correct? or am I misunderstanding this?

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:
longinvest wrote: It makes the inputs of the VPW calculation tab a little more complex (but probably easier than guessing an expected return), as we now have to ask for more numbers:
1. Portfolio size
2. Initial withdrawal amount
3. Retirement age
4. Depletion age
It is still not so easy to select 2 and, specially, 4.
So, it appears that by adding an initial withdrawal amount negates the 'need' for a 'valuation type of backtesting'. Correct? In other words you adjust your initial withdrawal percentage to smooth out your withdrawals, no matter what the valuations are.
Correct? or am I misunderstanding this?
Yes, you are correct. The more aggressive the initial withdrawal rate, the more you spend sooner. The less aggressive the initial withdrawal rate, the more the chance of high late withdrawals.

You still need back-testing to get a feeling of your choice on the shape of withdrawals over time. But, at least, we eliminate one level of complexity (guessing an expected return).

Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

longinvest wrote:
Yes, you are correct. The more aggressive the initial withdrawal rate, the more you spend sooner. The less aggressive the initial withdrawal rate, the more the chance of high late withdrawals.

You still need back-testing to get a feeling of your choice on the shape of withdrawals over time. But, at least, we eliminate one level of complexity (guessing an expected return).

Excellent !!...... I didn't like 'Expected return' or my 'Valuation Idea' either. Congrats to siamond for coming up with this!

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

siamond wrote: When you said 'Constant-Percentage', you meant CDW, not CPW, right? Let me assume this is the case, otherwise, there is no smoothing whatsoever!
Hi siamond,

Unfortunately, no. I did mean CPW. CDW does not makes sense to me, even here. But, I agree with your previous message: VPW is there to spend the money over the selected time. Let's not try to do more than this.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

longinvest wrote: Selecting a depletion percentage of 0% would be equivalent to pure Constant-Percantage Withdrawal.
I don' think this statement is correct. Just for the record, while the portfolio is never completely depleted, it is certainly not 0%.

0% Depletion would be only spending the real growth of the portfolio, Correct?

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Back to the smoothing idea... Not that I am fully convinced we should mix up methods like that, but hey, let's play around...

Here are some charts... I hope I didn't get mixed up somewhere.

I compared the regular VPW method (fixed duration 30 years) with a combined VPW and CDW (depletion % of 33% - seemed to give better results). While I was at it, I also included the pure CDW numbers and the Best-of-Worst method.

I used a 75/25 (stock/bond) portfolio. The returns might be a tad different than the ones used by longinvest though (due to another thing I was experimenting with).

I used a 3.5% initial WR and a 30 years retirement period. But, er... the regular VPW math actually forces the first few withdrawals at 5.25% and more when using an expected market return of 3.5%. Er, I was using longer retirement periods this morning, and I had less of a delta. Hm, need to rethink this part a bit. Later!

Here is the outcome for 1960. Monthly spend and portfolio value.

Here is the outcome for 1980. Monthly spend and portfolio value.

And no, I had absolutely no idea that the VPW+CDW combo would behave that close to BOW. Surprise, surprise! It is really a side effect of my choice of a depletion % of 33% though. I tried 50% and then the surprising alignment disappears.

PS. I also tried a combo VPW+BOW. This isn't actually as satisfying as I was expecting. CDW is a better 'smoother'.
Last edited by siamond on Sat Aug 24, 2013 11:30 pm, edited 1 time in total.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:
siamond wrote: When you said 'Constant-Percentage', you meant CDW, not CPW, right? Let me assume this is the case, otherwise, there is no smoothing whatsoever!
Hi siamond,

Unfortunately, no. I did mean CPW. CDW does not makes sense to me, even here. But, I agree with your previous message: VPW is there to spend the money over the selected time. Let's not try to do more than this.
I don't understand what you were trying to smooth, then? The post-retirement time? Didn't you guys convince me that it was a bad idea (yes, you did!) ?

Well, I did come up with another smoothing idea then! Cf. previous post. But I wouldn't push for it...

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

siamond wrote: I don't understand what you were trying to smooth, then? The post-retirement time? Didn't you guys convince me that it was a bad idea (yes, you did!) ?
I am not sure who 'You guys' are, but I was for smoothing in the sense that I did not want to see 50% drops in withdrawals. I was OK with a 25% drop however.

I did not want smoothing such as a Constant Dollar gives you, which is perfectly smooth. But of course, can fail.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:
longinvest wrote:Yes, you are correct. The more aggressive the initial withdrawal rate, the more you spend sooner. The less aggressive the initial withdrawal rate, the more the chance of high late withdrawals.

You still need back-testing to get a feeling of your choice on the shape of withdrawals over time. But, at least, we eliminate one level of complexity (guessing an expected return).

Excellent !!...... I didn't like 'Expected return' or my 'Valuation Idea' either. Congrats to siamond for coming up with this!

Thank you for the nice words, but... I'll beat myself here.

I just figured out that this may not be as simple as I was hoping for. I made a dubious leap of faith. I assumed that the Initial WR people tend to use (e.g. 3.5% or 4%, initial-spend/initial-portfolio) could be equated to a conservative view of future market returns (I think this part is ok). And then I assumed it would be 'close enough' to the first withdrawal VPW computes if we equate 'expected returns' with such IWR, and that the VPW variability would quickly hide the difference. And although the latter appears indeed reasonably true for long retirement periods, it is LESS true for a 30 years period. Which makes sense now that I thought more about it (cf. VPW 'spend it all' goal). Aarg.

We can 'hide' the issue by forcing the first withdrawal to be as the end user intended, but... Yeah, I know, I am cheating. Although it would kinda work...
Cut-Throat wrote:
siamond wrote:I don't understand what you were trying to smooth, then? The post-retirement time? Didn't you guys convince me that it was a bad idea (yes, you did!) ?
I am not sure who 'You guys' are, but I was for smoothing in the sense that I did not want to see 50% drops in withdrawals. I was OK with a 25% drop however.

I did not want smoothing such as a Constant Dollar gives you, which is perfectly smooth. But of course, can fail.
Well, then maybe a mix like I just showed on my last charts would be of interest for you then. Although... I have my own reservations... And I suspect you will too.

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

siamond wrote: I just figured out that this may not be as simple as I was hoping for. I made a dubious leap of faith. I assumed that the Initial WR people tend to use (e.g. 3.5% or 4%, initial-spend/initial-portfolio) could be equated to a conservative view of future market returns (I think this part is ok). And then I assumed it would be 'close enough' to the first withdrawal VPW computes if we equate 'expected returns' with such IWR, and that the VPW variability would quickly hide the difference. And although the latter appears indeed reasonably true for long retirement periods, it is LESS true for a 30 years period. Which makes sense now that I thought more about it (cf. VPW 'spend it all' goal). Aarg.
Not sure what you mean here. Why don't you try a 70% Bond 30% Stock \$1 Million Portfolio at age 63 going to age 100, Starting with a 3.4% withdrawal Rate for the year 1937 and show your results in a table.

I am very familiar with that year, as it is not particularly kind.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:Not sure what you mean here. Why don't you try a 70% Bond 30% Stock \$1 Million Portfolio at age 63 going to age 100, Starting with a 3.4% withdrawal Rate for the year 1937 and show your results in a table.

I am very familiar with that year, as it is not particularly kind.
Yes, sir. Here you are (I have the charts handy, not the table, tell me if you really want the table with all numbers). Yup, not a nice year indeed.

Here is the spend - oh by the way, this is the monthly spend. Sorry, this is the way my Excel sheet is set up.

This actually shows my problem nicely. See the delta between the very first spend of VPW and the very first spend of CDW? A tad too significant.

Remember, I had suggested to start from the spend somebody would enter in Firecalc or equivalent, in your case 3.4% of \$1M (annually), and then use that in lieu of the 'expected returns'. Worded like that, this actually works, but the bad part is that the first VPW spend (withdrawal) is quite different. And then we partly lose the intuitive factor I was looking for. Shoot.

Then here is the chart where I 'cheated', forcing the first withdrawal to be the exact amount implied by the 3.4% factor. The funny thing is that it looks like a perfectly reasonable plan to me. But strongly engineering-minded people might balk at my 'cheating'... Although it's hard to notice...

Last edited by siamond on Sat Aug 24, 2013 6:19 pm, edited 3 times in total.

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

Thanks !

I think there is a problem unless I am looking at the Graphs wrong.

A 3.4% Withdrawal rate on a \$1 Million Portfolio would give you \$34K a year. Monthly about \$2,800. Your graph looks Well over \$3,500 to start.

What I am looking for is a Withdrawal Rate that would not exceed a 25% drop from the start. I can fiddle with the 'Expected Returns' on the existing VPW spreadsheet and can accomplish this with a .5% Bond return and a 3% Stock Return. This gives a 3.4% Initial Withdrawal.

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

OK, the last graph is what I had in mind...... We were posting at the same time.

Although my table shows an Immediate decline from 1937, while yours show a 'Bump in the first few years following.
Last edited by Cut-Throat on Sat Aug 24, 2013 5:48 pm, edited 1 time in total.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:Thanks !

I think there is a problem unless I am looking at the Graphs wrong.

A 3.4% Withdrawal rate on a \$1 Million Portfolio would give you \$34K a year. Monthly about \$2,800. Your graph looks Well over \$3,500 to start.

What I am looking for is a Withdrawal Rate that would not exceed a 25% drop from the start. I can fiddle with the 'Expected Returns' on the existing VPW spreadsheet and can accomplish this with a .5% Bond return and a 3% Stock Return. This gives a 3.4% Initial Withdrawal.
I had a small mix-up between 3.4% and 3.5%, will fix in a minute. (EDIT: this is fixed now)

Look at the black line, it is indeed at \$2800. This is the CDW model. The disconnect between the initial CDW value and the initial VPW value is my 'user friendliness' problem indeed. I used 3.4% as expected market return in VPW. But this gives a 4.63% first withdrawal with the regular VPW math. Shesh, I feel stupid now.
Last edited by siamond on Sat Aug 24, 2013 5:59 pm, edited 1 time in total.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Cut-Throat wrote:OK, the last graph is what I had in mind...... We were posting at the same time.

Although my table shows an Immediate decline from 1937, while yours show a 'Bump in the first few years following.
Yeah, the returns I have in my Excel sheet are not (currently) aligned with the ones longinvest uses. Although supposedly both sources are derived from Shiller numbers. Go figure.

Anyhoo, did I succeed to explain my issue clearly now? Sorry, been looking at those numbers most of the day, my eyes are starting to cross each other...

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Back to the 'smooth' discussion...
siamond wrote:
longinvest wrote:
rmark1 wrote:You might want to look at Heblers simplest withdrawal plan at analyzenow.com

Divide portfolio annually over remaining IRS life expectancy, spending the lesser of the new annual division or the previous years division plus inflation.
The idea is neat. If I understand well, each year you calculate the withdrawal amount by selecting the minimum of two values:
• Your current portfolio balance divided by your life expectancy (in the RMD table).
• Last year's withdrawal amount increase by inflation.
Well, I was reading the write-up from the Autopilot author (Henry Hebeler): http://www.marketwatch.com/story/put-re ... 2013-07-24
[...]
I finally read the article from Mr Hebeler in more details, and was amused to find out that his latest 'autopilot' method is basically taking 50% from the regular CDW method (the ultimate 'smooth' method) and 50% from the RMD method (which varies a lot depending on the market returns).

Sounds that we re-invented the wheel a little bit when pondering to combine VPW and CDW in a similar way. This actually makes me reconsider that, although it sounds complicated to combine two methods, there might be some virtue to it when one is highly variable and the other one is not.

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

OK, I decided to reorganize the spreadsheet to make it easier to backtest and compare withdrawal methods. It's not completely finalized, yet, so there are missing texts. Here is a preliminary version: http://bit.ly/159tFg7
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:OK, I decided to reorganize the spreadsheet to make it easier to backtest and compare withdrawal methods. It's not completely finalized, yet, so there are missing texts. Here is a preliminary version: http://bit.ly/159tFg7
Longinvest, this sounds good. Could you please post the Excel version? Thanks!

papiper
Posts: 128
Joined: Tue May 28, 2013 10:36 am

### Re: Variable Percentage Withdrawal

I'm really impressed with the thought and capabilities watching this thread evolve. I am concerned that the model is slowly morphing into a smoothed conservative outlook that becomes weighted with worrying about bad market timing and risk. We already have several of those to choose from.

For those of us that could handle yearly swings of withdrawal, the initial model finally gave workable guidance of what actual percentage to plan each year by age. For people who can't risk that - it's not the correct model. But for those that can it's the best (and about only) model I've seen in it's original form.

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

Don't know how longinvest proceeded in his updated worksheet, but I think I found the solution to my problem this morning in the shower!

I was still too stuck in the previous model, trying to use an expected rate of return. If we completely reverse the reasoning, start from the initial WR and first withdrawal, we can back-compute the rate to be provided to the PMT formula (without giving it special semantics). To do so, we can use the corresponding Excel formula, i.e. RATE!

As an example: RATE(37,-34000, 1000000, 0, 1, 3.4%) -- using cut-throat's example.

And then it all works much better (without cheating!). The charts now look quite different, and actually make much more sense to me, as the algorithm reacts by constraining spend in the tough early years, then frees up the spend, and ends up spending it all (while the classic CDW model kind of works, except that its portfolio value is not supposed to crash and burn like that, but this is a consequence of choosing an especially tough year like 1937).

Then, just for kicks, I used 4.0% as the initial WR. Here is the spend - note how CDW miserably fails way too early!

For more regular years, increasing the initial WR skews the spend towards the early years, and decreasing it skews the spend towards the latter years. And it all beautifully auto-adjusts whatever happens. Pretty cool. Albeit still highly variable!

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

papiper wrote:I'm really impressed with the thought and capabilities watching this thread evolve. I am concerned that the model is slowly morphing into a smoothed conservative outlook that becomes weighted with worrying about bad market timing and risk. We already have several of those to choose from.

For those of us that could handle yearly swings of withdrawal, the initial model finally gave workable guidance of what actual percentage to plan each year by age. For people who can't risk that - it's not the correct model. But for those that can it's the best (and about only) model I've seen in it's original form.
I guess this is because we're proceeding on two threads at the same time here.
- The first line of thinking does follow very exactly what you are looking for; it just attempts to make it simpler by using input parameters that everybody would understand and easily compare to more 'classic' methods.
- While the second line of thinking is indeed more conservative by mixing the highly-variable method (VPW) with a highly-smooth method (CDW)

Both lines of thinking actually complement each other imho.

Now I do agree with you that you shouldn't lose the thinking about the guidance for the % to use. I believe it's good to let people experiment (or tune to their own goals) by varying the Initial WR, but some back-computation derived from the expected rate of return analysis, provided as an advice (not an input parameter) would seem in order. My 2 cents!
Last edited by siamond on Sun Aug 25, 2013 10:03 am, edited 2 times in total.

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

longinvest wrote:OK, I decided to reorganize the spreadsheet to make it easier to backtest and compare withdrawal methods. It's not completely finalized, yet, so there are missing texts. Here is a preliminary version: http://bit.ly/159tFg7
Great Work! I Like it a lot. It lets the user control the Dips for the backtesting very easily. I like all of the Graphs combined also. Nice!

One small suggestion. I like how you have arranged all of the inputs on the BackTest Page. And while I understand why you put the Starting age, Depletion age and Initial Withdrawal on the 'VPW Calculation Page, I think it would be a bit easier to understand with those inputs on the backtesting page as well. All of the inputs on 1 page, IOW. I think there may even be room to put the VPW Table at the bottom of the Backtest Page and the Inputs below your BackTest Inputs. That way the user could just change the inputs to VPW and immediately see the results on the same page, without having to flip back and forth.

A very Nice tool Indeed!

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

I just figured out that longinvest did very exactly the same thing as I did... Using the RATE function to initialize the whole thing. Nice. We're getting somewhere.

(and agreed with the formatting comments cut-throat made; I would also suggest to avoid the 'depletion' verbiage as this is only a property of VPW, not the other methods - entering the retirement duration would work much better, me think...)

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

Hi,

I have decided to preserve simplicity while adding flexibility by implementing 2 versions of VPW in the latest spreadsheet:
1. a plain VPW using asset allocation as guide for deriving withdrawal percentages (longinvest's recommended way), and
2. a Custom VPW where the user can tweak the withdrawals by specifying an initial withdrawal rate.
In the Custom VPW it's much easier to let the user play with the initial withdrawal rate than explain the concept of internal amortization rate. Thanks siamond for the terrific idea.

Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Posts: 62771
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

### Re: Variable Percentage Withdrawal

In 'Backtesting' C3, which is the Initial Portfolio value, enter \$999. The payments are broken.

I traced the problem to the embedded ROUND() in 'Details' D5:M40 - the payment calculations. This appears to be a truncation error. Since the formulas have no dollar limitation, I suggest removing ROUND() and modifying the cell's display format instead. Truncation error effects can be subtle - you will never find them until it is too late. So, I think removal of ROUND() is important.

I fixed my own copy by temporarily changing ROUND(..., 0) to ROUND(..., 10), i.e. round to 10 places instead of 0. In MS Excel, find ",0)" and replace with ",10)".

Rounding also occurs in CVPW and Calculation. The ROUND() should also be removed from here, I think.

================================
Administrative suggestion (UI): To help with chart labeling (and be easier to read), I recommend changing the acronym headings in 'Details' to a descriptive legend. A from/to:

Code: Select all

``````VPW            CDW                CPW          1/N               CVPW
Variable %		Constant \$		Constant %		1/N		Custom Variable %``````
You'll need to widen the chart.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

LadyGeek wrote:In 'Backtesting' C3, which is the Initial Portfolio value, enter \$999. The payments are broken.

I traced the problem to the embedded ROUND() in 'Details' D5:M40 - the payment calculations. This appears to be a truncation error. Since the formulas have no dollar limitation, I suggest removing ROUND() and modifying the cell's display format instead. Truncation error effects can be subtle - you will never find them until it is too late. So, I think removal of ROUND() is important.

I fixed my own copy by temporarily changing ROUND(..., 0) to ROUND(..., 10), i.e. round to 10 places instead of 0. In MS Excel, find ",0)" and replace with ",10)".

Rounding also occurs in CVPW and Calculation. The ROUND() should also be removed from here, I think.

The ROUND()s were added by design in the spreadsheet. I round percentages to the nearest .1% and all \$ withdrawal amounts to the nearest \$1000.

This was done in response of being accused of false precision. We are modeling the withdrawal of thousands of dollars out of a portfolio using historical data that doesn't even provide for enough accuracy (transactions costs, spread, taxes, etc.). If rounding a few dollars out or in (during withdrawals) breaks the plan, then there's no plan. Note that I keep more precision tracking portfolio returns (I round to the nearest 1\$, much more precise than withdrawals).

The Backtesting sheet cannot model the withdrawal out of pocket money (a few thousand dollars), only out of a portfolio (hundred of thousand dollars). I could do as you suggest, but I somehow like the current intentional imprecision; it is a philosophical message to VPW users.
================================
Administrative suggestion (UI): To help with chart labeling (and be easier to read), I recommend changing the acronym headings in 'Details' to a descriptive legend. A from/to:

Code: Select all

``````VPW            CDW                CPW          1/N               CVPW
Variable %		Constant \$		Constant %		1/N		Custom Variable %``````
You'll need to widen the chart.
Good idea. I've improved the abbreviations in a new upload.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

Here are the results for a very challenging retirement year: 1966.

Here are the basic parameters:

The Backtesting sheet of the Variable Percentage Withdrawal spreadsheet gives us the following comparison:

Fixed broken image links on March 17, 2020.
Last edited by longinvest on Tue Mar 17, 2020 6:46 pm, edited 1 time in total.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:
LadyGeek wrote:Since the formulas have no dollar limitation, I suggest removing ROUND() and modifying the cell's display format instead. Truncation error effects can be subtle - you will never find them until it is too late. So, I think removal of ROUND() is important.

The ROUND()s were added by design in the spreadsheet. I round percentages to the nearest .1% and all \$ withdrawal amounts to the nearest \$1000.

This was done in response of being accused of false precision. We are modeling the withdrawal of thousands of dollars out of a portfolio using historical data that doesn't even provide for enough accuracy (transactions costs, spread, taxes, etc.). If rounding a few dollars out or in (during withdrawals) breaks the plan, then there's no plan. Note that I keep more precision tracking portfolio returns (I round to the nearest 1\$, much more precise than withdrawals).

The Backtesting sheet cannot model the withdrawal out of pocket money (a few thousand dollars), only out of a portfolio (hundred of thousand dollars). I could do as you suggest, but I somehow like the current intentional imprecision; it is a philosophical message to VPW users.
I actually meant to make a similar suggestion, remove the ROUND functions, and simply use the cell formatting to display whole dollars (and a very small number of decimals when appropriate, e.g. market returns, percentages).

I get the point about false precision, but anyway, there are many reasons for which any exact amount (whether rounded or not) will NOT be what happens in real life. Everything you listed, plus also the side-effects of the underlying rebalancing assumption (that one might end up with a rounding factor to the next thousand dollars or even more! or no rounding at all, deliberating letting an asset not exactly match the AA when 'close enough'), etc. So I don't quite see that rounding brings us any closer to real life, frankly.

I do see drawbacks (speaking out of experience here! ):
- this makes the formulas harder to understand - the RATE/PMT concept is already tricky enough to understand, plus the current approach of fitting the entire algorithm in one cell, this all begs for NOT increasing formula's complexity
- this makes the outcome harder to compare with another implementation (Excel-based or some program ala Firecalc) - I actually struggled with that, comparing my own Excel sheet to an earlier version of yours and struggling to find the same results - hard to distinguish a true error from side-effects of ROUND formulas.

So... +1 for Ladygeek's suggestion here. As long as the numbers display do not show silly decimals, this should be fine.

Topic Author
longinvest
Posts: 4243
Joined: Sat Aug 11, 2012 8:44 am

### Re: Variable Percentage Withdrawal

siamond wrote:
I actually meant to make a similar suggestion, remove the ROUND functions, and simply use the cell formatting to display whole dollars (and a very small number of decimals when appropriate, e.g. market returns, percentages).

I get the point about false precision, but anyway, there are many reasons for which any exact amount (whether rounded or not) will NOT be what happens in real life. Everything you listed, plus also the side-effects of the underlying rebalancing assumption (that one might end up with a rounding factor to the next thousand dollars or even more! or no rounding at all, deliberating letting an asset not exactly match the AA when 'close enough'), etc. So I don't quite see that rounding brings us any closer to real life, frankly.

I do see drawbacks (speaking out of experience here! ):
- this makes the formulas harder to understand - the RATE/PMT concept is already tricky enough to understand, plus the current approach of fitting the entire algorithm in one cell, this all begs for NOT increasing formula's complexity
- this makes the outcome harder to compare with another implementation (Excel-based or some program ala Firecalc) - I actually struggled with that, comparing my own Excel sheet to an earlier version of yours and struggling to find the same results - hard to distinguish a true error from side-effects of ROUND formulas.

So... +1 for Ladygeek's suggestion here. As long as the numbers display do not show silly decimals, this should be fine.
Hi siamond,

You're looking at it from a different perspective than me. The drawbacks you list are for people like you and LadyGeek, people that can (and probably will) re-implement all the calculations in their own spreadsheet.

On the other hand, if you take a pencil and paper person that simply reads the numbers in the VPW Table and tries to confirm that the shown calculations are correct, then my approach is the right one. Also, I do think that it is quite likely that the person will withdraw a rounded amount out of the portfolio (\$ 39,000 instead of \$ 38,838.43). The difference won't have a perceptible impact on final results.

Even me, I like to confirm the calculations using a simple desk calculator, because it is so easy to screw up a formula in a spreadsheet. The rounding is really helpful for that.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Cut-Throat
Posts: 2011
Joined: Sun Oct 17, 2010 9:46 am

### Re: Variable Percentage Withdrawal

longinvest wrote: The ROUND()s were added by design in the spreadsheet. I round percentages to the nearest .1% and all \$ withdrawal amounts to the nearest \$1000.

This was done in response of being accused of false precision. We are modeling the withdrawal of thousands of dollars out of a portfolio using historical data that doesn't even provide for enough accuracy (transactions costs, spread, taxes, etc.). If rounding a few dollars out or in (during withdrawals) breaks the plan, then there's no plan. Note that I keep more precision tracking portfolio returns (I round to the nearest 1\$, much more precise than withdrawals).

The Backtesting sheet cannot model the withdrawal out of pocket money (a few thousand dollars), only out of a portfolio (hundred of thousand dollars). I could do as you suggest, but I somehow like the current intentional imprecision; it is a philosophical message to VPW users.
Completely Agree with your Philosophy. Not rounding would just make the results harder to read. And when Throwing a Hand Grenade, it does not matter whether you hit the Bullseye or were 1/16 of an inch off!

For myself, who is actually planning on implementing the withdrawal outputs of this tool in about 4 months, the real value is the percentage of withdrawal amounts generated by this tool. Precision to 1/10 of a percent is perfect! I have taken these Percentage Values and copied them into my own spreadsheet. I have run various scenarios. Taken the Worst Case Year for my 70% Bond 30% Stock Portfolio in the Backtesting portion. And am confident that I have a withdrawal strategy that cannot fail. I have actually upped my withdrawal amount by almost a half of a percent after using this tool.

Using the "2.5% is the New SWR" is quite archaic compared to this tool. 2.5% may very well fail. This tool cannot !

Thanks longinvest for all your hardwork! .... I'll think of you when I'm in the Bahamas with a Cold One This winter!

siamond
Posts: 5398
Joined: Mon May 28, 2012 5:50 am

### Re: Variable Percentage Withdrawal

longinvest wrote:You're looking at it from a different perspective than me. The drawbacks you list are for people like you and LadyGeek, people that can (and probably will) re-implement all the calculations in their own spreadsheet.

On the other hand, if you take a pencil and paper person that simply reads the numbers in the VPW Table and tries to confirm that the shown calculations are correct, then my approach is the right one. Also, I do think that it is quite likely that the person will withdraw a rounded amount out of the portfolio (\$ 39,000 instead of \$ 38,838.43). The difference won't have a perceptible impact on final results.

Even me, I like to confirm the calculations using a simple desk calculator, because it is so easy to screw up a formula in a spreadsheet. The rounding is really helpful for that.
Yeah, two different perspectives depending on the end user indeed... Seems to me we should leave to user-friendly tools like Firecalc and co the task of (optionally) providing rounded numbers, while using simpler and more precise formulas in Excel sheets, but hey, no big deal, you're the one managing this Excel sheet, it's your call! I know that once it's fully stable, the first thing I'll do is to make a copy and remove the "ROUND" formulas, but that's just me...