Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Retiree Portfolio Model

Post by BigFoot48 »

This is a portfolio forecasting Excel spreadsheet model designed for a person or couple nearing or in retirement. See the Boglehead Wiki article for more information: http://www.bogleheads.org/wiki/Retiree_Portfolio_Model

Dec 25 - version 24.0: https://dl.dropbox.com/scl/fi/e4eigp318 ... 3dvf5&dl=1

NOTE: Correction to Clear Entries made to 24.0 on 12/25.
NOTE: Non-working macros removed from Setup page to 24.0 on 12/26.


2024 Updates:
- Updated Federal tax factors, Medicare rates, escalation factors, etc. to 2024 amounts. Inflation rates for Social Security, CPI-U, etc.
- Added macros to copy default allocation data, mainly for those using the 2021 "maintain allocation rates" feature which don't use Tax Exempt and Money Market rates.
- Added macro to show/hide the Asset Allocation Summary page. This page has all the details of the allocation but is likely to be rarely viewed. Default is "hidden".
- Expanded Life Expectancy Table III to start at age 30, ending at 109, for pre-pre-retirees using RPM.
- Tested macro links, checked cell comments, ran spellchecker, etc. etc.

Dec 4 version 23.4: https://dl.dropbox.com/scl/fi/7y6r9rxfz ... 3eyuo&dl=1
Most users may want to wait until the 2024 version in released to update.

This update has many changes as suggested by users over the past few months: 1) Rearranged data on Results page to make it easier to study. thor. 2) IRMMA added to Results. smxnhe23. 3) An additional 20 years added to Uniform Life Expectancy Table III used in calculation of RMD. drownlearning. 4) Added Long Term Capital Gains to State Income Taxes. Settings in Tax Section. Larry. '5) Expenses as percent of beginning of year account balances added to Results, along with a graph chart. DebiT. 6) Added macro-buttons to Setup: 3. Return Rates and Allocation to hide or show the extensive AASummary page that will likely not be referenced frequently.

Most users may want to wait until the 2024 version in released in January to update.

July 18 version 23.2: Fixed formulas for IRA2 RMD Beneficiary in Details/Base line 101 (summary line 102), causing double-counting of IRA1"s Beneficiary RMD. Thanks to BH Pamina2.

July 5 fix of Dropbox download problem: the "dl=0" at the end of the download links must be replaced with dl=1 and "www" changed to "dl" to make it work. (Per Dropbox As of July 6).

23.1 updated May 22 SS End Age calculation when current ages are identical.. Thanks to BH DHBS for noticing and providing the correction. Few users will need this update.

23.1 updated April 23 Changed Example data RMD starting ages to 73, matching current law. Was 72. Users will be supplying their own data so no need to update for this fix.

23.1 updated March 2 Fixed a problem in the first two years of RMD calculations for those using the Spouse Age Younger Than 10 Years table. Few users will need this update.

23.1 updated March 1 Corrected totals in Federal Taxes By Bracket schedule on Setup to include NIIT amounts. Thanks to sdwaterman for reporting it. No need for users to update unless they have NIIT taxes and are referencing the tax bracket schedule.

A corrected version 23.1 is now available - Jan 23 This update corrects several factors in the calculation and inclusion of NIIT taxes. Users that use NIIT taxes should update. All others can ignore this update. Thanks to DHBS for reporting these problems.
A minor update on 1/25 corrected the average Federal tax rate formula on Details to include LTCG. Version 23.1 has been updated. Since a minor correction most users will not need to update.

A new version of RPM 23.0a is now available. This corrects the calculation of the two optional Roth account balances which were previously overstated. Thanks to ellde for discovering and analyzing this problem. Those without Roth accounts do not need to update:

RPM version 23.0 is now available - Dec 25 --- Updated Jan 8
Download link: replaced by 23.0a

2023 Update includes:
Updated Federal tax factors, Medicare rates, escalation factors, etc. to 2023 amounts. Inflation rates for Social Security, CPI-U, etc.
Correction of RMD for age 70 spouse thanks to DHBS
Fixed NIIT state tax factor
Miscellaneous corrections and improvements
==========================================================================================
Previous version:
RPM version 22.6 is now available - Oct 21
This updates adds an option to select an alternative RMD rate for spouses inheriting an IRA from the owner. The default is the historic method using the Joint Life table. The alternative is to use the Single Life table when the spouse inherits the owner IRA. This will reduce the RMD amounts. This improvement was suggested by a Bogleheads member.

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
===============================================================================

To download: Look for Blue "Download" box on Dropbox page in center, to lower half of screen. Selection box down arrow, and select "Direct Download". When the Dropbox sign in pop up appears, just ignore it, look at the bottom of the pop up and select: "No thanks, continue to view" and you will then be able to download the file to your computer.

Some user comments: "Great tool - excellent - a gem - useful tool - truly wonderful spreadsheet - very good tool - Two thumbs up - Excellent work - amazing work - a very useful tool - Great tool - by far the best tool I have come across for conversion decisions."

Background (original 2012 post): Version 1.0: https://www.dropbox.com/s/inlw8lvpg7tge ... 0.xls?dl=0
I recently started a thread to get advice on whether, as a retiree, I should do some Roth conversions as I have none presently. I got some good advice and ideas, and over the next month I hope to decide whether to do it or not. That thread is here: http://www.bogleheads.org/forum/viewtop ... =1&t=96928

As part of that effort I created an Excel 2003 model (following the advice of many to "just create a model") to try and get a handle on all the investment accounts, income sources, living expenses, and taxes that we have, and see how a series of Roth conversions would impact our financial situation. After creating and using it, I thought I could get some beta testers by adding some instructions and user-friendly features and sharing it, and that has worked out well as users have found some errors and have made useful suggestions for improving it. So I have continued to provide it as a service to my fellow Bogleheads, and others that may stumble upon it.

The model I'm providing is my personal model that I use periodically, entering my own portfolio, estimate of earnings, expenses and anticipated changes we think will happen, to look at the conversion decision. (I've got another five years until RMD and the related taxes kick in and any conversion benefit goes away for good.) In addition to the conversion analysis, and since the model includes Social Security benefits as an income source, I did add one feature that I can't use - a way to quantify the "when should I start Social Security benefits" decision. (We started at 62.) That feature also quantifies the SS decision process, and could be useful to many.

