# Variable percentage withdrawal (VPW)



## like_to_retire (Oct 9, 2016)

I keep reading about Variable percentage withdrawal (VPW) and thought I would take a look.

The scoop on this method compared to the oft-used simple 4% of portfolio withdrawal method in retirement is described as:

_"Variable percentage withdrawal (VPW) is a withdrawal method that adapts to the retiree's retirement horizon, asset allocation, and portfolio returns during retirement. It combines the best ideas of the constant-dollar, constant-percentage, and 1/N withdrawal methods to allow the retiree to spend most of the portfolio using return-adjusted withdrawals. By adapting withdrawals to market returns, VPW will never prematurely deplete the portfolio"._


Searching CMF for information, I found the thread Sequence of Returns Mitigation where respected member GreatLakers explained VPW nicely: _"To understand the VPW method, consider if you wanted to spend $100 equally over 5 years. In each year you spend $20, so after 5 years you would have spent it all. Now look at it another way. Instead of spending a fixed dollar amount, spend a variable percentage amount. In the first year spend 20% ($20). Then in the 2nd year you have $80 left so you spend 25% ($20). In the third year you have $60 left so you spend 33% ($20). In the 4th year you have $40 left so you spend 50% ($20). In the 5th year you have $20 left so you spend 100% ($20).
The difference between the 2 calculation methods is that the first method cannot handle varying returns. The second method can handle investment rates of return that vary each year, and still enable spending the portfolio down to $0 after a set number of years. That is basically how the VPW spreadsheet works."_

So I went to Finiki web site and downloaded to Google Sheets the _"VPW Accumulation And Retirement Worksheet"._

Simple enough. I entered the information required and it produced the results of what it thought I could withdraw from my portfolio this year, in addition to my fully indexed defined benefit pension and fully indexed CPP and OAS.

So many questions.

The value that I can withdraw, I think, is designed to deplete my portfolio to zero at about age 100, although I don't see that I can modify that age? 

It also says that this amount that I could withdraw from my portfolio this year includes all taxes. That's a whopping big spread. Consider my entire portfolio might be in a TFSA compared to an RRSP or non-registered account. The difference in taxes is enormous. I guess that's up to me to sort out?

In my reading in many other threads on this subject and including the thread I mentioned above, there are so many variables it indicates I have control over, but just don't see in the spreadsheet I downloaded?

Posters mention, _"The spreadsheet has two data sets built into it: Canada (1970-2015) and U.S. (1871-2015). And US dataset uses US returns for Domestic Stocks; International returns for International Stocks. Canadian dataset uses Canadian returns for Domestic Stocks; 50% US returns / 50% International returns for International Stocks. Pick Canadian dataset and you get fairly close to what you want. You will need to tweak the formula for International Stocks to get your exact desired allocation.You can pick one of the two to run backtests."_

What the heck is this? I don't have that option on my VPW spreadsheet?

Or stuff like: _"You see a rising income in the backtesting table because historical returns were better than the defaults (5%, 1.8%). VPW method gives you an automatic raise if actual returns exceed the default expectations. Try changing Override parameter to Yes and enter these returns:
Stocks: 8%
Bonds: 2.5%
You will see a much smoother withdrawal amounts in the Backtesting tab, because the override returns are closer to the actual historical returns.
Wow I did not know where the growth parameters were and didn't realize I could adjust them for myself_"

What the heck is this? I don't have that option on my VPW spreadsheet?

What am I missing that they sure haven't made clear..........

ltr


----------



## AltaRed (Jun 8, 2009)

LTR. there are several threads on FWF on the subject. Try a search there and read them. Longinvest is essentially the creator, with input from Bogleheads and FWF members on the creation of all this stuff.

You do need to make that estimate on taxes for yourself. Taxes is a 'spend' item as it should be, like food. Since you already know what your taxes generally are from year to year, it is a budget item like everything else and you have to decide whether to lower it, e.g. withdrawing from TFSA, or increase it withdrawing from RRIF.

Added: I don't use the spreadsheet itself. I use the factors in the table for my asset allocation to give me an upper bound for withdrawal amounts... like a RRIF factor.


----------



## GreatLaker (Mar 23, 2014)

The spreadsheet you downloaded is a newer, simpler version from which the backtesting and comparison tabs have been removed.

