Retirement Spreadsheet - Inflation Question

[Coachdavo]

I have been working on my own spreadsheet for retirement. I am 51 so the spreadsheet goes from the saving stage through withdrawal stage. I know I am accounting correctly for inflation up to the withdrawal stage but I am struggling with investment return and inflation during the withdrawal stage.

Tab 1: Retirement Budget/Income. Input establishes the budget in today's dollars and uses inflation input to get it into future dollars. SS uses a modest COLA from today through retirement spending. Income taxes are worked in the budget.

Tab 2: Saving Calculator, pretty straightforward, uses a post inflation return percentage

Tab 3: Withdrawal Calculator. Pulls in budget, income and savings data and runs the spending modeling. Here is where I think I might be double-dipping on inflation.
- There is a cell for inflation % input.
- The required monthly budget is increased with inflation.
- There is a also a cell for portfolio return.
- I am currently subtracting inflation from the return% to get a net return %.
Question: If I am already accounting for inflation in my monthly budget calcs do I need it on the portfolio #'s as well? For example, I am using 5% return and 3% inflation so my net is 2%.

Sorry for the rookie question, I appreciate any input

 

[mrfeh]

Question: If I am already accounting for inflation in my monthly budget calcs do I need it on the portfolio #'s as well? For example, I am using 5% return and 3% inflation so my net is 2%.

No. If you do, you are doubling your expected rate of inflation.

 

[flintnational]

No. If you do, you are doubling your expected rate of inflation.

+1

 

[pb4uski]

+2 You're modeling spending on a nominal basis (inflated) but investments on a real basis (net of inflation... today's $$).... the whole model should be either one or the other... I prefer nominal.

If you are familiar with Quicken Lifetime Planner, the default analysis is real but you can also see the results on a nominal basis by going to the * at the upper right corner of the results and selecting "Show amounts in future value" or change it back by selecting "Show amounts in today's value".

 

[wmc1000]

Coachdavo, Your current method while not totally correct, does provide you an increased cushion as you are under inflating investments relative to inflation growth of expenses. In our spreadsheets I use an inflation factor of 4% for expenses while a growth rate of only 2% for investments purposely to minimize any shortfalls. If these numbers work out to age 95 I feel more confident in not outliving my funds. Doesn't mean it can't happen, just reduces the likelihood of having to worry about having to reduce expenses down the line. Of course we have never lived beyond our means and purposely reduced our expenses as much as we could about 3 years before RE.

 

[wmc1000]

Additionally we don't have the luxury of a 7 figure nest egg either.

 

[mickeyd]

Sorry for the rookie question, I appreciate any input

As an alternative strategy, I have never taken inflation into account because it varies so much and is so unpredictable in the long run. In the future when I take the money out to spend it, I will buy inflated-price goods with my inflated cash.

 

[Dtail]

Different twist on the inflation question.
If one has 2% inflation for 2017 (same % for each month of 2017), but is only retired for 6 months for example, is the "real return" on the investment portfolio calculated at
"nominal return/1.02% or
"nominal return/1.01%

 

[scrabbler1]

In my spreadsheet, I enter a separate inflation rate for my medical expenses (I.e. mostly health insurance premiums) and non-medical expenses. I enter vastly different amounts for the two and can play what-if games for each one to see how I am doing.

 

[pb4uski]

Different twist on the inflation question.
If one has 2% inflation for 2017 (same % for each month of 2017), but is only retired for 6 months for example, is the "real return" on the investment portfolio calculated at
"nominal return/1.02% or
"nominal return/1.01%

Wouldn't the real return be the nominal return MINUS inflation rather than divided by 1+inflation?

So if the nominal investment return is 8% and inflation is 2%, then the real return is 6%.... and the real return fo 6 months is [(1+6%)^0.5]-1 = 2.96%

 

[Coachdavo]

