I agree that is much simpler to think about using a single number. What surprised me is that the explicit formula that Ken uses (not Excel's NPV function) behaves differently when you use Inflation = 2%, Return = 5%, than when you use Inflation = 0%, Return = 3%. I was expecting those to have the same result but they don't. So it made me curious if his formula captures something important that I don't understand about the difference between those two cases.GAAP wrote: ↑Tue Nov 20, 2018 11:03 amI find it much easier to work with a single real return assumption than independent nominal returns and inflation rate. Two variables will multiply the range of potential outcomes. To varying degrees at different times, nominal returns and inflation influence each other, and yield a real return result. I would much rather work with a "reasonable" expectation of real returns than try to create two "reasonable" estimates that end up creating real returns.siamond wrote: ↑Mon Nov 19, 2018 8:07 pmI didn't investigate Ken's spreadsheet in any detail, I only took a cursory look. If you succeeded to get the calculations to match, this is a great starting point. PV calculations can be performed in either nominal or real terms, but one has to be careful to stay fully consistent. I'll try to take a closer look at Ken's spreadsheet later tonight.skjoldur wrote: ↑Mon Nov 19, 2018 7:36 pmIf I use the same real rate of return in both spreadsheets, and set Ken's desired increase to zero, then I get the same result. However if I follow Ken's instructions and use the desired return, the results do not match.

Is this a bug or a feature? Does it indicate some basic thing about inflation and PV calculations or is it an error?

## Time Value of Money vs. Variable Percentage Withdrawal (VPW)

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Ken's explicit formula always put me off looking deeper into his spreadsheet. I have no idea why he doesn't just NPV/PV functions and (AFAIK) there's no explanation anywhere why or how his formula is different.skjoldur wrote: ↑Tue Nov 20, 2018 9:01 pmWhat surprised me is that the explicit formula that Ken uses (not Excel's NPV function) behaves differently when you use Inflation = 2%, Return = 5%, than when you use Inflation = 0%, Return = 3%. I was expecting those to have the same result but they don't. So it made me curious if his formula captures something important that I don't understand about the difference between those two cases.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Ok, I can't find the time to study Ken's spreadsheet in depth, but here are a couple of thoughts:AlohaJoe wrote: ↑Tue Nov 20, 2018 9:47 pmKen's explicit formula always put me off looking deeper into his spreadsheet. I have no idea why he doesn't just NPV/PV functions and (AFAIK) there's no explanation anywhere why or how his formula is different.skjoldur wrote: ↑Tue Nov 20, 2018 9:01 pmWhat surprised me is that the explicit formula that Ken uses (not Excel's NPV function) behaves differently when you use Inflation = 2%, Return = 5%, than when you use Inflation = 0%, Return = 3%. I was expecting those to have the same result but they don't. So it made me curious if his formula captures something important that I don't understand about the difference between those two cases.

a) I do remember going through Ken's 'explicit formula' a few years ago, and although I don't understand either why he doesn't leverage NPV/PMT functions, I did conclude at the time that he was properly emulating it.

b) I have a strong suspicion that skjoldur's observation is due to an arithmetic vs. geometric issue. How much is 5% minus 2%? One could argue it is equal to 5% - 2% = 3.00%. One could argue it is actually (1+5%)/(1+2%) = 2.94%. The latter is more correct when trying to go from nominal returns to real returns.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

I did a good scrubbing of the Google sheet that was discussed in this thread, notably adding a README tab with various explanations and instructions, and streamlining a bit the various tabs and calculations.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

It would probably be a good idea to complement such spreadsheet with a wiki and/or a blog article illustrating a few examples...

https://docs.google.com/spreadsheets/d/ ... sp=sharing

**Reviewers would be welcome**(text and formulas). Please post here or send me PMs.It would probably be a good idea to complement such spreadsheet with a wiki and/or a blog article illustrating a few examples...

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Thank you so much for the work you’ve put in here! I’ve been trying out the various revisions of your spreadsheet, and have incorporated bits into my private master spreadsheet (which you can take as a compliment).siamond wrote: ↑Fri Nov 23, 2018 5:15 amI did a good scrubbing of the Google sheet that was discussed in this thread, notably adding a README tab with various explanations and instructions, and streamlining a bit the various tabs and calculations.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Reviewers would be welcome(text and formulas). Please post here or send me PMs.

