Retiree Portfolio Model

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

Re: Retiree Portfolio Model

Post by goGators »

BigFoot48 wrote: Thu Jan 11, 2018 9: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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Thu Jan 11, 2018 12: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; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Thu Jan 11, 2018 5: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; 18-time loser
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sandramjet wrote: Fri Jan 12, 2018 1: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; 18-time loser
User avatar
One Ping
Posts: 1087
Joined: Thu Sep 24, 2015 4:53 pm

Re: Retiree Portfolio Model

Post by One Ping »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

One Ping wrote: Sat Jan 13, 2018 2:16 pmI 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; 18-time loser
User avatar
One Ping
Posts: 1087
Joined: Thu Sep 24, 2015 4:53 pm

Re: Retiree Portfolio Model

Post by One Ping »

BigFoot48 wrote: Sat Jan 13, 2018 4:06 pm
One Ping wrote: Sat Jan 13, 2018 2:16 pmI 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: 152
Joined: Fri Feb 21, 2014 2:50 pm

Some suggestions

Post by theta »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

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; 18-time loser
Dasnyc
Posts: 100
Joined: Wed Oct 16, 2013 6:51 pm

Re: Retiree Portfolio Model

Post by Dasnyc »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Dasnyc wrote: Mon Jan 15, 2018 11:47 amI 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; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

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: 15742
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

Boggedown wrote: Sun Jan 21, 2018 11:22 am 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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boggedown wrote: Sun Jan 21, 2018 11:22 am 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; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

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
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boggedown wrote: Mon Jan 22, 2018 4: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; 18-time loser
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

Boggedown wrote: Sun Jan 21, 2018 11:22 am
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 Medicare, consider IRMAA which is higher Medicare premiums for those with MAGI over $170,000.
Last edited by munemaker on Tue Jan 23, 2018 2:16 pm, edited 1 time in total.
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

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.
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Hi Bigfoot48,

I'm continuing to use RPM and am so thankful to have it. No evidence yet that its Roth conversion capabilities exist even in the pro tools like eMoney, MoneyGuide Pro etc.

There is an important error: In Setup Rows 286+, "TAXES BY BRACKET" in dollars Totals are wrong because they are not including the 37% chunks.
The dollars values in Details Row 186 do include this and are very different. The Setup "Taxes by Bracket" in percent are from Details and are OK.

Some notes and further requests, not in any order:

• Add 37% tax bracket amounts to all four Bracket charts on Setup.

• Please explain more the Cell 238 note. The words are clear but why would the IRA-1 balance go to zero when I start converting more than about half of it?

• In Setup cells I237,238 it says "Taxable Balance"; I suggest I236,237,238 to say "Taxable Acct Balance" to avoid noob wondering if this means taxable money somehow got into the IRA.

• Nit: Can't get rid of SPIA "using" msg; maybe add a "none" choice to portfolio choice. Or an overall disable control; I'd like to know it's not being figured in.

• On Summary, open more cells to the right of Col M; I have lots of Roth conversion sequences to try.

• A big Roth conversion will drive some cells into #####. A few are: Summary, Q48 and M96 and Q144 and BU41-47.

• Setup D220 & G220 round differently than the other Table values.

• In "Portfolio Cash Flow Diagram", the "Conversions" arrow to Roth doesn't show any value; prob was meant to.

==============================
The "OnTrajectory" tool author says that retirement planning is a gateway drug - once you start it's addicting. However, from personal experience I must warn you that genealogy is even worse. The more you learn the more you don't know. SO you will benefit by dropping that and getting back to work on RPM, and adding features I personally need, which I'll be glad to list and won't take you more than another 5000 hours...

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

Boogedown - you are rapidly becoming my favorite Beta Tester for all of these interesting finds! Keep it up!
Boggedown wrote: Sun Jan 28, 2018 8:15 pm There is an important error: In Setup Rows 286+, "TAXES BY BRACKET" in dollars Totals are wrong because they are not including the 37% chunks.
I want apologize to those in the top bracket for ignoring their need for information in the model. Will add the 37% data.

• Add 37% tax bracket amounts to all four Bracket charts on Setup.
Ditto

