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: 90
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: 5610
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: 2793
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: 292
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: 90
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: 2793
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: 13
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: 292
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: 2793
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: 2793
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: 62995
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: 13
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: 2793
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

armadillo1955
Posts: 2
Joined: Mon Mar 09, 2020 11:30 pm

Re: Retiree Portfolio Model

Post by armadillo1955 » Tue Mar 10, 2020 12:10 am

I've been playing with the spreadsheet for a couple of weeks now. I'm no spreadsheet wizard but I can appreciate a tool like this and the many many hours needed to create, maintain and support it. I'm new to this forum and this is my first post. This feels like a silly question but I have scoured the tool and can't seem to find an easy answer. I have input all my financial data and played with several different "setup" scenarios. I can make sense of how changing different setup values impact the result. The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM. I'm using LibreOffice ver 6.3.5.2. Thanks in advance for any help with what I hope is a simple question.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Mar 10, 2020 9:30 am

armadillo1955 wrote:
Tue Mar 10, 2020 12:10 am
The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM.
I'm glad you are finding the model useful. The answer is that RPM is not designed to make or offer investment and portfolio withdrawals decisions for the user. It requires the users to apply the knowledge they have on these and many more financial issues, learned here at Bogleheads (highly recommended) and elsewhere, to model their portfolios over the selected period.

Users to need to learn the benefits and impacts of different account withdrawals over future years first, then use RPM to model them. While i-Orp is excellent and I highly recommend it, its modeled withdrawal strategy might not be optimum for some users and having a good education in these matters before using any model will be of great benefit.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

armadillo1955
Posts: 2
Joined: Mon Mar 09, 2020 11:30 pm

Re: Retiree Portfolio Model

Post by armadillo1955 » Tue Mar 10, 2020 12:10 pm

BigFoot48 wrote:
Tue Mar 10, 2020 9:30 am
armadillo1955 wrote:
Tue Mar 10, 2020 12:10 am
The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM.
I'm glad you are finding the model useful. The answer is that RPM is not designed to make or offer investment and portfolio withdrawals decisions for the user. It requires the users to apply the knowledge they have on these and many more financial issues, learned here at Bogleheads (highly recommended) and elsewhere, to model their portfolios over the selected period.

Users to need to learn the benefits and impacts of different account withdrawals over future years first, then use RPM to model them. While i-Orp is excellent and I highly recommend it, its modeled withdrawal strategy might not be optimum for some users and having a good education in these matters before using any model will be of great benefit.
Thanks for the clarification and advice, much appreciated.

sonar230
Posts: 11
Joined: Fri Jul 20, 2018 5:59 am

Re: Retiree Portfolio Model

Post by sonar230 » Sun Mar 29, 2020 6:59 pm

I am considering retiring early (age 55) and, therefore, would need to sequence the withdrawals such that taxable accounts are drawn down first in early retirement until age 60 instead of the IRA accounts. Is there a way to force the sequence of withdrawals and also have the tool recommend the annual amounts for Roth conversions? The "Use automatic withdrawals" choice doesn't seem to force the I have entered all of my data, but I cannot seems to find a way to sequence the withdrawals. Has anyone figured this out?

Thanks,
sonar230

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Mar 30, 2020 8:39 am

sonar230 wrote:
Sun Mar 29, 2020 6:59 pm
I am considering retiring early (age 55) and, therefore, would need to sequence the withdrawals such that taxable accounts are drawn down first in early retirement until age 60 instead of the IRA accounts. Is there a way to force the sequence of withdrawals and also have the tool recommend the annual amounts for Roth conversions? The "Use automatic withdrawals" choice doesn't seem to force the I have entered all of my data, but I cannot seems to find a way to sequence the withdrawals. Has anyone figured this out?

Thanks,
sonar230
The model automatically pays the expenses input by the user and calculated taxes out of the taxable account. The taxable account increases based on the annual growth rate entered along with any income and special events (inheritances, asset sales, etc.) the user inputs. The user has to use the methods provided to set the Roth conversion amounts, usually optimized to stay within a lower tax bracket. RPM doesn't recommend conversion amounts.
I don't recommend use of the automatic withdrawal feature as it's just a simply way to use account funds but is not optimized for the best sequence of withdrawals in any year. Good luck in your early retirement!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

sonar230
Posts: 11
Joined: Fri Jul 20, 2018 5:59 am