It would probably be a good idea to complement such spreadsheet with a wiki and/or a blog article illustrating a few examples...

"How much you know is less important than how clearly you understand where the borders of your ignorance begin." — Jason Zweig

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Thanks for your work on the spreadsheet, Siamond.

I have a question about inflation and the discount rate applied to future cash flows.

It seems that the inflation adjusted cash flows (lump and recurring) would have to use a different discount rate than the non-adjusted cash flows.

In the simple example, the first SS payment of $12K will be nominally larger in 6 years due to inflation. The non-adjusted payment of $13K in the same year, I assume, is nominal and will be worth less than $13K of todays dollars.

Am I missing something?

I have a question about inflation and the discount rate applied to future cash flows.

It seems that the inflation adjusted cash flows (lump and recurring) would have to use a different discount rate than the non-adjusted cash flows.

In the simple example, the first SS payment of $12K will be nominally larger in 6 years due to inflation. The non-adjusted payment of $13K in the same year, I assume, is nominal and will be worth less than $13K of todays dollars.

Am I missing something?

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Actually, in the simple example, both column C and column D are quantities which are expressed in 'real' (inflation-adjusted) terms. For column C, this is simple and intuitive because Social Security and (a chunk of) Pension is automatically adjusted for inflation. For column D, this is the second part of the pension, the part that would NOT be adjusted for inflation year over year, therefore its real value would degrade year over year, which I implemented with a simple geometric formula. This degradation only starts when the pension starts though. In real life, most pensions have some level of adjustment and non-adjustment to inflation (e.g. my wife's pension will have $12k protected against inflation, the rest will degrade, so I have to separate those two numbers between column C and column D).skjoldur wrote: ↑Fri Nov 23, 2018 6:18 pmI have a question about inflation and the discount rate applied to future cash flows.

It seems that the inflation adjusted cash flows (lump and recurring) would have to use a different discount rate than the non-adjusted cash flows.

In the simple example, the first SS payment of $12K will be nominally larger in 6 years due to inflation. The non-adjusted payment of $13K in the same year, I assume, is nominal and will be worth less than $13K of todays dollars.

Am I missing something?

Now, as to the discount rate (or any rate of return in the spreadsheet), this is a real rate. There is no reason to have it different between an inflation-adjusted SS/Pension cash flow or a non-inflation-adjusted Pension cash flow, as long as the corresponding cash flows are both expressed as real quantities like done in the simple example. Overall, it is much simpler to consistently think in real terms...

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

On the Readme page where you write "using a basic loan formula, e.g. PMT()", instead of e.g. it should be i.e.siamond wrote: ↑Fri Nov 23, 2018 5:15 amI did a good scrubbing of the Google sheet that was discussed in this thread, notably adding a README tab with various explanations and instructions, and streamlining a bit the various tabs and calculations.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Reviewers would be welcome(text and formulas). Please post here or send me PMs.

More seriously, you might consider making it easier to add more years to the spreadsheet. Being able to model more/fewer than 30 years is another benefit of this approach. Some changes you could make (all based on the Simple Example tab):

Make it easier to copy & paste new rows. The main thing to tweak is how the "Year 37" (e.g. column A) is hardcoded. It can be made automagic with:

Code: Select all

```
="Year " & row(A37) - row(A$7)
```

**E$36**to just

**E**(which you already do in A6):

Code: Select all

```
=npv(G12, E$7:E)*(1+G12)
```

*On the Scenario 1/Scenario 2 sheets:*

L4: the note says "grace period" but that phrase isn't used elsewhere, so it isn't clear what this refers to. Is it just the "years to Social Security"? If so, can it be changed to just be

Code: Select all

```
=C6
```

In cell X4 one of the asset allocations is "1927". Choosing it results in an error (naturally). The 1927 should be removed.

There are other asset allocations in the table to the right "AA 40/60" and "AA Flat5" but they aren't options in the dropdown. I'd recommend either adding them to the dropdown or removing them from the table. That way people aren't tempted with something they can't pick

Reading it over, the distinction between the soft spending gate, the hard spending gate, and the spending floor isn't immediately clear. The Readme briefly explains two of them but I don't think enough information is really given for a user of the spreadsheet to understand what's really going on. Also, the explanations are kinda split into two different places. My suggestion would be to explain all three together, give some guidance on when to use/when not to use, and maybe even a motivating example.

This would be a bigger change but: when spreadsheets start to become larger like this, it often helps to group all of the user-chosen fields together in one place instead of being spread out across the spreadsheet. Obviously, this doesn't really work for the cash flows which need to stay where they are but others could be grouped together. A quick mock up of what I mean to help make it clearer:

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Fixed.

Fixed. I referenced the previous row, which is a more stable anchor. Similar change in Scenario tabs.

Fixed (and a couple of other similar instances in Scenario tabs).

I don't understand this point, A6 is computed and G15 references it. They don't have to be 30... I don't see the relation to cells G19/20 either?

Hm, yes, forgot to explain this one. Nothing to do with Full SS. This allows to extend the annuitization of the (regular) portfolio by X years over the expected lifetime, which has numerous applications. I beefed up the cell's note and added a line of explanation in the Readme tab.

Fixed the data validation range, which was indeed broken.AlohaJoe wrote: ↑Sat Nov 24, 2018 8:42 pmIn cell X4 one of the asset allocations is "1927". Choosing it results in an error (naturally). The 1927 should be removed.

There are other asset allocations in the table to the right "AA 40/60" and "AA Flat5" but they aren't options in the dropdown. I'd recommend either adding them to the dropdown or removing them from the table.

I deliberately explained the concepts step by step, to try to not overwhelm a first-time reader. I actually did it on purpose, I didn't want to derail the first-time reader. The main focus really should be on the regular PV math, much more than the spending gates. Would rather keep it that way. Scenario2 IS intended to be a motivating example!AlohaJoe wrote: ↑Sat Nov 24, 2018 8:42 pmReading it over, the distinction between the soft spending gate, the hard spending gate, and the spending floor isn't immediately clear. The Readme briefly explains two of them but I don't think enough information is really given for a user of the spreadsheet to understand what's really going on. Also, the explanations are kinda split into two different places. My suggestion would be to explain all three together, give some guidance on when to use/when not to use, and maybe even a motivating example.

I do agree the instructions are a bit terse. What I envision is to create a blog article that would explain the various concepts in much more details, to complement the spreadsheet.

That's really a matter of style. Personally, I prefer to keep the settings related to a computing column on top of the computing column. This spreadsheet is intended for 'hands on' people who are supposed to look at the formulas themselves and develop their own variations of the whole thing. I don't think we should try to package it like other types of spreadsheet (e.g. Ken Steiner's version of the same ideas) where the inner workings are pretty much a black box (or at least not intended to be examined by most users).AlohaJoe wrote: ↑Sat Nov 24, 2018 8:42 pmThis would be a bigger change but: when spreadsheets start to become larger like this, it often helps to group all of the user-chosen fields together in one place instead of being spread out across the spreadsheet. Obviously, this doesn't really work for the cash flows which need to stay where they are but others could be grouped together. [...]

MANY THANKS FOR THE DETAILED REVIEW. MUCH APPRECIATED.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

G19/G20 are showing what 4% withdrawals would be. (Just as a comparison point, I imagine.) I was just saying that 4% withdrawals only make sense for 30 year periods. If someone adds 10 years or removes 10 years, then 4% isn't quite the right benchmark.

Then I talked myself into handling that being a pointless change to make.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Oh, I see. Yes, I agree with your second self! 4% is a cell in yellow, an input, something to tune based on the scenario being looked at (and its duration). No change!AlohaJoe wrote: ↑Sun Nov 25, 2018 1:03 amG19/G20 are showing what 4% withdrawals would be. (Just as a comparison point, I imagine.) I was just saying that 4% withdrawals only make sense for 30 year periods. If someone adds 10 years or removes 10 years, then 4% isn't quite the right benchmark.

Then I talked myself into handling that being a pointless change to make.

I also beefed up a bit the instructions/explanations based on your feedback and various comments I received from another reviewer.

- Lieutenant.Columbo
**Posts:**1178**Joined:**Sat Sep 05, 2015 9:20 pm**Location:**Los Angeles CA

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

This Topic is excelente!

And that comes from someone who only understand 50% of what's been posted

Thank you everyone.

And that comes from someone who only understand 50% of what's been posted

Thank you everyone.

Lt. Columbo: Well, what do you know. Here I am talking with some of the smartest people in the world, and I didn't even notice!

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Admittedly, we've been going down the weeds pretty fast in this thread. For those of you who would like to revisit some basics about "Time Value of Money", I assembled a Powerpoint presentation, and used it earlier today during a local chapter meeting. It seemed to work reasonably ok, even if some eyes were glazing over at the end of the talk...

Here it is, maybe this can be of use to other people interested in this complex topic:

https://tinyurl.com/y8gbb9fl

Here it is, maybe this can be of use to other people interested in this complex topic:

https://tinyurl.com/y8gbb9fl

- willthrill81
**Posts:**10113**Joined:**Thu Jan 26, 2017 3:17 pm**Location:**USA

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

I think that it's very good, although to be honest, it is probably a bit too information dense for an audience unfamiliar with the topic.siamond wrote: ↑Sat Dec 15, 2018 7:44 pmAdmittedly, we've been going down the weeds pretty fast in this thread. For those of you who would like to revisit some basics about "Time Value of Money", I assembled a Powerpoint presentation, and used it earlier today during a local chapter meeting. It seemed to work reasonably ok, even if some eyes were glazing over at the end of the talk...

Here it is, maybe this can be of use to other people interested in this complex topic:

https://tinyurl.com/y8gbb9fl

“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

- willthrill81
**Posts:**10113**Joined:**Thu Jan 26, 2017 3:17 pm**Location:**USA

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

50%? You're way ahead of me!Lieutenant.Columbo wrote: ↑Sat Dec 08, 2018 10:24 amThis Topic is excelente!

And that comes from someone who only understand 50% of what's been posted

Thank you everyone.

“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

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Lieutenant.Columbo wrote: ↑Mon Nov 19, 2018 6:00 pmThis is a very helpful Topic. However, such a large amount of dense & complex posts makes it hard for those like me to synthesize the key learning points. I'd like to suggest you consider commissioning Willthrill81 and or siamond and or AlohaJoe tovoluntarilycreate a BH-Wiki page on the TVM (with spreadsheet/s?).

Sorry it took me forever, but I finally started to write the blog articles I was envisioning. Here is Part 1, which focuses on Time Value of Money basics while building up to a fairly complex scenario:

https://finpage.blog/2019/02/01/early-r ... e-of-money

I will follow up in a few days with a Part 2 discussing spending gates and more. I'll probably open a new thread once I'm done with all the writing, but early feedback is of course welcome.

I 'stole' a few words and sentences from this excellent post of yours. Thank you for the input!

- willthrill81
**Posts:**10113**Joined:**Thu Jan 26, 2017 3:17 pm**Location:**USA

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Your 'theft' flatters me.

Your write-up is excellent and not too mathematically overwhelming I think.

“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

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Cool. I updated a bit the article by adding a short section at the end introducing the 'Simple Example' calculator included in the spreadsheet.

https://finpage.blog/2019/02/01/early-r ... -of-money/

And I've been working on the follow-up article, with more considerations about the expected rate of return and the spending gates. Here it is.

https://finpage.blog/2019/02/03/early-r ... ney-part-2

Feedback welcome.

https://finpage.blog/2019/02/01/early-r ... -of-money/

And I've been working on the follow-up article, with more considerations about the expected rate of return and the spending gates. Here it is.

https://finpage.blog/2019/02/03/early-r ... ney-part-2

Feedback welcome.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

thank you siamond

1210

1210

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

I was reading the December 2018 article "Building Portfolios for Households with Multiple Investment Goals" by Rudd & Siegel which goes over similar ground. They do a few things that are interesting, though.

They take a behavioural/mental accounts approach and propose three mental accounts:

Anyone that has heard the many arguments for/against "Liability Driven Investing" or bond-ladders has heard the idea of duration-matching future liabilities. If you have an expense due in 17 years, then buy a 17-year TIPS.

Rudd & Siegel introduce the idea of...

**Separate out liabilities into different mental accounts and apply different discount rates to each one.**They take a behavioural/mental accounts approach and propose three mental accounts:

- Necessary liabilities ("needs"): use the TIPS yield curve
- Target liabilities ("wants"): use expected real return for a 60/40 global portfolio
- Aspirational liabilities ("wishes"): use expected real return for a 100/0 global portfolio

**They mention (but don't really develop, unfortunately) that liabilities should be beta-matched and not just duration-matched**Anyone that has heard the many arguments for/against "Liability Driven Investing" or bond-ladders has heard the idea of duration-matching future liabilities. If you have an expense due in 17 years, then buy a 17-year TIPS.

Rudd & Siegel introduce the idea of...

Unfortunately, other than that intriguing sidenote, they don't really give any more advice or examples on how to do this in practice.The beta of the liability is, in principle, the extent to which the present value of the liability moves with the stock market; this can be nontrivial. (For example, after the crash of 2008 we found great bargains in luxury hotels; they are bargains no more. A lifestyle that involves staying in these hotels thus has a high beta.)

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

The VPW approach has many strengths. The boglehead summary and matrix, providing each year's suggested withdrawal percentage linked to ones age and portfolio composition, takes most (actually: virtually *all*) of the work out of it. Therefore, in my view the objection that VPW might be inappropriate for some as "too complicated" is not well taken. If anyone wishing to use VPW had to start from scratch and generate the matrix for themselves, maybe then it would be too complicated. Having to consult the already provided matrix once a year? Um, no. That's not too complicated.

The VPW element calling for an conversion of a portion of the portfolio to an immediate annuity at age 80 is also well worth considering. I doubt that "inflation adjusted" is a

Smectym

The VPW element calling for an conversion of a portion of the portfolio to an immediate annuity at age 80 is also well worth considering. I doubt that "inflation adjusted" is a

*sine qua non*for this product at age 80, however. Look into it, but if the market for the inflation-adjusted version is not robust, with several competing providers, it will likely be too expensive. Inflation protection is more important for longer time horizons than most 80-year-olds need overly concern themselves about.Smectym

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Siamond:siamond wrote: ↑Sun Feb 03, 2019 2:48 pmCool. I updated a bit the article by adding a short section at the end introducing the 'Simple Example' calculator included in the spreadsheet.

https://finpage.blog/2019/02/01/early-r ... -of-money/

And I've been working on the follow-up article, with more considerations about the expected rate of return and the spending gates. Here it is.

https://finpage.blog/2019/02/03/early-r ... ney-part-2

Feedback welcome.

I downloaded and began working with your spreadsheet. Perhaps I’m missing something, is it possible to move the SS and recurring input values to row 0? Will this have any effect on the calculations?

I’m retired and currently collecting both SS and a pension. Having those inputs begin 6-10 years out, isn’t applicable in my situation.

Is it as simple as copying the respective input values into the year 0 row?

Any help or insight is appreciated.

### Re: Time Value of Money vs. Variable Percentage Withdrawal (VPW)

Those '6-10 years out' inputs were just arbitrary examples. Yes, sure, if you're already collecting some form of fixed income, then start at row 0. One might argue that the value-added of the spreadsheet is less significant in such case than when planning ahead of such fixed income flows, but it will still work and could still provide interesting outputs.schachtw wrote: ↑Tue Jun 11, 2019 1:12 pmI downloaded and began working with your spreadsheet. Perhaps I’m missing something, is it possible to move the SS and recurring input values to row 0? Will this have any effect on the calculations?

I’m retired and currently collecting both SS and a pension. Having those inputs begin 6-10 years out, isn’t applicable in my situation.

Is it as simple as copying the respective input values into the year 0 row?

About your pension, I would encourage you to carefully study how it will adjust to inflation (I've seen a bunch of cases where the pension isn't fully inflation-adjusted, while SS income is). This by itself might make the use of the spreadsheet still valuable, even if you're already collecting all the fixed income you are going to get.

EDIT: this wasn't your question, but another reason to introduce income starting from row 0 is to model the case before retirement or as part of a transition. Case in point, my wife will keep working for a few years while I stopped (lucky me!). So I can use the income column to model such a time-limited cash flow case.