Retiree Portfolio Model

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Tue Jan 15, 2019 11:31 pm I can enter some of the information, but I can't see how it all is suppose to tie together , nor fit what goes where with the terms being used , I do see something about the ACA in there, but there is no where to enter the subsidy for example, and any roth conversion I would do now, would greater impact my subsidy , etc etc
I'm sorry you are having first-time user trouble. It does have a lot of data that must be reviewed and the example data replaced with yours. There are 10 sections on the Setup page where data is entered. Start with "1. Ages and Year Factors" and replace the example data with yours. All of the blue colored cells require numbers or a blank. Each entry has instructions which can be viewed by clicking on the title.

Work your way down, one after another inserting your data. You might want to keep it simple to start by clearing unusual items like buying an annuity or entering data on a special event in Section 7. You might also want to use all the existing percentages in "3. Return Rates and Allocation" from the Example data the first time, then go back and edit them after you have the rest of your data entered.

Zephavest also offered excellent advice on automating the model to reduce the need to review year by year and adjust income and expenses to keep the Taxable account from running out of money, generating those warnings you are seeing.

Keep at it and you can get it to work. I also recommend iORP Optimal Retirement Calculator, which is online and super easy to use with excellent portfolio forecasting. Good luck!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
jchris
Posts: 127
Joined: Sun Sep 21, 2014 5:55 pm

Re: Retiree Portfolio Model

Post by jchris »

Did a deep dive into this model for the first time over the last couple of weeks, and I wanted to say thanks to BigFoot48 for developing such a great planning tool. I'm learning a lot about how the whole retirement finance picture fits together using this model.

I do have one question for you. My situation is probably not that unusual - where the initial retirement portfolio would consist mostly of a large traditional 401K/IRA, and relatively smaller Roth and taxable balances. I am using the model to examine the effects of using Roth conversions during retirement to avoid large RMDs and the consequent large tax bills. In the Roth Conversion Analysis section of the results tab, when the model compares the Base case (i.e., no Roth Conversions) with the Full case (Roth conversions), the final portfolio balances being compared seem to be an apples to oranges comparison. In other words, the final portfolio balance for the Base case consists primarily of a large traditional IRA balance, whereas the final portfolio balance for the Full case consists primarily of a large Roth conversion IRA balance. The model calculates the difference between these two numbers, and if the Base case final portfolio balance is larger, produces a "don't convert" conclusion. But the balance in the Full case portfolio, which is mostly Roth funds, is actually worth significantly more than an equivalent final balance in the Base case portfolio, because Uncle Sam actually "owns" a chunk of those Base case funds. So it seems to me that if my Full case (Roth conversion) portfolio balance is less than but anywhere close to the Base case final balance, it still makes sense to convert.

I just want to make sure that I am understanding what the model is doing here - there's no adjustment happening to account for the greater value of the Roth funds in the Full case, correct?

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

jchris wrote: Wed Jan 16, 2019 8:21 am The model calculates the difference between these two numbers, and if the Base case final portfolio balance is larger, produces a "don't convert" conclusion. But the balance in the Full case portfolio, which is mostly Roth funds, is actually worth significantly more than an equivalent final balance in the Base case portfolio, because Uncle Sam actually "owns" a chunk of those Base case funds. So it seems to me that if my Full case (Roth conversion) portfolio balance is less than but anywhere close to the Base case final balance, it still makes sense to convert.

I just want to make sure that I am understanding what the model is doing here - there's no adjustment happening to account for the greater value of the Roth funds in the Full case, correct?
There's no adjustment or even calculation of the future benefit of having those tax-free funds in the Roth conversion account. The "don't convert" message is just a simple comparison of the ending balances, without considering the future impact of taxes. Perhaps the message should be less opinionated!

[addition] It might be interesting for those contemplating doing conversions to do two models for one of their heirs - one model of them getting a traditional IRA and one of them getting a Roth conversion IRA. See how their potential financial life might turn out either way.

Glad you find the model useful, as I think a major benefit of it is to focus attention on many of the financial events that retirees may face.
Last edited by BigFoot48 on Wed Jan 16, 2019 9:33 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
jchris
Posts: 127
Joined: Sun Sep 21, 2014 5:55 pm

Re: Retiree Portfolio Model

Post by jchris »

BigFoot48 wrote: Wed Jan 16, 2019 8:38 am
jchris wrote: Wed Jan 16, 2019 8:21 am The model calculates the difference between these two numbers, and if the Base case final portfolio balance is larger, produces a "don't convert" conclusion. But the balance in the Full case portfolio, which is mostly Roth funds, is actually worth significantly more than an equivalent final balance in the Base case portfolio, because Uncle Sam actually "owns" a chunk of those Base case funds. So it seems to me that if my Full case (Roth conversion) portfolio balance is less than but anywhere close to the Base case final balance, it still makes sense to convert.

I just want to make sure that I am understanding what the model is doing here - there's no adjustment happening to account for the greater value of the Roth funds in the Full case, correct?
There's no adjustment or even calculation of the future benefit of having those tax-free funds in the Roth conversion account. The "don't convert" message is just a simple comparison of the ending balances, without considering the future impact of taxes. Perhaps the message should be less opinionated!

Glad you find the model useful, as I think a major benefit of it is to focus attention on many of the financial events that retirees may face.
Thanks for your quick reply, which confirms what I thought I was seeing.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

Zephavest wrote: Wed Jan 16, 2019 5:51 am
stvyreb wrote: Tue Jan 15, 2019 11:31 pm I am getting various negative amount errors, but don't see what is causing it, nor what it's even referenced is "negative" sigh

Alert! Portfolio has negative beginning yr balances in one or more accounts for 38 years
Alert! Base case has negative balances for 38 years
On your Setup tab, look towards the top of the page, make sure your settings are:

Minimum taxable balance $$$
Use auto withdrawals? y

