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
Zephavest
Posts: 89
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Sun Feb 02, 2020 8:17 am

sandramjet wrote:
Sun Feb 02, 2020 12:24 am
 - Do your different scenarios show clear result differences?
Yes they do and even if the percentage is small, that is just fine with me. If you can keep your scenarios always having a positive balance at the end it is a good plan, regardless of percentage remaining. I want to assure we or my surving spouse do not run out of money.
sandramjet wrote:
Sun Feb 02, 2020 12:24 am
 - How big a difference do you consider significant? 1%, 10% or ??
See answer number 1.
sandramjet wrote:
Sun Feb 02, 2020 12:24 am
 - Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?
You are correct, the big picture should contain many more factors then just the ending balance. In one of my early plans I would start my pension at age 63 and Social Security at age 68. I did this in light of aligning my Roth conversions to max out the 12%/15% bracket. In a later plan I noticed at age 71 my RMD along with my Social Security and Pension were fully supporting our living expenses, which also had the unintended consequence of minimizing the amount of Roth conversions I could do while keeping within the 12%/15% tax bracket. In studying a solution for this I realized if I delayed starting my pension from 63 until 65 and delayed starting my social security from 68 to 70 I could increase the amount of my Roth conversions between age 62 and 70, before the RMD kicks in, which also helped reduce the RMD amount and the associated taxes. Further as part of this step I realized it is better for us to live off of our existing Roth accounts during these intervening years in order to maximize IRA distributions and keep them maxed at the 12% bracket.

Another deciding factor was that by maxing out the Roth conversions I was minimizing the remaining IRA balance and reducing the inheritance taxes our daughter would have to pay on the inherited IRA. This plan has been working will for several years now and has the added benefit when the just passed "SecureAct" hit that causes the loss of the inherited stretch IRA's, it turns out my plan in minimizing the inherited IRA balance is spot on and my daughter still will be better off with reduced IRA taxes, even though she has draw down the inherited Roth within ten years.

Regardless of my annual cash flow needs, pre-Pension and pre-Social Security, I max out my Roth Conversions as it allows my to pay the lowest marginal tax rate. Then I withdraw annual income, as needed, from the Roth account. As far as cash needed to pay for the Roth conversion taxes for the first two years of retirement it came from the taxable account, but as that was spent down I now pay for it out the Roth Distributions I take for annual income. This is contrary to what some use as the "wise tale rule of thumb" that you can only do Roth Conversions if you have separate cash to pay the taxes. That it a misconception, If I did not do a Roth Conversion, and just took IRA Distributions for annual income, where is the cash coming from to pay the taxes on that? You can see I'm paying the exact same taxes on the money, regardless whether the 1099-R says "IRA Distribution" or "Roth Conversion", same balance, same taxes due.

smitcat
Posts: 4865
Joined: Mon Nov 07, 2016 10:51 am

Re: Retiree Portfolio Model

Post by smitcat » Sun Feb 02, 2020 8:43 am

sandramjet wrote:
Sun Feb 02, 2020 12:24 am
I have been using this tool for a few years now, and it is a great tool (Thank you Bigfoot48)!

My problem is not in executing the model but in interpreting the results :?

As I evaluate the results of different simulations, I find that the results often come down to very small differences over 30+ years. For example, when I look at whether to do Roth conversions or not, I find the final portfolio values vary by less than two percent. Given that many of the inputs you have to make (like estimated returns, expenses, inflation, etc, etc.) I figure that small a difference means it really doesn't matter which choice I make.

In some ways, it is reassuring to suggest that I'll be OK regardless of choice, but at the same time it doesn't really help me make a choice on what to do :confused

I'm curious what others have found in terms of:
- Do your different scenarios show clear result differences?
- How big a difference do you consider significant? 1%, 10% or ??
- Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?

Thanks for any thoughts


I'm curious what others have found in terms of:
- Do your different scenarios show clear result differences?
Yes - very clear
- How big a difference do you consider significant? 1%, 10% or ??
Some are over $400K of "spendable" dollars over the full cycle
- Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?
We see no merit in measuring just ending balances - we only find value in measuring 'spendable' dollars after tax between us and our heirs for the full cycle.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Feb 02, 2020 10:26 am