So, as my personal model, use it at your own risk. It may help you or it may not. Your situation may be more complex and the model be of only limited value. Your data may cause it to make bad calculations. It may indicate a conversion advantage because you used a 12% earnings rate. Lots of things can be indicated from the data entered, so use it as a start in your conversion decision, or SS benefits decision, and when in doubt, create a model! (Above updated Dec 2013)

What will this model do?
> Quantify the impact on a portfolio balances from doing Roth conversions, providing information that might help determine if it's worth doing.
> Calculate yearly results, including earnings, expenses, taxes, inheritances, and asset sales in a portfolio for a limited time period, 1 to 40 years.
> Test impact of common events, like the death of a spouse, receiving an inheritance, selling a home, buying an RV, etc., or experiencing a large tax rate increase.
> Show how conversions will impact average and marginal taxes each year, allowing for adjusting conversion amounts to achieve the best tax outcome.
> Provide a Federal tax calculation using simplified input factors that includes determining taxable Social Security benefits, but excludes many tax provisions for higher income taxpayers.
> Use two almost identical models, with the primary difference being Roth conversion and expenditure amounts, to focus on the real impact of conversions.
> Provide a free, unprotected, fully transparent Roth conversion model for those needing such a tool, but unable or not interested in preparing their own.
> Bonus: Compare two different options for starting Social Security benefits using different starting ages, e.g. spousal at 66, self at 70, or spouse at 62, self at 66, etc.

What won't this model do?
> Accurately predict the future. All input factors are under the user's control and the future will likely provide different results than the model calculates.
> Recognize or adjust for non-deductible (after-tax) contributions to your IRA. All IRAs are assumed to be
"tax-deductible" when funded. All IRA withdrawals will be taxed as ordinary income in this model.
> Calculate pre-59 1/2 IRA withdrawal penalties, or the penalty on withdrawing Roth conversion amounts within five years of conversion.
> Calculate the impact of conversions on taxes applicable to high income taxpayers, like the Medicare surtax and higher premium, the phase-out of deductions and personal exemptions, higher qualified dividend and cap gain rates, tax gain harvesting, the AMT, various tax credits, etc.
> Automatically correct for all negative account balances. The user needs to review the yearly schedule and make changes to keep accounts out of the red.

Please note this is a simple model, designed for married or single retirees, with limited selections for asset, earnings, revenue and expense items. While I believe it is operating correctly as designed, it may not be, and users may find errors or unusual results as a result of unusual data sets, which I will attempt to address if reported to me. Feel free to modify it as you see fit and provide an improved version if you wish. I do plan to add a "single taxpayer" option in the future [and did!], to provide for that inevitable event that most of us will face, [and to make the model usable by those filing as Single].

So, plug in your own numbers and see if it gives you any interesting or useful insights, and let me know of any problems found or ideas for improving it!
Last edited by BigFoot48 on Thu Mar 07, 2024 6:46 pm, edited 393 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
backofbeyond
Posts: 480
Joined: Tue Apr 01, 2008 1:07 pm

Re: Retiree Cash Flow Comparison Model

Post by backofbeyond »

Excellent...Many Thanks for passing this on! :sharebeer
The question isn't at what age I want to retire, it is at what income. - George Foreman
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

A new and improved version (2.0) of this model is now available for downloading: Find link to latest version in Post #1.
The primary improvements are:

1 - A user can switch tax status from married filing jointly to single at any year, reflecting an event most of us will face. Since the big advantage of a Roth is the tax-free withdrawals, coinciding them with a change to single status may be beneficial.

2 - The model now supports single tax filing status.

I have tested the model against Turbotax and it correctly calculates Federal taxes (within a few dollars). It models a simple return and doesn't take into account dividend tax rates, or other rates and credits taxpayers "enjoy".

Please let me know of any errors or suggested improvements. I think the model is now complete enough for me to use it for evaluating the financial benefits to doing Roth conversions after retirement.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:18 am, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

A new and much more user friendly version 3.0 of this model is now available at: Find link to latest version in Post #1.

Updates include:
1. A much improved casual user interface, including descriptions and explanations of how the model works and what each input item does.
2. Major sections now on separate pages for easy access.
3. A fix to a lookup table.

As usual, I welcome [strike]beta-testers[/strike] people to use the model and see if it works and provides useful data in the "should I do Roth conversions" question process.

As for myself, a 64-year old retiree with no Roth IRAs, I am still evaluating the decision with the help of this model, but at this point, and for our situation, I'm not seeing a huge advantage to doing so. However I'm going to use the model in the next few months to do some worse-case scenarios, such as significantly higher tax rates in my MRD years, as I need to get started this year if I'm going to do it.

Use and I hope it helps some people get started in this decision process!
Last edited by BigFoot48 on Tue Dec 31, 2013 8:18 am, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
trailmk
Posts: 91
Joined: Mon Mar 26, 2007 2:16 pm

Re: Retiree Cash Flow Comparison Model

Post by trailmk »

Thank you for sharing your work. I'm going to have fun playing with your spreadsheet.

trailmk
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

trailmk wrote:Thank you for sharing your work. I'm going to have fun playing with your spreadsheet.

trailmk
Yes, have some fun with numbers and do let me know if you find any problems, or have useful improvements ideas!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

I've updated and improved the model by adding an itemized deduction option to the federal tax calculation and providing a summary of key results. I also corrected a tax factor escalation calculation, and added or changed numerous other descriptive and help information items. Version 3.1: Find link to latest version in Post #1.

I've yet to get any feedback so I don't know if those who have used it have found it of value. I do think the model is now at a point where I can rely on it for making my own evaluation of doing Roth conversions. Just looking at the model sample data, which approximates some of mine, I'm not too impressed with the results favoring conversions for my situation. And while I call this a Retiree Model, I suppose someone who is 50 and working could use it with more encouraging results. I have not tested that.

Hope you find it of use, and let me know of any problems or suggested improvements.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:17 am, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

I realized my personal situation that RMDs from my IRA was all that I will require to live on might not work for everyone, so I modified the model to allow additional IRA withdrawals post-RMD start. I also put in an error check to keep the IRA balance from going negative due to all these automated withdrawals, and added some warning messages should any account end up with a negative balance as they don't as yet have a procedure to keep them from going negative.

Version 3.2 now available: Find link to latest version in Post #1.