Try this link and download the backtesting spreadsheet: https://www.finiki.org/wiki/Variable_percentage_withdrawal#Backtesting_Spreadsheet_Download_location
This version also allows you to set the age for portfolio exhaust.


----------



## like_to_retire (Oct 9, 2016)

AltaRed said:


> You do need to make that estimate on taxes for yourself. Taxes is a 'spend' item as it should be, like food. Since you already know what your taxes generally are from year to year, it is a budget item like everything else and you have to decide whether to lower it, e.g. withdrawing from TFSA, or increase it withdrawing from RRIF.


Interesting that the VPW download spreadsheet doesn't address this in its comments. That would be useful.

I know for myself I have a high marginal rate and a large OAS clawback, so the amount they say I can withdraw gets depleted pretty quick compared to someone in a lower income bracket.

It would be useful for that information to be passed on to the user in the spreadsheet comments?

And what's all this back-testing and the graphs I see? I don't have that available in my spreadsheet.

This spreadsheet needs work IMO for the average user.

ltr


----------



## AltaRed (Jun 8, 2009)

You can always make that suggestion. I don't have the answers myself about the back testing and graphs since I don't use it directly....just as guidance.

A few threads that might interest you....

https://www.financialwisdomforum.org/forum/viewtopic.php?f=30&t=117200 Long but may be worth slogging through it

A new thread on a forward test https://www.financialwisdomforum.org/forum/viewtopic.php?f=30&t=122041

Added much later: It may be that, in retirement, you shouldn't try to model everything into VPW (to build a portfolio). Treat it more like a RRIF table like I do, i.e. how much you can withdraw from your portfolio annually and add it to your other sources of income such as CPP and OAS (net of claw back) for the year to get your gross numbers on the revenue side of the ledger. Then it is simply a matter of what the expense side of your ledger is, including provision for income taxes. Sure, you have to guess what the income tax will be depending on what sources of capital you are pulling from, but presumably you already are going to do that as efficiently as possible anyway over the last few years. Last year's income tax number is a default. It's the system I use and I don't care about any of the other details.

FWIW, VPW was developed over time, starting back in 2013 I believe and tested vigorously by the rigor of folks on Bogleheads for the USA. It got some of the same rigor for the Canadian version in FWF. If you still want to go the full spreadsheet routine and the spreadsheets are not doing what you want for some reason, PM Longinvest over at FWF for answers, or look for answers in the threads I linked. If there is some glitch in the spreadsheets, the folks at FWF, and especially the contributors to Finiki, will want to know and fix them. Hope that helps.


----------



## prisoner24601 (May 27, 2018)

Or just use the Excel function PMT(R,N,PV)) each year, where R= risk free rate of return, N is number of years till TU, PV is investment balance at start of year. So for someone with $1M and 40 years to go, they might withdraw PMT(1.5/100,40,1000000) = $33K per year. For someone with 10 years to go and $1M left, they might withdraw PMT(1.5/100,10,1000000) = $108k per year. The risk free return is 6 month T-bill for example each year of whatever you like to assume.

Close enough for retirement work!


----------



## hfp75 (Mar 15, 2018)

I took a fast look and it looks like the VPW.xls file is a US based file..... the theory is good but refers to Social Security...


----------



## AltaRed (Jun 8, 2009)

hfp75 said:


> I took a fast look and it looks like the VPW.xls file is a US based file..... the theory is good but refers to Social Security...


The Canadian option is in Finiki.


----------



## GreatLaker (Mar 23, 2014)

hfp75 said:


> I took a fast look and it looks like the VPW.xls file is a US based file..... the theory is good but refers to Social Security...


Which specific file and cell are you referring to?

If it is the VPW-Accumulation-And-Retirement-Worksheet.xlsx, there are 4 sections for the user to enter defined benefit pensions, which for Canadians would include CPP, OAS, workplace DB pensions. You can rename the cell E8 from "Social Security" to anything you want.

If it is the Backtesting tab of the VariablePercentageWithdrawal.xls spreadsheet, cell C18 has a dropdown that can be changed between Canada and US data sets, which renames the Social Security cell to "CPP and OAS".


----------



## like_to_retire (Oct 9, 2016)

hfp75 said:


> I took a fast look and it looks like the VPW.xls file is a US based file..... the theory is good but refers to Social Security...


