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]

Quote:

Originally Posted by Coachdavo 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]

Quote:

Originally Posted by mrfeh 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]

Quote:

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]

Quote:

Originally Posted by 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%

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]

Quote:

Originally Posted by 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.

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]

Quote:

Originally Posted by 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.

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]

Quote:

Originally Posted by pb4uski 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]

Quote:

Originally Posted by Coachdavo 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]

Quote:

Originally Posted by Dtail 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]

Quote:

Originally Posted by pb4uski 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]

Quote:

Originally Posted by Coachdavo 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]

Quote:

Originally Posted by Coachdavo ... 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]

Quote:

Originally Posted by OldShooter 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]

Quote:

Originally Posted by Niuatoputapu 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/ins...io-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.