• Please explain more the Cell 238 note. The words are clear but why would the IRA-1 balance go to zero when I start converting more than about half of it?
Other withdrawals from the IRA may be occurring, reducing its balance faster than the conversions may indicate.

• In Setup cells I237,238 it says "Taxable Balance"; I suggest I236,237,238 to say "Taxable Acct Balance" to avoid noob wondering if this means taxable money somehow got into the IRA.
Sounds reasonable.

• Nit: Can't get rid of SPIA "using" msg; maybe add a "none" choice to portfolio choice. Or an overall disable control; I'd like to know it's not being figured in.
I'll fix this "false-positive" message by tying it to the SPIA amount, which if $0, will make it disappear and avoiding attracting attention.

• On Summary, open more cells to the right of Col M; I have lots of Roth conversion sequences to try.
Sorry, no can do. I suggest using the nearby "Notes" area or a separate spreadsheet to compare results.

• A big Roth conversion will drive some cells into #####. A few are: Summary, Q48 and M96 and Q144 and BU41-47.
Will expand these columns a bit.

• Setup D220 & G220 round differently than the other Table values.
Will fix. The highest bracket use to have a tenth, now simplified.

• In "Portfolio Cash Flow Diagram", the "Conversions" arrow to Roth doesn't show any value; prob was meant to.
If you turn Roth conversions "on" it should show a value, but if your conversion is $1M+ there may not be enough room in the arrow. I will see if I can adjust it.

... you will benefit by dropping that and getting back to work on RPM, and adding features I personally need, which I'll be glad to list and won't take you more than another 5000 hours...
I will try to fit RPM into my other numerous retirement activities to fix/add all the things you find!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Yes, if you assume a 6-figure conversion and a 7-figure account, you'll probably find a dozen or so columns overall that should be expanded a little.

Also, I did figure out (duh) that I could simply add a sheet for my temporary stuff.

Thanks again.
jimmieg
Posts: 66
Joined: Sun Feb 26, 2012 7:25 am

Re: Retiree Portfolio Model

Post by jimmieg »

Hey, Bigfoot48-

Thanks for all your work!
I'm almost fully retired at 70 and beginning RMD's and
new to the RPM and just getting set up.

In addition to his/hers IRA 1 and 2, I have a 403-b that will soon require an RMD. My problem is it is a special circumstance and withdrawaks are excluded from taxation.
I've tried a workaround, reporting it as a Roth account, but that doesnt break out the RMD. Thinking there might be a better way.

Suggestion?

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 »

jimmieg wrote: Mon Jan 29, 2018 6:00 pm In addition to his/hers IRA 1 and 2, I have a 403-b that will soon require an RMD. My problem is it is a special circumstance and withdrawaks are excluded from taxation.
I've tried a workaround, reporting it as a Roth account, but that doesnt break out the RMD. Thinking there might be a better way.
That's a new one (to me)! The "User input income, credit and adjustments" line on the Details page at line #167 allows yearly adjustments to the AGI. Entering a negative amount in each year equal to the yearly RMD will zero it out in taxable income. (Did a quick test and it seems to work!)
Last edited by BigFoot48 on Tue Jan 30, 2018 6:43 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
jimmieg
Posts: 66
Joined: Sun Feb 26, 2012 7:25 am

Re: Retiree Portfolio Model

Post by jimmieg »

Thanks!
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Hi Bigfoot48,

I wonder if the RPM "Table II" RMD calculations are correct...

[[Question was resolved.. see following posts. Deleted most of this comment to reduce thread clutter. ]]
Last edited by Boggedown on Wed Jan 31, 2018 10:04 am, 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 »

Boggedown wrote: Tue Jan 30, 2018 6:11 pm Hi Bigfoot48,

I wonder if the RPM "Table II" RMD calculations are correct. See the attached pix.
You have to input the life expectancy numbers for your and your spouse's age from IRS Table II. The Sample data is for an IRA owner age 60 with a spouse age 50. Because of all the possible combinations the user has to edit the table with their particular data. (See the Override Table II cell note.)

