Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
trueblueky
Posts: 1624
Joined: Tue May 27, 2014 3:50 pm

Re: Maximum Retirement Income

Post by trueblueky » Tue Jul 16, 2019 5:40 pm

ncdad1 wrote:
Tue Jul 16, 2019 3:50 pm
I wanted to end spending my last dollar on my last day and so have been trying to use Goal Seeker to backsolve for the maximum income I can take to end up with zero portfolios when I die. Is that the right way to do it?
cyanide (it's organic!) or arsenic (all natural!) :confused

kaesler
Posts: 60
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Adding "head of household" filing status to RPM

Post by kaesler » Wed Jul 17, 2019 10:25 am

I will be filing taxes as "head of household" in early retirement. How difficult would it be to add this option to the spreadsheet? I intend to look at doing it myself if it is feasible no-one has already started.

Guessing it may be complicated by the need to use two different tax filing statuses over the retirement period. The HOH status ends and single status kicks in after a few years of retirement.

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

Re: Adding "head of household" filing status to RPM

Post by BigFoot48 » Wed Jul 17, 2019 11:18 am

kaesler wrote:
Wed Jul 17, 2019 10:25 am
I will be filing taxes as "head of household" in early retirement. How difficult would it be to add this option to the spreadsheet? I intend to look at doing it myself if it is feasible no-one has already started.

Guessing it may be complicated by the need to use two different tax filing statuses over the retirement period. The HOH status ends and single status kicks in after a few years of retirement.
I'll take a look at it for 2020 but you're the first person to ask about it. As a work-around, you might be able to change the joint return factors on the Tax page to HOH factors and use joint return, then select a year to turn single.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

kaesler
Posts: 60
Joined: Mon Mar 12, 2007 4:53 pm
Location: Boston, MA

Re: Adding "head of household" filing status to RPM

Post by kaesler » Wed Jul 17, 2019 11:22 am

BigFoot48 wrote:
Wed Jul 17, 2019 11:18 am
kaesler wrote:
Wed Jul 17, 2019 10:25 am
I will be filing taxes as "head of household" in early retirement. How difficult would it be to add this option to the spreadsheet? I intend to look at doing it myself if it is feasible no-one has already started.

Guessing it may be complicated by the need to use two different tax filing statuses over the retirement period. The HOH status ends and single status kicks in after a few years of retirement.
I'll take a look at it for 2020 but you're the first person to ask about it. As a work-around, you might be able to change the joint return factors on the Tax page to HOH factors and use joint return, then select a year to turn single.
Many thanks for considering it. You have given me a serviceable workaround for now.

Big Dog
Posts: 1555
Joined: Mon Sep 07, 2015 4:12 pm

Re: Retiree Portfolio Model

Post by Big Dog » Tue Jul 30, 2019 6:26 pm

Man, I used to think that I was proficient in Excel, but this is impressive (and perhaps above my pay grade so to speak).

Two questions: 1) I have zero income for next 4 years (until age 70 for SS & RMDs), so model assumes I will live off of Taxable account, and decrements Taxable. While true, the model does not account for the Taxes that will be due for selling equities to live on. How can I add in the approx. taxes due for selling say, $100k of stock (all long term) each year. (Yes, when I run out of cash in the MMF to pay the taxes, I'll have to sell $100k+enuf to pay the tax hit.)

2) When I run RPM, it says no benefit to Roth conversions, but when I run iORP, it has me converting Roth's for next four years, and even doing some beyond that. Any idea what might be driving the wildly different answers (besides Operator Error)?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jul 30, 2019 6:58 pm

You can use the "User input income, credit and adjustments", line 191, on the Details page, to enter amounts to increase taxable income from selling those stocks. The amounts entered will become part of AGI so you will need to estimate how much to enter that will result in the calculated bracket taxes going up by your estimated taxes from selling them.

To include Roth conversions you need to turn on that option at the top of the Setup page then enter the amounts you want to convert in the Roth Conversion section 10. It's all done by the user in RPM actually pondering how much to convert every year based on tax bracket amounts and other factors.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Big Dog
Posts: 1555
Joined: Mon Sep 07, 2015 4:12 pm

Re: Retiree Portfolio Model

Post by Big Dog » Tue Jul 30, 2019 7:57 pm