Image
Last edited by BigFoot48 on Mon Jul 17, 2017 5:12 pm, edited 5 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
dcb
Posts: 148
Joined: Wed Sep 26, 2007 12:44 pm
Location: Southwest Michigan

Re: Retiree Cash Flow Comparison Model

Post by dcb »

BigFoot48 wrote:I've updated and improved the model by adding an itemized deduction option to the federal tax calculation and providing a summary of key results. I also corrected a tax factor escalation calculation, and added or changed numerous other descriptive and help information items. Version 3.1: [EDIT - Replaced by 3.2. Get link below.]
I've yet to get any feedback so I don't know if those who have used it have found it of value. I do think the model is now at a point where I can rely on it for making my own evaluation of doing Roth conversions. Just looking at the model sample data, which approximates some of mine, I'm not too impressed with the results favoring conversions for my situation. And while I call this a Retiree Model, I suppose someone who is 50 and working could use it with more encouraging results. I have not tested that.

Hope you find it of use, and let me know of any problems or suggested improvements.
Thank you for your efforts in making this model available to all. I too did not see much benefit in doing additional Roth conversions, but then i may not have entered all the inputs correctly, or my situation is somewhat unique. (I am now withdrawing my RMD plus a few thousand more from my IRA for living expenses. I delayed Soc. Sec. to age 70 so that is the bulk of my living expenses. Also, I already have about half of my IRA in a Roth.) My question is: What do I enter for post RMD IRA withdrawals? Is it the same as Pre RMD withdrawal or just the additional amount above the required amount? I would like to add more to my Roth. Not because it will help my bottom line necessarily, but because of the tax advantage of a Roth to my heirs. But then I do not wish to pay a high price for doing it either.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Cash Flow Comparison Model

Post by BigFoot48 »

dcb wrote:Thank you for your efforts in making this model available to all. My question is: What do I enter for post RMD IRA withdrawals? Is it the same as Pre RMD withdrawal or just the additional amount above the required amount? I would like to add more to my Roth. Not because it will help my bottom line necessarily, but because of the tax advantage of a Roth to my heirs. But then I do not wish to pay a high price for doing it either.
Thanks! Glad you're finding it useful.

Enter the new total IRA withdrawal amount for the post-MRD years on the Input form line 44 field, and then adjust your Roth contribution amount on the Roth page accordingly.

Of course, by doing this you have created a situation where the non-Roth model is no longer directly comparable to the Roth model, because you're making withdrawals you wouldn't have otherwise made just to fund the Roth. So you might want to copy the non-Roth model summary numbers before you make these changes, then compare the three results.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A maintenance update of this model is now available: Find link to latest version in Post #1.

An information and instruction page has been added, along with a number of title and formatting changes. Error checking has been added to warn if any account balance becomes negative due to excessive withdrawals, and an automated reduction in user IRA withdrawal amounts to prevent negative balances is now in place.

As always, comments and suggestions for changes and improvements are welcome.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:19 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Version 4.0 of this model is now available: Find link to latest version in Post #1.

In finally getting serious about making my own decision on Roth conversions for this year, and the next six, I used the model and discovered it didn't address my situation where I am taxable funds poor, and will need to withdraw more from my IRA to pay taxes and the model didn't provide for that. So I added it and one thing lead to another and I updated, changed or fixed a number of items.

If you have previously downloaded this model, I recommend you update to this one for the state income tax fix alone (wrong sign used - sorry about that). The other changes are as follows:

Added Input option to allow IRA deductions during Roth conversion years to be higher than in the Base Case in order to pay income taxes.
Added separate page for inputting Roth conversion amounts, allowing one to monitor the effect it has on the yearly Federal
income taxes to stay within the desired brackets.
Separated lump sum inheritance amounts and asset sales to better reflect when these events will occur.
Added line breaking out the Inherited IRA amount in the Account Changes section.
Improved automated IRA deduction formula to prevent a negative balance, although not quite perfect yet as small positive balances can remain.
Fixed tax calculation of zero taxable income years and state income tax which used the wrong sign in calculating net income.
Improved labels and notes.
Added Change Log

As usual, if anyone has any comments or suggestions, I would welcome them.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:20 am, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Found a formula error in the Base Case IRA withdrawal calculation which now has different input factors than the Roth Case. Updated 4.1 version: Find link to latest version in Post #1.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:20 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SGM
Posts: 3341
Joined: Wed Mar 23, 2011 4:46 am

Re: Retiree Roth Conversion Decision Model

Post by SGM »

I like your spreadsheet. I played with it for a few minutes and I am sure I will use it further. I have been converting since 2010.

My questions may be premature because I have not had much time to use the spreadsheet.

Non conversion: Say you have 100 k in tIRA and 25k in after tax. In the 25% tax bracket you have 100k-25k(expect 25% tax rate) +25k = 100k buying power
Conversion: If you have 100k in a Roth and 25k in after tax- 25k paid in taxes from your after tax acct= 100k buying power.

You have the same buying power with the conversion or without. I always think of the value of my tIRA or t401ks as its value minus my expected tax rate. Do you feel your spreadsheet addresses this issue?

How about higher tax rates if the present law goes into effect? I think your max rate was 33%.
Good job and lottsa effort. Thanks. :beer
"Let us endeavor, so to live, that when we die, even the undertaker will be sorry." Mark Twain
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

I believe the model contains the calculated data to make the after-tax buying power comparison between the Base Case (no conversions) and the Roth Conversion Case, but it's not really set out in the totals or summary. Check it out and see if it provides the data you want.

The tax rates for the four brackets included can be changed (but not the bracket amounts except in year one) in a future year to test the scenario that drives many a Roth conversion decision. Look for the tax factors at the bottom of the Input page.

Let me know if you find any problems or have any suggestions for improving it. I plan to use the model myself to make my own Roth conversion decision soon and hope it will provide a reasonable analysis of the financial impacts of doing so, although I think some of the data on the Results page could be improved to help in that decision.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SGM
Posts: 3341
Joined: Wed Mar 23, 2011 4:46 am

Re: Retiree Roth Conversion Decision Model

Post by SGM »

There is an excellent article from 2010 in the Journal of Retirement Planning. The authors look at comparisions of a variety of scenarios 10 years out. You might want to plug some of their numbers into your spreadsheet for comparison. The article does not account for expenditures during retirement. Have you found that expenditures make much of a difference in the outcomes? I find it difficult to predict changes in expenditures other than using an inflation factor.

