Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Retirement Spreadsheet - Inflation Question
Old 04-22-2018, 05:55 AM   #1
Confused about dryer sheets
 
Join Date: Mar 2017
Location: Alden
Posts: 7
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 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
__________________

Coachdavo is offline   Reply With Quote
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 Early-Retirement.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!

Old 04-22-2018, 06:10 AM   #2
Recycles dryer sheets
 
Join Date: Jul 2013
Posts: 449
Quote:
Originally Posted by Coachdavo View Post
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.
__________________

mrfeh is offline   Reply With Quote
Old 04-22-2018, 06:16 AM   #3
Full time employment: Posting here.
flintnational's Avatar
 
Join Date: Mar 2008
Location: Atlanta Suburb
Posts: 651
Quote:
Originally Posted by mrfeh View Post
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
flintnational is offline   Reply With Quote
Old 04-22-2018, 09:50 AM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 17,872
+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
pb4uski is offline   Reply With Quote
Old 04-22-2018, 10:24 AM   #5
Recycles dryer sheets
 
Join Date: Apr 2013
Location: Fishers
Posts: 473
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 is offline   Reply With Quote
Old 04-22-2018, 10:26 AM   #6
Recycles dryer sheets
 
Join Date: Apr 2013
Location: Fishers
Posts: 473
Additionally we don't have the luxury of a 7 figure nest egg either.
wmc1000 is offline   Reply With Quote
Old 04-22-2018, 01:07 PM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
mickeyd's Avatar
 
Join Date: Apr 2004
Location: South Texas~29N/98W
Posts: 5,971
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.
__________________
Part-Owner 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.
mickeyd is offline   Reply With Quote
Old 04-22-2018, 04:53 PM   #8
Full time employment: Posting here.
 
Join Date: Jan 2018
Location: Tampa
Posts: 617
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
Dtail is offline   Reply With Quote
Old 04-22-2018, 06:15 PM   #9
Thinks s/he gets paid by the post
 
Join Date: Nov 2009
Posts: 4,112
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.
__________________
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!"
scrabbler1 is offline   Reply With Quote
Old 04-23-2018, 02:24 AM   #10
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 17,872
Quote:
Originally Posted by Dtail View Post
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
pb4uski is offline   Reply With Quote
Old 04-23-2018, 02:51 AM   #11
Confused about dryer sheets
 
Join Date: Mar 2017
Location: Alden
Posts: 7
Quote:
Originally Posted by wmc1000 View Post
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 is offline   Reply With Quote
Old 04-23-2018, 03:06 AM   #12
Confused about dryer sheets
 
Join Date: Mar 2017
Location: Alden
Posts: 7
Quote:
Originally Posted by scrabbler1 View Post
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.
Coachdavo is offline   Reply With Quote
Old 04-23-2018, 05:32 AM   #13
Full time employment: Posting here.
 
Join Date: Jan 2018
Location: Tampa
Posts: 617
Quote:
Originally Posted by pb4uski View Post
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
Dtail is offline   Reply With Quote
Old 04-23-2018, 05:34 AM   #14
Full time employment: Posting here.
 
Join Date: Jan 2018
Location: Tampa
Posts: 617
Quote:
Originally Posted by Coachdavo View Post
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
Dtail is offline   Reply With Quote
Old 04-23-2018, 07:30 AM   #15
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 17,872
Quote:
Originally Posted by Dtail View Post
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
pb4uski is offline   Reply With Quote
Old 04-23-2018, 08:00 AM   #16
Full time employment: Posting here.
 
Join Date: Jan 2018
Location: Tampa
Posts: 617
Quote:
Originally Posted by pb4uski View Post
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
Dtail is offline   Reply With Quote
Old 04-23-2018, 08:26 AM   #17
Recycles dryer sheets
Niuatoputapu's Avatar
 
Join Date: Nov 2014
Posts: 82
Quote:
Originally Posted by Coachdavo View Post

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.
__________________
ER'd 6/5/2015 at age 58, and DW targets ER in 6/2019
Niuatoputapu is offline   Reply With Quote
Old 04-23-2018, 08:57 AM   #18
Thinks s/he gets paid by the post
OldShooter's Avatar
 
Join Date: Mar 2017
Location: City
Posts: 1,546
Quote:
Originally Posted by Coachdavo View Post
... 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.


OldShooter is offline   Reply With Quote
Old 04-24-2018, 04:03 AM   #19
Confused about dryer sheets
 
Join Date: Mar 2017
Location: Alden
Posts: 7
Quote:
Originally Posted by OldShooter View Post
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!
Coachdavo is offline   Reply With Quote
Old 04-24-2018, 07:47 AM   #20
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 17,872
Quote:
Originally Posted by Niuatoputapu View Post
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.
__________________

__________________
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
pb4uski is offline   Reply With Quote
Reply


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

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inflation Protected Assets vs. Inflation arebelspy FIRECalc support 5 05-17-2011 11:19 AM
Inflation and Spreadsheet Results DocLarry FIRECalc support 0 09-19-2009 06:34 AM
Tracking basis with spreadsheet, how to structure the spreadsheet? bamsphd FIRE and Money 13 08-03-2009 09:31 AM
Implied inflation rate in an inflation adjusted SPIA cashflo2u2 FIRE and Money 6 04-30-2008 07:24 PM
Inflation or No Inflation? Donner FIRE and Money 16 01-19-2005 12:58 PM

 

 
All times are GMT -6. The time now is 02:08 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2018, vBulletin Solutions, Inc.