ahh got it, thanks so much BigFoot.

Bogleer
Posts: 29
Joined: Thu Feb 28, 2008 10:11 pm

Re: Retiree Portfolio Model

Post by Bogleer » Fri Aug 30, 2019 8:58 am

New user of the RPM spreadsheet here (version 19.1a). Great tool and I'd like to add my thanks to BigFoot48 for all his efforts, and to those who are on this thread answering questions for those of us who are new users.

I've been playing around with RPM for a week or so and I think I may have found a small error. If it's already been reported, my apologies, I didn't see it in this thread.

On the Setup page, Column G258 through G288 (IRA 1 used for conversion, other withdrawals) is "=-Details!$XXYY". But from G289 though G297, the minus sign is a plus sign. Is this a mistake? Or am I missing something?

Thanks!

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Aug 30, 2019 9:13 am

I'm glad you like the tool and thanks for reporting this problem. Those cells for years 31-40 should have a negative sign and I'll fix that. These are informational cells in the Setup Roth Conversion section and do not impact any calculations. Thanks again.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

RangeleyLake
Posts: 3
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Sat Aug 31, 2019 6:06 pm

Hi BigFoot

Thanks again for this wonderful tool. I am still finding new opportunities to draw down our money and do Roth Conversion during our retirement years. I have one question about the cell commnt

I was looking at the cell comments recently on the setup Page. I notice in Section 6. IRA Contributions & Withdrawals that the Roth 1 cell comment is similar to the IRA 1 cell comment.

It states:
Enter amounts and factors to schedule withdrawals from IRA 1. Two withdrawal schedules are available. If there is no balance in IRA 1 the amounts are not used.
Suggested use: Withdrawal 1 for living expenses and 2 for Roth conversion tax funding or other temporary needs.
Withdrawals are taxable.

Can you verify this comment. Because the cell comment in Existing Roth IRAs states
Enter amounts and factors to schedule withdrawals from any existing Roth. If there is no existing Roths the amounts are ignored.

Withdrawals are not taxed.

Thank you in advanced in looking into this
RangeleyLake

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Aug 31, 2019 6:27 pm

RangeleyLake wrote:
Sat Aug 31, 2019 6:06 pm
I was looking at the cell comments recently on the setup Page. I notice in Section 6. IRA Contributions & Withdrawals that the Roth 1 cell comment is similar to the IRA 1 cell comment.
Thanks for reporting this. The Roth IRA cell comment was copied from the IRA cell, but someone! was suppose to edit it to apply to a Roth IRA. I will fix that to say something like:

Enter amounts and factors to schedule withdrawals from up to two Roth IRAs. Two withdrawal schedules are available for Roth 1. If there is no balance in an IRA, the amounts are not used.

Withdrawals are not taxable.


Thanks! Sorry for the confusing messages.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Diynofees
Posts: 3
Joined: Wed Jul 05, 2017 9:31 pm
Location: Boston

Re: Retiree Portfolio Model

Post by Diynofees » Thu Sep 05, 2019 10:12 am

Hi Bigfoot,

Thanks again for all you efforts...great tool.

Using the 19.1a version: it appears there is an issue with a data validation. I was trying to use the optional "asset allocation feature in a future age" under section 3. Inserting an age into cell E77 causes an age validation error. Upon analysis, the validator is pointing to some number off in the weeds at cell P195. Correcting this validation to some reasonable cell seems to allow the optional allocation figures to work ok.

It appears this needs correcting; but I was also wondering what your original intent was with this particular validation because the programmed error message refers to an "inherit age" as in an inherited IRA, I suppose. The optional allocation feature could be used for general modeling for any reason at any age change, so that's why I was wondering about the "inherit age" message. (I just changed the validator to the "Your_Age" cell, E25, to get this to work for me to look at an allocation change at any time in the future.)

Thanks!

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Sep 05, 2019 11:58 am

Diynofees wrote:
Thu Sep 05, 2019 10:12 am
Hi Bigfoot,

Thanks again for all you efforts...great tool.

Using the 19.1a version: it appears there is an issue with a data validation. I was trying to use the optional "asset allocation feature in a future age" under section 3. Inserting an age into cell E77 causes an age validation error.
PM sent. I am suspecting a model corruption problem as there's no data validation on ages entered into E77, no message link programmed to P195, and this section has nothing to do with inherited IRAs.