You need to download version 1.4 here into Google Sheets or into your computer and use Excel.

But yeah, the default entry for pensions has the heading Social Security named, but when you select it, you just enter all your own CPP, OAS, DB pension, etc.

I've played with it quite a bit in the last while and I've come to accept that simple is perhaps better with this higher level information. In other words, it's easy to use and comes up with an amount in dollars that you can remove from your portfolios this year. That amount is gross, and includes all taxes you may have to pay. 

The program can't know your situation and what you have in each of your TFSA, RRSP, RRIF, Non-Registered accounts. It's up to you to decide from which account the gross cash amount suggested for VPW should be removed to create the best tax situation. The only thing the software knows is your overall stock/bond asset allocation, but not where it's housed.

The beauty is that errors over time can't really build up since you re-enter all the information once a year to get a new dollar figure to withdraw from your portfolios. Simple. If it was a terrible year in the markets, it will change the amount you may withdraw. And of course, no one says you have to withdraw it all, because it's designed to deplete your entire portfolio eventually.

I do see this as a better solution than the standard 4% withdrawal method.

In that light, I have been able to get the backtesting software to work and all its graphs to compare VPW with the standard fixed 4% rules. Interesting stuff.

ltr


----------



## longinvest (Sep 12, 2012)

Hi ltr,



like_to_retire said:


> The value that I can withdraw, I think, is designed to deplete my portfolio to zero at about age 100, although I don't see that I can modify that age?


This age can't be modified, by design, in the VPW Accumulation And Retirement Worksheet (short name: VPW Worksheet).

The VPW Worksheet doesn't deplete the portfolio; it caps the withdrawal rate at a maximum of 10% in old age. The VPW Worksheet instructions in finiki say:

_At age 80, if you're still alive, it's important to consider using part (but not all) of your remaining portfolio to buy an inflation-indexed Single Premium Immediate Annuity (SPIA), so that the estimated *Income Floor After 100* is sufficient to live comfortably, independently of future portfolio withdrawals. This aims to reduce the financial risks associated with living past age 100._​
Effectively, starting at age 80, the VPW Worksheet displays an *Income Floor After 100* with a note reminding the retiree to make sure it's sufficient. This isn't displayed earlier to preserve liquidity and because inflation-indexed annuities get _somewhat_ more cost efficient at 80.



like_to_retire said:


> It also says that this amount that I could withdraw from my portfolio this year includes all taxes. That's a whopping big spread. Consider my entire portfolio might be in a TFSA compared to an RRSP or non-registered account. The difference in taxes is enormous. I guess that's up to me to sort out?


Taxes are very complex. TFSA withdrawals don't attract taxes. RRIF withdrawals give place to pension income credit while attracting taxes. Non-registered account withdrawals might come from return of capital (not taxed), dividends (with a partial tax credit), and capital gains (half-taxed).

This is a simple worksheet that calculates pre-tax income. It's up to the retiree to put aside part of that income for taxes. 



like_to_retire said:


> In my reading in many other threads on this subject and including the thread I mentioned above, there are so many variables it indicates I have control over, but just don't see in the spreadsheet I downloaded?


There are two spreadsheets:

VPW Accumulation And Retirement Worksheet (newer)
VPW Backtesting spreadsheet (older)

The newer VPW Worksheet has been designed as an easy-to-use retirement tool, taking into account current and future pensions with and without cost-of-living adjustments.

The older VPW Backtesting spreadsheet was only focused on the portfolio withdrawal part, to allow users to assess the robustness of the method through backtests (using historical US and Canadian market data) and comparison with constant inflation-adjusted withdrawals. It also provided some options, for users researching the method, for tweaking it. Those tweaks were not intended for end users of the method.




like_to_retire said:


> Posters mention, _"The spreadsheet has two data sets built into it: Canada (1970-2015) and U.S. (1871-2015). And US dataset uses US returns for Domestic Stocks; International returns for International Stocks. Canadian dataset uses Canadian returns for Domestic Stocks; 50% US returns / 50% International returns for International Stocks. Pick Canadian dataset and you get fairly close to what you want. You will need to tweak the formula for International Stocks to get your exact desired allocation.You can pick one of the two to run backtests."_
> 
> What the heck is this? I don't have that option on my VPW spreadsheet?


