## Amortization Based Withdrawal (ABW)

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Amortization Based Withdrawal (ABW)

When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.

Considering that the year 2000 was one of the worst starting periods for retirees, and 2000-2009 was a rough decade in general, we'll start the analysis in that year. We'll assume that we have an opposite sex couple aged 65 with a \$1 million starting portfolio. According to the SSA, there is only a 9% probability that either will be alive in 32 years (age 97), and they are comfortable with planning on their portfolio not being depleted before that time. It's important to keep in mind that they can adjust the period of time over which they want to amortize their portfolio whenever they wish. So the number of annual periods is 32.

The couple has no desire for a fixed bequest; they are fine with leaving behind whatever remains in their portfolio after they both pass away. So the future value is 0.

The last needed variable is the projected real rate of return. A historically conservative estimate could be placed here. Many would prefer to use valuations and current fixed income yields instead, so let's do that. According to Shiller's website, the CAPE on 1/1/2000 was 43.77, so the expected return for stocks over the next decade would be 1/43.77 or 2.3%. 10 year Treasury yields were 6.5%, and CPI for the year prior was 2.2%, so the expected real return of bonds was roughly 4.3%. So a 60/40 portfolio had an expected 10 year real return of 3.1%.

When we input all of these variables into the TMV and solve for the current payment, the withdrawal would have been \$49,716.40, leaving their balance at the beginning of 2000 at \$950,284.

Using the same method for calculating returns for each subsequent year, let's examine their withdrawals and portfolio balance each year going forward. To simplify the analysis, we'll assume that both spouses survive throughout this period but still wish to plan on exhausting their portfolio within the original 32 year period. Also, from 2003 forward, the real yield on 10 year TIPS at the start of each year are used instead of the difference between 10 year Treasuries and the prior year's CPI. Note that the portfolio balances are at the beginning of each year immediately following the annual withdrawal for that year.

2001:
Withdrawal - \$43,814
Balance - \$911,849

2002:
Withdrawal - \$44,008
Balance - \$827,549

2003:
Withdrawal - \$43,474
Balance - \$731,138

2004:
Withdrawal - \$48,215
Balance - \$821,707

2005:
Withdrawal - \$49,897
Balance - \$848,294

2006:
Withdrawal - \$51,565
Balance - \$837,398

2007:
Withdrawal - \$55,425.00
Balance - \$867,247

2008:
Withdrawal - \$57,446
Balance - \$874,526

2009:
Withdrawal - \$56,456
Balance - \$695,556

2010:
Withdrawal - \$53,597
Balance - \$733,423

2011:
Withdrawal - \$56,071
Balance - \$775,811

2012:
Withdrawal - \$49,296
Balance - \$781,404

Fast forward to 2019:
Withdrawal - \$77,000 (a 4% higher real withdrawal than 2000's)
Balance - \$769,802 (51% of the inflation-adjusted starting balance)

It's interesting to note that withdrawals dropped more from 2011 to 2012 than from 2008 to 2010. This is because 10 year TIPS had a negative real yield of -.04% at the beginning of 2012, so the rate of return used for that year was just 1.23%.

Notwithstanding the 12% drop in withdrawals from 2011 to 2012, I was surprised at how smooth the withdrawals over this turbulent decade were. Note that 2009's withdrawal did not drop much from the year prior because while the portfolio balance dropped significantly, expected returns for stocks, as measured by 1/CAPE, went up significantly, which came close to nullifying each other.

For those wanting to implement this type of withdrawal method but are afraid that it would be too complicated for their spouse to manage, a simple Excel spreadsheet could be created where all that would be necessary is for the spouse to enter the current portfolio balance, the number of years of desired withdrawals remaining, the current CAPE value (easily Googled), and the current 10 year TIPS yield (also easily Googled), and the TMV formula would indicate what the current withdrawal should be.

For more in-depth information, including current spreadsheets ready for use, see the Wiki page on the amortization based withdrawal (ABW) method.
Last edited by willthrill81 on Thu Nov 19, 2020 2:17 pm, edited 4 times in total.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
longinvest
Posts: 4447
Joined: Sat Aug 11, 2012 8:44 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

Let's compare this to our wiki's much simpler variable percentage withdrawal (VPW) approach.

I'll start with an identical \$1,000,000 60/40 stocks/bonds portfolio for an age 65 couple. The percentages were found in the 60/40 column of the VPW Table.

Code: Select all

``````                                 Nominal                Inflation Adj
VPW                    Portfolio                 Portfolio
Age  Year  Percentage    Withdrawal     Balance    Withdrawal     Balance
65   2000     5.0%           \$50,000   \$1,000,000      \$50,000   \$1,000,000
66   2001     5.1%           \$47,584     \$933,017      \$46,025     \$902,453
67   2002     5.1%           \$43,708     \$857,026      \$41,631     \$816,285
68   2003     5.2%           \$38,371     \$737,896      \$35,698     \$686,501
69   2004     5.3%           \$44,639     \$842,241      \$40,763     \$769,122
70   2005     5.4%           \$47,035     \$871,016      \$41,597     \$770,320
71   2006     5.5%           \$47,379     \$861,443      \$40,518     \$736,692
72   2007     5.6%           \$50,609     \$903,726      \$42,207     \$753,702
73   2008     5.7%           \$51,576     \$904,836      \$41,327     \$725,037
74   2009     5.9%           \$40,172     \$680,888      \$32,160     \$545,092
75   2010     6.0%           \$45,975     \$766,249      \$35,831     \$597,177
76   2011     6.2%           \$50,384     \$812,648      \$38,688     \$624,004
77   2012     6.3%           \$49,752     \$789,708      \$37,103     \$588,943
78   2013     6.5%           \$53,566     \$824,097      \$39,265     \$604,072
79   2014     6.7%           \$61,489     \$917,740      \$44,405     \$662,760
80   2015     6.9%           \$64,848     \$939,826      \$46,479     \$673,614
81   2016     7.2%           \$63,248     \$878,443      \$45,004     \$625,058
82   2017     7.5%           \$66,420     \$885,595      \$46,301     \$617,340
83   2018     7.8%           \$72,933     \$935,040      \$49,791     \$638,344
84   2019     8.1%           \$67,670     \$835,433      \$45,332     \$559,653
``````
At age 80 in early 2015, before taking their withdrawal, the couple considers the possibility of using part of their residual \$939,826 portfolio to dampen the financial risk of surviving beyond age 100.

I'll assume, for simplicity, that the couple can already live comfortably solely using their non-portfolio income, consisting of Social Security and a generous pension. So, they don't buy an annuity.

In 2019, at age 84, their withdrawal is \$67,670, representing 9% less in inflation-adjusted terms than their initial \$50,000 withdrawal at age 65. Their residual portfolio, after withdrawal, is \$767,763, representing 51% of the initial portfolio in inflation-adjusted terms.

Here's the important difference, though. The spouse responsible for finances, in the couple, might have died at age 75, in 2010. Luckily, the spouse left easy-to-follow instruction to the surviving spouse in the will and wrote to withdraw 6.2% of the portfolio balance in 2011, 6.3% of the portfolio balance in 2012, etc. That's all. No need to calculate weird ratios like 1/CAPE and find out appropriate TIPS yields.

I don't know about others, but I know that my wife would be able to follow a VPW table, but wouldn't know where to start to lookup Canadian CAPE ratios (I, myself, don't know where to find that!) and Real-Return Bond yields (the equivalent of TIPS in Canada).

But, I could see the attraction of TVM calculations for a spreadsheet aficionado single person.
Last edited by longinvest on Wed Feb 27, 2019 7:17 pm, edited 1 time in total.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

longinvest wrote: Wed Feb 27, 2019 6:33 pmHere's the important difference, though. The spouse responsible for finances, in the couple, might have died at age 75, in 2010. Luckily, the spouse left easy-to-follow instruction to the surviving spouse in his will. He said to withdraw 6.2% of the portfolio balance in 2011, 6.3% of the portfolio balance in 2012, etc. That's all. No need to calculate weird ratios like 1/CAPE and find out appropriate TIPS yields.

I don't know about others, but I know that my wife would be able to follow a VPW table, but wouldn't know where to start to lookup Canadian CAPE ratios (I, myself, don't know where to find that!) and Real-Return Bond yields (the equivalent of TIPS in Canada).

But, I could see the attraction of TVM calculations for a spreadsheet aficionado single person.
There's no need for a spouse to calculate anything. As I said in the OP, you can create a very simple spreadsheet that only requires the user to input the current ratios. I don't know about Canadian CAPE ratios, but for the U.S., it is very easily Googled, and the same goes for TIPS yields. But to make things even simpler, you could just input a conservative, fixed real rate of return like 2-3%.

It is true that my simple example could be taken very far, incorporating multiple income streams at different points in time, but that's far from necessary.

Here's a huge advantage to the TMV approach over VPW: what if a retiree has a serious illness and doesn't want to amortize their portfolio until age 100? Perhaps they don't believe they'll live more than 10 years. Using the TMV approach, you simply change the remaining number of periods.

Also, the TMV approach above would have resulted in significantly smoother withdrawals than VPW, yet still carries the advantage of never prematurely depleting the portfolio.

Yet again, you are bashing anything that isn't VPW.
Last edited by willthrill81 on Wed Feb 27, 2019 6:53 pm, edited 1 time in total.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
longinvest
Posts: 4447
Joined: Sat Aug 11, 2012 8:44 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

In my example, I've made explicit that the couple had ample non-portfolio income, justifying the use of a relatively volatile 60/40 stocks/bonds portfolio in retirement.

It might be important to remind readers that a robust approach to dampen stock volatility, and thus withdrawals of a percent-of-portfolio approach, is to include an appropriate ratio of bonds in the portfolio. Vanguard's Target Retirement Income Fund has a 30/70 stocks/bonds allocation, which has a very smooth behavior, while still being exposed to the growth of stocks, just not as much as a 60/40 portfolio. It would have delivered much smoother withdrawals. Let's test a 30/70 US-stocks/US-bonds portfolio with 30/70 VPW Table percentages:

Code: Select all

``````                           Nominal                Inflation Adj
VPW                    Portfolio                 Portfolio
Age  Year  Percentage   Withdrawal     Balance    Withdrawal     Balance
65   2000     4.4%           \$44,000   \$1,000,000      \$44,000   \$1,000,000
66   2001     4.5%           \$45,086   \$1,001,913      \$43,609     \$969,092
67   2002     4.5%           \$44,181     \$981,800      \$42,081     \$935,127
68   2003     4.6%           \$42,911     \$932,859      \$39,923     \$867,883
69   2004     4.7%           \$46,925     \$998,410      \$42,851     \$911,734
70   2005     4.8%           \$48,741   \$1,015,432      \$43,106     \$898,041
71   2006     4.9%           \$49,012   \$1,000,246      \$41,914     \$855,393
72   2007     5.0%           \$51,196   \$1,023,922      \$42,697     \$853,945
73   2008     5.2%           \$53,866   \$1,035,876      \$43,162     \$830,038
74   2009     5.3%           \$48,104     \$907,628      \$38,510     \$726,610
75   2010     5.5%           \$53,308     \$969,230      \$41,545     \$755,370
76   2011     5.6%           \$56,228   \$1,004,063      \$43,175     \$770,986
77   2012     5.8%           \$58,042   \$1,000,717      \$43,286     \$746,307
78   2013     6.0%           \$60,921   \$1,015,351      \$44,656     \$744,263
79   2014     6.2%           \$64,158   \$1,034,805      \$46,333     \$747,301
80   2015     6.4%           \$66,941   \$1,045,959      \$47,980     \$749,685
81   2016     6.7%           \$65,852     \$982,865      \$46,857     \$699,361
82   2017     6.9%           \$66,819     \$968,394      \$46,579     \$675,058
83   2018     7.3%           \$71,661     \$981,652      \$48,922     \$670,166
84   2019     7.6%           \$68,076     \$895,732      \$45,604     \$600,047
``````
If the retired couple didn't have much non-portfolio income, they would have preferably chosen such a smoother portfolio, and would probably have bought an inflation-adjusted Single-Premium Immediate Annuity in early 2015 at age 80 using part of their pre-withdrawal \$1,045,959 portfolio.

I have a single life. I will never bet the peacefulness of my retirement on simplistic prediction metrics like CAPE. Others can do whatever they wish. It's their money and their life.
Last edited by longinvest on Wed Feb 27, 2019 7:00 pm, edited 1 time in total.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

longinvest wrote: Wed Feb 27, 2019 6:51 pmI have a single life. I will never bet the peacefulness of my retirement on simplistic prediction metrics like CAPE.
You say this, but then you have no problem with VPW being based on historic market returns?

Further, even if the rate of return is off, it will only result in the current withdrawal being a little too low or high, and future withdrawals being a little higher or lower than they would have been otherwise.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
sixtyforty
Posts: 503
Joined: Tue Nov 25, 2014 12:22 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.
...
Thanks for posting this !
"Simplicity is the ultimate sophistication" - Leonardo Da Vinci
RoxieII
Posts: 62
Joined: Thu Dec 18, 2014 9:40 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

Hello,
Not sure how this different than a PMT formula? \$1,000,000 initial, interest rate of 3.1%, time period of 32 years equals an annual payment of \$49,308. I did not run your other numbers.

This is a hot topic for me right now and I created an excel spreadsheet around this, just three inputs: 1) current retirement balance, 2) number of years to go, and 3) an assumed rate. The PMT formula uses these three inputs and gives an income.