UPDATE: Diynofees did indeed find a cell data validation error which I originally missed. This non-functioning validation has been in the model since this change allocation feature was added in 2016. I will fix the validation so it actually checks against the user age and has the correct message.

As this is a non-functioning error message I am updating 19.1a. There is no need for a user to update for this problem.

Many thanks for Diynofees reporting this.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Boggedown » Tue Sep 24, 2019 6:18 pm

In v19.1a there seems to be an error in the Setup page Taxable account adjustment. Cell F207 has a formula that should (?) be in F205. Cell F205 has no dependents but apparently should, and the Help on it says the results will be used for all years.

[Incidentally the Help for cell F201 has typo "futre". The Help for cell B200 has typo "Margibal".]

I ran across these while trying to reflect the fact that I'll be selling from the Taxable account to get cash to cover taxes on my Roth conversions. These sales will have some capital gains effects / treatment. They may only be significant during the conversion years. Should I try to reflect them using the Taxable account adjustment (which seems right but affects all years) or the User adjustment to AGI (which seems like a workaround but can be done for specific years on the Details sheet).

Thanks

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Sep 24, 2019 6:40 pm

Thank you for the comments and typo alerts. The current version of the Taxable Account Adjustment was done with the help of a very knowledgeable BH member, and while the presentation and formulas may seem somewhat counter-intuitive, the adjustment works and provides a correct estimated of the reduction of taxable returns to account for lower-taxed earnings. However, I'll take a look at your findings and see if any clarification is needed.

I would use the User Adjustment for the few years you'll be selling assets to include the estimated tax impact, as the automated adjustment is for all years as you have noted.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

808
Posts: 53
Joined: Sun Sep 24, 2017 3:21 am

Re: Retiree Portfolio Model

Post by 808 » Tue Sep 24, 2019 10:36 pm

Boggedown wrote:
Tue Sep 24, 2019 6:18 pm
Cell F207 has a formula that should (?) be in F205.
Boggedown,
F205 = 1 - Taxable_Tax_Rate = 1 - F207

F207 = IF(E204=0,0,1-ROUND(E205/E204),2))
Assuming a non-zero value in E204, ignoring the ROUND, F207 = 1 - (E205/E204)

Thus, F205 = 1 - (1 - (E205/E204)) = 1 - 1 + E205/E204 = E205/E204 which is as stated in the comment.

Note that F206 does not do this trickery (the 1 minus 1 adjustment) and is simply defined as E206/E204, again assuming a non-zero value in E204 and ignoring the ROUND.

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

Re: Retiree Portfolio Model

Post by Boggedown » Mon Sep 30, 2019 2:12 pm

Thanks for the explanation.

The Setup page "Federal Taxes by Bracket" (and adjacent Base portfolio) error is still there. The Totals columns C and M do not include the 37% bracket value. This throws off the totals at the top of these charts as well. It doesn't affect other calculations but does matter if you rely on these to estimate taxes while experimenting with conversion values.

Also, I wonder if the title word "Default" in both chars is appropriate, considering that they are the active model as modified by all the user entries including Social Security values(?).

FYI a typo "withdrawan" in cells D142-144 notes.

Just a thought on IRMAA, understanding that changing any Setup now is a lot of work... Since everyone pays some IRMAA, and knows or can estimate their past two years MAGI, it seems like a natural for direct inclusion instead of an optional User transfer. As the latter it can be overlooked. But maybe there's an additional calc / logic complication I'm not seeing.
Last edited by Boggedown on Mon Sep 30, 2019 5:21 pm, edited 1 time in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Sep 30, 2019 3:41 pm

Boggedown wrote:
Mon Sep 30, 2019 2:12 pm
The Setup page "Federal Taxes by Bracket" (and adjacent Base portfolio) error is still there. The Totals columns C and M do not include the 37% bracket value.

Just a thought on IRMAA, understanding that changing any Setup now is a lot of work... Since everyone pays some IRMAA, and knows or can estimate their past two years MAGI, it seems like a natural for direct inclusion instead of an optional User transfer. As the latter it can be overlooked. But maybe there's an additional calc / logic complication I'm not seeing.
Thanks for reporting the Totals column error. 19.1a has been corrected for this, and any user in the 37% bracket may want to update if they are using this schedule. This has nothing to do with the calculation of income taxes for the 37% bracket. Your reported typos and others have been corrected.