It's in the older VPW Backtesting spreadsheet. 



like_to_retire said:


> Or stuff like: _"You see a rising income in the backtesting table because historical returns were better than the defaults (5%, 1.8%). VPW method gives you an automatic raise if actual returns exceed the default expectations. Try changing Override parameter to Yes and enter these returns:
> Stocks: 8%
> Bonds: 2.5%
> You will see a much smoother withdrawal amounts in the Backtesting tab, because the override returns are closer to the actual historical returns.
> ...


These options (in the older spreadsheet) were not intended for end users; they were intended for those researching the method. Unfortunately, some people started suggesting to end users to play with them (and try to time the market using simplistic metrics) without a good understanding of the potential consequences.

The VPW Table and the newer VPW Worksheet can't be tweaked.


----------



## longinvest (Sep 12, 2012)

The _VPW Accumulation And Retirement Worksheet_ doesn't come with a user manual. But, I've started a demo thread on FWF to show how it can be used (along with a small _cash cushion_) to generate monthly retirement income: A Simple Retirement Using Variable Percentage Withdrawals (VPW Forward Test).


----------



## like_to_retire (Oct 9, 2016)

longinvest said:


> The _VPW Accumulation And Retirement Worksheet_ doesn't come with a user manual. But, I've started a demo thread on FWF to show how it can be used (along with a small _cash cushion_) to generate monthly retirement income: A Simple Retirement Using Variable Percentage Withdrawals (VPW Forward Test).


Yeah, I've been following that thread. Thanks for your work on all this.

It did take me a while, but after doing reading on FWF threads and playing around with it all, I think I understand it fairly well now. I admit it all seemed a bit confusing for me at first, as perhaps I'm not a quick study, but I feel I eventually understood.

Thanks for taking time to answer those questions.

ltr


----------



## RBull (Jan 20, 2013)

Thanks longinvest for producing the spreasheets and for providing the additional information above. 

Good to read that from you ltr. I've been using VPW (the original version) as a guide for 3 years now in retirement, and am satisfied its the most suitable solution in our situation (and possibly for many people). I have yet to have a look at the newer version.


----------



## like_to_retire (Oct 9, 2016)

RBull said:


> I've been using VPW (the original version) as a guide for 3 years now in retirement, and am satisfied its the most suitable solution in our situation (and possibly for many people). I have yet to have a look at the newer version.


I think the two versions are a good compliment to each other. The original version offers lots of variables to play with, but perhaps a bit daunting for the uninitiated. It can be intimidating and might turn the regular user away. It's good to keep that version around though to allow people who like to dig deeper and play with variables.

The newer version is much simpler. As I said earlier, it could introduce errors over time, but the fact is, you are required to re-enter new parameters every year, so it never has a chance to get off track. It just offers an amount in dollars that you can remove from your portfolios this year. That amount is gross, and includes all taxes you may have to pay. It's up to you to take over from there. 

ltr


----------



## RBull (Jan 20, 2013)

like_to_retire said:


> I think the two versions are a good compliment to each other. The original version offers lots of variables to play with, but perhaps a bit daunting for the uninitiated. It can be intimidating and might turn the regular user away. It's good to keep that version around though to allow people who like to dig deeper and play with variables.
> 
> The newer version is much simpler. As I said earlier, it could introduce errors over time, but the fact is, you are required to re-enter new parameters every year, so it never has a chance to get off track. It just offers an amount in dollars that you can remove from your portfolios this year. That amount is gross, and includes all taxes you may have to pay. It's up to you to take over from there.
> 
> ltr


I agree on the compliment. I had adjusted the assumptions on both stocks and bonds downward and also like the allocations fine tuning availability on the original. Updating it annually is what makes it work. 

I have since visited the second version and updated all our information on it. So now we have the 2. Since my wife has a DB pension and maybe in another 5 years we'll apply for CPP & OAS (TBD) it's very useful to have all of the potential gross income listed as a total on one sheet. And yes, much simpler.


----------



## gingymarathoner (Oct 1, 2020)

longinvest said:


> The newer VPW Worksheet has been designed as an easy-to-use retirement tool, taking into account current and future pensions with and without cost-of-living adjustments.