gts1952 wrote:
Sat Feb 01, 2020 8:48 pm
I'm running the model using LibreOffice. I've been playing with the "20a" version for a week or so, and noticed that my NYS income tax had gone to $0. I figured out that this happened when I changed the state move age to zero from the initially populated value of 84. If I change it to an age value of 69 or greater, the approximate proper income tax value comes up. I downloaded the "20b" version just now, did no value changes other than the future state tax move age. If I change the move age to zero, the state tax value in 1st year increases from $1100 to $1900. I just noticed that the age should be blank or populated not zero, however when I change it from age 84 to a blank, the first year tax goes from 1100 to 900. It looks like there may be something inconsistent in the formulas, or possibly it's application based. As a side note, there are 8 income tax brackets in NYS. I can possibly fix this myself, suggesting it as part of a future update.
Excellent bug find and report! There was indeed a programming error in the Federal Tax Exempt income inclusion in state AGI which resulted in the future state factor being used for the current state if the future state year was blank. The Example data which uses a future state didn't reveal this, and likely most users don't have Federal exempt income, so it hasn't been noticed.

Version 20.0c that fixes this is now available. Only users that have Federal Tax Exempt income may need to upgrade. If no future state year is used then having the same "Tax-exempt investment income taxable in state" in the future state setting will also eliminate the problem. Link: https://www.dropbox.com/s/vrz9xd69c0rxk ... .xlsm?dl=0

I want to keep the model as simple as possible so the three state brackets should be used as best they can for states with more brackets.

Thanks for reporting this state tax calculation problem.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Delphic
Posts: 1
Joined: Mon Feb 03, 2020 6:48 pm

Re: Retiree Portfolio Model

Post by Delphic » Mon Feb 03, 2020 7:08 pm

Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.

sandramjet
Posts: 287
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet » Tue Feb 04, 2020 12:41 am

Delphic wrote:
Mon Feb 03, 2020 7:08 pm
Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.
While I have no opinion on the VLOOKUP/HLOOKUP issues, I certainly disagree about the base case not being of any value. I look at the differences in various sections between current and base for lots of things. And roth/non roth is not the only thing I am looking at. Looking at SS differences, comparing various adjustments in the detail/base cases (like modifying AGI, Expenses, etc... ) I would be disappointed if the base disappeared.

User avatar
Zephavest
Posts: 89
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Tue Feb 04, 2020 9:36 am

Delphic wrote:
Mon Feb 03, 2020 7:08 pm
Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.
Hi Delphic,

Welcome to the Bogleheads forum and RPM since this is your first post maybe you did not look at the history or read the entire thread? Countless other users have found the selfless contributions from BigFoot48 invaluable in that he has provided his personal model to the community. Not only did he develop it but he has continuously maintained it since 2012. He has added a number of new features at users suggestions when they may help the community at large. As an Excel user myself for over 30 years I know there are multiple ways to achieve the desired outcome within Excel. Like any design decisions, the creator, designer, engineer, etc. makes the best use of the tool as he/she sees fit. It is good to review some of the key information in the first post of this thread:
BigFoot48 wrote:
Thu May 31, 2012 7:13 pm
The model I'm providing is my personal model that I use periodically, entering my own portfolio, estimate of earnings, expenses and anticipated changes we think will happen, to look at the conversion decision. (I've got another five years until RMD and the related taxes kick in and any conversion benefit goes away for good.)  In addition to the conversion analysis, and since the model includes Social Security benefits as an income source, I did add one feature that I can't use - a way to quantify the "when should I start Social Security benefits" decision. (We started at 62.)  That feature also quantifies the SS decision process, and could be useful to many.

So, as my personal model, use it at your own risk.  It may help you or it may not.  Your situation may be more complex and the model be of only limited value.  Your data may cause it to make bad calculations.  It may indicate a conversion advantage because you used a 12% earnings rate.  Lots of things can be indicated from the data entered, so use it as a start in your conversion decision, or SS benefits decision, and when in doubt, create a model! (Above updated Dec 2013)
So when you find RPM no longer meets your personal needs or you need some additional information outside of RPM, take BigFoot's advice and "create a model!" of your own.That is what I did. After years of using RPM, modifying it as I wished for my own personal needs, there came a day when I wanted some additional information to meet my own personal needs so I created my own side model for those things. Did I have to carefully study the formula's to understand them, sure, but I learned a lot along the way.

Please try to start with compliments and not complaints, telling the author his design decisions are terrible would indicate that this may not be the tool for you.
BigFoot48 wrote:
Mon Feb 11, 2019 11:18 am
If a user is having too much difficulty with it, I suggest the excellent ORP model, which is very easy to use and provides nearly identical results in calculating future portfolio results. https://www.i-orp.com/ZERO/index.html

gts1952
Posts: 5
Joined: Thu Jan 28, 2016 10:00 pm

Re: Retiree Portfolio Model- Details line 171

Post by gts1952 » Tue Feb 04, 2020 12:22 pm

I do not understand the function of line 171 in the "Details" tab, and what inputs are being used to generate the number. In my case it generates a negative number that effectively offsets most of my (fully) taxable investment income. In an unmodified 20c version, it offsets $4000 of the $9600 taxable account earnings. The line description reads "Less other non-taxable earnings in taxable". On the same line there's a cell with a percentage value in it. The "Detective" tool did not yield any useful insights.

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

