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
stvyreb
Posts: 159
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
User avatar
Lacrocious
Posts: 377
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: 3112
Joined: Tue Feb 20, 2007 9: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; 18-time loser
Escapevelocity
Posts: 1145
Joined: Mon Feb 18, 2019 7: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: 3112
Joined: Tue Feb 20, 2007 9: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; 18-time loser
Escapevelocity
Posts: 1145
Joined: Mon Feb 18, 2019 7: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!
User avatar
stvyreb
Posts: 159
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: 3112
Joined: Tue Feb 20, 2007 9: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; 18-time loser
User avatar
stvyreb
Posts: 159
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: 5032
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: 12
Joined: Thu Jan 23, 2020 10: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: 3112
Joined: Tue Feb 20, 2007 9: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; 18-time loser
MrDrinkingWater
Posts: 277
Joined: Tue Dec 23, 2014 10: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: 12
Joined: Thu Jan 23, 2020 10: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: 15690
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.
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Version 20.2d is available as of Oct 11. It's likely few need to or will benefit from updating to this version.

This fixes a formula reference for Social Security Deferred Retirement Years for Spouse Spousal benefits that was using "Your" age instead of "Spousal". Thanks to BH FiveK for finding and reporting this. It is likely this has a very minor impact on calculated SS benefits for those claiming spousal benefits after the Full Retirement Age (no change in Example data results.)

Also corrected the number shown as "Net yearly growth taxed at marginal rate" in the Taxable Account Adjustment section on Setup. No calculation impact.

Download: https://www.dropbox.com/s/4oozwq7onrb2w ... .xlsm?dl=0
Last edited by BigFoot48 on Sun Oct 11, 2020 8:45 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Tattarrattat
Posts: 304
Joined: Wed Aug 19, 2020 6:05 pm

Re: Retiree Portfolio Model

Post by Tattarrattat »

Hi. This spreadsheet seems remarkable and clearly a lot of dedicated, skillful work has gone into it. The fact that it is being made available gratis is admirable - compliments to the author. I am having a rough time getting started, even with entering the initial data. I find the setup page confusing and I'm not sure what boxes get what data, what boxes should be cleared and so on, or if I should clear them all. I start entering things and quickly get red error messages. I read the readme page and clicked all the small triangles. Feel a little stuck with it. Is there an RPM-for-dummies type guide in one of these posts? Or maybe a youtube video where somebody clicks from box to box explaining what goes where? I have used i-orp without difficulty, but just can't seem to get to launch phase with this one. Any suggestions on how a beginner can more easily get rolling with this tool? Thanks.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

^^^ I have the same question. I mentioned this spreadsheet at my local (virtual) Bogleheads chapter meeting. Next thing I know, I'm scheduled to give a presentation at the next meeting. :)
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.
Tattarrattat
Posts: 304
Joined: Wed Aug 19, 2020 6:05 pm

Re: Retiree Portfolio Model

Post by Tattarrattat »

Would be great if someone could set up such a presentation, or perhaps record and archive one, in a way that might be available to a group extending beyond that of a local chapter. Or perhaps allow guest attendees on a one time basis or something. Actually, since it's all virtual anyway, perhaps "local chapter" is a little anachronistic and some broader collaborative efforts can be made in general between or among chapters?
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Tattarrattat wrote: Sun Oct 11, 2020 7:33 pm I have used i-orp without difficulty, but just can't seem to get to launch phase with this one. Any suggestions on how a beginner can more easily get rolling with this tool? Thanks.
I appreciate the difficulty new users may have with the model. Seven years ago it was so much easier! I do think the Readme page and cell comment instructions can be improved to help the new user. Alert/error messages can also be changed to tell users what to do, such as: "Taxable account is negative. Decrease expenses and/or increase income." that will take some of the mystery out of the messages. I will work on these.

It also might be useful to create a macro that clears the Example data and fills in a simple set of data that the first-time user can easily replace and get started. I also think macro pop-up messages could be used to supplement or replace cell comments that some users have problems reading.

At the present time I'm not inclined to create a video introduction but if someone else wants to they can.

Thanks for the comment!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

At a basic level, the spreadsheet's User Interface is one that's difficult to simplify. The way to simplify things is to isolate the content.

For example, a new sheet only containing entries for a single person who owns a home. By doing so, however, you eliminate choices that may be beneficial in a slightly different scenario, i.e. that same person needs to do something with her portfolio - Roth conversion, or possibly a Qualified Charitable Distribution. And then there's the IRMAA cliff. How much of that "complicated" information do you remove? I don't think it's possible to use this approach.

Alternatively, having "example" macros for first-time users is a great idea. You see the full brunt of all the features, but the configuration is "ready to go" for a simple case. The user can modify the scenario from there.

We just need to define how many variations of "first-time" scenarios should be included.

====================
Several members of my local Bogleheads chapter (including myself) have been using your spreadsheet for years. The going consensus is that the spreadsheet is incredibly useful, but difficult to understand.

Even though users were getting stuck due to its complexity, the spreadsheet was helpful to point out topics that needed further investigation.

Update: FYI - a new member is complimenting the spreadsheet: Roth conversion at 70?
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.
MrDrinkingWater
Posts: 277
Joined: Tue Dec 23, 2014 10:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater »

I would suggest that first-time RPM users should not sweat having negative numbers and red text error messages for the first few hours of using and entering data into RPM.

For the first-time in using RPM, don't clear all the entries on the Setup Page. Don't try to make a perfect model that exactly fits your situation. You just won't be able to do that in fifteen minutes or even an hour. Just be content on learning how the inputs that you enter on the Setup Page are to be entered.

Read through the entire Readme spreadsheet before starting to enter data on the Setup Page. You don't need to read the Readme page with 100% comprehension. Just read through it once so that you get some comprehension and at least a partial overview of what RPM does.

Now proceed to start editing the Setup spreadsheet page. Remember you are just learning how to use RPM, not making a perfect fit model to your own situation on the first try. Fill in the blue cells with data that you know about yourself, and if you are married, your spouse. Ignore all the error messages and "warning negative balance" stuff for the next hour or so of entering data.

