Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Help with the "IRA withdrawals" section of the Retiree Portfolio Model

Post by DSBH »

lkutsilev wrote: Fri Mar 18, 2022 1:51 pm ...
I would like to retire at 65, but I want to start receiving SS at 70, and because of that, in the summary tab (and on one of the graphs) I see that between 66 and 69, my income might drop below my expenses.

So I am wondering if, and how, the "IRA withdrawals" will help me during those years.
...
I have used "IRA withdrawals" to test withdrawing money from my Trad-IRA account and depositing into the Taxable account to cover expenses.

The amount withdrawn using "IRA withdrawals" will appear in both "Details" and "Base" cases.

If you don't want the amount withdrawn from T-IRA to appear in the "Base" case and only in the "Details" case, you can enter an offset (equal but negative amount) using the "optional base case withdrawals" just below.
John C. Bogle: "Never confuse genius with luck and a bull market".
thor111
Posts: 90
Joined: Sun Jun 06, 2021 6:53 pm

Re: Retiree Portfolio Model

Post by thor111 »

BigFoot48 wrote: Fri Apr 01, 2022 7:44 am RPM version 22.4 is now available - Apr 1

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
Greetings BigFoot! Trying this new version (and I certainly don't need the new features) I quickly stumbled upon an issue (probably just my ignorance). When I initially imported my data, it immediately showed up with an error (Alert: Base case negative for 39 years). Assuming it was some of my customization that caused the problem, I systematically tried taking to my customization out to see when/if it would clear. I eventually gave up. Starting from the downloadable copy using the test data, the first cell I tried to change was Setup!E77. Changing it from "o" to "t-r" gave me the same error I had on my sheet. Is there something special that needs to be done to get the "t-r" function to work? This is obviously a low priority request, but I was curious. Great work btw!
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

thor111 wrote: Sun Apr 03, 2022 1:33 pm ... Starting from the downloadable copy using the test data, the first cell I tried to change was Setup!E77. Changing it from "o" to "t-r" gave me the same error I had on my sheet. Is there something special that needs to be done to get the "t-r" function to work? This is obviously a low priority request, but I was curious. Great work btw!
The Portfolio Asset Allocation feature ("t-r" or "r-t") only supports Stocks and Bonds (sum of %AA must be 1 between Stocks and Bonds) and 0% for Tax-Exempt and Money Market - please look for error messages further down.
John C. Bogle: "Never confuse genius with luck and a bull market".
RangeleyLake
Posts: 46
Joined: Mon May 20, 2019 7:03 pm

Re: Retiree Portfolio Model

Post by RangeleyLake »

thor111 wrote: Sun Apr 03, 2022 1:33 pm
BigFoot48 wrote: Fri Apr 01, 2022 7:44 am RPM version 22.4 is now available - Apr 1

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
Greetings BigFoot! Trying this new version (and I certainly don't need the new features) I quickly stumbled upon an issue (probably just my ignorance). When I initially imported my data, it immediately showed up with an error (Alert: Base case negative for 39 years). Assuming it was some of my customization that caused the problem, I systematically tried taking to my customization out to see when/if it would clear. I eventually gave up. Starting from the downloadable copy using the test data, the first cell I tried to change was Setup!E77. Changing it from "o" to "t-r" gave me the same error I had on my sheet. Is there something special that needs to be done to get the "t-r" function to work? This is obviously a low priority request, but I was curious. Great work btw!
FYI, I am getting the same error message whether I am using the test data or my data. Not sure where to look...Thanks for the work that is involved in maintaining RPM.

I am also seeing #N/A in the majority of the cells on the Base tab and the other tabs that has the Base results
RangeleyLake
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

RangeleyLake wrote: Sun Apr 03, 2022 4:02 pm
thor111 wrote: Sun Apr 03, 2022 1:33 pm
BigFoot48 wrote: Fri Apr 01, 2022 7:44 am RPM version 22.4 is now available - Apr 1

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
Greetings BigFoot! Trying this new version (and I certainly don't need the new features) I quickly stumbled upon an issue (probably just my ignorance). When I initially imported my data, it immediately showed up with an error (Alert: Base case negative for 39 years). Assuming it was some of my customization that caused the problem, I systematically tried taking to my customization out to see when/if it would clear. I eventually gave up. Starting from the downloadable copy using the test data, the first cell I tried to change was Setup!E77. Changing it from "o" to "t-r" gave me the same error I had on my sheet. Is there something special that needs to be done to get the "t-r" function to work? This is obviously a low priority request, but I was curious. Great work btw!
FYI, I am getting the same error message whether I am using the test data or my data. Not sure where to look...Thanks for the work that is involved in maintaining RPM.

I am also seeing #N/A in the majority of the cells on the Base tab and the other tabs that has the Base results
You’re both correct, I will check/test the portfolio allocation feature.
John C. Bogle: "Never confuse genius with luck and a bull market".
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

I have withdrawn the model pending a fix of the t-r allocation problem. Users using the original allocation method can continue to use the existing 22.4, but if not downloaded already might want to wait to download until this is fixed.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

DSBH has fixed the reference code that was causing the issue. Since this was early in the release it will continue to be 22.4 with an April 4 date. Thanks to all for reporting the problem.

Link: see April 5 update

Anyone that downloaded the Apr 1 version and who will be using the t-r/r-t allocations should download the corrected version.
Last edited by BigFoot48 on Wed Apr 06, 2022 12:41 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

It seems that v22.4 does not work with Excel 2003. When I press the 'clear entries button (cell O1), many #VALUE! errors show up in the worksheet. There is no such issue with v21.1a.
Thank you in advance for your help.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Sun Apr 03, 2022 9:47 pm It seems that v22.4 does not work with Excel 2003. When I press the 'clear entries button (cell O1), many #VALUE! errors show up in the worksheet. There is no such issue with v21.1a.
Thank you in advance for your help.
Thanks for that report that a prior version actually worked in Excel 2003. Excel 2003 is not supported as changes in Excel since then are used in the model. Excel 2013 is used to create and maintain the model. Other spreadsheet programs also work which are noted on the Readme page.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95696
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

goGators wrote: Sun Apr 03, 2022 9:47 pm It seems that v22.4 does not work with Excel 2003. When I press the 'clear entries button (cell O1), many #VALUE! errors show up in the worksheet. There is no such issue with v21.1a.
Thank you in advance for your help.
From a security perspective, you shouldn't be using software from 2003.

If you don't want to purchase Microsoft Office, then I can recommend the freeware equivalent: LibreOffice - Free Office Suite -Compatible with Microsoft

If you're having issues with your PC, please ask for help in the Personal Consumer Issues forum.
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.
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

I'm possibly seeing a bug with the SS benefits section in v22.4

No matter whether I choose y or n for "use alternative SS benefits", the model indicates "using" for alternative benefits and I do not see a change in the Results Summary at the top of the setup page.

I am running LibreOffice Calc.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

mkc wrote: Mon Apr 04, 2022 8:50 am I'm possibly seeing a bug with the SS benefits section in v22.4

No matter whether I choose y or n for "use alternative SS benefits", the model indicates "using" for alternative benefits and I do not see a change in the Results Summary at the top of the setup page.

I am running LibreOffice Calc.
The alternative benefits are used and the change seen in the example data. Check you alternative benefits settings to make sure they are complete and different from the default benefits.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

BigFoot48 wrote: Mon Apr 04, 2022 7:18 am Thanks for that report that a prior version actually worked in Excel 2003. Excel 2003 is not supported as changes in Excel since then are used in the model. Excel 2013 is used to create and maintain the model. Other spreadsheet programs also work which are noted on the Readme page.
Thanks BigFoot.
I did not know that Excel 2003 was no longer support. I guess the Readme page should be changed to reflect that. Below is what shown in the V22.4 Readme page
"...Excel 2003-2019 - best program. If using v2003 the 2007 format xlsm will open and work with a warning. Must be saved in xls format...."
LadyGeek wrote: Mon Apr 04, 2022 7:37 am From a security perspective, you shouldn't be using software from 2003.

If you don't want to purchase Microsoft Office, then I can recommend the freeware equivalent: LibreOffice - Free Office Suite -Compatible with Microsoft
Thank you LadyGeek. I do have a copy of LibreOffice on my PC.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Mon Apr 04, 2022 9:21 am Thanks BigFoot.
I did not know that Excel 2003 was no longer support. I guess the Readme page should be changed to reflect that. Below is what shown in the V22.4 Readme page
"...Excel 2003-2019 - best program. If using v2003 the 2007 format xlsm will open and work with a warning. Must be saved in xls format...."
Good suggestion. I removed 2003 (which I still use for my other models, btw) from the list.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

BigFoot48 wrote: Mon Apr 04, 2022 8:55 am
mkc wrote: Mon Apr 04, 2022 8:50 am I'm possibly seeing a bug with the SS benefits section in v22.4

No matter whether I choose y or n for "use alternative SS benefits", the model indicates "using" for alternative benefits and I do not see a change in the Results Summary at the top of the setup page.

I am running LibreOffice Calc.
The alternative benefits are used and the change seen in the example data. Check you alternative benefits settings to make sure they are complete and different from the default benefits.
I did try changing the age in the alternative benefits settings, and no matter where in the 63 to 70 range I change it to, somewhere it is "stuck" showing me taking in 2025 (age 63). The details/results do not change with y/n for "using alternative benefits", nor do the details/results change from that 2025/age 63.

Could there be another spot that age gets set? Found it. Have to go into "Results" and "show" benefit comparison.
Last edited by mkc on Mon Apr 04, 2022 10:05 am, edited 2 times in total.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

mkc wrote: Mon Apr 04, 2022 9:39 am
I did try changing the age in the alternative benefits settings, and no matter where in the 63 to 70 range I change it to, somewhere it is "stuck" showing me taking in 2025 (age 63). The details/results do not change with y/n for "using alternative benefits", nor do the details/results change from that 2025/age 63.

Could there be another spot that age gets set?
I assume your current age is below 63. It must be for the calculations to work. If so, are the calculated values different in the two SS calculation sections with different ages entered?
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

BigFoot48 wrote: Mon Apr 04, 2022 9:55 am
mkc wrote: Mon Apr 04, 2022 9:39 am
I did try changing the age in the alternative benefits settings, and no matter where in the 63 to 70 range I change it to, somewhere it is "stuck" showing me taking in 2025 (age 63). The details/results do not change with y/n for "using alternative benefits", nor do the details/results change from that 2025/age 63.

Could there be another spot that age gets set?
I assume your current age is below 63. It must be for the calculations to work. If so, are the calculated values different in the two SS calculation sections with different ages entered?
I am, and I think I found the issue. I had to go into "Results" and click "show" for "benefit comparison". Without it, it was stuck with me starting at 63, no matter what I did. Once I selected "show", it allowed the y/n to be functional for the "alternative benefits", and changing ages changed results.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

mkc wrote: Mon Apr 04, 2022 10:06 am I am, and I think I found the issue. I had to go into "Results" and click "show" for "benefit comparison". Without it, it was stuck with me starting at 63, no matter what I did. Once I selected "show", it allowed the y/n to be functional for the "alternative benefits", and changing ages changed results.
Well that's very odd. Showing or hiding those lines on the Results page should have no impact on calculations. I can't replicate your situation by showing/hiding the section so don't know what is really going on. Glad you got it working nonetheless!

Wait, running that macro also turns off and then on system updates and auto recalculation. Perhaps those settings were turned off somehow in your Excel program.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

BigFoot48 wrote: Mon Apr 04, 2022 10:18 am
mkc wrote: Mon Apr 04, 2022 10:06 am I am, and I think I found the issue. I had to go into "Results" and click "show" for "benefit comparison". Without it, it was stuck with me starting at 63, no matter what I did. Once I selected "show", it allowed the y/n to be functional for the "alternative benefits", and changing ages changed results.
Well that's very odd. Showing or hiding those lines on the Results page should have no impact on calculations. I can't replicate your situation by showing/hiding the section so don't know what is really going on. Glad you got it working nonetheless!

Wait, running that macro also turns off and then on system updates and auto recalculation. Perhaps those settings were turned off somehow in your Excel program.
I'm using LibreOffice Calc, not Excel. I'm guessing it's something odd in LibreOffice.

The other modeling (changing Roth, changing other values) appeared to auto recalculate (at least I would see results) with no issue - just the SS one seemed to act up.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Version 22.4a is now available.

Link: https://www.dropbox.com/s/89xpfdrg3le93 ... .xlsm?dl=0

This update fixes the amount of LTCG included in three tax summary total lines on the Details/Base pages. Users with LTCG, including the automatic calculation in the Tax setup section, may want to update as this fix may result in slightly lower Federal taxes. Users with little or no LTCG could skip it. Version changed to 22.4a.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

BigFoot48 wrote: Wed Apr 06, 2022 12:40 am Version 22.4a is now available.
Once again, thank you for creating this fantastic tool!

I recently used it to dial in the best "emptying scheme" for an inherited IRA. I put the essential pieces of the IRA in the Income section, and modified the annual amount and years of "income" to model the withdrawal options. It made a significant difference.

I've also used it in concert with OpenSocialSecurity to study the different age schemes.

Lastly, I've used it to model Roth conversions (and whether they made sense for us) for a variety of scenarios (including different SS schemes).

Wonderful tool!
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

Hello,
I'm using v22.4 with Libre Office.
It seems that RPM counts my income twice and that my "State taxable income" (Details > B231) is doubled and causes the "State income taxes" (Details > B240) 2x larger than what it should be.

In Setup, my main income is a $300K Roth conversion (Conv_Start_Amt) + $15K LTCG. The calculated federal tax (setup > L281) is about $60K which I think is correct.
My state does tax IRA withdrawal. If I set "IRA withdrawals (less QCD) to include" (setup > IRA_State) to 100%. The 5% State tax amount (L282) is $31K which is incorrect. If I set IRA_State to 0% then the state tax amount is $16.5K which is about right.
Thank you.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Fri Apr 08, 2022 11:19 am Hello,
I'm using v22.4 with Libre Office.
It seems that RPM counts my income twice and that my "State taxable income" (Details > B231) is doubled and causes the "State income taxes" (Details > B240) 2x larger than what it should be.

In Setup, my main income is a $300K Roth conversion (Conv_Start_Amt) + $15K LTCG. The calculated federal tax (setup > L281) is about $60K which I think is correct.
My state does tax IRA withdrawal. If I set "IRA withdrawals (less QCD) to include" (setup > IRA_State) to 100%. The 5% State tax amount (L282) is $31K which is incorrect. If I set IRA_State to 0% then the state tax amount is $16.5K which is about right.
Thank you.
Sorry about that! I have updated 22.4a. Please download again, test and verify if it is fixed now.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

BigFoot48 wrote: Fri Apr 08, 2022 11:44 am I have updated 22.4a. Please download again, test and verify if it is fixed now.
Thank you so much. It works!
Agent86
Posts: 29
Joined: Sun Mar 16, 2014 5:45 am

Re: Retiree Portfolio Model

Post by Agent86 »

Bigfoot48, again thank you for an ever expanding tool.

As I am modeling my transition to retirement, there will be some 3rd party additions into my retirement funds that are not paid directly by me through earnings.

Is there a way to add funding to an IRA (Roth or Traditional), from external sources - i.e. employer match, retirement 'bonuses' etc.? other then an inherited IRA?

Thanks,

Agent86
davidski
Posts: 22
Joined: Sun Jul 08, 2018 9:21 pm

Re: Retiree Portfolio Model

Post by davidski »

One substantive question on V24a and a couple minor nits. Thanks so much for the continued support.

Long term capital gains (LTCGs)
I'm not following how RPM is calculating the LTCG effective rate. For a scenario where the retiree is making Roth conversions, I understood this to increase ordinary income, meaning that LTCGs should be greater than 0%. In a case with a computed ordinary income of $163K (Details!F558) and Taxable Income of $168K (Details!F556), I'm seeing RPM calculate zero taxable on LTCGs of around $5K. Shouldn't those be taxed at the 15% bracket level?

Nits
- Details!B561 - Typo in this entry. Reads "lione" instead of line.
- Details!F8 - The cell note says that a green colored cell indicates a different formula than the cells to the right. The color used in the sheet indicating a different formula is blue. :wink:
Last edited by davidski on Sun Apr 10, 2022 6:19 pm, edited 1 time in total.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

davidski wrote: Sun Apr 10, 2022 9:25 am One substantive question on V24a and a couple minor nits. Thanks so much for the continued support.
Thanks for reporting those items. Will check them out this week.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Agent86 wrote: Sun Apr 10, 2022 8:20 am Bigfoot48, again thank you for an ever expanding tool.

As I am modeling my transition to retirement, there will be some 3rd party additions into my retirement funds that are not paid directly by me through earnings.

Is there a way to add funding to an IRA (Roth or Traditional), from external sources - i.e. employer match, retirement 'bonuses' etc.? other then an inherited IRA?

Thanks, Agent86
You are very welcome. Use the Setup 6. IRA contributions & withdrawals section to add to existing or new IRAs, limited to two traditional and one Roth. IRAs may need to be combined due to the limitation. Hope that works for you.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Agent86
Posts: 29
Joined: Sun Mar 16, 2014 5:45 am

Re: Retiree Portfolio Model

Post by Agent86 »

Bigfoot48, thank you for the quick reply.

It was unclear to me if Section 6, funds allocation came from the taxable inputs or were external inputs. Based on you response, they are external and independent inputs into the various funding buckets - both IRA and Roth.

Therefore, once funds are input into taxable (income - Section 4), the only output is through the Taxes and Expenses (Section 5 and 8) sections - and optionally Specials (Section 7) and Roth Conversions (Section 10).

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

davidski wrote: Sun Apr 10, 2022 9:25 am One substantive question on V24a and a couple minor nits. Thanks so much for the continued support.

Long term capital gains (LTCGs)
I'm not following how RPM is calculating the LTCG effective rate. For a scenario where the retiree is making Roth conversions, I understood this to increase ordinary income, meaning that LTCGs should be greater than 0%. In a case with a computed ordinary income of $163K (Details!F558) and Taxable Income of $168K (Details!F556), I'm seeing RPM calculate zero taxable on LTCGs of around $5K. Shouldn't those be taxed at the 15% bracket level?
BH DSBH, who programmed the LTCG taxes, and I have not been able to duplicate what you are seeing. By creating a taxable income above the LTCG bracket 1 of $83,000 and having a LTCG amount we both are seeing it get taxed at 15%. We used the User Input lines at 181-AGI and 206-LTCG to test it and the tax is being calculated. If you are still seeing a problem I will need to get a copy of your model to see what's going on. Thanks for the report.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
davidski
Posts: 22
Joined: Sun Jul 08, 2018 9:21 pm

Re: Retiree Portfolio Model

Post by davidski »

Thanks for looking at this!

I'll whip up an example spreadsheet. As a minimal example here's some steps to reproduce:

1) Starting with v22.4a, use the clear macro to wipe the sheet.
2) In Details!F181 (user AGI adjustment) put in a large value, such as 200000
3) In Details!F206 (LTCG adjustment) put in a value, such as 10000.
4) Note that Details!F206 (LTCG 15% calculation) is 0. This is, I believe, an error.