I only use Auto Withdrawals to avoid balances going negative, simplifies the tool use. But other reasons for negative balances are you run out of money, or your expenses exceed your assets and income.

Hang in there, keep plugging, the tool does have a high learning curve but you can do it and benefit from the insight you gain into your own personal finances. You really don't want to outsource this.
I want to believe, just not sure how to progress on the curve without any documentation beyond what's there ......

so I have been putting my numbers into the document sample, the Minimum taxable balance said "10,000" I changed it to "$$$" and auto withdrawals was "n" , I made it "y" however, the negative balances didn't disapear


I feel like I am missing the big picture, on what the tool is suppose to do, or which accounts or what "beginning yr" would mean ...

etc thx though
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

stvyreb wrote: Wed Jan 16, 2019 2:51 pm
so I have been putting my numbers into the document sample, the Minimum taxable balance said "10,000" I changed it to "$$$"
$$$ was just representative for whatever dollar value you want, if you want say, $50,000 to be the minimum your taxable account ever drops to just enter 50,000
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

BigFoot48 wrote: Tue Jan 15, 2019 4:44 pm
stvyreb wrote: Tue Jan 15, 2019 3:15 pm Hello, thx for the RPM SS, however, I find it rather complex how to start or if it is even intended for an early retiree?

eg I am in my mid 50s, I have 2 parents IIRA RMDs every year, in Summary Section 1

it says "Inherited IRA at age 77" then the blue box I have entered 55 my age , It looks like this section can't be used for IIRA unless one is > 77 years old when they get the IIRA ?

Error: Inherited IRA RMD age not +1 year > inherited age

is there perhaps a longer written step-by-step how to use the SS, other than the 1st tab ??
Go to Section 7: Special Events and there are places for two inheritances. Enter your inherited IRAs here with their current balances and your current age, replacing the 77 and 72 used in the Example data. In Section 1 enter your current age +1. Unfortunately this may not result in a correct RMD calculation as the model is not programmed to model the RMD for an existing inherited IRA. [Edited from original comment.]

All the instructions for using the model are in cell comments on the Setup page. They describe how each input setting is used and what data should be entered.
well I cleared the sample entries, and started with no entries, and did as you suggested .... starting with 7. Special Events -> future events -> 1 & 2 include in portfolio "y" taxable account blank (as no idea what this means) , then entered the two IIRAs dollar amounts, and my current beneath both

return to section 1 -> IRA RMD ages and owners and entered my age +1 and owner y , however as soon as I do that I see the same negative beginning balance error beneath Modeling options

I have minimal taxable balance set to $5 and use auto withdrawals set to 'n'

the rest of the document has not been altered ......

to the right of modeling options portfolio in 2058 says #N/A
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Wed Jan 16, 2019 4:27 pm
well I cleared the sample entries, and started with no entries, and did as you suggested .... starting with 7. Special Events -> future events -> 1 & 2 include in portfolio "y" taxable account blank (as no idea what this means) , then entered the two IIRAs dollar amounts, and my current beneath both

return to section 1 -> IRA RMD ages and owners and entered my age +1 and owner y , however as soon as I do that I see the same negative beginning balance error beneath Modeling options

I have minimal taxable balance set to $5 and use auto withdrawals set to 'n'

the rest of the document has not been altered ......

to the right of modeling options portfolio in 2058 says #N/A
In the Special Event section"Taxable Account" means cash, flowing into or out of the taxable/brokerage account. So if someone inherits $100,000 in a brokerage account containing bonds, money market funds, etc. and $10,000 in gold coins it goes there.

Try using 1,000 as the minimum taxable balance as rounding may be causing a problem at $5. Set auto withdrawals to "Y' and see if that helps. You might also set expenses to $1000 and go up from there.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

The issue with the IIRAs is in Setup-> 1 -> Inherited IRA at age "53"(from section 7) ; if I enter age 54 -59 I get an error message at the bottom of "modeling options" complaining about negative balances, I go to look at "Summary tab" and account balances stop at age 54

If I enter "60" then the Summary section goes for the full 40 years with no Red error message about negative balances.....

I started receiving IIRA RMDs a few years ago, so I believe you, BF said, there is no capacity to input past events so what should I do in my case ?

Also does this model predict what the RMDs will be at age 71 (if I enter that based on my life expectany at age 71 ?)
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

stvyreb wrote: Thu Jan 17, 2019 6:27 pm
I started receiving IIRA RMDs a few years ago, so I believe you, BF said, there is no capacity to input past events so what should I do in my case ?
For my IIRA, I entered the current value as though I inherited it this year. Then set RMDs to start next year. It is not exact, but close enough for planning purposes.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

munemaker wrote: Fri Jan 18, 2019 8:01 am
stvyreb wrote: Thu Jan 17, 2019 6:27 pm
I started receiving IIRA RMDs a few years ago, so I believe you, BF said, there is no capacity to input past events so what should I do in my case ?
For my IIRA, I entered the current value as though I inherited it this year. Then set RMDs to start next year. It is not exact, but close enough for planning purposes.
are you in your 50s? it seems to be OK if I enter age 60 and above, but anything less and I get the portfolio negative balance for "38 years" ....... which isn't explicable to me, while all assets would be depleted in 1 -2 years including taxable , since seems like it would only be effecting the IIRA portion which year I input

sorry to say, I don't see there is any curve to learn, without better documentation or some sort IMO , think I have to give up, though I probably Do need to assess Roth Conversions impact, If there is no one to hire to assist with it, then I guess it's only for certain people

I've also got various EE and I bonds that have deferred interest and come due at various times, which seem to have no spot

and I have ACA subsidies ..... sigh
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

A couple of notes...
1) yes, the spreadsheet can have a steep learning curve, because it does have lots of options. But I found I learned a lot by trying out things and looking to see what it was doing. So I encourage you to keep trying, and coming back for help if needed, just for the education factor.

2) The notes and examples are generally pretty good, but you may have to read them carefully, and on several pages to fully understand the connections of how one input influences another.