I will consider including the IRMAA adjustment in the 2020 model as a forced expense item. However, while I think its good to be warned of such a possible future event, I don't think the amount rises to the level of a significant expense in the scheme of things.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by FiveK » Mon Sep 30, 2019 6:37 pm

BigFoot48 wrote:
Mon Sep 30, 2019 3:41 pm
I will consider including the IRMAA adjustment in the 2020 model as a forced expense item. However, while I think its good to be warned of such a possible future event, I don't think the amount rises to the level of a significant expense in the scheme of things.
"Significant expense" may be in the eye of the beholder. Below are the minimum marginal rates one incurs at the tier boundaries as a single filer as the Medicare MAGI goes from $85K to $160K. For MFJ it would be from $170K to $320K.

E.g., if one has $85,001 in other income and decides to withdraw $22K from a tIRA, thus reaching $107,001 MAGI, the marginal rate on that $22K is ((270.9+31.9)-(189.6+12.4))*12/(107001-85001) = 5.5%

Code: Select all

MAGI	Part B	Part D	Marginal
85001	189.6	12.4	Rate
107001	270.9	31.9	5.5%
133501	352.2	51.4	4.6%
160001	433.4	70.9	4.6%
ETA: fix typos.
Last edited by FiveK on Tue Oct 01, 2019 7:16 pm, edited 1 time in total.

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

Re: Retiree Portfolio Model

Post by sandramjet » Tue Oct 01, 2019 6:54 pm

