Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Version 19.1 of the Retiree Portfolio Model model is available as of March 1, 2019 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/9tmifm3lznlef ... .xlsm?dl=0

Current version updates:
April 20: The Portfolio Balance at Year End on the Summary page was not being calculated correctly (expenses were added rather than subtracted.) Fixed. Thanks to an anonymous user.
March 10: Fixed ACA calculation to still include non-taxed benefits.
March 6: Removed non-taxed SS benefits from MAGI calculation for Medicare premiums surcharges and added two year pause if a spouse dies. Thanks to BH sawdust60 for reporting this problem in this informational alert (doesn't impact model calculations). No need to update for most users.

Changes in version 19.1 include:
Graphs returned to v18 format as new formatting in 19.0 caused error messages for Apple Mac users.
Ability to make contributions to Roth 1 IRA added.
Correction of certain settings that may have affected taxable pension benefits for state income taxes.
Estimate of impact of Roth conversion on heirs taxes added. See Results page. Idea from BHs jchris and Boggedown.
Life expectancy tables used to calculate RMD expanded to start at age 50.
Added state taxes to Tax Brackets schedule on Setup page. Suggestion of BH Boggedown
Social Security benefits calculator showing yearly benefits for ages 62-70 improved. Suggestion of BH Zephavest.
Non-Excel products tested and now listed in order of functionality, with those not showing cell comments noted. Comments are essential to using the model.
Various headings and cell comments improved or corrected based on user suggestions

Most users of v19.0 may not need the changes in v19.1.

Version 19.0 with the revised graphs (will not work for Mac users) can be downloaded from Dropbox via this link: https://www.dropbox.com/s/cjkyy4wpce4wf ... .xlsm?dl=0

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
Last edited by BigFoot48 on Sat Apr 20, 2019 7:15 pm, edited 7 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

munemaker wrote: Thu Feb 28, 2019 8:01 pm
rts58 wrote: Thu Feb 28, 2019 12:10 pm BTW, I made the change and saw my Base plan was much better than the Roth Conv plan. Then realized I had to make the change to the Base tab as well as Detail!
Can you please explain further? If you change the inputs, don't they change both the base and detail tabs? What did you change that affected the base plan but not the Roth conversion plan?
The feature of having two nearly identical portfolios to make instant comparisons of the impact of Roth conversions or alternative Social Security benefits requires that a change in the primary full case Details page must be copies to the base case. With Roth and SS alternative not selected the two portfolios must be equal.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
rts58
Posts: 88
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 »

munemaker wrote: Thu Feb 28, 2019 8:01 pm Can you please explain further? If you change the inputs, don't they change both the base and detail tabs? What did you change that affected the base plan but not the Roth conversion plan?
Sorry for not being clear. I was referring to the fix to a formula having to do with state taxable pensions. I found a problem on the "details" tab but the fix also needed to be applied to the "Base" tab.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

rts58 wrote: Fri Mar 01, 2019 8:34 am
munemaker wrote: Thu Feb 28, 2019 8:01 pm Can you please explain further? If you change the inputs, don't they change both the base and detail tabs? What did you change that affected the base plan but not the Roth conversion plan?
Sorry for not being clear. I was referring to the fix to a formula having to do with state taxable pensions. I found a problem on the "details" tab but the fix also needed to be applied to the "Base" tab.
Thanks for the detail.

Yes, I did the same thing...tweaked the formulas a bit to accommodate PA income tax rules, where retirement income (e.g. pensions, SS, traditional IRA distributions, etc.) are not taxed.

PA is not very friendly in a lot of ways, but in the case of state income taxes, they are very friendly to retirees.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

I want to trace the cell formulae, but I can't figure out how to turn on the gridlines:
Excel 2011 MAC help is not working:
Sometimes the information on your sheet is easier to read when the gridlines are hidden.
Click the sheet.
On the Layout tab, under View, clear the Gridlines check box.
***
So VIEW / LAYOUT turns on ruler along the top & left side.
***
Why can't I enter anything for "Money Mkt & Other?"
class method: asset Money Mkt
allocation for each account Stocks Bonds Tax Exempt & Other

EDIT: oh, the amount less than 100% for the 3 blue boxes is entered here. That means than if you make a mistake & enter less than 100% for the blue boxes, you will not receive an error message & it might stay screwed up. Everywhere else (at least up to here) you have to add up to 100%. This breaks the pattern.
Last edited by Bongleur on Mon Mar 04, 2019 8:01 pm, edited 1 time in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
retire2022
Posts: 3286
Joined: Tue Oct 02, 2018 6:10 pm
Location: NYC

Re: Retiree Portfolio Model

Post by retire2022 »

BigFoot48 wrote: Fri Mar 01, 2019 6:55 am Version 19.1 of the Retiree Portfolio Model model is available as of March 1, 2019 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/9tmifm3lznlef ... .xlsm?dl=0

Changes in version 19.1 include:
Graphs returned to v18 format as new formatting in 19.0 caused error messages for Apple Mac users.
Ability to make contributions to Roth 1 IRA added.
Correction of certain settings that may have affected taxable pension benefits for state income taxes.
Estimate of impact of Roth conversion on heirs taxes added. See Results page. Idea from BHs jchris and Boggedown.
Life expectancy tables used to calculate RMD expanded to start at age 50.
Added state taxes to Tax Brackets schedule on Setup page. Suggestion of BH Boggedown
Social Security benefits calculator showing yearly benefits for ages 62-70 improved. Suggestion of BH Zephavest.
Non-Excel products tested and now listed in order of functionality, with those not showing cell comments noted. Comments are essential to using the model.
Various headings and cell comments improved or corrected based on user suggestions

Most users of v19.0 may not need the changes in v19.1.

Version 19.0 with the revised graphs (will not work for Mac users) can be downloaded from Dropbox via this link: https://www.dropbox.com/s/cjkyy4wpce4wf ... .xlsm?dl=0

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
I'm getting a 404 error message when clicking on the links, I assume this is down? Please advise thanks.
User avatar
LadyGeek
Site Admin
Posts: 95690
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Good timing, as someone just emailed bogleheads.org support* stating that version 19 has a 404 message. The person is not a member, but is using version 15.1 and wanted to upgrade.

* See: About Us | Bogleheads Investing Advice and Info or Forum Policies (Section 1.).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Dunno why DELETE isn't working for my dup post.

EXPENSES / IRMAA Surcharges -- optional feature.
If I'm not high income subject to it, I enter 0 and 0 in the blue boxes, right?
So why does the TOTAL SURCHARGES add up to many thousands?
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Why does it think that one of us is going to die in 3 years?

ages and settings Starting Ending
. First year 2019 2058
Years to forecast 40 End Age Model End
. Your age 60 99
Spouse age 62 101 101

*****
filing status Factors
Filing status m
Year single rate starts 3
Override single rate start year not using
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

FYI the choice popup is miniscule for this:
Bracket method factors
Use Fed itemized or standard deduction? i
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Shouldn't choosing 'i" flag an error if you are Not Using the "itemized deductions" ?

itemized deductions
Beginning in 2019 0 not using
Yearly change 5.0%
End age 68 2027

state rates and factors
Bracket method factors
Use Fed itemized or standard deduction? i
Standard deduction 5,000
Personal exemption 2,000
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Mon Mar 04, 2019 8:17 pm Good timing, as someone just emailed bogleheads.org support* stating that version 19 has a 404 message. The person is not a member, but is using version 15.1 and wanted to upgrade.

* See: About Us | Bogleheads Investing Advice and Info or Forum Policies (Section 1.).
Apologizes to all. I have been having computer problems and during the resolution is appears some files have been lost. The 19.1 model is temporarily off-line.

Update: 19.1 available again as of the time of this post.

Any user questions about the model will be addressed at a later time.
Last edited by BigFoot48 on Mon Mar 04, 2019 10:39 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95690
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

From someone who has to keep an entire website up running, it's not a big deal. Take your time and fix it right. :)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

the Social Security Benefits "end age" appears to transpose the Death Age to the other spouse.
It requires careful reading to figure this out, but people tend NOT to do that.

One problem is that you have at least 3 cells labeled "end age" but 2 of them are actually "age at death" and they should be linked together (and I guess they are).

Please give unique names so cells that have a common input are not confused with cells that do something else

BTW I used to be famous for "breaking" software because I always do things the writer did not anticipate.

at Ages & Settings, END AGE could be "Death Age."
The popup here tells the user to USE the same age at SS benefits. It should say "This age is automatically entered..."

At Pension, END AGE can be used, and the popup might explain that if a pension becomes reduced but not eliminated, the user should utilize one of the "other income" rows for the start date of the reduced amount. Should it be the same age, or age +1 ?

At Social Security Benefits, the popup for END AGE could start with "Shows YOUR Age at spouse's death; and SS adjusted."
The label itself could be "SS adjust Age".
Last edited by Bongleur on Tue Mar 05, 2019 12:53 am, edited 1 time in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

But now that I think of it, this is the ONLY SWR calculator that accounts for the death of one spouse !
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

State Rates & Factors
Percent Method Rate

Only allows exemption from state taxes for one of the partner's Pensions.
What if both are exempt?
What if the exempt pension is pension 2 ?

EDIT -- oh, its an INCLUSION... but the question still stands.

Usability Item : The 3 possibly excluded items should be phrased the same way. SS is a "negative" but the other two have to be entered as "positives." When data entry for similar concepts works in opposite ways, mistakes happen easier. There is room to be less parsiminous with words in the cell names:
Exclude SS ? (y/n)
Excluded % of Pension 1
Excluded % of Tax Exempt Investments

Or "Include(d)" but same word for all 3.

EDIT: I'm not complaining here, I really want to help make it more user friendly. I used to write & teach safety related procedures at a place that required a security clearance...
Last edited by Bongleur on Tue Mar 05, 2019 1:11 am, edited 1 time in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

Bongleur wrote: Mon Mar 04, 2019 10:28 pm ages and settings Starting Ending
. First year 2019 2058
Years to forecast 40 End Age Model End
. Your age 62 80 101
Spouse age 60 99
I'm not sure I understand ... are you setting ending ages for both you and your spouse, shorter than what the model end is? (I have always just selected one or the other, not both)
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Cell E202 has an "empty cell" error pointing to the manual entry cell, when that cell is empty. I don't know how that relates to the following:

If Portfolio Balance is zero, then the "Federal Taxable Income" segment does not work. Check also what happens when all the interest rates are zero but balance positive.

All I can say is that it puts a number into "QD/LTCP Adjustment" sometimes but not always, even though "QD/LTCP Adjustment" has a number in it.

Zeroing the balance allows me to enter some particular investment income distribution amount & quickly find out the tax without changing all the yield cells to something that comes out to the number I want to use :

taxable account adjustment Model Amts Alternative note area
Taxable account yearly growth 10,000 10,000
Less tax-exempt earnings (0% of Taxable) 0
Net yearly growth 10,000
Net growth distributed as dividends, CG, etc. 10,000 100% ie TOTAL distrib
Distributed qualified dividends and LTCG 10,000 100% portion of TOTAL in categories

I expect Taxable Account Earnings and QD/LTCP Adjustment to be $10,000 because I manually said so:

Federal Taxable Income 2019
Taxable acct earnings 0

Less tax-exempt earnings 0
Less earnings adjustment 0
IRA RMD 0
Less QCD 0
Other taxable income 0
User adjustments 0
AGI bef txbl SS benefits 0
SS Benefits 0
Taxable SS benefits 0
Adjusted gross income 0
Deductions & exemptions (24,400)
QD/LTCP Adjustment 0
Federal taxable income 0

Income and Net Cash Flow
Total earnings & income 0
Living expenses 0
Federal taxes 0
State taxes 0
Net cash flow 0

EDIT: don't understand error msg "Error: exclude amount must be less distributed amount"
Does this mean
"amount excluded must be less than the amount distributed"
or
"amount excluded must have the distributed amount subtracted from it"

And both cells are titled as something being DISTRIBUTED so reference to something being Excluded makes no sense.
Gotta make this work for people who are not CPAs.

EDIT: how about labeling them:
$$ Distributed that counts as Income
Part of above which is qualified Div or LTCG

OK figured it out.
Last edited by Bongleur on Tue Mar 05, 2019 4:33 am, edited 7 times in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

sandramjet wrote: Tue Mar 05, 2019 12:19 am
Bongleur wrote: Mon Mar 04, 2019 10:28 pm ages and settings Starting Ending
. First year 2019 2058
Years to forecast 40 End Age Model End
. Your age 62 80 101
Spouse age 60 99
I'm not sure I understand ... are you setting ending ages for both you and your spouse, shorter than what the model end is? (I have always just selected one or the other, not both)
Death at 80 was showing up as 78 in the SS section. I expected 80. But its YOUR age at the death of your SPOUSE. And its also weird that its listed on YOUR row. One expects it to show up on the row of the dead person. I deleted my details in that post & gave some suggestions to reduce confusion.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Using the "who dies first" option requires some thought. Conservatively, you need to use both the earliest date of death for the pair and the greatest loss of income. If they are not the same person:

1) the one entered to die should be the one whose loss reduces income by the most.

