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: 2783
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; 14-time loser

Admiral
Posts: 2847
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: 2783
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; 14-time loser

Admiral
Posts: 2847
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: 229
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: 89
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: 2847
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: 4153
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: 2847
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: 2847
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: 2783
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; 14-time loser

Admiral
Posts: 2847
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: 2783
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; 14-time loser

User avatar
Topic Author
BigFoot48
Posts: 2783
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; 14-time loser

Big Dog
Posts: 1724
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: 2783
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; 14-time loser

User avatar
changingtimes
Posts: 242
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: 2783
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; 14-time loser

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

Re: Retiree Portfolio Model

Post by 808 » Sun Apr 21, 2019 11:12 pm

What is the reason why FRA_You_Alt is hard-coded as 66 and not equal to +FRA_You?

I can unprotect and change but was wondering what your rationale was for hard-coding the value.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Apr 22, 2019 1:11 am

808 wrote:
Sun Apr 21, 2019 11:12 pm
What is the reason why FRA_You_Alt is hard-coded as 66 and not equal to +FRA_You?

I can unprotect and change but was wondering what your rationale was for hard-coding the value.
Just to save the user some work. The FRA is the FRA and its already been entered. OOPS: wrong, see below
Last edited by BigFoot48 on Tue May 21, 2019 8:21 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by 808 » Mon Apr 22, 2019 1:57 am

BigFoot48 wrote:
Mon Apr 22, 2019 1:11 am
808 wrote:
Sun Apr 21, 2019 11:12 pm
What is the reason why FRA_You_Alt is hard-coded as 66 and not equal to +FRA_You?

I can unprotect and change but was wondering what your rationale was for hard-coding the value.
Just to save the user some work. The FRA is the FRA and its already been entered.
In my case, I enter 67 for FRA_You. FRA_You_Alt is hard-coded as 66. So, I have to manually change FRA_You_Alt to 67 for the calculations to the right of the "alternate benefits" input to reflect the correct FRA.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Apr 22, 2019 7:17 am

808 wrote:
Mon Apr 22, 2019 1:57 am
BigFoot48 wrote:
Mon Apr 22, 2019 1:11 am
808 wrote:
Sun Apr 21, 2019 11:12 pm
What is the reason why FRA_You_Alt is hard-coded as 66 and not equal to +FRA_You?

I can unprotect and change but was wondering what your rationale was for hard-coding the value.
Just to save the user some work. The FRA is the FRA and its already been entered.
In my case, I enter 67 for FRA_You. FRA_You_Alt is hard-coded as 66. So, I have to manually change FRA_You_Alt to 67 for the calculations to the right of the "alternate benefits" input to reflect the correct FRA.
You are correct sir! I see the 18.0 model did not have 66 but referenced the FRA entry. Somehow that got changed.
Anyone doing an SS comparison and having a Your FRA of 67 will need to change that value. 19.1 is being updated to make it automatic. Thanks!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Program is dipping into my ROTH account Day 1

Post by ncdad1 » Mon Apr 22, 2019 12:58 pm

I want to start by saying I am in love - with the creator and the spreadsheet. Not only Is RPM relevant to me at 60. I am also learning so much about how to organize sheets, use colors and macros, and display information. The program is very well done and educational for learning excel, tax code and retirement strategies.

My issue is I have a large 401k, and two large Roth IRAs along with a small Tax investment. I am married. My problem is the logic seems to be sourcing my income from the 401k (good, what I am doing now) along with my pension and a small amount from my Roth IRA (bad). I checked the option to draw first from the 401k and then the Roth and the 401k has plenty of funds so I don’t know why it is dipping into the Roth IRA which I hope to never touch and pass to my kids. Can you give me a hint on where to go to troubleshoot this issue, please?

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

Re: Program is dipping into my ROTH account Day 1

Post by BigFoot48 » Mon Apr 22, 2019 3:51 pm

