Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

Love the new beta feature!

One minor issue - If I don't enter an age to enter a new set of asset allocations in Change_Age, instead of sticking with the first entered value, it uses the second set of allocations right from the start. Should stick with Stock_Alloc_BegTar instead of switching to Stock_Alloc_ChgTar for instance.
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Exchme wrote: Mon Jul 26, 2021 5:39 pm Love the new beta feature!

One minor issue - If I don't enter an age to enter a new set of asset allocations in Change_Age, instead of sticking with the first entered value, it uses the second set of allocations right from the start. Should stick with Stock_Alloc_BegTar instead of switching to Stock_Alloc_ChgTar for instance.
Looks like RPM handles this correctly but the new "feature" did not. I'll send the fix to BigFoot48. Thanks!
John C. Bogle: "Never confuse genius with luck and a bull market".
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

Looking at the new beta, when I enter year by year IRA withdrawals (not Conversions, those work) on the Setup sheet, something seems wrong. When I enter a withdrawal, it didn't withdraw, it added! and the amount it added was a few percent of what I tried to withdraw.
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Exchme wrote: Mon Jul 26, 2021 8:42 pm Looking at the new beta, when I enter year by year IRA withdrawals (not Conversions, those work) on the Setup sheet, something seems wrong. When I enter a withdrawal, it didn't withdraw, it added! and the amount it added was a few percent of what I tried to withdraw.
I’ll check it out tomorrow. Thanks so much for good testing!
John C. Bogle: "Never confuse genius with luck and a bull market".
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Mon Jul 26, 2021 8:42 pm Looking at the new beta, when I enter year by year IRA withdrawals (not Conversions, those work) on the Setup sheet, something seems wrong. When I enter a withdrawal, it didn't withdraw, it added! and the amount it added was a few percent of what I tried to withdraw.
The Beta had no impact on the Roth Conversion calculations. Withdrawal amounts are entered as positive numbers. The amount entered for each year should be the amount shown on the Detail page as the amount withdrawn. Or are you using the Calculated method? Those percentages appear to be working.

If you are referring to IRA1 and IRA2 withdrawals those amounts entered and percentage changes appear to be working. Again, withdrawals are entered as positive numbers.
Can you provide more details?
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

BigFoot48 wrote: Mon Jul 26, 2021 10:15 pm
Exchme wrote: Mon Jul 26, 2021 8:42 pm Looking at the new beta, when I enter year by year IRA withdrawals (not Conversions, those work) on the Setup sheet, something seems wrong. When I enter a withdrawal, it didn't withdraw, it added! and the amount it added was a few percent of what I tried to withdraw.
The Beta had no impact on the Roth Conversion calculations. Withdrawal amounts are entered as positive numbers. The amount entered for each year should be the amount shown on the Detail page as the amount withdrawn. Or are you using the Calculated method? Those percentages appear to be working.

If you are referring to IRA1 and IRA2 withdrawals those amounts entered and percentage changes appear to be working. Again, withdrawals are entered as positive numbers.
Can you provide more details?
My bad. I was looking at something I hadn't tried before, using the cells Setup!D344:D383. I thought they were going to withdraw from IRA 1 since the layout suggested to my brain that this table was operating on IRA 1. But it is withdrawing correctly from the Roth instead. Sorry for the confusion.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

An update is now available that corrects the error reported by BH Exchme; Beta.1"
https://www.dropbox.com/s/maqf1i8qk7eoh ... .xlsm?dl=0
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
thor111
Posts: 90
Joined: Sun Jun 06, 2021 6:53 pm

Re: Retiree Portfolio Model

Post by thor111 »

another small error: When loading data from a previous model, the new field keep_AA defaults to "n", instead of "o" for original. Unless I messed something up. :D
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

thor111 wrote: Sun Aug 01, 2021 3:34 pm another small error: When loading data from a previous model, the new field keep_AA defaults to "n", instead of "o" for original. Unless I messed something up. :D
Oops. That variable started out as "n" but was changed to "y" but the clear macro was not updated. Fixed now. Download again if needed. Thanks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Ineed$
Posts: 51
Joined: Wed Jun 26, 2013 4:31 pm

