Variable Percentage Withdrawal (VPW)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sun Dec 07, 2014 10:45 am

@BahamaMan: Thanks.

@BlueEars: You're right. It woud be better to avoid acronyms and to provide SWR data in table form. There isn't place on the VPW sheet for all this. I'll take your suggestion and move the comparison to a dedicated sheet.

@LadyGeek: That's really neat! The Simba spreadsheet people are wizards. I see the check boxes but I have been unable to figure out how they were set up to show and hide graph data. (I don't think I'll need it in a dedicated comparison sheet, but I might find a use for it in a future version).
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 41578
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Variable Percentage Withdrawal (VPW)

Post by LadyGeek » Sun Dec 07, 2014 10:59 am

longinvest wrote:@LadyGeek: That's really neat! The Simba spreadsheet people are wizards. I see the check boxes but I have been unable to figure out how they were set up to show and hide graph data. (I don't think I'll need it in a dedicated comparison sheet, but I might find a use for it in a future version).

The checkboxes set logical TRUE / FALSE states which are AND'ed with the scatter plot data. If TRUE, the formulas are calculated and displayed. If FALSE, the cell is blank (""). Scatter plots ignore blank cells.

One major difference between LibreOffice Calc and MS Excel is that LibreOffice Calc does NOT support named ranges in plots. MS Excel does, which is why I went with this technique. If you get stuck, let me know. (Check the Revision History notes for Rev13B.)
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.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sun Dec 07, 2014 11:00 am

Workinghard wrote:
Cut-Throat wrote:Here is the Formula I came up with for determining an adequate Nest Egg.

Nest Egg = (( Non Discretionary Budget * 2) - Annual Pension Amts and or S.S.) * 25

There has to be some flexibility in your budget to cut back if necessary. My formula for having 'Enough' $$$ to retire. (The constant of '2' is there to cut expenses by 50%, if need be)


I love this Cut Throat. It makes me feel so much better with the option of decreasing expenses. I've been "padding" by multiplying our annual expenses by 30 and not factoring in SS. It's also nice to know we can increase our spending when the time comes. T- 16 months for my husband (Medicare age).

I like Cut-Throat's formula a lot, but there's the fixed 25 factor that annoys me a little. I would be tempted to replace it with something like (1.25/VPWStartPercentage), so that the formula adapts to retirement age and asset allocation.

I would also add to the formula the amount needed to support delaying S.S. until 70. Something like:

Nest Egg = (( Non Discretionary Budget * 2) - Annual Pension Amts and or S.S.) * (1.25 / VPW start percentage) + (70 - Retirement age) * S.S. where S.S. is delayed until 70.

The (70 - Retirement age) * S.S. part should be invested separately (on retirement day) in a CD (or TIPS) ladder, with each rung maturing at the beginning of its withdrawal year.
Last edited by longinvest on Sun Dec 07, 2014 11:13 am, edited 1 time in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sun Dec 07, 2014 11:10 am

Hexdump wrote:One thing that I was trying to do was to vary the projected growth of my portfolio.
We use a 100% allocation to Wellesley and I wanted to use their growth % since inception. I pare it down a bit at 6% which I think is reasonable. (Probably get a few arguments re this)
So, is there a way in the spreadsheet to adjust for this ?

I'm not sure if I understand your question correctly. Here are two possible answers:
  1. I would recommend using the Simba spreadsheet to backtest portfolio growth. It might even already contain historical Wellesley return data. If not, you might want to ask the Simba team to for it.
  2. In the VPW spreadsheet, you can approximate it using the following asset allocation: 33% domestic stocks, 7% international stocks, 60% bonds.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sun Dec 07, 2014 11:15 am

LadyGeek wrote:
longinvest wrote:@LadyGeek: That's really neat! The Simba spreadsheet people are wizards. I see the check boxes but I have been unable to figure out how they were set up to show and hide graph data. (I don't think I'll need it in a dedicated comparison sheet, but I might find a use for it in a future version).

The checkboxes set logical TRUE / FALSE states which are AND'ed with the scatter plot data. If TRUE, the formulas are calculated and displayed. If FALSE, the cell is blank (""). Scatter plots ignore blank cells.

One major difference between LibreOffice Calc and MS Excel is that LibreOffice Calc does NOT support named ranges in plots. MS Excel does, which is why I went with this technique. If you get stuck, let me know. (Check the Revision History notes for Rev13B.)

I see it, now. Awesome trick. Thanks LadyGeek.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 41578
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Variable Percentage Withdrawal (VPW)

Post by LadyGeek » Sun Dec 07, 2014 11:34 am

longinvest wrote:
Hexdump wrote:One thing that I was trying to do was to vary the projected growth of my portfolio.
We use a 100% allocation to Wellesley and I wanted to use their growth % since inception. I pare it down a bit at 6% which I think is reasonable. (Probably get a few arguments re this)
So, is there a way in the spreadsheet to adjust for this ?

I'm not sure if I understand your question correctly. Here are two possible answers:
  1. I would recommend using the Simba spreadsheet to backtest portfolio growth. It might even already contain historical Wellesley return data. If not, you might want to ask the Simba team to for it.
  2. In the VPW spreadsheet, you can approximate it using the following asset allocation: 33% domestic stocks, 7% international stocks, 60% bonds.