For the Portfolio Balance section: Add up all your accounts to fit into the categories listed as inputs to RPM. For example, your 401k accounts and traditional IRA accounts all go together in IRA1, and your spouse's 401k accounts and traditional IRA accounts all go together in IRA2. Do the same sort of thing to combine your actual existing Roth IRAs together. All of your Roth IRAs are in Roth IRA1 and all of your spouse's Roth IRAs are in Roth IRA2. Combine your taxable accounts all together into the single taxable account category item. Now take those sums you've made from another spreadsheet or a piece of paper and enter them into the data fields. Until you complete editing the other sections of the Setup Page, you will most likely have red text error messages.

Continue working your way down through the Setup Page. I prefer using the Class Asset Method in Section 3, the "Rates and Returns" section. For this first learning session, just use the rates that are there (the default rates.) You can edit them later. Do edit the percentages of stocks, bonds, and tax-exempt to match the percentages that are present in your accounts. Notice that "Money Market and other" automatically completes the row of cells to add up to 100%, so you don't have to enter in percentages for what you have in cash in savings and checking accounts, or any other taxable accounts.

Now enter in your expected pension income data and expected Social Security income data. Enter in any anticipated earned income, like a side job you have or expect to have.

In Section 5, "Expenses", put in your first guess about a reasonable level of expenses into that cell. This will be your first chance to really drive the calculated model results to not have red text errors and negative balance warning messages. You can also see on the right end of that row of cells what your ending portfolio balance is calculated to be each time you edit your guess about what your anticipated expenses will be. Choose a yearly level of expenses that will keep your portfolio balance positive, and you'll likely get rid of nearly all of the red text error messages. Maybe putting in what is a conservative "expenses" number is a good practice, just to clear away the error messages for a while.

In Section 6, IRA Contributions and IRA Withdrawals, this is where you will have some work, editing, and tweaking to do. I recommend starting out by setting your annual withdrawals to zero in each section, but keep the default yearly percentage change adjustments, and go ahead and edit the start age and end age cells to your desired age ranges. Now, just for this training session, so that you'll get the hang of this feature of RPM, set the annual withdrawal to $10,000 for each of your traditional IRAs and $1000 for each of your Roth IRAs. This will help you see how the Traditional IRA withdrawals and Roth IRA withdrawals flow into your taxable account. It is your taxable account from which your annual expenses are subtracted from for each year of the model you are forecasting.

In Section 7, enter in any one-time expense, like buying a house or yacht, if you have some one-time expenses like that. You'll be able to see how that depletes your Taxable Balance quite clearly when you later look at the Summary, Results, and Details spreadsheets.

In Section 8, Taxes, just accept the default settings for the first hour or two of figuring out how the IRA Withdrawals, Expenses, and Income sections interact to add to or deplete the Taxable Balance. After you have some more insight and confidence in how your inputs affect your RPM model, you can then start fine-tuning the Taxes section for your particular situation.

I wouldn't fool with Section 9 during the first few hours of using RPM. Some folks may never enter data and use Section 9 for any purpose.

I would suggest that editing and putting data into Section 10, Optional Roth Conversions, should be done after a few hours of learning how the data entered into all the other sections affect RPM. Before doing the Optional Roth Conversions, I went back to reset my annual IRA1 withdrawals to zero, and then I started using Method 2 to enter in a Roth conversion amount each of the early years of my forecasted model. These "Method 2" Roth conversion amounts are added to the Roth Conversion balance that is not entered data, but calculated data, that shows up in Section 2, Portfolio Balances, and "Roth Start of Year Balance" that is two columns to the right of the "Roth Conversion Amount" column. When you want to fund your Taxable Account with "Roth Conversion balance" dollars, enter in the amount you want to transfer in that column just to the right (the column labeled "Withdrawals") of the conversion amount that could be entered for that model year.

The Optional Roth Conversions part of RPM is really useful for figuring out how much Roth Conversion to do each year of your forecasted model to aim for the top of a particular tax bracket. A lot of folks may not need or want to use this section of the Setup Page, but it is the key helpful feature for many RPM users.

Well, I hope this helps some of you get started with using RPM. I recommend spending some time studying the graphs that populate the Setup Page, and the other three spreadsheets that I mentioned earlier: Summary, Results, and Details. Now that you've made your own personalized "test case" of RPM that is hopefully in a condition of having a modest amount of anticipated yearly expenses, a positive portfolio balance after two-to-three decades of forecasted time modeled, and no red error messages, you can REALLY start fine-tuning the entries and numbers in Setup Page Sections 3, 4, 6, 8, and 10 to exactly your own situation. Good luck!
Last edited by MrDrinkingWater on Tue Oct 13, 2020 12:04 pm, edited 3 times in total.
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Excellent explanation MrDrinkingWater! Very good strategy for new users to follow in learning how to use RPM.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Eagle33
Posts: 2383
Joined: Wed Aug 30, 2017 3:20 pm

Re: Retiree Portfolio Model

Post by Eagle33 »

BigFoot48 wrote: Mon Oct 12, 2020 5:24 pm Excellent explanation MrDrinkingWater! Very good strategy for new users to follow in learning how to use RPM.
+1 :beer

I'm ready to download the latest version and now know how to begin & what to ignore in the early passes.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I have incorporated into Version 20.2d:

- A macro bug fix. When copying your data from an older spreadsheet, the "load entries" macro at the top of the Settings sheet misses three cells.

-- Cell E129: Social Security COLA
-- Cell E140: Social Security alternative COLA
-- Cell E264: Override single rate start year

BigFoot48 is clear to say that some settings may have been missed and to check the entries yourself. I had missed a few and spent some time backtracing through my data.

- Exclude pension and QCD distributions from state income tax. Details are in this post.

Note the follow-up post which explains that QCD distributions may be taxable in some states. To fix this, change SUM(F175:F179) back to SUM(F175:F178) in 2 places - each sheet.

- MrDrinkingWater's tutorial overview

=========================================

I am not posting a link to this update, as I don't want an "unofficial" version to be available. I sent BigFoot48 a link to the spreadsheet to review and incorporate at his discretion.

My intent for this post was to document a macro bug that may cause someone to be mislead. (The calculations are correct, but you may not notice that the setting has changed.) While I was fixing this, I added the tutorial overview and the state income tax exclusion.
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.
Tattarrattat
Posts: 304
Joined: Wed Aug 19, 2020 6:05 pm

Re: Retiree Portfolio Model