Re: Retiree Portfolio Model

Post by Ineed$ »

Hello,

Looking for information on PA 529, I have 4 grandchildren looking to help them down the road. Any others ideas welcome.
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 »

Hi,

I assume you're not asking about the spreadsheet (what this thread is for).

Please start a new thread in the Personal Finance (Not Investing) forum (this one) and explain your situation in detail. Include your state of residence (PA?). We'll be happy to help.
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.
PaulieLilly
Posts: 92
Joined: Sun May 31, 2020 12:06 pm

Re: Retiree Portfolio Model

Post by PaulieLilly »

I am a recent early retiree pondering future Roth conversions during my gap years. I settled in on the RPM as my model of choice and I've been working extensively with the previous RPM version. As I honed in on my best options, I recognized the limitation of controlling my AA became a significant issue. My work-around was to trend & average my AA based off the year-by-year Summary Account Balances, calculate the necessary adjustments, then go back and manually override the Return Rates as-needed in rows 22-27 on the Details tab. I've been able to effectively maintain my overall 60/40 AA target in this manner and the results are enlightening.

Then ... I stumbled upon this new Beta version which "automatically" addresses the overall AA issue! I ran my lead models thru the Beta version and got comparable results! The Beta end-portfolio results were a little higher than my "manual" results as I maintained a portion of my Taxable account in cash, but the differentials were in-line.

I am very grateful to BigFoot48 for creating, donating & maintaining this tool ... and all the BH's who've helped along the way!
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

PaulieLilly wrote: Sat Aug 07, 2021 3:01 pm Then ... I stumbled upon this new Beta version which "automatically" the overall AA issue! I ran my lead models thru the Beta version and got comparable results! The Beta end-portfolio results were a little higher than my "manual" results as I maintained a portion of my Taxable account in cash, but the differentials were in-line.

I am very grateful to BigFoot48 for creating, donating & maintaining this tool ... and all the BH's who've helped along the way!
Thanks very much PaulieLilly for your appreciation of RPM. Many BH's have indeed contributed to its performance, features and value over the years, with DSBH being the latest with his account allocation fix. So glad it worked for you which will help to move it from "beta" to "improved version" later this year.
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 »

DSBH wrote: Mon Jul 26, 2021 4:01 pm Many thanks to BH BigFoot48 who has been very gracious to offer the integration of this feature in RPM, and who has been very patient in working with me to get it to a beta version - instead of a potential user-unfriendly "research grade" version.

This feature attempts to maintain on an annual basis a user-specified portfolio Asset Allocation while trying to respect as much as possible user-specified individual account Asset Allocations by rebalancing assets in individual accounts.

<SNIP>

Looking forward to your comments/feedback/advice. Thank you in advance.
I've used this beta version, and I like it.

I like the seeing all data shown on "AA Summary" spreadsheet, especially the amount of IRMAA expense each plan year. On the "Details" spreadsheet, I like seeing that the IRMAA expenses get listed there, too, in the "Expenses" subsection, on the same line and in red text, "includes IRMAA $XXXXX".

This addition to the RPM spreadsheet should help those who want to evaluate how much additional IRMAA expense will have to be paid as part of the expense of doing Roth conversions or to just withdraw money from tax-deferred accounts for spending.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

An update to 21.2 Alloc Beta is available as of Aug 25 - Beta2
This corrects an over-allocation to the Traditional IRA account earning due to either Roth IRA conversions or withdrawals from Roth IRA Conversions. Thanks to BH DSBH for this.
Download: https://www.dropbox.com/s/b5hv7q6v4t1w3 ... .xlsm?dl=0

I anticipate the 21.2 Alloc version will continue as a beta test for a few more months, perhaps the rest of the year, and be adopted as the standard model Jan. 1 2022 at the latest.
Last edited by BigFoot48 on Wed Aug 25, 2021 6:31 pm, edited 1 time in total.
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 »

Some quick notes: this update corrects the errors that I discovered when I tested some more cases. There are 4 regular tests that I now conducted for each of several cases of mine, as well as the default RPM case:

1a and 1b. Set all account AA and the portfolio AA to be equal, e.g. 60/40 for the portfolio as well as Taxable, Roth and T-IRA accounts. Run RPM for both the With and Without Roth conversion options. The results should be the same no matter which "Maintain portfolio AA Target" option the users pick: "o" or "t-r" or "r-t",

2a and 2b. Set the stock and the bond average annual returns to be equal, e.g. 6% for both. Run RPM for both the With and Without Roth conversion options. The results should be the same no matter which "Maintain portfolio AA Target" option the users pick: "o" or "t-r" or "r-t".

The results should be the same because the Portfolio Allocation logic calculates the account earnings in the same manner as the original RPM, after performing the extra steps of portfolio asset allocation. Since either all AAs are the same or all average annual returns are the same, these 3 different asset allocation methods will yield the same earnings.
John C. Bogle: "Never confuse genius with luck and a bull market".
PHD-2
Posts: 29
Joined: Mon May 14, 2018 2:36 pm

Re: Retiree Portfolio Model

Post by PHD-2 »

I have 2 questions as I try to enter my data into the model. I have searched this thread and believe for #1 the model just doesn't handle it but wanted to confirm. I suspect my #2 will have the same issue but again, wanted to check in case I missed something.

#1 - I have a non spousal T-IRA I inherited years ago which I'm taking RMD's on. How should this be entered to correctly account for the previous inheritance and RMD's already in progress?

#2 - I have a non spousal Roth IRA I inherited years ago which I'm taking RMD's on. Same question - where to put this in the model so it's accounted for?

Thanks in advance.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

PHD-2 wrote: Sun Sep 05, 2021 2:57 am I have 2 questions as I try to enter my data into the model. I have searched this thread and believe for #1 the model just doesn't handle it but wanted to confirm. I suspect my #2 will have the same issue but again, wanted to check in case I missed something.

#1 - I have a non spousal T-IRA I inherited years ago which I'm taking RMD's on. How should this be entered to correctly account for the previous inheritance and RMD's already in progress?

#2 - I have a non spousal Roth IRA I inherited years ago which I'm taking RMD's on. Same question - where to put this in the model so it's accounted for?

Thanks in advance.
You will need to enter those IRAs in the Account Balances section which is limited to two traditional and two Roth IRAs. The Inherited IRA feature could also be used for the Traditional but the balance won't be shown until the second year. In some cases existing IRA balances may be combined. Hopefully these will work for your situation.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Cara
Posts: 148
Joined: Wed Feb 28, 2018 1:02 pm

Re: Retiree Portfolio Model

Post by Cara »

What is the red "Changed: 25.2" indicator in cells Setup!D100 and D133 ? It's not a formula, it appears to be hard-coded?

Thank you
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Cara wrote: Mon Sep 06, 2021 9:24 am What is the red "Changed: 25.2" indicator in cells Setup!D100 and D133 ? It's not a formula, it appears to be hard-coded?

Thank you
That indicates that the Asset Allocation calculations are changed in the Beta version from the previous standard version.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SIY
Posts: 119
Joined: Sun Dec 13, 2020 11:32 pm

Re: Retiree Portfolio Model

Post by SIY »

I get ".xlsm files are supported but something went wrong." when trying to download.
I try to download from the file at the top of the first post, the one with the largest ver #.
I'm using Brave the browser on an MacBook Pro.

Any ideas why, or how to resolve?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

SIY wrote: Mon Sep 06, 2021 6:26 pm I get ".xlsm files are supported but something went wrong." when trying to download.
I try to download from the file at the top of the first post, the one with the largest ver #.
I'm using Brave the browser on an MacBook Pro.

Any ideas why, or how to resolve?
I see that message on the Dropbox website and think it's just a failure of Dropbox to display the model and can be ignored. The download button works.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
SIY
Posts: 119
Joined: Sun Dec 13, 2020 11:32 pm

Re: Retiree Portfolio Model

Post by SIY »

BigFoot48 wrote: Mon Sep 06, 2021 6:42 pm I see that message on the Dropbox website and think it's just a failure of Dropbox to display the model and can be ignored. The download button works.
[ quote fixed by admin LadyGeek]