Simba's backtesting spreadsheet does indeed have entries for Wellesley (VWINX), but there's no historical data. I guess the intent was to use the entry as a placeholder. If you have the historical data, just fill in the columns and change the porfolio selections in the Portfolio tab.

Post questions in the supporting thread: Spreadsheet for backtesting (includes TrevH's data)
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.

User avatar
BlueEars
Posts: 3544
Joined: Sat Mar 10, 2007 12:15 am
Location: West Coast

Re: Variable Percentage Withdrawal (VPW)

Post by BlueEars » Sun Dec 07, 2014 1:09 pm

One can get data going back to 1980 for VWINX on Yahoo here: http://finance.yahoo.com/q/hp?s=VWINX+Historical+Prices
You must then convert the monthly returns to yearly.

stlutz
Posts: 4024
Joined: Fri Jan 02, 2009 1:08 am

Re: Variable Percentage Withdrawal (VPW)

Post by stlutz » Sun Dec 07, 2014 1:25 pm

... or you can get annual returns back to 1971 here. http://finance.yahoo.com/q/pm?s=VWINX+Performance :happy

User avatar
LadyGeek
Site Admin
Posts: 41578
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Variable Percentage Withdrawal (VPW)

Post by LadyGeek » Sun Dec 07, 2014 1:36 pm

... piling on, be sure you understand the definition of "Total Return". From: Personal Finance Glossary - Yahoo! Finance :)

Total return

Total return is your annual gain or loss on an equity or debt investment. It includes dividends or interest, plus any change in the market value of the investment. When total return is expressed as a percentage, it's figured by dividing the increase or decrease in value, plus dividends or interest, by the original purchase price.

I only mention this because there are Total Return indices which include reinvested dividends. See: Index types
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.

Workinghard
Posts: 368
Joined: Fri Apr 04, 2014 4:56 am

Re: Variable Percentage Withdrawal (VPW)

Post by Workinghard » Mon Dec 08, 2014 5:59 am

longinvest wrote:
Workinghard wrote:
Cut-Throat wrote:Here is the Formula I came up with for determining an adequate Nest Egg.

Nest Egg = (( Non Discretionary Budget * 2) - Annual Pension Amts and or S.S.) * 25

There has to be some flexibility in your budget to cut back if necessary. My formula for having 'Enough' $$$ to retire. (The constant of '2' is there to cut expenses by 50%, if need be)


I love this Cut Throat. It makes me feel so much better with the option of decreasing expenses. I've been "padding" by multiplying our annual expenses by 30 and not factoring in SS. It's also nice to know we can increase our spending when the time comes. T- 16 months for my husband (Medicare age).

I like Cut-Throat's formula a lot, but there's the fixed 25 factor that annoys me a little. I would be tempted to replace it with something like (1.25/VPWStartPercentage), so that the formula adapts to retirement age and asset allocation.

I would also add to the formula the amount needed to support delaying S.S. until 70. Something like:

Nest Egg = (( Non Discretionary Budget * 2) - Annual Pension Amts and or S.S.) * (1.25 / VPW start percentage) + (70 - Retirement age) * S.S. where S.S. is delayed until 70.

The (70 - Retirement age) * S.S. part should be invested separately (on retirement day) in a CD (or TIPS) ladder, with each rung maturing at the beginning of its withdrawal year.


Longinvest, based on your formula in comparison to the other, I would need to save more money. That's not a bad thing and probably closer to where we'll actually be when both of us are retired. Oops, I need to recalculate. I baed it on my husband's SS age at 70 versus the fact that he's going to restrict and file on mine and I will be filing at 62. That gives us 2 years SS income before he turns 70.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Mon Dec 08, 2014 7:01 am

It's all very approximate. Cut-Throat's formula has the benefit of being simpler. Mine is a little more "precise", if you could say so, for early retirees. Yet, neither is really precise, because one would need to know future returns to determine the right factors. The idea is to have a wide margin, at retirement time, for reducing withdrawals without ending up living in misery.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Wed Dec 31, 2014 12:23 am

Just in time for the new year, I have uploaded a new version of the VPW spreadsheet.

As usual, you can find the download links on the Boglehead Wiki's Variable percentage withdrawal page.

Changes:
  • Renamed the old "VPW" sheet as "Backtesting".
  • Renamed the old "Retirement" worksheet as "VPW".
  • Improved the VPW worksheet:
    1. It now has its own age and asset allocation settings, independent from the Backtesting sheet.
    2. It lets the user record past withdrawals, to keep track of them (when changing the Asset Allocation or Last Withdrawal Age later during retirement).
  • Moved the "Last Withdrawal Age" setting directly onto the VPW and Backtesting sheets.
  • Put the comparison to Constant-Dollar Withdrawal on its own sheet, and added a "Withdrawal Percentage" setting.

Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Wed Dec 31, 2014 9:00 am

LadyGeek: I tried to add sliders for setting the asset allocation, but the "scroll value max" setting would reset to 0 after saving to XLS format (as I always do) and reloading. Do you know of a workaround?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 41578
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Variable Percentage Withdrawal (VPW)