Post by Tattarrattat »

MrDrinkingWater wrote: Mon Oct 12, 2020 4:52 pm I would suggest that first-time RPM users should not sweat having negative numbers and red text error messages for the first few hours of using and entering data into RPM.

For the first-time in using RPM, don't clear all the entries on the Setup Page. Don't try to make a perfect model that exactly fits your situation. You just won't be able to do that in fifteen minutes or even an hour. Just be content on learning how the inputs that you enter on the Setup Page are to be entered.

Read through the entire Readme spreadsheet before starting to enter data on the Setup Page. You don't need to read the Readme page with 100% comprehension. Just read through it once so that you get some comprehension and at least a partial overview of what RPM does.

Now proceed to start editing the Setup spreadsheet page. Remember you are just learning how to use RPM, not making a perfect fit model to your own situation on the first try. Fill in the blue cells with data that you know about yourself, and if you are married, your spouse. Ignore all the error messages and "warning negative balance" stuff for the next hour or so of entering data.

For the Portfolio Balance section: Add up all your accounts to fit into the categories listed as inputs to RPM. For example, your 401k accounts and traditional IRA accounts all go together in IRA1, and your spouse's 401k accounts and traditional IRA accounts all go together in IRA2. Do the same sort of thing to combine your actual existing Roth IRAs together. All of your Roth IRAs are in Roth IRA1 and all of your spouse's Roth IRAs are in Roth IRA2. Combine your taxable accounts all together into the single taxable account category item. Now take those sums you've made from another spreadsheet or a piece of paper and enter them into the data fields. Until you complete editing the other sections of the Setup Page, you will most likely have red text error messages.

Continue working your way down through the Setup Page. I prefer using the Class Asset Method in Section 3, the "Rates and Returns" section. For this first learning session, just use the rates that are there (the default rates.) You can edit them later. Do edit the percentages of stocks, bonds, and tax-exempt to match the percentages that are present in your accounts. Notice that "Money Market and other" automatically completes the row of cells to add up to 100%, so you don't have to enter in percentages for what you have in cash in savings and checking accounts, or any other taxable accounts.

Now enter in your expected pension income data and expected Social Security income data. Enter in any anticipated earned income, like a side job you have or expect to have.

In Section 5, "Expenses", put in your first guess about a reasonable level of expenses into that cell. This will be your first chance to really drive the calculated model results to not have red text errors and negative balance warning messages. You can also see on the right end of that row of cells what your ending portfolio balance is calculated to be each time you edit your guess about what your anticipated expenses will be. Choose a yearly level of expenses that will keep your portfolio balance positive, and you'll like get rid of nearly all of the red text error messages. Maybe putting in what is a conservative "expenses" number is a good practice, just to clear away the error messages for a while.

In Section 6, IRA Contributions and IRA Withdrawals, this is where you will have some work, editing, and tweaking to do. I recommend starting out by setting your annual withdrawals to zero in each section, but keep the default yearly percentage change adjustments, and go ahead and edit the start age and end age cells to your desired age ranges. Now, just for this training session, so that you'll get the hang of this feature of RPM, set the annual withdrawal to $10,000 for each of your traditional IRAs and $1000 for each of your Roth IRAs. This will help you see how the Traditional IRA withdrawals and Roth IRA withdrawals flow into your taxable account. It is your taxable account from which your annual expenses are subtracted from for each year of the model you are forecasting.

In Section 7, enter in any one-time expense, like buying a house or yacht, if you have some one-time expenses like that. You'll be able to see how that depletes your Taxable Balance quite clearly when you later look at the Summary, Results, and Details spreadsheets.

In Section 8, Taxes, just accept the default settings for the first hour or two of figuring out how the IRA Withdrawals, Expenses, and Income sections interact to add to or deplete the Taxable Balance. After you have some more insight and confidence in how your inputs affect your RPM model, you can then start fine-tuning the Taxes section for your particular situation.

I wouldn't fool with Section 9 during the first few hours of using RPM. Some folks may never enter data and use Section 9 for any purpose.

I would suggest that editing and putting data into Section 10, Optional Roth Conversions, should be done after a few hours of learning how the data entered into all the other sections affect RPM. Before doing the Optional Roth Conversions, I went back to reset my annual IRA1 withdrawals to zero, and then I started using Method 2 to enter in a Roth conversion amount each of the early years of my forecasted model. These "Method 2" Roth conversion amounts are added to the Roth Conversion balance that is not entered data, but calculated data, that shows up in Section 2, Portfolio Balances, and "Roth Start of Year Balance" that is two columns to the right of the "Roth Conversion Amount" column. When you want to fund your Taxable Account with "Roth Conversion balance" dollars, enter in the amount you want to transfer in that column just to the right (the column labeled "Withdrawals") of the conversion amount that could be entered for that model year.

The Optional Roth Conversions part of RPM is really useful for figuring out how much Roth Conversion to do each year of your forecasted model to aim for the top of a particular tax bracket. A lot of folks may not need or want to use this section of the Setup Page, but it is the key helpful feature for many RPM users.

Well, I hope this helps some of you get started with using RPM. I recommend spending some time studying the graphs that populate the Setup Page, and the other three spreadsheets that I mentioned earlier: Summary, Results, and Details. Now that you've made your own personalized "test case" of RPM that is hopefully in a condition of having a modest amount of anticipated yearly expenses, a positive portfolio balance after two-to-three decades of forecasted time modeled, and no red error messages, you can REALLY start fine-tuning the entries and numbers in Setup Page Sections 3, 4, 6, 8, and 10 to exactly your own situation. Good luck!
This looks like a promising start-up guide. I will give it a go tomorrow and report back. Thank you for taking the time and effort to put together such a lengthy post.
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Tue Oct 13, 2020 10:37 am I have incorporated into Version 20.2d:

- A macro bug fix. When copying your data from an older spreadsheet, the "load entries" macro at the top of the Settings sheet misses three cells.

-- Cell E129: Social Security COLA
-- Cell E140: Social Security alternative COLA
-- Cell E264: Override single rate start year
These have been added in the current 20.2d version. Many thanks to LadyGeek for finding them. Her other suggestions and improvements are under review and will likely be in the next version within a few weeks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Tracker968
Posts: 369
Joined: Tue Feb 23, 2016 8:32 pm