ncdad1 wrote:
Mon Apr 22, 2019 12:58 pm
My issue is I have a large 401k, and two large Roth IRAs along with a small Tax investment. I am married. My problem is the logic seems to be sourcing my income from the 401k (good, what I am doing now) along with my pension and a small amount from my Roth IRA (bad). I checked the option to draw first from the 401k and then the Roth and the 401k has plenty of funds so I don’t know why it is dipping into the Roth IRA which I hope to never touch and pass to my kids. Can you give me a hint on where to go to troubleshoot this issue, please?
I recommend not using the auto withdrawals feature but rather decide for yourself in Section 6 the amounts and timing of the withdrawals from IRAs that will fund your expenditures.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by ncdad1 » Mon Apr 22, 2019 5:18 pm

Thank you. I apologized for being an idiot but after further study, I realized I was confusing income with earnings on the graph. Still, I like your suggestion better and will implement it

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

Re: Retiree Portfolio Model

Post by Admiral » Wed Apr 24, 2019 10:34 am

bigfoot, can you explain a bit about how the return rates are modeled? Is the entered rate of return simply multiplied, year after year, by the current balance. i.e. uninterrupted compound annual growth? That's the way it appears, but I'm not sure what's going on behind the scenes.

I realize that RPM is not a Monte Carlo simulator. At the same time, there's "average" rate over 30-40 years and then there's annual rate, which could be much greater, or much lower, than average.

Since sequence of returns is an issue when modeling, I'm just wondering if (or how) this is handled.

Thanks!

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Apr 24, 2019 10:40 am

Yes, the return rate set for each account type is applied to the average account balance for the year, year after year. So chose a rate appropriate for a long period. Some historic results are provided in the cell comments to help.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by Admiral » Wed Apr 24, 2019 1:10 pm

BigFoot48 wrote:
Wed Apr 24, 2019 10:40 am
Yes, the return rate set for each account type is applied to the average account balance for the year, year after year. So chose a rate appropriate for a long period. Some historic results are provided in the cell comments to help.
Thanks!

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

Re: Retiree Portfolio Model

Post by Admiral » Wed Apr 24, 2019 1:15 pm

BigFoot48 wrote:
Wed Apr 24, 2019 10:40 am
Yes, the return rate set for each account type is applied to the average account balance for the year, year after year. So chose a rate appropriate for a long period. Some historic results are provided in the cell comments to help.
Sorry...so the AVERAGE of previous years is used for the balance before calculating returns, or the actual amount?

IOW if I start with 100k and assume 7%, does it look like this:
Year 1: 100k
Year 2: 100k x1.07
Year 3 107k x 1.07 etc

Or is it

Year 1: 100k
Year 2: 100k x 1.07
Year 3 207 % 2 x 1.07 etc

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Apr 24, 2019 1:36 pm

I encourage you to test the numbers in the model and see the results at the top of the Details page

Year 1 start: $100,000, end $107,000
Year 2 start: $107,000, end $114,500
Year 3 start: $114,500, end $122,500

Start balance + additions - expenditures = preliminary end balance
(Start balance + preliminary end balance)/2 * earnings rate = earnings
Preliminary end balance + earnings = ending balance
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

catalina355
Posts: 283
Joined: Sun Jun 10, 2018 6:46 pm

Re: Retiree Portfolio Model

Post by catalina355 » Sun May 05, 2019 6:56 pm

Does the RPM v19.1 run correctly on macOS Numbers? Does it run correctly on macOS LibreCalc? If not what is the best approach for Macintosh users? Thank you.

rennale
Posts: 54
Joined: Sun Apr 23, 2017 6:50 am

Re: Retiree Portfolio Model

Post by rennale » Mon May 06, 2019 6:26 am

Buy a copy of Excel for Mac for $105.

https://www.amazon.com/dp/B07H4XBM1R

Anything else will cause more grief than it's worth.... (and Excel is a good product by any measure)

catalina355
Posts: 283
Joined: Sun Jun 10, 2018 6:46 pm

Re: Retiree Portfolio Model

Post by catalina355 » Mon May 06, 2019 6:35 am

rennale wrote:
Mon May 06, 2019 6:26 am
Buy a copy of Excel for Mac for $105.

