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!
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.
12 Replies
Mathetes – I reviewed your Federal Tax workbook. Very impressive! Nicely documented and put together.
Since I am looking annually way out into the future, I’ve done a tax table for each year, estimating the annual change to the brackets. The 2025 and 2026 brackets are posted online, so for 2027, I just used the 2026 increase (3.98% - 2.28%, depending on bracket). I did the same for Standard Deduction and Medicare.
I’ve attached my tax table worksheet for 2025 for your review. I hard-coded the income max for each bracket because there only seven brackets – easy to do. And because I am single, I don’t bother with the other filing statuses, though it is a good idea and I may copy yours in the future.
Cell D2 actually pulls my Federal Taxable Income from another worksheet, but here, I've just hard-coded it.
I added 2025 brackets to your named range “BracketCutoffs”. For Single, your Input & Output worksheet calculates the same taxes due as my 2025 tax table worksheet. I am very glad to get your confirmation.
I couldn’t figure out an elegant formula to handle the 37% tax bracket, so I just set it at $2M. If you have a more elegant formula to handle a taxable income of “$640,601 and higher”, let me know (though I see that you set yours at $20M+).
My tax table shows how much of my taxable income goes into each bracket and the tax for that bracket’s income. Then at the bottom it shows total tax.
It also shows how much “wiggle room” I have in whichever is my highest tax bracket because for Roth conversions, I may wish to the income in my top bracket. In this case, since I’m already in the 24% bracket, I may wish to convert enough to add $4,481 of taxable income to take me right to the top of the 24% bracket.
Thanks for the educational exchange.
PS – on your Input & Output worksheet, I think cell G5 should read “...C23...”, not “...C32...”.
Thanks for your kind words regarding my spreadsheet. It was fun to create.
Yours is very simple and educational in its own right. I like that you show how much of one's final tax is actually taxed at the LOWER rate. And, yes, I'd encourage you to design it for other filing statuses as well, partly for the fun of making it more flexible, partly so you can share it with family and friends.
From how people often talk about desperately trying to avoid getting into a higher bracket, it would seem that many people have the mistaken notion that once they get into, say, the 32% bracket, all of a sudden everything is taxed at 32%, when in fact it's only whatever exceeds the lower end of the new "higher" bracket. So if you're $10 into the 32% bracket, you've really only added $3.20 to the tax bill; your take home is still $6.80 than it would have been had you been one cent below that cutoff. Your display dispels the false notion.
=========================
You wrote: If you have a more elegant formula to handle a taxable income of “$640,601 and higher”, let me know (though I see that you set yours at $20M+).
Look more closely at mine; you'll see that the simple "rule" I followed was putting the relevant year into the first four digits of that number. So, yes, it's $20M, but the next two digits are 21, 22, etc for tax years 2021, 2022, etc.
You wrote: PS – on your Input & Output worksheet, I think cell G5 should read “...C23...”, not “...C32...”.
Thanks for catching that, though it actually should be C33, the cell that has the formula.
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.