Re: Retiree Portfolio Model

Post by Tracker968 »

How do you recommend using the a copy and copy-paste fields? It's not clear to me what is going on.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

Tattarrattat wrote: Sun Oct 11, 2020 7:33 pm Hi. This spreadsheet seems remarkable and clearly a lot of dedicated, skillful work has gone into it. The fact that it is being made available gratis is admirable - compliments to the author. I am having a rough time getting started, even with entering the initial data. I find the setup page confusing and I'm not sure what boxes get what data, what boxes should be cleared and so on, or if I should clear them all. I start entering things and quickly get red error messages. I read the readme page and clicked all the small triangles. Feel a little stuck with it. Is there an RPM-for-dummies type guide in one of these posts? Or maybe a youtube video where somebody clicks from box to box explaining what goes where? I have used i-orp without difficulty, but just can't seem to get to launch phase with this one. Any suggestions on how a beginner can more easily get rolling with this tool? Thanks.
"Is there an RPM-for-dummies type guide in one of these posts? Or maybe a youtube video where somebody clicks from box to box explaining what goes where?"

+1

PS: a nice addition is the ability to copy all the hidden red-square comments to MS word with the Macro, then it's 62 pages, so I think one might need many YouTube Tutorials, maybe we can start a "GoFund Me" for someone to do some?
Tattarrattat
Posts: 304
Joined: Wed Aug 19, 2020 6:05 pm

Re: Retiree Portfolio Model

Post by Tattarrattat »

MrDrinkingWater wrote: Mon Oct 12, 2020 4:52 pm I would suggest that first-time RPM users should not sweat having negative numbers and red text error messages for the first few hours of using and entering data into RPM.

For the first-time in using RPM, don't clear all the entries on the Setup Page. Don't try to make a perfect model that exactly fits your situation. You just won't be able to do that in fifteen minutes or even an hour. Just be content on learning how the inputs that you enter on the Setup Page are to be entered.

Read through the entire Readme spreadsheet before starting to enter data on the Setup Page. You don't need to read the Readme page with 100% comprehension. Just read through it once so that you get some comprehension and at least a partial overview of what RPM does.

Now proceed to start editing the Setup spreadsheet page. Remember you are just learning how to use RPM, not making a perfect fit model to your own situation on the first try. Fill in the blue cells with data that you know about yourself, and if you are married, your spouse. Ignore all the error messages and "warning negative balance" stuff for the next hour or so of entering data.

For the Portfolio Balance section: Add up all your accounts to fit into the categories listed as inputs to RPM. For example, your 401k accounts and traditional IRA accounts all go together in IRA1, and your spouse's 401k accounts and traditional IRA accounts all go together in IRA2. Do the same sort of thing to combine your actual existing Roth IRAs together. All of your Roth IRAs are in Roth IRA1 and all of your spouse's Roth IRAs are in Roth IRA2. Combine your taxable accounts all together into the single taxable account category item. Now take those sums you've made from another spreadsheet or a piece of paper and enter them into the data fields. Until you complete editing the other sections of the Setup Page, you will most likely have red text error messages.

Continue working your way down through the Setup Page. I prefer using the Class Asset Method in Section 3, the "Rates and Returns" section. For this first learning session, just use the rates that are there (the default rates.) You can edit them later. Do edit the percentages of stocks, bonds, and tax-exempt to match the percentages that are present in your accounts. Notice that "Money Market and other" automatically completes the row of cells to add up to 100%, so you don't have to enter in percentages for what you have in cash in savings and checking accounts, or any other taxable accounts.

Now enter in your expected pension income data and expected Social Security income data. Enter in any anticipated earned income, like a side job you have or expect to have.

In Section 5, "Expenses", put in your first guess about a reasonable level of expenses into that cell. This will be your first chance to really drive the calculated model results to not have red text errors and negative balance warning messages. You can also see on the right end of that row of cells what your ending portfolio balance is calculated to be each time you edit your guess about what your anticipated expenses will be. Choose a yearly level of expenses that will keep your portfolio balance positive, and you'll likely get rid of nearly all of the red text error messages. Maybe putting in what is a conservative "expenses" number is a good practice, just to clear away the error messages for a while.

In Section 6, IRA Contributions and IRA Withdrawals, this is where you will have some work, editing, and tweaking to do. I recommend starting out by setting your annual withdrawals to zero in each section, but keep the default yearly percentage change adjustments, and go ahead and edit the start age and end age cells to your desired age ranges. Now, just for this training session, so that you'll get the hang of this feature of RPM, set the annual withdrawal to $10,000 for each of your traditional IRAs and $1000 for each of your Roth IRAs. This will help you see how the Traditional IRA withdrawals and Roth IRA withdrawals flow into your taxable account. It is your taxable account from which your annual expenses are subtracted from for each year of the model you are forecasting.

In Section 7, enter in any one-time expense, like buying a house or yacht, if you have some one-time expenses like that. You'll be able to see how that depletes your Taxable Balance quite clearly when you later look at the Summary, Results, and Details spreadsheets.

In Section 8, Taxes, just accept the default settings for the first hour or two of figuring out how the IRA Withdrawals, Expenses, and Income sections interact to add to or deplete the Taxable Balance. After you have some more insight and confidence in how your inputs affect your RPM model, you can then start fine-tuning the Taxes section for your particular situation.

I wouldn't fool with Section 9 during the first few hours of using RPM. Some folks may never enter data and use Section 9 for any purpose.

I would suggest that editing and putting data into Section 10, Optional Roth Conversions, should be done after a few hours of learning how the data entered into all the other sections affect RPM. Before doing the Optional Roth Conversions, I went back to reset my annual IRA1 withdrawals to zero, and then I started using Method 2 to enter in a Roth conversion amount each of the early years of my forecasted model. These "Method 2" Roth conversion amounts are added to the Roth Conversion balance that is not entered data, but calculated data, that shows up in Section 2, Portfolio Balances, and "Roth Start of Year Balance" that is two columns to the right of the "Roth Conversion Amount" column. When you want to fund your Taxable Account with "Roth Conversion balance" dollars, enter in the amount you want to transfer in that column just to the right (the column labeled "Withdrawals") of the conversion amount that could be entered for that model year.

