My Lessons Learned and why everyone should build a custom model with Excel

[Romer]

The total value of my liquid assets. It sums up my accounts; IRA, 401K, Roth, Savings, CDs

so it plots the value of my money over time against the age at the bottom for the 3 scenarios

SS at 67 with no Roth Conversions
SS at 67 with Roth conversions to top of 22% Tax Bracket
SS at 70 with Roth conversions to top of 22% tax bracket

All plotted using the same starting expense escalated every year. Includes a calculation of federal and state taxes as part of expense. Includes RMDs in Tax for expense and unused post tax RMDs go into after tax savings

 

[Out-to-Lunch]

The total value of my liquid assets. It sums up my accounts; IRA, 401K, Roth, Savings, CDs

Just trying to understand: You simply sum the dollar amounts in these different accounts, making no attempt to assess the embedded tax liabilities on them? So, immediately after making a Roth conversion, the sum that you would calculate would be lower than just before the conversion?

 

[chassis]

BS alert: embedded tax liabilities are a red herring.

If one accounts for so called embedded tax liabilities then also one must include future entitlements. This means the present value of SS, pension and other sources need to be accounted for as assets.

Embedded tax liabilities on the balance sheet are a red herring. Skip that thought, ignore it.

 

[Romer]

Just trying to understand: You simply sum the dollar amounts in these different accounts, making no attempt to assess the embedded tax liabilities on them? So, immediately after making a Roth conversion, the sum that you would calculate would be lower than just before the conversion?

Fair point. In my details they are separate. So I take money out of the IRA I include taxes as an expense

I believe you are saying for example $500K of IRA is not the same as $500K of Roth as the IRA also has embedded tax liabilities

The graph just tracks total dollar value left to show me when or if I would run out of money. That was my only objective with it

In the graph below for example
For the No ROTH option the IRAD slowly goes to zero due to RMDs with excess RMDs going into savings, BUT I would have to live until 110 for that to happen.
More realistically at 85, there is 55% in Roth and 45% in after tax investments. Your point I believe is that the 55% of IRA is worth 26% less with federal and state tax applications

Did I capture your comment correctly?

 

[sengsational]

One thing about complex models is that there can be calculations that the modeler thinks are doing one thing, but they're doing something else. Back when I was obsessing over this kind of thing more often, I had all my assumptions defined, then build a version of my model that tried to replicate a simpler online model. Let me tell you, it was hard to replicate anything, because even if they tell you how it's supposed to be working, and lay out their assumptions, there's still methodological choices undisclosed.

The thing I came away with was if one follows a few principles, you can get most of the benefit. Also, the difference between winging it and putting the finest point on it as possible might not amount to much. That's not to say modeling isn't worth it...it is. It's just to say you should probably enjoy modeling if you do a lot of it. I used to enjoy it more than I do lately.

 

[pb4uski]

Late to this thread. My basic retirement planning was with Quicken Lifetime Planner and I think it is a great first step for most people because it is easy to use, reasonably flexible, and covers a lot of bases. For those who are not fluent in Excel, and even those that are, it is a great tool. It also includes a What-If function to compare scenarios with different assumptions that is useful. Its weaknesses are that it is deterministic rather than stochastic and that taxes are not particularly sophisticated (a assumed tax rate only), but it is a great basic retirement planning tool.

I then supplemented the basic plan in QLP by running FIRECalc and other stochastic planners using the same assumptions as in QLP (or as near as I can get) to stress test for SORR. Now having been retired for 10 years, I don't bother to do that as often anymore.