Thanks for that reply. It was inspiring to see the message from you and to know you are still working on this project.

So - it did download, but wouldn't open in Google Sheets. I read that it MAY work in sheets, and I stubbornly wanted it to work there (finally gave up and got LibreOffice).
I haven't read through the Read me, and I will read all of it, but I was excited to enter some stuff and noticed that when I delete out the spouse info, I get "Error: no spouse age - y required above" just below that little section. It's probably covered later. I'm grateful for your work and to finally start to understand the impact of RMDs, conversions, and withdrawls!
DemoEngr
Posts: 61
Joined: Thu Aug 31, 2017 8:01 am

Re: Retiree Portfolio Model

Post by DemoEngr »

I seem to be doing something wrong trying to use the new feature.

I keep getting an error of a circular reference even if I have no money market or tax exempt.
I noticed the "Round_to" function was giving a slightly different calculation, must be my version of excel.

Fixed the function and all seems fine.
justgary
Posts: 37
Joined: Sat Jun 27, 2020 12:24 pm

Re: Retiree Portfolio Model

Post by justgary »

BigFoot48 wrote: Sun Sep 05, 2021 7:52 am You will need to enter those IRAs in the Account Balances section which is limited to two traditional and two Roth IRAs. The Inherited IRA feature could also be used for the Traditional but the balance won't be shown until the second year. In some cases existing IRA balances may be combined. Hopefully these will work for your situation.
Is it possible to easily add IRAs? Between my wife and I, we have several traditional, Roth, and inherited IRAs. Since my goal is to play "what if" with possible Roth conversions, it would be much easier to keep them all separate in the tool. Thanks for the great tool, by the way!
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

justgary wrote: Wed Sep 08, 2021 6:04 pm
Is it possible to easily add IRAs? Between my wife and I, we have several traditional, Roth, and inherited IRAs. Since my goal is to play "what if" with possible Roth conversions, it would be much easier to keep them all separate in the tool. Thanks for the great tool, by the way!
Unfortunately the only way to include multiple IRAs is to combine them as best you can and use the existing accounts. Perhaps some other users might have advice on they dealt with the many-IRA situation. Glad you like the model!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
legion80
Posts: 1
Joined: Thu Sep 09, 2021 4:07 am

Re: Retiree Portfolio Model

Post by legion80 »

Thank you for putting this together. I just learned about this from the FIRE subreddit.

Two items:
1) A feature request to allow the projections to go out another 20 years.
2) My taxable accounts include a large percentage of a single stock (RSUs) which I intend on selling in the coming years and then reinvesting to my target allocation. How do I best record the capital gains tax I'd have to pay on the sale?
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

DemoEngr wrote: Wed Sep 08, 2021 11:55 am I seem to be doing something wrong trying to use the new feature.

I keep getting an error of a circular reference even if I have no money market or tax exempt.
I noticed the "Round_to" function was giving a slightly different calculation, must be my version of excel.

Fixed the function and all seems fine.
I have seen warnings of circular reference when the Taxable account has negative balance, and hence the resulting outputs were not very meaningful (e.g. what is 100% negative stock holding ...). Fixing the negative Taxable balance issue by having more money coming in the Taxable account in some way(s) should fix the circular reference problem.

You can also look at FIle/Options/Formulas, and make sure that the "Enable iterative calculation" option is selected, and the "Maximum Iterations" and "Maximum Change" were set equal to 1,000 to allow Excel to iterate around the circular reference problem until you fix the negative Taxable balance issue (FWIW the most iterations I have seen was around 150 or so).
John C. Bogle: "Never confuse genius with luck and a bull market".
DemoEngr
Posts: 61
Joined: Thu Aug 31, 2017 8:01 am

Re: Retiree Portfolio Model

Post by DemoEngr »

The file settings change fixed it.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

legion80 wrote: Thu Sep 09, 2021 4:40 am Thank you for putting this together. I just learned about this from the FIRE subreddit.