FiveK wrote:
Mon Sep 30, 2019 6:37 pm
E.g., if one has $85,001 in other income and decides to withdraw $32K from a tIRA, thus reaching $107,001 MAGI, the marginal rate on that $32K is ((270.9+31.9)-(189.6+12.4))*12/(107001-85001) = 5.5%
Not sure I follow this... 107-85 is 22k, not 32k... so are you saying that the 22K has a marginal rate bump of 5.5%, and after that the next 10K of the 32K has no marginal rate bump? This is one of the things that I often get confused about when talking of marginal rates on these tax items that are step functions. Looking at the "marginal" rates right near the discontinuity makes the rates look huge, but the actual dollar amount is fixed (in this case at just over 1200 per year. That impact would be felt even if you went over by just $1 - which would be a huge percentage increase.

Seems like it makes more sense to compare these in real $ terms (ie 1200) to decide if it is "significant" rather than trying to think about whether the percentage is "significant"

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

Re: Retiree Portfolio Model

Post by FiveK » Tue Oct 01, 2019 7:15 pm

sandramjet wrote:
Tue Oct 01, 2019 6:54 pm
FiveK wrote:
Mon Sep 30, 2019 6:37 pm
E.g., if one has $85,001 in other income and decides to withdraw $32K from a tIRA, thus reaching $107,001 MAGI, the marginal rate on that $32K is ((270.9+31.9)-(189.6+12.4))*12/(107001-85001) = 5.5%
Not sure I follow this... 107-85 is 22k, not 32k... so are you saying that the 22K has a marginal rate bump of 5.5%,
Yes, typos. The 32s should have been 22s - nothing more.
Looking at the "marginal" rates right near the discontinuity makes the rates look huge, but the actual dollar amount is fixed (in this case at just over 1200 per year. That impact would be felt even if you went over by just $1 - which would be a huge percentage increase.
Yes, the intent of using the largest possible denominator was to avoid talking about those huge spikes. Seems clear enough that one would much rather be $1 under a tier than $1 over it.
Seems like it makes more sense to compare these in real $ terms (ie 1200) to decide if it is "significant" rather than trying to think about whether the percentage is "significant"
That's a very good observation. People do worry - perhaps overly so - about 10% vs. 12% and 22% vs. 24%. The IRMAA tier effects of at least ~5% are more significant than the 2% bracket steps, and more than the 3.8% NIIT.

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

Re: Retiree Portfolio Model

Post by Boggedown » Thu Oct 03, 2019 1:47 pm

With big conversion scenarios IRMAA can hit $5K/yr, and even $10K in distant projections. This is always small in percent but I suggest worth including by default.

Again something that's noticeable in big conversions: The Setup row 254 tables for Federal and State taxes show only the lower six brackets.

Minor consistency consideration: The Setup row 100 "SS Benefits and Expenses" table has income in blue bars, expenses in red dots. The nearby row 129 "Income and Expenses" table uses the opposite presentation.

Question: on the topic of showing current / uninflated dollar values, part of the Apr 2018 advice was to enter Section 3 earning rates at "real" values. How does that term apply to those percent estimates? For example if I estimate stocks will return 7%.

Thanks

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Oct 03, 2019 5:38 pm

Boggedown wrote:
Thu Oct 03, 2019 1:47 pm
With big conversion scenarios IRMAA can hit $5K/yr, and even $10K in distant projections. This is always small in percent but I suggest worth including by default.

I will consider this for 2020 as a selectable option since the amount is already being calculated.

Again something that's noticeable in big conversions: The Setup row 254 tables for Federal and State taxes show only the lower six brackets.

These brackets show the percent of each bracket being used for Roth conversions. Since the top bracket has no upper limit it would not be useful in this section.

Minor consistency consideration: The Setup row 100 "SS Benefits and Expenses" table has income in blue bars, expenses in red dots. The nearby row 129 "Income and Expenses" table uses the opposite presentation.

Graphs are designed to emphasize the primary elements of the section they are illustrating. They will not be color consistent.

Question: on the topic of showing current / uninflated dollar values, part of the Apr 2018 advice was to enter Section 3 earning rates at "real" values. How does that term apply to those percent estimates? For example if I estimate stocks will return 7%.

I have no knowledge of how real rates are determined for the various income factors and it's not an effort I have any interest in supporting. Users are on their own if they want a real return analysis.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Boggedown » Fri Oct 04, 2019 5:51 pm

Got it.

Actual bug report: Base row 212 cols H to AS are missing the row 211 factor.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Oct 04, 2019 6:03 pm

Boggedown wrote:
Fri Oct 04, 2019 5:51 pm
Got it.

Actual bug report: Base row 212 cols H to AS are missing the row 211 factor.
So it was! Fixed. Thanks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

User avatar
Cosmo
Posts: 1243
Joined: Mon Mar 05, 2007 9:46 pm

Re: Retiree Portfolio Model

Post by Cosmo » Fri Oct 04, 2019 8:22 pm

Apologies if this was already asked somewhere in this thread but is this spreadsheet compatible with OSX Numbers? TIA

Cosmo

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Oct 05, 2019 8:23 am

Cosmo wrote:
Fri Oct 04, 2019 8:22 pm
Apologies if this was already asked somewhere in this thread but is this spreadsheet compatible with OSX Numbers? TIA

Cosmo
This was my conclusion back in 2015 (do a thread search on OSX to see) and I suspect nothing has changed.
As for the Apple O/S, a forum member recently tested RPM with Apple Numbers and there were numerous errors preventing the model from running, so it's not compatible with Numbers.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

sampaine
Posts: 10
Joined: Wed May 11, 2016 11:00 am

Re: Retiree Portfolio Model

Post by sampaine » Sun Oct 06, 2019 10:54 am

I'm sorry I haven't been able to figure out the answer to this. I'm confused about how to account for RMDs on the Setup page. Are RMDs automatically calculated and taken into account (we're both over 70.5) or do I need to make an IRA withdrawal entry? If I don't plan on any IRA withdrawals beyond RMDs, do I do anything in the IRA withdrawals section? Thanks!

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Oct 06, 2019 11:01 am

sampaine wrote:
Sun Oct 06, 2019 10:54 am
I'm sorry I haven't been able to figure out the answer to this. I'm confused about how to account for RMDs on the Setup page. Are RMDs automatically calculated and taken into account (we're both over 70.5) or do I need to make an IRA withdrawal entry? If I don't plan on any IRA withdrawals beyond RMDs, do I do anything in the IRA withdrawals section? Thanks!
If you have IRA balances and enter age data in Section 1: IRA RMD ages and owners, the RMD amounts will be calculated and automatically made. I am 71 and just looked at my personal RPM model for 2019 and it has the exact RMD (rounded to the nearest $100) that Schwab told me to take for this year.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Boggedown » Sun Oct 06, 2019 1:28 pm

