Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
stvyreb
Posts: 99
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

BigFoot48 wrote: Fri Sep 04, 2020 2:06 pm
stvyreb wrote: Fri Sep 04, 2020 2:02 pm strange in Libre Office 6.4.6.2 some of the version 19 macros work, but not in the newer v20.x

so much easier to use when they do, I assume they don't have to be enabled with a default setup, as I can't see anything else to change.
Both versions came from the same 2013 version of Excel. I know of no changes to the macro function but Microsoft may have made a change.
hmm in LOv7 , 20c macros work, while loaded initially as .xlsm file , but after being saved as "Excel 2007-365-macro-enabled" and then reopened , it is then that they fail
but if saved in .ods format seem to now work.
hope haven't disturbed you
Lacrocious
Posts: 338
Joined: Thu Mar 22, 2007 9:45 pm
Location: Wisconsin

Re: Retiree Portfolio Model

Post by Lacrocious »

Hi Bigfoot48,
I am on macOS Catalina using the latest RPM - 20.2c and Excel for Mac v16.4 - latest Office 365 version for Mac. I cleared 20.2c and went to copy from 20.2b. This is the first time I have tried the Copy option. I get the "Please Confirm" prompt and answer "Yes" to do the copy. After that I get a VBA error with the standard End/Debug options:

Code: Select all

Run-time error '1004':

Method 'GetOpenFilename' of object'_Application' failed
Thoughts?
- L
User avatar
Topic Author
BigFoot48
Posts: 2817
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Lacrocious wrote: Sun Sep 06, 2020 9:21 am Hi Bigfoot48,
I am on macOS Catalina using the latest RPM - 20.2c and Excel for Mac v16.4 - latest Office 365 version for Mac. I cleared 20.2c and went to copy from 20.2b. This is the first time I have tried the Copy option. I get the "Please Confirm" prompt and answer "Yes" to do the copy. After that I get a VBA error with the standard End/Debug options:

Code: Select all

Run-time error '1004':

Method 'GetOpenFilename' of object'_Application' failed
Thoughts?
- L
The Clear and Copy macros are working in Excel for 20.2c. I don't have access to Mac so can't determine nor have a clue why that would happen. You may need to use the view Side by Side feature to manually copy the settings.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Escapevelocity
Posts: 166
Joined: Mon Feb 18, 2019 8:32 am

Re: Retiree Portfolio Model

Post by Escapevelocity »

Thank you for sharing this model with us. I’ve had a good experience setting it up today.

I’m curious what your opinion is about modeling everything in today’s dollars compared to building in inflation rates to rates of return and expenses. Once challenge is that you need to model your investment returns in real terms instead of nominal. The other is that things like non-indexed annuities would need to be modeled using a negative real return. But the advantage is that it is more intuitive to look at future portfolio levels and things like taxes using today’s valuations compared to future dollars. Fidelity’s retirement model let’s you toggle.

I set up my inputs into your model using zero inflation and real rates of return on stocks, bonds and cash. It seems to work fine but what is your opinion?
User avatar
Topic Author
BigFoot48
Posts: 2817
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Escapevelocity wrote: Mon Sep 07, 2020 4:58 pm I set up my inputs into your model using zero inflation and real rates of return on stocks, bonds and cash. It seems to work fine but what is your opinion?
Glad you like the model. My opinion is that nominal is a better way to look at our numbers since that's what we will be experiencing and inflation may be more difficult to forecast than nominal rates of returns, but I'm sure other users share your opinion as using real forecasts has come up occasionally. Others might want to chime in.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Escapevelocity
Posts: 166
Joined: Mon Feb 18, 2019 8:32 am

Re: Retiree Portfolio Model

Post by Escapevelocity »

BigFoot48 wrote: Mon Sep 07, 2020 5:17 pm
Escapevelocity wrote: Mon Sep 07, 2020 4:58 pm I set up my inputs into your model using zero inflation and real rates of return on stocks, bonds and cash. It seems to work fine but what is your opinion?
Glad you like the model. My opinion is that nominal is a better way to look at our numbers since that's what we will be experiencing and inflation may be more difficult to forecast than nominal rates of returns, but I'm sure other users share your opinion as using real forecasts has come up occasionally. Others might want to chime in.
I appreciate your point, but I'm sticking with real return approach. I don't think that forecasting nominal rates of return is any more or less challenging than forecasting real rates. Also, I believe SSA benefit estimates on the government website are expressed in today's dollars. An idea for enhancement would be a toggle to look at it either way.

Thanks again for building this model. I am finding it very useful!
stvyreb
Posts: 99
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

I'm curious for brief explanation of why the section of 'withdrawals'(starting amounts,etc) /annual escalation/de-escalation and 'storage' below it in the Roth Conv section.
.....maybe it's all about tweaking the conversion years to 100% of the 12% bracket, or is there something else to it?
Last edited by stvyreb on Fri Sep 18, 2020 11:49 am, edited 1 time in total.
User avatar
Topic Author
BigFoot48
Posts: 2817
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Thu Sep 17, 2020 9:11 pm v20.2c.ods ->Set up-> 10. Roth Conv 'included' -> pop-up bubble after 'conversations' still reads e.g. '15%'
I don't have answers for some of your questions. I recommend all new users study each setting on the Setup page and replace the example data settings with their own. This will help to minimize errors.

SS is taxed based on Federal AGI so reduce AGI to lower the 80% SS taxable amount. Expenses are withdrawn from the Taxable account, which gets its balance from earnings, wages, withdrawals from IRAs and other sources.

