Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
jdjd2
Posts: 24
Joined: Thu Feb 14, 2013 11:47 am

Re: Retiree Roth Conversion Decision Model

Post by jdjd2 » Thu Feb 28, 2013 9:04 am

Thanks for the quick response above.

• RE: Social Security Benefits

Ok, manually changing future years benefits worked for me. I’ll just need to apply these changes to any future version of your spreadsheet.

• RE: Columns R – V Taxes By Tax Bracket

Ok. This helped explain the differences between the taxes I was getting for 2013 and what your spreadsheet got. My (very simple) spreadsheet assumed one made a partial Roth conversion right at the start of the year. So the subsequent new remaining balance in the unconverted portion of the IRA was the (IRA value at the start of the year – the Roth conversion dollars) * the expected % return on investment for the year.

I was also using 2012 tax rates in my spreadsheet for all future years rather than the 2013 rates and your extrapolation for subsequent years.

All is good now. Working with my (very simple) spreadsheet and yours, I came up with some additional observations and questions.

• Roth Case Worksheet, Row 6 (Account Balance – Taxable) and Row 14 (Income – Taxable Account)

On average, the Vanguard fund I was using returns 6% annually (yes, past performance does not …). Using the 6% value, my ending values on the taxable account in your spreadsheet and mine were way off. To keep it simple in mine, I just plugged a single value for the yearly return in the spreadsheet and kept it constant. Not the best way, thus my interest in using your spreadsheet.

But in trying to understand why your yearly balances were so different from mine, I realized that while the fund is returning 6% annually, I am only receiving the portion that is paid out in dividends and capital gains. So yes, the fund increased in value by ~6%, but what I saw for taxable purposes was only about 2.8%. the rest is accounted for by the increased share price. To compensate for this in your spreadsheet I used 2.8% rather than the fund return of 6%.

This might be worth a note in the Readme worksheet.

• To offset capital gains on taxable funds I will use to pay for the Roth conversion, I will use capital losses from a stock I should have sold years ago. I also plan to have $3,000 greater losses for a given year to decrease my taxes somewhat. To reflect this $3,000 loss, I adjusted yearly income for 2013.

I have not looked at how I can do this for subsequent years.

• I also found I had to compensate for qualified dividends, i.e. that portion of the taxable account that are taxed at the capital gains tax rate rather than as ordinary income. In my case, ~50% of my yearly dividends are qualified dividends.

I’ll continue to work with your spreadsheet. I’ve not gotten to the analysis yet. Rather as I do with my own spreadsheet, I first want to validate that the numbers I am getting make sense to me. Your spreadsheet is a very useful tool!



Thanks.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu Feb 28, 2013 5:25 pm

jdjd2 wrote:But in trying to understand why your yearly balances were so different from mine, I realized that while the fund is returning 6% annually, I am only receiving the portion that is paid out in dividends and capital gains. So yes, the fund increased in value by ~6%, but what I saw for taxable purposes was only about 2.8%. the rest is accounted for by the increased share price. To compensate for this in your spreadsheet I used 2.8% rather than the fund return of 6%.

This might be worth a note in the Readme worksheet.

A very insightful discovery, and a factor I had not considered. By considering all annual growth in the Taxable account as taxable income, even that which is in reality undistributed gains, my model overstates income tax expense. Conversely it would seem, in later years when funds are being withdrawn from a taxable account by selling funds, and realizing gains, more taxes should be due. Offhand, I have no idea how this could be modeled, but I'll use as an excused I'd rather not make using the model too complex for the average user.

I will add a note on this phenomenon so that users can be aware of it. However, since the Roth comparison comes from comparing two spreadsheets with this same calculation flaw, the results may still be reasonably "correct" with the over-taxation error.

I also can't include some of the more important tax provisions, like the capital gains rate, without over-complicating the user input requirement. And in arriving at the comparison of Base Case to Roth Case, this tax rate error should generally wash-out.

Thanks again for the helpful comments!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu Feb 28, 2013 5:58 pm

zip605 wrote: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?

Glad this model is useful to you.

In Setup, cells B35 and B38 you can select an amount to be withdrawn from the TIRA in the Roth model case before and after the RMD start-year. Using this you can provide an approximate amount of funds to cover the taxes from the Roth conversion amounts selected. I will consider adding a start and end years to these withdrawals in a future update to make them more accurate for this purpose. An explanation of this is in cell A34.

The are a number of error checks and messages to warn of negative balances, but it is not universal and users should review the various account-type balances in each spreadsheet for negative balances. At this point it is up to the user to adjust the various factors to avoid negative balances, just like in real life!

Thanks for the comments.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Roth Conversion Decision Model

Post by DickBenson » Thu Feb 28, 2013 8:24 pm

For those who might have some difficulties using a spreadsheet to decide whether or when to convert to Roth, let me repost here an alternative view of a TIRA which might help one make such a decision.