Coachdavo, Your current method while not totally correct, does provide you an increased cushion as you are under inflating investments relative to inflation growth of expenses. In our spreadsheets I use an inflation factor of 4% for expenses while a growth rate of only 2% for investments purposely to minimize any shortfalls. If these numbers work out to age 95 I feel more confident in not outliving my funds. Doesn't mean it can't happen, just reduces the likelihood of having to worry about having to reduce expenses down the line. Of course we have never lived beyond our means and purposely reduced our expenses as much as we could about 3 years before RE.

Thank you, I appreciate the comments. I do something similar with an age that we run out of money. I have a safe age of 100 set in the spreadsheet. I also run 2 Monte Carlo simulations (1 sigma, 2 sigma) with the mean set around the 10 year rolling S&P ave to get a confidence level on meeting my savings goal to enter into retirement. I would consider this part of the spreadsheet in a beta phase, but if it says 85% confidence level it makes me feel better

 

[Coachdavo]

In my spreadsheet, I enter a separate inflation rate for my medical expenses (I.e. mostly health insurance premiums) and non-medical expenses. I enter vastly different amounts for the two and can play what-if games for each one to see how I am doing.

Thanks. Insurance cost in retirement is the one figure in my budget that worries me. I need to do some research on the topic. I do like the idea of inflating health insurance separately from the rest of the expenses.

 

[Dtail]

Wouldn't the real return be the nominal return MINUS inflation rather than divided by 1+inflation?

So if the nominal investment return is 8% and inflation is 2%, then the real return is 6%.... and the real return fo 6 months is [(1+6%)^0.5]-1 = 2.96%

Thanks pb4uski.
I guess I was thinking of the real return more on a multiple year concept, but condensed to a partial year concept.
Thus using your example, if on an investment of 1,000,000, if one had a nominal return for 2 years of 8% and 2% inflation (let's say 8% total 2 year sum nominal and 2% inflation each year), wouldn't the real return value after 2 years be as follows:
(1,000,000*1.08)/1.04=1,038,462
Sorry for the confusion.

 

[Dtail]

Thanks. Insurance cost in retirement is the one figure in my budget that worries me. I need to do some research on the topic. I do like the idea of inflating health insurance separately from the rest of the expenses.

Currently, Fidelity uses 5.5% in their retirement calculator for medical expenses.

 

[pb4uski]

Thanks pb4uski.
I guess I was thinking of the real return more on a multiple year concept, but condensed to a partial year concept.
Thus using your example, if on an investment of 1,000,000, if one had a nominal return for 2 years of 8% and 2% inflation (let's say 8% total 2 year sum nominal and 2% inflation each year), wouldn't the real return value after 2 years be as follows:
(1,000,000*1.08)/1.04=1,038,462
Sorry for the confusion.

Yes, ignoring compounding as you have done. Though technically, if inflation is 2% each year then the denominator should be 1.0404 (1.02^2) rather than 1.04.

However, assuming 4% nominal growth annually and 2% annual inflation, the real rate of growth would be 2% and the value would be $1,040,400... $1m * (1+2%)^2.

I think you are best off/easiest to use an annual rate assumption and then compound it using exponents... greater than 1 for multiple periods and less than 1 for partial periods. Usually for a partial year the exponent is just less than 1 assuming that the rate assumption is an annual rate.

 

[Dtail]

Yes, ignoring compounding as you have done. Though technically, if inflation is 2% each year then the denominator should be 1.0404 (1.02^2) rather than 1.04.

However, assuming 4% nominal growth annually and 2% annual inflation, the real rate of growth would be 2% and the value would be $1,040,400... $1m * (1+2%)^2.

I think you are best off/easiest to use an annual rate assumption and then compound it using exponents... greater than 1 for multiple periods and less than 1 for partial periods. Usually for a partial year the exponent is just less than 1 assuming that the rate assumption is an annual rate.

Yes agree. I do appreciate your mathematically precision response.

 

[Niuatoputapu]

For example, I am using 5% return and 3% inflation so my net is 2%.

You and I are in the same neighborhood. My spreadsheet currently uses:

1) 4.2% average annual investment return for 60/40 portfolio
2) 2% inflation for SS and pension (neither yet started)
3) 3% inflation for non-medical living expenses
4) 5% inflation for medical expenses

