Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
goGators
Posts: 40
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators » Thu Jan 11, 2018 1:06 pm

BigFoot48 wrote:
Thu Jan 11, 2018 10:58 am
I tested it in my copy of LibreOffice 5.3.7.2, win10x64 and the macros work (a few, like Clear, may not work due to the different code that LibreOffice uses). Check the setting at Tools > Options > Security > Macros Security. Mine is set to "Low".
I downloaded the file again and this time most of the macros worked. However, I noticed that half of the arrow labels in the Cash Flow diagram were upside down. I just wonder if the new file is still corrupt. Could you or someone please post the checksum of your file?
Mine is f2fb4838aa27166c8e21b4480835115b.
Thank you.

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Jan 11, 2018 1:12 pm

goGators wrote:
Thu Jan 11, 2018 1:06 pm
I downloaded the file again and this time most of the macros worked. However, I noticed that half of the arrow labels in the Cash Flow diagram were upside down. I just wonder if the new file is still corrupt. Could you or someone please post the checksum of your file?
Mine is f2fb4838aa27166c8e21b4480835115b.
Thank you.
I noticed that too, and have no explanation. They're all right-side up in Excel. I'll take a look at the data but think all the arrows are formatted identically.

They're all formatted the same so I have no idea why they're upside down. However I did notice a missing arrow for the Special Events total - will fix that - done!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

goGators
Posts: 40
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators » Thu Jan 11, 2018 6:28 pm

Hi BigFoot,
When I change the portfolio values in section 2, I don't see them get updated in the cash flow diagram.
I have your latest version with the Special Event fix.
As mentioned in previous post, I'm using LibreOffice 5.3.7.2 and win10x64.
Thank you.

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Jan 11, 2018 7:59 pm

goGators wrote:
Thu Jan 11, 2018 6:28 pm
When I change the portfolio values in section 2, I don't see them get updated in the cash flow diagram.
I have your latest version with the Special Event fix.
The cash flow diagram is working in Excel but I agree it is not in LibreOffice Calc, which does not support Excel shapes with embedded formulas. I will add that limitation to the Libre comment in the spreadsheet link area. Sorry about that. Thanks for reporting it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sandramjet
Posts: 186
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet » Fri Jan 12, 2018 2:33 pm

I see that the spreadsheet indicates how many years you are above the Medicare income limits (IRMAA). However, it doesn't look like it does anything with that information (unless, as is quite possible, I missed it). Is it possible that there could be an option to automatically add the additional cost to the expenses for that year? That way, the impact of that additional costs would be assessed automatically along with choices like Roth conversions amount, etc.

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Jan 13, 2018 12:59 am

sandramjet wrote:
Fri Jan 12, 2018 2:33 pm
I see that the spreadsheet indicates how many years you are above the Medicare income limits (IRMAA). However, it doesn't look like it does anything with that information (unless, as is quite possible, I missed it). Is it possible that there could be an option to automatically add the additional cost to the expenses for that year? That way, the impact of that additional costs would be assessed automatically along with choices like Roth conversions amount, etc.
The topic of increased Medicare costs due to higher income level is an occasional topic on the forum, so I thought I would provide an alert in the model to indicate if a user's future income levels may trigger a higher premium. Less than 5% of Medicare participants are currently impacted by this. The possible minor additional expense just needs to be considered when selecting the overall annual expense amount.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
One Ping
Posts: 296
Joined: Thu Sep 24, 2015 4:53 pm

Re: Retiree Portfolio Model

Post by One Ping » Sat Jan 13, 2018 3:16 pm

I’m trying to set up a RPM scenario (V 18.0) and am having difficulty getting it to model a specific SS benefit scenario.

Background:
  • Spouse A
    Spouse A Age: 66
    Spouse A FRA: 66
    Spouse A PIA: $32,000
    Spouse A SS Start Age: 66
    (Spouse A filed for spousal benefit at age 66, in December 2017.)
  • Spouse B
    Spouse B Age: 72
    Spouse B FRA: 66
    Spouse B PIA: $6,700
    Spouse B SS Start Age: 66
    (Spouse B filed for their own benefit at age 66, ~6 years ago)
SS Benefit Scenario:
  • Spouse A continues taking spousal benefit until age 70, when they file for their own benefit, accruing 48 mos of Deferred Retirement Credits (DRCs).
  • Spouse B continues taking their own benefit until becoming eligible for spousal benefit at age 76 (Spouse A age 70).
