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

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
AlohaJoe
Posts: 4935
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

dcabler wrote:
Sun Mar 24, 2019 5:52 am
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.
You also don't have to try to hit \$0. It is even possible to use time value of money for the portfolio to survive indefinitely, if for some reason that's your goal.

So it seems as if nothing JustinR dislikes about the approach is actually part of the strategy

If you don't want to try to hit \$0 then pick some other number in the formula. Any positive number will "survive indefinitely".

Here are three different examples. In each case you start with \$1,000,000 and assuming a planning period of 30 years and a rate of 3.5% (your current best guess about expected returns on your portfolio).

Code: Select all

``````You want to target a final portfolio value of \$0.
=pmt(3.5%, 30, -1000000, 0, 1)
=\$52,532.69

You don't like the idea of targeting \$0, you want to leave \$250,000 for your heirs
=pmt(3.5%, 30, -1000000, 250000, 1)
=\$47,853.62

You want to (try to) ensure that you portfolio stays the same. So you die with the same \$1,000,000 you started with
=pmt(3.5%, 30, -1000000, 1000000, 1)
=\$33,816.43
``````
And then, as this thread and others have said, each year feel free to update any or all of the parameters every year as circumstances change.

Decide that you don't want your good-for-nothing children to get anything after all? Change the "future value" parameter. Think that we're headed for a secular stagnation? Change the "rate" parameter. Divorced and remarried someone 20 years younger? Change the "number of periods" parameter. Parents died and you inherited their 401k? Change the portfolio parameter.

GAAP
Posts: 955
Joined: Fri Apr 08, 2016 12:41 pm

### 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.
x% lasting forever is only true if you pick a small enough x.

I use a variable TVM method that effectively treats retirement as a perpetuity. As long as my estimated portfolio returns "reasonably match" reality over longer periods, the variability in the method will handle the remaining differences. However, this requires an estimate of future returns instead of longevity -- I don't think you will find a solution that is both open-ended and only based on currently known information.
“Adapt what is useful, reject what is useless, and add what is specifically your own.” ― Bruce Lee

