Forum Discussion
How to Account for Inflation
Attached I show an account balance forecast going out 20 years. But 20 years from now, $176,560 won't feel like $176,560 feels today. So how can I reflect a 3% annual inflation rate in each of the years?
Each year's balance is based on many "ins and outs" of money, so I can't just calculate using a rate of return less inflation.
Thanks in advance!
10 Replies
From what both of you are saying, I think I am better understanding the best way to look at this.
The "ins and outs" are assumptions about growth rate, monthly distributions, Roth conversions, taxes, and medicare expense; hence this simplified hypothetical example.
So maybe what I should be doing is simply comparing the year-end balance of the account to what the 2025 balance will be worth in the future, given inflation (3% in my example).
Using a simple formula for inflation (I've modified my attachment), my estimated 2044 balance is $176,560 against an inflation adjusted $198,134. Thus, my balance will be worth less, adjusted for inflation, than it is today.
Does that make more sense?
scrail2004 "Thus, my balance will be worth less, adjusted for inflation, than it is today. Does that make more sense?"
Well, yes. But on the other hand, common sense would have told you that much.
If I might offer an observation, I think a more useful spreadsheet (to the extent you want something that might help in planning or budgeting) would make visible--i.e., in columns dedicated to them--those "assumptions about growth rate, monthly distributions, Roth conversions, taxes, and medicare expense" rather than hiding them behind whatever it was you did to come up with the column B numbers.
The real spreadsheet I have, on which I based my first example for you, is actually something I use to project income and expenses in my retirement years. (I retired 23 years ago (am now in my early 80s.) Both my wife and I have (non-Roth) IRAs, from which we get the Required Minimum Distributions; we both get Social Security payments; occasional, minimal outside income; then we have our various living expenses. Each of these has a column in a spreadsheet; the spreadsheet has one row per year, going into the future; I have assumptions about Cost of living increases (AKA inflation), assumptions about investment growth, in the IRAs, assumptions about annual SocSec changes, etc. That spreadsheet has proven very useful in helping us know how much we need to tighten our belts, or whether or not we can afford a nice vacation trip......
I also have another workbook into which I enter (download) data from banks and credit cards, to track in detail AC:TUAL income and expenses by category.
So it's not clear to me where this spreadsheet you're creating fits into the bigger picture of your own financial planning, but there you have some possible things to consider.
True! Common sense did tell me that. But I want to graph the projected value of the IRA against an inflation projection, based on which I will adjust my distributions to maintain an inflation-adjusted target balance for my heirs. That was how this whole workbook started for me. Of course, it got more and more complicated as I delved into it.
You and I think very much alike. Below are the column headers of the worksheet I am working on. I've included most of the items you mention - IRA projected growth, distributions, conversions, taxes, etc. It calculates my taxes based each income source and projected changes in the Fed and CA tax brackets and deductions, Medicare premiums based on those brackets (and projections), and Social Security based on a growth projection.
The final column gives me my "net net income" - post-tax, post Medicare premium...the money on which we will actually live (and hopefully travel).
Going forward, I'll adjust the next year's distributions based on market performance to maintain the targeted inflation-adjusted IRA/Roth IRA balance.
PS - I also track expenses by category. :-)
Thanks Mathetes! I hope you had a great summer of kayaking!
Sorry, I attempted attaching an excel file. Not sure why it did not work. Let me know if it attaches this time.
My actual worksheet is very much like the one you attached, but my attachment is a simplified version.
Using the example you attached, I'm looking to apply inflation to each year's EOY Bal to answer the question, "what will be the buying power of each year's EOY Bal, given X percent inflation each year. In my attached, I reference a cell that is set to 3%, but can be changed, as you suggest.
Hi,
Try this formula in cell C4:
=B4 * BYROW(SEQUENCE(ROWS(A4:A23),, 0), LAMBDA(a, (1 + C1) ^a))Hope this helps.
IlirU
Thanks IlirU, but wouldn't inflation diminish the value of the balance each year, not increase it? My assumption is that in 2026, $114,910 is not going to buy as much as $114,910 would buy today. Because of inflation, it will buy what, say, $110,000 would buy today. And $176,560 is not going to buy as much in 2044 as it would buy today.
First of all, there's no attachment, so it's hard to visualize how you've laid this out, to say nothing of how you incorporate the various "ins and outs," how many "ins" or "outs" there are.
That said, I'm attaching one way to do it. You'll notice that I created a table off to the side, a table in which you can make different assumptions for different types of "ins and outs." You should always use a table like this, rather than doing what's called "hard coding," where you would put your 3% assumption into each formula where it applied. Using a table allows you to test assumptions, change scenarios, without revising the formulas; you just change an assumption.
This is a very simple example. You can enhance it to your heart's content.
Let me know if you have questions or need further clarification.