2) but WHEN he/she dies needs to be jiggered. You want to use the earliest expected date of death between the two. If that is the other spouse, then figure out how many years from now that will be, and add that to the current age of the one chosen to die first.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

RETURN RATES & ALLOCATION
the segment for "Return rates: by account" should have the same sentence structure as the other segment:
Account Method: return rates

The "average returns calculator" does not DO anything. It does not feed into any calculations. But it has the appearance that it might. Confusing.
There are a lot of these sorts of segments that are merely informational, not affecting spreadsheet results. Can you give them all a unique identifier, maybe surrounding them with a color, or putting them in italics, or titling them all with the prefix "INFO" ?

EDIT: make their background color other than blue.
EDIT: looks like the titles are in green print. However, the 1st level outline is already green. Also, the "calculators" popup is kind of hidden away in a random spot. It should tell the reader how to distinguish these items. People don't necessarily notice a subtle color change -- or color blind cannot. IMO making the color of the entry cells unique allows instant recognition when scanning around the sheet looking for something. They can be ignored without much cognitive effort.

Its hard to optimize conveying visual info to people whose worst learning style is visual, but that's the goal.

EDIT noticed you are also using green print for displaying results of internal calculations, such as Rates Being Used.
EDIT: the cell "Money Mkt & Others" should be in green font since it is the above type of data.
Last edited by Bongleur on Tue Mar 05, 2019 2:27 am, edited 3 times in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