Longinvest, your work on the VPW project is amazing. As a math teacher and a person who respects data and its intelligent use, I think you and the bogleheads have come up with a way of conceptualizing retirement withdrawal that will appeal to most retirees who are not keen to leave a significant estate. 

I've completed the Retirement tab of the VPW version 1.5 spreadsheet (attached). I've read several of the FWF posts and threads on this topic. I think I have a better understanding of the design of the spreadsheet but there is something that is not making sense to me. When I decrease any of the DB pensions monthly amounts, the annual portfolio withdrawal amount increases. This doesn't make sense to me. If pension income is reduced, one would think, the portfolio withdrawal amount for this year would also fall given that the overall portfolio is lessened (less pension income in the future). I wonder if I've done something wrong with how I've populated the spreadsheet or how I'm understanding the spreadsheet. Is this a case of an issue of trying to complete one spreadsheet for a couple?


----------



## Spudd (Oct 11, 2011)

gingymarathoner said:


> Longinvest, your work on the VPW project is amazing. As a math teacher and a person who respects data and its intelligent use, I think you and the bogleheads have come up with a way of conceptualizing retirement withdrawal that will appeal to most retirees who are not keen to leave a significant estate.
> 
> I've completed the Retirement tab of the VPW version 1.5 spreadsheet (attached). I've read several of the FWF posts and threads on this topic. I think I have a better understanding of the design of the spreadsheet but there is something that is not making sense to me. When I decrease any of the DB pensions monthly amounts, the annual portfolio withdrawal amount increases. This doesn't make sense to me. If pension income is reduced, one would think, the portfolio withdrawal amount for this year would also fall given that the overall portfolio is lessened (less pension income in the future). I wonder if I've done something wrong with how I've populated the spreadsheet or how I'm understanding the spreadsheet. Is this a case of an issue of trying to complete one spreadsheet for a couple?


I believe this is because he is trying to equalize your income across years. So let's say you have a 50k/year pension coming later, you're 50 years old and you plan to die at 100.

Let's assume the stock market will be flat with no gains/losses to simplify the example.

If you just take simplistically the percent suggested per year (you don't enter your pensions into the worksheet), then your income will remain approximately constant throughout your life. I believe this is why the percent rises as you age - because you've been depleting the portfolio, to keep the same approx income, you would draw larger and larger amounts.

OK. So then when your pension kicks in, all of a sudden you're making (let's say) the 40k/year from VPW, PLUS your 50k pension for a total of 90k. That means when you're less than 60 you have to scrimp a bit, and when you're 60+ you can live high on the hog.

By increasing the percent withdrawal from the portfolio in the pre-pension years it evens it out so your standard of living remains more constant.

Edit: I put numbers that I thought would illustrate my point and then I realized I don't know the VPW suggested percentages so they ended up being useless numbers. Just ignore them.


----------



## longinvest (Sep 12, 2012)

Gingymarathoner, Thanks for the comments. In short, the $1,190,000 60/40 stocks/bonds portfolio is clearly insufficient; it can only fund 92% of pension bridges before the loss, and 65% of pension bridges after the loss. Instead of issuing a "You're not ready to retire!" error message, the spreadsheet does what it can with the situation using a simple calculation (it applies the funding ratio to all pension bridges). See this post. Possible improvements could be discussed on the official Canadian VPW thread.


----------



## gingymarathoner (Oct 1, 2020)

longinvest said:


> Gingymarathoner, Thanks for the comments. In short, the $1,190,000 60/40 stocks/bonds portfolio is clearly insufficient; it can only fund 92% of pension bridges before the loss, and 65% of pension bridges after the loss. Instead of issuing a "You're not ready to retire!" error message, the spreadsheet does what it can with the situation using a simple calculation (it applies the funding ratio to all pension bridges). See this post. Possible improvements could be discussed on the official Canadian VPW thread.


Thank you longinvest. If I use the VPW table at my husband's age next July (49) and 60/40 stocks/bonds, it indicates a safe withdrawal rate of 4.3% ($51,170 based on our current portfolio). If we can make this work (likely bu supplementing our income over the next few years, is there any reason that using the table rather than the spreadsheet is problematic?

Thanks.


----------



## longinvest (Sep 12, 2012)

Gingymarathoner, to better understand how VPW works, I suggest to carefully read my posts in the ongoing _forward test_ documented here.


----------