I recommend i-ORP for an excellent and easy to use forecast tool. https://www.i-orp.com/EmptyRow/index.html
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
stvyreb
Posts: 99
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

in my opinion, sure would be great if someone might create a tutorial for youtube(seems it would be fairly trivial, some folks need visuals), that understands the various small points, some of it, I eventually can sort out, but much it seems opaque.

eg I was just looking at again the Roth Conversion section, and just above 'taxable account balance' there is , in green font, a total, the red dot pop-up just says "ending balance", I'm sure makes perfect sense to those that know, thats just one point.

I guess, it's ok to just say 'well go try the ORP' instead, because? its more of a forecast? or 'its simplier'? anyway maybe I should just go back to lurking
Admiral
Posts: 2986
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

stvyreb wrote: Fri Sep 18, 2020 7:34 pm in my opinion, sure would be great if someone might create a tutorial for youtube(seems it would be fairly trivial, some folks need visuals), that understands the various small points, some of it, I eventually can sort out, but much it seems opaque.

eg I was just looking at again the Roth Conversion section, and just above 'taxable account balance' there is , in green font, a total, the red dot pop-up just says "ending balance", I'm sure makes perfect sense to those that know, thats just one point.

I guess, it's ok to just say 'well go try the ORP' instead, because? its more of a forecast? or 'its simplier'? anyway maybe I should just go back to lurking
I ORP is indeed simpler. You should try it. RPM has a learning curve but once you master it, you will appreciate the level of detail. i ORP is designed to maximize retirement spending and minimize taxes, which is not what everyone wants to do. You can finagle the numbers (tell it to leave an estate) to help reduce this propensity. RPM is not designed this way.

If you want something in between I recommend you look at Flexible Retirement Planner. It is free and has no learning curve. It is a great retirement planning tool. Outputs are in current not inflated dollars.
renega
Posts: 4
Joined: Thu Jan 23, 2020 11:09 am

Re: Retiree Portfolio Model

Post by renega »

First off, I'd like to thank you for sharing your work with the community. I found your model after creating my own as I am contemplating the best strategy for Roth conversions in the upcoming years. I can see you've invested a ton of effort into your model and it looks awesome. What I need help with is understanding what I am missing because I am coming up with a completely different strategy between your model and my own model.

I think the difference is that in your model, if taxable account balances go negative, that means an error. In my model, I allow dipping into the Roth account to cover annual expenses and only flag an error if both taxable and roth accounts cannot cover annual expenses. To help illustrate, I added an excel two variable data table to your spreadsheet to vary the Roth Conversion & Roth Withdrawal amounts from retirement until IRA RMDs start. The following shows the varying Roth Conversion amounts in the rows and Roth Withdrawal amounts in the columns and the taxable account balance in each intersecting cell. I flagged negative balances red and conditional formatted a gradient coloring scheme for the rest of the chart.
Image

With my model, I get very different results:
Image

I'm not concerned about the values in the cells, but rather the strategies seem completely different. Any thoughts on what I might be missing?
User avatar
Topic Author
BigFoot48
Posts: 2817
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

renega wrote: Thu Sep 24, 2020 9:37 am I'm not concerned about the values in the cells, but rather the strategies seem completely different. Any thoughts on what I might be missing?
RPM is designed to require the user to furnish their own estimates of annual income, expenses and account withdrawals and the model just does the math on the results. There is an automated balance feature than can be turned on to keep the Taxable account positive.

Once a year I compare results of a simple portfolio to i-ORP and they agree except for Federal taxes, as i-ORP uses an average rate and RPM the detailed Federal brackets, so I have a high confidence that RPM is calculating results and account balances accurately.

Sorry, I could not see your images to see your results.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
MrDrinkingWater
Posts: 181
Joined: Tue Dec 23, 2014 11:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater »

renega wrote: Thu Sep 24, 2020 9:37 am <SNIP>
I think the difference is that in your model, if taxable account balances go negative, that means an error. In my model, I allow dipping into the Roth account to cover annual expenses and only flag an error if both taxable and roth accounts cannot cover annual expenses.

<SNIP>

Any thoughts on what I might be missing?
In Section 10, Optional Roth Conversions, you can use the blue-shaded withdrawal column to keep the taxable account from going negative. Just withdraw money from the Roth Conversion balance that you have built up to keep the taxable account balance greater than zero in each model year. I think this is how many users "zero in" on adding income (to meet expenses) without adding taxable income (by doing a larger Traditional IRA withdrawal, for example) that might push them into the next higher tax bracket in a given model year.

Your addition or work-around that you've added to a copy of RPM likely is a good attempt at automating subtractions from either Roth IRA1, Roth IRA2, or Roth Conversion balances. But, I couldn't see the graphic or code snippet that you attempted to display in your post.

It sounds as if you have figured out how to do Roth IRA withdrawals successfully in Section 6, IRA Contributions and Withdrawals. That's the other way many users top-up their taxable account balance (without adding in more taxable income via Traditional IRA Withdrawals) to keep the taxable account balance from having a negative balance in a given model year.
renega
Posts: 4
Joined: Thu Jan 23, 2020 11:09 am

Re: Retiree Portfolio Model

Post by renega »

Thanks for the comments. I'll play with RPM some more. I don't know why the images didn't display inline. I could right click on the image icon and open into a new tab to display it.
User avatar
FiveK
Posts: 10104
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

renega wrote: Thu Sep 24, 2020 9:41 pm Thanks for the comments. I'll play with RPM some more. I don't know why the images didn't display inline. I could right click on the image icon and open into a new tab to display it.
Posting images in the Bogleheads forum - Bogleheads might be useful.
Post Reply