Social Security Benefits:
optional alternative benefits

The FRA ages are locked, need to be blue user entered.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

calculator: Social Security benefits calculator

V19.0 gives me the same number as the IRS for benefit amount at age 70, the benefits letters are from June & Sept 2018.
V19.1 gives a smaller number.
Does the new version not take into account the 1/3 year for the "true" FRA of 66 2/3?

NOTE: I set the SS COLA to zero.

V19.0:
67 17,400 21,144 FRA number per IRS letter
68 18,792 22,836
69 20,184 24,527
70 21,576 26,219

versus V19.1

67 17,400 21,144
68 18,792 22,836
69 18,832 24,527
70 20,130 26,219

EDIT: in V19.0, the number in the Calculator matches the number to the left (after rounding):
Starting
21,600 YOU
26,200 SPOUSE

47,800 total

V19.1 has the same STARTING figures as V19.0.
But of course that's different from V19.1 calculator results ::
Dunno if this can be a significant error or not.
YOU.........SPOUSE
20,130 26,219

Why did you change the way the Calculator works, but not change the app to be the same?
Last edited by Bongleur on Tue Mar 05, 2019 6:31 am, edited 2 times in total.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

9. Optional: Alternative Tax Rates

the example in the popup does not match the default numbers in the blue boxes
12 + 5 <> 20