Finally, it appears you have encountered a situation that is a bit beyond what the model is set up for. Specifically, I believe the issue is that if you look at the life expectancy tables on the "Tax Tables" tab, you'll see that it starts at age 60. However, if you were to edit that table so it has the proper range (e.g starting at 50), I believe it will correct the problem. I did try a dummy case where I set the starting age to 50 and it showed your error; then I edited that table and those error messages went away.
The reference for the tables content is listed in the TaxTable tab.

Hope this helps!
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

stvyreb wrote: Fri Jan 18, 2019 6:11 pm
are you in your 50s? it seems to be OK if I enter age 60 and above, but anything less and I get the portfolio negative balance for "38 years" ....... which isn't explicable to me, while all assets would be depleted in 1 -2 years including taxable , since seems like it would only be effecting the IIRA portion which year I input
I am mid 60s.
stvyreb wrote: Fri Jan 18, 2019 6:11 pm I've also got various EE and I bonds that have deferred interest and come due at various times, which seem to have no spot
and I have ACA subsidies ..... sigh
You do not input individual securities like your EE and I bonds. You input categories...Cash, Bonds, Stocks. Your savings bonds would be cash. Add them into your cash, calculate the weighted average interest rate for all your cash, and use it.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sandramjet wrote: Fri Jan 18, 2019 7:31 pm A couple of notes...
1) yes, the spreadsheet can have a steep learning curve, because it does have lots of options. But I found I learned a lot by trying out things and looking to see what it was doing. So I encourage you to keep trying, and coming back for help if needed, just for the education factor.

2) The notes and examples are generally pretty good, but you may have to read them carefully, and on several pages to fully understand the connections of how one input influences another.

Finally, it appears you have encountered a situation that is a bit beyond what the model is set up for. Specifically, I believe the issue is that if you look at the life expectancy tables on the "Tax Tables" tab, you'll see that it starts at age 60. However, if you were to edit that table so it has the proper range (e.g starting at 50), I believe it will correct the problem. I did try a dummy case where I set the starting age to 50 and it showed your error; then I edited that table and those error messages went away.
The reference for the tables content is listed in the TaxTable tab.

Hope this helps!
These are excellent suggestions and the solution to his primary problem. The life expectancy table is indeed the source of the errors. There is an alternative table which can be selected, which starts at age 50, but no obvious instruction to notify users of this option. When selected the error goes away.

Another source of his frustration is that he is using LibreOffice Calc. When I look at RPM using the current 6.1 version 99% of cell notes and instructions can't be viewed. This program use to be a good substitution but 6.1 is not. I'll try an earlier version to see if its any better. UPDATE: v5.4 and v6.07 don't show comments either. Apache OpenOffice Calc 4.1.6 is worse, displaying no comments.

I will expand the life tables to an earlier starting age to prevent this problem for others.

Thanks to all for the help in solving this issue.
Last edited by BigFoot48 on Sat Jan 19, 2019 10:36 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

I just opened a test copy of my current version in Google Sheets and all comments are visible.

This maybe an interim solution, but Google sheets does not support macros.
scottadelman
Posts: 9
Joined: Fri Mar 13, 2015 12:30 pm

Question about Special Expenditures

Post by scottadelman »

I just wanted to confirm that my following assumption is correct. In the setup page there are only two cells for special expenditures. I want to model a few more. Can I just add them to the RESULTS tab, Line #15: Special Expenditures and Donations? If that is correct do I need to do anything else after entering them or will they just be carried over to all calculations?

Thanks again for any feedback. And Thanks again for this incredible tool.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Question about Special Expenditures

Post by BigFoot48 »

scottadelman wrote: Sun Jan 20, 2019 9:31 pm I just wanted to confirm that my following assumption is correct. In the setup page there are only two cells for special expenditures. I want to model a few more. Can I just add them to the RESULTS tab, Line #15: Special Expenditures and Donations? If that is correct do I need to do anything else after entering them or will they just be carried over to all calculations?

Thanks again for any feedback. And Thanks again for this incredible tool.
Thanks. No, you should not add them to the Results page. That is all calculated data. There are several areas on the Details page that allow additional amounts to be added to the model if the Setup page can't provide for them:

Line 141 - User input expenses - add to or reduce expenditures for any of the years. [USE THIS FOR YOUR NEED]
Line 179 - User input income, credit and adjustments - add to or reduce Federal Adjusted Gross Income for items not provided for in the model.
Line 209 - User input income, credit and adjustments - add to or reduce State AGI
Line 267 - Excluded foreign income or other - add to or reduced Modified AGI for foreign income, etc.

And also note that the two Special Events - Asset Sales entries can be used for expenditures by entering negative numbers.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

These are excellent suggestions and the solution to his primary problem. The life expectancy table is indeed the source of the errors. There is an alternative table which can be selected, which starts at age 50, but no obvious instruction to notify users of this option. When selected the error goes away.

Another source of his frustration is that he is using LibreOffice Calc. When I look at RPM using the current 6.1 version 99% of cell notes and instructions can't be viewed. This program use to be a good substitution but 6.1 is not. I'll try an earlier version to see if its any better. UPDATE: v5.4 and v6.07 don't show comments either. Apache OpenOffice Calc 4.1.6 is worse, displaying no comments.

I will expand the life tables to an earlier starting age to prevent this problem for others.

Thanks to all for the help in solving this issue.
I did notice G Sheets shows the cell comments, I could use LibreOffice Calc side by side , but maybe bit tedious, cheapest M$ office I see that is Not a subscription based service is $130 ...... unless there is some other workaround to obtain Office legally , that will work the the RPM.

Appreciate that you checked other versions of LibreOffice, and I take it that there are zero, no versions that show the comments .... ?
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

I checked all the versions of Windows spreadsheet programs and here's the current status, in order of best to worse cell comment etc. viewing. Viewing of these comments is critical to using the program the first time.