Re: Retiree Portfolio Model

Post by sonar230 » Mon Mar 30, 2020 8:38 pm

Thanks. I will take another look. I really appreciate all the effort you have put into this tool.

Thanks,
sonar230

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Apr 15, 2020 8:19 am

An updated version of RPM is now available. This update adds an option to set 2020 calculated RMD amounts to zero that may be required in any of the two IRAs or two inherited IRAs. This can be selected via a setting in the Modeling Options section on the Setup page. This is a result of a provision in the Cronos19 legislation.

A related change improves the Taxable Account Adjustment section in 8. Income Taxes to better explain this feature and to calculate the Base case adjustment separately to calculate it correctly if the 2020 RMD is set to zero.

Version 20.1 of the Retiree Portfolio Model model is available as of April 15, 2020 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/h5eozrgkj1y0r ... .xlsm?dl=0

This update is only needed for users subject to RMDs in 2020 who want to see the results of not taking the RMDs. Please note: a test of this using data that has a 2020 RMD revealed that selecting to not take it 1) increased the RMD for 2021 and following years, which is subject to tax at the highest marginal rate, resulting in 2) increased Federal taxes and 3) a reduced portfolio balance at the end of the modeling period. So, if correct, there may be a reason to take some RMD in 2020 after-all. Note, these increased amounts are very small and are mainly an interesting impact of the deferred RMD.

While I believe the model is calculating the use of this feature correctly, there may be a problem I didn't find. Please post here or PM me with any issues.
Last edited by BigFoot48 on Thu May 07, 2020 8:13 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

User avatar
FiveK
Posts: 8965
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK » Wed Apr 15, 2020 11:47 am

BigFoot48 wrote:
Wed Apr 15, 2020 8:19 am
Please note: a test of this using data that has a 2020 RMD revealed that selecting to not take it 1) increased the RMD for 2021 and following years, which is subject to tax at the highest marginal rate, resulting in 2) increased Federal taxes and 3) a reduced portfolio balance at the end of the modeling period. So, if correct, there may be a reason to take some RMD in 2020 after-all.
Makes perfect sense if one substitutes "convert some to Roth" for "take some RMD" especially if one pays the conversion tax from taxable. In that case, even if this year's marginal rate is the same as future marginal rates, there can be an advantage due to the reduced tax drag from "moving" the tax amount from taxable to Roth. See Traditional versus Roth - Bogleheads for details.

sampaine
Posts: 11
Joined: Wed May 11, 2016 11:00 am

Re: Retiree Portfolio Model

Post by sampaine » Sat Apr 18, 2020 8:18 am

For the link to 20.1 I get a 404 error.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Apr 18, 2020 8:26 am

sampaine wrote:
Sat Apr 18, 2020 8:18 am
For the link to 20.1 I get a 404 error.
Thanks. Link in announcement post above worked but the one in post #1 got truncated. Fixed.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Speckles
Posts: 47
Joined: Sun Jul 21, 2019 1:36 am

Re: Retiree Portfolio Model

Post by Speckles » Sat Apr 18, 2020 8:33 pm

Thank you for the spreadsheet. It’s fabulous already and you keep making it even better. Your effort is always appreciated!
Specs

Barsoom
Posts: 269
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom » Wed May 20, 2020 1:36 pm

With permission from BigFoot48, I am announcing a variant to the Retiree Portfolio Model, a Monte Carlo addition to test sequence of return risk on your portfolio. Using built-in Excel capabilities, this variant contains 1,000 generated 40-year stock and bond rate sequences in place of the single rates defined in the Setup tab. It produces a probability distribution of your final portfolio balance, along with the failure rate (percent of trials that ran out of money).

The 40-year rate sequence model is customizable, allowing you to try different minimum, maximum, and likely value shapes to change the volatility of the return rate sequences. You can also single-step through the scenarios to examine the details of your portfolio under different market conditions.

Instructions are included in the Read Me and Monte Carlo Setup tabs. Just use the Copy feature to import your portfolio from the original Retiree Portfolio Model spreadsheet, change the Formula->Calculation Options to "Automatic except for data tables," set the "Use Monte Carlo growth rates" switch to "y," and press F9 to run the analysis. Run time should be under 30 seconds. Results are displayed on the main Setup tab.

