Retiree Portfolio Model
Re: Retiree Portfolio Model
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.
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.
Re: Retiree Portfolio Model
Looks like RPM handles this correctly but the new "feature" did not. I'll send the fix to BigFoot48. Thanks!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.
John C. Bogle: "Never confuse genius with luck and a bull market".
Re: Retiree Portfolio Model
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.
Re: Retiree Portfolio Model
I’ll check it out tomorrow. Thanks so much for good testing!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.
John C. Bogle: "Never confuse genius with luck and a bull market".
Re: Retiree Portfolio Model
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.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.
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
Re: Retiree Portfolio Model
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.BigFoot48 wrote: ↑Mon Jul 26, 2021 10:15 pmThe 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.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.
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?
Re: Retiree Portfolio Model
An update is now available that corrects the error reported by BH Exchme; Beta.1"
https://www.dropbox.com/s/maqf1i8qk7eoh ... .xlsm?dl=0
https://www.dropbox.com/s/maqf1i8qk7eoh ... .xlsm?dl=0
Retired |
Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Re: Retiree Portfolio Model
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.
Re: Retiree Portfolio Model
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
Re: Retiree Portfolio Model
Hello,
Looking for information on PA 529, I have 4 grandchildren looking to help them down the road. Any others ideas welcome.
Looking for information on PA 529, I have 4 grandchildren looking to help them down the road. Any others ideas welcome.
Re: Retiree Portfolio Model
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.
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.
-
- Posts: 92
- Joined: Sun May 31, 2020 12:06 pm
Re: Retiree Portfolio Model
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!
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!
Re: Retiree Portfolio Model
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.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!
Retired |
Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
-
- Posts: 277
- Joined: Tue Dec 23, 2014 10:30 am
Re: Retiree Portfolio Model
I've used this beta version, and I like it.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 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.
Re: Retiree Portfolio Model
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.
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
Re: Retiree Portfolio Model
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.
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".
Re: Retiree Portfolio Model
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.
#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.
Re: Retiree Portfolio Model
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.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.
Retired |
Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Re: Retiree Portfolio Model
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
Thank you
Re: Retiree Portfolio Model
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
Re: Retiree Portfolio Model
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 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?
Re: Retiree Portfolio Model
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
Re: Retiree Portfolio Model
[ 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!
Re: Retiree Portfolio Model
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 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.
Re: Retiree Portfolio Model
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!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.
Re: Retiree Portfolio Model
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!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!
Retired |
Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Re: Retiree Portfolio Model
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?
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?
Re: Retiree Portfolio Model
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.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.
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".
Re: Retiree Portfolio Model
The file settings change fixed it.
Re: Retiree Portfolio Model
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.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?
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
Re: Retiree Portfolio Model
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)
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)
Re: Retiree Portfolio Model
Yes you are correct, they should be divided by Round_To instead of 1,000.
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.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)
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".
Re: Retiree Portfolio Model
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.
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
Re: Retiree Portfolio Model
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".
Re: Retiree Portfolio Model
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.
Re: Retiree Portfolio Model
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
Re: Retiree Portfolio Model
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?
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?
Re: Retiree Portfolio Model
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.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?
[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".
-
- Posts: 4
- Joined: Wed Jul 07, 2021 12:07 pm
Re: Retiree Portfolio Model
Couldn't you just transfer the ending amounts from one RPM model into a second RPM workbook covering your additional years.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?
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.
-
- Posts: 4
- Joined: Wed Jul 07, 2021 12:07 pm
Re: Retiree Portfolio Model
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.)
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.)
Re: Retiree Portfolio Model
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.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.)
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
Re: Retiree Portfolio Model
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.
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
-
- Posts: 6
- Joined: Wed Oct 06, 2021 12:34 am
Re: Retiree Portfolio Model
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.
Re: Retiree Portfolio Model
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.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.
Retired |
Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
-
- Posts: 6
- Joined: Wed Oct 06, 2021 12:34 am
Re: Retiree Portfolio Model
OK, thanks for the quick reply. At least I didn't miss something in the model somewhere . Again, this model is absolutely fantastic. I can only imagine the amount of work that has gone into it.BigFoot48 wrote: ↑Wed Oct 06, 2021 9:22 amI 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.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.
Re: Retiree Portfolio Model
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!
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!
Re: Retiree Portfolio Model
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.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!
John C. Bogle: "Never confuse genius with luck and a bull market".
Re: Retiree Portfolio Model
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?
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?
Re: Retiree Portfolio Model
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".
Re: Retiree Portfolio Model
Appreciate the help & confirmation!