The Optional Roth Conversions part of RPM is really useful for figuring out how much Roth Conversion to do each year of your forecasted model to aim for the top of a particular tax bracket. A lot of folks may not need or want to use this section of the Setup Page, but it is the key helpful feature for many RPM users.

Well, I hope this helps some of you get started with using RPM. I recommend spending some time studying the graphs that populate the Setup Page, and the other three spreadsheets that I mentioned earlier: Summary, Results, and Details. Now that you've made your own personalized "test case" of RPM that is hopefully in a condition of having a modest amount of anticipated yearly expenses, a positive portfolio balance after two-to-three decades of forecasted time modeled, and no red error messages, you can REALLY start fine-tuning the entries and numbers in Setup Page Sections 3, 4, 6, 8, and 10 to exactly your own situation. Good luck!
Thank you for this, MrDrinkingWater! Just sat with it and followed your startup guide and actually got some results that made some sense - really helpful. There are still boxes and buttons and macros that don't make sense to me but I did get the big picture and I got to the point where could do theoretical Roth conversions, which was mostly what I was looking for, although the use of the model as a more refined kind of Firecalc was helpful as well, for projecting and planning in the larger sense. An excellent feature was seeing the Roth conversion table right next to the no-conversion table. One thing though, is it made clear to me that by doing Roth conversions, I'm paying lots of taxes voluntarily in my 60s and early 70s, in order to lower my taxes in my 80s and 90s. It occurred to me however that I may not care that much what my taxes are in my 80s and 90s when I will probably be a lot less active and have a lot less need for discretionary income. Which brings up the philosophical point of whether these conversions are worth doing at all, legacy and inheritance questions aside. But that is probably a topic for its own thread.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

That is exactly what the spreadsheet's insight showed to me.

It quickly became obvious that I never really looked at the impact on taxes when RMDs kick-in. In one scenario, Roth conversions no longer make sense, but Qualified Charitable Distributions do make sense.

This would have been the first time I did a Roth conversion. After looking at the spreadsheet, I think I'll pass.

That side-by-side Roth conversion comparison table makes this easy (once you get comfortable with the spreadsheet).
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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Tracker968 wrote: Tue Oct 13, 2020 9:16 pm How do you recommend using the a copy and copy-paste fields? It's not clear to me what is going on.
I assume you're looking at the Results Summary near the top of the Setup sheet (Rows 9 to 21).

That area is used to store results from different scenarios. In the example, there are 4 scenarios.

Case 1. SS at 70/66, no Roth, $70k exp
Case 2. SS at 62/62, no Roth, $70k exp
Case 3. Roth conv, SS at 70/66, $70k exp
Case 4. 2019 copied from 19.1a

The baseline situation is stated in Row 6 "Example Data: $70k expenses before tax. SS at 70 & 66. 23% SS reduction in 2035. $200k inherited. Sell house at 84. Wife widowed at 85.".

The user has configured the spreadsheet with this baseline, but wants to see what happens if a few things are changed.

Column E, "current results", always reflects the the current configuration (settings in the rest of the spreadsheet).

Now, look at Column F (Case 1). See how the numbers match Column E? The copy-paste macro in Column F copied the current results into that column. You now have a record of the results for Case 1.

What about Columns G, H, and I? Those are the result of changing the spreadsheet for each of those cases (2, 3, and 4). The user first modified the spreadsheet for each case and the results appeared in "current results". Then, the copy-paste button above the intended column was pressed to copy those results into that column.

The steps are: (1) modify the spreadsheet (2) enter a description in the appropriate column for that configuration, e.g. enter "Roth conv, SS at 70/66, $70k exp" in Column H (rows 10 to 12). Click on the copy-paste button in Cell H9. You now have a record of the results.

How do you know what you did to get those 4 scenarios? That's a very important question and it's not obvious. Scroll down to Row 493 "Results and Input Data Storage". It contains the summary from the top of the spreadsheet as well as every setting made to get those results (Rows 502 to 832).

To save the configuration, you need to run the copy-paste macro in Row 497. If you want to rerun the case, you'll have to manually update the data as shown in the relevant column - but at least you have it.


This section also shows the base vs. full case comparisons.

Looking at this further, I think the summary at the top of the page should not be separated from the full information in that manner. Copying the current results from Column E to (Column G, H, or I) should copy everything (results and settings from the top and bottom sections) to the appropriate column in one fell-swoop. I'll see what I can do. It should be a straight-forward macro update.

Update: See below.
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.
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Wed Oct 14, 2020 6:42 pm Looking at this further, I think the summary at the top of the page should not be separated from the full information in that manner. Copying the current results from Column E to (Column G, H, or I) should copy everything (results and settings from the top and bottom sections) to the appropriate column in one fell-swoop. I'll see what I can do. It should be a straight-forward macro update.
Using the copy buttons in the summary automatically copies all the current entries to the corresponding column at the bottom of the page. The additional copy buttons at the bottom can be used to store settings if the summary section is not being used. I believe this is what you are referring to.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

You're absolutely right. I have corrected my post. I thought they were separate because the macro buttons in Row 497 also copied the columns in that section.

I now see that you've documented this in the "copy" comment in cell E9.

FYI - I'm currently using LibreOffice Calc in Linux. Everything runs fine, but the macro buttons in Row 497 seem to be offset by a row. In LibreOffice Calc, they appear in Row 495. I guess that's yet another minor difference between MS Excel and LibreOffice Calc.

Also, comments for cells D9 and E9 (Results Summary, copy) aren't showing (no red highlight to see that a comment is there). No clue why.

Everything I've done so far was in MS Office 2016. I work in MS Excel, but occasionally check with LibreOffice Calc.
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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Upon further review of my earlier explanation for "copy-paste", I can now see that I didn't understand the cell comments. I propose three clarifications:

Cell D9 "Results Summary": Change "Three" to "Four", modify Note to explain use of the Storage section, add manual instructions that you also need to copy the storage settings.

From:
Three storage columns are provided for saving summary results of your model. Enter a 3-line description first, then use macro buttons or menu commands to copy current results to one of the three storage lines. (i.e. copy-paste values)

Note: Using the macros also copies all Setup page entries to the respective storage column (1 to 1, etc) in the Case Results and Input Data Storage section at the bottom of this page.