Excel 2003-2016 - best program. If using 2003 the 2007 format xlsm will open and work with a warning and must be saved in xls format.

Free programs:
Excel Web Application - cell comments viewable in side window, macros don't work, graphs work, model must be set to be "editable"
WPS Kingsoft Spreadsheet v2013 - comments viewable, graphs show but are slightly changed, macros not supported in free version.
Google Docs Spreadsheets - comments viewable, graphs show with no labels, macros not supported
LibreOffice Calc - cell comments/instructions not visible, graphs show (axis labels may not), most macros work
Apache OpenOffice Calc - cell comments/instructions not visible, some graphs fail, macros work but titles not formatted

WPS is a Chinese company, I believe. The 2013 version will allow just loading of the spreadsheet program from their bundled programs. The 2016 version didn't have that option so it gives you a Word and another program too.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Thanks again for your continuing hard work on this spreadsheet.

I do have several comments / suggestions on the current release:

• On the Setup sheet Sec 1, the cell D35 message logic still uses "your age - spouse age >= 10" whereas it should just be 'greater than' 10. This may not be the only place where this occurs; I just noticed it here.

• On the Setup sheet Sec 10, I suggest that the three "Taxes By Bracket..." tables should be titled "Federal Taxes by Bracket...", and the B303 title should be something like "Total Fed Taxes". I realize this should be easy to keep in mind, but when I once lost track there were some anxious moments.

• Also in that section, a column showing total Fed + State taxes would be useful. The Federal dominates the calculations but this is what we'll have to cough up along the way.

• On the Tax Tables sheet, I think the popup for Cell H337 has the table numbers switched and should instead be: "Factors for a married couple using either Table III (most common) or Table II (spouse more than 10 years younger), based on which table is selected to be used."
Last edited by Boggedown on Wed Jan 23, 2019 3:28 pm, edited 1 time in total.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

BigFoot48 wrote: Tue Jan 22, 2019 12:39 am I checked all the versions of Windows spreadsheet programs and here's the current status, in order of best to worse cell comment etc. viewing. Viewing of these comments is critical to using the program the first time.

Excel 2003-2016 - best program. If using 2003 the 2007 format xlsm will open and work with a warning and must be saved in xls format.

Free programs:
Excel Web Application - cell comments viewable in side window, macros don't work, graphs work, model must be set to be "editable"
WPS Kingsoft Spreadsheet v2013 - comments viewable, graphs show but are slightly changed, macros not supported in free version.
Google Docs Spreadsheets - comments viewable, graphs show with no labels, macros not supported
LibreOffice Calc - cell comments/instructions not visible, graphs show (axis labels may not), most macros work
Apache OpenOffice Calc - cell comments/instructions not visible, some graphs fail, macros work but titles not formatted

WPS is a Chinese company, I believe. The 2013 version will allow just loading of the spreadsheet program from their bundled programs. The 2016 version didn't have that option so it gives you a Word and another program too.
Sadly, something is seriously dodgy about that WPS software, both in Windows and Linux they don't use https for their downloads, and even if you agree to allow the http download it fails .....

Is there any hope that the RPM might work in LibreOffice someday ....... I'm not really sure how to purchase old versions of MS Office like 2013 , and prefer to use legal secure software esp. when entered my private financials :)

Failing finding 2013 for a reasonable price just for the use of RPM, I guess I can take a stab, at reading the comments in "Sheets" and entering info in LOffice version 6.x.something
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boggedown wrote: Wed Jan 23, 2019 1:34 pm I do have several comments / suggestions on the current release:
Thanks for those suggestions and corrections to the alert message in Sec 1 and the cell message on the Tax page. I'll fix all those and implement the title improvements and think I can add a state tax total to the Federal recap area.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Wed Jan 23, 2019 3:19 pm
Sadly, something is seriously dodgy about that WPS software, both in Windows and Linux they don't use https for their downloads, and even if you agree to allow the http download it fails .....

Is there any hope that the RPM might work in LibreOffice someday ....... I'm not really sure how to purchase old versions of MS Office like 2013 , and prefer to use legal secure software esp. when entered my private financials :)

Failing finding 2013 for a reasonable price just for the use of RPM, I guess I can take a stab, at reading the comments in "Sheets" and entering info in LOffice version 6.x.something
I downloaded the WPS 2013 free-trial version here: http://www.ksosoft.com/product/spreadsheets-pro Just remember to de-select loading the Word program and one other during the install. I believe this is a safe program but admit I'd prefer an American product and wish LibreOffice worked better.

Unfortunately there's no easy way to show all the instructions contained in cell comments on the visible pages.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

.
To folks dealing with non-Excel spreadsheet issues:

It's fine to take a look at RPM in LibreOffice or whatever. You needn't get it fully working for your case - just see what the workbook wants as input and what it will output. What you see is what you get: RPM will deliver the results indicated. But you can't just diddle around with it; all factors matter given the long time periods RPM addresses. It will take many hours to become comfortable with what you're seeing. Think about that.

Then if you do decide to use RPM, buy MS Office. Its cost is negligible compared to what you will be putting into RPM and what RPM will do for you. I'm cheap myself, but this is not the place to save $130!

Plus if we all man up and get on a standard platform, RPM's author can focus on that - to everyone's benefit. (IMHO he should simply make Excel a requirement / this is $5K+ software free / end of story.)
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Cosmetic issue: The "Sources of Earnings" chart on Results sheet near cell J21 has a fixed rather than auto vertical scale. As as result it will overflow at higher values. The same chart on the Setup sheet is fine.

-----------------
The point raised on 16 Jan by jchris had long been on my mind too. Paraphrased: the balance in the Full case portfolio, which will be mostly Roth funds, is actually worth significantly more than an equal final balance in the Base case portfolio, because of taxes eventually due on the latter.