https://www.amazon.com/dp/B07H4XBM1R

Anything else will cause more grief than it's worth.... (and Excel is a good product by any measure)
I really don’t want to buy MS products.

Is anyone using LibreCalc successfully on a Mac with RPM?

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

Re: Retiree Portfolio Model

Post by Admiral » Mon May 06, 2019 6:39 am

catalina355 wrote:
Mon May 06, 2019 6:35 am
rennale wrote:
Mon May 06, 2019 6:26 am
Buy a copy of Excel for Mac for $105.

https://www.amazon.com/dp/B07H4XBM1R

Anything else will cause more grief than it's worth.... (and Excel is a good product by any measure)
I really don’t want to buy MS products.

Is anyone using LibreCalc successfully on a Mac with RPM?
I use RPM on a Mac in Excel, but I also have Numbers. I will try it and see if it works and report back. If you disable the macros (which are not needed to use the file) my guess is it will work, but bigfoot will confirm.

rennale
Posts: 54
Joined: Sun Apr 23, 2017 6:50 am

Re: Retiree Portfolio Model

Post by rennale » Mon May 06, 2019 7:00 am

catalina355 wrote:
Mon May 06, 2019 6:35 am
rennale wrote:
Mon May 06, 2019 6:26 am
Buy a copy of Excel for Mac for $105.

https://www.amazon.com/dp/B07H4XBM1R

Anything else will cause more grief than it's worth.... (and Excel is a good product by any measure)
I really don’t want to buy MS products.

Is anyone using LibreCalc successfully on a Mac with RPM?
Yeah, I didn't either (I'm many years a Linux and Mac person). You don't need Macros, and LibreOffice will stumble along reasonably well parsing RPM. Try it.

But, when running on Excel, RPM lights up fully and simply moves into a different league.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon May 06, 2019 1:06 pm

Many thanks to the Mac RPM users who provided information to BH catalina355 on which Mac products to use to best run the model. I will make sure the Readme page has this formation for future Mac users.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

puravida
Posts: 36
Joined: Sun Apr 21, 2013 3:01 pm
Location: Central Florida

Re: Retiree Portfolio Model

Post by puravida » Thu May 09, 2019 1:44 pm

Could use some help/clarification on the Roth conversion analysis in the Results tab. I'm age 61 and mostly in the 22-24% bracket over the next 10 years. I entered Roth conv amts to max out these brackets.

Portfolio balance is 1.5M worse with conversions - Don't Convert.
Portfolio earnings is 1.8M worse with conversions - Don't convert
Fed taxes on above is 100K better with conversions - Convert

Impact on RMD of Roth Conv:
IRA 1 RMD is 1M better - Convert
Income tax on RMD is 236K better - Convert

How do I weigh this out to make a decision? Perhaps not convert since Portfolio is better off not converting? But yet with Roth, the Roth balance is worth more than an IRA balance (taxes owed). Sorry if this question was answered before.
Simple, passive, effective - the Bogle way...

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

Re: Retiree Portfolio Model

Post by FiveK » Thu May 09, 2019 1:47 pm

puravida wrote:
Thu May 09, 2019 1:44 pm
How do I weigh this out to make a decision?
Total spendable (and donatable, if you are so inclined) after-tax money is usually the best way. What does that number tell you?
Last edited by FiveK on Thu May 09, 2019 4:01 pm, edited 1 time in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu May 09, 2019 2:08 pm

puravida wrote:
Thu May 09, 2019 1:44 pm
How do I weigh this out to make a decision? Perhaps not convert since Portfolio is better off not converting? But yet with Roth, the Roth balance is worth more than an IRA balance (taxes owed). Sorry if this question was answered before.
Those simple comparisons and convert conclusions are just some of the factors that need to be considered. The impact on RMD is included in the total portfolio balance, and is shown as it's a frequent discussion topic. There is also a new calculation of how the conversion might impact a heir.