Married, Filing Jointly
2019 New Change Year
10% 15% 2025
12% 20% 2025
22% 30% 2025
24% 33% 2025
32% 38% 2025
35% 40% 2025
37% 45% 2025
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Re: Retiree Portfolio Model

Post by Bongleur »

The row "total federal taxes" could be clearer saying "total federal taxes paid".
At a glance, the numbers being above the percentage brackets, I thought it was the top of the bracket figure.
And IMO it would be nice to see the top of the bracket figure. If just "over" a bracket, you can look for a way to reduce taxable just a bit.

Federal Taxes By Bracket
Full default portfolio
Total
Federal Taxes 114,500 44,000 70,500
Year - Age Total 10% 12%
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Bongleur wrote: Tue Mar 05, 2019 5:52 am Excel 2011 MAC help is not working
Dunno why DELETE isn't working for my dup post.
Why does it think that one of us is going to die in 3 years?
FYI the choice popup is miniscule for this:
Shouldn't choosing 'i" flag an error if you are Not Using the "itemized deductions" ?
at Ages & Settings, END AGE could be "Death Age."But now that I think of it, this is the ONLY SWR calculator that accounts for the death of one spouse !
I'm not complaining here, I really want to help make it more user friendly.
don't understand error msg "Error: exclude amount must be less distributed amount"
& gave some suggestions to reduce confusion.
Using the "who dies first" option requires some thought.
The "average returns calculator" does not DO anything. It does not feed into any calculations. But it has the appearance that it might. Confusing.
There are a lot of these sorts of segments that are merely informational, not affecting spreadsheet results. Can you give them all a unique identifier, maybe surrounding them with a color, or putting them in italics, or titling them all with the prefix "INFO" ?
BTW I used to be famous for "breaking" software because I always do things the writer did not anticipate.
Bongleur,

All the things you are asking, a mental train of thought items, like this is piece of software you personally paid for and want it rewritten so YOU understand it. You can see how many years this thread has been running and only at page 13, at the rate you are going you will have another 13 pages in a few months. This software IS for SOPHISTICATED users who use it, as thousands have, and come to Understand it and work with what they have, not how they think it should be. Your questions continue to show your not understanding the big picture of the tool and are not taking time to figure it out on your own, meaning months of use. It has been pointed out this is a complicated tool only intended for advanced users, you really need to use i-ORP, the simple, free Optimal Retirement Planner: https://www.i-orp.com/bequest/index.html If that is to advanced for you then you should really consider consulting a CPA for your needs.

I spent my career in engineering, I have written and read countless software test reports. Yours, all the posts above, reminds me of all the engineers I've trained, who were first year grads, some got it, dug in and learned why themselves when pointed in the right direction. Others never made it past their 90 day probation period, they did not want to put in the effort to learn how to figure things out on their own, they just wanted to be given the answers.
jkrm
Posts: 116
Joined: Wed Oct 08, 2008 8:20 am

Re: Retiree Portfolio Model

Post by jkrm »

Zephavest wrote: Tue Mar 05, 2019 6:38 am
Bongleur wrote: Tue Mar 05, 2019 5:52 am Excel 2011 MAC help is not working
Dunno why DELETE isn't working for my dup post.
Why does it think that one of us is going to die in 3 years?
FYI the choice popup is miniscule for this:
Shouldn't choosing 'i" flag an error if you are Not Using the "itemized deductions" ?
at Ages & Settings, END AGE could be "Death Age."But now that I think of it, this is the ONLY SWR calculator that accounts for the death of one spouse !
I'm not complaining here, I really want to help make it more user friendly.
don't understand error msg "Error: exclude amount must be less distributed amount"
& gave some suggestions to reduce confusion.
Using the "who dies first" option requires some thought.
The "average returns calculator" does not DO anything. It does not feed into any calculations. But it has the appearance that it might. Confusing.
There are a lot of these sorts of segments that are merely informational, not affecting spreadsheet results. Can you give them all a unique identifier, maybe surrounding them with a color, or putting them in italics, or titling them all with the prefix "INFO" ?
BTW I used to be famous for "breaking" software because I always do things the writer did not anticipate.
Bongleur,

All the things you are asking, a mental train of thought items, like this is piece of software you personally paid for and want it rewritten so YOU understand it. You can see how many years this thread has been running and only at page 13, at the rate you are going you will have another 13 pages in a few months. This software IS for SOPHISTICATED users who use it, as thousands have, and come to Understand it and work with what they have, not how they think it should be. Your questions continue to show your not understanding the big picture of the tool and are not taking time to figure it out on your own, meaning months of use. It has been pointed out this is a complicated tool only intended for advanced users, you really need to use i-ORP, the simple, free Optimal Retirement Planner: https://www.i-orp.com/bequest/index.html If that is to advanced for you then you should really consider consulting a CPA for your needs.