David
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

davidski wrote: Mon Apr 11, 2022 5:50 pm Thanks for looking at this!

I'll whip up an example spreadsheet. As a minimal example here's some steps to reproduce:

1) Starting with v24.a, use the clear macro to wipe the sheet.
2) In Details!F181 (user AGI adjustment) put in a large value, such as 200000
3) In Details!F206 (LTCG adjustment) put in a value, such as 10000.
4) Note that Details!F206 (LTCG 15% calculation) is 0. This is, I believe, an error.

David
Hmmm, if you add those amounts to the existing sample data you get $1,600 of LTCG taxes, the correct amount. I added them to my personal model and it showed the correct amount. Perhaps something in or not in your data is preventing the calculation. Will probably need to see your example.

Update: there may be an issue with the Single calculation. Investigating.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
rts58
Posts: 88
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 »

davidski wrote: Mon Apr 11, 2022 5:50 pm
4) Note that Details!F206 (LTCG 15% calculation) is 0. This is, I believe, an error.
You may be looking at the wrong row?
I took a look at my model. Details!F206 is actual for User Input LTCG, F205 is the calculated cell.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

RPM version 22.5 is now available. Download link: https://www.dropbox.com/s/27c05ug25psj2 ... .xlsm?dl=0

This update fixes an issue with the new LTCG tax calculations and those who file taxes as a single. It also provides a macro at the top of Setup that changes the Example data on the Setup page from a married couple to a single. This will help single users in getting started and in debugging future changes to make sure both tax filing methods work with updates and changes.

