

Retirement Spreadsheet  Inflation Question
04222018, 06:55 AM

#1

Dryer sheet wannabe
Join Date: Mar 2017
Location: Buffalo
Posts: 11

Retirement Spreadsheet  Inflation Question
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 doubledipping 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
__________________




Join the #1 Early Retirement and Financial Independence Forum Today  It's Totally Free!
Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that EarlyRetirement.org is a great community to join. Best of all it's totally FREE!
You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

04222018, 07:10 AM

#2

Full time employment: Posting here.
Join Date: Jul 2013
Posts: 737

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.
__________________



04222018, 07:16 AM

#3

Thinks s/he gets paid by the post
Join Date: Mar 2008
Location: Atlanta Suburb
Posts: 1,350

Quote:
Originally Posted by mrfeh
No. If you do, you are doubling your expected rate of inflation.

+1
__________________
"Oh, twice as much ain't twice as good
And can't sustain like one half could
It's wanting more that's gonna send me to my knees"  John Mayer



04222018, 10:50 AM

#4

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 23,402

+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".
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
Retired Jan 2012 at age 56...60/35/5 AA



04222018, 11:24 AM

#5

Full time employment: Posting here.
Join Date: Apr 2013
Location: Fishers
Posts: 757

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.



04222018, 11:26 AM

#6

Full time employment: Posting here.
Join Date: Apr 2013
Location: Fishers
Posts: 757

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



04222018, 02:07 PM

#7

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Apr 2004
Location: South Texas~29N/98W
Posts: 6,251

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 inflatedprice goods with my inflated cash.
__________________
PartOwner of Texas
Outside of a dog, a book is man's best friend. Inside of a dog, it's too dark to read. Groucho Marx
In dire need of: faster horses, younger woman, older whiskey, more money.



04222018, 05:53 PM

#8

Thinks s/he gets paid by the post
Join Date: Jan 2018
Location: Tampa
Posts: 4,570

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%
__________________
TGIM



04222018, 07:15 PM

#9

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Nov 2009
Posts: 5,193

In my spreadsheet, I enter a separate inflation rate for my medical expenses (I.e. mostly health insurance premiums) and nonmedical expenses. I enter vastly different amounts for the two and can play whatif games for each one to see how I am doing.
__________________
Retired in late 2008 at age 45. Cashed in company stock, bought a lot of shares in a big bond fund and am living nicely off its dividends. IRA, SS, and a pension await me at age 60 and later. No kids, no debts.
"I want my money working for me instead of me working for my money!"



04232018, 03:24 AM

#10

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 23,402

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%
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
Retired Jan 2012 at age 56...60/35/5 AA



04232018, 03:51 AM

#11

Dryer sheet wannabe
Join Date: Mar 2017
Location: Buffalo
Posts: 11

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



04232018, 04:06 AM

#12

Dryer sheet wannabe
Join Date: Mar 2017
Location: Buffalo
Posts: 11

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 nonmedical expenses. I enter vastly different amounts for the two and can play whatif 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.



04232018, 06:32 AM

#13

Thinks s/he gets paid by the post
Join Date: Jan 2018
Location: Tampa
Posts: 4,570

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.
__________________
TGIM



04232018, 06:34 AM

#14

Thinks s/he gets paid by the post
Join Date: Jan 2018
Location: Tampa
Posts: 4,570

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.
__________________
TGIM



04232018, 08:30 AM

#15

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 23,402

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.
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
Retired Jan 2012 at age 56...60/35/5 AA



04232018, 09:00 AM

#16

Thinks s/he gets paid by the post
Join Date: Jan 2018
Location: Tampa
Posts: 4,570

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.
__________________
TGIM



04232018, 09:26 AM

#17

Recycles dryer sheets
Join Date: Nov 2014
Posts: 115

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 nonmedical 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.
__________________
ER'd 6/5/2015 at age 58. DW targets R in June of a future year.
Planned WR before my SS (20212026) is 56%, then start SS and 4% WR at age 70 (2027)



04232018, 09:57 AM

#18

Thinks s/he gets paid by the post
Join Date: Mar 2017
Location: City
Posts: 3,651

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.



04242018, 05:03 AM

#19

Dryer sheet wannabe
Join Date: Mar 2017
Location: Buffalo
Posts: 11

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!



04242018, 08:47 AM

#20

Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 23,402

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 nonmedical 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...ioallocations) so I have haircut to 6% to be conservative. 2.7% inflation (longterm average) across all expenses so a 3.3% real rate of return.
__________________
__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
Retired Jan 2012 at age 56...60/35/5 AA





Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


Thread Tools 
Search this Thread 


Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off




» Recent Threads













» Quick Links