Post by LadyGeek » Wed Dec 31, 2014 10:35 am

longinvest: I have duplicated your problem and tried a workaround using the combo-box control, which will allow you to select from a list -or- enter your own value.

Both the slider and combo-box work fine in LibreOffice, but the behaviour changes (breaks) when saving as XLS format. In the XLS format, the combo-box will not permit manual entry - which turns this control to a list box. My testing is within LibreOffice Calc (Linux). If it doesn't work here, there is no point to proceed with MS Excel.

I also tried creating a combo-box in Calc XLS format and resulted in the same problem. I think the use of controls is not compatible between LibreOffice and Microsoft.

There is another issue here - the behavioural aspects of your intentions. We normally recommend asset allocations to have an accuracy of no more than 5%. Going to more accuracy (like 1%) will not make a significant difference in the results and it increases the complexity to maintain your portfolio.

Having a "slider" feature implies you can "fine-tune" your results as an semi-automatic process and sends the wrong message. I think it is better for someone to manually type in the information; which will force you to stop, look, and think about at the changes (3 actions). If you want to use 0.01% precision in your analysis, that's fine. You've thought about it.
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.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Wed Dec 31, 2014 10:43 am

LadyGeek wrote:longinvest: I have duplicated your problem and tried a workaround using the combo-box control, which will allow you to select from a list -or- enter your own value.

Both the slider and combo-box work fine in LibreOffice, but the behaviour changes (breaks) when saving as XLS format. In the XLS format, the combo-box will not permit manual entry - which turns this control to a list box. My testing is within LibreOffice Calc (Linux). If it doesn't work here, there is no point to proceed with MS Excel.

I also tried creating a combo-box in Calc XLS format and resulted in the same problem. I think the use of controls is not compatible between LibreOffice and Microsoft.


Thanks for the investigation!

LadyGeek wrote:There is another issue here - the behavioural aspects of your intentions. We normally recommend asset allocations to have an accuracy of no more than 5%. Going to more accuracy (like 1%) will not make a significant difference in the results and it increases the complexity to maintain your portfolio.

Having a "slider" feature implies you can "fine-tune" your results as an semi-automatic process and sends the wrong message. I think it is better for someone to manually type in the information; which will force you to stop, look, and think about at the changes (3 actions). If you want to use 0.01% precision in your analysis, that's fine. You've thought about it.


I must admit that I had not considered all the behavioral impacts. I will heed your advice and refrain from making it too simple to play with the allocation.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Wed Dec 31, 2014 11:50 am

I have uploaded a new version of the VPW spreadsheet (December 31, 2014).

I was informed through a private message that I introduced a small presentation error for the Bonds allocation on the Backtesting sheet in yesterday's version. It's a side effect of my playing with sliders. I am sorry for any inconvenience caused by this.

As usual, you can find the download links on the Boglehead Wiki's Variable percentage withdrawal page.

Changes:
  • Improved the instructions for using VPW during retirement, taking into account behavioral impacts.
  • Fixed a small error in the presentation of the Bonds allocation on the Backtesting sheet (which did not affect the correctness of calculations).

I wish you all a very Happy New Year and thank you for your help and support!

Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sat Jan 10, 2015 9:16 pm

I have uploaded a new version of the VPW spreadsheet (January 10, 2015).

As usual, you can find the download links on the wiki:Variable percentage withdrawal page.

Changes:
  • Added Social Security and Pension to backtesting.

Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
hiosilver
Posts: 18
Joined: Tue Mar 02, 2010 12:00 pm

Re: Variable Percentage Withdrawal (VPW)

Post by hiosilver » Tue Jan 20, 2015 6:28 pm

Great spreadsheet. By the way, is there a synopsis of the purpose of each sheet available or should I just poke around and kind of figure it out?

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: Variable Percentage Withdrawal (VPW)

Post by BahamaMan » Tue Jan 20, 2015 6:59 pm

hiosilver wrote:Great spreadsheet. By the way, is there a synopsis of the purpose of each sheet available or should I just poke around and kind of figure it out?


I think if read the instructions that are given and then 'poke around and kind of figure it out', most of your questions will probably be answered. Then if you have any specific questions, someone will answer them here for sure! :happy

User avatar
hiosilver
Posts: 18
Joined: Tue Mar 02, 2010 12:00 pm

Re: Variable Percentage Withdrawal (VPW)

Post by hiosilver » Tue Jan 20, 2015 8:11 pm

BahamaMan wrote:
hiosilver wrote:Great spreadsheet. By the way, is there a synopsis of the purpose of each sheet available or should I just poke around and kind of figure it out?


I think if read the instructions that are given and then 'poke around and kind of figure it out', most of your questions will probably be answered. Then if you have any specific questions, someone will answer them here for sure! :happy



Thank you for your response, BahamaMan. Yes, I have read the instructions but there is no information on workbook structure. Just thought I'd ask. I'll be just poking around for now.
It seems the crux of VPW is contained in the !VPW and !Table sheets. The rest of the sheets appear to deal with backtesting and comparisons.

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: Variable Percentage Withdrawal (VPW)