Single users with LTCG should upgrade. Optional for all other users. As always, please let me know of any issues with this version.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
kleiner
Posts: 346
Joined: Tue Jun 02, 2020 8:45 am

Re: Retiree Portfolio Model

Post by kleiner »

Apologies if this has been asked before: I am retired but my wife will continue to work for a few more years. Where do I include her salary in this spreadsheet? My guess is that it will be in section 4 under "Other income 2" ?

The only thing is that I notice that both "Other income 1" and "Other income 2" are set to use my age rather than my wife's age.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

kleiner wrote: Tue May 03, 2022 1:29 pm Apologies if this has been asked before: I am retired but my wife will continue to work for a few more years. Where do I include her salary in this spreadsheet? My guess is that it will be in section 4 under "Other income 2" ?

The only thing is that I notice that both "Other income 1" and "Other income 2" are set to use my age rather than my wife's age.
Use those. The Your age is used to standardize some input items so just convert your wife's salary start and stop ages to yours for specifying the years.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
RetireSoon90
Posts: 62
Joined: Mon Jan 01, 2018 7:13 am

Re: Retiree Portfolio Model

Post by RetireSoon90 »

I'm struggling with using Retiree Portfolio Model on Libre. We are retired one year and want to use it for various Roth conversion scenarios. I don't know if my struggles are because I am using Libre or because I am filling it out incorrectly. I am on the fence on purchasing Excel so I can use RPM to see if that helps. Does the RPM work will using Libre?