An interesting scenario which may increase the benefit of conversion is when a spouse passes on. In the example the other then pays at a higher rate say from 25 to 28% even with a loss of the lower of 2 SS payments. I see your program seems to take that into account.

To Convert or Not to Convert,
That is the Question
By Robert S. Keebler and Stephen J. Bigge
"Let us endeavor, so to live, that when we die, even the undertaker will be sorry." Mark Twain
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

I'll see if one or more of their examples can fit into my model. I have not run any test cases to see how varying expenses affects the outcome. but I can see how the model would benefit from a couple of expenses refinements.

First, it assumes expenses go up every year based on the selected escalation rate but, I believe, the reality is that expenses usually decrease later in life (and, for some, jump back up at the end). Second, it doesn't have a provision for making a large purchase that might be funded by a Roth account. And lastly, while the model allows for triggering a single taxpayer rate in any year, it doesn't provide for decreasing the SS benefits resulting from the death of a spouse. I will try to add these to the next version.

The article: http://www.google.com/url?sa=t&rct=j&q= ... jYKtBEPt_A
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
crow
Posts: 256
Joined: Sat May 03, 2008 3:49 pm

Re: Retiree Roth Conversion Decision Model

Post by crow »

Thank you so much for all the work you put into this. I had done some basic calculations on my own, but this is a lot more detailed and helped me to ultimately decide not to convert.

Crow
ResNullius
Posts: 2091
Joined: Wed Oct 24, 2007 3:22 pm

Re: Retiree Roth Conversion Decision Model

Post by ResNullius »

OK, I have a Roth question. I'm retired, so I have no earned income. Can I contribute to a Roth IRA? If so, what is the max that I can contribute each year? I'm 62. I've always assumed that I couldn't do a Roth while I was working due to the income limits. Now that I'm not working, I've been assuming that I still can't because I don't have earned income. Also, I've assumed that the max contribution anyway would be only $6K for someone my age, so it really doesn't seem to be worth the trouble. Am I off base with these assumptions?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Crow, I'm glad you have found it useful. Do revisit your decision if you used the model and see a major update to it via the other tread. For example, I decided to add some flexibility to the expense forecast today and that lead me into splitting the SS benefits, adding a spouse age, and allowing SS benefits to start and end at different ages. Look this revised model by tomorrow.

ResNullis, this thread is about converting a portion of a traditional IRA into a Roth after 59 1/2, which to the best of my knowledge can be done in any amount if one wants to pay the tax on the withdrawal. There may be some limitations on this that I'm unaware of and perhaps someone will expand on the process.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
ResNullius
Posts: 2091
Joined: Wed Oct 24, 2007 3:22 pm

Re: Retiree Roth Conversion Decision Model

Post by ResNullius »

Oh, yes, I got mixed up. The issue for me and the OP is whether to convert a TIRA to a Roth IRA, and that's quite different from making normal contributions to a Roth.
DickBenson
Posts: 809
Joined: Sun Apr 08, 2007 7:27 pm

Re: Retiree Roth Conversion Decision Model

Post by DickBenson »

ResNullius wrote:OK, I have a Roth question. I'm retired, so I have no earned income. Can I contribute to a Roth IRA?
No. Your assumption that you need earned income is correct.

Dick
DickBenson
Posts: 809
Joined: Sun Apr 08, 2007 7:27 pm

Re: Retiree Roth Conversion Decision Model

Post by DickBenson »

BigFoot48 wrote:... while the model allows for triggering a single taxpayer rate in any year, it doesn't provide for decreasing the SS benefits resulting from the death of a spouse. I will try to add these to the next version.
Does the model take into account any changes in the amount of SS benefits that become taxable when the tax bracket changes come sooner when filing as a single taxpayer?

Dick
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Dick, it calculates how much of SS benefits are taxable based on other income and uses either the married or single filing choice in doing the calculation. The primary fields from Form 1040 for the taxable SS calculation are in the yearly summaries, and can be viewed by unhiding the rows.

A new version of the model with many nice improvements will be posted tomorrow if you want to check it out.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A nicely improved, if I must say so myself, version of the model is now available - Find link to latest version in Post #1.

This calculates the data the same as previous models, but adds a number of refinements to allow users to more accurately reflect their situation. Primarily, SS benefits which were previously entered for both the user and spouse, but only the users start date was used, can now be started for each individual, such as at 66 and 70. In addition, either benefit can be stopped at a future date to stimulate the death of a spouse.

The example data used in the model indicates about a 4% higher portfolio balance at age 93 by doing Roth conversions. If an across-the-board 5% tax rate increase happens in 2020, this jumps to 7.6%. Unfortunately we're all not example data, but the assumptions are realistic for a lot of people, I believe.

This is the list of all the changes.
> Social Security benefit start years for a married couple can now be different (amounts always were) and either benefit can have an end date to model the lost of benefits occurring from the death of a spouse,
> Living expenses usually drop later in life. A percentage reduction can now be entered at a future age.
>One benefit of doing a Roth conversion is to use those funds tax-free in a future year. A large expenditure can now be input in a future year to simulate the use of these funds, rather than just their accumulation in the taxable account. Pending: Option to use IRA withdrawals in the Base Case to fund triggering a tax event!
> Existing Roth account separated from previous single row for all Roths. Withdrawal calculation set up for existing Roths.
> Trigger "year" type input data has been changed to the more intuitive trigger "age".
> Added a "Percent of Tax Bracket Used" to Roth Conversions Input page to help determine optimum conversions each year.
> Added summaries showing cash flows to bottom of Base and Roth pages.
> Numerous title and description refinements to be more descriptive, and other changes to make the model more warm and fuzzy!

As usual, if anyone finds any errors or problems with the model, let me know.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:21 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
earlyout
Posts: 1542
Joined: Tue Feb 20, 2007 4:24 pm

Re: Retiree Roth Conversion Decision Model

Post by earlyout »

A tremendous amount of effort to get this put tegether. Thank you!

Yesterday afternoon I tried to determine if the value of traditional IRA used to generate "Portfolio Balance at age 93" is discounted for the taxes that will have to be paid on the tIRA. If so, what tax rate did you use?

Thanks.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Thanks, you're very welcome. No, the values at the end of the model are not discounted for their possible tax burdens, but certainly an important factor, as a basket of Roth IRAs at 93 would be significantly more valuable than a basket of TIRA's! I will add that to think "think about doing this" list for the model.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version is now available with some very useful improvements - 5.0: TEMPORARILY WITHDRAWN