Post by BahamaMan » Tue Jan 20, 2015 8:27 pm

hiosilver wrote:Thank you for your response, BahamaMan. Yes, I have read the instructions but there is no information on workbook structure. Just thought I'd ask. I'll be just poking around for now.
It seems the crux of VPW is contained in the !VPW and !Table sheets. The rest of the sheets appear to deal with backtesting and comparisons.


I think you're getting the hang of it !

Alan S.
Posts: 6942
Joined: Mon May 16, 2011 6:07 pm
Location: Prescott, AZ

Re: Variable Percentage Withdrawal (VPW)

Post by Alan S. » Tue Jan 20, 2015 11:10 pm

Not to be outdone, enter the "PERFECT WITHDRAWAL" - for nerds only:

http://papers.ssrn.com/sol3/papers.cfm? ... id=2551370

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Tue Jan 20, 2015 11:25 pm

Alan S. wrote:Not to be outdone, enter the "PERFECT WITHDRAWAL" - for nerds only:

http://papers.ssrn.com/sol3/papers.cfm? ... id=2551370

Alan, I do not see how this paper relates to a variable withdrawal method. I would recommend to start a new thread to discuss it.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

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

Re: Variable Percentage Withdrawal (VPW)

Post by siamond » Thu Jan 22, 2015 9:00 am

longinvest wrote:
Alan S. wrote:Not to be outdone, enter the "PERFECT WITHDRAWAL" - for nerds only:

http://papers.ssrn.com/sol3/papers.cfm? ... id=2551370

Alan, I do not see how this paper relates to a variable withdrawal method. I would recommend to start a new thread to discuss it.

Actually, they do suggest a withdrawal method of sorts on page 15, albeit not terribly practical. I need to read it a second time more carefully, but it does look like an interesting paper, and yes, it would deserve its own thread.

Longinvest, here are a few quotes which I found relevant for a VPW thread... (and yes, I am speaking half seriously, half in jest)...

Note 5: The formula for the fixed payment needed to pay off a loan in a given number of periods can be found in any
general Finance textbook (cf. Brigham & Houston, 2013). We only “flipped it around” to turn it into a formula
for withdrawals instead of payments, and included a term to allow for a non-zero ending balance.

====
Although this might seem like a trivial adjustment to the existing strategies – which may focus on the
zero-bequest case only for clarity’s sake – the fact is that is that the specific functional form by which
inheritance goals affect the optimal recommendation has not been established before (Bengen,
2006; Bernard, 2011; Spitzer, 2008). It is the proportion of the desired final balance to the starting
balance that matters and not, as might be assumed, the amount by which the initial balance exceeds
the target.
For example, a natural way to deal with this complication would be to “put aside” the inheritance
money – effectively removing it from the analysis – and work with the excess balance as if it were a
zero-bequest case (cf. “Two-Bucket” strategy in Bernard, 2011). But this of course would be
misguided because the separated funds will accumulate returns too, and there is no reason for these
not to be available for consumption (at least the real part, anyway). So how should we handle this?
Should the planner separate the funds to be inherited but then plow back the real returns into the
“consumable” balance? Our approach renders all these efforts unnecessary by including a bequest
term in the equations, making the no-bequest scenario just a specific instance of the general case.
Also, this shows that there is a fourth factor – in addition to rates of return, age, and risk preference
– that is an intrinsic determinant of the optimal withdrawal rate: we need to know what fraction of
the starting balance is to be bequeathed in order to advise on how much to withdraw.


As you and I discussed by PM, it wouldn't be hard to extend VPW (or define a VPW derivative) that would use the "final value" term of the PMT formula to do exactly what they suggest, instead of hard-wiring it to zero. Makes a ton of sense to me...

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Thu Jan 22, 2015 11:11 am

siamond wrote:As you and I discussed by PM, it wouldn't be hard to extend VPW (or define a VPW derivative) that would use the "final value" term of the PMT formula to do exactly what they suggest, instead of hard-wiring it to zero. Makes a ton of sense to me...

Siamond,

Yes, this is right: it does makes some sense, and it would be easy to implement. Actually, if you remember the first post of this thread (yes, the very first post), my initial idea of VPW allowed for the specification of a target drawdown (= 100% - target bequest), but in terms of a percentage (like they do in that article) instead of an absolute value. But, the reason I removed it was to make VPW as simple as possible. Let me explain.

My worry is that people imagine that the only money left for bequest is the specified percentage, when in reality it is expected that the retiree will die before the planned final withdrawal. The retiree is thus very likely to leave a sizable bequest, even without planning explicitly for it.

Furthermore, just targeting an older last-withrawal age achieves the objective of leaving a significant bequest in a very effective way. As an example, a retiree who targets a last withdrawal at age 119 but dies at age 80 or even 90 will leave a significant bequest. If he does get to live to 115, it is unlikely that his children (probably dead) and even grandchildren (now in their late 50s or 60s) will have any need for the bequest. So, why complicate things with an additional VPW parameter, given the fuzziness of a bequest expressed in percentage (where the absolute target amount changes with markets), when one can simply increase the age of last withdrawal?