A Traditional IRA (TIRA) can be thought of as a partnership with the government in a profit making business (the government's share is the tax bracket you are in when you withdraw funds). Believe it or not, calculations can show that it also behaves as if both your share and the government's share are growing in a tax-free manner. Thus a conversion to a Roth can be looked upon as "buying out" the government's share and thus investing your funds (those used to pay the tax) into a tax-free investment, rather than investing or keeping those same funds in a taxable investment. Your net worth doesn't decrease as you now own "all" of the Roth account.

However, the main feature of this "partnership" is that your share of the TIRA will vary over time with your tax bracket. Thus you would want to "buy out" your partner when the tax bracket (cost) is as low as possible. For many of us this occurred in the period between retirement and the beginning of RMDs. For others it can occur while still filing joint returns, before willing it to a heir in a high tax bracket, before taking social security, etc.

It could be that the problem of determining the best time to make the "buy out", is simply to make the "best guess" as to when you will be in your lowest tax bracket.

Dick

jdjd2
Posts: 24
Joined: Thu Feb 14, 2013 11:47 am

Re: Retiree Roth Conversion Decision Model

Post by jdjd2 » Fri Mar 01, 2013 2:07 pm

I've finally completed my review of the Roth Case and Base Case and reconciled it with my (very simple) spreadsheet. It led to my finding a few errors in my spreadsheet; a useful way to understand yours and validate mine (which is not worth anyone else using).

I also looked at the Results worksheet and have a question on Row 14 (IRA Required Minimum Distribution), cell G14.

Assume C14 = (100,000), D14 = (8000), then E14 = 92,000, F14 = -92, and G14 = Base. Shouldn't G14 = Roth in this case? That is a smaller IRA Required Minimum Distribution is better in this case?

thanks

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Sat Mar 02, 2013 10:36 pm

jdjd2 wrote:Assume C14 = (100,000), D14 = (8000), then E14 = 92,000, F14 = -92, and G14 = Base. Shouldn't G14 = Roth in this case? That is a smaller IRA Required Minimum Distribution is better in this case?

Thank you for pointing this out. Actually the formulas in C14 and D14 should have a positive sign, rather than a negative one as these just represent the values of those amounts. After an earlier change in how the RMD is shown, I failed to change these formulas. The model has been updated and by removing the negative sign in these two cells you can correct it.

Thanks again for helping refine the model!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

zip605
Posts: 15
Joined: Wed Feb 27, 2013 5:51 pm
Location: Chicago area

Re: Retiree Roth Conversion Decision Model

Post by zip605 » Sun Mar 03, 2013 12:51 am

Hi again,

Two additions I made to my copy of the model might be useful to others.

1. On the RESULTS tab I created a chart of the AVERAGE TAX RATE, similar to the MARGINAL TAX RATES. I located the new chart just below the MARGINAL chart. Its inputs come from the BASE and ROTH tabs which have the values already computed.

2. On the ROTH CONVERSIONS tab, I used column J to record the Roth case AVERAGE TAX RATE (Roth case!U87). This allows one to adjust the conversion amount (Roth conversion!C8..) and quickly get an idea how the average tax rate is affected. I can now easily pick a conversion amount that keeps me within a tax bracket, or within a certain (average) tax rate.
zip605

jdjd2
Posts: 24
Joined: Thu Feb 14, 2013 11:47 am

Re: Retiree Roth Conversion Decision Model

Post by jdjd2 » Mon Mar 04, 2013 1:37 pm

RE: Two additions I made to my copy of the model might be useful to others.

zip605,

How can I get a copy of the model with your modifications? I've basically been doing what you did in your second addition to the base model, but in my own separate (very simple) spreadsheet. It is cumbersome and time consuming though, thus my interest in your additions to this model.

thanks!

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Mar 04, 2013 1:54 pm

I like the idea of people improving the model with ideas that I may incorporate into the official one at sometime. I can see adding an average tax rate line to the existing marginal tax graph might be useful, but I think most people focus solely on filling the marginal brackets in coming up with their Roth conversion amounts, so I'm not sure having the average tax rate on that page would be useful.

But keep the ideas coming!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

zip605
Posts: 15
Joined: Wed Feb 27, 2013 5:51 pm
Location: Chicago area

Re: Retiree Roth Conversion Decision Model

Post by zip605 » Mon Mar 04, 2013 2:19 pm

I don't know of an easy way to make a copy available, but the addition to show the average tax rate is simple enough. I know just enough of excel to be dangerous. I believe what I am doing is correct, but I disclaim any responsibility (standard disclaimer).

I'm using the Model version 13.1.

On the Roth conversion sheet, in cell:

Code: Select all

J8     place formula   ='Roth Case'!$F$87
J9    ='Roth Case'!$G$87
J10  ='Roth Case'!$H$87

and so on.
 


There is probably an easy way to copy the horizontal line 87 of the Roth Case sheet onto column J of the Roth conversions sheet but I just did it brute force.
zip605

djp456
Posts: 1
Joined: Thu Apr 04, 2013 1:44 pm

Re: Retiree Roth Conversion Decision Model

Post by djp456 » Thu Apr 04, 2013 2:19 pm

Is there something in the model to help with the effect of a Roth conversion on the capital gains tax rate? This is a tricky part of Roth conversion. Some of my (long-term) capital gains are not taxed and some are taxed at 15%. Because the taxable amount of capital gains is based on total income (ordinary income plus capital gains), converting money from a tIRA to a Roth will result in more taxable capital gains.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu Apr 04, 2013 7:17 pm

I've tried to keep the tax calculations as simple as possible as I don't really want to try to model the various complexities of the tax law, nor have to update the model to keep current with the changes that occur. (Adding the two brackets for 2013 was a bit annoying, I will admit.) That said, I understand the problem with treating capital gains as marginal rate taxable revenue. I will add it to my list of possible changes for a future update, but anticipate any treatment for cap gains will still be pretty simplistic.

In the meantime, you might just want to try and adjust your other projected income to somehow comprehend your capital gain amounts so that when the marginal rate is applied it approximates the result of the lower rates for the gains.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
LadyGeek
Site Admin
Posts: 40383
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Roth Conversion Decision Model

Post by LadyGeek » Sat Apr 06, 2013 7:05 pm

After a PM exchange, BigFoot48's spreadsheet is now in the wiki. See: Roth IRA conversion (Using a spreadsheet) and Tools and Calculators (Roth conversion calculators)

Comments / questions / suggestions on the wiki article should be posted in the referenced thread: Should I Do Some Roth Conversions?
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

True Wealth
Posts: 2
Joined: Sun May 20, 2012 9:23 pm
Location: NE Ohio
Contact:

Re: Retiree Roth Conversion Decision Model

Post by True Wealth » Thu May 02, 2013 2:53 pm

I am looking forward to using this tool per all the great feedback!

Unfortunately, I cannot get it to download from Dropbox. I have an account, and can see it listed, but when I attempt to download, the new window just shows the "processing" circle going around. Have let it try to download multiple times over multiple hours!

Any advice?

Thanks!
You say, 'If I had a little more, I should be very satisfied.' You make a mistake. If you are not content with what you have, you would not be satisfied if it were doubled. | -Charles Haddon Spurgeon

bayview
Posts: 1280
Joined: Thu Aug 02, 2012 7:05 pm
Location: WNC

Re: Retiree Roth Conversion Decision Model

Post by bayview » Thu May 02, 2013 3:10 pm

I was able to open it just now. I used the link from the post on this thread dated Wed Feb 27, 2013 7:31 pm, which appears to be the most recent version.
The continuous execution of a sound strategy gives you the benefit of the strategy. That's what it's all about. --Rick Ferri

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu May 02, 2013 3:11 pm

I'm sure you will find it useful if you can get it! I just clicked on the link in Post #1 and it downloaded quickly and without a problem, both with and without being logged into Dropbox. Try again and if you're still having problems I'll try to come up with a Plan B.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
LadyGeek
Site Admin
Posts: 40383
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Roth Conversion Decision Model

Post by LadyGeek » Thu May 02, 2013 4:23 pm

I just downloaded it, but this message was at top of the page: "Please enable browser-cookies to use the Dropbox website." Try enabling browser cookies.

Also: Clear your browser cache. Use a different browser, perhaps a different PC.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

gailofiowa
Posts: 1
Joined: Mon Aug 12, 2013 12:06 pm

Re: Retiree Roth Conversion Decision Model

Post by gailofiowa » Mon Aug 12, 2013 12:18 pm

I'd like to suggest that you add a widow/widower status to the social security section of the analysis. As a widow, I am entitled to draw on my late husbands SS record beginning as early as age 60 and then I can switch to a benefit on my own record when it is higher or at a later date of my choosing. The model needs the option to input a benefit beginning at age 60 and the capability to change to a different benefit at a later age. SS can tell me my later age benefits now, but they will have been updated for inflation when I reach those later ages.

I also thought it was a bit confusing to have to set level withdrawals to keep the taxable account positive. Level withdrawals aren't likely to happen. Can't the withdawal amount needed be calculated by year and used in the calculations?

Thanks for developing and sharing this model.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Aug 12, 2013 2:45 pm

Thank you for those suggestions. While the model allows for a surviving spouse to assume the benefits of the deceased spouse, it doesn't address the surviving spouse scenario you described. I will see if I can incorporate that into the model.

I used a fixed withdrawal with an escalation factor to keep the input simple for users. I will look at whether a year-by-year withdrawal could be added, but until and if that is incorporated, I would suggest you just input your desired withdrawal amounts in the appropriate cells on Line 30 - Taxable Withdrawals of the Base Case and Roth Case models, overriding the formulas.

I'm glad you found the model useful.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Tue Aug 13, 2013 1:34 pm

gailofiowa wrote:I'd like to suggest that you add a widow/widower status to the social security section of the analysis. As a widow, I am entitled to draw on my late husbands SS record beginning as early as age 60 and then I can switch to a benefit on my own record when it is higher or at a later date of my choosing. The model needs the option to input a benefit beginning at age 60 and the capability to change to a different benefit at a later age. SS can tell me my later age benefits now, but they will have been updated for inflation when I reach those later ages.
You can accomplish this with the existing model as shown below. Just show an age for your late husband, and his SS benefits starting at age 60 and ending one year before yours start. The model will show an error message as you should select "Single" tax filing status, but that can be ignored. The SS Benefits (lines 19 & 20) in the Base/Roth yearly spreadsheets are normally hidden but you can un-hide them to see how the spouse benefits end and your benefits begin in the ages selected.

Let me know if this solves your request.

Image
Last edited by BigFoot48 on Mon Jul 17, 2017 4:57 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sengsational
Posts: 19
Joined: Sat Feb 05, 2011 8:48 pm

Keeping Taxable Account Positive with B40 zero

Post by sengsational » Sat Aug 31, 2013 1:15 pm

I might just be missing something basic here, but I'm having trouble when attempting to iterate on the B34 through B41 inputs to get non-zero balance in taxable.

From the sample as a starting point, I turn off lump sum events, change B17, 18, and 19 to 150K, 600K, 150K, respectively, and set B32 through B41 and B48 to zero.

Taxable goes negative in 2016. So, based on that, how does one work the inputs to get back to a plan without negative balances, while leaving B40 at zero (this cell has the note "Set to provide adequate funds for living expenses, or set to 0 and allow model to automatically do it")?

The taxable balance gets worse by between 67K and 53K per year. So what do I do with that information? How do I take information from the base case tab and use it to fill-in the B32 through B41 inputs?

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Keeping Taxable Account Positive with B40 zero

Post by BigFoot48 » Sat Aug 31, 2013 5:02 pm

sengsational wrote:Taxable goes negative in 2016. So, based on that, how does one work the inputs to get back to a plan without negative balances, while leaving B40 at zero (this cell has the note "Set to provide adequate funds for living expenses, or set to 0 and allow model to automatically do it")?

The taxable balance gets worse by between 67K and 53K per year. So what do I do with that information? How do I take information from the base case tab and use it to fill-in the B32 through B41 inputs?

What has happened in your example is that you have run out of money in taxable due to the living expenses exceeding taxable income and cash flow into taxable. To fund your taxable account you're going to have to have some withdrawals from your tIRA and Roth. For example, starting with your changes, then putting $50,000 in B34, $25,000 in B37 (pre and post MRD tIRA withdrawals respectively) and $15,000 in B41 starting in 2016 (Roth withdrawals) will provide living expenses until 2034 when funds are exhausted. Cell B40, Taxable Withdrawals, once had a purpose in the model but is now probably unnecessary, but leaving it zero still requires the user to adequately fund the taxable account for living expenses by manipulating withdrawals from IRAs. The model is not designed to do this automatically.

Bottom line, adjust withdrawals as necessary to keep all account balances positive for as long as possible. Hope that helps!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sengsational
Posts: 19
Joined: Sat Feb 05, 2011 8:48 pm

Re: Keeping Taxable Account Positive with B40 zero

Post by sengsational » Sun Sep 08, 2013 2:08 pm

BigFoot48 wrote:Cell B40, Taxable Withdrawals, once had a purpose in the model but is now probably unnecessary, but leaving it zero still requires the user to adequately fund the taxable account for living expenses by manipulating withdrawals from IRAs. The model is not designed to do this automatically.
Thanks for the reply. I guess I was looking for the same kind of functionality as i-orp if I set B40 to zero (automagically suggest tax favored account withdrawls). But I'll get back in there and do what it takes to keep those balances positive. Thanks for sharing your work!

User avatar
HawaiiBrewer
Posts: 71
Joined: Mon Sep 30, 2013 10:50 pm
Location: Hawaii

Re: Retiree Roth Conversion Decision Model

Post by HawaiiBrewer » Fri Nov 01, 2013 9:34 pm

WOW...I just finished Ed Slott's book..."The Retirement Savings Time Bomb and How to Defuse It" and it scared the heck out of me and got me thinking about converting all my 401k to a Traditional IRA then to a Roth.....and was wondering how to make the calculations and a decision about the conversion. I went to the Forum and low and behold I may have found an answer. I have yet to plug and chug into the spread sheet, but I find myself(and wife) in similar situation as BigFoot48...62 yrs old and no heirs.....I figured we'd just leave any remaining funds to a local charity and let them worry about taxes....

Thanks BigFoot48 for your efforts...I'll be playing with the spread sheet and provide feedback.

Aloha,

Paul
If you don't know where you are going, any road will get you there

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Sat Nov 02, 2013 8:12 am

Glad you found it Paul and I welcome any comments and suggestions you might have after using it. In a matter of a few minutes you can get insight into answering the nagging question of "should I do Roth conversions?" using your own personal data.

I'll be updating the tax schedules in January for the new year but don't have any other major changes in the pipeline so the model is fairly mature at this time, subject to future ideas, discoveries, and law changes.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

rogerdub
Posts: 5
Joined: Wed Feb 20, 2013 8:34 pm

Re: Retiree Roth Conversion Decision Model

Post by rogerdub » Fri Nov 15, 2013 9:17 am

Hi BigFoot48,

Thanks for all the work you put into your Roth conversion model. It looks like a very useful tool.
I started looking at the Base Case in Ver. 13.1 to establish a baseline for my personal situation.... retired, age 70, pension and SS, small Roth + large trad. IRA, wife 67. I should have done more Roth converting when I was still working, but it may never be too late.

My pension stops on my demise - I bought a 20-year fixed term insurance policy when I left a direct job and started contract work at age 61. I noticed the input sheet allows one to define an end year (in cell D32) for the "Other income -long term" (i.e. pension). I input an end year, but the Base Case ignored the end date.

So I changed the Base Case cell F22 to the following:
=(+E22*(1+$C22))*IF(F$5-$E$5<='Setup and Input'!$B$8-1,1,0)*IF((F5-'Setup and Input'!$D$32<=0),1,0) and copied it across the years.

This fixed the problem, i.e., it ended the pension at my input guessed of age 86, Do I do this right? Or is this just another "Senior" moment.

Again, thanks for all your efforts. RogerDub

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Fri Nov 15, 2013 3:46 pm

Rogerdub

I'm glad you are finding the model useful. Other Income-Long Term does not use an end date, so you should have found it not ending in both the Base Case and Roth Case numbers on hidden line 22. Other Income-Temporary does have an ending year option and you could have used it for your pension. That said, you did almost make the correct adjustment to the formula for line 22 but it should be (note last two digits): =(+E22*(1+$C22))*IF(F$5-$E$5<='Setup and Input'!$B$8-1,1,0)*IF((F5-'Setup and Input'!$D$32<=0),0,1) which is how the line 23 formulas are structured that uses the ending date feature.

I think for the 2014 update I will give Other Income-Long Term an ending date too to add flexibility to the model, so I do appreciate you bringing this to my attention.

Let me know of any other ideas or problems!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

bruce65
Posts: 1
Joined: Thu Nov 21, 2013 10:32 am

Re: Retiree Roth Conversion Decision Model

Post by bruce65 » Thu Nov 21, 2013 10:43 am

Thanks -- Great tool - definitely provides much more insight and accuracy than the simple calculators I have found.

It seems like the medicare part B and Part D additional payments could have a measurable difference in the Roth conversion plans. For example, for a couple with Par tD, there is an additional "tax" of 296.60/month = 3235.20/year for exceeding $214,000/year of income. Have you considered adding this to the spreadsheet or do you think the affect is too small to be considered?

Thanks

bruce65

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu Nov 21, 2013 12:59 pm

bruce65 wrote:It seems like the medicare part B and Part D additional payments could have a measurable difference in the Roth conversion plans. For example, for a couple with Par tD, there is an additional "tax" of 296.60/month = 3235.20/year for exceeding $214,000/year of income. Have you considered adding this to the spreadsheet or do you think the affect is too small to be considered?

Thanks Bruce. Yes, I've seen discussions of this additional Medicare "tax" for high income individuals but I'm hesitant to add special tax calculations to the model. As it is, the model works by comparing, among other things, the after-tax calculated net income and I think the Medicare cost bump would be the same in both cases most of the time for the high income users, and thus wash out in the comparison result.

However, I think someone could emulate the impact by forcing slightly higher tax rates in the Federal Tax Rate Change table, such as by making the 33% rate 33.1% starting in the first year, or whatever rate results in about a $3,000 higher Fed tax using your example.

Thanks for the suggestion.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Sun Dec 08, 2013 4:22 pm

Alert: Formula error found! The last 9 years out of 40 for the Roth conversion and withdrawal amounts in the Roth Case (lines 38 and 39) are referencing year 31, and not their respective years from the Roth Conversion input table. If you are using those years for either of these functions you can either manually override either or both of these formulas in the Roth Case spreadsheet, or wait for the next update that is coming by January 1. Sorry about that! Update: This error fixed in the current model version as of today, Dec 9.

I discovered this as I'm doing a comparison to i-Orp, and one thing it will do is force a zero or low balance at the end of its forecast period, and this forces use of all the Roth conversion amounts. Mine didn't! Other results are matching up fairly well although Orp does not consider taxable account earnings taxable and my model does, and his Federal tax calculation is a little simpler apparently. I will re-examine the taxable dividend issue but not too excited about being too sophisticated (i.e. accurate) there for maintenance reasons (i.e. keeping up with tax law changes).

Otherwise, there are numerous improvements to the useability and features coming soon! Look for it here.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
Info_Hound
Posts: 240
Joined: Wed Mar 23, 2011 9:47 am
Location: Fort Collins, Colorado

Re: Retiree Roth Conversion Decision Model

Post by Info_Hound » Sat Dec 14, 2013 1:44 pm

Bigfoot48,

You are amazing with your conversion model!

I've just run across this thread and your model. I'm in the early stages of asking the same question...convert or not to a Roth from an IRA account so I downloaded your model. I have already retired.

My situation may be somewhat unique but I have two questions on how to enter specific data. How do I reflect the questions shown below in the data entry worksheet?

1) I am currently collecting SS spousal death benefits (at age 62) and will change over to my own SS benefits (larger $ amount) at age 70 1/2. So my SS benefits will increase at a specific age. I am considered single within the model so I am struggling with how to reflect spousal SS benefits currently being collected when there is no spouse.
2) I have 2 RMD ages I need to reflect - the normal 70 1/2 and a second one at age 78. Both accounts are tax deferred. The second RMD age is for a survivor beneficiary account. The $ sums are different enough in the two accounts that lumping the accounts together as a lump sum entry seems to significantly alter the projections since the RMDs will kick in differently.

