Author Topic: Accounting for inflation in cashflow projection spreadsheets  (Read 877 times)

MisterA

  • Stubble
  • **
  • Posts: 156
  • Location: UK
Accounting for inflation in cashflow projection spreadsheets
« on: January 25, 2023, 05:37:34 AM »
This is pretty basic, but I don't see many (any) messages about it.

I have spreadsheets that keep track of my ongoing investment value, no problems. I also have a spreadsheet that looks forward to my cashflow after I've FIREd, and I'm not certain about the best way to account for inflation, looking years ahead. I have various income streams that are staged over years.
Obviously inflation affects your outgoings, and the effective growth (or decline!) of your stache.

Currently I estimate the forward growth of my funds as net growth after inflation. So for instance if annual inflation is 2% and my fund grows 6% in that year, my spreadsheet would only plot fund growth of 4%.
Because of this, I don't incorporate inflation on my outgoing expenses projections. What ever happens to my expenses due to inflation is offset due to my net stache growth.

Another example to illustrate how I work this:
2023 Food cost estimate, $500/m, inflation 2%, fund growth 6%
For my spreadsheet projection for 2024, I'd estimate
2024 Food cost estimate still $500/m, and I'd only grow my investments by 4% (then reduce the value of my stache by my withdrawals)

So I'm showing everything in 'todays' money even though I know there will be inflation, and inflation is ignored as it is offset by the net growth of my stache.

Does this make sense, or am I missing something???

maizefolk

  • Walrus Stache
  • *******
  • Posts: 7219
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #1 on: January 25, 2023, 06:54:18 AM »
It makes sense. For a snapshot in time to answer questions like "When can I retire?" I don't think you're missing anything.

If you're going to be running your model long term and comparing it to your actual expenses and changes in net worth eventually you'll want to incorporate inflation, at least retroactively. Because the inflation really does catch up with you.

I built my first investment/spending model in 2012. In today's dollars, my investments need to be 125% of what I projected my original "fire" number was in 2012. Similarly, if my expenses matched the numbers I used in 2012, it would mean I'd actually managed an addition 20% cut (in real terms) from what I was forecasting in 2012.

But I wouldn't worry about that sort of thing for at least 5-6 years.

GilesMM

  • Handlebar Stache
  • *****
  • Posts: 1154
  • Location: PNW
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #2 on: January 25, 2023, 07:27:28 AM »
Inflation is difficult to forecast.  If you have inflation-protected income (like SS), it doesn't matter. If you are relying on investments, they need to appreciate faster than inflation long term. Investment return is also hard to forecast.   If we have a long term recession (1929 style) and raging inflation, you could be in trouble for 15 years or more.


In your spreadsheet you should have both inflation and investment return as variables so you can see how things behave.  Set inflation at 5% and return at 3% and see what you think.  Not pretty.

patchyfacialhair

  • Handlebar Stache
  • *****
  • Posts: 1243
  • Age: 34
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #3 on: January 25, 2023, 08:06:36 AM »
I also use a real rate of return (nominal minus inflation) in my cash flow projections. Generally, I assume 7% nominal with 3% inflation for a 4% real return. Some might argue that it's too optimistic but at the end of the day...who cares...if it takes longer for me to early retire I'll still be retiring possibly decade(s) earlier than normal. If I never am able to retire despite a high savings rate...well there's more to worry about.

ChpBstrd

  • Walrus Stache
  • *******
  • Posts: 6229
  • Location: A poor and backward Southern state known as minimum wage country
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #4 on: January 25, 2023, 08:59:00 AM »
Operating in terms of today's dollars saves the trouble of projecting inflation, but a problem would arise if your cash flows from investment were not equal to your spending. In that event, the portfolio's value must change.

Example 1:
You earn $100k/year from dividends and capital gains that will rise with inflation. Assume this is perfectly linked to inflation.
You spend $50k/year.
Inflation is 5%.
Now income goes up by 5k but expenditures go up by only 2.5k. Your portfolio goes up.

Example 2:
You typically earn $50k/year from your portfolio and that amount will rise exactly with inflation.
You typically spend $100k/year.
Inflation is 5%.
Now your spending increases by $5k but your income only increases  by $2.5k. The portfolio depletion rate increases, reducing future earnings.