Topic Author
willthrill81
Posts: 13960
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:
Sun Mar 24, 2019 9:18 am
Decide that you don't want your good-for-nothing children to get anything after all? Change the "future value" parameter. Think that we're headed for a secular stagnation? Change the "rate" parameter. Divorced and remarried someone 20 years younger? Change the "number of periods" parameter. Parents died and you inherited their 401k? Change the portfolio parameter.
Exactly. The TMV approach is extremely flexible and can be as simple or complex as the user wants it to be. You can change any parameter at any time, take into account multiple cash flows and/or lump sums occurring at different points in time (Siamond's spreadsheet does an excellent job of demonstrating how you can do this fairly easily), 'front-load' your withdrawals to spend more when you're alive and likely healthier, etc.
Last edited by willthrill81 on Sun Mar 24, 2019 11:50 am, 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: 13960
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

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.
I'm glad that you found my post valuable.

You actually don't need any kind of paid software to do the calculations. I did the calculations for the OP using a free TMV app on my Android phone.
grayfox wrote:
Sun Mar 24, 2019 7:28 am
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.
True. And since the TVM method takes current conditions, particularly your current portfolio value, into account, it (1) is impossible to prematurely deplete your portfolio before your specified period of time and (2) if your portfolio grows over time, your withdrawals are likely to grow as well, though this will not necessarily occur because your forward projected returns may offset the portfolio increase. This latter event happened in the OP from 2011 to 2012 because projected bond returns fell substantially, which more than offset the small portfolio increase.
“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: 367
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

I personally like this approach. It would really be neat if this could be back tested, particularly during the 1932 an 1966 retirement periods.
"Simplicity is the ultimate sophistication" - Leonardo Da Vinci

Topic Author
willthrill81
Posts: 13960
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

sixtyforty wrote:
Sun Mar 24, 2019 7:18 pm
I personally like this approach. It would really be neat if this could be back tested, particularly during the 1932 an 1966 retirement periods.
You could with relative ease. Bond yields are easily obtained from those periods, and Shiller's website has CAPE values for those years as well.
“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

Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 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,
...
and the TMV formula would indicate what the current withdrawal should be.

This?
https://finpage.blog/2019/02/01/early-r ... -of-money/
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

siamond
Posts: 5030
Joined: Mon May 28, 2012 5:50 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
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."
Yes, indeed. I have been using a TVM-like approach for several years in early retirement by now, including CAPE math and so on. I don't expect my wife (if she survives me or I become incapacitated) to do the same.

So I wrote a simplified financial policy statement where, among other topics, I suggested to use a simple % of the current portfolio withdrawal approach, in addition to fixed income. This is a Google Sheet, a link is automatically e-mailed to my wife and one of my (fairly numbers-oriented) sons every year. I also used this document as a sneaky way to plant a few personal finance ideas in my son's head (while telling him he needed to listen to be ready to help his mom in case of), worked like a charm!

Topic Author
willthrill81
Posts: 13960
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

Bongleur wrote:
Sun Jul 28, 2019 2:54 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,
...
and the TMV formula would indicate what the current withdrawal should be.

This?
https://finpage.blog/2019/02/01/early-r ... -of-money/
There isn't an official TVM formula because it's the basic PMT formula widely used in all time value of money applications. There are many phone apps and dedicated calculators (I cut my teeth on the BAII-Plus) that use it.
“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

Rob1
Posts: 163
Joined: Thu May 04, 2017 2:57 pm

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

siamond wrote:
Sun Jul 28, 2019 8:38 am
AlohaJoe wrote:
Fri Mar 01, 2019 2:21 am
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."
Yes, indeed. I have been using a TVM-like approach for several years in early retirement by now, including CAPE math and so on. I don't expect my wife (if she survives me or I become incapacitated) to do the same.

So I wrote a simplified financial policy statement where, among other topics, I suggested to use a simple % of the current portfolio withdrawal approach, in addition to fixed income. This is a Google Sheet, a link is automatically e-mailed to my wife and one of my (fairly numbers-oriented) sons every year. I also used this document as a sneaky way to plant a few personal finance ideas in my son's head (while telling him he needed to listen to be ready to help his mom in case of), worked like a charm!
Agree - X now, Y if I die. My wife and I agree that she will engage Vanguard’s PAS if I pass before her.

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

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

sixtyforty wrote:
Sun Mar 24, 2019 7:18 pm
I personally like this approach. It would really be neat if this could be back tested, particularly during the 1932 an 1966 retirement periods.
Check this post on the other TVM thread. Not exactly what you requested, but should still be informative. Please believe me, I backtested the matter to death while I was trying to decide what to do with my own early retirement...

Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 am

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

willthrill81 wrote:
Sun Jul 28, 2019 10:13 am
Bongleur wrote:
Sun Jul 28, 2019 2:54 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,
...
and the TMV formula would indicate what the current withdrawal should be.

This?
https://finpage.blog/2019/02/01/early-r ... -of-money/
There isn't an official TVM formula because it's the basic PMT formula widely used in all time value of money applications. There are many phone apps and dedicated calculators (I cut my teeth on the BAII-Plus) that use it.
that's kinda what I thought. Redundant acronyms should be deprecated and not used...
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

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

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

Bongleur wrote:
Sun Jul 28, 2019 8:10 pm
willthrill81 wrote:
Sun Jul 28, 2019 10:13 am
There isn't an official TVM formula because it's the basic PMT formula widely used in all time value of money applications. There are many phone apps and dedicated calculators (I cut my teeth on the BAII-Plus) that use it.
that's kinda what I thought. Redundant acronyms should be deprecated and not used...
TVM is the overarching concept while PMT is one of the typical underlying formulas. See Investopedia TVM as an example.

But yeah, I sympathize, way too many acronyms in our (financial) life...

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

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

After having a few in-depth discussions with local Bogleheads who are close to retirement and running a few models with them, I improved a bit the Time Value of Money spreadsheet that I developed a little while ago. Besides some cosmetic and clarification stuff, here are the two main changes:

1. I made it easier to update the spreadsheet year after year, allowing to simply delete the first row without the formulas getting messed up, allowing to easily modify the twilight years assumptions by adding or deleting rows.

2. I added a level of indirection in the cells providing the inputs to the rate of return computation. Point being to ease the use of a more dynamic expected returns model (e.g. a mix of 1/CAPE, bond yield, historical returns) as opposed to the static math solely based on historical returns. Such dynamic approach can go a LONG way in minimizing the sequence of income risk (i.e. variability of one's spending budget) year after year, and in being more adaptive to an unknown future.

The online spreadsheet can be accessed here (this is a read-only link, please make a copy for yourself and then you can customize at will): http://bit.ly/2WuNmvf

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

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

siamond wrote:
Thu Nov 14, 2019 1:15 pm
After having a few in-depth discussions with local Bogleheads who are close to retirement and running a few models with them, I improved a bit the Time Value of Money spreadsheet that I developed a little while ago. Besides some cosmetic and clarification stuff, here are the two main changes:

1. I made it easier to update the spreadsheet year after year, allowing to simply delete the first row without the formulas getting messed up, allowing to easily modify the twilight years assumptions by adding or deleting rows.

2. I added a level of indirection in the cells providing the inputs to the rate of return computation. Point being to ease the use of a more dynamic expected returns model (e.g. a mix of 1/CAPE, bond yield, historical returns) as opposed to the static math solely based on historical returns. Such dynamic approach can go a LONG way in minimizing the sequence of income risk (i.e. variability of one's spending budget) year after year, and in being more adaptive to an unknown future.

The online spreadsheet can be accessed here (this is a read-only link, please make a copy for yourself and then you can customize at will): http://bit.ly/2WuNmvf
Thank you sir for the continued work on this.

I downloaded the previous version and found it very helpful. I have had item 1 above on my list of things to do for my local copy of the spreadsheet.

Procrastination has paid once again
Once in a while you get shown the light, in the strangest of places if you look at it right.

KarenC
Posts: 118
Joined: Mon Apr 27, 2015 7:25 am

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

I like the changes! I did find the note on cell C9 (in Simple Example) as well as the value in cell D9 a bit misleading in that you might have overridden the historical values. (I ended up adding a true/false value and modifying the formula in C9 to toggle between the override values in A5/A7 and the historical values in A9/A11.)

I'm especially interested in how you present the "REGULAR SPENDING" value (J9). Since I've been modeling with a special expense, I've always wondered if the actual Year 0 withdrawal would be J9 plus whatever special expenses were modeled. The new spreadsheet seems to make that clearer in that it seems (especially with the comment in K10) to distinguish spending from portfolio withdrawals. (I might be confused about that.)

I do have a question about the soft spending gate. If it is activated (as it is in my current modeling), and you have special expenses, does that mean the suggested withdrawal would be the the soft gate value (N10) plus whatever special expenses were modeled?
"How much you know is less important than how clearly you understand where the borders of your ignorance begin." — Jason Zweig

KarenC
Posts: 118
Joined: Mon Apr 27, 2015 7:25 am

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

Also, the summation of the cash flows (column I in Simple Example) includes columns B and H, which seems wrong.
"How much you know is less important than how clearly you understand where the borders of your ignorance begin." — Jason Zweig

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

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

KarenC wrote:
Fri Nov 15, 2019 7:33 am
I like the changes! I did find the note on cell C9 (in Simple Example) as well as the value in cell D9 a bit misleading in that you might have overridden the historical values. (I ended up adding a true/false value and modifying the formula in C9 to toggle between the override values in A5/A7 and the historical values in A9/A11.)
Ah, thank you! I forgot to update the commentary coming with the cells computing the portfolio rate of return, silly me. The intent is for cells A5/A7 (and the derived math in C9) to provide the expected rate of return, whether it is computed from historical returns or by other means. I took a stab at improving the comments. And I changed the coloring of cells A5/A7 to better show that they are intended to be customized. Please let me know if this is clearer.
KarenC wrote:
Fri Nov 15, 2019 7:33 am
I'm especially interested in how you present the "REGULAR SPENDING" value (J9). Since I've been modeling with a special expense, I've always wondered if the actual Year 0 withdrawal would be J9 plus whatever special expenses were modeled. The new spreadsheet seems to make that clearer in that it seems (especially with the comment in K10) to distinguish spending from portfolio withdrawals. (I might be confused about that.)

I do have a question about the soft spending gate. If it is activated (as it is in my current modeling), and you have special expenses, does that mean the suggested withdrawal would be the the soft gate value (N10) plus whatever special expenses were modeled?
The 'regular spending' and the 'spending gate' are intended to be directly comparable. Neither include the 'special expenses'. To clarify, I just tweaked a bit the spreadsheet to show in bright blue both the regular spending budget and the full budget (incl. special expenses). I also aligned the spending gate with the regular budget (now on the same row).

As to portfolio withdrawals vs. spending budget, yes, they are different. As explained in the README section, "the spending money will come in priority from income cash flows, then by portfolio withdrawals (from any type of retirement/brokerage account). Extra income (if any) is assumed to be invested in the portfolio."

Overall, many thanks for your interest and feedback. I had originally intended this spreadsheet as a simple starting point that people would change in many ways. I am starting to realize that I should probably do more efforts to package it as a tool. I don't want to go too far in this direction (it does remain intended for 'hands on' users), but feedback would be welcome for incremental improvements.

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

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

KarenC wrote:
Fri Nov 15, 2019 10:32 am
Also, the summation of the cash flows (column I in Simple Example) includes columns B and H, which seems wrong.
This is deliberate. This allows end users who insert new columns to not get the formulas messed up if they happen to insert the new column at the beginning or the end of the set of cash flows columns. Old Excel trick...

The more I use this type of Present Value computation for various situations, the more I am inclined to use many columns, to clearly distinguish what is what. So I wanted to make it very easy to add such new columns.

KarenC
Posts: 118
Joined: Mon Apr 27, 2015 7:25 am

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

siamond wrote:
Fri Nov 15, 2019 11:01 am
KarenC wrote:
Fri Nov 15, 2019 10:32 am
Also, the summation of the cash flows (column I in Simple Example) includes columns B and H, which seems wrong.
This is deliberate. This allows end users who insert new columns to not get the formulas messed up if they happen to insert the new column at the beginning or the end of the set of cash flows columns. Old Excel trick...

The more I use this type of Present Value computation for various situations, the more I am inclined to use many columns, to clearly distinguish what is what. So I wanted to make it very easy to add such new columns.
OK, I just don’t like how the cash flows are a bit off in that they include the year number (0, 1, 2, etc.).
"How much you know is less important than how clearly you understand where the borders of your ignorance begin." — Jason Zweig

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

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

KarenC wrote:
Fri Nov 15, 2019 11:08 am
OK, I just don’t like how the cash flows are a bit off in that they include the year number (0, 1, 2, etc.).
Hmpf. I am an idiot. You are absolutely right. Fixed. Keep the feedback coming!

WoodSpinner
Posts: 915
Joined: Mon Feb 27, 2017 1:15 pm

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

Siamond,

Many thanks for the detailed writeup and the spreadsheet! I have been using it to help assess my spending plans now that I have retired.

Definitely still learning and have a few questions ....

1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?

2. The Expected Rate of Return (cell C9) has a description (D9) as being Inflation Adjusted. From the calculation it looks like this is the Nominal Rate not the Real Rate. It could be my inexperience but I find the description confusing.

3. My planning spreadsheet is all in Nominal Dollars. I have 2 Income Streams that have COLAs (SS + Small-Pension) and a Larger Pension which is not Inflation Adjusted. Wondering what logic changes would be needed to shift the Calculations to use Nominal vs. Inflation Adjusted?
- Adjust SS and Small Pension values to increase with inflation
- Remove the inflation decrease to the Larger Pension
- Leave Expected Rate of Return as is but update description to indicate its Nominal
- Anything else?

Thanks so much

WoodSpinner

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

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

WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?
Discount rates are a mind binder. I get this question all the time. I always struggle to answer in a clear manner. The trick for me is to think forward (starting from now) instead thinking backward (discounting). The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW. If you spend more from your portfolio in the period of time when you don't have social security (yet), then future SS income really should be discounted according to the returns you WON'T get from your portfolio due to this extra expense. In other words, discounting is not about with the level of risk/certainty of a future income flow, it has everything to do with how you compensate today for the future income. I tried to elaborate more on this point in the blog article (part 1 and part 2). And yeah, I know, it is terribly counter-intuitive... Think forward!
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
2. The Expected Rate of Return (cell C9) has a description (D9) as being Inflation Adjusted. From the calculation it looks like this is the Nominal Rate not the Real Rate. It could be my inexperience but I find the description confusing.
Hm, no, all rates are real, starting from the historical rates in cells A9 and A11. Maybe what confused you is that there is no formula explicitly adjusting for (expected) inflation, but this is because it all starts from real rates. I just added more words to the various cells involved to try to be as explicit as possible. Thanks for the feedback, if it confused you, it needed clarification.
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
3. My planning spreadsheet is all in Nominal Dollars. I have 2 Income Streams that have COLAs (SS + Small-Pension) and a Larger Pension which is not Inflation Adjusted. Wondering what logic changes would be needed to shift the Calculations to use Nominal vs. Inflation Adjusted?
- Adjust SS and Small Pension values to increase with inflation
- Remove the inflation decrease to the Larger Pension
- Leave Expected Rate of Return as is but update description to indicate its Nominal
- Anything else?
You're right, PV math can indeed be performed in nominal dollars, although I would strongly advise against it (numbers 10 or 20 years from now would become extremely unintuitive, notably when plotted on a graph). About your points:
- every pension system is different, so you have to think hard about the exact terms of yours, but if there is one small part which is inflation-adjusted and one large part which is not, then your description is correct.
- the expected rate of return HAS to be modified to become nominal, starting from cells A9 and A11. It gets confusing though, should you adjust with historical inflation numbers (which varied a lot over time and geographies) or add an expected (future) inflation rate like the 2% Fed target to (real) return rates? I would strongly advise to do the latter.
- if you use a more dynamic model (e.g. a mix of 1/CAPE and bond yield), then inflation has to be added to 1/CAPE (which is a real quantity) while bond yields (for regular funds, not TIPS) are a nominal quantity and can be used as is.
- if you want to factor in some special expenses way late in the future (e.g. extra spend beyond regular budget due to long-term care costs), then it becomes tricky because the inflation adjustment highly depends on the timeframe until then. Same issue, albeit less acute, with lump sumps coming your way down the road (e.g. bequest, house downsizing).

Yes, it can all be done in nominal dollars, but again, I would strongly advise you to start thinking in real dollars. SSSSOOO much simpler and intuitive, therefore MUCH LESS error-prone.

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

siamond wrote:
Tue Nov 19, 2019 12:13 pm
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?
Discount rates are a mind binder. I get this question all the time. I always struggle to answer in a clear manner. The trick for me is to think forward (starting from now) instead thinking backward (discounting). The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW. If you spend more from your portfolio in the period of time when you don't have social security (yet), then future SS income really should be discounted according to the returns you WON'T get from your portfolio due to this extra expense. In other words, discounting is not about with the level of risk/certainty of a future income flow, it has everything to do with how you compensate today for the future income.
An alternative way of thinking is to try to match the discount rate to the asset/liability at hand: so for TVM calculations regarding Social Security (or any essential expenses/income) we would want to use a very conservative CD or treasury or annuity rates, while for more discretionary expenses/investments we'd want to use a higher expected discount rate. The argument for this is that--contrary to the annuity model the TVM was designed for--future portfolio income is not a given. While it is true that someone might elect to utilize a risky portfolio for monies that are used in the period of time when you don't yet have SS, that would be unwise given the consequence of failure. So in the end discounting is about the risk/certainty of future income flow and also has to do with how you compensate today for future income.

But context is everything with discount rates. Things might be different for the person who has their essential needs covered by a pension and would be using their future SS monies for "fun"-- for that person using the portfolio expected return as the discount rate would make sense assuming they used a conservative discount rate to calculate the pv of their pension.

One consequence of this is that if the portfolio you actually use for funding future essential assets/expenses has a higher expected return than that used in your PMT calculations, you'll likely end up with an ever increasing PMT as time goes on. In other words, you'd be "playing it safe" with lower PMT in the short run to ensure you weren't later caught with your pants down. But you're doing so knowing that the it's more important to be sure your essentials are covered rather than to maximize overall return or have a more stable withdrawal rate. In contrast, using the portfolio discount rate for SS would give you a false sense of confidence.

WoodSpinner
Posts: 915
Joined: Mon Feb 27, 2017 1:15 pm

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

siamond wrote:
Tue Nov 19, 2019 12:13 pm
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?
Discount rates are a mind binder. I get this question all the time. I always struggle to answer in a clear manner. The trick for me is to think forward (starting from now) instead thinking backward (discounting). The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW. If you spend more from your portfolio in the period of time when you don't have social security (yet), then future SS income really should be discounted according to the returns you WON'T get from your portfolio due to this extra expense. In other words, discounting is not about with the level of risk/certainty of a future income flow, it has everything to do with how you compensate today for the future income. I tried to elaborate more on this point in the blog article (part 1 and part 2). And yeah, I know, it is terribly counter-intuitive... Think forward!

Given current circumstances (2% inflation) the 30 year Treasury Bills real rate is .31% which is significantly less than the expected real rate of return for my portfolio (currently 54/46) of 3.5% (using the historical values provided). This will tend to understate the additional spending available due to future income streams. Fortunately I have excess spending capacity (my projected expenses are still under the Regular Spending Budget proposed) even when using the more conservative 30 Year Treasury Rate.

As part of my model, I project the expected Rate of Return for each year of Retirement. It changes based on my upward glide path (54/46 -> 60/40) and on expected returns for US Stocks, Intl Stocks, Intermediate Treasuries, Short Term Treasuries. I don't think this will ever be under the 30 Year Treasury Rate.

Is there a way to use this array of yearly expected returns to better model the annualized payments?

Am I correct that the big risk in using the 30 year Treasury Bills is understating the potential spend?

WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
2. The Expected Rate of Return (cell C9) has a description (D9) as being Inflation Adjusted. From the calculation it looks like this is the Nominal Rate not the Real Rate. It could be my inexperience but I find the description confusing.
Hm, no, all rates are real, starting from the historical rates in cells A9 and A11. Maybe what confused you is that there is no formula explicitly adjusting for (expected) inflation, but this is because it all starts from real rates. I just added more words to the various cells involved to try to be as explicit as possible. Thanks for the feedback, if it confused you, it needed clarification.

Thanks, I understand the logic now. I think a small update in the descriptions for the historical rates would be helpful.
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
3. My planning spreadsheet is all in Nominal Dollars. I have 2 Income Streams that have COLAs (SS + Small-Pension) and a Larger Pension which is not Inflation Adjusted. Wondering what logic changes would be needed to shift the Calculations to use Nominal vs. Inflation Adjusted?
- Adjust SS and Small Pension values to increase with inflation
- Remove the inflation decrease to the Larger Pension
- Leave Expected Rate of Return as is but update description to indicate its Nominal
- Anything else?
You're right, PV math can indeed be performed in nominal dollars, although I would strongly advise against it (numbers 10 or 20 years from now would become extremely unintuitive, notably when plotted on a graph). About your points:
- every pension system is different, so you have to think hard about the exact terms of yours, but if there is one small part which is inflation-adjusted and one large part which is not, then your description is correct.
- the expected rate of return HAS to be modified to become nominal, starting from cells A9 and A11. It gets confusing though, should you adjust with historical inflation numbers (which varied a lot over time and geographies) or add an expected (future) inflation rate like the 2% Fed target to (real) return rates? I would strongly advise to do the latter.
- if you use a more dynamic model (e.g. a mix of 1/CAPE and bond yield), then inflation has to be added to 1/CAPE (which is a real quantity) while bond yields (for regular funds, not TIPS) are a nominal quantity and can be used as is.
- if you want to factor in some special expenses way late in the future (e.g. extra spend beyond regular budget due to long-term care costs), then it becomes tricky because the inflation adjustment highly depends on the timeframe until then. Same issue, albeit less acute, with lump sumps coming your way down the road (e.g. bequest, house downsizing).

Yes, it can all be done in nominal dollars, but again, I would strongly advise you to start thinking in real dollars. SSSSOOO much simpler and intuitive, therefore MUCH LESS error-prone.

Wish I had this conversation in 2017 when I was building out this model! At this point the heavy lifting and complexities have been dealt with. Started down this path to better reflect my expenses which have different rates of inflation (e.g. Medical Costs vs. Fuel). It's been working well and I continue to maintain and tinker to improve its reporting and capabilities.

For Spending Analysis I calculate the potential spend using:
- 4% Rule
- VPW
- TVM

This provides a gauge which I can use to measure my projected budget and a degree of confidence that I am within a reasonable spending range.

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

WoodSpinner wrote:
Tue Nov 19, 2019 3:06 pm
As part of my model, I project the expected Rate of Return for each year of Retirement. It changes based on my upward glide path (54/46 -> 60/40) and on expected returns for US Stocks, Intl Stocks, Intermediate Treasuries, Short Term Treasuries. I don't think this will ever be under the 30 Year Treasury Rate.

Is there a way to use this array of yearly expected returns to better model the annualized payments?

Am I correct that the big risk in using the 30 year Treasury Bills is understating the potential spend?
The biggest risk in using the 30y Treasury bill is that your yearly withdrawals from the PMT formula will be lower in the first few years and higher in the last few years (since it will under-state your returns relative to your actual portfolio). With too low a discount the PMT formula will always be playing "catch up". Of course this could turn out to be an advantage if future portfolio returns turn out to be lower than the expected (which is not outlandish, since given sd, there a real chance an expected 20y return of 3.5% could be 0%!).

One way to utilize your "array" of yearly expected returns is to break your portfolio down into misc buckets, each with its own purpose/return/risk and discount rate.
Your essentials (food, lodging, core expenses on the liability side and social security, pensions, CDs etc on the income side) could be discounted with your long-term safe rate (i.e. long term TIPs rate). The second bucket of discretionary liabilities (and discretionary income) could be discounted with your portfolio expected return. (For instance, 50/50 might be about 2% real.)

Using two or three buckets with differing discount rates has pluses and minuses to lumping your whole portfolio into one AA and discount rate.

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

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

WoodSpinner wrote:
Tue Nov 19, 2019 3:06 pm
As part of my model, I project the expected Rate of Return for each year of Retirement. It changes based on my upward glide path (54/46 -> 60/40) and on expected returns for US Stocks, Intl Stocks, Intermediate Treasuries, Short Term Treasuries. I don't think this will ever be under the 30 Year Treasury Rate.

Is there a way to use this array of yearly expected returns to better model the annualized payments?
I never tried to use a Present Value logic with an array of returns driven by a glidepath. I am not too sure how to do that with reasonably simple formulas. In your case, I don't see the point, as 54/46 and 60/40 are very close to each other. I'd suggest to use 60/40 and be done with it, if this is what you are converging to. Please remember, the expected rate of return is really a VERY ROUGH probabilistic input. Also the PMT formula has strong auto-correcting characteristics. So precision isn't necessary here (and would actually be pretty much meaningless).
WoodSpinner wrote:
Tue Nov 19, 2019 3:06 pm
Am I correct that the big risk in using the 30 year Treasury Bills is understating the potential spend?
Hm, I think it depends on your exact sequence of cash flows. It may understate or overstate your spending for the current year (play with cell F5 in my spreadsheet, you can manually override it). Not by a lot though. As I explained in the blog (part 2), the rate that goes in an NPV computation immediately followed by a corresponding PMT computation isn't a terribly sensitive input. Another reason to keep things simple and consistent with a single rate.
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
I think a small update in the descriptions for the historical rates would be helpful.
Yup, done, thank you.
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
Started down this path to better reflect my expenses which have different rates of inflation (e.g. Medical Costs vs. Fuel). It's been working well and I continue to maintain and tinker to improve its reporting and capabilities.
Well, if you're comfortable with it, by all means, keep going. This whole thread is for do-it-yourself hands-on people, so... to each their own! I feel you went a little far on the road towards false precision though (e.g. glidepath, separate rates of inflation, etc). But again, as long as you kept everything consistent and it makes you comfortable, then... stay the course!

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

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

jmk wrote:
Tue Nov 19, 2019 2:00 pm
siamond wrote:
Tue Nov 19, 2019 12:13 pm
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?
Discount rates are a mind binder. I get this question all the time. I always struggle to answer in a clear manner. The trick for me is to think forward (starting from now) instead thinking backward (discounting). The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW. If you spend more from your portfolio in the period of time when you don't have social security (yet), then future SS income really should be discounted according to the returns you WON'T get from your portfolio due to this extra expense. In other words, discounting is not about with the level of risk/certainty of a future income flow, it has everything to do with how you compensate today for the future income.
An alternative way of thinking is to try to match the discount rate to the asset/liability at hand: so for TVM calculations regarding Social Security (or any essential expenses/income) we would want to use a very conservative CD or treasury or annuity rates, while for more discretionary expenses/investments we'd want to use a higher expected discount rate. The argument for this is that--contrary to the annuity model the TVM was designed for--future portfolio income is not a given. While it is true that someone might elect to utilize a risky portfolio for monies that are used in the period of time when you don't yet have SS, that would be unwise given the consequence of failure. So in the end discounting is about the risk/certainty of future income flow and also has to do with how you compensate today for future income.
Yes, I see your point. I was answering in the context of the TVM model discussed in this thread, where we're using an implicit assumption that there is no partitioning of the savings, it's all one annuitized portfolio. I personally think it is MUCH wiser to do so for early retirees than to rely on a TIPS ladder or anything like that, but the LMP/RP-minded crowd would beg to disagree, and I can -somewhat- understand their perspective. So yes, you're right, it is all about context.

I actually had this precise discussion with Wade Pfau a little while ago. We went round and round... And ended up with the unexpected conclusion that we were saying the same thing in a different manner! Think about my sentence "The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW.". Now think to a partitioned portfolio with a TIPS ladder. The money being spent RIGHT NOW to compensate from lack of SS income is coming from the TIPS ladder. Ssssooo... the corresponding (expected) rate of return will be very conservative and more or less matching what one's intuition would say about future SS cash flows, given their low risk profile. This being said, this is STILL the rate of return of the money being spent NOW, NOT some number associated with the nature of the future cash flows.

Kevin M
Posts: 11252
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

willthrill81 wrote:
Sun Mar 24, 2019 11:18 am
grayfox wrote:
Sun Mar 24, 2019 7:28 am
<snip>
One thing, LibreOffice does not have TMV() function. It is PMT() function.
<snip>
You actually don't need any kind of paid software to do the calculations. I did the calculations for the OP using a free TMV app on my Android phone.
LibreOffice is free (Excel clone). So is Google Sheets (online, functions mostly similar to Excel). Both are free spreadsheet programs/applications that support the various TVM functions (PMT, FV, PV, RATE, NPER).

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

patrick013
Posts: 2760
Joined: Mon Jul 13, 2015 7:49 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 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.

I get the same number using my computer's financial calculator. Too bad it doesn't provide an after-tax number. If we withdraw from stocks we get one number, if we withdraw from bonds we get another number.

Topic Author
willthrill81
Posts: 13960
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

patrick013 wrote:
Tue Nov 19, 2019 7:44 pm
willthrill81 wrote:
Wed Feb 27, 2019 5:47 pm

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.
I get the same number using my computer's financial calculator. Too bad it doesn't provide an after-tax number. If we withdraw from stocks we get one number, if we withdraw from bonds we get another number.
It's needful to select whether the withdrawal is at the beginning of the period or at the end. This is usually an option with most financial calculators.

Everyone's tax situation is different, so a simple TVM calculator doesn't 'know' what your tax rate, etc. is.
“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

patrick013
Posts: 2760
Joined: Mon Jul 13, 2015 7:49 pm

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

willthrill81 wrote:
Tue Nov 19, 2019 7:50 pm
patrick013 wrote:
Tue Nov 19, 2019 7:44 pm
willthrill81 wrote:
Wed Feb 27, 2019 5:47 pm

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.
I get the same number using my computer's financial calculator. Too bad it doesn't provide an after-tax number. If we withdraw from stocks we get one number, if we withdraw from bonds we get another number.
It's needful to select whether the withdrawal is at the beginning of the period or at the end. This is usually an option with most financial calculators.

Everyone's tax situation is different, so a simple TVM calculator doesn't 'know' what your tax rate, etc. is.
This calculator is actually a loan payment calculator where the payments are made at the end of each payment period entered. But's working fine. Good point.

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

siamond wrote:
Tue Nov 19, 2019 5:02 pm
jmk wrote:
Tue Nov 19, 2019 2:00 pm
siamond wrote:
Tue Nov 19, 2019 12:13 pm
WoodSpinner wrote:
Mon Nov 18, 2019 9:07 pm
1. Typically I use the 30 year Treasury rate as the Discount Factor in the other calculations in my Retirement Plant (e.g. Funding Ratio). Wondering why you are using the expected rate of return for the Portfolio?
Discount rates are a mind binder. I get this question all the time. I always struggle to answer in a clear manner. The trick for me is to think forward (starting from now) instead thinking backward (discounting). The short answer is that it really has to be the (expected) rate of return of the \$\$ that you spend (or don't spend) RIGHT NOW. If you spend more from your portfolio in the period of time when you don't have social security (yet), then future SS income really should be discounted according to the returns you WON'T get from your portfolio due to this extra expense. In other words, discounting is not about with the level of risk/certainty of a future income flow, it has everything to do with how you compensate today for the future income.
An alternative way of thinking is to try to match the discount rate to the asset/liability at hand: so for TVM calculations regarding Social Security (or any essential expenses/income) we would want to use a very conservative CD or treasury or annuity rates, while for more discretionary expenses/investments we'd want to use a higher expected discount rate. The argument for this is that--contrary to the annuity model the TVM was designed for--future portfolio income is not a given. While it is true that someone might elect to utilize a risky portfolio for monies that are used in the period of time when you don't yet have SS, that would be unwise given the consequence of failure. So in the end discounting is about the risk/certainty of future income flow and also has to do with how you compensate today for future income.
Yes, I see your point. I was answering in the context of the TVM model discussed in this thread, where we're using an implicit assumption that there is no partitioning of the savings, it's all one annuitized portfolio. I personally think it is MUCH wiser to do so for early retirees than to rely on a TIPS ladder or anything like that, but the LMP/RP-minded crowd would beg to disagree, and I can -somewhat- understand their perspective. So yes, you're right, it is all about context.
I actually had this precise discussion with Wade Pfau a little while ago. We went round and round... .
One thing that dawned on me after reading your reply is that you and I are not really disagreeing. If an investor is rational then their overall asset allocation (to cover essentials + discretionary) will be equivalent to what it would be if they rationally partitioned into buckets. Which means their combined discount rate should also match the weighted discount rates of the partitions. Put another way, a rational investor's overall portfolio Asset Allocation should have a very high probability of meeting essential needs and a probability of meeting discretionary needs equal to their individual utility function. To use me for instance (!), I have a 50/50 portfolio based on the fact that the 50% CDs/bonds need to in principal cover my essentials even if I end up using stocks to pay for them rather than a "TIPs ladder". I think I was trying to address WindSpinner's way of thinking with his/her carefully developed expected returns for different assets.

A really deep dive into this issue that I highly recommend is a Kitces' article on "Choosing An Appropriate Discount Rate For Retirement Planning Strategies" . He basically agrees with you. As he puts it, .
Given that a discount rate represents the implicit “time value” of the dollars, and the opportunity cost of the money if it is not received until later, the discount rate should represent whatever could/would have been done with those dollars in the meantime. In other words, had Ashley not delayed Social Security, and taken the payments early instead, what would have happened to the money?
Assuming Ashley had a choice in the first place (i.e., she didn’t “need” the money as her sole source of spending, and had other resources available), choosing to take Social Security benefits early represents either an opportunity for Ashley to invest the payments into her portfolio, or alternatively meant she could have spent those payments and not had to withdraw from her portfolio instead. Either way, the opportunity cost of delaying is the dollars for the portfolio that are not saved, or the dollars from the portfolio that would have to be withdrawn/liquidated/spent while waiting for Social Security. Which means the portfolio’s expected rate of return is, once again, the appropriate discount rate.
Not that you're asking, but the discount rate I personally use in my own TVM funded ratio spreadsheet is a weighted average of my portfolio's valuation-based expected-returns for the next 20 years (2.2% for 50/50), combined with the portfolio 91y historical cagr (4.7% for 50/50) for the remaining 25 years. In other words, I assume lower returns then a return-to-mean. This comes out to a lower overall discount rate (3.6% for 50/50) than the long-term averages you and Longinvest use but a higher figure than one would get through simple valuation.

jmk

WoodSpinner
Posts: 915
Joined: Mon Feb 27, 2017 1:15 pm

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

JMK,

Could you elaborate a bit in the calculations used to get your discount rate. My apologies but I am not sure I fully understand.

That said, I am doing something similar, developing a yearly Expected Return (both Nominal and Real) for each year of retirement. Projecting lower expected returns over next 5 years and then reversion towards historical averages. Easy enough to implement but certainly could be false precision..

Currently reviewing my 2020 projected income, expense, tax, and Roth Conversion plans so now is a good time to adjust.

WoodSpinner

#Cruncher
Posts: 2859
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

Kevin M wrote:
Tue Nov 19, 2019 6:10 pm
... the various TVM functions (PMT, FV, PV, RATE, NPER).
For anyone wanting to gain familiarity with these five functions, I've created a little six row by four column spreadsheet that may be useful. Enter any four of the five main parameters and it will use the appropriate function [1] to calculate the value of the parameter left blank. Here is an example of it calculating the payment mentioned in the original post:
willthrill81 wrote:
Wed Feb 27, 2019 5:47 pm
When we input all of these variables into the TMV and solve for the current payment, the withdrawal would have been \$49,716.40 ...

Code: Select all

``````Row  COL A                                   COL B   Col C       Col D
1  Rate per period                           3.1%  RATE
2  Number of periods                          32   NPER
3  Payment per period                              PMT     49,716.40
4  Present value                      (1,000,000)  PV
5  Future value                                0   FV
6  Pmt at period start (1) or end (0)          0``````
To use the spreadsheet Select All, Copy, and Paste [2] the following at cell A1 of a blank Excel sheet:

Code: Select all

``````Rate per period	0.031	RATE	=IF(COUNT(B\$1:B\$5)=5,"Leave one of the five parameters blank.",IF(COUNT(B\$1:B\$5)<4,IF(ISNUMBER(B1),"","Leave only one parameter blank."),IF(ISNUMBER(B1),"",RATE(B\$2,B\$3,B\$4,B\$5,B\$6))))
Number of periods	32	NPER	=IF(COUNT(B\$1:B\$5)=5,"Leave one of the five parameters blank.",IF(COUNT(B\$1:B\$5)<4,IF(ISNUMBER(B2),"","Leave only one parameter blank."),IF(ISNUMBER(B2),"",NPER(B\$1,B\$3,B\$4,B\$5,B\$6))))
Payment per period		PMT	=IF(COUNT(B\$1:B\$5)=5,"Leave one of the five parameters blank.",IF(COUNT(B\$1:B\$5)<4,IF(ISNUMBER(B3),"","Leave only one parameter blank."),IF(ISNUMBER(B3),"",PMT(B\$1,B\$2,B\$4,B\$5,B\$6))))
Present value	-1000000	PV	=IF(COUNT(B\$1:B\$5)=5,"Leave one of the five parameters blank.",IF(COUNT(B\$1:B\$5)<4,IF(ISNUMBER(B4),"","Leave only one parameter blank."),IF(ISNUMBER(B4),"",PV(B\$1,B\$2,B\$3,B\$5,B\$6))))
Future value	0	FV	=IF(COUNT(B\$1:B\$5)=5,"Leave one of the five parameters blank.",IF(COUNT(B\$1:B\$5)<4,IF(ISNUMBER(B5),"","Leave only one parameter blank."),IF(ISNUMBER(B5),"",FV(B\$1,B\$2,B\$3,B\$4,B\$6))))
Pmt at period start (1) or end (0)	0``````
Enter values in any four of the cells in the range B1:B5 leaving the fifth cell blank. The spreadsheet will calculate the parameter left blank. For example, here is the output if "Payment per period" is entered and "Present Value" is left blank:

Code: Select all

``````  1  Rate per period                           3.1%  RATE
2  Number of periods                          32   NPER
3  Payment per period                  49,716.40   PMT
4  Present value                                   PV     (1,000,000)
5  Future value                                0   FV
6  Pmt at period start (1) or end (0)          0``````
1. Here are the five functions: RATE, NPER, PMT, PV, and FV. Beginning balances and money flowing in should have the opposite sign of ending balances and money flowing out. I generally enter beginning balances and money flowing in as negative. In some cases the RATE function requires an additional "guess" parameter not shown in my little spreadsheet.
2. If you have trouble pasting, try "Paste Special" and "Text".

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

WoodSpinner wrote:
Wed Nov 20, 2019 10:56 am
JMK, Could you elaborate a bit in the calculations used to get your discount rate. My apologies but I am not sure I fully understand.
That said, I am doing something similar, developing a yearly Expected Return (both Nominal and Real) for each year of retirement. Projecting lower expected returns over next 5 years and then reversion towards historical averages. Easy enough to implement but certainly could be false precision..
I have a 45 year plan (retiring in 2 years out to the 25% probability longevity) at 50/50 AA. I use the 1/CAPE for my 20y stock ER. I know the ER and duration of my "safe" portion of my investments, which is a combination of CD yield and SEC yield on bond funds. I use the Cleveland Fed model of expected inflation over that 20 years. So my current 20y projected ER for 50/50 is 2.2% real.

But I also assume that after 20 years of lower than average returns, things will return to the mean, assuming a crash or two. Do note, this huge assumption is more a leap of faith on my part. For the long term average, I use Simba's TSM and TBM data but only counted 1928 on (given the existential difference in the bond market under the gold standard in the previous century). The long term CAGR of 50/50 from 1928 to present comes to 4.7% real ER. (This is a bit lower than the simple average of 90 year TSM and 90 year TBM due to rebalancing.) As a "check" I also ran the median of all the 25 year CAGRs of 50/50 from 1871 to present, which came to 4.6%, very close.

So... drum roll.... for my rough-and-tumble discount rate on my total portfolio for 45 years I use the weighted average of 20 years of 2.2% and 25 years of 4.7%, which comes to 3.6%. This is what I use to figure my default Funded Ratio in my 45 year spreadsheet. Do keep in mind that this figure will change each year based on a changing 20 year ER together with a decreasing numbers of years of longevity beyond that as I age.

(Sidenote: I'm somewhat worried that the last century of US returns are higher than the international average, so may adjust the 4.7% long term part of the total down to the 4.0% median 1930-2015 world 50/50 found in the "Return on Everything" database; this would decrease the 3.6% overall down to about 3.2%.)

I will admit that because I am a low-income individual without family wealth, I also run the same Funded Ratio with a similar 45 year Safe expected 1.4% discount rate (i.e. ER of just CDs). This obviously produces a lower Funded Ratio, but I use it as my conservative check on when I can retire. (The idea of varying the TVM discount rate depending on the context of what one is asking is the point I was making with Siamond.)

With regard to the year-by-year projections, in a similar vein I actually have a spreadsheet where I've experimented with backtesting different methods of combining things: Single vs changing discount rates for: (1) Single portfolio, one discount rate (siamond-style), vs (2) Core expenses discounted at ER Bond rate + Discretionary at ER Stock rate, vs (3) Keeping 20 years of NPV Core Expenses at Bond ER, rest of core expenses at 50/50, discretionary in stocks. After tidying it up I'm happy to share it here. But so far it looks like the simple Average used in VPW and Siamond's TVM produces a more stable and larger withdrawal amount than year by year flipping around as valuations change. The rub is this is not surprising since I am backtesting--of course a long-term average will be more stable. But going forward, not sure this holds. However, the rule of thumb in modeling is simple is always better than complex to minimize errors.

Hope this helps. Just offering it as one person's way of doing things, not at all convinced it's better than anyone else's.
Last edited by jmk on Thu Nov 21, 2019 1:02 pm, edited 3 times in total.

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

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

jmk wrote:
Wed Nov 20, 2019 9:47 am
A really deep dive into this issue that I highly recommend is a Kitces' article on "Choosing An Appropriate Discount Rate For Retirement Planning Strategies" . He basically agrees with you. As he puts it, .
Given that a discount rate represents the implicit “time value” of the dollars, and the opportunity cost of the money if it is not received until later, the discount rate should represent whatever could/would have been done with those dollars in the meantime.[...] Which means the portfolio’s expected rate of return is, once again, the appropriate discount rate.
Ah, thank you for sharing this article. Unsurprisingly, Kitces put it much more clearly than I ever did... Totally agreed. And yes, the three of 3 (and Wade Pfau too) are saying the same thing. It just depends on the exact context, i.e. which portfolio \$ will be used in the meantime (while waiting for future fixed income, e.g. SS/Pension/etc). But again, in an NPV/PMT sequence of formulas to account for future cash flows, the outcomes have limited sensitivity to the rate parameter, so we're having an interesting but semi-academic discussion here.

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

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

jmk wrote:
Wed Nov 20, 2019 9:47 am
Not that you're asking, but the discount rate I personally use in my own TVM funded ratio spreadsheet is a weighted average of my portfolio's valuation-based expected-returns for the next 20 years (2.2% for 50/50), combined with the portfolio 91y historical cagr (4.7% for 50/50) for the remaining 25 years. In other words, I assume lower returns then a return-to-mean. This comes out to a lower overall discount rate (3.6% for 50/50) than the long-term averages you and Longinvest use but a higher figure than one would get through simple valuation.
We're switching to the expected rate of return for the regular portfolio here, which is a parameter with much greater sensitivity than for the cash flow math. Historical averages are a good starting point and have the great advantage of being very easy to understand and to plug in a spreadsheet (as I did for the default setting of the TMV spreadsheet we're discussing). They are far from optimal though, potentially leading to disturbing variability of yearly outcomes, plus one has to be careful with 'success bias' when using US historical numbers.

Factoring in a dynamic expected returns model of some sorts is DEFINITELY my recommendation IF one is hands-on enough to deal with a couple of easy extra steps. Not only does this bring welcome smoothing properties, but it should also be more adaptive to an unknown long-term future. I don't want to clutter the spreadsheet with too many details along those lines, but, yes, such dynamic approach does help.

Not that you're asking , but my approach is to use 1/CAPE for stocks (e.g. checking the StarCapital Web site to find the value for US/developed/emerging markets). For bonds, since my investments are centered on VBILX, I use the corresponding SEC Yield, subtract the expected inflation (Fed target of 2%), then average with long-term worldwide bonds returns. SEC Yield by itself is too much of a short-term prediction for my taste, hence my little trick, which is similar to yours. For stocks, I feel fine with 1/CAPE, this is a robust model with sound empirical and logical/intuitive evidence behind it. Again, all of this is just a rough guess, precision isn't important here. Being comfortable with your own model IS critical though, for obvious behavioral reasons.

GAAP
Posts: 955
Joined: Fri Apr 08, 2016 12:41 pm

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

siamond wrote:
Wed Nov 20, 2019 12:59 pm
Not that you're asking , but my approach is to use 1/CAPE for stocks (e.g. checking the StarCapital Web site to find the value for US/developed/emerging markets). For bonds, since my investments are centered on VBILX, I use the corresponding SEC Yield, subtract the expected inflation (Fed target of 2%), then average with long-term worldwide bonds returns. SEC Yield by itself is too much of a short-term prediction for my taste, hence my little trick, which is similar to yours. For stocks, I feel fine with 1/CAPE, this is a robust model with sound empirical and logical/intuitive evidence behind it. Again, all of this is just a rough guess, precision isn't important here. Being comfortable with your own model IS critical though, for obvious behavioral reasons.
I'm also somewhat similar, using 1/CAPE for equities. I set a minimum CAPE value of 12.5 to limit my estimate to 8% real -- not that I really want things to get bad enough to actually hit that limit. I've been using the StarCapital site also, but I'm not too thrilled with the time delay between end of month and when they actually update the site. For that reason, I've been considering using Research Affilates https://interactive.researchaffiliates. ... e=Equities, but haven't gotten around to seeing how well they agree.

For bonds, I estimate long term real return to equal the current nominal 1-year return -- that should generally underestimate actual results for a global bond allocation.

I too feel that a rough guess is sufficient -- and a conservative (underestimating) rough guess is even better.
“Adapt what is useful, reject what is useless, and add what is specifically your own.” ― Bruce Lee

patrick013
Posts: 2760
Joined: Mon Jul 13, 2015 7:49 pm

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

patrick013 wrote:
Tue Nov 19, 2019 8:04 pm
willthrill81 wrote:
Tue Nov 19, 2019 7:50 pm
patrick013 wrote:
Tue Nov 19, 2019 7:44 pm
willthrill81 wrote:
Wed Feb 27, 2019 5:47 pm

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.
I get the same number using my computer's financial calculator. Too bad it doesn't provide an after-tax number. If we withdraw from stocks we get one number, if we withdraw from bonds we get another number.
It's needful to select whether the withdrawal is at the beginning of the period or at the end. This is usually an option with most financial calculators.

Everyone's tax situation is different, so a simple TVM calculator doesn't 'know' what your tax rate, etc. is.
This calculator is actually a loan payment calculator where the payments are made at the end of each payment period entered. But's working fine. Good point.
Here's a very good HP-12C which can be installed on google-chrome. The manual is needed (PDF).

Well recommended.

And here is an actual spreadsheet to see the calc actually adds up.

I'd probably use 5 or 6 per cent for equity returns based on lowest returns observed during recent bear market periods but Bogle's method or CAPE could have more statistical merit.

My bond ladder is designed to buy when yields are above the historical mean in steps so the SEC yield would have to do for now. Someday the ladder will be all 29 year bonds yielding well above the long term mean.

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

GAAP wrote:
Wed Nov 20, 2019 2:52 pm
I'm also somewhat similar, using 1/CAPE for equities. I set a minimum CAPE value of 12.5 to limit my estimate to 8% real -- not that I really want things to get bad enough to actually hit that limit. I've been using the StarCapital site also, but I'm not too thrilled with the time delay between end of month and when they actually update the site. For that reason, I've been considering using Research Affilates https://interactive.researchaffiliates. ... e=Equities, but haven't gotten around to seeing how well they agree.
I look at the Research Affiliates often, and fwiw their valuation based estimate doesn't agree with 1/CAPE; I think they may refine their estimate based on "true valuation" related to interest rates and other factors similar to how Vanguard does things. One thing I do like about their site is that they utilize Valuation based ER and also a separate ER based on Yield + Growth. They allow you to customize any weighted average of these two valuation methods. Neat!

For instance, at the moment, 100% US large 10 year ER on RA site is:
0.5% real by their Valuation method (way lower than the 3.2% ER by 1/cape), but
3.2% real by their "Dividend + Growth" method.

Quite a difference! When I use their site I use 50/50 split between the two methods of valuation since they both have academic merit and track record and I'm not smart enough to know which is better.
Last edited by jmk on Thu Nov 21, 2019 8:20 pm, edited 2 times in total.

Topic Author
willthrill81
Posts: 13960
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

jmk wrote:
Thu Nov 21, 2019 1:19 pm
GAAP wrote:
Wed Nov 20, 2019 2:52 pm
I'm also somewhat similar, using 1/CAPE for equities. I set a minimum CAPE value of 12.5 to limit my estimate to 8% real -- not that I really want things to get bad enough to actually hit that limit. I've been using the StarCapital site also, but I'm not too thrilled with the time delay between end of month and when they actually update the site. For that reason, I've been considering using Research Affilates https://interactive.researchaffiliates. ... e=Equities, but haven't gotten around to seeing how well they agree.
I look at the Research Affiliates often, and fwiw their valuation based estimate doesn't agree with 1/CAPE; I think they may refine their estimate based on "true valuation" related to interest rates and other factors similar to how Vanguard does things. One thing I do like about their site is that they utilize Valuation based ER and also a separate ER based on Yield + Growth. They allow you to customize any weighted average of these too valuation methods. Neat!

For instance, at the moment, 100% US large 10 year ER on RA site is:
0.5% real by their Valuation method (way lower than the 3.2% ER by 1/cape), but
3.2% real by their "Dividend + Growth" method.

Quite a difference!
.5% real over the next 10 years?!? If I thought that was the most likely return, I wouldn't own U.S. stocks at all.
“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

Kevin M
Posts: 11252
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

willthrill81 wrote:
Thu Nov 21, 2019 1:42 pm
.5% real over the next 10 years?!? If I thought that was the most likely return, I wouldn't own U.S. stocks at all.
Well, the 10-year TIPS yield is only 0.13%, so some allocation to stocks with an expected real return of 0.5% would increase the portfolio expected return. Of course you need to consider the risk as well as the expected return, so the stock allocation might not be particularly high.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

Topic Author
willthrill81
Posts: 13960
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

Kevin M wrote:
Thu Nov 21, 2019 3:48 pm
willthrill81 wrote:
Thu Nov 21, 2019 1:42 pm
.5% real over the next 10 years?!? If I thought that was the most likely return, I wouldn't own U.S. stocks at all.
Well, the 10-year TIPS yield is only 0.13%, so some allocation to stocks with an expected real return of 0.5% would increase the portfolio expected return. Of course you need to consider the risk as well as the expected return, so the stock allocation might not be particularly high.

Kevin
As it was presented, the RA estimate does not concern fixed income at all, only U.S. stocks. Many here believe 1/CAPE to be a conservative estimate of forward 10 year stock real returns, and it's value is currently 3.31% according to Shiller's site. Yes, that is only the mid-point of a projected range, but it's still the predicted value and much higher than .5%.
“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

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

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

willthrill81 wrote:
Thu Nov 21, 2019 1:42 pm
.5% real over the next 10 years?!? If I thought that was the most likely return, I wouldn't own U.S. stocks at all.
And that's exactly why Research Affiliates recommends 0% US large, and mostly international, emerging, alts and factors at the moment! Here's RA's recommended efficient portfolio with 10% SD (After clicking the SD you want along the left, click along the right to show the details of recommended assets.)

It's worth remembering that even with an equity ER in the next ten 20 years of 3.3% (1/CAPE), there is still a 1/4 chance you would end up with 0.38% at the end of that period!! The downside of treating your risky portfolio like an annuity is that it's easy to forget that ER are not guaranteed like a real annuity.

Take a look at this monte carlo:

Jonathan
Last edited by jmk on Thu Nov 21, 2019 9:59 pm, edited 1 time in total.

Topic Author
willthrill81
Posts: 13960
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

jmk wrote:
Thu Nov 21, 2019 8:13 pm
willthrill81 wrote:
Thu Nov 21, 2019 1:42 pm
.5% real over the next 10 years?!? If I thought that was the most likely return, I wouldn't own U.S. stocks at all.
And that's exactly why Research Affiliates recommends 0% US large, and mostly international, emerging, alts and factors at the moment! Here's RA's recommended efficient portfolio with 10% SD

I will say this to you though WillThrill: Even with an equity ER in the next ten 20 years of 3.3% (1/CAPE), there is still a 1/4 chance you would end up with 0.38% at the end of that period!! The downside of treating your risky portfolio like an annuity is you can sometimes forget that ER are not guaranteed like a real annuity.

Take a look at this monte carlo:

Jonathan
Yes, I realize that there is a distribution around the expected return and said so above. U.S. stocks had negative real returns from 2000-2009, so that could certainly happen again.

That being said, I don't put any stock at all in Monte Carlo analysis when it comes to the financial markets (no pun intended). They have serious problems with their distributions in that they predict 'fatter tails' than what the historic data suggests that they should.
“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

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

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

jmk wrote:
Thu Nov 21, 2019 8:13 pm
The downside of treating your risky portfolio like an annuity is that it's easy to forget that ER are not guaranteed like a real annuity. [...]
We can debate the plus and minus of various expected returns models for a long, long time (RA baffles me, to be honest), but this precise point you made is indeed very important. This is why we all need to clearly communicate that we're speaking of a variable withdrawal method (possibly smoothed, but still variable) and that we need sanity checks against extreme situations that the annuitization math can lead us to. Hence all the discussions we had about spending gates in the original thread Willthrill81 opened and the soft gate model in the spreadsheet I shared. This is a hand-on model, which requires a deep understanding of what we're doing (upsides and downsides and inner mechanics), so that we use it with our eyes wide open.

patrick013
Posts: 2760
Joined: Mon Jul 13, 2015 7:49 pm

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

Everybody wants their "research" to be the accepted way of doing things. Should we take the MC distribution or take the historical numbers and chop off 5% from each side to get a 90% confidence interval of returns ? A non-normal confidence interval. So the best way to confirm we are not clairvoyant could be the simplest. Any estimate can be adjusted but a moderate AA can lessen that adjustment. Adjust withdrawals every year if you like for the subjective future. I can't see where any of these math-centric methods belong in a finance textbook except the basic mean and resulting withdrawal. Subjectively selecting a low mean return anyway for safety. IMO. And see if MC confirms your decision at perhaps 5% return for stocks.

Topic Author
willthrill81
Posts: 13960
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

patrick013 wrote:
Fri Nov 22, 2019 11:06 am
Everybody wants their "research" to be the accepted way of doing things. Should we take the MC distribution or take the historical numbers and chop off 5% from each side to get a 90% confidence interval of returns ? A non-normal confidence interval. So the best way to confirm we are not clairvoyant could be the simplest. Any estimate can be adjusted but a moderate AA can lessen that adjustment. Adjust withdrawals every year if you like for the subjective future. I can't see where any of these math-centric methods belong in a finance textbook except the basic mean and resulting withdrawal. Subjectively selecting a low mean return anyway for safety. IMO. And see if MC confirms your decision at perhaps 5% return for stocks.
Keep in mind that an advantage of the TMV approach is that over or underestimating returns is not likely to be a serious issue, especially if one maintains a little common sense as siamond advocates (e.g. don't assume 15% real returns no matter what). If retirees overestimate their returns, then they are simply front-loading their withdrawals somewhat, which is actually something that we want to do (i.e. spend more when we're young, healthy, and alive). Underestimating returns has the opposite effect and leads to a classic first-world problem.
“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

Kevin M
Posts: 11252
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

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

willthrill81 wrote:
Fri Nov 22, 2019 11:23 am
Keep in mind that an advantage of the TMV approach ...
<snip>
Just curious: is it just a typo when you (and others) write "TMV" instead of "TVM"? TVM stands for "time value of money". I don't know what "TMV" stands for.

In your OP, you wrote TVM at first, then switched to TMV:
willthrill81 wrote:
Wed Feb 27, 2019 5:47 pm
When I've brought up the concept of using the TVM formula ...
<snip>
When we input all of these variables into the TMV ...
<snip>
... and the TMV formula would indicate what the current withdrawal should be.
Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)