The biggest part of the equation is what rate to assume. Although, does it really matter? If you assume a low rate, you withdraw less, the portfolio increases more, next year you withdraw more. Any rate errors wash out over time?

All the best,
Rox
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

RoxieII wrote: Wed Feb 27, 2019 11:03 pm Hello,
Not sure how this different than a PMT formula? \$1,000,000 initial, interest rate of 3.1%, time period of 32 years equals an annual payment of \$49,308. I did not run your other numbers.
I verified that the numbers in the OP are correct (i.e. \$49,716 is first withdrawal), so I'm not sure where the slight discrepancy is coming from. Since you're using Excel, that might be the source. A number of Excel's formulas are known to have computational errors, and the TMV formula might be one of them.
RoxieII wrote: Wed Feb 27, 2019 11:03 pmThis is a hot topic for me right now and I created an excel spreadsheet around this, just three inputs: 1) current retirement balance, 2) number of years to go, and 3) an assumed rate. The PMT formula uses these three inputs and gives an income.
There is also a fourth variable, the future (aka terminal) value of the portfolio. In this context, it is generally set to zero, but that wouldn't be necessary. If someone wanted to leave behind a bequest of at least a certain dollar amount or wanted a reserve for something like long-term care, they might wish to have a positive value for this.
RoxieII wrote: Wed Feb 27, 2019 11:03 pmThe biggest part of the equation is what rate to assume. Although, does it really matter? If you assume a low rate, you withdraw less, the portfolio increases more, next year you withdraw more. Any rate errors wash out over time?
Yes, if your rate of return is set too high, then all you've done is 'front-load' your current withdrawal and reduce your later withdrawals and vice versa for overly conservative rate of return estimates. In fact, since research indicates that retirees tend to reduce their spending by 1-2% annually in real dollars, you could purposefully add 1-2% to what you believe your returns would actually be in order to front-load your withdrawals somewhat. If a retiree wanted to spend more dollars on discretionary things like travelling during their 'go-go' years and was fine with smaller withdrawals later on in their retirement, this would be a very easy way to do so.

So in the OP's example, if the retiree wanted to front-load withdrawals somewhat, they could use a rate of return of 4.1% (3.1% assumed portfolio return + 1% front-loading), which would result in a withdrawal of \$56,663, a 14% increase in that year's withdrawal. Consequently, this will eventually reduce the size of future withdrawals compared to not front-loading them.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
dknightd
Posts: 2368
Joined: Wed Mar 07, 2018 11:57 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 6:50 pm
It is true that my simple example could be taken very far, incorporating multiple income streams at different points in time, but that's far from necessary.

Here's a huge advantage to the TMV approach over VPW: what if a retiree has a serious illness and doesn't want to amortize their portfolio until age 100? Perhaps they don't believe they'll live more than 10 years. Using the TMV approach, you simply change the remaining number of periods.
I think being able to include future income streams is the most valuable part of the TVM method (I think it can also be included in the VPF method.)

I still need to dig into both these better to fuller understand them. I think I'll probably use both every year, along with other methods, then use some kind of average for what I consider safe for that year. This is still a work in progress for me since I don't retire for 6 months . . .

Edit - thanks to both of you, and others, for sharing your thoughts with us
If you value a bird in the hand, pay off the loan. If you are willing to risk getting two birds (or none) from the market, invest the funds.
RoxieII
Posts: 62
Joined: Thu Dec 18, 2014 9:40 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

Any thoughts on how to adjust for the changes in portfolio, for example, a 20% drop?
GAAP
Posts: 1154
Joined: Fri Apr 08, 2016 12:41 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

RoxieII wrote: Thu Feb 28, 2019 12:08 pm Any thoughts on how to adjust for the changes in portfolio, for example, a 20% drop?
I would probably just create a formula that simulated the drop, using a lookup table for the year and the amount of drop desired.

You could then add it as an additional withdrawal in a particular year and then use TVM concepts to model the results after the change.

If you want to "protect" yourself, you could also deduct the 20% up-front and base your modeling on the resulting balance.
“Adapt what is useful, reject what is useless, and add what is specifically your own.” ― Bruce Lee
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