I've now tried to quantify this effect. In my case the RMDs from my Trad IRA will go into the Taxable account. I've established a sub-balance in Taxable to account for this influx and the market gains on it. From that I subtract the total RMDs up to that point. The remainder is the market gains that are due to the RMDs alone. I then assume that 20% of these gains will have to go for taxes, and subtract that much from the nominal Base portfolio result. The 20% value is just a guess at my heir's average tax rate over the conversion (RMD) years they might have available.

This of course improves the apparent benefit to Roth conversion, which I had previously both over- and under-estimated. The effect is substantial in my case - if I've done it right, of course... Any input on this is welcome. And naturally I suggest a calc like this as an enhancement to RPM someday.

Thanks again for the great tool.
Last edited by Boggedown on Sat Feb 02, 2019 8:57 pm, edited 2 times in total.
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

A couple issues with the Setup sheet Sec. 10 operation. Neither affects the results.

---------
Setup!C253 note has incomplete sentence: "being converted[]...]"; should be something like "being converted would have a negative balance".

---------
Logic for Setup cell A258 conversion adjustment "A" flag may need a fix. It shows an adjustment needed if the C258 entered conversion amount exceeds the H258 source IRA end of year balance. So the flag will appear if you convert more than about half the IRA in the first year. When this happens, the Setup C253 Adjusted total still equals the C254 Entered total ie. no adjustment was actually made.

The flag logic for subsequent years makes sense, and I think the A258 condition is only a "first year of calcs" startup difficulty. As such there may be no ideal solution. Looking at the Details!F311 and related values the best simple choice may be changing the Setup!A258 flag logic:

From: =IF(AND($H258<$C258,$C258>0),"A","")
To: =IF(AND(Details!F311<$C258,$C258>0),"A","")


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

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boggedown wrote: Tue Jan 29, 2019 4:42 pm A couple issues with the Setup sheet Sec. 10 operation. Neither affects the results.
Thanks very much. I will address all those problems in the next release, coming soon, and see about adding a simple Roth conversion benefit to heirs analysis to the Results page.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

stvyreb wrote: Fri Jan 18, 2019 6:11 pm I've also got various EE and I bonds that have deferred interest and come due at various times, which seem to have no spot
and I have ACA subsidies ..... sigh
You do not input individual securities like your EE and I bonds. You input categories...Cash, Bonds, Stocks. Your savings bonds would be cash. Add them into your cash, calculate the weighted average interest rate for all your cash, and use it.
[/quote]

1) if I enter the saving bonds as "cash" , how would I account for the big tax hit, at age 62-66 on the substantial deferred interest likely put push me into a higher federal bracket ?
a) ......and the fact that the interest would not be taxable on state returns
b) perhaps this is too complex for the model?

2) is there a section for allocation for the Inherited IRAs ?

3) any possibility of including ACA subsidy trade-offs in future RPMs , this is actually a major factor for myself in not harvesting gains, nor considering Roth conversions ..... at some point still seems way to complex, and Am still thinking if there were tax planners or financial planners whom were also using the RPM .... we might be able to speak the same language when comparing options for "alpha" type stuff ....

eg https://www.financial-planning.com/news ... -tax-alpha Allan Roth
Mooger
Posts: 2
Joined: Sat Feb 02, 2019 6:44 am

Re: Join the Wiki!

Post by Mooger »

Golly! What a find!
I am in Canada, so am doing translations on the fly, but very useful tips.
I wonder if anyone has/will do a Canadian version of the Retiree Portfolio Model spreadsheet? (we don't have IRA or ROTH, but other acronyms haha).
User avatar
LadyGeek
Site Admin
Posts: 95703
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Welcome! I moved your post into the support thread.

May I suggest you ask this question in our sister Canadian forum? Financial Wisdom Forum? We collaborate on many projects.

Also see: finiki, the Canadian financial wiki

Disclaimer: I'm a member of both forums.
Wiki 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.
Mooger
Posts: 2
Joined: Sat Feb 02, 2019 6:44 am

Re: Retiree Portfolio Model

Post by Mooger »

Excellent, thank you, have gone fishing in finiki too, great resources.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

stvyreb wrote: Thu Jan 31, 2019 12:59 pm
stvyreb wrote: Fri Jan 18, 2019 6:11 pm I've also got various EE and I bonds that have deferred interest and come due at various times, which seem to have no spot
and I have ACA subsidies ..... sigh
You do not input individual securities like your EE and I bonds. You input categories...Cash, Bonds, Stocks. Your savings bonds would be cash. Add them into your cash, calculate the weighted average interest rate for all your cash, and use it.

1) if I enter the saving bonds as "cash" , how would I account for the big tax hit, at age 62-66 on the substantial deferred interest likely put push me into a higher federal bracket ?
a) ......and the fact that the interest would not be taxable on state returns
b) perhaps this is too complex for the model?

2) is there a section for allocation for the Inherited IRAs ?

3) any possibility of including ACA subsidy trade-offs in future RPMs , this is actually a major factor for myself in not harvesting gains, nor considering Roth conversions ..... at some point still seems way to complex, and Am still thinking if there were tax planners or financial planners whom were also using the RPM .... we might be able to speak the same language when comparing options for "alpha" type stuff ....

eg https://www.financial-planning.com/news ... -tax-alpha Allan Roth
1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.

2) The rate of return for Inherited IRAs 1 & 2 is the same as Existing IRAs 1& 2 respectively. So no, you don't enter an allocation for the inherited IRAs. RPM uses the same allocation as existing IRAs.

3) Obviously this would be up to the creator, but it would seem to go against the intent of RPM in that RPM is a modeling program, not an optimizer. You can change your inputs on the SETUP sheet and see projections for ACA on the DETAILS sheet, lines 171 & 172.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

munemaker wrote: Sat Feb 02, 2019 10:46 am 1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet.

etc etc
Thanks for providing that information to another user, it's accurate and should be a very helpful.