For now, I am treating this as a beta test to gauge Boglehead user sentiment. Please post any comments/questions/concerns to me. If the community feels that this is a positive tool, I will continue to support it when BigFoot48 makes updates.

You can access the variant from this Dropbox link: https://www.dropbox.com/s/a42ju17d7afos ... .xlsm?dl=0

Thank you for your consideration and feedback.

-B

ncdad1
Posts: 10
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Wed May 20, 2020 2:29 pm

Thank you. I love new things to play with. I will let you know how it goes

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

Re: Retiree Portfolio Model

Post by Zephavest » Wed May 20, 2020 2:30 pm

Hi Barsoom, thanks for sharing this with the community, nice job!!!

ncdad1
Posts: 10
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Wed May 20, 2020 2:54 pm

Barsom, I got stuck at the "Copy feature". I am using MAC excel. It comes up with a dialog box. Normally, one can navigate to a file in the directory. I tried putting both the original file and your file in the same download directory and typing the name of the original files but I got an error the program could not find the original file?? Am I support to add directory structure even though both files are in the same download directory?

Barsoom
Posts: 269
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom » Wed May 20, 2020 3:22 pm

ncdad1 wrote:
Wed May 20, 2020 2:54 pm
Barsom, I got stuck at the "Copy feature". I am using MAC excel. It comes up with a dialog box. Normally, one can navigate to a file in the directory. I tried putting both the original file and your file in the same download directory and typing the name of the original files but I got an error the program could not find the original file?? Am I support to add directory structure even though both files are in the same download directory?
Did that work before, copying from an original to original spreadsheet?

I didn't change any macros or do anything to original functionality, so I'm perplexed at why my changes would affect this. I have Windows, so I can't help with Mac questions.

Some top-of-mind suggestions... I don't think the .xlsm (macro-enabled) filetype really matters if you still enable macros after opening the spreadsheet. Can you try changing the format to .xlsb or .xlsx and see if that changes things? Do this by saving as... and changing the filetype in the dropdown (don't just change the file name).

If the original to original works but the original to my version does not, I'd say that for now, can you manually enter the data?

-B

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed May 20, 2020 3:36 pm

BHs, Barsoom did an outstanding job adding a Monte Carlo simulator to the current version of RPM. Anyone wanting to see how this forecasts their portfolio results should check it out. As of now this will be a Beta/Alternative version that he will support. By leaving the Excel model open there have been many user-provided contributions over the years and his illustrates the value of doing this. Enjoy!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

ncdad1
Posts: 10
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Wed May 20, 2020 3:55 pm

Barsom

I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of
"~/Downloads/Retiree Portfolio Model v20.0.xlsm"

each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.

Barsoom
Posts: 269
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom » Wed May 20, 2020 4:10 pm

ncdad1 wrote:
Wed May 20, 2020 3:55 pm
Barsom

I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of
"~/Downloads/Retiree Portfolio Model v20.0.xlsm"

each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.
Does it display a file explorer? Can't you navigate to the proper folder from within the file explorer?

-B

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed May 20, 2020 4:13 pm

ncdad1 wrote:
Wed May 20, 2020 3:55 pm
I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of "~/Downloads/Retiree Portfolio Model v20.0.xlsm"
each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.
There's been no reports of the macro copy settings feature not working. Have you tried it between 20.0 and 20.1? I don't have Mac Excel to test it but that may be the reason. If you can't get it to work just revert to the manual copy method.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

ncdad1
Posts: 10
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Wed May 20, 2020 4:38 pm

Bansom and Bigfoot

I tried saving the file from xlsm to xlsx and that did it.

Rajsx
Posts: 477
Joined: Wed Mar 21, 2007 10:07 pm
Location: Florida

Re: Retiree Portfolio Model

Post by Rajsx » Fri May 22, 2020 1:34 pm

I downloaded The Retiree Portfolio Model, it asks me to buy Microsoft 365 (Office).

Bigfoot, Is there a way to try RPM without buying the Office ?

Thanks
We do not stop laughing because we grow old, we grow old because we stop laughing !!

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri May 22, 2020 1:37 pm

Rajsx wrote:
Fri May 22, 2020 1:34 pm
I downloaded The Retiree Portfolio Model, it asks me to buy Microsoft 365 (Office).

Bigfoot, Is there a way to try RPM without buying the Office ?

Thanks
The best free alternative is LibraOffice Calc. The list of alternatives tested is on the Readme page.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Post Reply