RoxieII wrote: Thu Feb 28, 2019 12:08 pm Any thoughts on how to adjust for the changes in portfolio, for example, a 20% drop?
That happened in the example I provided. From 2008 to 2009, the porfolio dropped by just over 20%. Withdrawals didn't drop much because the projected rate of return of stocks increased significantly from 2008 to 2009. Note that if you were using a fixed percentage approach or even the VPW method, your withdrawals would be as volatile as your portfolio. Using the 1/CAPE approach to estimate forward stock returns smooths out withdrawals in a logical way. If stocks drop significantly, that reduces the size of your portfolio, but it also results in increased expectations for future stock performance. From 2008 to 2009, this roughly balanced out.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Thu Feb 28, 2019 12:39 pm
RoxieII wrote: Thu Feb 28, 2019 12:08 pm Any thoughts on how to adjust for the changes in portfolio, for example, a 20% drop?
That happened in the example I provided. From 2008 to 2009, the porfolio dropped by just over 20%. Withdrawals didn't drop much because the projected rate of return of stocks increased significantly from 2008 to 2009. Note that if you were using a fixed percentage approach or even the VPW method, your withdrawals would be as volatile as your portfolio. Using the 1/CAPE approach to estimate forward stock returns smooths out withdrawals in a logical way. If stocks drop significantly, that reduces the size of your portfolio, but it also results in increased expectations for future stock performance. From 2008 to 2009, this roughly balanced out.
If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
Once in a while you get shown the light, in the strangest of places if you look at it right.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Thu Feb 28, 2019 12:51 pm
willthrill81 wrote: Thu Feb 28, 2019 12:39 pm
RoxieII wrote: Thu Feb 28, 2019 12:08 pm Any thoughts on how to adjust for the changes in portfolio, for example, a 20% drop?
That happened in the example I provided. From 2008 to 2009, the porfolio dropped by just over 20%. Withdrawals didn't drop much because the projected rate of return of stocks increased significantly from 2008 to 2009. Note that if you were using a fixed percentage approach or even the VPW method, your withdrawals would be as volatile as your portfolio. Using the 1/CAPE approach to estimate forward stock returns smooths out withdrawals in a logical way. If stocks drop significantly, that reduces the size of your portfolio, but it also results in increased expectations for future stock performance. From 2008 to 2009, this roughly balanced out.
If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
A constant SWR, if followed to the letter, has a non-zero chance of premature portfolio depletion, whereas that is a mathematical impossibility for the TMV approach.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
Deltoid
Posts: 18
Joined: Wed Nov 23, 2016 3:07 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

This is the most intuitively appealing withdrawal method to me.

I really like your idea of creating a spreadsheet requiring only simple market measures as inputs for a spouse to follow.
#Cruncher
Posts: 3076
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 5:47 pmWe'll assume that we have an opposite sex couple aged 65 ... According to the SSA, there is only a 9% probability that they will both be alive in 32 years (age 97) … (underline added)
I think you mean either will be alive, Will. Based on the SSA 2015 Period Life Table, there is an 11.41% chance that at least one of a man/woman couple age 65 will be alive after 32 years, but only a 0.30% chance that both will be alive. (As computed by my longevity estimator spreadsheet.)
willthrill81 in same post wrote:When we input all of these variables into the TMV and solve for the current payment, the withdrawal would have been \$49,716.40, leaving their balance at the beginning of 2000 at \$950,284.
You've calculated what the withdrawal would be if taken at the end of the year. However, you appear to be treating it as if it is taken at the beginning. In this case it would need to be about \$1,500 less as shown by the Excel PMT function, where the last parameter (0 or 1) indicates whether payment is at the end or start of the period.
49,716.40 = PMT(3.1%, 32, -1000000, 0, 0) : withdrawal at end of year
48,221.54 = PMT(3.1%, 32, -1000000, 0, 1) : withdrawal at start of year
willthrill81 in same post wrote:To simplify the analysis, we'll assume that both spouses survive throughout this period but still wish to plan on exhausting their portfolio within the original 32 year period. (underline added)
That's a simplification that dramatically alters the result. The same life table that shows at least one of the couple having an 11.41% chance of living 32 years when they're 65, shows only a 0.17% chance when they are 75.
willthrill81 wrote: Wed Feb 27, 2019 11:52 pm
RoxieII wrote: Wed Feb 27, 2019 11:03 pm… Not sure how this different than a PMT formula? \$1,000,000 initial, interest rate of 3.1%, time period of 32 years equals an annual payment of \$49,308. ...
I verified that the numbers in the OP are correct (i.e. \$49,716 is first withdrawal), so I'm not sure where the slight discrepancy is coming from.
The difference arises because Roxell is assuming monthly rather than annual withdrawals:
49,308.80 = PMT(3.1% / 12, 32 * 12, -1000000, 0, 0) * 12
willthrill81 in same post wrote:Since you're using Excel, that might be the source. A number of Excel's formulas are known to have computational errors, and the TMV formula might be one of them.
I'm not aware of such errors, Will; could you give an example. Also you speak of the TVM formula. But there is no such function in Excel. Rather there are five related functions that all take into account the time value of money: RATE, NPER, PMT, PV, and FV.

Also, I don't see where you actually show the formula you keep referring to. For those who don't want to rely on a spreadsheet function (i.e., PMT,) here it is:

Code: Select all

``````If r = 0
withdrawal = present value / n
Otherwise (if withdrawal at end of period)
withdrawal = present value / ((1 - 1 / (1 + r)     ^ n)  / r)
49,716.40 = 1000000       / ((1 - 1 / (1 + 0.031) ^ 32) / 0.031)
(If withdrawal at start of period, divide result above by 1 + r)``````
H-Town
Posts: 2912
Joined: Sun Feb 26, 2017 2:08 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.
How does this change if we look at absolute dollar withdrawal?

Person A is projected to spend on average \$50k a year. He saved 50x his annual expense to retire at 50 year old. He chose a 50/50 allocation. Each year, he can withdraw \$50k (plus inflation) in absolute dollar amount to pay for this living expenses. Any market return will be his discretionary spending. He has a very low chance of running out of money.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

#Cruncher wrote: Thu Feb 28, 2019 5:12 pm
willthrill81 wrote: Wed Feb 27, 2019 5:47 pmWe'll assume that we have an opposite sex couple aged 65 ... According to the SSA, there is only a 9% probability that they will both be alive in 32 years (age 97) … (underline added)
I think you mean either will be alive, Will. Based on the SSA 2015 Period Life Table, there is an 11.41% chance that at least one of a man/woman couple age 65 will be alive after 32 years, but only a 0.30% chance that both will be alive. (As computed by my longevity estimator spreadsheet.)
Yes, I meant either. Good catch.
#Cruncher wrote: Thu Feb 28, 2019 5:12 pm
willthrill81 in same post wrote:When we input all of these variables into the TMV and solve for the current payment, the withdrawal would have been \$49,716.40, leaving their balance at the beginning of 2000 at \$950,284.
You've calculated what the withdrawal would be if taken at the end of the year. However, you appear to be treating it as if it is taken at the beginning. In this case it would need to be about \$1,500 less as shown by the Excel PMT function, where the last parameter (0 or 1) indicates whether payment is at the end or start of the period.
49,716.40 = PMT(3.1%, 32, -1000000, 0, 0) : withdrawal at end of year
48,221.54 = PMT(3.1%, 32, -1000000, 0, 1) : withdrawal at start of year
I was attempting to calculate the withdrawals at the beginning of each year, but now I see that the default option on my calculator was to take the withdrawal at the end of the period rather than at the beginning. So that will change the numbers in the OP somewhat. Good catch.
#Cruncher wrote: Thu Feb 28, 2019 5:12 pmThe difference arises because Roxell is assuming monthly rather than annual withdrawals:
49,308.80 = PMT(3.1% / 12, 32 * 12, -1000000, 0, 0) * 12
Ah, that makes sense.
#Cruncher wrote: Thu Feb 28, 2019 5:12 pm
willthrill81 in same post wrote:Since you're using Excel, that might be the source. A number of Excel's formulas are known to have computational errors, and the TMV formula might be one of them.
I'm not aware of such errors, Will; could you give an example. Also you speak of the TVM formula. But there is no such function in Excel. Rather there are five related functions that all take into account the time value of money: RATE, NPER, PMT, PV, and FV.

Also, I don't see where you actually show the formula you keep referring to. For those who don't want to rely on a spreadsheet function (i.e., PMT,) here it is:

Code: Select all

``````If r = 0
withdrawal = present value / n
Otherwise (if withdrawal at end of period)
withdrawal = present value / ((1 - 1 / (1 + r)     ^ n)  / r)
49,716.40 = 1000000       / ((1 - 1 / (1 + 0.031) ^ 32) / 0.031)
(If withdrawal at start of period, divide result above by 1 + r)``````
I'm just using the TVM function on a phone app. The computational errors involved in Excel formulas are numerous. Here's an example of many lamenting over this on MS's website.
Last edited by willthrill81 on Thu Feb 28, 2019 8:41 pm, edited 1 time in total.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