Manual method:
1. Select the nine rows of "current results" data.
2. Copy using menu icon or Ctrl-C.
3. Select one of the columns by selecting the first row below the comment area.
4. Paste values using menu icon or right-click and Paste Special - Values.
To:
Four storage columns are provided for saving summary results of your model. Enter a 3-line description first, then use the "copy-paste" macro buttons to copy current results to one of the four storage columns.

Note: These macros also copy (overwrite) all Setup page entries to the respective storage column (1 to 1, etc) in the Results and Input Data Storage section at the bottom of this page. Use this section to reproduce the entries which created the case for the relevant column.

Manual method:
1. Select the nine rows of "current results" data.
2. Copy using menu icon or Ctrl-C.
3. Select one of the columns by selecting the first row below the comment area.
4. Paste values using menu icon or right-click and Paste Special - Values.
5. Scroll down to the Results and Input Data Storage Section.
6. Select rows 499 to 833 of "current results" data.
7. Copy using menu icon or Ctrl-C.
8. Select one of the columns by selecting the first row below the comment area.
9. Paste values using menu icon or right-click and Paste Special - Values.
(1 of 3)
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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

(2 of 3) Cell E9, "Copy": Add - that storage settings should also be copied.

From:
Use these copy-paste buttons to copy the current results summary numbers to the storage column below the button. This will also copy all the settings to the storage section at the bottom of this page.

Or just copy in results from current or prior results.

Macros:
copy_sum1
copy_sum2
copy_sum3
copy_sum4
To:
Use these copy-paste buttons to copy the current results summary numbers to the storage column below the button. This will also copy all the settings to the storage section at the bottom of this page.

Or just copy in results from current or prior results. Be sure to also copy the settings in the storage section at the bottom of this page.

Macros:
copy_sum1
copy_sum2
copy_sum3
copy_sum4
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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

(3 of 3) Cell G495, "Store settings and results in this area:" Be very clear that the macros at the top of the page will overwrite the data.

From:
Click on one of the four macro buttons to copy and paste current results into the storage area below it, or click the "copy results" macro button to copy the results, then paste the values manually into any storage column.

Enter a description in the four rows below each macro button.

Note: Using the four copy macros on the Setup page at the Summary Results section also copies and stores the Current Results data to the first three columns (1,2,3,4).

Manual method:
1. Select current results between yellow labels
2. Copy using menu or Ctrl-C
3. Select one of the top cells in this area
4. Paste data using menu or right-click and Paste Special - Values
To:
Click on one of the four macro buttons to copy and paste current results into the storage area below it, or click the "copy results" macro button to copy the results, then paste the values manually into any storage column.

Enter a description in the four rows below each macro button.

Caution: Using the four copy macros at the top of the Setup page (Summary Results section) will overwrite the storage settings (1,2,3,4).

Manual method:
1. Select current results between yellow labels
2. Copy using menu or Ctrl-C
3. Select one of the top cells in this area
4. Paste data using menu or right-click and Paste Special - Values
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.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Separately, LibreOffice Calc in Linux is not displaying the comments in Row 9 and Row 495 (where I proposed the changes), as well as a few other cells.

I'm working in Windows 10 and Microsoft Excel 2016 for this spreadsheet. I also check compatibility with LibreOffice Calc in Linux.

I'll see what I can do. Resolving compatibility differences between competing products can not be guaranteed, i.e. it might not have a fix.

You can always have 2 separate versions - one for Microsoft Excel and one for LibreOffice Calc, but that's twice the work and should be avoided if possible.
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.
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Thu Oct 15, 2020 4:06 pm (3 of 3) Cell G495, "Store settings and results in this area:" Be very clear that the macros at the top of the page will overwrite the data.
The above three comment edits provide needed updates and a better explanation and have been made in the next version. Thanks much.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

stvyreb wrote: Wed Oct 14, 2020 1:30 pm
"Is there an RPM-for-dummies type guide in one of these posts?"
Don't know anything about dummies but I have been using RPM for a few months, and am still learning. I used the following steps when I began:

1. Model a very simple example of a MFJ retired couple with pensions, SS, pays federal taxes and have a Taxable account, then
2. Examine and make sure that I understand most of the:
----- A) "Summary" page first,
----- B) "Results" page second, and
----- C) "Details" page, then
3. Add one complication (first one could be IRA, second one SPIA, etc.), and
4. Go back to step 2 if I want to add more complications, or else step 5
5. Go back to step 1 with my own data.

Below is how I set up the case in step 1 above once I downloaded the model:

X) In the Setup / "Modeling Options" section, make sure the "Include ROTH conversions?" flag is set to "n" to bypass section 10 (Roth conversions)
IX) In the Setup / "Modeling Options" section, make sure the "Test future tax rate change?" flag is set to "n" to bypass section 9 (Alt. tax rates)
VIII) In the Setup / section 8 "Income taxes", enter:
----- 1. ZERO in 4 entries for "taxable account adjustment" to bypass the tax calculation on Taxable annual earnings
----- 2. "percent" for the state tax method, ZERO for the 2 current and future "percent method rate" to bypass the state tax calculation
VII) In the Setup / section 7 "Special Events" enter "n" for the 6 "future event - include in portfolio?" and "QCD - include?" flags to bypass section 7
VI) In the Setup / section 6 "IRA contributions & Withdrawals" enter ZERO for all blue entries under the "Annual" column to bypass section 6
V) Leave the section 5 unchanged - keep the valued in the downloaded model
IV) In the Setup / section 4 "Income" - enter ZERO for the SPIA Purchase price to bypass the SPIA
*** II) In the Setup / section 2 "Portfolio Balances" - enter 1,000,000 for Taxable (to avoid negative values) and ZERO for all other accounts
*** III) In the Setup / section 3 "Return Rates and Allocation" enter:
----- 1. "c" as "method to use" (asset class)
----- 2. 60% stock, 40% bond, and 0% tax-exempt (or your preferred AA) - same AA for ALL current and future accounts.

Note that RPM rebalances annually within each account but not across accounts in order to maintain a desired portfolio AA. In other words if you specify your desire for 100% stock in Taxable and 100% bond in T-IRA, RPM does not provide you an option to maintain a portfolio AA of 60% stock / 40% bond by rebalancing across the Taxable and T-IRA accounts.