Is this why your results don't look right?
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Yes! I had misunderstood the steps needed to use Override Table II. I just set 'o' and left it at that... Now I understand. To help others avoid this mistake, I suggest beefing up the notification perhaps as I show here. I've also edited the instructions to be more specific since, until you've done it once, the terms can be confusing. I'll paste that text in here below if you want to use it.

Thanks again

Image

===========================
The IRS uses one of three different life expectancy tables for determining the current year Required Minimum Distribution when the IRA owner reaches age 70.5. Table I is for single persons. Table II is for married couples where the spouse is more than 10 years younger than the owner. Table III is for other married couples.

Table I (Single Life Expectancy):
Use Table I if you are an individual and a designated beneficiary, but not the owner's surviving spouse and sole designated beneficiary.
or
The beneficiary is not an individual and the owner died on or after the required beginning date, defined earlier.

Table II (Joint Life and Last Survivor Expectancy):
Use Table II if you are the IRA owner and your spouse is both your sole designated beneficiary and more than 10 years younger than you.

TO USE THIS: First, choose the Override (o) setting in the selector above. Then go to IRS Publication 590-B and find its Table II in Appendix B. There, in the left column reading down, locate your age at which RMDs will begin. For a Traditional IRA this is "age 70.5" which means age 70 if you were born Jan-Jun or age 71 if you were born Jul-Dec. Reading across the table, find the spouse age that will pertain at your starting age. Enter that in the first cell of the "Spouse Age" column in the Override Table II to the right here; the cells below that will auto-fill with the following ages. Then copy the Life Expectancy values that begin at that point in the IRS table (going down) into the "Life Exp" column to the right here. Overwrite the sample data provided, which is for an IRA owner age 60 with a spouse age 50.

Table III (Uniform Lifetime):
Use Table III if you are the IRA owner and your spouse is not both the sole designated beneficiary of your IRA and more than 10 years younger than you.

Table III is for IRAs owned by an unmarried person, or a married person with a spouse no more than 10 years younger, or a married person with a spouse who is not the sole benefactor of the IRA.

TO USE THIS: Choose the Default (d) setting in the selector above.
Last edited by Boggedown on Wed Jan 31, 2018 9:48 pm, 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 »

Excellent pop-up improvement which I will use, and I will use a version of your instruction addition too. Thanks for the help and insights. (You also get added to the list of BH members who have helped improve the model!)
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Great. On the same topic - In Setup maybe move the Table II alert from Cell F35 to C35 or B35, so that the full text will appear. The "action needed" part has to be noticed for folks to get started correctly with this option.

Thanks
Boggedown
Posts: 23
Joined: Sun Jan 21, 2018 10:51 am

Re: Retiree Portfolio Model

Post by Boggedown »

Also - the red Alert text(s) on the Setup page (referred to in my previous post) should change to reflect that Table II can only be used if the spouse is >10 years younger. Ten years younger isn't enough. I also fixed this in my 'Help' text posted above. Verified in IRS 590-B.

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 »

No, thank you. I will make sure all the instructions and alert messages are clear on this +10 younger spouse issue. I note that the alert message currently only appears if the difference in the ages entered is 11 or greater. I'm thinking of changing this to 10 to make sure the alert is seen by that couple, even though it will not be applicable to some with a rounded-off 10 year difference which is actually 9 years, 11 months.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Digerati
Posts: 1
Joined: Tue Feb 06, 2018 5:45 pm

Re: Retiree Portfolio Model

Post by Digerati »

Bigfoot48: This is a fabulous tool. I"ve been heads down with it for days. I have a question regarding the amount being considered for Roth Conversion. What influences the amount in C234 in the setup page? Wondering how this amount is calculated?

Thank you so much for providing such a valuable service. I intend to fully utilize all the information I can learn from the reports generated to make my retirement as comfortable as possible.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Digerati wrote: Tue Feb 06, 2018 6:06 pm Bigfoot48: This is a fabulous tool. I"ve been heads down with it for days. I have a question regarding the amount being considered for Roth Conversion. What influences the amount in C234 in the setup page? Wondering how this amount is calculated?
Thanks Digerati. And I highly recommend a comfortable retirement!