H-Town wrote: Thu Feb 28, 2019 5:26 pm
willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.
How does this change if we look at absolute dollar withdrawal?

Person A is projected to spend on average \$50k a year. He saved 50x his annual expense to retire at 50 year old. He chose a 50/50 allocation. Each year, he can withdraw \$50k (plus inflation) in absolute dollar amount to pay for this living expenses. Any market return will be his discretionary spending. He has a very low chance of running out of money.
I'm not sure what your question is. How would you determine 'market return'? Does this mean that if the portfolio ever drops below its inflation-adjusted starting value that only an inflation-adjusted \$50k is withdrawn?
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
H-Town
Posts: 2912
Joined: Sun Feb 26, 2017 2:08 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Thu Feb 28, 2019 5:45 pm
H-Town wrote: Thu Feb 28, 2019 5:26 pm
willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.
How does this change if we look at absolute dollar withdrawal?

Person A is projected to spend on average \$50k a year. He saved 50x his annual expense to retire at 50 year old. He chose a 50/50 allocation. Each year, he can withdraw \$50k (plus inflation) in absolute dollar amount to pay for this living expenses. Any market return will be his discretionary spending. He has a very low chance of running out of money.
I'm not sure what your question is. How would you determine 'market return'? Does this mean that if the portfolio ever drops below its inflation-adjusted starting value that only an inflation-adjusted \$50k is withdrawn?
Person A will withdraw \$50k, regardless of market conditions. If market is good to him, he will withdraw some of the gain in the year for his discretionary expenses. If the market is down, he does not have those discretionary spending.

This perspective look at two things:
1) Did you save enough? 50 x \$50k = \$2.5M should allow you to have at least 50 year of spending - more or less.
2) Variable spending depends on how the market does. This looks at cumulative return, rather gain/loss in just one particular year.
ionball
Posts: 236
Joined: Wed Jan 31, 2018 12:17 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 6:50 pm
longinvest wrote: Wed Feb 27, 2019 6:33 pmHere's the important difference, though. The spouse responsible for finances, in the couple, might have died at age 75, in 2010. Luckily, the spouse left easy-to-follow instruction to the surviving spouse in his will. He said to withdraw 6.2% of the portfolio balance in 2011, 6.3% of the portfolio balance in 2012, etc. That's all. No need to calculate weird ratios like 1/CAPE and find out appropriate TIPS yields.