I am not completely opposed to introducing a target bequest percentage, but I am not convinced that the drawback of adding another layer of complexity is offset by enough usefulness. I would prefer to add more efforts on educating people about how one should select the last-withdrawal age appropriately (e.g. not based on "average mortality" but on realistic personal (near) worst case, to be reassessed every few years) and how an overly conservative last-withdrawal age increases the eventual bequest size.

Finally, we should not discount Taylor's example of giving to his children while alive, instead of waiting to be dead first: http://www.bogleheads.org/forum/viewtop ... 3#p2330653
We began giving our our three sons their inheritance in the form of an allowance about 10 years ago. They were and are very grateful. It gives us great pleasure to see them enjoy their inheritance in advance.

One son died last year. I could not forgive myself if we had not helped him when he was alive.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

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

Re: Variable Percentage Withdrawal (VPW)

Post by siamond » Thu Jan 22, 2015 11:40 am

Well, if something like that would be done, then I wouldn't want a % as an input parameter, but an absolute value (in real terms). This is what has real meaning as a goal. And yes, I understand the annoyance of having to adjust it based on inflation rate in the model, but such is life. Also, I appreciate Taylor's strategy, I think this is a great idea, but a bequest isn't necessarily for children, this could be for charity. Or for peace of mind (cf. the perpetual portfolio discussion). Anyhoo, we've circled around this in the past, I was just amused to read this part in the paper. :wink:

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: Variable Percentage Withdrawal (VPW)

Post by BahamaMan » Thu Jan 22, 2015 12:07 pm

longinvest wrote:Also, why complicate things with an additional VPW parameter, given the fuzziness of a bequest expressed in percentage (where the absolute target amount changes with markets), when one can simply increase the age of last withdrawal?



Completely Agree! - VPW is a withdrawal tool, not a tool to manage investments and inheritances. It is very easy to take a lump sum and not include it in VPW and manage it elsewhere for investments and bequests.

User avatar
BlueEars
Posts: 3544
Joined: Sat Mar 10, 2007 12:15 am
Location: West Coast

Re: Variable Percentage Withdrawal (VPW)

Post by BlueEars » Thu Jan 22, 2015 1:48 pm

longinvest wrote: ...
Furthermore, just targeting an older last-withrawal age achieves the objective of leaving a significant bequest in a very effective way. As an example, a retiree who targets a last withdrawal at age 119 but dies at age 80 or even 90 will leave a significant bequest. If he does get to live to 115, it is unlikely that his children (probably dead) and even grandchildren (now in their late 50s or 60s) will have any need for the bequest. So, why complicate things with an additional VPW parameter, given the fuzziness of a bequest expressed in percentage (where the absolute target amount changes with markets), when one can simply increase the age of last withdrawal?

I am not completely opposed to introducing a target bequest percentage, but I am not convinced that the drawback of adding another layer of complexity is offset by enough usefulness. I would prefer to add more efforts on educating people about how one should select the last-withdrawal age appropriately (e.g. not based on "average mortality" but on realistic personal (near) worst case, to be reassessed every few years) and how an overly conservative last-withdrawal age increases the eventual bequest size.
...

I agree and think VPW is complex enough and yet retains it's transparency (a fully readable spreadsheet). If one were looking for a "next step", I would think it would be to make this into an app for the non-spreadsheet literate folks. But then the transparency would be lost as it would be hard to keep things synchronized unless there were a way for the spreadsheet to be linked to an app, i.e. the spreadsheet is the source code for the app. If it were me, I'd not want to do all that work without being paid. :wink:

The way I run VPW is to set the depletion years (via the last withdrawal age) to 44. I notice then that for our parameters, the real value of the portfolio stays above 50% of the starting value out to year 36. This gives plenty of mid-course correction opportunities should the future sequence be better then the 1968 start date sequence (near worst case in modern times). Also this happens to give me a withdrawal rate = 4.5% this year. That's plenty for us. :happy

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

Re: Variable Percentage Withdrawal (VPW)

Post by siamond » Thu Jan 22, 2015 3:58 pm

BahamaMan wrote:
longinvest wrote:Also, why complicate things with an additional VPW parameter, given the fuzziness of a bequest expressed in percentage (where the absolute target amount changes with markets), when one can simply increase the age of last withdrawal?


Completely Agree! - VPW is a withdrawal tool, not a tool to manage investments and inheritances. It is very easy to take a lump sum and not include it in VPW and manage it elsewhere for investments and bequests.

Well, you are (way) richer than me... If I have $2M to retire, I can live comfortably. But with a bequest goal of $1M, if I only have $1M to inject in a withdrawal method (VPW or else), life ain't fun. And I really can't aim at a $2M bequest (and why not?)... This is the fundamental problem with bucket approaches, you don't use the (real) returns of the whole portfolio for your own needs. The authors of the paper made the same comment.

Anyhoo, I was mostly teasing longinvest... :wink:

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: Variable Percentage Withdrawal (VPW)

Post by BahamaMan » Thu Jan 22, 2015 4:25 pm