I spent my career in engineering, I have written and read countless software test reports. Yours, all the posts above, reminds me of all the engineers I've trained, who were first year grads, some got it, dug in and learned why themselves when pointed in the right direction. Others never made it past their 90 day probation period, they did not want to put in the effort to learn how to figure things out on their own, they just wanted to be given the answers.
Having read most of the posts by Bongleur, I agree completely with you, Zepavest, and could not have said it better myself. This workbook was something that Bigfoot48 created for his own use many years ago, and shared with the Bogleheads community for feedback and to make a contribution. He says this in the first post in this thread: "So, as my personal model, use it at your own risk. It may help you or it may not. Your situation may be more complex and the model be of only limited value. Your data may cause it to make bad calculations. It may indicate a conversion advantage because you used a 12% earnings rate. Lots of things can be indicated from the data entered, so use it as a start in your conversion decision, or SS benefits decision, and when in doubt, create a model!"

This continues to be true, though BigFoot48 has continued to maintain and improve it through the years. He has been extremely helpful when people did not understand the inputs or the results. When errors are found he's corrected them when possible within the limits of Excel and of the way he chose to build the model (in my case, I think I have found two "errors" - one that he corrected, and another that he gently pointed out was my own misunderstanding).

The important point is that this is not a piece of commercial software that is being sold for profit, and that thus should have a simple interface with an extensive help system. It's a guy's personal workbook that he's been kind enough to share with us and add features to over the years. There are many other options out there for people who want a more polished product. I have used Maxifi (they have an extensive online help system, a help desk, and I believe video tutorials) as well as Pralana Gold (a one or two-hundred page very detailed manual). I-ORP, which has also been recommended, is also pretty good and is free to use. RPM requires more effort to understand and use properly, but to me it has the advantage of being completely transparent - I can look at any formula I want and change it to suit my needs (and I HAVE made a couple of minor changes). As a result it is my primary tool, but I am pretty good with Excel and can certainly see why others might prefer something else.

So I will conclude by adding my great thanks and appreciation to Bigfoot48 for his creation and sharing of this model. I was working on my own when I stumbled on RPM about three or four years ago, and decided he'd saved me weeks or months of work.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

Bongleur wrote: [content removed - moderator prudent]
Look, RPM is a tool. It is not going to conform to you. It is not about how you think it should work or how you wish it would work. You need to figure out how to use it and apply to your situation. To do this, you have to have some knowledge of finance and Microsoft Excel. If you are in over your head (and it appears you might be), consider iORP which is also an excellent tool that is much easier to use.

People will help you with your questions but you need to change your attitude.
User avatar
LadyGeek
Site Admin
Posts: 95690
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Bongleur - I sent you a PM.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
sawdust60
Posts: 367
Joined: Tue Jul 17, 2018 12:06 pm

Re: Retiree Portfolio Model

Post by sawdust60 »

BigFoot48 wrote: Tue Jan 01, 2019 6:55 am ...
re: version 19.1 available March 1, 2019.
...
Ages that Medicare premiums will be higher due to income-related monthly adjustment amounts (IRMAA) are now shown on the Setup page in the 5. Expenses section.
....
Thanks for the updates.
Very nice to see the alert on IRMAA with ages shown.

I would not have seen it as I manually entered my Roth conversions to stay well below the first IRMAA bracket. And the table at SETUP:J254 'Federal taxes by bracket with Roth conversion' provides a good view of the utilization of the 22% tax bracket.

So I noticed the reason for the alert was in the two years following spouse's death. Doesn't the change in filing status also need to be a 2-year lag for IRMAA?
Example on sheet: Tax Tables
Current Formula AC34 = AC96
Revision: AC34 = AA96
-- but what about columns F & G: F=F and G=F ?
_______________________________________

You might check the formula for Medicare MAGI; I don't think it should include untaxed SS income.

https://www.ssa.gov/pubs/EN-05-10536.pdf
"Your MAGI is your total adjusted gross income and tax-exempt interest income."
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sawdust60 wrote: Wed Mar 06, 2019 3:34 pm Doesn't the change in filing status also need to be a 2-year lag for IRMAA?

You might check the formula for Medicare MAGI; I don't think it should include untaxed SS income.
Thanks for reporting those items. I will check it out.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sawdust60 wrote: Wed Mar 06, 2019 3:34 pm to be a 2-year lag for IRMAA?
You might check the formula for Medicare MAGI; I don't think it should include untaxed SS income.
I have updated the model v19.1 for both of your suggestions. The non-taxable SS income impacts two Medicare premium calculations, but not the surcharge item as I now know. And a two-year lag on the surcharge estimate has been added after the death of a spouse.

Most users will not need to download this update as the Medicare surcharge forecast is not used in any model calculations and serves just as a warning for a possible future expense.

Thanks for reporting these problems.

UPDATE: March 10: Fixed ACA calculation to still include non-taxed benefits.
Last edited by BigFoot48 on Sun Mar 10, 2019 12:50 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
rts58
Posts: 88
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 »

Hi,