The investment return is actually calculated as one year at a negative 9.02%, followed by four years of positive 9.02%, then repeated. Have not had portfolio fall 9% or more since 2008, so I just keep moving that loss prediction year forward. Easy way to have "actual" exceed "plan" most years.

 

[OldShooter]

... I also run 2 Monte Carlo simulations (1 sigma, 2 sigma) with the mean set around the 10 year rolling S&P ave to get a confidence level on meeting my savings goal to enter into retirement. ...

Sounds good. A couple of thoughts:

The S&P 500 represents only the US Large Cap sector. If your investments are limited to this sector, then using it in your model makes sense. If OTOH your investments are much broader (as IMO they should be) then something like the Russell 3000 or the ACWI may be more appropriate.

Again IMO, looking forward the SD on inflation may be higher than the SD on the S&P. For example, IIRC the last 30 years' inflation is around 2.6% with a pretty low SD but if you reach back 50 years, which adds the 70s/80s excitement, you get 4.5% and a bigger SD. So if you're playing Monte Carlo games I'd suggest that you do it with inflation as well. The big wild card on inflation is the (IMO likely) future decline of the dollar as the world's reserve currency and consequent decline in its value. This would ignite inflation that the Fed would have no power at all to stop. How to model this? I have no idea.

Worse, if there is a serious decline in the dollar the S&P will probably get exciting too. Companies with large export businesses may benefit. The Consumer Discretionary sector will tank and the Consumer Staples sector will at least be unhappy. So the S&P will probably be negatively correlated with inflation. How to model? Again, I have no idea.

​

 

[Coachdavo]

Sounds good. A couple of thoughts:

The S&P 500 represents only the US Large Cap sector. If your investments are limited to this sector, then using it in your model makes sense. If OTOH your investments are much broader (as IMO they should be) then something like the Russell 3000 or the ACWI may be more appropriate.

Again IMO, looking forward the SD on inflation may be higher than the SD on the S&P. For example, IIRC the last 30 years' inflation is around 2.6% with a pretty low SD but if you reach back 50 years, which adds the 70s/80s excitement, you get 4.5% and a bigger SD. So if you're playing Monte Carlo games I'd suggest that you do it with inflation as well. The big wild card on inflation is the (IMO likely) future decline of the dollar as the world's reserve currency and consequent decline in its value. This would ignite inflation that the Fed would have no power at all to stop. How to model this? I have no idea.

Thank you, Good points on the S&P, I am going to look a little closer at the best approach. I also thought of integrating some inflation modeling. I could do it in a separate spreadsheet but would have no clue how to cross the 2 data sets in the same spreadsheet. The irony is that if I could retire earlier I would have the time to figure it out!

 

[pb4uski]

You and I are in the same neighborhood. My spreadsheet currently uses:

1) 4.2% average annual investment return for 60/40 portfolio
2) 2% inflation for SS and pension (neither yet started)
3) 3% inflation for non-medical living expenses
4) 5% inflation for medical expenses

The investment return is actually calculated as one year at a negative 9.02%, followed by four years of positive 9.02%, then repeated. Have not had portfolio fall 9% or more since 2008, so I just keep moving that loss prediction year forward. Easy way to have "actual" exceed "plan" most years.

If your investment return is -9.02% followed by +9.02% for 4 years then the average return is more like 5.7% so where does the 4.2% come from?

I'm much less conservative. 6% investment return... historical investment return for a 60/40 portfolio is 8.7% (https://personal.vanguard.com/us/insights/saving-investing/model-portfolio-allocations) so I have haircut to 6% to be conservative. 2.7% inflation (long-term average) across all expenses so a 3.3% real rate of return.

 