When I downloaded the latest version, first link in this topic, it came with the values pre-populated. Is that correct? It was confusing because I did not know what I filled out and what was already filled out.

TIA!
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

RetireSoon90 wrote: Sun May 29, 2022 6:52 am I'm struggling with using Retiree Portfolio Model on Libre. We are retired one year and want to use it for various Roth conversion scenarios. I don't know if my struggles are because I am using Libre or because I am filling it out incorrectly. I am on the fence on purchasing Excel so I can use RPM to see if that helps. Does the RPM work will using Libre?

When I downloaded the latest version, first link in this topic, it came with the values pre-populated. Is that correct? It was confusing because I did not know what I filled out and what was already filled out.

TIA!
RPM works well with Libre Calc (tested using 7.2.4.1). The model comes with example data which can all be cleared off using the "clear entries" macro button at the top of the Setup page. Libre supports "SOME" macros, which some none-Excel programs don't. Hope the model helps you with the Roth issue - the original purpose of RPM, now coming up on 10 years old I see.

Update: added "some" after below post added. Thanks.
Last edited by BigFoot48 on Sun May 29, 2022 8:55 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: Retiree Portfolio Model

Post by mkc »

BigFoot48 wrote: Sun May 29, 2022 8:07 am
RetireSoon90 wrote: Sun May 29, 2022 6:52 am I'm struggling with using Retiree Portfolio Model on Libre. We are retired one year and want to use it for various Roth conversion scenarios. I don't know if my struggles are because I am using Libre or because I am filling it out incorrectly. I am on the fence on purchasing Excel so I can use RPM to see if that helps. Does the RPM work will using Libre?
RPM works well with Libre Calc (tested using 7.2.4.1). The model comes with example data which can all be cleared off using the "clear entries" macro button at the top of the Setup page. Libre supports macros, which some none-Excel programs don't.
I've found a few of the macros aren't supported with LibreCalc (7.3), some of the show/hide ones in results related to Roth Conversions and Activity Details (I get Basic Runtime Errors), and inability to copy-paste from previous versions. Nothing show-stopping, but I am also considering purchasing Excel.
steve51
Posts: 10
Joined: Thu May 26, 2022 6:36 pm