siamond wrote:Well, you are (way) richer than me... If I have $2M to retire, I can live comfortably. But with a bequest goal of $1M, if I only have $1M to inject in a withdrawal method (VPW or else), life ain't fun. And I really can't aim at a $2M bequest (and why not?)... This is the fundamental problem with bucket approaches, you don't use the (real) returns of the whole portfolio for your own needs. The authors of the paper made the same comment.

Anyhoo, I was mostly teasing longinvest... :wink:


Teasing aside, you can't have it both ways! --- You either are withdrawing from your portfolio, with VPW, with the intent of spending it all or most of it. Or, you are sheltering some of it for a bequest. You can't say I have a Portfolio that I want to Withdraw from (with VPW) and then want to leave over half of it as a bequest. As, one of the objectives of VPW was to spend All or most all of your portfolio.

b4real
Posts: 101
Joined: Wed Mar 05, 2014 6:19 am

Re: Variable Percentage Withdrawal (VPW)

Post by b4real » Thu Jan 22, 2015 4:39 pm

When we retired last year I set aside an amount to cover 5 years LTC for 2 people based on today's LTC costs. If not used for LTC this will be inheritance and charity. The balance of the nest egg will be spent down according to VPW over 35 years.

I modified VPW to keep track of the set-aside amount each year, adding to it by the original set-aside % of the portfolio if the portfolio grew in the prior year, but not decreasing it if the portfolio declined. My hope is that the set-aside amount will keep pace with inflation. I'll adjust if necessary as we go along. So, if I start out with X% set-aside of the portfolio then each year the set-aside amount would be increased by X% of the portfolio increase in the prior year or 0 if the portfolio decreased. The VPW portfolio amount for the new year would be the total portfolio amount minus the new set-aside amount.

I'm not suggesting that the general VPW tool be changed to accommodate a portfolio set-aside. It's probably best for each person to tailor this to their own needs. For me, I want to manage each piece based on how the total portfolio increases and decreases each year.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Wed Jan 28, 2015 4:44 pm

I have uploaded a new version of the VPW spreadsheet (January 28, 2015).

As usual, you can find the download links on the wiki:Variable percentage withdrawal page.

Changes:

All my thanks go to the Bogleheads Simba spreadsheet team (U.S. data) and Norbert Schlenker of Libra Investment Management (Canadian data) for providing spreadsheets with historical data.

Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
Hexdump
Posts: 1610
Joined: Fri Oct 10, 2008 7:28 am
Location: Houston, Texas

Re: Variable Percentage Withdrawal (VPW)

Post by Hexdump » Thu Jan 29, 2015 10:15 am

longinvest wrote:I have uploaded a new version of the VPW spreadsheet (January 28, 2015).

As usual, you can find the download links on the wiki:Variable percentage withdrawal page.

Changes:

All my thanks go to the Bogleheads Simba spreadsheet team (U.S. data) and Norbert Schlenker of Libra Investment Management (Canadian data) for providing spreadsheets with historical data.

Enjoy!


Longinvest or any others, Do you download the latest version when it becomes available ? I have been doing that but am beginning to think that some versions may not have any significant changes.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Thu Jan 29, 2015 10:40 am

Hexdump wrote:Longinvest or any others, Do you download the latest version when it becomes available ? I have been doing that but am beginning to think that some versions may not have any significant changes.

Hexdump, if the "Changes" section of the new version announcement message didn't contain something relevant to me, I could ignore the new version. But, personally, I'm a curious person so I would still download it. Your mileage may vary. :happy
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

RustyShackleford
Posts: 1197
Joined: Thu Sep 13, 2007 12:32 pm
Location: NC

Re: Variable Percentage Withdrawal (VPW)

Post by RustyShackleford » Fri Feb 06, 2015 1:42 pm

I just came across this VPW spreadsheet. I have not read all 11 pages of postings, so sorry if these questions/comments are redundant.

1. I don't understand why you need to enter your actual withdrawal each year. It seems to me the only things that should matter, going forward, are your current portfolio balance and AA, and your number of withdrawal years left. Indeed, experimenting a little, the number entered for actual withdrawal does not seem to affect future year's withdrawal recommendations.

2. It'd be nice if alternative investments, such as real estate and commodities, were included. Maybe not possible because of a lack of data to predict performance.

3. I'm surprised as how large withdrawals it allows. With a 50/50 AA and a 35 year withdrawal horizon, it allows me to take almost 5% the first year.

Anyhow, the thing is pretty cool. Thanks !

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: Variable Percentage Withdrawal (VPW)

Post by BahamaMan » Fri Feb 06, 2015 2:06 pm

RustyShackleford wrote:I just came across this VPW spreadsheet. I have not read all 11 pages of postings, so sorry if these questions/comments are redundant.

1. I don't understand why you need to enter your actual withdrawal each year. It seems to me the only things that should matter, going forward, are your current portfolio balance and AA, and your number of withdrawal years left. Indeed, experimenting a little, the number entered for actual withdrawal does not seem to affect future year's withdrawal recommendations.http://www.bogleheads.org/forum/posting ... p=2365138#

2. It'd be nice if alternative investments, such as real estate and commodities, were included. Maybe not possible because of a lack of data to predict performance.