Two items:
1) A feature request to allow the projections to go out another 20 years.
2) My taxable accounts include a large percentage of a single stock (RSUs) which I intend on selling in the coming years and then reinvesting to my target allocation. How do I best record the capital gains tax I'd have to pay on the sale?
RPM was created for retirees and near-retirees, hence the 40 year period. Extending that would open it up to many more users that have non-retiree needs involving yet more changes, so at this time the period will stay at 40. Appreciate the suggestion.

You can use the User Input line in the Federal AGI section on the Details page to enter an amount in the stock sale year that when taxed at your marginal rate for that year would approximate the additional capital gains taxes you will pay on the sale.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

A couple of things about the beta.

On the AA_Summary page, Column T, for both the Details and Base case, the IRMAA values are divided by 1000, when I believe they should be divided by Round_To, otherwise they will be incorrect if the user selects a different roundoff.

Not sure if this is a bug or something I'm missing, but it seems that when the taxable account has expenses ex taxes greater than additions, that a back check of the overall stock allocation at the start of the year will yield a number lower than target and when the additions to taxable are greater than expenses, the overall stock allocation at the start of the year will be greater than target. So for instance if I have lots of growth in taxable or other income, the overall asset allocation is above target at the start of every year, which seems odd. (I'm using the t-r option if that matters)
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Exchme wrote: Sat Sep 11, 2021 10:23 pm On the AA_Summary page, Column T, for both the Details and Base case, the IRMAA values are divided by 1000, when I believe they should be divided by Round_To, otherwise they will be incorrect if the user selects a different roundoff.
Yes you are correct, they should be divided by Round_To instead of 1,000.
Not sure if this is a bug or something I'm missing, but it seems that when the taxable account has expenses ex taxes greater than additions, that a back check of the overall stock allocation at the start of the year will yield a number lower than target and when the additions to taxable are greater than expenses, the overall stock allocation at the start of the year will be greater than target. So for instance if I have lots of growth in taxable or other income, the overall asset allocation is above target at the start of every year, which seems odd. (I'm using the t-r option if that matters)
Yes you are correct again, the overall asset allocation at the start of the year incorrectly includes the changes to Taxable before earning and taxes.

I will submit the fixes to BigFoot48. Thanks so much for excellent testing!
John C. Bogle: "Never confuse genius with luck and a bull market".
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

DSBH has corrected the problems reported by Exchme and made a couple of other minor corrections. The revised version Beta 3 is now available at: https://www.dropbox.com/s/u06jfg8p1msso ... .xlsm?dl=0

This correction had a very minor impact on overall results so updating is not necessary for most users. Thanks for reporting possible problems and thanks to DSBH for his quick addressing of the issues.
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 »

DSBH wrote: Sun Sep 12, 2021 10:21 am
Exchme wrote: Sat Sep 11, 2021 10:23 pm On the AA_Summary page, Column T, for both the Details and Base case, the IRMAA values are divided by 1000, when I believe they should be divided by Round_To, otherwise they will be incorrect if the user selects a different roundoff.
Yes you are correct, they should be divided by Round_To instead of 1,000.
Upon further inspection, I think that the IRMAA values in column T (and R and AG) should be divided by "Sum_Rnd" - cell W2 in sheet "Summary" currently set equal to 1,000, because most other values in the "AA Summary" page are retrieved from the "Summary" sheet which utilizes "Sum_Rnd" as display units.

I will submit the fixes to BigFoot48.
John C. Bogle: "Never confuse genius with luck and a bull market".
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

Not sure if others have pointed this out, but the embedded comment in Setup! E77 explaining the t-r vs. r-t allocation priorities seems backwards. The embedded comment in cell D77 on the same subject seems correct.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Wed Sep 15, 2021 9:07 am Not sure if others have pointed this out, but the embedded comment in Setup! E77 explaining the t-r vs. r-t allocation priorities seems backwards. The embedded comment in cell D77 on the same subject seems correct.
They are the same in both the E77 selection instructions and the D77 comment. What am I missing?

D77:
2. t-r to attempt to maintain the desired portfolio AA while trying to respect individual account AAs, while assigning stock to Taxable account first before ROTH accounts,