Re: Retiree Portfolio Model- Details line 171

Post by BigFoot48 » Tue Feb 04, 2020 12:57 pm

gts1952 wrote:
Tue Feb 04, 2020 12:22 pm
I do not understand the function of line 171 in the "Details" tab, and what inputs are being used to generate the number. In my case it generates a negative number that effectively offsets most of my (fully) taxable investment income. In an unmodified 20c version, it offsets $4000 of the $9600 taxable account earnings. The line description reads "Less other non-taxable earnings in taxable". On the same line there's a cell with a percentage value in it. The "Detective" tool did not yield any useful insights.
See the "taxable account adjustment" in the 8. Income Taxes section on the Setup page for the source and explanation of that adjustment to gross taxable earnings.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Joe3zz
Posts: 9
Joined: Thu Sep 12, 2019 9:54 am

Re: Retiree Portfolio Model

Post by Joe3zz » Tue Feb 11, 2020 1:07 pm

Thank you Bigfoot for your work on this tool!

Hoping someone can help/clarify. I entered our data in the current version of the Retiree Portfolio Model. My wife and I are essentially retired and have a 50/50 portfolio., age 63, no social security yet.

Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?

sandramjet
Posts: 287
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model - Base Model Adjustments

Post by sandramjet » Wed Feb 12, 2020 10:26 pm

There is an input for "optional base case adjustment" on lines 219-224 of the setup page... However, I'm not quite sure I understand how to use it properly.

The note says:
Optional Base Case Adjustment: Making Roth conversions may require additional IRA withdrawals to fund Federal taxes. Use these optional adjustments to reduce the withdrawals in the comparison (base) case for these extra withdrawals. This will provide a more accurate comparison in evaluating the benefit of Roth conversions as those higher withdrawals are not needed if conversions are not being made.
This is a rarely used feature.

But it is not clear to me what the adjustment amount should be. I'm assuming that if you want to convert say 10K from traditional to roth, but will incur a $2k tax hit that must also come out of the traditional, then you would input a -2k value for that parameter since you wouldn't be pulling out as much if you have the tax money come from somewhere else. Is this a correct interpretation, or do I have the sign wrong (ie should use +2k since you would end up with 2k more staying in the traditional) .... or have I completely missed the boat on when/how to use this?

A related question is when you say this is a "rarely used feature" is that comment because people generally have $ for taxes elsewhere? In my case, I have exhausted all of my taxable accounts and have only traditional and Roth accounts to pull from.

Thanks!

RangeleyLake
Posts: 12
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Thu Feb 13, 2020 7:30 am

Joe3zz wrote:
Tue Feb 11, 2020 1:07 pm
Thank you Bigfoot for your work on this tool!



Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?
HI Joe3zz
I have been wondering the same thing. I did come across on the setup page section 8 - Taxable Account Adjustment on lines 275-276 that I think answers this questions. I have just started using this area. This might be the option we are both looking for.
I am still struggling as to what numbers to plug in there but I think this works. I am still not totally comfortable with that little section. (I am still playing around with it)
RangeleyLake

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Feb 13, 2020 11:48 am

Joe3zz wrote:
Tue Feb 11, 2020 1:07 pm
Thank you Bigfoot for your work on this tool!

Hoping someone can help/clarify. I entered our data in the current version of the Retiree Portfolio Model. My wife and I are essentially retired and have a 50/50 portfolio., age 63, no social security yet.

Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?
The Taxable Account Adjustment section in the Setup "8 Income Tax" section includes a method of having distributed dividends and capital gains taxed at the marginal rate.

Use this setting to enter a capital gains amount that will result in an approximate capital gain tax amount being calculated after applying the bracket marginal rate.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model - Base Model Adjustments

Post by BigFoot48 » Thu Feb 13, 2020 12:01 pm

sandramjet wrote:
Wed Feb 12, 2020 10:26 pm
There is an input for "optional base case adjustment" on lines 219-224 of the setup page... However, I'm not quite sure I understand how to use it properly.

The note says:
Optional Base Case Adjustment: Making Roth conversions may require additional IRA withdrawals to fund Federal taxes. Use these optional adjustments to reduce the withdrawals in the comparison (base) case for these extra withdrawals. This will provide a more accurate comparison in evaluating the benefit of Roth conversions as those higher withdrawals are not needed if conversions are not being made.
This is a rarely used feature.