Re: Retiree Portfolio Model v22.5 with LibreOffice v 6.4.7.2

Post by steve51 »

Hello BigFoot,
Just started using the spreadsheet. Initially in LibreOffice ods mode.
Started filling Setup page but soon noticed sheet was not updating calculated cells with new information ... still digging into sheet operation.

Am filing single and well into 'setup' process discovered macro for single filers - clicked on it and BASIC runtime error 448 came up .... see below

Image

Thanks
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model v22.5 with LibreOffice v 6.4.7.2

Post by BigFoot48 »

steve51 wrote: Wed Jun 01, 2022 1:24 pm Hello BigFoot,
Just started using the spreadsheet. Initially in LibreOffice ods mode.
Started filling Setup page but soon noticed sheet was not updating calculated cells with new information ... still digging into sheet operation.

Am filing single and well into 'setup' process discovered macro for single filers - clicked on it and BASIC runtime error 448 came up .... see below

Image

Thanks
Check Data > Calculate and make sure Auto-Recalculate is on. Macros may not all be supported in non-Excel programs. The new single filer macro just sets the various single vs married settings to single, saving some time but can be done by the user as one goes down the Setup page. I tested the macro in 7.4.2.1 and it worked.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
steve51
Posts: 10
Joined: Thu May 26, 2022 6:36 pm