I don't know about others, but I know that my wife would be able to follow a VPW table, but wouldn't know where to start to lookup Canadian CAPE ratios (I, myself, don't know where to find that!) and Real-Return Bond yields (the equivalent of TIPS in Canada).

But, I could see the attraction of TVM calculations for a spreadsheet aficionado single person.
There's no need for a spouse to calculate anything. As I said in the OP, you can create a very simple spreadsheet that only requires the user to input
Something to consider-
For my spouse, "input" makes the spreadsheet too complex. Especially at an advanced age! If I mention CAPE, the google search will likely lead to vampires and superheroes. I appreciate the quest for new improved solutions, but simplicity for my successor is critical.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

ionball wrote: Thu Feb 28, 2019 6:24 pm
willthrill81 wrote: Wed Feb 27, 2019 6:50 pm
longinvest wrote: Wed Feb 27, 2019 6:33 pmHere's the important difference, though. The spouse responsible for finances, in the couple, might have died at age 75, in 2010. Luckily, the spouse left easy-to-follow instruction to the surviving spouse in his will. He said to withdraw 6.2% of the portfolio balance in 2011, 6.3% of the portfolio balance in 2012, etc. That's all. No need to calculate weird ratios like 1/CAPE and find out appropriate TIPS yields.

I don't know about others, but I know that my wife would be able to follow a VPW table, but wouldn't know where to start to lookup Canadian CAPE ratios (I, myself, don't know where to find that!) and Real-Return Bond yields (the equivalent of TIPS in Canada).

But, I could see the attraction of TVM calculations for a spreadsheet aficionado single person.
There's no need for a spouse to calculate anything. As I said in the OP, you can create a very simple spreadsheet that only requires the user to input
Something to consider-
For my spouse, "input" makes the spreadsheet too complex. Especially at an advanced age! If I mention CAPE, the google search will likely lead to vampires and superheroes. I appreciate the quest for new improved solutions, but simplicity for my successor is critical.
In that case, hiring someone like an accountant to actually take care of the mechanics of withdrawals in the event of your passing might be optimal.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
AlohaJoe
Posts: 5572
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Thu Feb 28, 2019 12:51 pm If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
They don't perfectly cancel each other out, nor should they be expected to.

At a superficial level we can see that they don't perfectly cancel each other out by noticing that willthrill81 didn't post the same exact numbers every year; we can clearly see there are years with (nominal) drops (which means the real drop will be even a bit steeper).

At a more detailed level we can take a look at a backtest to see what would happen in various years:

marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

Nice analysis, thanks for running that, it does provide nice smoothing of withdrawals.

I do realize they don't cancel perfectly, I was being facetious. Poking a little fun at the goal of trying to smooth the fluctuations in the PMT approach. If a little smoothing is good, a lot is better, right? Taking it to the extreme, get you back towards where you started, there is no free lunch.

I do like the idea. I have been playing around a lot with the spread sheet that Siamond provided in another thread that allows one to combine this idea with other income streams coming on line at various times.
AlohaJoe wrote: Thu Feb 28, 2019 9:42 pm
marcopolo wrote: Thu Feb 28, 2019 12:51 pm If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
They don't perfectly cancel each other out, nor should they be expected to.

At a superficial level we can see that they don't perfectly cancel each other out by noticing that willthrill81 didn't post the same exact numbers every year; we can clearly see there are years with (nominal) drops (which means the real drop will be even a bit steeper).

At a more detailed level we can take a look at a backtest to see what would happen in various years:

Once in a while you get shown the light, in the strangest of places if you look at it right.
AlohaJoe
Posts: 5572
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

RoxieII wrote: Wed Feb 27, 2019 11:03 pm Hello,
Not sure how this different than a PMT formula?
As suggested by dknightd's reply, it isn't. I mean...it is using the PMT formula, reevaluated every year.

In the interest of simplicity, willthrill81 left out the ability to handle future cashflows, which is where this approach really shines.

Let's say you are going to retire at age 60 and:
• Plan on getting Social Security at age 70
• Plan on selling your house in 2-3 years and moving closer to the grandchildren
• Plan on paying for your granddaughter's university in 8 years, as a gift to your daughter
• Want to take a round-the-world cruise (\$60,000) in the next year or two.
And you are kinda wondering how that all shakes out. The old SWR "just spend \$80,000 a year and you'll be fine" doesn't really help when you know your spending will be all over the place.

There are ways to do all that that don't involve time value of money calculations. A lot of people will just go "I'll ignore the proceeds of the house downsizing and count it as a bonus if/when it happens", "I'll just put \$60,000 in a 24-month CD and ignore that part of my portfolio", "I'll just build a TIPS ladder to fund my granddaughter's education", and so on.

That's a viable approach -- really it comes down to the ancient argument between safety-first and probability-based schools of retirement planning. Like all religious arguments, the fact that they share so much of their philosophies in common is the reason the arguments get so heated

This approach is detailed more in a pair of blogposts siamond wrote for the Bogleheads blog.

https://finpage.blog/2019/02/01/early-r ... -of-money/
https://finpage.blog/2019/02/03/early-r ... ey-part-2/

It is the same approach Ken Steiner has suggested for years on his website http://howmuchcaniaffordtospendinretire ... gspot.com/
AlohaJoe
Posts: 5572
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Thu Feb 28, 2019 9:52 pm I do realize they don't cancel perfectly, I was being facetious. Poking a little fun at the goal of trying to smooth the fluctuations in the PMT approach. If a little smoothing is good, a lot is better, right? Taking it to the extreme, get you back towards where you started, there is no free lunch.
Investment risk translates directly to consumption risk. There is a strain of thought that says if your withdrawals are too volatile then it just means your portfolio is too volatile. Instead of engaging in income smoothing strategies, you should just use more bonds (or some other low-vol asset/strategy; how would a volatility targeting strategy work here, for instance?).

When you introduce income smoothing you are taking your personal pension (which is what our retirement fund actually is) and making it "underfunded", just like all those state pensions we love to hate on. You have introduced a new kind of risk -- that the market doesn't revert or doesn't revert in time or doesn't revert enough.
Even with smoothing, the true underlying, unsmoothed risk is realized whenever there is a long period of disappointing returns
That said, I personally think we shouldn't be too quick to write off smoothing.

First, if we keep in mind the "personal pension" model, then smoothing only really becomes a problem if we actually become underfunded. If our funded ratio -- viewtopic.php?t=219878 -- is, say, 2.1 then we clearly have an overfunded personal pension and can stand quite a lot of income smoothing before we actually get anywhere close to being underfunded.

What's more, I'm sympathetic to the arguments made by Gordon Pye that pre-emptive cuts to our retirement don't really make a lot of sense in many circumstances. With income smoothing, we are often talking about scenarios where we have two choices something like:

1. We cut \$5,000 a year now, or
2. If markets don't recover then in 5 years we need to cut \$8,000

The steeper cut five years from now sounds worse. Except in 5 years, we'll be five years older. We'll be less active. And so on. So delaying cutting spending has some arguments in its favor.

Anyway, I wrote two blog articles on the subject, one about personal pensions & underfunding:

https://medium.com/@justusjp/unfunded-p ... d33df74ca3

and one a survey of income smoothing strategies

https://medium.com/@justusjp/income-smo ... 0062771466
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

AlohaJoe wrote: Thu Feb 28, 2019 10:08 pm
marcopolo wrote: Thu Feb 28, 2019 9:52 pm I do realize they don't cancel perfectly, I was being facetious. Poking a little fun at the goal of trying to smooth the fluctuations in the PMT approach. If a little smoothing is good, a lot is better, right? Taking it to the extreme, get you back towards where you started, there is no free lunch.
Investment risk translates directly to consumption risk. There is a strain of thought that says if your withdrawals are too volatile then it just means your portfolio is too volatile. Instead of engaging in income smoothing strategies, you should just use more bonds (or some other low-vol asset/strategy; how would a volatility targeting strategy work here, for instance?).

When you introduce income smoothing you are taking your personal pension (which is what our retirement fund actually is) and making it "underfunded", just like all those state pensions we love to hate on. You have introduced a new kind of risk -- that the market doesn't revert or doesn't revert in time or doesn't revert enough.
Even with smoothing, the true underlying, unsmoothed risk is realized whenever there is a long period of disappointing returns
That said, I personally think we shouldn't be too quick to write off smoothing.

First, if we keep in mind the "personal pension" model, then smoothing only really becomes a problem if we actually become underfunded. If our funded ratio -- viewtopic.php?t=219878 -- is, say, 2.1 then we clearly have an overfunded personal pension and can stand quite a lot of income smoothing before we actually get anywhere close to being underfunded.

What's more, I'm sympathetic to the arguments made by Gordon Pye that pre-emptive cuts to our retirement don't really make a lot of sense in many circumstances. With income smoothing, we are often talking about scenarios where we have two choices something like:

1. We cut \$5,000 a year now, or
2. If markets don't recover then in 5 years we need to cut \$8,000

The steeper cut five years from now sounds worse. Except in 5 years, we'll be five years older. We'll be less active. And so on. So delaying cutting spending has some arguments in its favor.

Anyway, I wrote two blog articles on the subject, one about personal pensions & underfunding:

https://medium.com/@justusjp/unfunded-p ... d33df74ca3

and one a survey of income smoothing strategies

https://medium.com/@justusjp/income-smo ... 0062771466
I think most people do is type of smoothing whether it is formulaic, or ad hoc. It would be useful to have a framework on which to base those adjustments.

Thanks for the links to the articles. I will definitely take a look.
Once in a while you get shown the light, in the strangest of places if you look at it right.
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

AlohaJoe wrote: Thu Feb 28, 2019 10:08 pm

Anyway, I wrote two blog articles on the subject, one about personal pensions & underfunding:

https://medium.com/@justusjp/unfunded-p ... d33df74ca3

and one a survey of income smoothing strategies

https://medium.com/@justusjp/income-smo ... 0062771466

Just read through this and had a follow-up question.

What did you use for a discount rate in the analysis for the articles? That seems like a critical piece to this.
The idea of modifying that based on bond yields and CAPE is kind of interesting, I am just not sure how much faith i have in CAPE as a proxy for forward returns. Not much actually, but it might be the best we have.

What are your thoughts on that, do you use a fixed discount rate, or try to vary using some other metric?
Once in a while you get shown the light, in the strangest of places if you look at it right.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

AlohaJoe wrote: Thu Feb 28, 2019 9:52 pmIn the interest of simplicity, willthrill81 left out the ability to handle future cashflows, which is where this approach really shines.
Precisely. Using the TVM formula can be as complex as the user wants it to be, and the example I provided was a simplistic one. Even so, we see some people remarking on the example in the OP still being too complicated for those who are uninterested in the topic. I think that providing a ready-made spreadsheet with links to the relevant data points or even a simple web page that included that year's numbers and users would just input the number of years of desired withdrawals and portfolio size would be very helpful. That goes beyond my skill level, but it's well within the wheelhouse of many here. Perhaps someone will pick up that mantle.
AlohaJoe wrote: Thu Feb 28, 2019 10:08 pmWhat's more, I'm sympathetic to the arguments made by Gordon Pye that pre-emptive cuts to our retirement don't really make a lot of sense in many circumstances.
I completely concur. It's a bit like giving a person medication for a disease they haven't gotten yet and may never get, and the medication will make them sick, maybe for a while and maybe for the rest of their life.
AlohaJoe wrote: Thu Feb 28, 2019 10:08 pmThe steeper cut five years from now sounds worse. Except in 5 years, we'll be five years older. We'll be less active.
And maybe dead.
AlohaJoe wrote: Thu Feb 28, 2019 9:42 pmAt a more detailed level we can take a look at a backtest to see what would happen in various years
Thanks. That's very useful.

I wonder if you've looked into the average investor stock allocation as another potential means of forecasting future returns. It's certainly done well since Philosophical Economics proposed it. I haven't compared its results since publication to CAPE, but maybe a combination of both would be useful for estimating forward stock returns. Just an idea.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Thu Feb 28, 2019 11:57 pm
AlohaJoe wrote: Thu Feb 28, 2019 9:52 pmIn the interest of simplicity, willthrill81 left out the ability to handle future cashflows, which is where this approach really shines.
Precisely. Using the TVM formula can be as complex as the user wants it to be, and the example I provided was a simplistic one. Even so, we see some people remarking on the example in the OP still being too complicated for those who are uninterested in the topic. I think that providing a ready-made spreadsheet with links to the relevant data points or even a simple web page that included that year's numbers and users would just input the number of years of desired withdrawals and portfolio size would be very helpful. That goes beyond my skill level, but it's well within the wheelhouse of many here. Perhaps someone will pick up that mantle.

I your other thread about TVM vs. VPW, Siamond provided a link to his article which links to a spread sheet, and provides detailed explanation of a very thorough TVM based approach to withdrawal estimation.

Here is a link to his article again:

https://finpage.blog/2019/02/01/early-r ... -of-money/
Once in a while you get shown the light, in the strangest of places if you look at it right.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Fri Mar 01, 2019 12:05 am
willthrill81 wrote: Thu Feb 28, 2019 11:57 pm
AlohaJoe wrote: Thu Feb 28, 2019 9:52 pmIn the interest of simplicity, willthrill81 left out the ability to handle future cashflows, which is where this approach really shines.
Precisely. Using the TVM formula can be as complex as the user wants it to be, and the example I provided was a simplistic one. Even so, we see some people remarking on the example in the OP still being too complicated for those who are uninterested in the topic. I think that providing a ready-made spreadsheet with links to the relevant data points or even a simple web page that included that year's numbers and users would just input the number of years of desired withdrawals and portfolio size would be very helpful. That goes beyond my skill level, but it's well within the wheelhouse of many here. Perhaps someone will pick up that mantle.

I your other thread about TVM vs. VPW, Siamond provided a link to his article which links to a spread sheet, and provides detailed explanation of a very thorough TVM based approach to withdrawal estimation.

Here is a link to his article again:

https://finpage.blog/2019/02/01/early-r ... -of-money/
Siamond's done great work in this area. But I was referring to a web site that already incorporated 1/CAPE for stock returns, some measure like 10 year TIPS yield, etc. so that users could just input their current portfolio size and number of desired years of amortization to get their current withdrawal. Something very, very simple.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Fri Mar 01, 2019 12:21 am
marcopolo wrote: Fri Mar 01, 2019 12:05 am
willthrill81 wrote: Thu Feb 28, 2019 11:57 pm
AlohaJoe wrote: Thu Feb 28, 2019 9:52 pmIn the interest of simplicity, willthrill81 left out the ability to handle future cashflows, which is where this approach really shines.
Precisely. Using the TVM formula can be as complex as the user wants it to be, and the example I provided was a simplistic one. Even so, we see some people remarking on the example in the OP still being too complicated for those who are uninterested in the topic. I think that providing a ready-made spreadsheet with links to the relevant data points or even a simple web page that included that year's numbers and users would just input the number of years of desired withdrawals and portfolio size would be very helpful. That goes beyond my skill level, but it's well within the wheelhouse of many here. Perhaps someone will pick up that mantle.

I your other thread about TVM vs. VPW, Siamond provided a link to his article which links to a spread sheet, and provides detailed explanation of a very thorough TVM based approach to withdrawal estimation.

Here is a link to his article again:

https://finpage.blog/2019/02/01/early-r ... -of-money/
Siamond's done great work in this area. But I was referring to a web site that already incorporated 1/CAPE for stock returns, some measure like 10 year TIPS yield, etc. so that users could just input their current portfolio size and number of desired years of amortization to get their current withdrawal. Something very, very simple.
Ah. I get it. Sorry, I misunderstood.
Yes. That would be very helpful.

I am still trying to fully digest this approach based on Siamond's articles and a few others i have read.
If i decide to adopt some it, making such a simple spreadsheet for my wife, in case of my demise, would be a useful task.
Once in a while you get shown the light, in the strangest of places if you look at it right.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Thu Feb 28, 2019 11:25 pmThe idea of modifying that based on bond yields and CAPE is kind of interesting, I am just not sure how much faith i have in CAPE as a proxy for forward returns. Not much actually, but it might be the best we have.
It might or might not be the best we have. The correlation between subsequent 10 year returns and starting CAPE from 2000 (i.e. 2000-2009) to 2009 (i.e. 2009-2018) was .534, which is usually viewed as being in the 'moderate' range.

Here's a graph of this.

It looks like 1/CAPE did a reasonably good job, although it did underestimate market returns 8 out of the 10 years, which has been the story for a long time with CAPE.

But....there is another.

It's gotten some flack around here as being nothing more than a datamined artifact (even though Shiller just datamined to get CAPE, but whatever), but it's looking like it might turn out to be an even better predictor than CAPE: the average investor allocation to stocks. It was first publicized by the Philosophical Economist back in Dec., 2013, and since then, it's continued to predict stock returns remarkably well.

https://financial-charts.effingapp.com/

I didn't include it in the OP because it would raise too many eyebrows, but I might go back at some point and do the analysis again using this metric instead of 1/CAPE as a projection of stock returns to evaluate the difference in the withdrawals.

*And yes, this metric is predicting 3% real returns for U.S. stocks for the next decade. Ouch.
Last edited by willthrill81 on Fri Mar 01, 2019 8:47 pm, edited 1 time in total.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
AlohaJoe
Posts: 5572
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Fri Mar 01, 2019 12:21 am But I was referring to a web site that already incorporated 1/CAPE for stock returns, some measure like 10 year TIPS yield, etc. so that users could just input their current portfolio size and number of desired years of amortization to get their current withdrawal. Something very, very simple.
https://docs.google.com/spreadsheets/d/ ... sp=sharing

As with all such things there are caveats, especially for a "long-running spreadsheet" (we expect to use this thing for, what?, 15+ years, right?)
• We need a data source. TIPS yield is easy because the Treasury provides that. So we have an "oracle" for that.
• CAPE10 is harder. Shiller provides it but only in an Excel spreadsheet. Multpl provides it, so we use that.
• Any of these things (especially Multpl) might stop providing it. Maybe they start charging money for it. Or the website shuts down. It would be better if we had a better source, like FRED or even Google/Yahoo.
• Any of our sources might change their API. This will break the spreadsheet and leave non-technical users with a problem.
So consider this a proof of concept to surface the challenges with this approach. Concretely: imagine the husband who created the spreadsheet died; the wife is now 82 years old; and suddenly Multpl goes away and the spreadsheet stops working. Or just imagine the husband isn't technically savvy, copied the spreadsheet from Bogleheads 9 years ago and has no idea how it works or what it does and it stops working.

That said, I don't think such arguments invalid the approach. I don't see any issues with a strategy that says "Do X while I'm alive but Do Y if I die."
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

AlohaJoe wrote: Fri Mar 01, 2019 2:21 am
willthrill81 wrote: Fri Mar 01, 2019 12:21 am But I was referring to a web site that already incorporated 1/CAPE for stock returns, some measure like 10 year TIPS yield, etc. so that users could just input their current portfolio size and number of desired years of amortization to get their current withdrawal. Something very, very simple.
https://docs.google.com/spreadsheets/d/ ... sp=sharing

As with all such things there are caveats, especially for a "long-running spreadsheet" (we expect to use this thing for, what?, 15+ years, right?)
• We need a data source. TIPS yield is easy because the Treasury provides that. So we have an "oracle" for that.
• CAPE10 is harder. Shiller provides it but only in an Excel spreadsheet. Multpl provides it, so we use that.
• Any of these things (especially Multpl) might stop providing it. Maybe they start charging money for it. Or the website shuts down. It would be better if we had a better source, like FRED or even Google/Yahoo.
• Any of our sources might change their API. This will break the spreadsheet and leave non-technical users with a problem.
So consider this a proof of concept to surface the challenges with this approach. Concretely: imagine the husband who created the spreadsheet died; the wife is now 82 years old; and suddenly Multpl goes away and the spreadsheet stops working. Or just imagine the husband isn't technically savvy, copied the spreadsheet from Bogleheads 9 years ago and has no idea how it works or what it does and it stops working.

That said, I don't think such arguments invalid the approach. I don't see any issues with a strategy that says "Do X while I'm alive but Do Y if I die."
Thanks for this.

Just tried playing around with it a bit.
Unless i am misinterpreting something, there might be problem in the formulation.
If i leave everything else the same, and just increase the final portfolio value, i would have expected the withdrawal amount to go down, but it actually went up.
Once in a while you get shown the light, in the strangest of places if you look at it right.
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

AlohaJoe wrote: Fri Mar 01, 2019 2:21 am
willthrill81 wrote: Fri Mar 01, 2019 12:21 am But I was referring to a web site that already incorporated 1/CAPE for stock returns, some measure like 10 year TIPS yield, etc. so that users could just input their current portfolio size and number of desired years of amortization to get their current withdrawal. Something very, very simple.
https://docs.google.com/spreadsheets/d/ ... sp=sharing

As with all such things there are caveats, especially for a "long-running spreadsheet" (we expect to use this thing for, what?, 15+ years, right?)
• We need a data source. TIPS yield is easy because the Treasury provides that. So we have an "oracle" for that.
• CAPE10 is harder. Shiller provides it but only in an Excel spreadsheet. Multpl provides it, so we use that.
• Any of these things (especially Multpl) might stop providing it. Maybe they start charging money for it. Or the website shuts down. It would be better if we had a better source, like FRED or even Google/Yahoo.
• Any of our sources might change their API. This will break the spreadsheet and leave non-technical users with a problem.
So consider this a proof of concept to surface the challenges with this approach. Concretely: imagine the husband who created the spreadsheet died; the wife is now 82 years old; and suddenly Multpl goes away and the spreadsheet stops working. Or just imagine the husband isn't technically savvy, copied the spreadsheet from Bogleheads 9 years ago and has no idea how it works or what it does and it stops working.

That said, I don't think such arguments invalid the approach. I don't see any issues with a strategy that says "Do X while I'm alive but Do Y if I die."
It would actually be far easier to use the average investor allocation to stocks that I noted above because those data are easily available from FRED. But CAPE10 could theoretically be used by having the spreadsheet calculate it from available sources, which might be problematic though, I agree.

My instructions for my wife in the event of my passing are based around your last sentence: basically, a fixed percentage-of-portfolio approach with a three fund portfolio.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
RoxieII
Posts: 62
Joined: Thu Dec 18, 2014 9:40 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Thu Feb 28, 2019 9:52 pm
I do like the idea. I have been playing around a lot with the spread sheet that Siamond provided in another thread that allows one to combine this idea with other income streams coming on line at various times.
Can someone post the link to this spread sheet?

Thanks,
Rox
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

RoxieII wrote: Fri Mar 01, 2019 10:51 am
marcopolo wrote: Thu Feb 28, 2019 9:52 pm
I do like the idea. I have been playing around a lot with the spread sheet that Siamond provided in another thread that allows one to combine this idea with other income streams coming on line at various times.
Can someone post the link to this spread sheet?

Thanks,
Rox
Here is a link to the article that describes what goes into the spreadsheet. About 7 paragraphs into the article, there is a link to the Google Docs sheet that has actual spreadsheet.

https://finpage.blog/2019/02/01/early-r ... -of-money/
Once in a while you get shown the light, in the strangest of places if you look at it right.
RoxieII
Posts: 62
Joined: Thu Dec 18, 2014 9:40 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

Much appreciated!
AlohaJoe
Posts: 5572
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

marcopolo wrote: Fri Mar 01, 2019 10:23 am Unless i am misinterpreting something, there might be problem in the formulation.
You're right, I made a typo. I've fixed it. Thanks for point it out!
TravelforFun
Posts: 2211
Joined: Tue Dec 04, 2012 11:05 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

I think I'd just stick to my 3% SWR and let the heirs have what's left.

TravelforFun
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

TravelforFun wrote: Fri Mar 01, 2019 8:45 pm I think I'd just stick to my 3% SWR and let the heirs have what's left.

TravelforFun
Depending on how long you live, that's historically likely to be multiples of what you start with. But whatever floats your boat!
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
TravelforFun
Posts: 2211
Joined: Tue Dec 04, 2012 11:05 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Fri Mar 01, 2019 8:48 pm
TravelforFun wrote: Fri Mar 01, 2019 8:45 pm I think I'd just stick to my 3% SWR and let the heirs have what's left.

TravelforFun
Depending on how long you live, that's historically likely to be multiples of what you start with.
Yes. This is my goal.

TravelforFun
dknightd
Posts: 2368
Joined: Wed Mar 07, 2018 11:57 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Fri Mar 01, 2019 10:40 am
My instructions for my wife in the event of my passing are based around your last sentence: basically, a fixed percentage-of-portfolio approach with a three fund portfolio.
As you probably know, I plan to annuitize enough so that Wife, or I, will be OK. Forever.
My instructions to wife, or me, is your base living expenses are covered through annuities and SS.
The rest you can do whatever you want with. Take a trip. Give it to the kids. Give it to your charity of choice.
I suspect we will die with money leftover just because we both like having "money in the bank".
If you value a bird in the hand, pay off the loan. If you are willing to risk getting two birds (or none) from the market, invest the funds.
#Cruncher
Posts: 3076
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 5:47 pmWe'll assume that we have an opposite sex couple aged 65 ... According to the SSA, there is only a 9% probability that either will be alive in 32 years (age 97), and they are comfortable with planning on their portfolio not being depleted before that time. …

... let's examine their withdrawals and portfolio balance each year going forward. To simplify the analysis, we'll assume that both spouses survive throughout this period but still wish to plan on exhausting their portfolio within the original 32 year period. (underline added)
If it's good to base the time period on life expectancy (to a certain probability) at age 65, it would also be good to do so in each subsequent year. To keep the original period makes no sense. If there's a 9% chance at least one of the couple will live 32 years (to age 97) when they're 65, there certainly is not a 9% chance at least one will live 32 years (to age 107) when they're 75.

Actually the expected age for at least one of the couple to live increases very little with each passing year. This is shown in the following table for a same age man/woman couple with mortality corresponding to the 2015 SSA Period Life Table. It shows the approximate age that at least one will live with a 9% probability. For example at age 65 there is a 9% chance at least one will live to age 97.8. But if they both survive ten years to age 75, the age corresponding to a 9% probability either one will be alive has increased only 0.5 years to age 98.3. If they both survive another ten years to age 85, the corresponding expectation only increases another 1.4 years to 99.7.

Code: Select all

``````Pers 1  Either   Pers 1  Either   Pers 1  Either
Age  Lives To    Age  Lives To    Age  Lives To
---------------  ---------------  ---------------
65    97.8       75    98.3       85    99.7
66    97.8       76    98.4       86    99.9
67    97.9       77    98.5       87   100.2
68    97.9       78    98.6       88   100.5
69    97.9       79    98.7       89   100.8
70    98.0       80    98.9       90   101.1
71    98.0       81    99.0       91   101.5
72    98.1       82    99.2       92   101.9
73    98.2       83    99.3       93   102.4
74    98.3       84    99.5       94   102.9``````
To do this calculation for other ages or sexes, follow these steps to create the large spreadsheet I got the above figures from:
• Select All, Copy, and Paste [ * ] the following at cell A1 of a blank Excel sheet.

Code: Select all

``````Person 1: Male / Female	M	=IF(UPPER(LEFT(TRIM(B1)))="M",0,1)
Person 2: Male / Female	F	=IF(UPPER(LEFT(TRIM(B2)))="M",0,1)
Probability either 1 or 2 alive	0.09
="Age person 2 when person 1 is "&A9	50
Age person 2 vs age person 1	=ROUND(B4-A9,0)
Person 1 first calculation age	=A9+MAX(0,-B5)
Percent Die		Number Alive
Person 1 Age	Man	Woman	Person 1	Person 2	Index	Age	1	=H8+1
50	0.005013	0.003193	=IF(A9<B\$6,0,IF(A9=B\$6,100000,D8*(1-OFFSET(B8,0,C\$1))))	=IF(A9<B\$6,0,IF(A9=B\$6,100000,E8*IF(A9+B\$5-1>A\$78,0,(1-OFFSET(B8,B\$5,C\$2)))))	=IF(AND(BE9<\$B\$3,\$B\$3<H9),MATCH(\$B\$3,H9:BE9,-1),0)	=IF(F9>0,(INDEX(H9:BE9,1,F9)-\$B\$3)/(INDEX(H9:BE9,1,F9)-INDEX(H9:BE9,1,F9+1))+INDEX(H\$8:BE\$8,1,F9)+A9,0)	=IF(\$A9<\$B\$6,0,1-(1-OFFSET(\$D9,H\$8,0)/\$D9)*(1-IF(\$E9=0,0,OFFSET(\$E9,H\$8,0)/\$E9)))	=IF(\$A9<\$B\$6,0,1-(1-OFFSET(\$D9,I\$8,0)/\$D9)*(1-IF(\$E9=0,0,OFFSET(\$E9,I\$8,0)/\$E9)))
51	0.005524	0.003491
52	0.006059	0.003801
53	0.006611	0.004119
54	0.007187	0.004449
55	0.0078	0.004813
56	0.008456	0.005201
57	0.009144	0.005583
58	0.009865	0.005952
59	0.010622	0.006325
60	0.011458	0.006749
61	0.01235	0.007238
62	0.013235	0.007776
63	0.014097	0.008368
64	0.014979	0.009032
65	0.015967	0.009794
66	0.017109	0.010673
67	0.018392	0.011676
68	0.019836	0.012815
69	0.021465	0.014105
70	0.023351	0.015616
71	0.025482	0.017318
72	0.027794	0.019118
73	0.030282	0.020996
74	0.033022	0.023033
75	0.036201	0.025413
76	0.039858	0.028197
77	0.043891	0.031313
78	0.048311	0.034782
79	0.053228	0.038689
80	0.058897	0.043258
81	0.065365	0.04849
82	0.072491	0.054223
83	0.080288	0.060446
84	0.088916	0.067338
85	0.098576	0.075133
86	0.109438	0.084033
87	0.121619	0.094177
88	0.135176	0.105633
89	0.150109	0.118407
90	0.166397	0.132476
91	0.183997	0.147801
92	0.202855	0.164331
93	0.222911	0.182012
94	0.244094	0.200783
95	0.265091	0.219758
96	0.285508	0.23863
97	0.304926	0.257065
98	0.322919	0.274706
99	0.339065	0.291189
100	0.356018	0.30866
101	0.373819	0.32718
102	0.39251	0.34681
103	0.412135	0.367619
104	0.432742	0.389676
105	0.454379	0.413057
106	0.477098	0.43784
107	0.500953	0.464111
108	0.526	0.491957
109	0.5523	0.521475
110	0.579915	0.552763
111	0.608911	0.585929
112	0.639357	0.621085
113	0.671325	0.65835
114	0.704891	0.697851
115	0.740135	0.739722
116	0.777142	0.777142
117	0.815999	0.815999
118	0.856799	0.856799
119	0.899639	0.899639``````
• Format for readability.
• Copy cells I8:I9 right to column BE.
• Copy cells D9:BE9 down to row 78.
• Modify the assumptions in cells B1:B4 as needed.
* If you have trouble pasting, try "Paste Special" and "Text".
Topic Author
willthrill81
Posts: 21324
Joined: Thu Jan 26, 2017 3:17 pm
Location: USA

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

#Cruncher wrote: Sun Mar 03, 2019 6:11 pm
willthrill81 wrote: Wed Feb 27, 2019 5:47 pmWe'll assume that we have an opposite sex couple aged 65 ... According to the SSA, there is only a 9% probability that either will be alive in 32 years (age 97), and they are comfortable with planning on their portfolio not being depleted before that time. …

... let's examine their withdrawals and portfolio balance each year going forward. To simplify the analysis, we'll assume that both spouses survive throughout this period but still wish to plan on exhausting their portfolio within the original 32 year period. (underline added)
If it's good to base the time period on life expectancy (to a certain probability) at age 65, it would also be good to do so in each subsequent year. To keep the original period makes no sense. If there's a 9% chance at least one of the couple will live 32 years (to age 97) when they're 65, there certainly is not a 9% chance at least one will live 32 years (to age 107) when they're 75.

Actually the expected age for at least one of the couple to live increases very little with each passing year. This is shown in the following table for a same age man/woman couple with mortality corresponding to the 2015 SSA Period Life Table. It shows the approximate age that at least one will live with a 9% probability. For example at age 65 there is a 9% chance at least one will live to age 97.8. But if they both survive ten years to age 75, the age corresponding to a 9% probability either one will be alive has increased only 0.5 years to age 98.3. If they both survive another ten years to age 85, the corresponding expectation only increases another 1.4 years to 99.7.
Yes, I was aware of that but don't have your spreadsheet skills to actually do that analysis.

I had not noticed that both surviving to age 85 does not substantially increase the probability of them outliving the original 97. It illustrates how truly rare it is for anyone, at least in the U.S., to reach that age.
#Cruncher wrote: Sun Mar 03, 2019 6:11 pm

Code: Select all

``````Pers 1  Either   Pers 1  Either   Pers 1  Either
Age  Lives To    Age  Lives To    Age  Lives To
---------------  ---------------  ---------------
65    97.8       75    98.3       85    99.7
66    97.8       76    98.4       86    99.9
67    97.9       77    98.5       87   100.2
68    97.9       78    98.6       88   100.5
69    97.9       79    98.7       89   100.8
70    98.0       80    98.9       90   101.1
71    98.0       81    99.0       91   101.5
72    98.1       82    99.2       92   101.9
73    98.2       83    99.3       93   102.4
74    98.3       84    99.5       94   102.9``````
To do this calculation for other ages or sexes, follow these steps to create the large spreadsheet I got the above figures from:
• Select All, Copy, and Paste [ * ] the following at cell A1 of a blank Excel sheet.

Code: Select all

``````Person 1: Male / Female	M	=IF(UPPER(LEFT(TRIM(B1)))="M",0,1)
Person 2: Male / Female	F	=IF(UPPER(LEFT(TRIM(B2)))="M",0,1)
Probability either 1 or 2 alive	0.09
="Age person 2 when person 1 is "&A9	50
Age person 2 vs age person 1	=ROUND(B4-A9,0)
Person 1 first calculation age	=A9+MAX(0,-B5)
Percent Die		Number Alive
Person 1 Age	Man	Woman	Person 1	Person 2	Index	Age	1	=H8+1
50	0.005013	0.003193	=IF(A9<B\$6,0,IF(A9=B\$6,100000,D8*(1-OFFSET(B8,0,C\$1))))	=IF(A9<B\$6,0,IF(A9=B\$6,100000,E8*IF(A9+B\$5-1>A\$78,0,(1-OFFSET(B8,B\$5,C\$2)))))	=IF(AND(BE9<\$B\$3,\$B\$3<H9),MATCH(\$B\$3,H9:BE9,-1),0)	=IF(F9>0,(INDEX(H9:BE9,1,F9)-\$B\$3)/(INDEX(H9:BE9,1,F9)-INDEX(H9:BE9,1,F9+1))+INDEX(H\$8:BE\$8,1,F9)+A9,0)	=IF(\$A9<\$B\$6,0,1-(1-OFFSET(\$D9,H\$8,0)/\$D9)*(1-IF(\$E9=0,0,OFFSET(\$E9,H\$8,0)/\$E9)))	=IF(\$A9<\$B\$6,0,1-(1-OFFSET(\$D9,I\$8,0)/\$D9)*(1-IF(\$E9=0,0,OFFSET(\$E9,I\$8,0)/\$E9)))
51	0.005524	0.003491
52	0.006059	0.003801
53	0.006611	0.004119
54	0.007187	0.004449
55	0.0078	0.004813
56	0.008456	0.005201
57	0.009144	0.005583
58	0.009865	0.005952
59	0.010622	0.006325
60	0.011458	0.006749
61	0.01235	0.007238
62	0.013235	0.007776
63	0.014097	0.008368
64	0.014979	0.009032
65	0.015967	0.009794
66	0.017109	0.010673
67	0.018392	0.011676
68	0.019836	0.012815
69	0.021465	0.014105
70	0.023351	0.015616
71	0.025482	0.017318
72	0.027794	0.019118
73	0.030282	0.020996
74	0.033022	0.023033
75	0.036201	0.025413
76	0.039858	0.028197
77	0.043891	0.031313
78	0.048311	0.034782
79	0.053228	0.038689
80	0.058897	0.043258
81	0.065365	0.04849
82	0.072491	0.054223
83	0.080288	0.060446
84	0.088916	0.067338
85	0.098576	0.075133
86	0.109438	0.084033
87	0.121619	0.094177
88	0.135176	0.105633
89	0.150109	0.118407
90	0.166397	0.132476
91	0.183997	0.147801
92	0.202855	0.164331
93	0.222911	0.182012
94	0.244094	0.200783
95	0.265091	0.219758
96	0.285508	0.23863
97	0.304926	0.257065
98	0.322919	0.274706
99	0.339065	0.291189
100	0.356018	0.30866
101	0.373819	0.32718
102	0.39251	0.34681
103	0.412135	0.367619
104	0.432742	0.389676
105	0.454379	0.413057
106	0.477098	0.43784
107	0.500953	0.464111
108	0.526	0.491957
109	0.5523	0.521475
110	0.579915	0.552763
111	0.608911	0.585929
112	0.639357	0.621085
113	0.671325	0.65835
114	0.704891	0.697851
115	0.740135	0.739722
116	0.777142	0.777142
117	0.815999	0.815999
118	0.856799	0.856799
119	0.899639	0.899639``````
• Format for readability.
• Copy cells I8:I9 right to column BE.
• Copy cells D9:BE9 down to row 78.
• Modify the assumptions in cells B1:B4 as needed.
* If you have trouble pasting, try "Paste Special" and "Text".
Thanks! Your analysis, as always, is second to none.
“It's a dangerous business, Frodo, going out your door. You step onto the road, and if you don't keep your feet, there's no knowing where you might be swept off to.” J.R.R. Tolkien,The Lord of the Rings
JustinR
Posts: 1382
Joined: Tue Apr 27, 2010 11:43 pm

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

What about an approach where you use this or the x% rule, whichever is lesser?

Wouldn't that be the safest thing to do? Or is the goal of this approach not to survive, but something else?

The thing I like about x% is that the goal is to survive infinitely. I don't really like the approach of guessing when you're going to die and then trying to hit \$0 by that age.
dcabler
Posts: 1566
Joined: Wed Feb 19, 2014 11:30 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

JustinR wrote: Sun Mar 24, 2019 4:32 am What about an approach where you use this or the x% rule, whichever is lesser?

Wouldn't that be the safest thing to do? Or is the goal of this approach not to survive, but something else?

The thing I like about x% is that the goal is to survive infinitely. I don't really like the approach of guessing when you're going to die and then trying to hit \$0 by that age.
You don't have to. Some people add buffer years. Or they use mortality tables and update the number of years remaining every year.
grayfox
Posts: 5569
Joined: Sat Sep 15, 2007 4:30 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.

...

For those wanting to implement this type of withdrawal method but are afraid that it would be too complicated for their spouse to manage, a simple Excel spreadsheet could be created where all that would be necessary is for the spouse to enter the current portfolio balance, the number of years of desired withdrawals remaining, the current CAPE value (easily Googled), and the current 10 year TIPS yield (also easily Googled), and the TMV formula would indicate what the current withdrawal should be.
This is excellent! Very simple indeed. Also very transparent.

One thing, LibreOffice does not have TMV() function. It is PMT() function.
marcopolo wrote: Thu Feb 28, 2019 12:51 pm If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
The big difference is that this TVM method is based on current conditions, whereas the SWR is based on backtesting over periods that were much different than today.
marcopolo
Posts: 3607
Joined: Sat Dec 03, 2016 10:22 am

### Re: Using the Time Value of Money Formula to Determine Withdrawals: Year 2000 Retiree Example

grayfox wrote: Sun Mar 24, 2019 7:28 am
willthrill81 wrote: Wed Feb 27, 2019 5:47 pm When I've brought up the concept of using the TVM formula to calculate annual withdrawals in other threads, many have thought that it was too complex. As such, I though that it would be useful to demonstrate how relatively simple it can be.

...

For those wanting to implement this type of withdrawal method but are afraid that it would be too complicated for their spouse to manage, a simple Excel spreadsheet could be created where all that would be necessary is for the spouse to enter the current portfolio balance, the number of years of desired withdrawals remaining, the current CAPE value (easily Googled), and the current 10 year TIPS yield (also easily Googled), and the TMV formula would indicate what the current withdrawal should be.
This is excellent! Very simple indeed. Also very transparent.

One thing, LibreOffice does not have TMV() function. It is PMT() function.
marcopolo wrote: Thu Feb 28, 2019 12:51 pm If the drop and expected forwards return perfectly canceled each other out, which you seem to imply would be a desirable outcome, wouldn't you just come full circle back to a constant SWR, which you say no one would ever use?
The big difference is that this TVM method is based on current conditions, whereas the SWR is based on backtesting over periods that were much different than today.
TVM still has to use an assumed growth/discount rate, what do you base that on other than historical knowledge.

If you think future will be worse, you can simply lower your SWR.

There are a lot of reason to like TVM, I am seriously considering adopting it. But, this does not seem like a reason to differentiate it from other approaches.
Once in a while you get shown the light, in the strangest of places if you look at it right.