Any help would be greatly appreciated. Thank you again for providing such a wonderful way to model the question...convert or not.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Sat Dec 14, 2013 2:29 pm

Info_Hound wrote:1) I am currently collecting SS spousal death benefits (at age 62) and will change over to my own SS benefits (larger $ amount) at age 70 1/2. So my SS benefits will increase at a specific age. I am considered single within the model so I am struggling with how to reflect spousal SS benefits currently being collected when there is no spouse.
2) I have 2 RMD ages I need to reflect - the normal 70 1/2 and a second one at age 78. Both accounts are tax deferred. The second RMD age is for a survivor beneficiary account. The $ sums are different enough in the two accounts that lumping the accounts together as a lump sum entry seems to significantly alter the projections since the RMDs will kick in differently.

Thanks Info! Well, for your first item the model doesn't currently emphasis how to do spousal benefits, although I suspect it may be somewhat common, but it can be done. The second can be done also. I am currently updating it (a significant number of improvements) so I will review both of these to make sure I'm correct.

Updated after review.

You should be able to accomplish both of these items. 1) Insert your age in the Spouse's Age field. Show her benefit starting at the same age, and ending at 70. Start your benefit at 71. The warnings you get from selecting "Single" for taxes are just alerts, and the calculations should work just fine. [See my previous response on a similar question with graphic 2 on Aug 13 2013]. 2) Use the Lump Sum Event - Inherited IRA field B56 and show the age for it to start in Inherited IRA RMD field B12. (In the new version coming out the individual who inherited can be selected, although not applicable in your situation.)