Supposing you'd want to know...

• Sec. 6 "IRA 1 Withdrawal 1" (and Withdrawal 2) - A positive value entered works as expected, being treated as a withdrawal from the start age to end age spec'd. But a negative value will run from the start age to model end year ie end age is ignored. I didn't use the IRA 1 Contributions box because of some confusion then over how RPM money flows. So I tried a negative withdrawal because RPM does in some cases allow negative contribs and withdrawals. But this logic may be too complex to reasonably manage that. However, the application to all years could reveal a fixable bug.

• If "future tax rate change" is enabled in Setup top Modeling Options, then future entries in the "Marginal Federal Tax Rates" graph appear to be off. At least they differ from the Sec. 10 "Federal taxes by bracket" table, which I think reflects the actual values in use. (The same may be true of the "Average Federal Tax Rates" graph but that's harder to judge). I think this is because the base tax tables aren't / can't be rewritten for the alternate future case(s). If the charts are in fact off, an alert message could be added.
Last edited by Boggedown on Sun Oct 06, 2019 2:51 pm, edited 2 times in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Oct 06, 2019 2:43 pm

Boggedown wrote:
Sun Oct 06, 2019 1:28 pm
Supposing you'd want to know...

• Sec. 6 "IRA 1 Withdrawal 1" (and Withdrawal 2) - A positive value entered works as expected, being treated as a withdrawal from the start age to end age spec'd. But a negative value will run from the start age to model end year ie end age is ignored. I didn't use the IRA 1 Contributions box because of some confusion then over how RPM money flows. So I tried a negative withdrawal because RPM does in some cases allow negative contribs and withdrawals. But this logic may be too complex to reasonably manage that. However, the application to all years could reveal a fixable bug.

Using negative withdrawal factors is not and will not be supported in the model.

• If "future tax rate change" is enabled in Setup top Modeling Options, then future entries in the "Marginal Federal Tax Rates" graph appear to be off. At least they differ from the Sec. 10 "Federal taxes by bracket" table, which I think reflects the actual values in use. (The same may be true of the "Average Federal Tax Rates" graph but that's harder to judge). I think this is because the base tax tables aren't / can't be rewritten for the alternate future case(s). If the charts are in fact off, an alert message could be added.

An alert will be added in 2020.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Boggedown » Sun Oct 06, 2019 5:00 pm

Right- this time I really have got it.

User avatar
Cosmo
Posts: 1243
Joined: Mon Mar 05, 2007 9:46 pm

Re: Retiree Portfolio Model

Post by Cosmo » Thu Oct 10, 2019 8:58 pm

BigFoot48 wrote:
Sat Oct 05, 2019 8:23 am
Cosmo wrote:
Fri Oct 04, 2019 8:22 pm
Apologies if this was already asked somewhere in this thread but is this spreadsheet compatible with OSX Numbers? TIA

Cosmo
This was my conclusion back in 2015 (do a thread search on OSX to see) and I suspect nothing has changed.
As for the Apple O/S, a forum member recently tested RPM with Apple Numbers and there were numerous errors preventing the model from running, so it's not compatible with Numbers.
Thank you for your response, BigFoot48.

Cosmo

ceejay185
Posts: 7
Joined: Thu Feb 18, 2010 4:58 pm

Google Sheets

Post by ceejay185 » Sun Oct 13, 2019 11:54 am

Just wondering if anyone else has experienced issues with importing RPM into Google Sheets?

I have been trying to import the latest version (19.1a) and keep getting the error message "Too large to import. Remove rows or columns and try again."

Any help or guidance would be greatly appreciated.

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

Re: Google Sheets

Post by BigFoot48 » Mon Oct 14, 2019 12:32 am

ceejay185 wrote:
Sun Oct 13, 2019 11:54 am
Just wondering if anyone else has experienced issues with importing RPM into Google Sheets?

I have been trying to import the latest version (19.1a) and keep getting the error message "Too large to import. Remove rows or columns and try again."

Any help or guidance would be greatly appreciated.
I have confirmed that Google Sheets is returning this message when trying to open 19.1a and 19.1. 19.0 can be opened. This is puzzling since 19.1 is well within the size limit for Sheets - 100MB and 5 million cells. I will review the changes since 19.0 and see if any of them may be causing this message. Thanks for the report.

UPDATE: I have tested RPM releases since 19.0 and none will work in Sheets. All of these had just normal edits to them from 19.0, and none changed significantly the scope or size of the model. Sheets is seeing something that causes this size error message. At this point I'm at a loss on how to fix this but will try to get Google to look at it, and recommend Excel Online as an alternative.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

ceejay185
Posts: 7
Joined: Thu Feb 18, 2010 4:58 pm

Re: Retiree Portfolio Model

Post by ceejay185 » Mon Oct 14, 2019 10:59 am

Thanks so much for the quick response! Will go with Excel...

ncdad1
Posts: 6
Joined: Mon Apr 22, 2019 12:50 pm

Single Premium Immediate Annuity vs. QLAC

Post by ncdad1 » Mon Oct 14, 2019 11:22 am

Is the Single Premium Immediate Annuity in the model the same as a QLAC?

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

Re: Single Premium Immediate Annuity vs. QLAC

Post by BigFoot48 » Mon Oct 14, 2019 11:45 am

ncdad1 wrote:
Mon Oct 14, 2019 11:22 am
Is the Single Premium Immediate Annuity in the model the same as a QLAC?
It is not, although it may be possible to model the impact of a Qualified Longevity Annuity Contract using this feature, but I have no knowledge on whether it would accurately model it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

palanzo
Posts: 9
Joined: Thu Oct 10, 2019 4:28 pm

Re: Google Sheets

Post by palanzo » Sun Oct 20, 2019 4:04 pm

BigFoot48 wrote:
Mon Oct 14, 2019 12:32 am
...recommend Excel Online as an alternative.
Does RPM work well with the free online version of Excel? Does it also work well with LibreCalc?

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

Re: Google Sheets

Post by BigFoot48 » Sun Oct 20, 2019 4:08 pm

palanzo wrote:
Sun Oct 20, 2019 4:04 pm
BigFoot48 wrote:
Mon Oct 14, 2019 12:32 am
...recommend Excel Online as an alternative.
Does RPM work well with the free online version of Excel? Does it also work well with LibreCalc?
The calculations all work to the best of my knowledge but cell comments may not be visible and graphs may not display properly. I recently downloads LibreCalc 6.3 and it worked, a little slow, except for the displaying of cell comments (it showed a few). Graphs looked good.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

palanzo
Posts: 9
Joined: Thu Oct 10, 2019 4:28 pm

Re: Google Sheets

Post by palanzo » Sun Oct 20, 2019 4:46 pm

BigFoot48 wrote:
Sun Oct 20, 2019 4:08 pm
palanzo wrote:
Sun Oct 20, 2019 4:04 pm
BigFoot48 wrote:
Mon Oct 14, 2019 12:32 am
...recommend Excel Online as an alternative.
Does RPM work well with the free online version of Excel? Does it also work well with LibreCalc?
The calculations all work to the best of my knowledge but cell comments may not be visible and graphs may not display properly. I recently downloads LibreCalc 6.3 and it worked, a little slow, except for the displaying of cell comments (it showed a few). Graphs looked good.
It sounds like LibreCalc is better than the free online version of Excel. Do you recommend the full version of Excel rather than using LibreCalc?

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

Re: Google Sheets

Post by BigFoot48 » Sun Oct 20, 2019 4:54 pm

palanzo wrote:
Sun Oct 20, 2019 4:46 pm
BigFoot48 wrote:
Sun Oct 20, 2019 4:08 pm
palanzo wrote:
Sun Oct 20, 2019 4:04 pm
BigFoot48 wrote:
Mon Oct 14, 2019 12:32 am
...recommend Excel Online as an alternative.
Does RPM work well with the free online version of Excel? Does it also work well with LibreCalc?
The calculations all work to the best of my knowledge but cell comments may not be visible and graphs may not display properly. I recently downloads LibreCalc 6.3 and it worked, a little slow, except for the displaying of cell comments (it showed a few). Graphs looked good.
It sounds like LibreCalc is better than the free online version of Excel. Do you recommend the full version of Excel rather than using LibreCalc?
I recommend Excel as all the substitutes have one problem or another. It's developed and maintained on Excel (currently 2013) and it's going to give the best user experience. All the rest - downloads and online - have issues.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

808
Posts: 53
Joined: Sun Sep 24, 2017 3:21 am

Re: Retiree Portfolio Model

Post by 808 » Thu Oct 24, 2019 2:24 am

v19.1a

Setup tab
T114 and T115
Test_FRA_Y should be compared to value 66 like the formula in T113

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Oct 24, 2019 8:45 am

808 wrote:
Thu Oct 24, 2019 2:24 am
v19.1a

Setup tab
T114 and T115
Test_FRA_Y should be compared to value 66 like the formula in T113
Thanks for reporting this missing code in the Social Security calculator (does not impact model results) for
"You Benefits Before COLA", ages 69 and 70. Version 19.1a has been corrected.

No need for users to update for this unless they are using the calculator to see what SS benefits might be in those ages.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

louden00
Posts: 2
Joined: Fri Nov 15, 2019 3:10 pm

Re: Retiree Portfolio Model

Post by louden00 » Fri Nov 15, 2019 3:15 pm

Thanks to all who have contributed to the Retiree Portfolio Model. I have found it quite helpful.

Now for what may be a stupid question: Is the Net Investment Income Tax surcharge factored into the tax calculations in the model?

I have searched high and low for this answer - both with Internet searches and looking through all of the tabs of the spreadsheet - with no success.

I would very much appreciate any help.

Regards,
Bob

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Nov 15, 2019 4:25 pm

louden00 wrote:
Fri Nov 15, 2019 3:15 pm
Thanks to all who have contributed to the Retiree Portfolio Model. I have found it quite helpful.

Now for what may be a stupid question: Is the Net Investment Income Tax surcharge factored into the tax calculations in the model?

I have searched high and low for this answer - both with Internet searches and looking through all of the tabs of the spreadsheet - with no success.

I would very much appreciate any help.

Regards,
Bob
Since I had to look that up the odds are 100% that it's not in the model. The model is not designed to be a completely accurate modeling of income taxes but rather a fairly good estimate based on typical earnings of a retiree or near-retiree using current brackets and rates. There is a line for entering yearly adjustments to AGI not provided for in the model so perhaps that could be used to create an estimate of the surcharge.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

louden00
Posts: 2
Joined: Fri Nov 15, 2019 3:10 pm

Re: Retiree Portfolio Model

Post by louden00 » Sun Nov 17, 2019 10:18 am

Thank you. It really is an amazing tool - especially for all of the special situations that it does cover.

For a moment, I had the idea of creating a new tax bracket (for MFJ, going from 24% to 27.8% as income goes above 250K) but then I realized that the extra 3.8% only applies to "investment income" rather than all income. So that would not work.

Thanks again.

Bob

BennyW
Posts: 2
Joined: Fri Feb 08, 2019 5:32 pm

Alternative Tax Rates Change Year

Post by BennyW » Sun Nov 24, 2019 6:16 pm

First off, thanks so much for the wonderful tool. Without it, I would be lost at Roth conversion planning or spending mega $$$ for a CPA to do it for me.

I do have a question on section 9. Alternative Tax Rates.

As I look at my Roth Conversions and the average Federal tax rate for each year, I notice that the rate jumps up in year 2025.

I have search the tax cut law and as I read it, the personal rates expire after 2025. I'm wondering if the correct default value for the "Change Year" in section should be 2026 and not 2025 and hence the lower rates still apply to conversions in 2025.

Thanks, Benny.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Nov 24, 2019 11:59 pm

Benny
I'm glad you're finding the model useful. I have chosen not to model the possible return to the pre-2018 Federal tax structure in 2026. The fact that the Alternative Tax section sample data has a change year near that possible year is merely coincidental.

This section could be used to model to the prior bracket rates in 2026, but it will not change the current law bracket income ranges, deductibles, etc. used in the model, so it would not be a totally accurate calculation of the taxes in those future years. It may be close enough to get a general idea, however.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

BennyW
Posts: 2
Joined: Fri Feb 08, 2019 5:32 pm

Re: Retiree Portfolio Model

Post by BennyW » Mon Nov 25, 2019 9:34 am

Thanks for the clarification.

- Benny

Post Reply