The most generally useful change is rather than being a fixed 33 years, the model now extends to 40 years and the user can select however many years he/she wishes, from 1 to 40. So a person 50 may select a 40 year modeling period, which a person 66 a 24 year period. This makes the ending year data much more useful, I believe.

The other major change is allowing the user to trigger an IRA withdrawal, above and beyond the normal pre-MRD/post-MRD withdrawals, at any age and for however many years desired. This will produce a better comparison between the use of tax-free Roth funds to make expenditures later in life, to that required if taxable IRA funds were withdrawn.

For example, and as use in the model's sample data, a person may want to buy a $150k RV at age 75. If they did a Roth conversion years earlier, those funds would be available tax-free, but if they needed to withdraw money from an IRA to fund it, say over the previous three years to minimize tax bracket creep, then a tax cost would be incurred. I wanted the model to analyze this situation.

Other minor improvements have been made in titles and explanations. While no calculating changes have been made as best I can recall, I recommend all previous users of this model upgrade for the useful improvements.

Enjoy, report back, and now, finally, maybe I can finish my own conversion analysis!

Change Log:
Total number of years in model has been increased to 40.
A user can chose the number of years to forecast, ranging between 1 and 40.
The Base Case now has a feature to make a TIRA deduction, at any age and for any number of years, to mimic a Roth deduction in the same period.
Expanded some calculation details, normally hidden, to provide easier understanding and an audit trail.
Improved instructions and labels.

Oct 17: I just notice while doing mine that the SS benefits start dates must be equal to or greater than your current ages as input for the model to include SS benefits. I added a cell comment noting this and resaved the model.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

An new version is now available - 5.1: Find link to latest version in Post #1.

Sorry about an update so soon, but I discovered a one-year shift error in a formula today while doing my own numbers.

Changes:
Fixed Lump Sum Change event that was included in wrong year.
Added several error checks to alert for negative account balances in any year, and SS start date earlier than users' ages.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:22 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SlowLane
Posts: 5
Joined: Thu Nov 15, 2012 12:09 pm

Re: Retiree Roth Conversion Decision Model

Post by SlowLane »

:happy

Bigfoot, thanks for your Roth Model. It is a very good tool for learning about Roth IRAs and the tax situation. My question was "how much should I convert in 2012, before the 2013 tax increases?" I had to do some fiddling, but I arrived at the answer using your Roth Model. You did a great job in creating it!

I offer these ideas for improving your Roth Model:
1) Instead of entering annual withdrawal amounts from the Taxable Account and Existing Roth Account, it would be helpful for the Model to automatically drain the Taxable Account before touching the Existing Roth Account. Perhaps set a priority list of the different accounts and automatically drain them in the specified order for taxes and expenses.
2) I used "Single" tax filing and the RMD was not being taken from the IRA account in the "Roth Case". The "Base Case" worked fine, so I just copied the RMD line to the "Roth Case"
3) The top tax rates are needed to get a complete picture. Nice foresight to make tax tables accessible, and this feature saved the day for me. For higher growth rates, it pays to convert more aggressively, at higher tax rates.

Thanks again for a job well done.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

I'm glad you found it useful and thanks very much for the suggestions, and finding that calculation error. I will correct that immediately. One advantage of using two almost identical models to make the calculation is that by removing the few input differences, like Roth conversion amounts, the two models' results should be identical, making debugging easier, but I missed that one because I don't test the Single option enough!

As I originally developed the model for my situation, I didn't model the higher brackets but will consider putting them in, perhaps in the new year with any changes in the rates, etc.

I will see if your idea of setting an account priority list can be accomplished without too much difficulty. I've left some input items to be user-regulated, and provide messages when an account goes into negative balance, rather than just automatically adjusting withdrawal amounts. I may be able to refine this in future releases.

UPDATE: RMD calculation for IRA fixed on Roth side of models (had spouse age and should have been user age). Download again if you used the Single option, or your spouse's age is different than your own, AND you have IRAs (existing or inherited) in your case.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SlowLane
Posts: 5
Joined: Thu Nov 15, 2012 12:09 pm

Re: Retiree Roth Conversion Decision Model

Post by SlowLane »

:D

BigFoot, you are on the ball! You provide better support than commercial software vendors!

Strategies for Tax-Efficient Retirement Withdrawal is a whole other topic. Here is one link:
http://www.fpanet.org/journal/CurrentIs ... lPlanning/

The above article is dated, and does not consider Roth accounts. If you modified your Roth Model, you could discover the optimal strategies and publish a paper. That is, if you are so inclined.

Thanks for fine work!
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version of this model, 5.5, is now available. Newer version is now available. Find link to latest version in Post #1.

I recommend users of earlier version download this and re-input their data as there are a few error corrections that may have affected some users' results.

The primary changes are:
1. Added 33% and 36% tax brackets
2. Added start year to Taxable and Roth accounts withdrawals, allowing more flexibility in matching withdrawal rates and timing with needs,
3. Added field to enter Case Description to track and label results and remember model input factors.
4. Fixed age selection error in determining IRA MRD in Roth model. Spouse age was used rather than "self" age as used in the Base Case.
5. Fixed taxable Social Security calculation for error from a married couple having a "become single" year entered which forced the use of the single SS factor early.
6. Added error checking and messages, including hint to match going to a Single tax rate for married couples in the same year that SS benefits end for one.

Three of these changes come from SlowLane's suggestions (thanks!). When I originally prepared the model I ignored the higher income brackets as they didn't apply to me, or most people, but decided to add them as there's evidence there's at least one person that would benefit, and for future flexibility as rates are likely to shift in years to come and who wants to address it then.

SL suggested that withdrawals from the Taxable and existing Roth accounts be automated and linked, but I like the idea of leaving these things in the hands of the users. So to partially accomplish his idea I added a start year to each of these withdrawal factors. So you can input a number for taxable, matching current year needs not met by income, for example, and when the Taxable account is exhausted, start withdrawals from the Roth in that year. Please note that Taxable account changes are automated anyway, in that if in a year expenses exceed income, the needed cash flow to maintain a zero result is deducted from taxable automatically, and via-a-verse for excess funds in a year.