Let me know if that works for you, and I'll continue to review both of them to see if I missed anything, or need to make the spousal benefit a little clearer.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
Info_Hound
Posts: 240
Joined: Wed Mar 23, 2011 9:47 am
Location: Fort Collins, Colorado

Re: Retiree Roth Conversion Decision Model

Post by Info_Hound » Sun Dec 15, 2013 11:18 am

Thanks so much for your reply! I greatly appreciate all you have done and your quick response to my queries.

I've been mulling over the convert/no convert question and had not found a tool that could help point out the pros and cons in the way yours does.

I'll give it a go and see if I can get it to play nicely with my rather odd situation. I'll be following this thread in the future and wish you all the best!

JW-Retired
Posts: 6370
Joined: Sun Dec 16, 2007 12:25 pm

Re: Retiree Roth Conversion Decision Model

Post by JW-Retired » Mon Dec 16, 2013 10:31 am

Applying this excellent Roth Decision spreadsheet has radically altered our Roth conversion plans. We and our heirs send big kudos and thanks to Bigfoot48! :beer

Our situation is probably like a lot of Bogleheads. DW and I were good savers and we are reaching our first RMD year in 2014 with a large TIRA, a taxable account, and a very small Roth. The TIRA is 81% of the total. Income was nearly always above the Roth limits and since I didn't retire until age 69, there was no low tax bracket opportunity to do Roth conversions. We have done a little bit of converting this year (up to the top of our current bracket) but the Roth is still pretty miniscule. Next year is a bracket jump because of RMDs. I was thinking more conversions wouldn't be a good idea, but thanks to this fine Roth Decision Model we can now see there would still be an advantage to them.