3. r-t to attempt to maintain the desired portfolio AA while trying to respect individual account AAs, and assign stock to ROTH accounts first before Taxable account.

E77:
o = original RPM portfolio allocation method
t-r = stock allocation in Taxable adjusted before Roth
r-t = stock allocation in Roth adjusted before Taxable
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

I didn't read those as the same. I guess it's the the word "adjusted" in E77 that throws me.

In my case, taxable started at 100% stock in both r-t and t-r so if it's adjusted first in t-r it sounds like it's reduced first. To keep others from getting confused, can you just repeat the priority as explained in D77?
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Exchme wrote: Wed Sep 15, 2021 5:07 pm I didn't read those as the same. I guess it's the the word "adjusted" in E77 that throws me.

In my case, taxable started at 100% stock in both r-t and t-r so if it's adjusted first in t-r it sounds like it's reduced first. To keep others from getting confused, can you just repeat the priority as explained in D77?
Well I thought that “adjusted” meant “worked on first” but I can see why it could be confusing, perhaps “allocated” is clearer. Repeating the priority as explained in D77 might be the simplest solution.

[Edit] For instance if an investor has a 1 million portfolio, wants 100,000 in stocks and 900,000 in bonds (10/90 portfolio AA) with 100% stock as desired AA for the Taxable account, the "t-r" option will attempt to allocate 100,000 of stocks to the Taxable account first, subject to what the Taxable account balance is. So if the Taxable account only has 60,000 dollars as account balance then the stock allocation in Taxable is adjusted to 60,000 from 100,000.
John C. Bogle: "Never confuse genius with luck and a bull market".
rmichaelsm
Posts: 4
Joined: Wed Jul 07, 2021 12:07 pm

Re: Retiree Portfolio Model

Post by rmichaelsm »

legion80 wrote: Thu Sep 09, 2021 4:40 am Thank you for putting this together. I just learned about this from the FIRE subreddit.

Two items:
1) A feature request to allow the projections to go out another 20 years.
2) My taxable accounts include a large percentage of a single stock (RSUs) which I intend on selling in the coming years and then reinvesting to my target allocation. How do I best record the capital gains tax I'd have to pay on the sale?
Couldn't you just transfer the ending amounts from one RPM model into a second RPM workbook covering your additional years.
You may be able to even just link back into the first for the values you need transferred so any changes in the first one carries forward to the 2nd.
rmichaelsm
Posts: 4
Joined: Wed Jul 07, 2021 12:07 pm

Re: Retiree Portfolio Model

Post by rmichaelsm »

I am using the RPM and it has been fantastic. Thank you to BigFoot48 for building this and everyone who has contributed to its evolution! As I am in the gap years before Medicare/SS/RMD hits, my main focus is evaluating the impact of and maximizing Roth conversions. One of my issues are investments providing considerable dividends and capital gains distributions. I need a way to provide more accurate tax impacts from Roth conversions. Utilizing the Excel file,"Federal Income Tax 1040 Excel Model - Glenn Reeves," that is linked to in the RPM file as a backend tool, I have built one approach. I placed a couple of worksheets on the front end leveraging the Data Table in the Whatif Analysis tool in Excel to cycle through a large sequence of Roth Conversion Amounts. This allows me to enter all of my other tax values along with the conversion amounts and estimates for dividends, capital gains, and any other data that impacts my income tax. This process can provide the calculated tax amounts broken into Qualified Dividend/Capital Gains brackets as well as each income tax bracket. I am looking now at interfacing this into my copy of the RPM next. I likely will do it in a similar fashion as the ORP hidden sheet is currently. Obviously, this is impacted by tax code changes, but it is the best way I have found to derive the actual taxes. Also, the added sheets reach into the forms for the data and can easily be reproduced in the future on top of new versions of his 1040 models.

I am wondering if there is already work that has been done on this type of process? Also, would others benefit from the work?

(I have also looked a LOT into many subscription financial planning software tools used by CFPs. Soon, I plan to get free versions of each to further double check my plans. It should be an interesting exercise.)
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