All users of RPM should understand that it's designed for a majority of retirees and near-retirees, and cannot contain all the thousands of situations that might face this group in assets, income, expenses and taxes. So use the settings on the Setup page as best you can and also make use of the yearly manual input lines on the Details page for expenses line #141, Federal AGI adjustments #179, state AGI #209 and Federal MAGI #267. The Special Events section can also be used to adjust income and expenses, such as for ACA subsidies.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Info for other users on determining Roth conversion amounts. References are to RPM Rev. 19.0.

tl;dr: An Excel technique to automatically establish factors for a Roth conversion. The effect of these refinements over manual entries will be small compared to that of uncertainties in other assumptions and estimates in your model. So... for interest only.

--------------------
I have always used the RPM Setup Sec. 10 'Entered yearly anounts' option to choose Roth conversion amounts (set cell E244 to 'e'), picking values for cells E258 etc that would cause Federal taxes to just hit 100% of one bracket without straying into the next higher one. That's simply a rule of thumb but is reasonable for manual use. I ignored the 'Calculated' amounts method because in my case it has only three variables to work with: first-year Starting value, Annual percent change, and End Age. I had assumed that this limitation would prevent it from bettering hand-crafted entries, and a few manual trials of 'Calculated' seemed to show that.

However, Microsoft Excel has a powerful optimizing add-in called 'Solver'. It also has 'Goal Seek' but AFAIK that can only handle one variable so won't apply to this case. Solver can theoretically handle large sets of variables, and provides several advanced methods of optimizing a 'results' cell by manipulating such sets of input cells.

It finally occurred to me to try Solver on the 'Calculated' method, and I was surprised and impressed to find that the results were better than my manually 'Entered' values. Again, these are small differences at a distant time, but it's easy to try and interesting to see.

Here's how to do it:
  • Install the Excel 'Solver' add-in - google for instructions on that. Another good reason to buy Excel and not scrape by on VisiCalc.

    Enable RPM's Roth conversion 'Calculated amounts' mode (set cell E244 to 'c').

    Set your Start Age. I have no choice in this value but if you do, see the note later on.

    Open Solver (Excel top menu | Data | Solver).

    At the bottom, select the 'Evolutionary' method.

    At the top, tell Solver to maximize Portfolio Ending Balance w Conversion (cell I247).

    Below that, tell Solver to change variable cells for Starting amount, Annual Escalation, and End Age (cells E247,E248,E250) aka Roth_Convert, Roth_Esc, Roth_End.

    These input cells must have high and low bounds set; that's done in the middle window. For cell E247 I suggest a max of your entire Trad IRA starting value, and a minimum of 5% of that value. For cell E248 I suggest a max of 0.2 (a 20%/yr increase) and a minimum of -0.2 (a 20% decrease). Set cell E250 to allow for an approx 20-year span.

    Click on 'Solve'.
Excel should within a few minutes return practical results in cells E247, E248, E250. In my case these resulted in conversion amounts that, though they sometimes jumped into a higher bracket than I would have chosen with 'Entered' amounts, gave a superior final balance.

If Solver instead reports that it can't find a solution, or produces some obviously bogus values, you can try tweaking it. Open the Options dialog and tab to the Evolutionary settings. There 'Mutation Rate' can be increased up to a maximum of 1.0, and for 'Random Seed' you can choose any positive integer. Allowable solution time can also be increased, here and in the All Methods tab. More info on these is available on the web, especially from Frontline Systems who developed the Solver module. Also note that if you have flexibility in your Start Age you can use "E247:E250" as a range above, and perhaps do even better.

Once you have a good set of Calculated numbers you can manually clean them up by rounding to the nearest dollar or 0.01%, while keeping an eye on final balance to make sure you don't affect that. Don't be surprised if small changes here actually improve the outcome, as the Evolution solver can miss those. You can tighten the convergence settings and re-run the Evolutionary method, or the GRG Nonlinear method, starting with these first Evolutionary results, just to see what happens.

It won't be of much value to re-optimize / change these Calculated values unless some other RPM factor has altered significantly. Solver will remember its last scenario so you can run it again very easily if necessary. The Calculated conversion amounts can be seen in Details sheet cells F128-AS128. These will at least give you a reasonable set of starting points and time span for manual 'Entered' values before you decide on funding an real conversion.

Of course Solver can be theoretically be used to directly calculate optimum 'Entered' amounts for cells E258 etc, hoping to find the maximum possible final balance. However, even its Evolutionary engine is not sufficiently robust to do this reliably in a straightforward manner. It will most often report failure or give useless results. That's because the RPM model is necessarily highly and sharply nonlinear. There are techniques to help Solver with this (such as input scaling) but they have performed only a little better for me and are not easy to explain. Still, everyone's situation is different and it can be worth trying Solver on the 'Entered' values directly just out of interest. The Calculated method above is completely automatic after the first optimization so may be best most of the time.
Last edited by Boggedown on Sun Feb 03, 2019 2:05 pm, edited 4 times in total.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.

3) re: ACA You can change your inputs on the SETUP sheet and see projections for ACA on the DETAILS sheet, lines 171 & 172.
thanks for the reply so
re: 1) the problem is there is a mix of EE & I Bonds, with varying interest rates, and as I understand it, these rates are not fixed , I am about 20 years into their 30 life, so 10 years further, and as I understand it , it would be a wild guess to predict their Final Interest lump sum amount....... I could enter today's Interest Sums , which is multitudes higher than their face values .....

3) hmm, on SETUP what would I be changing, I see only 5. Expenses -> ACA 10 years qualify , currently on DETAILS my lines go from 166 to 179 , skipping the numbers in between ....
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

I seem to have entered what I know, where it should go ......

but, it is interesting if I enter $55,000 for expenses my models goes "negative" in 3 years.
However, if I enter $50,000 it will last for 40 years ......... somehow that doesn't seem that it could be accurate ?