I'm trying to use the model to help me understand the trade off between taking LT capital gains in my trading account versus rolling IRA to Roth. I've retired early and am funding that through my taxable trading account until I start receiving a pension at 65 and SS at 70. The model has helped me to set the Roth conversion levels for the next few years while I have no earned income. I've been selling bonds/mm as needed but this year my portfolio allocation needs to reduce stocks. So I am considering cutting my conversion by half or in full and instead recognize some LT gains instead. How can I use the model to understand the impact?

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

rts58 wrote: Fri Mar 08, 2019 5:17 pm Hi,

I'm trying to use the model to help me understand the trade off between taking LT capital gains in my trading account versus rolling IRA to Roth. I've retired early and am funding that through my taxable trading account until I start receiving a pension at 65 and SS at 70. The model has helped me to set the Roth conversion levels for the next few years while I have no earned income. I've been selling bonds/mm as needed but this year my portfolio allocation needs to reduce stocks. So I am considering cutting my conversion by half or in full and instead recognize some LT gains instead. How can I use the model to understand the impact?

Thanks!
The model doesn't do a detailed calculation of LT gains and taxation of them, but rather has a somewhat crude adjustment to taxable earnings to approximate the benefit of lower LT gains and capital gains rates. I would suggest you use RPM for the broad conversion picture, then do a detailed calculation for the current and future years using other tools. Other users may want to chime in on this.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
FiveK
Posts: 15742
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

BigFoot48 wrote: Fri Mar 08, 2019 5:28 pm
rts58 wrote: Fri Mar 08, 2019 5:17 pm Hi,

I'm trying to use the model to help me understand the trade off between taking LT capital gains in my trading account versus rolling IRA to Roth. I've retired early and am funding that through my taxable trading account until I start receiving a pension at 65 and SS at 70. The model has helped me to set the Roth conversion levels for the next few years while I have no earned income. I've been selling bonds/mm as needed but this year my portfolio allocation needs to reduce stocks. So I am considering cutting my conversion by half or in full and instead recognize some LT gains instead. How can I use the model to understand the impact?

Thanks!
The model doesn't do a detailed calculation of LT gains and taxation of them, but rather has a somewhat crude adjustment to taxable earnings to approximate the benefit of lower LT gains and capital gains rates. I would suggest you use RPM for the broad conversion picture, then do a detailed calculation for the current and future years using other tools. Other users may want to chime in on this.
There is the '0% LTCG or t->R' tab in the personal finance toolbox spreadsheet that takes a stab at what I think rts58 is after.
rts58
Posts: 88
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 »

BigFoot48 wrote: Fri Mar 08, 2019 5:28 pm The model doesn't do a detailed calculation of LT gains and taxation of them, but rather has a somewhat crude adjustment to taxable earnings to approximate the benefit of lower LT gains and capital gains rates. I would suggest you use RPM for the broad conversion picture, then do a detailed calculation for the current and future years using other tools. Other users may want to chime in on this.
Thanks, it has been very helpful with the broad picture, I was struggling with how to take it down to a little more detail.
FiveK wrote: Fri Mar 08, 2019 5:46 pm There is the '0% LTCG or t->R' tab in the personal finance toolbox spreadsheet that takes a stab at what I think rts58 is after.
Thank you, I'll take a look at this!
sawdust60
Posts: 367
Joined: Tue Jul 17, 2018 12:06 pm

Re: Retiree Portfolio Model

Post by sawdust60 »

BigFoot48 wrote: Thu Mar 07, 2019 9:50 am
sawdust60 wrote: Wed Mar 06, 2019 3:34 pm ...
...The non-taxable SS income impacts two Medicare premium calculations...
Can you tell me how non-taxable SS impacts Medicare premiums?
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sawdust60 wrote: Fri Mar 08, 2019 9:19 pm
Can you tell me how non-taxable SS impacts Medicare premiums?
https://www.everycrsreport.com/reports/ ... f529460434
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
sawdust60
Posts: 367
Joined: Tue Jul 17, 2018 12:06 pm

Re: Retiree Portfolio Model

Post by sawdust60 »

BigFoot48 wrote: Fri Mar 08, 2019 9:23 pm
...The non-taxable SS income impacts two Medicare premium calculations...
sawdust60 wrote: Fri Mar 08, 2019 9:19 pm
Can you tell me how non-taxable SS impacts Medicare premiums?
https://www.everycrsreport.com/reports/ ... f529460434
Thanks for the link -- two health care programs, but not Medicare.

Table 1 shows that non-taxable SS is added to AGI, for ACA Premium Credit and Initial Medicaid Eligibility.
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

rts58 wrote: Fri Mar 08, 2019 5:17 pm I've retired early and am funding that through my taxable trading account until I start receiving a pension at 65 and SS at 70. The model has helped me to set the Roth conversion levels for the next few years while I have no earned income. I've been selling bonds/mm as needed but this year my portfolio allocation needs to reduce stocks. So I am considering cutting my conversion by half or in full and instead recognize some LT gains instead. How can I use the model to understand the impact?
I'm in the same boat, retired age 61 planned Social Security start date age 69. Because of the current low tax brackets that are only guaranteed until 2025, I'm maxing out the 12% bracket yearly to take the gift while it is available for Roth Conversions. If you can minimize your taxable account gains by selective holdings, taking what taxable distributions you must, you still may want to try to maximize the Roth Conversions until 2025, and see what happens with the tax law then.