Expected SS benefits (Assuming no inflation COLA for now to make it simpler to see what’s going on):
  • Spouse A:
    Age 66-69: $3,500/yr (Spouse B SS benefit currently ~$7,000/yr)
    Age 70+: $42,240/yr
  • Spouse B:
    Age 72-75: $7,000/yr
    Age 76+: $16,000/yr
__________________________________________________________________________________________

I cannot get RPM to model this scenario correctly. What do I need to do to set up the model to generate this SS benefit scenario?

Thanks for any help!

One Ping
"Re-verify our range to target ... one ping only."

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Jan 13, 2018 5:06 pm

One Ping wrote:
Sat Jan 13, 2018 3:16 pm
I cannot get RPM to model this scenario correctly. What do I need to do to set up the model to generate this SS benefit scenario?
I suspected there was a SS scenario that was un-modelable by RPM and when I saw your post I thought, "See! You were right!", but alas, I think I was able to do it thanks to the Additional Benefits options I added when the file/suspend went away.

Try these settings (ignore the error message):

Image
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
One Ping
Posts: 296
Joined: Thu Sep 24, 2015 4:53 pm

Re: Retiree Portfolio Model

Post by One Ping » Sat Jan 13, 2018 5:34 pm

BigFoot48 wrote:
Sat Jan 13, 2018 5:06 pm
One Ping wrote:
Sat Jan 13, 2018 3:16 pm
I cannot get RPM to model this scenario correctly. What do I need to do to set up the model to generate this SS benefit scenario?
I suspected there was a SS scenario that was un-modelable by RPM and when I saw your post I thought, "See! You were right!", but alas, I think I was able to do it thanks to the Additional Benefits options I added when the file/suspend went away.

Try these settings (ignore the error message):

Image
YES!! I too thought it might not be possible, but I'm glad (and grateful) to be proved wrong! :sharebeer

Thanks, BigFoot.

One Ping
"Re-verify our range to target ... one ping only."

theta
Posts: 85
Joined: Fri Feb 21, 2014 3:50 pm

Some suggestions

Post by theta » Sun Jan 14, 2018 12:12 pm

A big thanks to BigFoot for RPM! A tremendous tool that provided great insight to me as I look at retirement down the road.

The following are suggestions and in no way should be regarded as criticisms. At some point in time you may want to take RPM in this direction, if you choose:

1. IRMAA thresholds - I believe (correct me if I'm wrong) that RPM only counts the first IRMAA threshold. It would be useful to also note where MAGI exceeds the 2nd, 3rd, and 4th thresholds.

2. IRMAA increased fees - I realize this is a complicating factor, but it would be handy to gain a better picture of how IRMAA fees are affected overall outcomes. The IRMAA fees are equivalent to about 4% of income taxes and can be much worse if you're just over an IRMAA threshold. I suspect that IRMAA fees will become more and more of a focus for folks beginning their retirement planning.

I will concur that some additional descriptions/help documentation would be helpful - one particular item is consistent use of terminology - the base is always referred to as the base, but the full (I think) is referred to by a 2 or 3 different titles.

One last observation - I was using Google Docs/Sheets and I noticed only one shortcoming - the colored blocks that were used in some diagrams were not labelled. The diagram itself was fine, but without labelling on the associated colored blocks, I couldn't tell what the diagram was showing. I can give more specific info if you need it.

Thanks again, BigFoot!

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Jan 14, 2018 12:50 pm

Thanks for those suggestions theta! I was just reading this morning The Nerd's Eye View excellent recap of IRMAA history and the 2018 factors, and plan to update RPM to improve the calculations and information of this potential additional cost to retirees (estimated it impacts about 5%). I am reluctant to include any calculation of this additional cost in the model results but will see what the update reveals. https://www.kitces.com/blog/irmaa-medic ... hresholds/

I will admit in my move to focus on the primary "Full" portfolio this year and move the basic "Base" portfolio (doesn't include Roth conversions or alternative SS benefits), used for instant comparisons, to the sidelines, I have not been able to arrive a good alternative name for the Full portfolio and thus used various trial names in the 2018 model, possibly leading to some confusion. Suggestions are welcome!

I will check the model in Google Sheets and see if the labeling problem can be fixed. UPDATE: I did and see that macro graphic labels are not displayed, and some of the graphics are distorted. I'm afraid that just a limitation in Sheets. Otherwise, it appears to do the calculations correctly and is a good, but not great, substitute for Excel.

Thanks again!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

Dasnyc
Posts: 43
Joined: Wed Oct 16, 2013 6:51 pm

Re: Retiree Portfolio Model

Post by Dasnyc » Mon Jan 15, 2018 12:47 pm

First, a very sincere ‘Thank you’ for your efforts to not only create and thoroughly test this very informative work, but also for the timely fixes and enhancements.

I am having one small issue that is impacting my rather significant (~10%) state tax estimates. In my state, contributions to 529 accounts are deductible from state income, although not deductible from federal income. I cannot find a way to account for this in the RPM. Is there a way to do this? If not, would you consider adding a way to modify state taxable income to allow for these types of items?

Thanks

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Jan 15, 2018 1:16 pm

Dasnyc wrote:
Mon Jan 15, 2018 12:47 pm
I am having one small issue that is impacting my rather significant (~10%) state tax estimates. In my state, contributions to 529 accounts are deductible from state income, although not deductible from federal income. I cannot find a way to account for this in the RPM. Is there a way to do this? If not, would you consider adding a way to modify state taxable income to allow for these types of items?
Thanks, glad you like it.

I don't see a way to accommodate that adjustment to state taxable income in the current version, but I will see if I can add a generic adjustment in a future update. In the meantime, I would try reducing the state standard deduction or personal exemption by an amount that would approximately increase taxable income by the amount needed. Both of these entries can add to state taxable income by entering a negative number. The "second state" option could then be used to reverse any temporary adjustment.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

Boggedown
Posts: 4
Joined: Sun Jan 21, 2018 11:51 am

Re: Retiree Portfolio Model

Post by Boggedown » Sun Jan 21, 2018 12:22 pm

First, thanks for the wonderful modeler which looks to be very, very helpful to me going forward. Especially and primarily for it's Roth conversion feature - this seems to be unavailable elsewhere except possibly in pro tools. Even there (e.g. Money Tree) there isn't much evidence of capabilities like this.

I do have a few minor comments. I'm running Excel 2010 on a Windows 10 machine:

• On the Summary page, some columns go to ###### at $10K and above. I see this in Basic | 'Expenditures & Receipts' totals; Results vs Basic | Earnings & Income 'Taxable', and Account Balances End of Year 'Taxable'.

• In the Setup pg I suggest having a Federal Income Tax chart by year, right above State chart which is already there.

• In Setup, the Taxes By Bracket for Roth (dollar values) seems to be missing the green bar header seen in the Taxes By Bracket for Roth (percent value) above.

• In Setup, the cash flow title bars should say "Portfolio Cash Flow ($K)" since the values are in thousands.


AND a question:

I'm approaching retirement. I'm assuming a 7% return since I stay mostly in stocks. RPM shows a major lifetime benefit in an immediate Roth conversion of my Traditional IRA. That takes a big income tax hit especially since I'm still employed, and will take two years. Delaying conversion several years until retirement shows worse results. Stretching it out to ~12 years to stay in a 32% bracket also shows worse results, and it's not even feasible to do it within the 24% bracket.

My question is: does this surprising RPM prediction (big immediate conversion) seem reasonable / comparable to what others have found and done?

I will hire a planning / tax pro to look at the same thing, but if whatever tools they've got disagree with RPM's recommendation, I'll have to make a judgement call. RPM is open and looks very reliable. The actual experience of others would be highly interesting.

Thanks again for this great tool (and some ten years of refining it). If there's anything even comparable available to the average person, I haven't found it.

User avatar
FiveK
Posts: 3601
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK » Sun Jan 21, 2018 1:29 pm

Boggedown wrote:
Sun Jan 21, 2018 12:22 pm
My question is: does this surprising RPM prediction (big immediate conversion) seem reasonable / comparable to what others have found and done?
It is not inconsistent with what www.i-orp.com often finds.

You may want to compare taxes calculated by RPM (and/or or i-orp) in those high conversion years with more comprehensive tax calculations. If those agree "closely enough" then you can feel better about the overall results. If there is significant disagreement, then you may suspect the RPM/i-orp results.

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Jan 21, 2018 2:04 pm

Boggedown wrote:
Sun Jan 21, 2018 12:22 pm
I do have a few minor comments.

AND a question:
boggedown - that you for the formatting ideas and suggestions, and I'm glad you find the model useful. I will correct/address those formatting items in the next minor update.

The reason I left the green heading off in the tax table is that it is the one most users, (who have no interest or only past interest in Roth conversions) will use, so leaving it off was just a way to simplify the formatting, but I see the inconsistency and will fix the heading of the table or the other!

I don't recall any posts detailing actual real-life Roth conversion results, but believe the model does the calculations correctly for the factors used. I use example data that maximizes use of the now 12% bracket and have not tested maximizing higher brackets for the same conversion amount and how that might compare, so I don't have an opinion on what you are seeing.

Note: RPM updated today for formatting, labeling and graph issues pointed out above.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

Boggedown
Posts: 4
Joined: Sun Jan 21, 2018 11:51 am

Re: Retiree Portfolio Model

Post by Boggedown » Mon Jan 22, 2018 5:47 pm

Thank you, BigFoot48 and FiveK.

I appreciate the pointer towards i-ORP which I hadn't yet found. That's quite a piece of work too. I've begun to use it and, of course, compare its results with RPM. I have looked through the several threads here on I-ORP.

In my case, ORP finds that a Roth conversion over 7 years will maximize income, whereas RPM finds that a two-year slam conversion (paying the high taxes briefly) will maximize assets. I understand RPM as a simulator / modeler, but ORP is an optimizer working to a different goal, and it will take me a while to reconcile the two in my head.

==> The Bogleheads Wiki page on RPM says it has a sheet on "ORP Comparison". But that's no longer in RPM; no doubt for good reasons, but I wonder what it used to show...

Thx

User avatar
BigFoot48
Posts: 2520
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Jan 22, 2018 6:27 pm

Boggedown wrote:
Mon Jan 22, 2018 5:47 pm
==> The Bogleheads Wiki page on RPM says it has a sheet on "ORP Comparison". But that's no longer in RPM; no doubt for good reasons, but I wonder what it used to show...

Thx
It's still there as the far right tab, but hidden from normal view. You can view by clicking on the macro "show ORP" button near the top right on the Setup page. The ORP page has a comparison of the current RPM results to ORP, but the ORP results must be copied in. It's a bit difficult and, if ORP has made changes since the last reconciliation I did, it may be impossible to reconcile the two models without some reformatting.

All that said, the two models produce similar results using simple factors. (The ORP page shows the last reconciliation.) ORP calculates earnings and taxes differently from RPM, and the automated entries it creates are sometimes difficult or impossible to replicate in RPM, but the bottom line is that I believe ORP is doing its calculations reasonably accurately. :wink: I think RPM is better in many regards, but ORP is certainly easier to use for a quick result!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

munemaker
Posts: 2394
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Tue Jan 23, 2018 7:58 am

Boggedown wrote:
Sun Jan 21, 2018 12:22 pm

My question is: does this surprising RPM prediction (big immediate conversion) seem reasonable / comparable to what others have found and done?

Regarding a big, immediate conversion, consider the ObamaCare taxes that I don't believe are included in RPM (surprisingly these taxes were not repealed):
- The first tax is an incremental 0.9% Medicare tax on wages above $250,000 (married filing joint) and $200,000 (single). A married couple with $500,000 in wages in 2013 will owe an incremental $2,250 in taxes or simply $250,000 x.9%. This new tax is applied to gross wages and is before deductions for items like 401(k) contributions and healthcare premiums. It should be noted the tax also applies to self employment income earned by sole proprietors and partnerships as well.
- The second tax related to Obamacare is the "unearned income" tax. Married couples filing jointly with modified adjusted gross income (MAGI) above $250,000 are subject to this new tax. For single individuals this tax kicks in for MAGI above $200,000. So how does this second tax work? A married couple will pay the lower of 3.8% of:
1) excess MAGI above $250k or 2) unearned income.
For example, a married couple with a MAGI of $280,000 and unearned income of $15,000 would owe $570. The calculation works as follows:
Lower of:

Excess MAGI $30,000 x 3.8% = $1,140

Unearned income $15,000 x 3.8% = $250

reference:https://www.thestreet.com/story/1170996 ... rners.html

Also, for those on SS, consider IRMAA which is higher Medicare premiums for those with MAGI over $170,000.

Boggedown
Posts: 4
Joined: Sun Jan 21, 2018 11:51 am

Re: Retiree Portfolio Model

Post by Boggedown » Tue Jan 23, 2018 2:35 pm

Thanks, Bigfoot48, and thanks for chiming in with these tax cautions, munemaker. I didn't have a clue about them. Because RPM is an open spreadsheet, I can ad-hoc it a little to work in some consideration of what you've pointed out.

Post Reply