The first resulting "Summary" / "Results" / "Details" pages should be straightforward, showing Taxable and its earning, your pension, other income and SS at the specified time, cost of living and taxes. You can now add in other complication such as IRA count value in section 2 and repeat the learning process.
Last edited by DSBH on Fri Oct 16, 2020 4:54 pm, edited 1 time in total.
John C. Bogle: "Never confuse genius with luck and a bull market".
Tattarrattat
Posts: 304
Joined: Wed Aug 19, 2020 6:05 pm

Re: Retiree Portfolio Model

Post by Tattarrattat »

Thank you! Nicely organized. I am going to combine this with my MrDrinkWater cheat sheet to really have a go at that spreadsheet.
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

2 questions,

is the "Portfolio Cash Flow Diagram" upper right just an example, I am using LibreOffice, and it doesn't seem to change at all ? ; though everything else seems functional

2)
for Inherited IRAs how does one indicate allocations , does it use the IRA1 Class Method bond/stock splits? Even though, IIRA is not included in IRA1 in section 2, portfolio balances?

3)
for E273 it says : " $D$273 Enter the taxable account growth expected in the current year based on historic results.
The amount calculated by model for the first year Taxable account growth is shown ( )and can be used when the Alternative setting is set to 0 or blank. Not recommended."

My Cell E273 is empty , but E276 has some number in it, that apparently is "growth" as if I Zero it out then the "view federal income for the year" no longer has the correct AGI , sigh

for Taxes, does the Model, use the Right hand side Federal Tax Calculator to populate Cells like E276 (net growth distributed...)
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Fri Oct 23, 2020 6:51 pm 2 questions,

is the "Portfolio Cash Flow Diagram" upper right just an example, I am using LibreOffice, and it doesn't seem to change at all ? ; though everything else seems functional

It's functional and works in Excel, but apparently not in Libre.

2)
for Inherited IRAs how does one indicate allocations , does it use the IRA1 Class Method bond/stock splits? Even though, IIRA is not included in IRA1 in section 2, portfolio balances?

As documented in the cell comments, inherited IRA1 and IRA2 use the factors entered for IRA1 and IRA2.

3)
for E273 it says : " $D$273 Enter the taxable account growth expected in the current year based on historic results.
The amount calculated by model for the first year Taxable account growth is shown ( )and can be used when the Alternative setting is set to 0 or blank. Not recommended."

My Cell E273 is empty , but E276 has some number in it, that apparently is "growth" as if I Zero it out then the "view federal income for the year" no longer has the correct AGI , sigh

I recommend entering taxable account estimates in both 273 and 276. No sure why zeroing them out would impact the estimated AGI. Will look at that.

for Taxes, does the Model, use the Right hand side Federal Tax Calculator to populate Cells like E276 (net growth distributed...)

The calculator is just a stand-alone tax calculator as documented in the cell comments.
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 »

E276 and E277 are populated by the model

or only by the user ?

I don't really understand the terminology yet somehow the "federal tax for any year" to the right of the cells has about the right amount of AGI
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Fri Oct 23, 2020 8:10 pm E276 and E277 are populated by the model

or only by the user ?

I don't really understand the terminology yet somehow the "federal tax for any year" to the right of the cells has about the right amount of AGI
All blue cells are user supplied. Any data there may be left over from the Example data that was not cleared.
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 »

so doesn't the model determine

Taxable Account Earnings Adjustment to determine taxable earnings
Annual Growth: Enter expected taxable account growth in the first or a typical year.

so is this somehow overriding what growth the model is calculating based on the user's data inputted with the Balances and Return rates?
User avatar
Topic Author
BigFoot48
Posts: 3112
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Fri Oct 23, 2020 10:07 pm so doesn't the model determine

Taxable Account Earnings Adjustment to determine taxable earnings
Annual Growth: Enter expected taxable account growth in the first or a typical year.

so is this somehow overriding what growth the model is calculating based on the user's data inputted with the Balances and Return rates?
No, it's calculating an amount to deduct from taxable account income representing the growth in the account that are not distributed as taxable income. The cell comment:

The annual earnings of the taxable account includes asset appreciation plus distributed interest, ordinary and qualified dividends and short and long-term capital gains. Not all of these elements are taxable.

The factors in this section are designed to reduce the taxable account earnings by an estimate you make of the non-taxable and lower-rate taxable items.

Instructions for each entry are in the descriptions' cell comments.
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 »

this whole section I'm totally clueless to translate, maybe, if someone could give me a real life example?

--
Taxable Account Earnings Adjustment to determine taxable earnings
Annual Growth: Enter expected taxable account growth in the first or a typical year.
Tax-Exempt Earnings: Enter estimate to determine net taxable growth.
Net Yearly Growth: amount that will be taxed at marginal, LTCG or dividend rates
Dividends and STCG: Enter estimate of these included in net yearly growth
Qualified Dividends and LTCG: Enter estimate of these included in net yearly growth
Taxable Growth: Amount of annual taxable account growth subject to user's marginal tax rates.

cell popup comments are difficult IMHO
$D$272 The annual earnings of the taxable account includes asset appreciation plus distributed interest, ordinary and qualified dividends and short and long-term capital gains. Not all of these elements are taxable.

The factors in this section are designed to reduce the taxable account earnings by an estimate you make of the non-taxable and lower-rate taxable items.

Instructions for each entry are in the descriptions' cell comments.

$E$272 Insert amounts the typical yearly brokerage/taxable account growth and distributed earnings.

Use 0 in fields to use the model's current year Taxable account amounts shown on the right instead but estimates of distributions are still required. Not recommended.

Analyze your actual Taxable account amounts from a recent year to get your typical taxable account earnings and distributions.

These amounts are used to calculate the percentages shown below, which are used to calculate the adjustment for all future years.
$G$272 Separate calculation for the Base case added in 20.1 to provide correct calculation if 2020 RMD is set to zero in Full case.
$D$273 Enter the taxable account growth expected in the current year based on historic results.

The amount calculated by model for the first year Taxable account growth is shown and can be used when the Alternative setting is set to 0 or blank. Not recommended.


$D$274 Enter an estimate of the yearly exempt earnings included in a typical year in the taxable ccount.

The amount calculated by model for the first year Taxable account tax-exempt earnings is shown and used when the setting is set to 0 or blank. Not recommended.

The tax exempt investment percentage is set in the Return Rates and Allocation section. The current percentage is shown in this setting's title.