We are fortunate that our pension and deferred to age 70 social security will comfortably cover our basic needs. A lot of our savings will end up with our children & grandchildren if things go as we hope. Using the spreadsheet to forecast the TIRA/taxable account/Roth mix at the end of our joint life expectancy for the Decision Model "base case" of no Roth conversions, at conservative investment return rates it ends up 65% taxable/29% TIRA/6% Roth. The TIRA gets depleted by RMDs and the taxable grows because much of the after-tax RMDs get saved. Our base case existing Roth never amounts to much, even though I felt very justified to assume a 1% higher investment return for our Roth due to higher equities. We would have no conceivable need to ever tap the Roth.

Seems to us that our heirs would obviously be better off with a much bigger inherited Roth portion. We are finding we can accomplish that by Roth converting just 20% of the TIRA over the next 5 years. Roughly up to the top of next year's high tax bracket. This makes us end up passing on an estate with 40% taxable/22% TIRA/38% Roth. That seems a good mix but to top it off, the gross estate actually grows to be larger than the do-nothing case by the time we pass our mid-80's. At the end of the 5 years of converting we drop out of our high tax bracket, whereas it takes 12 years to do that if we don't convert. Finally, it's insurance against tax increases. All kinds of good stuff happens. :D

We will probably fiddle around endlessly with permutations but this is the gist of it.