Here is my retirement cash flow approach that may work for other Bogleheads, based on my insight gained from running multiple scenarios in RPM. Regardless of my annual cash flow needs, pre-Pension and pre-Social Security, I max out my Roth Conversions as it allows my to pay the lowest marginal tax rate. Then I withdraw annual income, as needed, from the Roth account. The remaining growing Roth balance is now sheltered for my heirs, so they don't get hit with large inherited IRA taxes for years on end.

For those that say, wait, what cash are you using to pay the taxes on the Roth Conversion? For the first two years of retirement it came from the taxable account, but as that has spent down I now pay for it out the Roth Distributions I take for annual income. Now I hear a number of you saying, but you can't do that, you must follow the "wise tale rule of thumb" that you can only do Roth Conversions if you have separate cash to pay the taxes. That it a misconception, If I did not do a Roth Conversion, and just took IRA Distributions for annual income, where is the cash coming from to pay the taxes on that? You can see I'm paying the exact same taxes on the money, regardless whether the 1099-R says "IRA Distribution" or "Roth Conversion", same balance, same taxes due.
rts58
Posts: 88
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 »

Zephavest wrote: Sat Mar 09, 2019 6:46 am Here is my retirement cash flow approach that may work for other Bogleheads, based on my insight gained from running multiple scenarios in RPM. Regardless of my annual cash flow needs, pre-Pension and pre-Social Security, I max out my Roth Conversions as it allows my to pay the lowest marginal tax rate. Then I withdraw annual income, as needed, from the Roth account. The remaining growing Roth balance is now sheltered for my heirs, so they don't get hit with large inherited IRA taxes for years on end.
Thanks for your comments. I have tried to keep my most aggressive growth investments in the Roth accounts, so I haven't really considered pulling cash back out. Bonds are mostly in the IRA's and some in the trading. I could pull more from the IRA's but that will raise my marginal tax rate into 22%, the recognizing of capital gains from the trading account would be at 15%. I really need to sell down a stock position, I am just trying to fully understand the impact of the alternatives.

For now I'm messing with the tool FiveK suggested.

Thanks
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

Don't know, but Mr.Bongleur seemed to be saying his career was with manuals FWIW.

I seem to have all the data in the right places, however, the vocabulary is sufficiently opaque that I'm doubting it could be meaningfully used, in my case, I went over and plugged things into the i-ORP , it seems to have paragraphs that are available to attempt to decypher the lingo, but is a bit apples and oranges , I'd prefer to use the RPM some more, but the cell popups , seem a bit of a dead-end ....

maybe someday there might be a glossary or even just a small manual ...... and might be wider adoption of BFs work ...... :happy
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

Question on how RPM calculates the WITHDRAWAL RATE:

To calculate WITHDRAWAL RATE, RPM is adding: TAXABLE INCOME (X 1-taxable_tax_rate) + PENSIONS/ANNUITIES/WAGES + SOCIAL SECURITY - LIVING EXPENSES - FEDERAL & STATE TAXES and dividing the sum by ACCOUNT BALANCE AT START OF YEAR.

So my question is: by adjusting TAXABLE INCOME for taxes, and then adjusting for FEDERAL & STATE TAXES, are some taxes being adjusted for twice? Presumably the FEDERAL & STATE TAXES include the adjustment that is made on TAXABLE INCOME.

I am wondering if perhaps the TAXABLE INCOME should not be multiplied by (1-taxable_tax_rate)?

I hope I explained the question clearly.

Another related question: Presumably taxable dividends, capital gain distributions and interest are included in taxable income. Wouldn't thes be considered as "withdrawals?" Is RPM treating them as such?

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

munemaker wrote: Wed Apr 10, 2019 8:22 pm Question on how RPM calculates the WITHDRAWAL RATE:
The Withdrawal Rate is shown on the Results page. The Taxable Account income is being adjusted by the taxable account adjustment factor set on the Setup page, not the tax rate. So the withdrawal rate calculation is including the portion of the taxable account earnings that are estimated to be actually distributed, plus income sources less expenses including taxes paid, to estimate net withdrawals and calculate an estimated withdrawal rate.
Last edited by BigFoot48 on Thu Apr 11, 2019 2:01 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
smitcat
Posts: 13300
Joined: Mon Nov 07, 2016 9:51 am

Re: Retiree Portfolio Model

Post by smitcat »

stvyreb wrote: Sat Mar 09, 2019 7:33 pm Don't know, but Mr.Bongleur seemed to be saying his career was with manuals FWIW.

I seem to have all the data in the right places, however, the vocabulary is sufficiently opaque that I'm doubting it could be meaningfully used, in my case, I went over and plugged things into the i-ORP , it seems to have paragraphs that are available to attempt to decypher the lingo, but is a bit apples and oranges , I'd prefer to use the RPM some more, but the cell popups , seem a bit of a dead-end ....

maybe someday there might be a glossary or even just a small manual ...... and might be wider adoption of BFs work ...... :happy
We have no problems using the IORP or RPM.
It did take some time to work with the RPM to understand the inputs and the very helpful outputs.
In our case It was well worth the time to get the RPM up and running - great insight on how to maximize spendable money in retirement..
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