[OldShooter]

Thank you, Good points on the S&P, I am going to look a little closer at the best approach. I also thought of integrating some inflation modeling. I could do it in a separate spreadsheet but would have no clue how to cross the 2 data sets in the same spreadsheet. The irony is that if I could retire earlier I would have the time to figure it out!

Unfortunately, changing the index to one that is a little more like your portfolio is the easiest but it is of almost trivial importance. Looking through the windshield to forecast inflation is huge and, of course, impossible. This is why we hold enough TIPS to cushion most possible scenarios. Let the government take the risk. I view the TIPS as insurance, not as investments. Any small loss in yield compared to alternatives is simply an insurance premium.

 

[joylesshusband]

The investment return is actually calculated as one year at a negative 9.02%, followed by four years of positive 9.02%, then repeated. Have not had portfolio fall 9% or more since 2008, so I just keep moving that loss prediction year forward. Easy way to have "actual" exceed "plan" most years.

Besides your math, this logic of yours seems faulty as well:

Based on it, and on the fact that you've never died before, you should safely conclude that you are immortal...

 

[scrabbler1]

I mentioned earlier how I include inflation in my longer-term spreadsheet (separate rate for medical versus non-medical expenses). On the income side, I assign a separate rate of return for each of the mutual funds I own (4 in taxable, 2 in the rollover IRA). For the bond funds, I look at only the monthly dividends, not any change in the value of the funds or any of the usually tiny, if any, cap gain distributions. For the stock funds, I estimate the sum of the 4 quarterly dividends (per share) distributions and multiply that by the number of shares I own. I then assume I will reinvest cap gain distributions so I build that back into each fund's share balance for the following year. Similarly, I build back any yearly excess of dividends over expenses into additional shares of my main bond fund for the following year. If I project a cash shortfall, I take that shortfall from the value of one of the other bond funds.


The IRA has automatic reinvestment of all distributions (this spreadsheet goes only up through age 60, the critical years of my ER plan) which simplifies things there.


Every year, I replace any projected numbers with actual ones. I can also update any projected rates of return on the bond funds as well as dividends per share for the stock funds.

 

[free2020]

Sounds good. A couple of thoughts:

The S&P 500 represents only the US Large Cap sector. If your investments are limited to this sector, then using it in your model makes sense. If OTOH your investments are much broader (as IMO they should be) then something like the Russell 3000 or the ACWI may be more appropriate.

Again IMO, looking forward the SD on inflation may be higher than the SD on the S&P. For example, IIRC the last 30 years' inflation is around 2.6% with a pretty low SD but if you reach back 50 years, which adds the 70s/80s excitement, you get 4.5% and a bigger SD. So if you're playing Monte Carlo games I'd suggest that you do it with inflation as well. The big wild card on inflation is the (IMO likely) future decline of the dollar as the world's reserve currency and consequent decline in its value. This would ignite inflation that the Fed would have no power at all to stop. How to model this? I have no idea.

Worse, if there is a serious decline in the dollar the S&P will probably get exciting too. Companies with large export businesses may benefit. The Consumer Discretionary sector will tank and the Consumer Staples sector will at least be unhappy. So the S&P will probably be negatively correlated with inflation. How to model? Again, I have no idea.

​

Nice quote from Taleb. Fooled by randomness is another great book of his. What struck me was just how pervasive the foolishness is in everyday life. He is a genius.

What will really serve this group well is their conservatism and frugality. More than precise modeling. I too have a spreadsheet similar to the OP since 2009 and upon annual review versus the model, the one thing that is constant is how wrong I’ve been towards the conservative side. The model still helps but it’s less and less utility for me. It’s almost like the need for it was based on self doubt that we didn’t have enough but it is clearer that we likely will have more than enough. Taleb’s turkey gives me pause though.