There was a calculation error SL detected in the RMD calculation as his single status triggered it. I discovered another filing status type error in the Social Security tax calculation, and fixed that too. These would have relatively minor impact on the comparison results (an error duplicated in both sides of the model would tend to negative themselves), but users should use the new version just to verify that their result hasn't significantly changed. I have checked the model's 2012 calculation of taxes, using numerous alternatives, against TaxCaster and the resulting Federal tax exactly matches (except for a few dollars off in a minority of cases for some unknown reason).

I made a change to the example data, increasing expected investment return rates from 3% to 5% to be closer to what I've been seeing as expected real returns in coming years. The sample data indicates a 9.8% end-portfolio advantage to doing $150K of Roth conversion for the married couple, and 13.7% if a 5% tax rate increase happens across the board in 2020. In version 5.1 with the errors, the advantage was 9.7% using the same data, so a minor impact, but I would prefer no such problems!

As usual, please let me know you find any problems, or have any suggestions.

Update: A user requested an Other Income item that has an end date, such as a salary for a pre-retiree. This will be in the next release.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:22 am, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SlowLane
Posts: 5
Joined: Thu Nov 15, 2012 12:09 pm

Re: Retiree Roth Conversion Decision Model

Post by SlowLane »

:thumbsup :thumbsup

Two thumbs up from me! Version 5.5 is great! I like the "Start Year" for withdrawals - it is a very good solution. And the additional tax brackets make your model more robust. Great job BigFoot!
bsteiner
Posts: 9151
Joined: Sat Oct 20, 2012 9:39 pm
Location: NYC/NJ/FL

Re: Retiree Roth Conversion Decision Model

Post by bsteiner »

There are so many uncertainties that it's impossible to analyze the Roth conversion with precision. The best you can do is make some reasonable assumptions as to investment returns (both in the IRA and after-tax in the taxable account), and future tax rates.

However, as a general rule, assuming you have enough nonretirement assets to pay the tax on the conversion, the Roth conversion generally makes sense if the tax rate on the conversion is less than, the same as, or not too much higher than the tax rate when you (or your beneficiaries) would otherwise withdraw the money.

A simple example. Assume a constant 30% income tax rate. You have $100 in your traditional IRA and $30 in your taxable account. You convert and use your $30 taxable account to pay the income tax on the conversion. Over some period of time, your $100 IRA (which is now a Roth IRA) grows to $200, all of which is yours. Your twin brother did not convert. Over the same period of time, his $100 traditional IRA grew to $200. He withdraws the $200, pays $60 tax, and has $140 left. He also has his taxable account. However, it grew to less than $60, since he had to pay tax on the income and gains on it each year.

The way to look at your traditional IRA is that it's part yours and part the government's, and that the income and gains on your part are tax-free. In the above example, it's 70% yours and 30% the government's. Converting is the same as contributing an additional $30 to your IRA.

The complexity arises because your tax rate can change over time.