rmichaelsm wrote: Fri Sep 24, 2021 2:19 am I am using the RPM and it has been fantastic. Thank you to BigFoot48 for building this and everyone who has contributed to its evolution! As I am in the gap years before Medicare/SS/RMD hits, my main focus is evaluating the impact of and maximizing Roth conversions. One of my issues are investments providing considerable dividends and capital gains distributions. I need a way to provide more accurate tax impacts from Roth conversions. Utilizing the Excel file,"Federal Income Tax 1040 Excel Model - Glenn Reeves," that is linked to in the RPM file as a backend tool, I have built one approach. I placed a couple of worksheets on the front end leveraging the Data Table in the Whatif Analysis tool in Excel to cycle through a large sequence of Roth Conversion Amounts. This allows me to enter all of my other tax values along with the conversion amounts and estimates for dividends, capital gains, and any other data that impacts my income tax. This process can provide the calculated tax amounts broken into Qualified Dividend/Capital Gains brackets as well as each income tax bracket. I am looking now at interfacing this into my copy of the RPM next. I likely will do it in a similar fashion as the ORP hidden sheet is currently. Obviously, this is impacted by tax code changes, but it is the best way I have found to derive the actual taxes. Also, the added sheets reach into the forms for the data and can easily be reproduced in the future on top of new versions of his 1040 models.

I am wondering if there is already work that has been done on this type of process? Also, would others benefit from the work?

(I have also looked a LOT into many subscription financial planning software tools used by CFPs. Soon, I plan to get free versions of each to further double check my plans. It should be an interesting exercise.)
Not specifically for your ROTH analysis, but my variant RPM Monte Carlo model also uses a data table to cycle the RPM model through 1,000 Monte Carlo iterations where the inflation, stock, bond, and money market rates are varied on a year by year basis over the 40 year planning horizon.

I mention this to point out that using a data table works just fine with RPM, if that's what you're asking. What doesn't work is nesting data tables, so your variant and my variant won't play well together. However, your variant can find the best ROTH approach, and then my variant can test it under varying market conditions.

That said, I think that my variant is not widely used, so don't let it dissuade you from working on yours.

-B
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

A new version of RPM 21.2 Alloc Beta 4 is now available. Download link: https://www.dropbox.com/s/eetehcmm7atkh ... .xlsm?dl=0

This has minor text and comment corrections and improvements and the addition of a waterfall chart for Roth conversions by DSBH. Most users will not need to upgrade.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
NorthStars7
Posts: 6
Joined: Wed Oct 06, 2021 12:34 am

Re: Retiree Portfolio Model

Post by NorthStars7 »

Hi. I'm new to the RPM (which has been incredibly helpful) and have a question. I'm trying to model the much discussed Roth Conversion vs. Keeping ACA subsidy analysis. Is there a way that I can insert an additional expense by year (my lost ACA subsidy) into the default/full case portion of the model (not the base portion)? I can see where to add additional expense on the detail tab, but that appears to apply only to the base case portion. If I could get it into the default/full case portion, that would allow me to see the base case (no Roth conversion and no additional ACA expense) vs. a default/full case (Roth conversion AND increased expenses from losing the ACA subsidy). Apologies if this has been asked before, but I wasn't able to find a thread on it. Hopefully, I haven't missed something obvious in the RPM. Thanks for the help.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

NorthStars7 wrote: Wed Oct 06, 2021 12:59 am Hi. I'm new to the RPM (which has been incredibly helpful) and have a question. I'm trying to model the much discussed Roth Conversion vs. Keeping ACA subsidy analysis. Is there a way that I can insert an additional expense by year (my lost ACA subsidy) into the default/full case portion of the model (not the base portion)? I can see where to add additional expense on the detail tab, but that appears to apply only to the base case portion. If I could get it into the default/full case portion, that would allow me to see the base case (no Roth conversion and no additional ACA expense) vs. a default/full case (Roth conversion AND increased expenses from losing the ACA subsidy). Apologies if this has been asked before, but I wasn't able to find a thread on it. Hopefully, I haven't missed something obvious in the RPM. Thanks for the help.
I don't believe anyone has asked this question. There's no feature that will allow you to include that cost only in the conversion case results. You can either add/subtract the total ACA cost to the result totals and record it in the Results Summary or a Note section on the Setup page, or if you have Excel editing skills, record the ACA in the User Expense Adjustment on the Details page, then go to the Base page, unprotect it to allow editing, and remove the formulas on the User Expense Adjustment line that pulls data from the Details page. Glad you like the model.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
NorthStars7
Posts: 6
Joined: Wed Oct 06, 2021 12:34 am

