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: 2676
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Apr 14, 2019 7:43 am

Have you put your current taxable account and IRA balances and current age in? You might also examine the example data that comes with the model for what data a typical user might input.

Those rates are the Fed tax rates and I don't think not relevant to your problem.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sun Apr 14, 2019 8:01 am

BigFoot48 wrote:
Sun Apr 14, 2019 7:43 am
Have you put your current taxable account and IRA balances and current age in? You might also examine the example data that comes with the model for what data a typical user might input.

Those rates are the Fed tax rates and I don't think not relevant to your problem.
Yes. Perhaps this will be helpful. As you can see the other accounts are growing normally while the taxable account goes negative immediately. Sorry the columns don't line up well

Code: Select all

Account Balances		Rates	Totals	2019	2020	2021	2022	2023	2024	2025	2026

Code: Select all

Taxable			(284,300)	 80,000 	 8,200 	 (70,100)	 (155,700)	 (249,200)	 (351,400)	 (462,900)	 (584,400)
IRA ^	.		2,764,200 	 555,000 	 617,900 	 685,000 	 756,600 	 833,000 	 914,500 	 1,001,400 	 1,094,100 
IRA, inherited ^	.		6,300 	0 	 -   	 -   	 -   	 -   	 -   	 -   	 -   
Roth IRA ^	.		2,033,400 	 140,000 	 150,500 	 161,800 	 173,900 	 186,900 	 200,900 	 216,000 	 232,200 
Roth IRA, conversion			1,083,700 	0 	 -   	 -   	 -   	 -   	 -   	 -   	 -   
Total	Negative! Adjust factors.		5,603,300 	 775,000 	 776,600 	 776,700 	 774,800 	 770,700 	 764,000 	 754,500 	 741,900 
Negative balances in one or more years. Adjust setup factors.				 	 	Negative	Negative	Negative	Negative	Negative	Negative

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Apr 14, 2019 8:13 am

Admiral wrote:
Sun Apr 14, 2019 8:01 am
Yes. Perhaps this will be helpful. As you can see the other accounts are growing normally while the taxable account goes negative immediately. Sorry the columns don't line up well
I'll suspect you don't have enough income or withdrawals from your IRAs to keep the taxable account positive. I'll send you a PM.

SOLUTION: Admiral solved his issue by using the Retirement Income section to model his working years salaries. An update will improve the labels in the section to nake it clear it can be used for any annual income source.

200,000 views
Last edited by BigFoot48 on Sun Apr 14, 2019 2:45 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sun Apr 14, 2019 8:14 am

I think I see part of the problem. It's not populating the income section with the info I entered. So even though the AGI is correct in the income tax section, that info is not being populated to any other cell that I can see.

User avatar
Harry Livermore
Posts: 15
Joined: Thu Apr 04, 2019 5:32 am

Re: Retiree Portfolio Model

Post by Harry Livermore » Sun Apr 14, 2019 9:32 am

I'm eager to tackle this very thorough-looking spreadsheet. Many thanks to BigFoot48 for creating and updating it. It looks amazing, and quite a value (seeing as it costs $0!!!)
I have a question for Mac users in the group. I generally do not download random spreadsheets from the internet, and have Excel set to disable macros since this has been a method by which Mac users have been infected by viruses in the past. This spreadsheet uses macros.
So, given my irrational fear, I just want to confirm that other Mac users have opened this spreadsheet, run the macros, and been OK? I'm sure the answer is yes, but wanted to ask the group. I know this is a well-policed site, probably one of the best and strictest public forums on the internet. And I note that the topic author has a long tenure, many posts, and seems well-respected, so this is not meant to disparage BigFoot48 in any way. Just some due diligence and paranoia on my part.
Cheers

User avatar
Zephavest
Posts: 48
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Sun Apr 14, 2019 9:43 am

Harry Livermore wrote:
Sun Apr 14, 2019 9:32 am
I know this is a well-policed site, probably one of the best and strictest public forums on the internet. And I note that the topic author has a long tenure, many posts, and seems well-respected.
You answered your own questions, I've been using it for years, it's the real thing, park the paranoia!

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Tue Apr 16, 2019 11:34 am

Is anyone aware of why/how come RPM reports a marginal tax rate of 24% (which is correct) in the Simple Tax Calculator section, but then in the bar chart to the left, it's showing 35% marginal for the same year?