or perhaps I've set the model up incorrectly in the #1 section ? thx for being patient with any dumb or evolving questions
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Sat Feb 02, 2019 9:43 pm re: 1) the problem is there is a mix of EE & I Bonds, with varying interest rates, and as I understand it, these rates are not fixed , I am about 20 years into their 30 life, so 10 years further, and as I understand it , it would be a wild guess to predict their Final Interest lump sum amount....... I could enter today's Interest Sums , which is multitudes higher than their face values .....

3) hmm, on SETUP what would I be changing, I see only 5. Expenses -> ACA 10 years qualify , currently on DETAILS my lines go from 166 to 179 , skipping the numbers in between ....
Sometimes wild guesses are required when no better data is available.

Some rows on the Details and other pages are normally hidden as they are supporting or seldom used data, but can be revealed by activating the "hidden rows" "show" macro at the top of the page. If macros are not available, select the row before and after the hidden rows, right-click, and select "unhide".
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

stvyreb wrote: Sat Feb 02, 2019 11:35 pm I seem to have entered what I know, where it should go ......

but, it is interesting if I enter $55,000 for expenses my models goes "negative" in 3 years.
However, if I enter $50,000 it will last for 40 years ......... somehow that doesn't seem that it could be accurate ?


or perhaps I've set the model up incorrectly in the #1 section ? thx for being patient with any dumb or evolving questions
Do you have the "Use Auto Withdrawals" option in Section 1 selected? If not, you must make sure you have enough withdrawals planned or income coming in to cover expenses.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

sandramjet wrote: Sun Feb 03, 2019 7:59 am
stvyreb wrote: Sat Feb 02, 2019 11:35 pm I seem to have entered what I know, where it should go ...... but, it is interesting if I enter $55,000 for expenses my models goes "negative" in 3 years. However, if I enter $50,000 it will last for 40 years ......... somehow that doesn't seem that it could be accurate ?
or perhaps I've set the model up incorrectly in the #1 section ? thx for being patient with any dumb or evolving questions
Do you have the "Use Auto Withdrawals" option in Section 1 selected? If not, you must make sure you have enough withdrawals planned or income coming in to cover expenses.
So, I had left that on the default Modeling Options-> general settings-> use auto withdrawals -> "n"
Minimum taxable balance is 10,000
etc

Reading the inline popups for auto withdrawals
----
Enter a 'y' to have the model automatically calculate withdrawal amounts (in addition to any manual withdrawals entered) from tax-advantaged accounts to fund deficits in the Taxable account. Enter a 'n' to only use your input withdrawal amounts.

Default is 'n'.

Set a minimum balance higher than $0 for the Taxable account, otherwise it will default to $0 when automated setting is used.

Withdrawals are made in this order, with each account depleted to $0 before the next account is used:
1. Traditional IRAs
2. Inherited IRAs
3. Roth IRA 1
4. Roth Conversion IRA (Full case only)

Limitation: Because of circular calculation issues, this feature uses the prior years' tax expenses in determining the current year withdrawal amounts. This makes it more of an approximation and may result in a Taxable account balance that is not equal to the selected Minimum Balance.
-----

and Minimal taxable balance says:

------
Enter the minimum balance to maintain in the Taxable account when using the automated IRA withdrawal feature. 10,000 is recommended.

Because of the approximation used in this calculation, it's better to keep this amount high ($10k+), otherwise negative taxable account balances are more likely to occur in some years.
-------

So, the model is designed to determine Earnings from growth, dividends and interest vs. living expenses year by year and if auto withdrawals is not set to "y" ? .......... I guess I was interpreting negative balances as net worth balances ..... my mistake.

However, I can't follow the language eg what are the "manual withdrawals entered" ? It seems to be saying that it is going to take extra IRA withdrawals (beyond my RMDs) to cover inadequate taxable Earnings from growth, dividends and interest ?

In my case I have only Inherited IRA RMDs and don't plan on other RMDs till age 71 15 years from now ..... so why wouldn't the model use my taxalbe balances instead of "tax-advantaged accounts"


like I said "dumb" questions :)

PS: what are "prior years tax expenses"
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

munemaker wrote: Sat Feb 02, 2019 10:46 am
1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.
Thinking about this a little more, you can prevent the savings bond interest income from showing up on your state income tax by entering the savings bond interest as a negative number on line 209 on the DETAILS page. That's more elegant than changing the formula as I had suggested earlier.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Sun Feb 03, 2019 2:51 pm However, I can't follow the language eg what are the "manual withdrawals entered" ? It seems to be saying that it is going to take extra IRA withdrawals (beyond my RMDs) to cover inadequate taxable Earnings from growth, dividends and interest ?

In my case I have only Inherited IRA RMDs and don't plan on other RMDs till age 71 15 years from now ..... so why wouldn't the model use my taxalbe balances instead of "tax-advantaged accounts"

PS: what are "prior years tax expenses"
The purpose of the automatic feature is to keep the Taxable account above zero or the user specified amount. If turned on, it kicks in if the user fails to use adequate yearly withdrawals from IRAs, i.e. "manual withdrawals", to fund the taxable account. It draws money from IRAs in the order listed to do this.

I recommend users try to not use this feature and instead learn how much they will have to earn and/or withdrawal each year to keep the Taxable account funded so that it can provide for annual living expenses. It may be an enlightening experience.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

munemaker wrote: Sun Feb 03, 2019 2:59 pm
munemaker wrote: Sat Feb 02, 2019 10:46 am
1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.
Thinking about this a little more, you can prevent the savings bond interest income from showing up on your state income tax by entering the savings bond interest as a negative number on line 209 on the DETAILS page. That's more elegant than changing the formula as I had suggested earlier.
OK, so shall I use today's value of the Interest, as esp for the I bonds, I believe there is no way to predict what further interest will be received till when they begin maturing in about 10 years from now.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