BigFoot48 wrote: Thu Apr 11, 2019 8:24 am
munemaker wrote: Wed Apr 10, 2019 8:22 pm Question on how RPM calculates the WITHDRAWAL RATE:
The Withdrawal Rate is shown on the Results page. The Taxable Account income is being adjusted by the taxable account adjustment factor set on the Setup page, not the tax rate. So the withdrawal rate calculation is including the portion of the taxable account earnings that are estimated to be actually distributed, plus income sources less expenses including taxes paid to estimate net withdrawals and calculate an estimated withdrawal rate.
Now I see! Thanks for explaining.
User avatar
munemaker
Posts: 4338
Joined: Sat Jan 18, 2014 5:14 pm

Re: Retiree Portfolio Model

Post by munemaker »

smitcat wrote: Thu Apr 11, 2019 8:30 am We have no problems using the IORP or RPM.
It did take some time to work with the RPM to understand the inputs and the very helpful outputs.
In our case It was well worth the time to get the RPM up and running - great insight on how to maximize spendable money in retirement..
+1

I agree completely.
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

Can someone explain why:

RPM is telling me I have "Alert! Portfolio has negative beginning yr balances in one or more accounts for 39 years" messages?

In the first section, are you supposed to put the current year, or the year you expect to retire? If the later, you would then have to guess as to future balances?

Similarly, in the Portfolio Balances section, are you supposed to put CURRENT balances, or projected balances upon retirement?

Confused.

Also in the IRA Contribution section, it says (in the note) to "enter the amounts and factors to schedule contributions to IRA 1. These amounts are withdrawn from the Taxable account."

This is perhaps part of the problem. Isn't this where you enter your annual savings from your income? Why would you be making contributions from a taxable account?

This seems like a very thorough tool but man...it is complicated. :?
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Sat Apr 13, 2019 8:02 pm Can someone explain why:

RPM is telling me I have "Alert! Portfolio has negative beginning yr balances in one or more accounts for 39 years" messages?

In the first section, are you supposed to put the current year, or the year you expect to retire? If the later, you would then have to guess as to future balances?

Similarly, in the Portfolio Balances section, are you supposed to put CURRENT balances, or projected balances upon retirement?

Confused.

Also in the IRA Contribution section, it says (in the note) to "enter the amounts and factors to schedule contributions to IRA 1. These amounts are withdrawn from the Taxable account."

This is perhaps part of the problem. Isn't this where you enter your annual savings from your income? Why would you be making contributions from a taxable account?

This seems like a very thorough tool but man...it is complicated. :?
Users have said that! It's designed to use current account balances, but if someone wants to put their guess in for what they will be in 10 years that could be done.

You will get a warning on negative balances if withdrawals exceed starting balances plus income and earnings in any year. Go to the Summary or Results page to see why you are getting negative balances. Also, start by inserting current balances and appropriate earning rates - there should be no negative balances. Add income. Larger balances should result. Then add expenses at a level to avoid creating negative balances.

Income from salaries and social security are deposited into the taxable account. Contributions to an IRA are made from the taxable account. This keeps the math simple.

Good luck1
Last edited by BigFoot48 on Sun Apr 14, 2019 7:29 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Sat Apr 13, 2019 8:14 pm
Admiral wrote: Sat Apr 13, 2019 8:02 pm Can someone explain why:

RPM is telling me I have "Alert! Portfolio has negative beginning yr balances in one or more accounts for 39 years" messages?

In the first section, are you supposed to put the current year, or the year you expect to retire? If the later, you would then have to guess as to future balances?

Similarly, in the Portfolio Balances section, are you supposed to put CURRENT balances, or projected balances upon retirement?

Confused.

Also in the IRA Contribution section, it says (in the note) to "enter the amounts and factors to schedule contributions to IRA 1. These amounts are withdrawn from the Taxable account."

This is perhaps part of the problem. Isn't this where you enter your annual savings from your income? Why would you be making contributions from a taxable account?

This seems like a very thorough tool but man...it is complicated. :?
Users have said that! It's designed to use current account balances, but if someone wants to put their guess in for what they will be in 10 years that could be done.

You will get a warning on negative balances if withdrawals exceed starting balances plus income and earnings in any year. Go to the summary or detail pages to see why you are getting negative balances. Also, start by inserting current balances and appropriate earning rates - there should be no negative balances. Add income. Larger balances should result. Then add expenses at a level to avoid creating negative balances.

Income from salaries and social security are deposited into the taxable account. Contributions to an IRA are then made from those taxable account balances. This keeps the math simple.

Good luck1
Thanks. I looked at the Summary so that was helpful. I guess my confusion is it seems to be withdrawing my current living expenses each year from my taxable account, but I don't see any money going INTO the taxable account, even though I have filled out the entire income section (these are the total listed at the end) which are correct.

Average Rate 17.0%
Marginal Rate 24.0%
Capital Gains Rate 15.0%

Is RPM supposed to take this data and auto-populate some cells? Even when I change the cell for "Annual" under the Expenses section to "0" I'm still getting a warning that "Base Portfolio : Negative Balances for 15 years." There's also an "Alert: Base Case has negative balance for 15 years."

So clearly I am doing something very wrong.
Post Reply