Re: Retiree Portfolio Model

Post by steve51 »

Thanks BigFoot,
Just started with fresh spreadsheet and turned on auto-calculate setting - that's working.
Clicked on filing single setting, still got same error message but it appears to have removed spousal inputs.
Cheers
steve51
Posts: 10
Joined: Thu May 26, 2022 6:36 pm

Re: Retiree Portfolio Model

Post by steve51 »

Hello BigFoot - working thru spreadsheet -
Sec 8 Income Taxes - On Federal tax calculator green cells - comparing to my tax return... lines 9 and 11 are the same with no other income (ln 8).
EDIT not (ln 8) but (ln 10).
Adjusted Gross Income is Total Income less deductions Line 11 - Line 14.

In your spreadsht line 15 is "X278-X279-X282", shouldn't X278 be the same as X276?

thanks

EDIT - fyi BigFoot, I'm using 1040-SR tax form if that makes a difference. Your calculated tax does not match mine and I see in your tax table sheet the taxable income looks like it has the same problem.
thanks.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

RPM version 22.5a is now available - Jun 22 updated Jun 23
Download link: https://www.dropbox.com/s/x4m5gq8bwu5pm ... .xlsm?dl=0

This minor update fixed the optional Income Tax Calculator for a reference error (that may have made tax calculations wrong) and updated form line numbers/names to 2021 1040 tax form line numbers. Corrected 2021 married Cap gains bracket. Thanks to BH steve51 and Dlynofees for finding the bad line references.