But it is not clear to me what the adjustment amount should be. I'm assuming that if you want to convert say 10K from traditional to roth, but will incur a $2k tax hit that must also come out of the traditional, then you would input a -2k value for that parameter since you wouldn't be pulling out as much if you have the tax money come from somewhere else. Is this a correct interpretation, or do I have the sign wrong (ie should use +2k since you would end up with 2k more staying in the traditional) .... or have I completely missed the boat on when/how to use this?

A related question is when you say this is a "rarely used feature" is that comment because people generally have $ for taxes elsewhere? In my case, I have exhausted all of my taxable accounts and have only traditional and Roth accounts to pull from.

Thanks!
I suspected back in 2013 based on threads that most people doing Roth conversions pay the taxes from existing funds, and having to withdraw more to pay taxes was an issue. If additional IRA withdrawals are required to pay the taxes, this adjustment to the Base case was an attempt to negate the impact of those withdrawals so that just the impact of the conversions could be studied.

I don't know if this works or anyone has used it. There's never been a question about it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by LadyGeek » Fri Feb 14, 2020 9:06 am

I removed an off-topic post. As a reminder, see: General Etiquette
We expect this forum to be a place where people can feel comfortable asking questions and where debates and discussions are conducted in civil tones.

...At all times we must conduct ourselves in a respectful manner to other posters. Attacks on individuals, insults, name calling, trolling, baiting or other attempts to sow dissension are not acceptable.
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.

Joe3zz
Posts: 9
Joined: Thu Sep 12, 2019 9:54 am

Re: Retiree Portfolio Model

Post by Joe3zz » Sat Feb 15, 2020 1:37 pm

Thank you both for the referral to section 8. Like RangeleyLake, I will need to figure out what to plug in there, but it is a start.



RangeleyLake wrote:
Thu Feb 13, 2020 7:30 am
Joe3zz wrote:
Tue Feb 11, 2020 1:07 pm
Thank you Bigfoot for your work on this tool!



Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?
HI Joe3zz
I have been wondering the same thing. I did come across on the setup page section 8 - Taxable Account Adjustment on lines 275-276 that I think answers this questions. I have just started using this area. This might be the option we are both looking for.
I am still struggling as to what numbers to plug in there but I think this works. I am still not totally comfortable with that little section. (I am still playing around with it)

RangeleyLake
Posts: 12
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Mon Feb 17, 2020 1:32 pm

BigFoot48 wrote:
Thu Feb 13, 2020 11:48 am
The Taxable Account Adjustment section in the Setup "8 Income Tax" section includes a method of having distributed dividends and capital gains taxed at the marginal rate.

Use this setting to enter a capital gains amount that will result in an approximate capital gain tax amount being calculated after applying the bracket marginal rate.
Thank you BigFoot48 for confirming what I thought would show LTCG and Qualified Dividends. When I started to play around in this section I stumbled across a couple of "#name?" error message has I was following the flow. It was on the Detail tab and Base tab on cell C175 ("IRA withdrawals" row) and C177 ("Inherited IRA withdrawals" row) the same cell reference for both tabs . This only happens when I select Y on "Use Automatic Withdrawals?" on the set up tab. On these cells it had a formula of =IF(Automate="y",Auto withdrawals included,""). When I change the formula on these cells and added quotation on "Auto withdrawals included" such as =IF(Automate="y","Auto withdrawals included","."), that resolved that error and gave me the message of "Auto withdrawals included" in on all of these cells. I was wondering if these cells was for only information purpose and/or if those cells are being used for any other part of this model which would impact any other results. Thanks again for a model that gives us a complete picture of our financial decision. ( I am not sure if you received a PM from me on this question sometime ago but was not sure if I was successful sent it. If I was successful in sending that PM sorry for the duplicate question.---still learning on how to use this forum.
RangeleyLake

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 17, 2020 1:42 pm

RangeleyLake wrote:
Mon Feb 17, 2020 1:32 pm
It was on the Detail tab and Base tab on cell C175 ("IRA withdrawals" row) and C177 ("Inherited IRA withdrawals" row) the same cell reference for both tabs . This only happens when I select Y on "Use Automatic Withdrawals?" on the set up tab. On these cells it had a formula of =IF(Automate="y",Auto withdrawals included,""). When I change the formula on these cells and added quotation on "Auto withdrawals included" such as =IF(Automate="y","Auto withdrawals included","."), that resolved that error and gave me the message of "Auto withdrawals included" in on all of these cells. I was wondering if these cells was for only information purpose and/or if those cells are being used for any other part of this model which would impact any other results.
Thanks for that report. That's just an alert message and has no impact on calculations. I suspect few people use the automated withdrawal feature or un-hide those lines and I seldom test the auto-withdraw option anymore so those errors were not noticed. I will fix it on the current release. No need for users to update.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Post Reply