Other benefits of the Roth conversion are (i) no required distributions after age 70 1/2 (this is a major benefit), (ii) a Roth IRA is a more valuable asset to fund a credit shelter trust (less important if the estate tax exempt amount stays at a high level and portability is made permanent) or GST exempt disposition, (iii) if you leave your IRA to your children or grandchildren in trust rather than outright (to keep it out of their estates and to better protect against their potential creditors (including spouses), you avoid the compressed income tax brackets for trusts, and (iv) you avoid the double tax problem caused by the income tax deduction for the estate tax applying only to the Federal (but not the state) estate tax.

If you're in a high bracket and expect to remain in a high bracket, you should consider converting your entire IRA now (especially given the scheduled increase in income tax rates).

If you're in a high bracket now but expect to be in a lower bracket later (such as upon retirement), you may want to wait until you retire.

If you're in a low bracket but your IRA is large, and converting it all at once would put you into a high bracket, you may want to consider converting some each year, so as not to be in too high a bracket.

If you're terminally ill, you may want to accelerate your conversion.

The above is intended only as general information, and not as specific advice, for which one would need to know the details of a particular individual's situation and his/her objectives.
Last edited by bsteiner on Thu Nov 22, 2012 9:17 pm, edited 1 time in total.
kaneohe
Posts: 6786
Joined: Mon Sep 22, 2008 12:38 pm

Re: Retiree Roth Conversion Decision Model

Post by kaneohe »

bsteiner wrote: A simple example. Assume a constant 40% income tax rate. You have $100 in your traditional IRA and $30 in your taxable account. You convert and use your $30 taxable account to pay the income tax on the conversion. .
you might want to fix your typo here..........
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version of the model, 5.6, is now available. You can download it via this link: Find link to latest version in Post #1.

The release includes a new feature and fixes a logic error that resulted in over-stating taxable Social Security benefits. increasing taxes on both sides of the calculation. This impacted final portfolio amounts by less than 1% in the example data, and likely had little impact in comparison for most users, and none for those that chose the married tax option and had a "becoming single" year entered, as that situation was handled correctly.

Added is a new input item allowing a person to enter a temporary income item with an ending year, allowing pre-retirees, or retirees working part time, to include this income with an ending year.

Changes:
>Added Other Income revenue item with an end date, use for temporary income like a pre-retiree salary or temporary job.
>Fixed taxable Social Security calculation error caused by selecting "married" but not having a "becoming single" factor.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:22 am, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Oops, just found an error in the Existing Roth Withdrawals start year. It was using a difference factor's start year, as result of copying formulas and not modifying every cell reference that needed modifying. If you downloaded the latest model in the last few days and expect to use this element you might want to download again, otherwise you can ignore it.

I also just realized there may be a flaw in how SS benefits are handled. Users input their current benefits, or their expected benefits starting in some future year, but these future benefits are not escalated by the COLA inflation factor. (Unless the users do this themselves.) There probably should be an escalation, as almost all other items in the model are subject to an escalation factor or an annual earnings rate. I will address this in the next release. I don't think it has much if any impact on the comparison results, as both sides would be slightly understated by the same amount.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version, 5.7, of the model is now available: Find link to latest version in Post #1.

While I regret having to release a new version so soon, I feel it is necessary because of the large impact on forecast portfolio values that escalating the SS benefit by the COLA to the chosen start date had in the Example Data. This needed change dawned on me the other day. If anyone disagrees with my thinking or its application to this model please let me know.

The Example data assumes a couple at age 60 with the spouse taking spousal benefits at 66 and the primary wage earner taking at 70. For the Example, I assumed maximum SS benefits, which is currently $30,315 starting at age 66. The primary, who is waiting until 70 to start, would get the 8% yearly bump, giving them a $39,806 benefit at 70. But wait, there's more - there's the yearly COLA that has averaged about 2.7% over the last two decades. I used 3% in the model and apply this yearly to the $39,806 for the 10 years this person will be waiting to start makes the benefit $53,757 in 2022.

This refinement has a huge impact on income and portfolio values. In the previous version of the model without the interim COLA, for both persons total SS benefits were $1,477,871. In the new version it's $1,921,717. It also impacted the purpose of this model, determining the value of a Roth Conversion. In the earlier version doing conversions resulted in an ending portfolio value (age 89) $111,393 higher, or 10.3%. It's now $125,882 higher but because of the much larger portfolio balance the percentage has dropped to 7.9%.

Previous users can decide if they want to re-run their numbers, but it would probably be worthwhile to use this more accurate model. However, for people already on SS this change will have no impact, as the COLA was always applied to their existing benefits in calculating future benefits.

While feature creep in software is often not a positive thing, I decided to add one feature to this model since all of the data was here and ready to be used. You can now compare the results of two alternative Social Security starting year and amounts. To use this, the Roth conversion data should be zero, resulting in the two models starting out with identical results, then a selection is made trigger the alternative SS numbers to be used by the Roth model. The usual results data can be then be examined, along with a new SS comparison, to see what impact the alternative SS benefits and timing had on the portfolio. Consider this an Easter Egg, the main focus remains on the Roth conversion analysis.

As usual, please report any problems, and suggestions or comments are always welcome.

Changes:
Added the COLA adjustment to SS benefit so that its starting value in a future year is more accurately included in the model's calculations.
Added a bonus option to compare two different SS benefit alternatives, such as "take at 62" and "take at 70".
Improved error messages, formatting and instructions.

Image
Last edited by BigFoot48 on Mon Jul 17, 2017 5:15 pm, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
shawcroft
Posts: 929
Joined: Sun Feb 24, 2008 3:18 pm
Location: Connecticut

Re: Retiree Roth Conversion Decision Model

Post by shawcroft »

BigFoot:
Excellent work- thanks. Helps a lot.
Shawcroft
travellight
Posts: 2892
Joined: Tue Aug 12, 2008 5:52 pm
Location: San Diego

Re: Retiree Roth Conversion Decision Model

Post by travellight »

amazing work, bigfoot!

This is an example of what is so fantastic about this site.
364
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

I'm glad people are finding the model useful, and I'm even more happy to contribute something to this valuable forum.

The current version of the model can now be imported into Google Spreadsheet. This will allow users without Excel to use it, or as reported by one user to me, those that might have a problem using Mac PC to run it. The import to Google is largely successful, and all calculation appear to work as they do under Excel. The only thing that does not successfully transfer is the one graph on the Results page, and I notice Google has a problem in lower levels of the Input page in lining up the cursor with the correct cell. Just be careful that you are changing the correct cell on the Input page.

I had to make some text formatting changes to accommodate the Google program, but I have not created a new release. If you want to try Google Spreadsheet, download the formatting-revised model from the above link starting today.

Info on the on-line Google Spreadsheet program: https://support.google.com/drive/bin/an ... 20322&rd=1
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SlowLane
Posts: 5
Joined: Thu Nov 15, 2012 12:09 pm

Re: Retiree Roth Conversion Decision Model

Post by SlowLane »

Thanks for your diligent work BigFoot! You have helped me figure out the optimal ages for SS in my circumstance.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version of the Retiree Roth Conversion Model is now available. This version, 13.0, includes the 2013 tax brackets, exemptions and deductions (as best determined at this point of time), along with a number of improvements, including a refined method of calculating annual returns on average portfolio account balances, and a less-cluttered input page.

You can download the Excel model file here: Find link to latest version in Post #1.
Mote: 2013 tax deduction and exemption were $50 low according to CCH. Model updated on 1/11 at 5PM. DIY or download.

Changing the annual income calculation to an average of beginning and ending balance resulted in slightly higher investment income in every account, while a correction to the tIRA account calculation to deduct the current year conversion amount in averaging the balance resulted in lower income in that one account. As a result of this and the tax effects, the sample data results changed from a $126k/7.9% advantage of doing a Roth conversion to one of $96k/5.9%.

I recommend all users download the current model and re-run their numbers to get what I believe is a more accurate result. (If forecasting for 30 years with guessed annual return and tax rates can be said to produce "accurate" results.)

I created this model and I'm also a user of it, and for my situation at 65 it indicates a 2.9% higher ending portfolio balance in 30 years if I maximized my 15% bracket by doing Roth conversions for the next six years. While this is a nice bump, my numbers are such with no heirs that I'm not inclined to pay the extra income taxes now, so I don't plan on doing any conversions myself - subject to periodic revisits of course!

Changes in this version:
> Starting year, new tax rates, brackets, exemptions and deductions set for 2013, as best currently determined. (Always user adjustable.)
> Added yearly marginal tax rate determination, and a chart comparing the two cases in the Results page.
> tIRA yearly calculated income which was not reflecting the impact of current year Roth conversions, thus being overstated.
> Added option to not calculate Federal and state income taxes to better understand impact of Roth and other decisions on after tax cash flow.
> Started new version numbering scheme based on the tax year.
> Moved instructions, notes and comments to title and factor comment fields to allow more space, ease of editing and readability.
> Added negative balance monitoring to automatically prevent negative IRA account balances.
> Updated, improved and added descriptions and instructions.

As usual, I welcome any comments, suggestions or problem reports. Enjoy, and I hope all find it useful in making the Roth conversion decision.

Image
Last edited by BigFoot48 on Mon Jul 17, 2017 5:17 pm, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
jdjd2
Posts: 30
Joined: Thu Feb 14, 2013 10:47 am

Re: Retiree Roth Conversion Decision Model

Post by jdjd2 »

I found your thread when you commented on a thread I started
http://www.bogleheads.org/forum/viewtop ... 1&t=110999
where I was using a very unsophisticated spreadsheet to consider a Roth conversion.

Thanks for pointing me to your thread and spreadsheet, a very useful tool.

A few comments and questions:

Setup and Input Worksheet

• Row 47 - 4. Special tIRA Withdrawals

The comment states: Having a Roth account allows tax-free withdrawals for spending purposes. To more accurately compare the Base Case to the Roth Conversion, force tIRA withdrawals to be made in the Base Case at the same time Roth withdrawals are being made in the Roth Case. Use the lump-sum Expenditure field just above to match the Roth expenditure amounts, if any.”

While I don’t understand how this would “more accurately compare the Base Case to the Roth Conversion”, it appears to be a moot point if one does not need to make Roth withdraws prior to full Roth conversion. Is this correct?

• Rows 26 and 27 - Social Security Benefits

Initially, our SS benefits equal my wife’s PIA at RFA plus my spousal benefits. At age 70, I will claim my deferred benefits, and my wife will claim her spousal benefits against my PIA at my FRA. This strategy maximizes our SSI now and at age 70.

How do I reflect this change in SS benefits when we turn 70?

Roth Conversions Worksheet

• Columns R – V – Taxes By Tax Bracket

Do these or some other columns reflect the Five Tax Increases Effective In 2013 For Upper Income Earners
http://www.boston.com/business/personal ... ncrea.html, specifically:

o 0.9% Medicare surtax on wages
o 3.8% Medicare tax applied to net income.
o Phase out of itemized deductions and personal exemptions
o Increase in qualifying dividend and capital gains tax rate
o Increase in the federal income tax rate

Roth Case Worksheet

• I went through this worksheet in detail to understand the calculations. The only fields (rows) I did not understand were fields (well actually rows) F14, F15 and F18. What is the reasoning behind these calculations? It looks like values are being averaged for some reason??

Thanks! I will continue to look at your spreadsheet with the goals of understand it and then using it for my conversion decision.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Glad you are finding it useful and thank you for the questions.

• Row 47 - 4. Special tIRA Withdrawals
Once advantage of a Roth account is the ability to make withdrawals tax-free, and this is in the model. But in order to keep the symmetry of the two models for comparison, I also give the user the ability to make equal withdrawals from their IRA in the same years, and thus provide a comparative financial result of the same potential action, like buying an RV.

If you do not plan any Roth withdrawals then this feature won't be needed.

• Rows 26 and 27 - Social Security Benefits
For more sophisticated SS benefit plans, I recommend putting in your initial choices for the calculation, then manually change the values in each model (lines 19 and 20) in the appropriate future years to the SS benefits you estimate you will receive in those years. The model will then escalate those values for remaining years. Just be sure to use the same escalation factors as the model does, and remember that formulas in those change years have been replaced by values.

• Columns R – V – Taxes By Tax Bracket
The special tax factors for high income individuals are not in the model. If there's enough demand for this I might be able to include some or all of them in a future update.

• It looks like values are being averaged for some reason??
In order to calculate yearly income for each account, I average the beginning and ending balances, and then apply in revenue factor. It's a bit crude, but as it's being done the same way in both models and then compared, some of this crudeness gets to be minimized. The best way to test the impact of this is to put in a simple starting account balance, with no deductions, and see if its calculating reasonably well for your situation.

These are my brief, quick answers. If you have more questions let me know.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
BradFreeMan
Posts: 1
Joined: Wed Feb 27, 2013 1:13 pm

Re: Retiree Roth Conversion Decision Model

Post by BradFreeMan »

Hi!
I am so grateful for your spreadsheet that, after many years of reading this forum, I am inspired to finally contribute. I was in the process of trying to develop a spreadsheet to analyze my personal case for Roth conversions when I stumbled upon yours. Being a retired software engineer, I'd like to avoid the hazards of re-inventing the wheel, so I have adopted you spreadsheet for my analysis.

I have a question about line #31, IRA Withdrawals Pre/Post RMD. My wife and I have different ages and the formula for line 31 is different in the Roth and Base cases. In the Roth case, the RMD age comes from line 4, spouse's age; in the Base case, the RMD age comes from line 3. Was this intentional? I'm using version 13.0 of the spreadsheet and noticed this when trying to make the Base Case match the Roth Conversion case by setting Roth Conversions to 0.

Thanks again for your fine effort and your willingness to share.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

Thank you Brad. This is indeed an error and I appreciate you reporting it although I'm sorry you were subjected to it. I have fixed it and will provide a revised version by the morning, as I am now scanning the model for any similar mistakes in use of ages, as I have seen it once before but thought it was eradicated.

Although I test the model as much as I can, by starting with minimal data, and adding factor after factor, I now realize that I have tended to have "self" and "spouse" at the same age, partially because the spouse age is not used (or supposed to be used!) for many calculations, and my wife and I are the same age so I had a prejudice toward that. Even the sample data that comes with the model has both the same age, which I will now change.

Thanks again and I'm happy you have found it useful - or will when fixed. As usual, I welcome all comments, suggestions, and error reports.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
zip605
Posts: 28
Joined: Wed Feb 27, 2013 4:51 pm
Location: Chicago area

Re: Retiree Roth Conversion Decision Model

Post by zip605 »

Great tool (v13.0). Just what I was looking for.

Is there some support in the tool to have the Roth conversion cost (tax) calculated as part of the tIRA conversion/withdrawal. In other words, the tax is deducted from the tIRA funds, and not from the taxable account(s)? What if the taxable accounts value is minimal? I think the tool just posts the cost as a negative against the taxable account (BASE tab, cells E48 F48...) If the account doesn't have sufficient funds, shouldn't something be flagged as a NEGATIVE?

I know the README says the model will not automatically adjust for this, but maybe it could flag it. Maybe a switch could be added to force the model to somehow account for the tax consequences from the tIRA withdrawal.

If I understand how this would work, each year I would need to withdraw enough from the tIRA to cover both the Roth portion and the tax portion. This withdrawal value is not constant, but depends on the amount desired to actually end up in the Roth. What I don't seem to grasp is how to get the Roth account to not include the variable amount that should go toward taxes.

Well, I hope this makes sense. In any case, the model is by far the best tool I have come across for conversion decisions.

Thanks for sharing.

zip60
zip605
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 »

A new version of the model is now available, 13.1: Find link to latest version in Post #1.

This corrects an error found by BradFreeMan that had the wrong age being used to determine the start of IRA withdrawals on the Roth conversion model, causing a distortion in the comparison of the two. Unless users have the same age for self and spouse, I recommend downloading and re-running your analysis.
Last edited by BigFoot48 on Tue Dec 31, 2013 8:24 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Post Reply