Only those wanting to estimate 2022 taxes using the calculator need this update. Please note this is an approximate calculator as not all tax provisions are provided for. I note it is different from my TurboTax return by $29 (after DSBH fix) for my 2021 taxes due to rounding being set at $100.

Jun 23: BH DSBH noted the 2021 married brackets were 2020 and needed updating, and another calculator reference was to the wrong cell. All fixed. Many thanks
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
jim9t
Posts: 1
Joined: Fri Jul 08, 2022 8:15 am

retiree model portfolio problems

Post by jim9t »

[ Merged into ongoing discussion -- moderator oldcomputerguy]

I am working with the retiree model portfolio v22.5 to help with Roth conversions.
On the setup tab in the federal tax calculator is what I think is an error. Cell X279 (standard or itemized deduction) is subtracted twice which underreports the taxable income in cell X284.
On the tax tables tab where the Net Investment income tax in calculated cell G159 to cell AS159 it appears the $250,000 dollar limit is indexed to inflation. This tax is not indexed to inflation it remains a $250,000 for married filing status. In the single filing status section there is a similar issue except the limit is $200,000.
Do I have this right?
mkc
Moderator
Posts: 3292
Joined: Wed Apr 17, 2013 2:59 pm

Re: retiree model portfolio problems

Post by mkc »