Tax exempt earnings are automatically deducted from taxable account earnings to determine taxable income.

$K$275 Qualified Dividends and Long-Term Capital Gains are subtracted from Federal taxable income here. While a portion of these may be taxed on your Federal return, the rates are below the marginal rates, so for modeling purposes and simplicity they are excluded from taxable Federal income (but not state).

$D$276 Enter an estimate of how much of the net yearly growth, as shown above, will be distributed for the year as interest, dividends, qualified dividends and short and long term capital gains.

This will create the rate shown to the right that will be used in all model years.

Dividends and short-term capital gains on stock investments are taxed at your marginal tax rate.

Qualified dividends and long-term capital gains, as of 2018, are taxed at 0% for the lowest two tax brackets, 15% for the next three higher brackets, and 20% in the two highest.
$F$276 Percent of net yearly growth distributed which is income. This includes interest, dividends and capital gains. This rate will be used in all model years.
$X$276 Enter either your itemized deductions or the standard deduction. Standard:

2020:
Individual $12,400 (+1,650 if 65+)
Married: $24,800 (+$1,300/each if 65+)

2019:
Individual $12,200 (+1,650 if 65+)
Married: $24,400 (+$1,300/each if 65+)

$D$277 Enter an estimate of how much of the distributed income entered above are Qualified Dividends and Long-Term Capital Gains. While a portion of these may be taxed on your Federal return, the rates are below the marginal rates, so for modeling purposes and simplicity they are excluded from taxable Federal income.

This will create the rate shown to the right that will be used in all model years.


$F$277 Percent of net yearly growth to be excluded from Federal taxable income. This rate will be used in all model years.
--

what I did so far was just use my 2019 1040 to fill in E276 and E277 with my realized gains from 2019
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

stvyreb wrote: Sun Oct 25, 2020 5:00 pm this whole section I'm totally clueless to translate, maybe, if someone could give me a real life example?
--
First I changed the text color to Black in cells Setup/F278 and G278 so you can see the percentages, then I set up a simple case where a MFJ couple has $100,000 income and also $100,000 expenses, $4,000,000 taxable in 100% stock earning 5%/year.

Case #1 - enter 0 in cells Setup/E273/E274 and E276/E277, you should see 100% in F278 for the Details case and G278 for the Base case - so 100% of the gain in Taxable Account came from share price increase.
Now if you inspect the Adjusted Gross Income Calculation section of the "Details" tab (beginning in row 168) first year you should see:
Taxable (Account) earning = 200,000 (= 4M * 5%)
Less "Earning from Taxable Account" which is not taxable = -200,000 (=-100% * 200,000) since 100% of the gain came from share price increase,
Plus other income = 100,000
So Adjusted Gross Income = 200,000 - 200,000 + 100,000 = 100,000
Minus standard deduction = -24,800
Taxable income = 100,000 - 24,800 = 75,200
Minus 0% adjustment for LTCG/QD = 0
So Taxable Income Adjusted = 75,200
and Federal Income tax = 8,700

Case #2 - enter 200,000 (first year earning from Taxable Account = 4M * 5%) in cells Setup/E273, 0 in cell E274 (no tax-exempt),
120,000 in cell E276 - assuming that 60% of the 200,000 gain in Taxable Account was from Capital Gain and Dividend distributions, and
50,000 in cell E277 - assuming that 50,000 (41.67%) of the 120,000 distributed are either LTCG or qualified dividends,
you should see 40% in F278 and G278 - so 40% of the gain in the Taxable Account (80K out of 200K) came from share price increase,
Now if you inspect the Adjusted Gross Income Calculation section of the "Details" tab (beginning in row 168) first year you should see:
Taxable (Account) earning = 200,000 (= 4M * 5%)
Less "Earning from Taxable Account" which is not taxable = -80,000 (=-40% * 200,000) since 40% of the gain came from share price increase,
Plus other income = 100,000
So Adjusted Gross Income = 200,000 - 80,000 + 100,000 = 220,000
Minus standard deduction = -24,800
Taxable income = 220,000 - 24,800 = 195,200
Minus 25% (50K/200K) adjustment for LTCG/QD = -50,000
So Taxable Income Adjusted = 145,200, which is 75,200 from the previous case plus 70,000 (120,000-50,000) or ordinary dividend,
and Federal Income tax = 23,600

Hope this helps to illustrate a bit more on Bigfoot48's earlier notes.
[Edited - some wording and change from 60K to 50K for clarification)
Last edited by DSBH on Tue Nov 03, 2020 10:26 am, edited 1 time in total.
John C. Bogle: "Never confuse genius with luck and a bull market".
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

DSBH wrote: Mon Oct 26, 2020 11:23 am
stvyreb wrote: Sun Oct 25, 2020 5:00 pm this whole section I'm totally clueless to translate, maybe, if someone could give me a real life example?
--
First I changed the text color to Black in cells Setup/F278 and G278 so you can see the percentages, then I set up a simple case where a MFJ couple has $100,000 income and also $100,000 expenses, $4,000,000 taxable in 100% stock earning 5%/year.

Case #1 - enter 0 in cells Setup/E273/E274 and E276/E277, you should see 100% in F278 for the Details case and G278 for the Base case - so 100% of the gain in Taxable came from share price increase.
Now if you inspect the Adjusted Gross Income Calculation section of the "Details" tab (beginning in row 168) first year you should see:
Taxable earning = 200,000 (= 4M * 5%)
Less Non-taxable earning in Taxable = -200,000 (=-100% * 200,000) since 100% of the gain came from share price increase,
Plus other income = 100,000
So Federal Income = 200,000 - 200,000 + 100,000 = 100,000
Minus standard deduction = -24,800
Taxable income = 100,000 - 24,800 = 75,200
Minus 0% adjustment for LTCG/QD = 0
So Taxable Income Adjusted = 75,200
and Federal Income tax = 8,700
for "$100k income" are you putting that in Pension section?
for case #1 shouldn't E273/277 be $197,500 in order to get "100%" for details ?
but, for your example you want them to be 0% because it's all unrealized capital gains, no distributions?

sorry doesn't make sense, it must be you mean for all 197,500 to be taxable? ( I noticed that expenses somehow change the $1000k x 5% calculations, so it's not $200k
Post Reply