stvyreb wrote: Sun Feb 03, 2019 11:36 pm
munemaker wrote: Sun Feb 03, 2019 2:59 pm
munemaker wrote: Sat Feb 02, 2019 10:46 am
1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.
Thinking about this a little more, you can prevent the savings bond interest income from showing up on your state income tax by entering the savings bond interest as a negative number on line 209 on the DETAILS page. That's more elegant than changing the formula as I had suggested earlier.
OK, so shall I use today's value of the Interest, as esp for the I bonds, I believe there is no way to predict what further interest will be received till when they begin maturing in about 10 years from now.
As far as predicting the future of interest rates, the same applies to other investments too. No one knows what the future holds.

Make your best estimate and use it. I used the forecast of investment returns on Research Affiliates web site. I don't know a lot about interest on savings bonds. For interest rates on government guaranteed bonds, I would use the inflation rate as a base. So if you use say, 2.1% as the rate of inflation, then maybe 2.5% nominal (0.4% real) for a blend of short/intermediate government bonds. I would guess that savings bonds might be similar. Someone else may have a better suggestion.
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

I've run across a difference between the Details and Base case. In the latter, the formula in cell AS205 has the extra term "-AS169". That doesn't appear to be intentional...

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boggedown wrote: Mon Feb 04, 2019 11:50 am I've run across a difference between the Details and Base case. In the latter, the formula in cell AS205 has the extra term "-AS169". That doesn't appear to be intentional...

Thanks
Removed. Thanks. Only impacted the year 40 state income tax calculation. I ran the example data on a 40 year period and the default and base cases were identical, so it's unlikely there are any other such problems.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

BigFoot48 wrote: Sun Feb 03, 2019 3:08 pm
stvyreb wrote: Sun Feb 03, 2019 2:51 pm However, I can't follow the language eg what are the "manual withdrawals entered" ? It seems to be saying that it is going to take extra IRA withdrawals (beyond my RMDs) to cover inadequate taxable Earnings from growth, dividends and interest ?

In my case I have only Inherited IRA RMDs and don't plan on other RMDs till age 71 15 years from now ..... so why wouldn't the model use my taxalbe balances instead of "tax-advantaged accounts"

PS: what are "prior years tax expenses"
The purpose of the automatic feature is to keep the Taxable account above zero or the user specified amount. If turned on, it kicks in if the user fails to use adequate yearly withdrawals from IRAs, i.e. "manual withdrawals", to fund the taxable account. It draws money from IRAs in the order listed to do this.

I recommend users try to not use this feature and instead learn how much they will have to earn and/or withdrawal each year to keep the Taxable account funded so that it can provide for annual living expenses. It may be an enlightening experience.
so, if there isn't enough taxable income generated, to cover the choice of expenses/year, then (even for a 55 year old), the model is going to pull money from the traditional IRA 1st ? ;

I can't quite follow, what "negative balances" is being referred to, or what is the goal ? I would think that reducing my expenses to 30,000 would not have the same effect as raising my expenses to $60,000, but both complain of negative balances...
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Tue Feb 05, 2019 2:11 amso, if there isn't enough taxable income generated, to cover the choice of expenses/year, then (even for a 55 year old), the model is going to pull money from the traditional IRA 1st ? ;

I can't quite follow, what "negative balances" is being referred to, or what is the goal ? I would think that reducing my expenses to 30,000 would not have the same effect as raising my expenses to $60,000, but both complain of negative balances...
Look at the yearly balances of your accounts at the top of the Details page and you will see which ones have negative balances. You must have more withdrawals from your accounts than you have money going into them. Figure that out and those negative balance messages should disappear.

The goal in the model, as in life, is to not have any over-withdrawn accounts. You might want to use ORP which is fully automated: https://www.i-orp.com/bequest/index.html
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

BigFoot48 wrote: Tue Feb 05, 2019 6:48 am
stvyreb wrote: Tue Feb 05, 2019 2:11 amso, if there isn't enough taxable income generated, to cover the choice of expenses/year, then (even for a 55 year old), the model is going to pull money from the traditional IRA 1st ? ;

I can't quite follow, what "negative balances" is being referred to, or what is the goal ? I would think that reducing my expenses to 30,000 would not have the same effect as raising my expenses to $60,000, but both complain of negative balances...
Look at the yearly balances of your accounts at the top of the Details page and you will see which ones have negative balances. You must have more withdrawals from your accounts than you have money going into them. Figure that out and those negative balance messages should disappear.

The goal in the model, as in life, is to not have any over-withdrawn accounts. You might want to use ORP which is fully automated: https://www.i-orp.com/bequest/index.html
the setup sheet says Full: Negative for 38 years

from what I can see on the top of the details page, there IS my inherited IRA which I expect to draw down to zero and does on that page in year 2041 , which seems to me to be 17 years, though I do see further down

under Taxable Account a line item
Inherited IRA RMD
which is zero'd out after 2 years, perhaps that is where the "38" is coming from ,

bottom line though, seems as you say, this spreadsheet must be for professionals and niche hobbiests , think I'll have to give up ....

regards
gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Social Security taxation intricacies

Post by gponym »

Hi Bigfoot,

First, thanks for this great RPM tool. The Roth conversion question is a big one in my case and I'm happy to find a tool as sophisticated as yours. It's extra generous that you still improve and support it.

I'm a newbie and beg pardon if this question has already been answered.

Does RPM handle the rather byzantine rules that determine what percent of a taxpayer's Social Security income gets included into taxable income?

(I recently read a pretty good Michael Kitces blog on the topic: https://www.kitces.com/blog/the-taxatio ... -increase/.)
mkedst
Posts: 10
Joined: Sun Oct 07, 2018 11:14 am

Re: Retiree Portfolio Model

Post by mkedst »

gponym wrote: Fri Feb 08, 2019 7:39 pm Does RPM handle the rather byzantine rules that determine what percent of a taxpayer's Social Security income gets included into taxable income?
Yes, the calculate can be seen on the Details tab, section "SS benefits taxation calculation".
Post Reply