All of these are just some mathematical calculations based on the user's input data and assumptions that shows the possible impact of conversions. It's up to the user to make their own convert or don't convert decision based on the calculations and other assumptions on the future, such as higher tax rates and the desire to leave heirs with a Roth account.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

puravida
Posts: 36
Joined: Sun Apr 21, 2013 3:01 pm
Location: Central Florida

Re: Retiree Portfolio Model

Post by puravida » Sat May 11, 2019 3:18 pm

Thanks Bigfoot for the quick response.

Thanks Fivek - regarding spendable (after tax), if I understand correctly, it still comes out better to Not Convert by 950K. I took the planning-end account balances (ie @ 90 years old) & took taxes (roughly 25% for quick calculation) out for Trad IRA & Taxable accounts, thereby changing them all to after tax. I now realize I should have used a lower tax rate (allowing for lower cap gains rate) for the taxable account. The total portfolio balance after tax is 950K higher with not converting.
Simple, passive, effective - the Bogle way...

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

Re: Retiree Portfolio Model

Post by Zephavest » Sun May 12, 2019 7:30 am

puravida wrote:
Sat May 11, 2019 3:18 pm
The total portfolio balance after tax is 950K higher with not converting.
For anyone working through the convert / not convert conundrum, here are a couple of real life scenarios to consider for your heirs. If you leave a $1M, $2M, $3M inheritance to your heirs, have you taken time to seriously consider and understand the tax consequences they face? With an inherited IRA for example, they will be forced to draw it down in x years, likely paying high taxes and bumping them into as higher tax bracket.

My decision, after looking at a number of scenarios and after considering the potential loss of principle the heirs would face, by paying much higher taxes, eating into the overall inheritance they would actually realize, is to max out my conversation every year, even more important now through 2025 while the tax brackets are lower. Here are more details from an earlier post:
Zephavest wrote:
Sat Mar 09, 2019 7: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.

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.
These decisions can be started by doing a word search within the RPM spreadsheet tabs for the word 'heir'. Thank you to Bigfoot for adding this helpful information within the tool for you to consider the implications of this very important choice.

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

Re: Retiree Portfolio Model

Post by Big Dog » Sun May 12, 2019 1:49 pm

For anyone working through the convert / not convert conundrum, here are a couple of real life scenarios to consider for your heirs. If you leave a $1M, $2M, $3M inheritance to your heirs, have you taken time to seriously consider and understand the tax consequences they face?
You raise an excellent point zep. If my marginal rate is <22% and my kid's is 36% (or a whole lot more living in NYC), perhaps maxing Roth conversions now is definitely the way to go. Of course, that assumes kid does not wise up and realize that paying a combined ~50% marginal rate to live in Manhattan is not a smart economic plan. (And has similar income when I die.)

Along those lines, I once read an article about the planning for tax implications of the estate for kids. For example, if you have two kids who will share equally, but one is in a high income profession (and high tax state), while one is in a lower income profession, instead of splitting every account 50:50, estimate the kids' tax burdens and split the accounts accordingly, with more Roth $ and stepped-up equities to the higher income kid and more tIRA to the lower income kid.

The idea is that they both end up with similar amounts after tax.

time_to_go
Posts: 3
Joined: Wed Jul 04, 2018 11:41 am

Re: Retiree Portfolio Model

Post by time_to_go » Sun May 12, 2019 3:32 pm

Along those lines, I once read an article about the planning for tax implications of the estate for kids. For example, if you have two kids who will share equally, but one is in a high income profession (and high tax state), while one is in a lower income profession, instead of splitting every account 50:50, estimate the kids' tax burdens and split the accounts accordingly, with more Roth $ and stepped-up equities to the higher income kid and more tIRA to the lower income kid.

The author of that article might have the best intentions at heart to leave both his children the same amount (after taxes) - but the children may not see it that way. The lower earner might be resentful that not only does the other sibling make more money, and now the parents have left him a greater share. It might lead to a lifetime of resentments - better to leave them each the same amount.

User avatar
LadyGeek
Site Admin
Posts: 61070
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek » Sun May 12, 2019 3:42 pm

Let's keep this discussion focused on spreadsheet support.