Thanks again Bigfoot!
JW
Last edited by JW-Retired on Mon Dec 16, 2013 9:16 pm, edited 1 time in total.
Retired at Last

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Dec 16, 2013 1:05 pm

You are very welcome JW! I consider the benefit of leaving a Roth to one's heirs the #1 reason for doing conversions. In the sample data I'm putting into the latest version I have a couple with a $1M portfolio and modest living costs doing conversions in their 60's, some withdrawals in their 80's, and ending with a Roth balance at 91 of $1.1M and $800k in other accounts. The heirs getting say $50k a year in tax-free income from that Roth would be a huge benefit. (Let's all hope the tax laws in 20 years will still allow that!)

Be sure to grab the new version, 14.0, when I release it, which will either be Christmas or New Years. It calculates just like the 2013 version, but has many improvements, like additional graphs for observing results on the input page, and additional flexibility such as providing for an increase in living expenses at a future age (assisted living), switching from itemized deductions to standard at a future year, and allowing the option of making a portion of taxable account earnings not subject to income tax, reflecting the reality of taxable earnings.

As usual, I use this model for my own evaluation of doing Roth conversions, and as we have no human heirs, and started SS benefits at 62, it does not show a significant benefit for making conversions in our situation. I might get up the courage and run ours assuming we didn't start SS until 66/70 and see what it says, but I'm not sure I want to know!

As usual, all suggestions and reports of problems found are welcome!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Tue Dec 31, 2013 9:12 am

Time to quit fiddling with the model and release it into the wild.

A new version of the Retiree Roth Conversion model is now available for download. Version 14.0 includes many
improvements and all users should upgrade, but it calculates and displays results much like the previous version so the learning curve is short. All instructions and cell help comments have been reviewed and improved, making it easier to use for those downloading it for the first time.

The list of changes is very long (36 items, see the Readme page), but here are a few of my favorite:

> Added a retired couple's story to illustrate how the Example case data was developed. See the right side on the Setup page.
> Added option to select either yourself or spouse as inheritor of an IRA, and use that person's age for start of RMD.
> Added age data and instructions to clarify how to correctly use the Spouse Age to end spousal SS benefits upon the death of a spouse, and continue the higher benefit.
> Added Future Value Calculator to determine inflation adjusted amounts to use in the Lump Sum Events option.
> Added charts to Setup and Results pages to show status of account balances and other data. Improved existing charts. (Google Spreadsheet has problems with the charts.)
> Added factor to reduce Taxable Account earnings for AGI and tax calculation, to approximate non-taxable income and special dividend tax rates included in Taxable earnings.
> Tested against ORP, FIREcalc, and TurboTax and confirmed reasonableness of results.

I'd also like to remind everyone of the Social Security benefits feature. While a by-product of the model, it does quantify the eternal question of "when should we start SS benefits?" And not surprisingly, for the long-lived that we all hope and plan to be, the answer appears to be "wait until 70" (at least for the Example data couple).

As always, suggestions, ideas and error reports will be appreciated. With this many changes, and despite my best
efforts, some problems, in particular in unusual situations I've not considered, may show up. Let me know immediately if you find one!

Download: See post #1 for current version.

The current version download link will also always be available in post #1.

I would like to thank James Welch, the creator and provider of remarkable ORP, the Optimal Retirement Planner, for his helpful responses to my questions as I worked to reconcile the Retiree Roth model to ORP. He also thought the Retiree Roth model was very useful, and provided a link to this thread in his website. I highly recommend everyone use his excellent tool in their retirement planning. http://www.i-orp.com/
Last edited by BigFoot48 on Thu Mar 06, 2014 10:54 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

JW-Retired
Posts: 6370
Joined: Sun Dec 16, 2007 12:25 pm

Re: Retiree Roth Conversion Decision Model

Post by JW-Retired » Mon Jan 20, 2014 12:16 pm

I'm bumping this because I have found BigFoot48's spreadsheet Roth conversion analysis tool to be a gem, but it appears it's not being used very much. Recent "convert or not convert?" threads seem unaware of it.
JW
Retired at Last

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Jan 20, 2014 12:26 pm

Thanks for the compliment and bump, JW. With 15,413 views as of today, it's certainly getting looked at, but I have no way of knowing how many people have downloaded the model. If I see a thread with a person in my target audience, near or retired, puzzling over the conversion decision I will occasionally recommend it, but don't particularly like self-promotion, and that's another reason I appreciate your post!

BTW, I really like the current version, 14.0, as it shows you in graphs on the input page the impact of return and withdrawal choices as you make them, for either Roth conversion analysis, or Social Security benefits starting year analysis.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
LadyGeek
Site Admin
Posts: 40383
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Roth Conversion Decision Model

Post by LadyGeek » Mon Jan 20, 2014 4:40 pm

May I recommend putting it in the wiki? Consider we have Simba's backtesting spreadsheet and Variable percentage withdrawal, but no Roth conversion spreadsheets.

If you want to edit the wiki to add the spreadsheet yourself, let me know. Otherwise, post content here and we can create an article.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Jan 20, 2014 5:35 pm

Ladygeek, thanks to your efforts some months ago, it is in the Wiki in the Roth Conversion section, http://www.bogleheads.org/wiki/Roth_IRA ... preadsheet , but as it is a spreadsheet, it might be appropriate to at least list it there with a link to the Roth section. As I was recently granted Wiki privileges, I would be glad to insert an appropriate reference as my first Wiki contribution.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
LadyGeek
Site Admin
Posts: 40383
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Roth Conversion Decision Model

Post by LadyGeek » Mon Jan 20, 2014 5:51 pm

Sorry, it's my fault. I never thought to look there. :oops:

I created a new page for you: Retiree Roth conversion decision model

When it's done, we'll put it with the other spreadsheets. (The "Help improve this page" form appears on its own, just ignore it.)
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Tue Jan 21, 2014 10:54 am

Thank you LadyGeek for that jump start on the Wiki page. I've posted a first version for the topic and other Wiki editors can jump in.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
Info_Hound
Posts: 240
Joined: Wed Mar 23, 2011 9:47 am
Location: Fort Collins, Colorado

Re: Retiree Roth Conversion Decision Model

Post by Info_Hound » Tue Jan 21, 2014 12:11 pm

Thanks for posting the 14.0 version. I just grabbed the updated copy.

Agree this is Wiki worthy, I'm glad we are giving it a good home so it is not lost.

User avatar
LadyGeek
Site Admin
Posts: 40383
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Roth Conversion Decision Model

Post by LadyGeek » Tue Jan 21, 2014 9:38 pm

BigFoot48 wrote:Thank you LadyGeek for that jump start on the Wiki page. I've posted a first version for the topic and other Wiki editors can jump in.

I jumped in and made a few editorial updates. It's now in with the rest of the spreadsheets. Retiree Roth conversion decision model

I also updated the Roth IRA conversion page to point to the right spot. See: Roth IRA conversion

Check them over and make whatever changes you want. Note that I borrowed some text from the Roth IRA conversion page.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Thu Mar 06, 2014 10:53 am

A new version 14.1 is now available. See post #1 for current version.

As this version includes two minor calculation corrections, I recommend all users update to it. The changes include:
1. Fixed inherited tIRA RMD calculation to the correct method of using the Single life expectancy table and subtracting one for each subsequent year.
2. Fixed Base Case Special IRA Withdrawal which was not escalating due to a missing cell reference.
3. Added switch to set Roth conversions and withdrawals to zero in Roth Case for ease in running alternative SS benefits calculation.
4. Heading, help and note changes, and simplification of Setup page.

Any comments, suggestions, or corrections are welcome.
Last edited by BigFoot48 on Mon Mar 17, 2014 10:36 am, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
Flobes
Posts: 800
Joined: Tue Feb 16, 2010 12:40 am

Re: Retiree Roth Conversion Decision Model

Post by Flobes » Thu Mar 06, 2014 2:37 pm

BigFoot48 wrote:A new version is now available. https://www.dropbox.com/s/ozx2eg6o66plj ... 0v14.1.xls

... The changes include:
1. Fixed inherited tIRA RMD calculation to the correct method of using the Single life expectancy table and subtracting one for each subsequent year...


Many thanks for this upgrade.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Mon Mar 17, 2014 10:34 am

A new version 14.2 is now available: [Replaced by newer version - see post #1 for link]

Boglehead cdbma found an error in how the "Lump Sum Events" are selected. All three events were being selected based on the "include/exclude-y/n" setting for the Inheritance item. This update fixes that and also a +/- sign issue on the Results page that made the yearly differences for the portfolio balances, and other other summary items, to have the opposite sign from what the Summary shows which might be confusing.

There is no need to upgrade to this version if:
1. You don't use the Lump Sum Events section, or
2. You have all events set to "y", as used in the example settings, or
3. You only use the Inheritance item.

But if you have Inheritance set to "n" but have values in Asset Sale or Expenditure that you want included then you should probably upgrade.

Many thanks to cdbma finding this and promptly letting me know.
Last edited by BigFoot48 on Fri Mar 28, 2014 8:41 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

dstac
Posts: 52
Joined: Sun Jun 02, 2013 5:12 pm

Re: Retiree Roth Conversion Decision Model

Post by dstac » Thu Mar 20, 2014 11:10 pm

Thank you for continuing to update this useful tool over the extended period.

User avatar
BigFoot48
Posts: 2487
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Roth Conversion Decision Model

Post by BigFoot48 » Fri Mar 28, 2014 8:50 am

A new version 14.3 is now available: new update available - see post #1

Boglehead wijwij discovered that using certain withdrawal factors the "Existing Roth" account at the end of the modeling period may have a balance remaining, even though the factors should have produced a zero balance. The formula was fixed to ensure the balance is correctly calculated.

This is a "recommended" update as it may have impacted only a minority of users, and the impact of having a balance would likely be minor when evaluation the Roth conversion or SS benefits decisions. However, combined with the other changes, I think everyone should update.

To make the updating process a little easier, I suggest users open your existing model and the new one, put both on the Setup page, and use Excel "Compare Side-by-Side" feature found in the Windows menu item to show a portion of both pages at once with synced scrolling. This makes it very easy to copy settings from the old to the new. It doesn't work so well on the Roth Conversions page because of the format, but that data is easy to just copy using the copy function.

BH wijwij was also inspired to create two new graphs for the Results page showing the ending balances in all of the accounts for both cases. A great addition in helping to see an overview of all this data and how it differs between the Base and Roth/SS cases. His effort inspired me to create two similar graphs showing earnings in all of the accounts for the modeling period. I think users will find these very interesting. Thanks wijwij! Examples of these graphs are shown below.

I have also added at the bottom of the Results page a section that contains a few of the current cases comparison summaries, all of the nearly 100 input and selection factors used in running the case. There is
lots of room for storing this data so that different scenarios can be stored, compared and re-created.

I just ran a simulation for our own situation using a 0% earnings rate and a 5% inflation rate just to get a
preview of what one Bad Decade may do to our portfolio. Made it one of the stored results in this area as a
reminder.

I also added the Base Case yearly average and marginal tax rates and amounts to the Roth Conversions page tax rate section. If a user just wants to use the Base Case as a forecasting tool this data can help see the impact of taxes when RMD starts, for example, and possibly indicate that earlier IRA withdrawals would reduce overall taxes. I plan to test this myself as my DW may be inheriting a large IRA and our combined RMD is rather large.

As usual, all comments, suggestion, or alerting of possible problems is appreciated.
Image

Image
Last edited by BigFoot48 on Mon Jul 17, 2017 5:00 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

buzzbud
Posts: 2
Joined: Mon Apr 21, 2014 10:06 am

Re: Retiree Roth Conversion Decision Model

Post by buzzbud » Mon Apr 21, 2014 10:48 am

Thank you Bigfoot48 for this truly wonderful spreadsheet. To me, it was like finding the Holy Grail. My wife and I are now able to make rational decisions about our financial future. You saved us from having to pay an extra $50,000 for our 2014 taxes. I've been walking on a cloud since I found your spreadsheet yesterday and entered our data. It is too cool.

I do have a question. On the Base Case Page, row 80 shows Exemptions of 3,950 and row 81 shows Standard Deductions of 6,200. We are married filing jointly so shouldn't that be 7,800 and 12,200? I am using the latest Model v14.3.

Post Reply