Variable Percentage Withdrawal (VPW)
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 endage = 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 reallife, 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 fixedduration 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...
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 endage = 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 reallife, 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 fixedduration 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...
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 backtesting sheet.
I think that it is better to keep all backtestingspecific 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.
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.

 Posts: 2875
 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.
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 21Aug: 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. (24Aug: longinvest  to clarify, can you please put this in as a bugfix? Thanks.)
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 2599, 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.
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.
 CutThroat
 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 2599, 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.
Re: Variable Percentage Withdrawal
CutThroat 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 2599, 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.
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.
If you compare the tables in IRS publication 590, RMD essentially assumes joint life expectancy with a spouse 10 years younger.
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 2599, 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 fixedduration 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/viewtopic.php?f=10&t=120430&p=1784020#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.
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.
Divide portfolio annually over remaining IRS life expectancy, spending the lesser of the new annual division or the previous years division plus inflation.
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 2599, 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 fixedduration 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/viewtopic.php?f=10&t=120430&p=1784020#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 userfriendly 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 autoadjusts itself anyway.
PS. I modeled this idea in my own Excel sheet, a "VPWEasy" 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.

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: Variable Percentage Withdrawal
LadyGeek wrote:Update 21Aug: 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. (24Aug: longinvest  to clarify, can you please put this in as a bugfix? Thanks.)
Thanks LadyGeek. OK, I'll fix this.
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR

 Posts: 2518
 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 2599, 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 endofplan 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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR

 Posts: 2518
 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 ConstantDollar 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. Lockingin the depressed inflationadjusted 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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
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 writeup from the Autopilot author (Henry Hebeler): http://www.marketwatch.com/story/putretirementsavingswithdrawalsonautopilot20130724
And... he refers to the 'Planners' model. Which sounds quite close to what you suggested, longinvest, isn't it?

 Posts: 2518
 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 userfriendly 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 autoadjusts itself anyway.
PS. I modeled this idea in my own Excel sheet, a "VPWEasy" 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:
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion age
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 ConstantPercentage on the other 50% (thus, preserving the longterm value of this portfolio portion, as long as the constantpercentage 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:
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion percentage
 Depletion age
What do you think?
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
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:It is still not so easy to select 2 and, specially, 4.
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion age
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.
Let's discuss without the 'smoothing' ideas to start with.
What I was saying was that all you need to ask is:
 Portfolio size
 Initial withdrawal amount
 Retirement DURATION (aka 'Years to Model')
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 doublekicker, 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.
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 ConstantPercentage on the other 50% (thus, preserving the longterm value of this portfolio portion, as long as the constantpercentage 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: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 ConstantPercentage Withdrawal.
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion percentage
 Depletion age
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 'ConstantPercentage', 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...
 CutThroat
 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:It is still not so easy to select 2 and, specially, 4.
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion age
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?

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: Variable Percentage Withdrawal
CutThroat 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:It is still not so easy to select 2 and, specially, 4.
 Portfolio size
 Initial withdrawal amount
 Retirement age
 Depletion age
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 backtesting 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).
The spreadsheet will be ready soon, to experiment with this idea.
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
 CutThroat
 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 backtesting 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).
The spreadsheet will be ready soon, to experiment with this idea.
Excellent !!...... I didn't like 'Expected return' or my 'Valuation Idea' either. Congrats to siamond for coming up with this!
I'll try it out as soon as you download it!

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: Variable Percentage Withdrawal
siamond wrote:When you said 'ConstantPercentage', 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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
 CutThroat
 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 ConstantPercantage 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?
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 BestofWorst 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'.
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 BestofWorst 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.
Re: Variable Percentage Withdrawal
longinvest wrote:siamond wrote:When you said 'ConstantPercentage', 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 postretirement 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...
 CutThroat
 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 postretirement 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.
Re: Variable Percentage Withdrawal
CutThroat 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 backtesting 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).
The spreadsheet will be ready soon, to experiment with this idea.
Excellent !!...... I didn't like 'Expected return' or my 'Valuation Idea' either. Congrats to siamond for coming up with this!
I'll try it out as soon as you download it!
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%, initialspend/initialportfolio) 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...
CutThroat wrote:siamond wrote:I don't understand what you were trying to smooth, then? The postretirement 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.
 CutThroat
 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%, initialspend/initialportfolio) 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.
Re: Variable Percentage Withdrawal
CutThroat 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 engineeringminded 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.
 CutThroat
 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.
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.
 CutThroat
 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.
Although my table shows an Immediate decline from 1937, while yours show a 'Bump in the first few years following.
Last edited by CutThroat on Sat Aug 24, 2013 5:48 pm, edited 1 time in total.
Re: Variable Percentage Withdrawal
CutThroat 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 mixup 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.
Re: Variable Percentage Withdrawal
CutThroat 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...
Re: Variable Percentage Withdrawal
Back to the 'smooth' discussion...
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 reinvented 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.
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 writeup from the Autopilot author (Henry Hebeler): http://www.marketwatch.com/story/putretirementsavingswithdrawalsonautopilot20130724
[...]
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 reinvented 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.

 Posts: 2518
 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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
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!
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.
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.
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 backcompute 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 cutthroat'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 autoadjusts whatever happens. Pretty cool. Albeit still highly variable!
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 backcompute 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 cutthroat'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 autoadjusts whatever happens. Pretty cool. Albeit still highly variable!
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 highlyvariable method (VPW) with a highlysmooth 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 backcomputation 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.
 CutThroat
 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!
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 cutthroat 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...)
(and agreed with the formatting comments cutthroat 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...)

 Posts: 2518
 Joined: Sat Aug 11, 2012 8:44 am
Re: Variable Percentage Withdrawal
Hi,
I've uploaded a new version of the Variable Withdrawal Spreadsheet. You'll find the link in the usual location: viewtopic.php?f=10&t=120430&p=1761580#p1761563
I have decided to preserve simplicity while adding flexibility by implementing 2 versions of VPW in the latest spreadsheet:
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.
Thanks also to CutThroat, LadyGeek, and all that have commented on this thread for your feedback.
I've uploaded a new version of the Variable Withdrawal Spreadsheet. You'll find the link in the usual location: viewtopic.php?f=10&t=120430&p=1761580#p1761563
I have decided to preserve simplicity while adding flexibility by implementing 2 versions of VPW in the latest spreadsheet:
 a plain VPW using asset allocation as guide for deriving withdrawal percentages (longinvest's recommended way), and
 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.
Thanks also to CutThroat, LadyGeek, and all that have commented on this thread for your feedback.
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
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:
You'll need to widen the chart.
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.

 Posts: 2518
 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.
Hi LadyGeek,
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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR

 Posts: 2518
 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:
Here are the basic parameters:
The Backtesting sheet of the Variable Percentage Withdrawal spreadsheet gives us the following comparison:
Bogleheads investment philosophy 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
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.
Hi LadyGeek,
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 sideeffects 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 (Excelbased 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 sideeffects 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.

 Posts: 2518
 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 sideeffects 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 (Excelbased 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 sideeffects 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) reimplement 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 
Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflationindexed)bonds 
VCN/VXC/VAB/ZRR
 CutThroat
 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!
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) reimplement 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 userfriendly 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...