Estate planning should be discussed in a new thread.
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.

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

Re: Retiree Portfolio Model

Post by RangeleyLake » Mon May 20, 2019 7:49 pm

Hi, I am a new member and have following this thread for a couple of years. I have read the many comments of how powerful this spreadsheet and I am seeing this for myself. Thank you for this wonderful tool, it has been useful in some of decision that we made about retiring over the last year. It took me awhile to grasp all the information. But after using it and all the feedback from everyone on this thread has help me better understand all of the inputs and Etc.

I have a question when I run the latest model (V19.1) using Automated withdrawals. When I don't choose any Planning option on the set up page, I have an alert that tells me that my "Base Camp has a negative balance". Should I have the same alert on the regular portfolio? This alert did not show up in the previous version (V19.0) using the same data. I only updated because I was curious about the new feature that was added.

I had one thought after looking at this more closely ( not sure if this was the problem). Is it possible on the base tab, that one of the formula is looking at the wrong line. ( I am looking at line 126 - Roth 1 automatic) In this formula it mention line 360. This line indicates it is a "Less withdrawals 2" Should it be looking at line 362 which is "Available for Automatic withdrawals"

Thanks again for this wonderful tool. Great compliment to ORP and FRA
RangeleyLake
RangeleyLake

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon May 20, 2019 8:45 pm

RangeleyLake, I'm glad you're finding the model useful and even more glad you found a problem AND solved it! Yes, those formulas should be identical in the Full and Base calculations, but in doing a recent modification the Full formulas for that line didn't get copied over.

I will fix that and update the model. Thanks for the report and fix.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model - UPDATE RELEASE

Post by BigFoot48 » Tue May 21, 2019 8:13 am

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

This update fixes a formula error in Base case Roth IRA 1 automatic withdrawals calculations. Update to this version is only needed if using the automated withdrawals settings AND doing Full to Base comparisons (Roth conversions or SS alternatives). Thanks to BH RangeleyLake for reporting and solving this error.

Use of alpha version labels, i.e. "19.1a", to signify important but minor changes also begins. Thanks to BH Zephavest for this idea to make the files of released versions more manageable.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

FutureEye
Posts: 9
Joined: Tue May 28, 2019 6:40 am

Re: Retiree Portfolio Model

Post by FutureEye » Tue May 28, 2019 7:16 am

I am comparing a retirement model that i made to the Boggleheads Retiree Portfolio Model. Couple of observations & questions that I'm going to post as short individual replies.
Why do you believe using the Round function adds value? IMHO it makes it difficult to audit functions, adds to the processing overhead & could introduce error over long periods.

FutureEye
Posts: 9
Joined: Tue May 28, 2019 6:40 am

Re: Retiree Portfolio Model

Post by FutureEye » Tue May 28, 2019 7:19 am

I am comparing a retirement model that i made to the Boggleheads Retiree Portfolio Model.
The Boggle IRA delta formula is (I use IRA as example, it is the same for all accounts):
=IRA+(IRA+contributions+Withdrawals+RMD withdrawals+automatic+SPIA purchase-IRA2Roth)/2*%ROI.
Simplified it is
=IRA+(IRA+changes)/2*%ROI.
I use
=(IRA+changes)*%ROI to calculate ROI at "End of Year" from prior "End of Year".
What is the advantage?

FutureEye
Posts: 9
Joined: Tue May 28, 2019 6:40 am

Re: Retiree Portfolio Model

Post by FutureEye » Tue May 28, 2019 7:30 am

I'm focusing on the logic behind multiyear IRA to Roth conversions (IRA2Roth). I built my own spreadsheet 10yrs ago. My brother recently sent me the Boggleheads spreadsheet.
In my spreadsheet I am graphing “value” of my savings: Taxable+Roth+IRA-(IRA*Then Year Tax Bracket). Boggle spreadsheet just shows total Taxable+Roth+IRA. Once IRA2Roth are done it makes no difference. But before then I think it is very relevant. I am using the value of total savings & graphing 30yr predictions.

Post Reply