I like opensocialsecurity.com for looking at claiming strategy optimization because that tool includes both mortality (the probability of your or your spouse being alive to receive your SS benefit) as well as discounting for the time value of money (don't forget to use only real rates of return as an input).

Before I retired I had an Excel model that essentially did a lot of what QLP does but I could see the results easier.

Since I retired, I built a projection model to assess Roth conversions that has an embedded tax calculation included in it similar to what the OP describes. I have tax brackets that inflate with an inflation assumption (I use the Fed's target of 2%). I use todays tax brackets but can adjust rates to include reversion to pre-2017 tax rates, etc.

The one thing that I have included that I didn't see mentioned is that I have separate tax calulations for ordinary income and for preferenced income (qualified dividends and LTCG).

 

[Romer]

One thing about complex models is that there can be calculations that the modeler thinks are doing one thing, but they're doing something else. Back when I was obsessing over this kind of thing more often, I had all my assumptions defined, then build a version of my model that tried to replicate a simpler online model. Let me tell you, it was hard to replicate anything, because even if they tell you how it's supposed to be working, and lay out their assumptions, there's still methodological choices undisclosed.

The thing I came away with was if one follows a few principles, you can get most of the benefit. Also, the difference between winging it and putting the finest point on it as possible might not amount to much. That's not to say modeling isn't worth it...it is. It's just to say you should probably enjoy modeling if you do a lot of it. I used to enjoy it more than I do lately.

I agree completely. I do enjoy it as it forces me to learn how things all play together. I keep finding things I missed or need to revise. However, I do not expect my model to be reality, just a planning tool to be updated periodically based on actual performance to help make decisions for the next year. I don't expect it to be prefect, but it does make me feel I am putting in the due diligence to make a good decision (Engineer in me). And if things turn out differently, as Yogi Berra says "it is what it is"

 

[pb4uski]

BS alert: embedded tax liabilities are a red herring.

If one accounts for so called embedded tax liabilities then also one must include future entitlements. This means the present value of SS, pension and other sources need to be accounted for as assets.

Embedded tax liabilities on the balance sheet are a red herring. Skip that thought, ignore it.

^^^ That is BS. There is nothing that says that if you include deferred taxes in your planning that you "must" include future entitlements like SS or pensions.

In fact, the professional accounting standards for personal financial statements... what you would need to follow if you were having personal financial statements opined on by a CPA firm... would require deferred income taxes but prohibit recognition of assets for SS or pensions since the receipt of those cash flows are life contingent (though that last part is controversial that is wher they landed back in the 70s and personal financial statement is such a narrow area of practice that it has never been revisited by the standard setters).

I don't include any of those in my measurement of net worth, more due to simplicity. I could easily include deferred income taxes but the difficulties in assessing the right tax rate to use is problematic enough that it isn't worth the effort.

For me, an individual can decide for themselves what to include or not, just like a company can decide what to include or not in their management financial statements (and many do).

 

[pb4uski]

... More realistically at 85, there is 55% in Roth and 45% in after tax investments. Your point I believe is that the 55% of IRA is worth 26% less with federal and state tax applications

Did I capture your comment correctly?

Not O-T-L, but yes, you captured the idea correctly... basically there is an embedded tax liability aka deferred income tax liability or DTL for things like tax-deferred retirement money because it will be taxed when withdrawn... so you'll have less than $1 to spend for each $1 withdrawn whereas for money in a roth if you withdraw $1 you have $1 available to spend.

In your taxable income calculation are you including only 85% of SS? That would be an easy thing to miss. I didn't bother to build a calculation of taxable SS in my model but I just assumed that 85% of SS would be taxable across the board given the level of our income.

 

[Romer]

For me, an individual can decide for themselves what to include or not, just like a company can decide what to include or not in their management financial statements (and many do).

Gentleman, there is no right answer. No BS here. What everyone is looking for and wants to see in a graph and tool is different

My only goal is making sure I have a viable conceptual plan to not run out of money. I considered tax implications as I moved money between account but not of the accounts themselves.

Others want something else out of the tool and can make their own assumptions based on what they want to see

Then as some point out, the results in later years are all BS since so much is out of our control. Its just a tool not a high reliable predictive analysis like one would use to make sure they are able to reach the orbit of Mars

 

[Romer]

In your taxable income calculation are you including only 85% of SS? That would be an easy thing to miss. I didn't bother to build a calculation of taxable SS in my model but I just assumed that 85% of SS would be taxable across the board given the level of our income.

I am only including 85% for SS and also the standard deduction of $24500

 

[pb4uski]

I am only including 85% for SS and also the standard deduction of $24500

Do you inflate the standard deduction and then bump it up it when you each reach age 65? I increase the standard deduction usng the same assumption as the increase in tax brackets.

For 2021 the base standard deduction for MFJ is $25,100 and it increases $1,350 for each person 65 or older.

 

[corn18]

One thing about complex models is that there can be calculations that the modeler thinks are doing one thing, but they're doing something else. Back when I was obsessing over this kind of thing more often, I had all my assumptions defined, then build a version of my model that tried to replicate a simpler online model. Let me tell you, it was hard to replicate anything, because even if they tell you how it's supposed to be working, and lay out their assumptions, there's still methodological choices undisclosed.

The thing I came away with was if one follows a few principles, you can get most of the benefit. Also, the difference between winging it and putting the finest point on it as possible might not amount to much. That's not to say modeling isn't worth it...it is. It's just to say you should probably enjoy modeling if you do a lot of it. I used to enjoy it more than I do lately.

x2

My spreadsheet gets out of control sometimes. That’s when I go back and eliminate some of the 999 assumptions and what if’s I have made and get it back to simple. At the end of the day, I have income and expenses. My savings has to cover the difference. Once I saw the simplicity of this approach, my model became a lot simpler.

 

[Out-to-Lunch]

Fair point.
Did I capture your comment correctly?

Yes, I believe you did, and @PB4uski's comments did capture my thoughts. Thank you.

BS alert: embedded tax liabilities are a red herring.

Well, we have been polite on the differences between our viewpoints, but I do take umbrage at calling my view "bullshit."

Let's consider a parable:

Two identical twins, Adam and Brian, have lived identical financial lives, and they each have $1,000,000 in a traditional IRA as of Monday. Due to other income, they are in the 22% tax bracket.

On Tuesday, Brian decides to convert some money to a Roth IRA, so he distributes $64k from his tIRA, has $14k tax withheld, and deposits $50 in the Roth.

Wed:
Adam: $1,000k tIRA; $0 Roth; $1000k total
Brian: $936k tIRA; $50k Roth; $986 total (Hmmm, is Brian poorer than Adam?)

On Thursday, the twins each decide to splurge and buy a new Cadillac for their upcoming 60th birthday. Each costs $50k. Adam takes the funds from his tIRA, and Brian uses the Roth funds. Adam withdraws $64k and has $14k tax witheld.

Friday:
Adam: $936k tIRA; $0 Roth; $936k total + a Caddy.
Brian: $936k tIRA; $0k Roth;$936k total + a Caddy.

I think you would agree that the Roth conversion did not harm Brian’s financial standing, yes?

Gentleman, there is no right answer. No BS here. What everyone is looking for and wants to see in a graph and tool is different

My only goal is making sure I have a viable conceptual plan to not run out of money. I considered tax implications as I moved money between account but not of the accounts themselves.

I certainly agree with your sentiments here. I will perhaps quibble that you say that your "only goal" was to establish a viable plan, but then you seemed to use your spreadsheet upthread to decide on the advisability of Roth conversions (without taking into account all the implications).

 

[Romer]

Do you inflate the standard deduction and then bump it up it when you each reach age 65? I increase the standard deduction usng the same assumption as the increase in tax brackets.

For 2021 the base standard deduction for MFJ is $25,100 and it increases $1,350 for each person 65 or older.

I havent done that although I did raise the brackets by the average they have changed the last few years. That would create even more margin. Thats a good point to raise if someone wanted to consider that

 

[Romer]

I certainly agree with your sentiments here. I will perhaps quibble that you say that your "only goal" was to establish a viable plan, but then you seemed to use your spreadsheet upthread to decide on the advisability of Roth conversions (without taking into account all the implications).

I understand your perspective and I could have dove deeper. There certainly are other factors to consider.

My objective was to find in which scenario my money lasts the longest. As money comes out of the IRA or is converted into a Roth the tax implications of those transactions are considered. That allows me to see the conversion impacts on how long money lasts. Once the money goes to zero there are no tax implications

To your point I did not consider the future tax implications of the money remaining in the IRA vs the Roth. Although certainly a factor, I did not see it required for the how long exercise. If I dive deeper, and I likely will once I am retired, to define the next year option for Roth vs no Roth looking at long term factors I will include that. Thanks for the comment, I do appreciate it

 

[corn18]

I havent done that although I did raise the brackets by the average they have changed the last few years. That would create even more margin. Thats a good point to raise if someone wanted to consider that

This is where planning in nominal dollars really simplifies things. You don't need to inflate the tax brackets or standard deductions.

If you dig really deep into taxes on SS, you will find the "standard" deduction from SS earnings is not indexed to inflation, so you will have to account for that if planning in nominal dollars. This is a sneaky tax increase on SS earnings that happens every year.

 

[pb4uski]

^^^ I think you have it backwards... if you plan in nominal dollars then you should include inflation and use nominal rates of return but if you plan in real dollars then you can ignore inflation and use real rates of return.

I find nominal easier because that is what will be reflected in my accounts, tax calculations, etc. I don't mind factoring in inflation as an assumption.

 

[TheWizard]

I'm a retired engineer similar to a few others here.
I never had a budget or bothered to track expenses back in my working years. But I had a decent income, saved a lot in tax-deferred off the top, and wasn't a spendaholic, so it worked out.

As I got close to retirement, the forums I read recommended recommended getting a handle on your "expenses". So I put together a detailed spreadsheet, updated many times over a period of several weeks.
I focused mainly on Basic Expenses, since discretionary travel expenses would hopefully be a lot higher in retirement.

Turned out my Basic Expenses were a modest fraction of my pre-retirement income, so I just decided: let's just aim for the same Net Monthly Income in retirement as when working, meaning that my checking account would hardly know the difference.

So I did that and my AGI and resulting income taxes have been higher than my employment years each year since retiring in 2013.

Now I do maintain a different spreadsheet in retirement to project and manage my AGI. This comes down mainly to figuring how much to Roth convert to get AGI up close to but not over the next higher Medicare IRMAA tier. I don't try to estimate income taxes or growth of my investments in this spreadsheet, just my AGI.

I do my own income taxes each year, so I know my taxes for this year will be just a bit more than last year, provided I don't do something silly and get a big jump in AGI...

 

[Romer]

I'm a retired engineer similar to a few others here.
I never had a budget or bothered to track expenses back in my working years. But I had a decent income, saved a lot in tax-deferred off the top, and wasn't a spendaholic, so it worked out.

As I got close to retirement, the forums I read recommended recommended getting a handle on your "expenses". So I put together a detailed spreadsheet, updated many times over a period of several weeks.
I focused mainly on Basic Expenses, since discretionary travel expenses would hopefully be a lot higher in retirement.

Turned out my Basic Expenses were a modest fraction of my pre-retirement income, so I just decided: let's just aim for the same Net Monthly Income in retirement as when working, meaning that my checking account would hardly know the difference.

So I did that and my AGI and resulting income taxes have been higher than my employment years each year since retiring in 2013.

Now I do maintain a different spreadsheet in retirement to project and manage my AGI. This comes down mainly to figuring how much to Roth convert to get AGI up close to but not over the next higher Medicare IRMAA tier. I don't try to estimate income taxes or growth of my investments in this spreadsheet, just my AGI.

I do my own income taxes each year, so I know my taxes for this year will be just a bit more than last year, provided I don't do something silly and get a big jump in AGI...

Sounds like you know what needs to be done

The one thing this last discussion has me thinking about is I do have a good amount of savings that could fully meet my needs for the first couple of years

I had been taking expenses out of Roth conversions where as I could just make it 100% Roth

or I could look at stretching it to be in the 12% tax bracket for the first few years of retirement

Likely will go the Roth conversion for at least year one and see how that goes

also need to consider since I am retiring in January and I will get a Vacation and Bonus payout how that all impacts year one. It likely means year 2 and 3 could be off of savings for expenses along with my pension

BUT, I also need to make sure I have sufficient cash or safe funds in case of a market downturn. I could do that within the IRA though

There is always something more to look at Always something more to learn

 

[chassis]

@Out-to-Lunch when you get back from lunch you will likely recall the many threads on this site about Roth conversions. Romer has charted quite nicely the impoverishing effect of Roth conversions the “morning after” they are taken. There are many long winded threads on this.

On the specific topic which lit the BS meter light for me, is future tax liabilities. Hogwash. Do you carry as an asset on your balance sheet the present value of all future income such as SS, pension, RE rent, etc? Do you also carry the present value of all future liabilities such as sales tax on everything you might possibly purchase, all property tax, in fact any expense you might incur during your remaining time on Earth? I don’t.

Romer and others treat tax in the year it is payable. To wit: after income has been realized. This is handled on the income statement.

 

[Out-to-Lunch]

@Out-to-Lunch when you get back from lunch you will likely recall the many threads on this site about Roth conversions. Romer has charted quite nicely the impoverishing effect of Roth conversions the “morning after” they are taken. There are many long winded threads on this.

Yes, we have hashed this over before, but you continue to assert that Roth conversions "impoverish" the holder. Did you read the parable? Did you find any flaws in it?

On the specific topic which lit the BS meter light for me, is future tax liabilities. Hogwash. Do you carry as an asset on your balance sheet the present value of all future income such as SS, pension, RE rent, etc? Do you also carry the present value of all future liabilities such as sales tax on everything you might possibly purchase, all property tax, in fact any expense you might incur during your remaining time on Earth? I don’t.

You know, you have asked me these questions before, and I have answered them. I doubt you have memory problems, so I am beginning to sense that your questions are not earnest.

It is okay for you to not want to do Roth conversions. It is okay for you to argue against them. It is okay for you to point out their limitations, as it is okay for others to point out circumstances where they are advantageous. It is not okay to call the arguments of others "bullshit."

 

[RunningBum]

Yes, we have hashed this over before, but you continue to assert that Roth conversions "impoverish" the holder. Did you read the parable? Did you find any flaws in it?




You know, you have asked me these questions before, and I have answered them. I doubt you have memory problems, so I am beginning to sense that your questions are not earnest.

It is okay for you to not want to do Roth conversions. It is okay for you to argue against them. It is okay for you to point out their limitations, as it is okay for others to point out circumstances where they are advantageous. It is not okay to call the arguments of others "bullshit."

At some point it is just not worth responding to someone who ignores any clear proof (like your twins example a few posts back) and just repeats some meaningless requirements. But that's your call. I've already decided. And if someone want to follow their bad advice, that's on them.

 

[Out-to-Lunch]

At some point it is just not worth responding to someone who ignores any clear proof (like your twins example a few posts back) and just repeats some meaningless requirements. But that's your call. I've already decided. And if someone want to follow their bad advice, that's on them.

That is a good question, RB. I want to state a couple of things, mostly for Romer's benefit. He started this thread to give info and get feedback on his modeling. I didn't particularly wish to turn his thread into (yet another) Roth conversion thread. But it appeared to me that Romer may have been overlooking the point (hashed out above) about the relative value of Roth, tIRA, and taxable funds, and I wanted to point it out to him for him to consider. Having other people give their viewpoints, which may not agree with mine, is part of that package, if done respecfully.

My purpose in participating in this forum is to learn things AND to share information and perspectives. In other threads, I have been pretty clear about what my purpose is in making Roth conversions (mostly protecting my DW after I kick, keeping us in a lower IRMAA tier, and perhaps a bit of tax-rate arbitrage). I mention conversions a lot, but it is not like I think they are a panacea or that everyone should do them. I happen to be in a position where they will (likely) benefit me (and my DW later). And I am happy to continue to learn, to possibly reassess that decision if new information comes to light. I like seeing the pros and cons of that strategy discussed, but I don't like it when they are misrepresented.

 

[hmango]

On the FireCalc's website under Resources it mentions a book Nest Egg Care by Tom Canfield (below is an excerpt). His book is a great resource and has many excellent ideas. Something to consider for your own spreadsheet.

A Masters Thesis on FIRECalc?
If FIRECalc is the tool that lets you do a bunch of research, Tom Canfield's recent (2017) Nest Egg Care is the book on what to think about and look at, how to plan, and how to focus on fun and joy in retirement and giving to those you care about. Highly recommended!

See Tom's website at www.nesteggcare.com