3. I'm surprised as how large withdrawals it allows. With a 50/50 AA and a 35 year withdrawal horizon, it allows me to take almost 5% the first year.

Anyhow, the thing is pretty cool. Thanks !


1.) You don't have to enter your actual withdrawal. It's just for your personal record keeping.
2.) Yup, Impossible to predict or backtest on Real estate.
3.) Yes, that is one reason why it is a superlative Withdrawal method!

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

Re: Variable Percentage Withdrawal (VPW)

Post by siamond » Sat Mar 28, 2015 12:36 pm

Here is a short study on a few ways to smooth the VPW spending trajectory:

https://www.bogleheads.org/forum/viewtopic.php?f=2&t=160073&p=2436045#p2435979

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Sun Mar 29, 2015 2:06 pm

I do not endorse the smoothing methods presented in that thread, as:
  • they increase the risk of the portfolio doing badly in bad times,
  • some of these methods depend on timing the market (using valuations to estimate future returns), and
  • they require that one unnecessarily increase the age of last withdrawal.

I think that it is much better to use the following approach to retirement planning:
  • Use delayed Social Security and, if necessary, inflation-adjusted Single Premium Immediate Annuities as a basic income floor (and longevity insurance).
  • Use VPW with an appropriate asset allocation as a simple yet effective method to efficiently withdraw money from the portfolio without increasing risk and to leave a declining inheritance the older one dies (and the less heirs need the money).
  • Revisit the plan every few years.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

DFrank
Posts: 474
Joined: Thu Mar 20, 2014 10:44 pm
Location: SoCal

Re: Variable Percentage Withdrawal (VPW)

Post by DFrank » Tue Mar 31, 2015 1:04 pm

longinvest wrote:I think that it is much better to use the following approach to retirement planning:
  • Use delayed Social Security and, if necessary, inflation-adjusted Single Premium Immediate Annuities as a basic income floor (and longevity insurance).
  • Use VPW with an appropriate asset allocation as a simple yet effective method to efficiently withdraw money from the portfolio without increasing risk and to leave a declining inheritance the older one dies (and the less heirs need the money).
  • Revisit the plan every few years.


Would you consider improving the spreadsheet to allow for backtesting some of these methods? I find the backtesting sheet to be quite limited in it's ability to model income streams other than those coming from the portfolio, and unable to model some of your recommended approaches above. For example, there is no way to model delayed social security relative to the retirement date.

Some examples of what I think people with other than a "standard" retirement scenario would find useful:

1. Ability to model two social security income streams with independent start dates relative to the retirement date
2. Ability to model two pensions with independent start dates relative to the retirement date (some might also want the ability model a DB pension with a COLA, although my seat of the pants impression from comments on the forum here is that most of those folks aren't as dependent on their portfolio, so may be of limited interest)
3. Ability to model N income streams, where N is some reasonable number (2?), with independent start/stop dates. This could be used to model periods of part time work after retirement, or the case of a younger spouse that may work for some time past the retirement date of the older spouse.
4. The ability to model the conversion of a portion of the portfolio to a SPIA at a certain age.
Dave

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Tue Mar 31, 2015 1:38 pm

Dfrank,

These are all very good ideas, but the objective of the spreadsheet is mostly to illustrate VPW and let people backtest it. Keeping the spreadsheet as simple as possible allows people to investigate how it works and see that there are no hidden magical formulas. The spreadsheet is really not meant as a tool to model an overall retirement plan.

The good new is that a more comprehensive software that uses VPW already exists: the cfiresim.com web site allows for backtesting an overall retirement plan and does provide VPW as a choice of withdrawal method. :)

For simulating delayed Social Security using the VPW spreadsheet, I would simply reduce the portfolio by the amount needed to buy enough CDs to replace the missing Social Security payments.

As a last alternative, you can modify the VPW spreadsheet to perfectly model your own custom retirement plan. That's the beauty of a free spreadsheet: you're not restricted to the only models chosen by the software developer; you can modify it to implement your own model. :)
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

cadreamer2015
Posts: 534
Joined: Wed Apr 08, 2015 1:52 pm
Location: North County San Diego

Re: Variable Percentage Withdrawal (VPW)

Post by cadreamer2015 » Tue May 12, 2015 1:34 pm

Security error in downloading VPW spreadsheet?

I get the following error message when downloading the VPW spreadsheet from either the googledocs or dropbox locations:

"Security Error - Office File Validation
WARNING: Office File Validation detected a problem while trying to open this file.
Opening this is probably dangerous, and may allow a malicious user to take over your computer."

Does anyone know about this problem? I would like to try out the spreadsheet model, but don't want to have security problems with my computer.

Thanks,
cadreamer2015
De gustibus non est disputandum

User avatar
BlueEars
Posts: 3544
Joined: Sat Mar 10, 2007 12:15 am
Location: West Coast

Re: Variable Percentage Withdrawal (VPW)

Post by BlueEars » Tue May 12, 2015 3:04 pm

cadreamer2015 wrote:Security error in downloading VPW spreadsheet?

I get the following error message when downloading the VPW spreadsheet from either the googledocs or dropbox locations:

"Security Error - Office File Validation
WARNING: Office File Validation detected a problem while trying to open this file.
Opening this is probably dangerous, and may allow a malicious user to take over your computer."

Does anyone know about this problem? I would like to try out the spreadsheet model, but don't want to have security problems with my computer.

Thanks,
cadreamer2015

If you look back at my similar concerns back in Nov 2014, there is some discussion about this. I outlined how I did things on the Nov 17 post. I'm not sure this was every eliminated but apparently it is a benign issue. I'd rather not be confronted with such a choice though.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Tue May 12, 2015 3:20 pm

I develop the VPW spreadsheet using the open-source LibreOffice Calc software. Microsoft Excel doesn't like that the spreadsheet was not generated using itself, so it complains, but it can open the file. The warning is scary, though; it gives the impression that someone is trying to corrupt your computer. It is a prudent thing not to expose one's computer to such danger.

My recommendation would be that you simply download the LibreOffice software. You can get it for free from: http://libreoffice.org . LibreOffice does not raise any warning for the VPW spreadsheet, even though LibreOffice protects its user against dangerous spreadsheets (such as those with macros). This is due to the fact that the VPW spreadsheet was built without using any macro (which was a challenge). You'll feel much safer using LibreOffice.

As a bonus, if you have a programmer friend, he'll be able to go through the source code of LibreOffice to make sure it contains no malicious code. He won't be able to do the same with Excel, as Microsoft keeps its source code secret.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

cadreamer2015
Posts: 534
Joined: Wed Apr 08, 2015 1:52 pm
Location: North County San Diego

Re: Variable Percentage Withdrawal (VPW)

Post by cadreamer2015 » Tue May 12, 2015 3:24 pm

Thanks BlueEars and longinvest. I had tried searching this thread for the security error, but wasn't able to find the earlier discussion. I will try this again tonight with your suggestions.
De gustibus non est disputandum

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Tue May 12, 2015 5:38 pm

I've added a "Known Problems and Solution" section to the wiki page to help future users.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
BlueEars
Posts: 3544
Joined: Sat Mar 10, 2007 12:15 am
Location: West Coast

Re: Variable Percentage Withdrawal (VPW)

Post by BlueEars » Tue May 12, 2015 6:17 pm

longinvest wrote:I develop the VPW spreadsheet using the open-source LibreOffice Calc software. Microsoft Excel doesn't like that the spreadsheet was not generated using itself, so it complains, but it can open the file. The warning is scary, though; it gives the impression that someone is trying to corrupt your computer. It is a prudent thing not to expose one's computer to such danger.
...

What I've done is simply to create an Excel file (.xlsx in Excel 2007) from the downloaded file. Can't quite remember the process except as noted on my Nov 17 post. It now comes up on my PC with no complaints.

I'm unclear why this cannot be done prior to posting a version for general consumption. Maybe I don't understand the issue of how to get a clean Excel file for download? If this is too much bother, I'm fine with the current method.

longinvest
Posts: 2487
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW)

Post by longinvest » Tue May 12, 2015 9:01 pm

I don't possess a Microsoft Office license; that's why I can't do it.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
BlueEars
Posts: 3544
Joined: Sat Mar 10, 2007 12:15 am
Location: West Coast

Re: Variable Percentage Withdrawal (VPW)

Post by BlueEars » Wed May 13, 2015 10:17 am

longinvest wrote:I don't possess a Microsoft Office license; that's why I can't do it.
Sorry, should have figured that was the issue. Thanks again for VPW!

User avatar
Dutch
Posts: 1277
Joined: Thu Jun 27, 2013 2:12 pm

Re: Variable Percentage Withdrawal (VPW)

Post by Dutch » Wed May 13, 2015 11:51 am

cadreamer2015 wrote:Security error in downloading VPW spreadsheet?

I get the following error message when downloading the VPW spreadsheet from either the googledocs or dropbox locations:

"Security Error - Office File Validation
WARNING: Office File Validation detected a problem while trying to open this file.
Opening this is probably dangerous, and may allow a malicious user to take over your computer."

Does anyone know about this problem? I would like to try out the spreadsheet model, but don't want to have security problems with my computer.

Thanks,
cadreamer2015


I simply disregarded the error message, opened the file in excel and saved the spreadsheet as an excel file. It didn't trigger any of my virus scanners/firewalls. That was 5 months ago. The spreadsheet works fine and my computer is fine as well.

mouth
Posts: 73
Joined: Sun Apr 19, 2015 6:40 am

Re: Variable Percentage Withdrawal (VPW)

Post by mouth » Sat Jun 13, 2015 8:02 am

I'm sorry if I missed this in the all the previous posts but a few comments on the backtesting tab:

1) Pension - not all pensions are NOCOLA
2) Social Security - It seems to start at StartAge vice 62/67/70 ... problem is I plan on retiring at 43.
3) The same can be said for pensions. Mine actually has started already, but others might want to input a different start age than StartAge if they plan to retire earlier than their pension starts.

Not huge deals since backtesting is just a nice to have feature compared to the main VPW tab. Thanks for all the hard work!!!

Post Reply