Example 3:
You typically earn $50k/year from your portfolio and this time let's assume it's not linked to inflation (e.g. a 100% nominal bond portfolio with no TIPS or Ibonds).
You typically spend $75k/year.
You draw $25k from Social Security, which is indexed to inflation.
Inflation is 5%.
Portfolio earnings increase zero.
Social security earnings increase $1.25k.
Spending increases $3.75k.
So your portfolio is depleted $2.5k due to the inflation (even worse, if the inflation caused rates to rise, you must raise that $2.5k by selling bonds that have likely depreciated).

I'm not saying you must guess the annual rate of inflation for each of the next 20 years, but it might be helpful to set up your spreadsheet so long-term  inflation is a variable you can change. You'd like to be able to try out 1%, 2%, 5%, and 8% scenarios and see how your plan's outcomes change. Doing this could reveal vulnerabilities, such as the way a 100% bond portfolio doesn't increase earnings in response to inflation and might force you to sell depleted bonds, or how the low-inflation scenario might look a lot like Japanification.

You could go so far as to input historical correlations, such as between stock total real returns and inflation (spoiler: it's basically zero) and bond total real returns (maybe something there!), and then model the outcomes under different inflation regimes. Or you could just let EarlyRetirementNow.com do the work for you:
Quote
On a standalone basis, even knowing the future 10-year inflation rate is useless!
Quote
we should certainly be worried about the future and choose a conservative safe withdrawal rate. But that has nothing to do with inflation, and has everything to do with lofty equity valuations!
Source: https://earlyretirementnow.com/2022/02/28/retirement-in-a-high-inflation-environment-swr-series-part-51/

MisterA

  • Stubble
  • **
  • Posts: 156
  • Location: UK
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #5 on: January 25, 2023, 02:14:06 PM »
Thanks guys, no huge concerns raised, which I'm pleased and relieved about.

In your spreadsheet you should have both inflation and investment return as variables so you can see how things behave.  Set inflation at 5% and return at 3% and see what you think.  Not pretty.
So a real return of -2%, my spreadsheet handles that just fine.

@maizefolk - thanks.
@patchyfacialhair -  :-)
@ChpBstrd - yes, I understand, thanks for taking time with the examples.

For my purposes, I think using a real rate of return (nominal minus inflation) gives me what I need. It's very difficult to estimate investment growth and inflation for >20 years, so I'll just stick with the easy method, for now.

Ron Scott

  • Pencil Stache
  • ****
  • Posts: 902
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #6 on: January 28, 2023, 12:52:01 PM »
After you get past estimating your general $$ level of expenses, predicting becomes like tea leaves and Tarot cards. Trying to predict future returns using historical data or inflation using god-knows-what, doesnít work, especially over longer periods of time like a retirement.

Iíve given up on predicting futures altogether and live on ďhopeĒ, that my 60-40ish portfolio can generate a long-term average real rate of return thatís at least 0, maybe positive.

vand

  • Handlebar Stache
  • *****
  • Posts: 2240
  • Location: UK
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #7 on: January 28, 2023, 01:10:43 PM »
There is a saying that you work on a basis of "returns you can eat" - ie, net of taxes, inflation and fees.

MisterA

  • Stubble
  • **
  • Posts: 156
  • Location: UK
Re: Accounting for inflation in cashflow projection spreadsheets
« Reply #8 on: February 03, 2023, 04:34:53 AM »
After you get past estimating your general $$ level of expenses, predicting becomes like tea leaves and Tarot cards. Trying to predict future returns using historical data or inflation using god-knows-what, doesnít work, especially over longer periods of time like a retirement.

Iíve given up on predicting futures altogether and live on ďhopeĒ, that my 60-40ish portfolio can generate a long-term average real rate of return thatís at least 0, maybe positive.
Yes exactly, that's where I'm at. If my portfolio just holds its own against inflation for 30 years, I'll be fine. If it doesn't, then everybody has a problem.

There is a saying that you work on a basis of "returns you can eat" - ie, net of taxes, inflation and fees.
Yes, that's what my estimations are based on, in so far as you can estimate anything over such a long time period.

 

Wow, a phone plan for fifteen bucks!