Re: Retiree Portfolio Model

Post by NorthStars7 »

BigFoot48 wrote: Wed Oct 06, 2021 9:22 am
NorthStars7 wrote: Wed Oct 06, 2021 12:59 am Hi. I'm new to the RPM (which has been incredibly helpful) and have a question. I'm trying to model the much discussed Roth Conversion vs. Keeping ACA subsidy analysis. Is there a way that I can insert an additional expense by year (my lost ACA subsidy) into the default/full case portion of the model (not the base portion)? I can see where to add additional expense on the detail tab, but that appears to apply only to the base case portion. If I could get it into the default/full case portion, that would allow me to see the base case (no Roth conversion and no additional ACA expense) vs. a default/full case (Roth conversion AND increased expenses from losing the ACA subsidy). Apologies if this has been asked before, but I wasn't able to find a thread on it. Hopefully, I haven't missed something obvious in the RPM. Thanks for the help.
I don't believe anyone has asked this question. There's no feature that will allow you to include that cost only in the conversion case results. You can either add/subtract the total ACA cost to the result totals and record it in the Results Summary or a Note section on the Setup page, or if you have Excel editing skills, record the ACA in the User Expense Adjustment on the Details page, then go to the Base page, unprotect it to allow editing, and remove the formulas on the User Expense Adjustment line that pulls data from the Details page. Glad you like the model.
OK, thanks for the quick reply. At least I didn't miss something in the model somewhere :happy . Again, this model is absolutely fantastic. I can only imagine the amount of work that has gone into it.
jwb0007
Posts: 13
Joined: Fri Feb 19, 2021 1:07 pm

Re: Retiree Portfolio Model

Post by jwb0007 »

Re the latest ver of RPM, LOVE the program!

My question is if/how I might:

1. Reproduce an existing completed model (to which I have created/attached a couple of additional worksheets) in the new beta version?

2. Or, perform some sort of incremental update(?) to my present expanded model, so that I may keep the original, and enjoy the benefits of the updates?

Thanks for any responses!
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

jwb0007 wrote: Mon Oct 11, 2021 12:25 pm Re the latest ver of RPM, LOVE the program!

My question is if/how I might:

1. Reproduce an existing completed model (to which I have created/attached a couple of additional worksheets) in the new beta version?

2. Or, perform some sort of incremental update(?) to my present expanded model, so that I may keep the original, and enjoy the benefits of the updates?

Thanks for any responses!
I normally download the latest version, run the "clear" and "copy" macros (Setup!O1 and O2) to clear existing entries and copy (most) data over from my model in the previous version, then port over my additional custom worksheets while watching out for external references.
John C. Bogle: "Never confuse genius with luck and a bull market".
jwb0007
Posts: 13
Joined: Fri Feb 19, 2021 1:07 pm

Re: Retiree Portfolio Model

Post by jwb0007 »

DBSH,

Thank you for your response. When you say "port over" do you mean copy the original (to be copied) sheet over to the new model, and place it where I want in the latest model/version?
DSBH
Posts: 734
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

jwb0007 wrote: Mon Oct 11, 2021 3:28 pm DBSH,

Thank you for your response. When you say "port over" do you mean copy the original (to be copied) sheet over to the new model, and place it where I want in the latest model/version?
Yes, and either manually remove the external references (try Find and Replace) or copy over the formulas inside the cells, if any.
Last edited by DSBH on Tue Oct 12, 2021 6:11 pm, edited 1 time in total.
John C. Bogle: "Never confuse genius with luck and a bull market".
jwb0007
Posts: 13
Joined: Fri Feb 19, 2021 1:07 pm

Re: Retiree Portfolio Model

Post by jwb0007 »

Appreciate the help & confirmation!
Post Reply