The poorly-documented C234 is the total of adjusted Roth conversion withdrawals. Input yearly withdrawal amounts may exceed the amount in the Roth conversion available for withdrawal. The model monitors this and reduces the withdrawals as necessary to keep the account from having a negative balance. C234, when compared to C235, which is "entered Roth withdrawals", will indicate if a reduction occurred. I will add a cell-note explaining this and a alert message should this occur.
Last edited by BigFoot48 on Wed Feb 07, 2018 7:49 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
rennale
Posts: 110
Joined: Sun Apr 23, 2017 6:50 am

Re: Retiree Portfolio Model

Post by rennale »

A quick question - before I waste a hundred or so bucks can anybody confirm that Excel 2016 for Mac will handle RPM fully (Macros etc)? My Mac is running High Sierra (10.13.3).

https://www.microsoft.com/en-us/store/d ... q7ttc0k5f2

I've been using RPM with LibreOffice (5.4.4.2) for a while - where it works reasonably well (although the Macros are hit and miss, usually miss).

However, I happened to use RPM on a Windows box with Excel yesterday and got to see what I've been missing. I realized that it's not really worth messing around with LibreOffice (which I've been using happily for years) with a spreadsheet as complex as RPM.

I know that Excel in Office 365 does't support Macros, so is not an option.

By the way, I'll add another rave review for RPM. A genuine masterpiece.

Thanks.
rennale
Posts: 110
Joined: Sun Apr 23, 2017 6:50 am

Re: Retiree Portfolio Model

Post by rennale »

I can now answer my own question.

I purchased "Office 2016 for Mac" from Amazon for $130. In addition to Excel it provides Word, Powerpoint and Onenote. The original reviews for Excel for Mac back in 2016 were dismal, but I figured that a couple of years of updates would have redeemed it.

So it proved. With all the latest updates applied Excel on Mac works very well. It's considerably quicker than LibreOffice and has better support for Apple's Retina graphics. So I'm converting my other spreadsheets to use it too. RPM's macros work and all the graphs display correctly.

A couple of times it has stopped showing the help popups (when hovering the mouse over the little red triangles in cell corners), but a restart fixes that. LibreOffice usually doesn't even show the red triangles.

For the price it's a no brainer.
Last edited by rennale on Mon Feb 19, 2018 5:13 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 »

rennale wrote: Mon Feb 19, 2018 12:30 pm I purchased "Office 2016 for Mac" from Amazon for $130.
..
With all the latest updates applied Excel on Mac works very well. It's considerably quicker than LibreOffice and has better support for Apple's Retina graphics.
That's very good to learn as there have been a number of Mac users over the years that had problems running the model. I'll add it to the list of programs that will run it.
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 »

Version 18.1 of the Retiree Portfolio Model model is now available. This update adds new features along with other improvements and updates. Calculated results have not changed from 18.0 so only those who would like to use one of the new features need to download it.

Note: the model was updated as of April 16 5PM for minor fixes and improvements to the "clear entries" and "load entries" macros. Not essential, but re-download if you want this tweak.

Replaced Sept 8, 2018 by version 18.2.

Changes in version 18.1 include:
Added option to make yearly IRA contributions in IRAs 1 and 2. Note: Taxes on IRA withdrawals have not been adjusted (reduced) for these after-tax contributions as of v18.1.
Added option to reduce Social Security benefits in a future year as being forecast by the SS Admin (e.g., 23% reduction in 2035). Tip of the hat to i-ORP for this idea!
Added option to manually enter yearly living expenses and yearly amounts in the state AGI calculation on the Details page. Use if adjustments are needed to programmed entries.
Added editable areas to document yearly living expenses, SS benefits and tax calculator notes.
Improved estimates of Income Related Medicare Adjustments and ACA Premium Tax Credits.
Updated and improved bonus Federal income tax estimator, including the ability to calculate either 2017 or 2018 taxes. Many thanks to Boglehead rennale for his valuable suggestions and help in this effort.
Formatting change on Summary page to show expenses, etc. as positive numbers (without brackets).
Added and adjusted page breaks for better printing results
Other informational labels, instructions and comments as suggested by users.

Significant new features 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.
Last edited by BigFoot48 on Sat Sep 08, 2018 2:28 pm, edited 5 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 »

Hello,
I assume RPM account balances are nominal (inflated dollars). Is there an easy way to display them in real (non-inflated) dollars?
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: Sun Apr 15, 2018 1:42 pm Hello,
I assume RPM account balances are nominal (inflated dollars). Is there an easy way to display them in real (non-inflated) dollars?
Thank you.
You can set the inflation rate to 0% (Section 5 - Expenses), and the tax rate/bracket escalator to 0% (Rax Tables page), and enter your earnings rates at real values (Section 3 - Return Rates). That should do it - I think.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

When I tried to use the "clear entry macro" I get an error (1004) "the cell or chart is on a protected sheet ". The debug shows it to be at:
!special events
Range("G156:J156").Select
Selection.ClearContents
Range("Inheritance").Select
ActiveCell.FormulaR1C1 = "n"
Range("Inherit_Taxable").Select
ActiveCell.FormulaR1C1 = "0"
Range("Inherit_tIRA").Select
ActiveCell.FormulaR1C1 = "0"
LesH8210
Posts: 3
Joined: Sat Jan 31, 2015 12:57 pm

Re: Retiree Portfolio Model - SPIA Exclusion Ratio

Post by LesH8210 »

I could use a little direction regarding the Exclusion Ratio for a SPIA purchase. I have reviewed several annuity offerings and each have a different method for defining the taxable portion. One in particular states the monthly payment is $2376 and the taxable portion is $323 per month. Based on this what is the correct exclusion ratio that RPM needs?

Thanks, excellent design and a really good way to learn the big picture of our finances!

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

sandramjet wrote: Sun Apr 15, 2018 4:32 pm When I tried to use the "clear entry macro" I get an error (1004) "the cell or chart is on a protected sheet ". The debug shows it to be at:
!special events
Range("G156:J156").Select
Sorry about that! Fixed. I failed to test the clear macro with page protection turned on and it faulted on those cell numbers which had changed due to form row additions. Works now.
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 - SPIA Exclusion Ratio

Post by BigFoot48 »

LesH8210 wrote: Sun Apr 15, 2018 4:52 pm I could use a little direction regarding the Exclusion Ratio for a SPIA purchase. I have reviewed several annuity offerings and each have a different method for defining the taxable portion. One in particular states the monthly payment is $2376 and the taxable portion is $323 per month. Based on this what is the correct exclusion ratio that RPM needs?

Thanks, excellent design and a really good way to learn the big picture of our finances!

Les H
It would be 1-323/2376 or 86.4%. Note the model by default rounds to the nearest $100 so in testing it the numbers became $2,400 and an exclusion of $2,100 (using 86.4%) resulting in taxable income of $300. (I used the monthly numbers but the model is in yearly amounts so multiple by 12.)
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
rjensen52
Posts: 1
Joined: Mon May 14, 2018 2:46 pm

Re: Retiree Portfolio Model

Post by rjensen52 »

Exemptions bug.

Setup tab, 8. Income Taxes section

I change H185 to equal 1. When I put in any value into H186 it generates an error. Looking into the Data Validation for H186 the Maximum: =#REF!, but it should probably be G25.

Also H187 Maximum: =J20, but it should probably be G26.

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

rjensen52 wrote: Mon May 14, 2018 3:31 pm Exemptions bug.
Thanks for reporting that bug and its been fixed! Personal Federal exemptions are not used beginning in 2018, but the age checking has been fixed to require the starting age to be greater than Your starting age and the ending age less than Your ending age. The extra exemptions are not currently used in the state calculation.

Unless someone has a 2017 or earlier start year and uses the extra exemptions feature, there's no reason to download the model again.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
marcopolo
Posts: 8445
Joined: Sat Dec 03, 2016 9:22 am

Re: Retiree Portfolio Model

Post by marcopolo »

Thanks for all the work that goes into developing/maintaining this!

Any chance there will be a versions that includes as an input, the cost basis of taxable account and takes into consideration the capital gains tax paid on withdrawals from such an account, as well as its impact on Roth conversion tax rates. This seems like it would change the Roth Conversion analysis quite a bit for an early retiree doing Roth Conversions while living off withdrawals from taxable account.

Until then, is there a recommended work around to try to model this scenario?
Once in a while you get shown the light, in the strangest of places if you look at it right.
User avatar
Topic Author
BigFoot48
Posts: 3115
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

marcopolo wrote: Sat May 26, 2018 9:48 am Any chance there will be a versions that includes as an input, the cost basis of taxable account and takes into consideration the capital gains tax paid on withdrawals from such an account, as well as its impact on Roth conversion tax rates. This seems like it would change the Roth Conversion analysis quite a bit for an early retiree doing Roth Conversions while living off withdrawals from taxable account.

Until then, is there a recommended work around to try to model this scenario?
An improvement on how taxable account earnings are taxed based on investments and qualified dividends and capital gains distributions is on the to-do list. In the meantime, you should use the Taxable Account Adjustment to reduce the calculated yearly taxable account earnings. After this reduction, the taxable earnings balance should be such that, when taxed at your marginal rate, the taxes will approximate your actual taxes in the account when including the lower qualified dividends and capital gains rates.

For example, if the taxable account is $100,000 and the earnings rate is 5%, then $5,000 in earnings will be taxed at normal rates unless the Adjustment is made. Suppose $2,000 of QD and CG are expected based on historic results. This leaves $3,000 that should be subject to normal taxes plus a small (0% or 15%, etc.) tax on the $2,000. So a 60% Adjustment (reduction) will get the taxable income close to what it should be. Unfortunately there's no calculator at this time for getting a better rate, say 62%, that would include the QD/CD taxes, if any.

Just keep in mind that there are so many variables at work and actual earnings rates, etc. will be different from a multi-year forecast that any modeling should just be considered a broad estimate and in each year doing Roth conversions a more precise calculation must be done to keep conversions at the necessary amount to maximize tax benefits.

Good luck! Glad the model is a help and I will see if I can improve the taxable account taxes calculation.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
marcopolo
Posts: 8445
Joined: Sat Dec 03, 2016 9:22 am

Re: Retiree Portfolio Model

Post by marcopolo »

BigFoot48 wrote: Sat May 26, 2018 8:15 pm
marcopolo wrote: Sat May 26, 2018 9:48 am Any chance there will be a versions that includes as an input, the cost basis of taxable account and takes into consideration the capital gains tax paid on withdrawals from such an account, as well as its impact on Roth conversion tax rates. This seems like it would change the Roth Conversion analysis quite a bit for an early retiree doing Roth Conversions while living off withdrawals from taxable account.

Until then, is there a recommended work around to try to model this scenario?
An improvement on how taxable account earnings are taxed based on investments and qualified dividends and capital gains distributions is on the to-do list. In the meantime, you should use the Taxable Account Adjustment to reduce the calculated yearly taxable account earnings. After this reduction, the taxable earnings balance should be such that, when taxed at your marginal rate, the taxes will approximate your actual taxes in the account when including the lower qualified dividends and capital gains rates.

For example, if the taxable account is $100,000 and the earnings rate is 5%, then $5,000 in earnings will be taxed at normal rates unless the Adjustment is made. Suppose $2,000 of QD and CG are expected based on historic results. This leaves $3,000 that should be subject to normal taxes plus a small (0% or 15%, etc.) tax on the $2,000. So a 60% Adjustment (reduction) will get the taxable income close to what it should be. Unfortunately there's no calculator at this time for getting a better rate, say 62%, that would include the QD/CD taxes, if any.

Just keep in mind that there are so many variables at work and actual earnings rates, etc. will be different from a multi-year forecast that any modeling should just be considered a broad estimate and in each year doing Roth conversions a more precise calculation must be done to keep conversions at the necessary amount to maximize tax benefits.

Good luck! Glad the model is a help and I will see if I can improve the taxable account taxes calculation.
Thanks!

I was trying to figure what the intent of those Adjustments were. This makes sense and is a creative way to use the adjustment. Appreciate the help.
Once in a while you get shown the light, in the strangest of places if you look at it right.
Post Reply