I cannot figure out where any additional income is being pulled from. The chart under "Federal Taxes By Bracket" is also incorrect, as it shows $69,700 in total federal taxes, which does not match what appears in the Simple Tax Calculator section. We are well below 32% and will be for the next several years, yet the chart shows 35% until 2029.

I've made sure I don't have any Roth conversions or anything, and the tax draw on my taxable portfolio is low (and anyway that's accounted for in the Calculator section). I checked the dates on the SS and pension sections as well and they are connect.

Does this somehow calculated imputed tax on tax-advantaged savings (top of line deductions)?

That still would not make sense as the marginal rate is not correctly reported.

User avatar
munemaker
Posts: 4092
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Tue Apr 16, 2019 1:09 pm

Admiral wrote:
Tue Apr 16, 2019 11:34 am
Is anyone aware of why/how come RPM reports a marginal tax rate of 24% (which is correct) in the Simple Tax Calculator section, but then in the bar chart to the left, it's showing 35% marginal for the same year?

I cannot figure out where any additional income is being pulled from. The chart under "Federal Taxes By Bracket" is also incorrect, as it shows $69,700 in total federal taxes, which does not match what appears in the Simple Tax Calculator section. We are well below 32% and will be for the next several years, yet the chart shows 35% until 2029.

I've made sure I don't have any Roth conversions or anything, and the tax draw on my taxable portfolio is low (and anyway that's accounted for in the Calculator section). I checked the dates on the SS and pension sections as well and they are connect.

Does this somehow calculated imputed tax on tax-advantaged savings (top of line deductions)?

That still would not make sense as the marginal rate is not correctly reported.
You do realize that the "Simple Tax Calculator" is not linked or tied to the rest of RPM, right? It is just a tool and operates independently as a simple calculator. You need to make sure the inputs in sections 1 through 10 agree with the simple calculator if you expect to see the same results.

Aside from that, I cannot find a graph labeled "Federal Taxes by Bracket." Can you state what page and cell location this graph is at? I see graphs labeled "Average Federal Tax Rates" and "Marginal Federal Tax Rates" on the SETUP page but no graph labeled "Federal Taxes by Bracket."

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Tue Apr 16, 2019 1:21 pm

munemaker wrote:
Tue Apr 16, 2019 1:09 pm
Admiral wrote:
Tue Apr 16, 2019 11:34 am
Is anyone aware of why/how come RPM reports a marginal tax rate of 24% (which is correct) in the Simple Tax Calculator section, but then in the bar chart to the left, it's showing 35% marginal for the same year?

I cannot figure out where any additional income is being pulled from. The chart under "Federal Taxes By Bracket" is also incorrect, as it shows $69,700 in total federal taxes, which does not match what appears in the Simple Tax Calculator section. We are well below 32% and will be for the next several years, yet the chart shows 35% until 2029.

I've made sure I don't have any Roth conversions or anything, and the tax draw on my taxable portfolio is low (and anyway that's accounted for in the Calculator section). I checked the dates on the SS and pension sections as well and they are connect.

Does this somehow calculated imputed tax on tax-advantaged savings (top of line deductions)?

That still would not make sense as the marginal rate is not correctly reported.
You do realize that the "Simple Tax Calculator" is not linked or tied to the rest of RPM, right?
It is just a tool and operates independently as a simple calculator. You need to make sure the inputs in sections 1 through 10 agree with the simple calculator if you expect to see the same results.

Aside from that, I cannot find a graph labeled "Federal Taxes by Bracket." Can you state what page and cell location this graph is at? I see graphs labeled "Average Federal Tax Rates" and "Marginal Federal Tax Rates" on the SETUP page but no graph labeled "Federal Taxes by Bracket."
You do realize that the "Simple Tax Calculator" is not linked or tied to the rest of RPM, right?
Eh.... no. I did not realize that.

In the current version on the Setup Tab if you scroll down below Sec 10 (Optional: Roth Conversions) there is a section called "Federal Taxes By Bracket." It starts at Cell B300.

There is also a graphical representation (bar graph) just below Cell J210 that shows Marginal Federal Tax Rates for each year of the plan. (It's to the right of section 8. Income Taxes)

I think I figured out the problem, tho. The defaults had the tax rates changing from married to single in the first plan year, instead of upon the death of the spouse. Once I corrected that, the rates fell to what they should be. That said, it's still showing the tax as 10k more than I actually owed so I need to figure that out.

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sat Apr 20, 2019 7:18 am

Can someone point me to where the expected rates of inflation are entered in RPM for investments?

I see that there is a field to adjust inflation in Section 5. Income. Does that number also apply to the entire model? In other words are the percentages in Sec 3 Return Rates and Allocations real rates, after the % in sec 5 is applied, or nominal? Or is there some internal inflation calculation that cannot be adjusted?

The results are obviously in inflated numbers, so I'm just trying to understand what happens if inflation is higher or lower.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Apr 20, 2019 8:01 am

Admiral wrote:
Sat Apr 20, 2019 7:18 am
Can someone point me to where the expected rates of inflation are entered in RPM for investments?

I see that there is a field to adjust inflation in Section 5. Income. Does that number also apply to the entire model? In other words are the percentages in Sec 3 Return Rates and Allocations real rates, after the % in sec 5 is applied, or nominal? Or is there some internal inflation calculation that cannot be adjusted?

The results are obviously in inflated numbers, so I'm just trying to understand what happens if inflation is higher or lower.
The model is designed to model real life financial results. Investments don't go up in real terms and expenses don't change in real terms. However, if someone wants to model future years using real rates it could be done but you're on your own in determining how to do that. I have no interest in supporting such a forecast.

Section 3: Return Rates are nominal. If you think stocks will go up 5% this year and in future years enter 5%.
Section 4: Social Security has an estimate of the actual COLA rate used by SS. Note: A model using real rates would have the SS benefits in 2049 the same as 2019. Is that beneficial?
Section 5: Expenses has the COLA inflation rate for expenses. Only this one rate is used for all years in forecasting living expenses to be incurred over the selected model periods.
Section 7: Special Events are entered at the future estimated values. A Future Value Calculator is provided to assist in determining those amounts. NOTE: The model has a number of Calculators on the Setup page. Any amounts shown on them are not used in the model unless the user inputs them somewhere.
Section 8: Income Taxes. Current year income tax brackets, standard deductions are escalsted based on a historic average of the change used by the IRS and this rate is on the Tax Tables page.

Cell comments are provided detailing what every setting does and how it is used. I recommend they all be read when starting to use the model.
Last edited by BigFoot48 on Sat Apr 20, 2019 5:27 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Admiral
Posts: 1880
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sat Apr 20, 2019 8:12 am

BigFoot48 wrote:
Sat Apr 20, 2019 8:01 am
Admiral wrote:
Sat Apr 20, 2019 7:18 am
Can someone point me to where the expected rates of inflation are entered in RPM for investments?

I see that there is a field to adjust inflation in Section 5. Income. Does that number also apply to the entire model? In other words are the percentages in Sec 3 Return Rates and Allocations real rates, after the % in sec 5 is applied, or nominal? Or is there some internal inflation calculation that cannot be adjusted?

The results are obviously in inflated numbers, so I'm just trying to understand what happens if inflation is higher or lower.
The model is designed to model real life financial results. Investments don't go up in real terms and expenses don't change in real terms. However, if someone wants to model future years using real rates it could be done but you're on your own in determining how to do that. I have no interest in supporting such a forecast.

Section 3: Return Rates are nominal. If you think stocks will go up 5% this year enter 5%.
Section 4: Social Security has an estimate of the actual COLA rate used by SS.
Section 5: Expenses has the inflation rate for expenses. Only this one rate is used for all years.
Section 7: Special Events are entered at the future estimated values. A Future Value Calculator is provided to assist in determining those amounts. NOTE: The model has a number of Calculators on the Setup page. Any amounts shown on them are not used in the model unless the user inputs them somewhere.
Section 8: Income Taxes. Current year income tax brackets, standard deductions are escalsted based on a historic average of the change used by the IRS and this rate is on the Tax Tables page.
Thanks for that explanation. My question(s) is how to interpret the results in RPM vs a MC simulator like Flexible Retirement Planner which gives future results in current dollars and applies an expected (i.e. projected) inflation rate (with an input for standard deviation).

So, the question is how to square a future portfolio value in RPM that is $12m in 30 years vs. the same portfolio valued at (for example) $5m in 30 years but given in current dollars.

Does this make sense?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Apr 20, 2019 8:21 am

Admiral wrote:
Sat Apr 20, 2019 8:12 am

Thanks for that explanation. My question(s) is how to interpret the results in RPM vs a MC simulator like Flexible Retirement Planner which gives future results in current dollars and applies an expected (i.e. projected) inflation rate (with an input for standard deviation).

So, the question is how to square a future portfolio value in RPM that is $12m in 30 years vs. the same portfolio valued at (for example) $5m in 30 years but given in current dollars.

Does this make sense?
Yes. I recommend you use iORP which is also a nominal forecaster and agrees closely to RPM results if you want a comparison.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Apr 20, 2019 7:10 pm

Version 19.1 has been updated today for a calculation error on the Summary page, found and reported by an anonymous user. The Portfolio Balance at Year End on the Summary page was not being calculated correctly (expenses were added rather than subtracted due to not being fixed when expenses were changed to positive numbers from negative numbers in a previous update).

Probably not necessary for most users to update as Portfolio Balances at Start of Year were correct and all balances on other pages are correct.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Big Dog » Sat Apr 20, 2019 7:39 pm

this model is incredible, bigfoot.

I've looked at it several times in the past, but was always scared off by the complexity. But over the past two weekends, I've been diligently plugging and playing. Wow, just wow. Thanks a bunch.

btw: obviously, we all download the spreadsheet and run it locally. But when there's an updated ver published, do you have any suggestions for an easy way to copy all personal entries onto the rev?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Apr 20, 2019 9:33 pm

Big Dog wrote:
Sat Apr 20, 2019 7:39 pm
this model is incredible, bigfoot.

I've looked at it several times in the past, but was always scared off by the complexity. But over the past two weekends, I've been diligently plugging and playing. Wow, just wow. Thanks a bunch.

btw: obviously, we all download the spreadsheet and run it locally. But when there's an updated ver published, do you have any suggestions for an easy way to copy all personal entries onto the rev?
Thanks very much! The easiest way to load data into a new version is to use the clear and load macros at the top of the Setup page. That's what I use. If you can't or don't want to use it, then the next best way is to load both the previous version and the new one and use the View Side By Side command in the View menu. This allows an easier way to replace the Example data with your data from an earlier model. Just be sure to zero or blank-out any entries you don't use.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

User avatar
changingtimes
Posts: 125
Joined: Mon Jul 24, 2017 9:28 am

Re: Retiree Portfolio Model

Post by changingtimes » Sat Apr 20, 2019 9:45 pm

Bigfoot, can you give me some hints on the best way to enter the data for my situation? I can't seem to quite get there.

I'm a widow. My husband died at 55, so his SS (and mine) hadn't started yet. I now have the inherited IRA (which I can access now, albeit as income, but without penalty), my 401k, my Roth, my personal IRA, my taxable, his pension already started, my pension at 65, and likely survivor SS at 60 before starting mine at 70. And I'm still working for a few more years.

A lot of moving parts. 😀 It may be a little too outside the norm, but I thought I'd ask.

Great job on it.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Apr 21, 2019 10:09 am

changingtimes wrote:
Sat Apr 20, 2019 9:45 pm
Bigfoot, can you give me some hints on the best way to enter the data for my situation? I can't seem to quite get there.

I'm a widow. My husband died at 55, so his SS (and mine) hadn't started yet. I now have the inherited IRA (which I can access now, albeit as income, but without penalty), my 401k, my Roth, my personal IRA, my taxable, his pension already started, my pension at 65, and likely survivor SS at 60 before starting mine at 70. And I'm still working for a few more years.
I recommend new users leave the example data in place and start by reviewing all the factors entered in the 10 sections on the Setup page to get a general idea of what portfolio and income items are modeled.

Then start at section 1. Ages and Year Factors and replace the example data with yours. Read the cell comments for the section and each entry if its not clear what the factors are. Ignore any error messages that might appear until finished as they're false-positives and a result of a mixture of your data (i.e a single person) and the example data (a married couple).

Continue on with each section, replacing or clearing data not applicable to your situation. The example data uses every factor to test the model and most users likely won't use them all. Replace factors not applicable to you with zeros or blanks, but again read the cell comments for instructions.

The last two sections are optional and typically can be ignored unless the user is testing Roth conversion amounts as they're activated by settings at the top of the page and are turned off in the example data.

When finished users might see error messages about negative taxable account balances if they have too little income or IRA withdrawals and/or too large expenses to keep the taxable account balance positive. Adjust these factors as necessary to remove error messages.

Good luck!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Post Reply