jim9t wrote: Fri Jul 08, 2022 8:42 am [ Merged into ongoing discussion -- moderator oldcomputerguy]

I am working with the retiree model portfolio v22.5 to help with Roth conversions.
On the setup tab in the federal tax calculator is what I think is an error. Cell X279 (standard or itemized deduction) is subtracted twice which underreports the taxable income in cell X284.
Did you see the post just above yours (now that yours was merged into the existing topic)? That specifically addresses the quoted question (it was fixed in 22.5a).
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: retiree model portfolio problems

Post by BigFoot48 »

jim9t wrote: Fri Jul 08, 2022 8:42 am [ Merged into ongoing discussion -- moderator oldcomputerguy]

I am working with the retiree model portfolio v22.5 to help with Roth conversions.
On the setup tab in the federal tax calculator is what I think is an error. Cell X279 (standard or itemized deduction) is subtracted twice which underreports the taxable income in cell X284.
On the tax tables tab where the Net Investment income tax in calculated cell G159 to cell AS159 it appears the $250,000 dollar limit is indexed to inflation. This tax is not indexed to inflation it remains a $250,000 for married filing status. In the single filing status section there is a similar issue except the limit is $200,000.
Do I have this right?
It does appear that the NIIT brackets have not changed in many years and the escalation applied to the brackets may understate NIIT for future years. I will fix that in the next update. (Of course having no escalation may understate NIIT in 10 years or more, but that the nature of these models.) Thanks for the report.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
bluesky50
Posts: 73
Joined: Tue Jan 09, 2018 10:05 pm

Re: Retiree Portfolio Model

Post by bluesky50 »

I would like to download the spreadsheet and it is asking me to sign up.

Do I need to sign up in order to download?

Thanks!
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Shouldn't be a requirement to sign up for downloading. Post if you are unable to download. Thanks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
bluesky50
Posts: 73
Joined: Tue Jan 09, 2018 10:05 pm

Re: Retiree Portfolio Model

Post by bluesky50 »

BigFoot48 wrote: Tue Aug 09, 2022 12:45 pm Shouldn't be a requirement to sign up for downloading. Post if you are unable to download. Thanks.
Thank you BigFoot48!

I got pass that signup screen and now I am able to download.

I am planning to study the spreadsheet and see if I can use it to model our retirement portfolio as I am about to retire in a few months.
Post Reply