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
dumbbunny
Posts: 876
Joined: Thu Apr 24, 2014 10:00 pm
Location: Oregon coast

Re: Retiree Portfolio Model

Post by dumbbunny » Tue Dec 18, 2018 3:20 pm

My wife and I keep our finances separate. This agreement was her choice due to her first marriage. I prepare the taxes (TurboTax) so I know her income comes from a small pension and Social Security. But I don't know her IRA and taxable account balances. Will this spreadsheet be beneficial to me without that input?
“It’s the curse of old men to realize that in the end we control nothing." "Homeland" episode, "Gerontion"

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

Re: Retiree Portfolio Model

Post by munemaker » Thu Dec 20, 2018 7:34 pm

dumbbunny wrote:
Tue Dec 18, 2018 3:20 pm
My wife and I keep our finances separate. This agreement was her choice due to her first marriage. I prepare the taxes (TurboTax) so I know her income comes from a small pension and Social Security. But I don't know her IRA and taxable account balances. Will this spreadsheet be beneficial to me without that input?
If she won't share her financial information, then personally I don't think using RPM would be very fruitful.

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

Re: Retiree Portfolio Model

Post by Zephavest » Fri Dec 21, 2018 6:53 am

dumbbunny wrote:
Tue Dec 18, 2018 3:20 pm
My wife and I keep our finances separate. This agreement was her choice due to her first marriage. I prepare the taxes (TurboTax) so I know her income comes from a small pension and Social Security. But I don't know her IRA and taxable account balances. Will this spreadsheet be beneficial to me without that input?
If you are interested in planning your own financial retirement picture, for your own finances only, not your wife's, then certaining you can use RPM.

For taxes you can set that you are married but put her income at zero. Setup your own Social Security, again, hers at zero, and just list both of your ages/end dates the same so you don't have to bother looking at the spouse survivor benefits.

Now you can use the tool as if you are single but having the taxes calculated as married.

royalswin
Posts: 2
Joined: Mon Dec 24, 2018 8:27 pm

Re: Retiree Portfolio Model - Negative Balances

Post by royalswin » Mon Dec 24, 2018 8:33 pm

Hi folks, newbie here. This tool is awesome but I am having one issue and can't seem to find the answer on the board. I know that the tool does not correct for negative account balances, and that must be done manually. (That is, once the taxable account runs out, I want to change it to stop going negative but instead take withdrawals from the IRA account). But where is that change suppose to be made? Every cell I try to change in this regard says it is protected. I'm sure I'm missing something simple--help????

Thanks!!!!

MrDrinkingWater
Posts: 131
Joined: Tue Dec 23, 2014 11:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater » Mon Dec 24, 2018 8:52 pm

Try this: On the Setup Page, go to section 6, "IRA Contributions & Withdrawals". Set up some withdrawals from a traditional or Roth IRA to fill your taxable account. Also, go to section 4, "Income", to put in any pension income that you may have forgotten to include earlier. I hope this helps.

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

Re: Retiree Portfolio Model - Negative Balances

Post by munemaker » Mon Dec 24, 2018 9:01 pm

royalswin wrote:
Mon Dec 24, 2018 8:33 pm
Hi folks, newbie here. This tool is awesome but I am having one issue and can't seem to find the answer on the board. I know that the tool does not correct for negative account balances, and that must be done manually. (That is, once the taxable account runs out, I want to change it to stop going negative but instead take withdrawals from the IRA account). But where is that change suppose to be made? Every cell I try to change in this regard says it is protected. I'm sure I'm missing something simple--help????

Thanks!!!!
I spent a lot of time with RPM at the beginning of the year and I will do the same at the beginning of 2019. I say this because it is not all fresh in my mind.

I do not remember accounts going negative.

I believe you specify the year for starting to draw from the various accounts on the Setup page. Take a good look at the Setup page.

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

Re: Retiree Portfolio Model - Negative Balances

Post by Zephavest » Tue Dec 25, 2018 8:14 am

royalswin wrote:
Mon Dec 24, 2018 8:33 pm
I'm sure I'm missing something simple--help????
The simplest method I've found is on the Setup tab, Cell G11, the "general settings" section, set the "Minimum taxable balance" for your desired taxable account spend rate annualy, for example $50,000. Then, still in the same section, set the "Use auto withdrawals?" to Yes. If this does not acheive what you want then you can also do additional manual entries in Section 6.

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

Re: Retiree Portfolio Model - Negative Balances

Post by BigFoot48 » Tue Dec 25, 2018 8:16 am

royalswin wrote:
Mon Dec 24, 2018 8:33 pm
Hi folks, newbie here. This tool is awesome but I am having one issue and can't seem to find the answer on the board. I know that the tool does not correct for negative account balances, and that must be done manually. (That is, once the taxable account runs out, I want to change it to stop going negative but instead take withdrawals from the IRA account). But where is that change suppose to be made? Every cell I try to change in this regard says it is protected. I'm sure I'm missing something simple--help????
As others have pointed out, all entries to model your portfolio are made on the Setup page. Go through each section, read the instruction in the heading titles and entries titles, and enter your data. This model requires the user to plan their future financial events, such as when Social Security and pensions will start, when and how much to withdraw from IRAs to keep the taxable/brokerage account funded and what special events may happen in the future to impact the portfolio.

So to keep the taxable account from going negative you need to either increase cash flowing into the account by increasing income, starting IRA withdrawals earlier and in larger amounts, or decreasing expenses. You can also use the "Use auto withdrawals?" setting in the General Settings section in the Modeling Options area. This should keep the taxable account from going negative, but you lose the future planning education that comes from entering withdrawal amounts yourself.

Hope that helps you. Thanks to those that replied to your question.

Merry Christmas to all, and version 19 is coming New Years day.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

royalswin
Posts: 2
Joined: Mon Dec 24, 2018 8:27 pm

Re: Retiree Portfolio Model

Post by royalswin » Tue Dec 25, 2018 8:27 am

That worked guys, thanks so much for the quick responses!!!!

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

Re: Retiree Portfolio Model

Post by sandramjet » Thu Dec 27, 2018 8:51 pm

Just wondering if you have any thoughts on the best way to model some of the less common tax events... in my case specifically, looking at how to model the impact of the American Opportunity Tax Credit, which provides a credit of up to $2500 if you have appropriate college expenses of $4000 or more, but it begins to phase out at 160K of AGI, and is completely out at 180K. Because of that, I was looking to try to evaluate whether to do Roth conversions or limit them so as not to lose the credit.

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

Re: Retiree Portfolio Model

Post by FiveK » Thu Dec 27, 2018 9:34 pm

sandramjet wrote:
Thu Dec 27, 2018 8:51 pm
Just wondering if you have any thoughts on the best way to model some of the less common tax events... in my case specifically, looking at how to model the impact of the American Opportunity Tax Credit, which provides a credit of up to $2500 if you have appropriate college expenses of $4000 or more, but it begins to phase out at 160K of AGI, and is completely out at 180K. Because of that, I was looking to try to evaluate whether to do Roth conversions or limit them so as not to lose the credit.
The personal finance toolbox spreadsheet is only an estimation tool, but seems to contain more of the common credits, phaseouts, etc., than the usual web-based shortcut tools.

The marginal rate graph it generates seems worth the proverbial 1000 words, but that may be a matter of taste.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Dec 28, 2018 8:58 am

sandramjet wrote:
Thu Dec 27, 2018 8:51 pm
Just wondering if you have any thoughts on the best way to model some of the less common tax events... in my case specifically, looking at how to model the impact of the American Opportunity Tax Credit, which provides a credit of up to $2500 if you have appropriate college expenses of $4000 or more, but it begins to phase out at 160K of AGI, and is completely out at 180K. Because of that, I was looking to try to evaluate whether to do Roth conversions or limit them so as not to lose the credit.
Located below the Roth conversion entry section and Taxes By Bracket schedule on the Setup page of RPM is a "supporting data and calculations" section. This is normally hidden so "show" it. This is the "Taxable Income and Tax Summary table". It would be easy to enter your desired conversion amounts, then scroll down to see what AGI is forecast as a result, and make adjustments to the conversion amounts to keep AGI below 160k. Be sure it is set to show the "Full" portfolio which includes conversions.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

LookinAround
Posts: 68
Joined: Tue Mar 27, 2018 5:41 am
Location: Chicagoland

Re: Retiree Portfolio Model

Post by LookinAround » Fri Dec 28, 2018 10:04 am

I am single.

I download the spreadsheet, enable Editing and Content, go to the Setup tab. Spouse age is set to default 61. Instructions say "Leave blank if single". I tried Delete and Space to get a blank for Spouse age but both generate multiple red error messages. What to do if single?

TIA

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Dec 28, 2018 10:07 am

LookinAround wrote:
Fri Dec 28, 2018 10:04 am
I am single.

I download the spreadsheet, enable Editing and Content, go to the Setup tab. Spouse age is set to default 61. Instructions say "Leave blank if single". I tried Delete and Space to get a blank for Spouse age but both generate multiple red error messages. What to do if single?

TIA
It will show an error as there are other settings in the Example data that reference the spouse, such as owning an IRA, getting SS benefits and inheriting an IRA. Clear all the settings first and those errors will go away.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 01, 2019 7:55 am

Version 19.0 of the Retiree Portfolio Model model is available as of Jan 1, 2019 and can be downloaded from Dropbox via this link: removed as version 19.1 available March 1.

Changes in version 19.0 include:
2019 Federal tax rates, brackets and standard deduction. Simple tax calculator does 2018 and 2019 estimates.
Added ability to set return rates for Roth 2 and Roth Conversion accounts, allowing fine-tuning of returns for those accounts.
Added second withdrawal option for existing Roth IRA 1.
Added a Qualified Dividend/LTCG adjustment to the Federal taxable income calculation to approximate the impact of the lower tax rates of these income items. Thanks to BH mkedst for this suggestion and help.
Corrected second state income tax bracket for future/second state calculation error.
Modified many graphs to show the chosen number of years being modeled and not just the full 40 years. X axis labels don't show in LibeOffice Calc for these, unfortunately.
Spouse-related entries on Setup page are now labeled in purple to make easier to quickly find and update.
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.

Minor updates added to 19.0. Download again if any of these may benefit your model.
Jan 1: Modified the SPIA Purchase settings to allow the inclusion of income from a purchase made in a year prior to the model starting year. Any of the three accounts allowed for SPIA purchases can now include the income. The purchase price is ignored if an age earlier than Your starting age is entered as the Buying Age.
Jan 2: Fixed the Social Security "Test Alternative PIA Amounts" calculator to properly calculate the yearly amounts.
Jan 6: Fixed calculation of Roth IRA 1 withdrawals to insure automated withdrawals don't exceed the account balance.

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. 173,838 views

To download: Look for Blue "Download" box on Dropbox page in center, to lower half of screen. Selection box down arrow, and select "Direct Download". When the Dropbox sign in pop up appears, just ignore it, look at the bottom of the pop up and select: "No thanks, continue to view" and you will then be able to download the file to your computer.

A happy, health and prosperous New Year to all Bogleheads!
Last edited by BigFoot48 on Fri Mar 01, 2019 8:03 am, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 01, 2019 10:12 am

A user reported a situation not covered by the model - the purchase of an annuity in an earlier year, and the need to show the income in the current and future years. While this income could be shown in the existing Other Income settings, there is no provision in them for income in IRA 1 or Roth IRA, both of which could be used to purchase an annuity, and are non-taxable. Luckily, I was able to quickly modify the SPIA purchase and income section to include the income from a prior purchase if an earlier age is entered. See the cell comments for instructions.

19.0 has been updated.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

vest74
Posts: 108
Joined: Tue Apr 07, 2015 12:05 pm

Re: Retiree Portfolio Model

Post by vest74 » Tue Jan 01, 2019 10:23 am

what if you do not have drop box and do not care to "enroll" i use open office

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 01, 2019 10:32 am

vest74 wrote:
Tue Jan 01, 2019 10:23 am
what if you do not have drop box and do not care to "enroll" i use open office
The download should not require a membership. Can this be confirmed?
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Zephavest » Tue Jan 01, 2019 10:39 am

Confirmed, it works fine without an account. I signed out of my Dropbox account and tried downloading again.

Do these steps:
Look for Blue "Download" box on Dropbox page in center, to lower half of screen. Selection box down arrow, and select "Direct Download"'

When the Dropbox sign in pop up appears, just ignore it, look at the bottom of the pop up and select: "No thanks, continue to view"
and you will then be able to download the file to your computer.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 01, 2019 10:54 am

Thanks Zephavest! I will add those instructions to the post.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

scottadelman
Posts: 9
Joined: Fri Mar 13, 2015 12:30 pm

Re: Retiree Portfolio Model

Post by scottadelman » Wed Jan 02, 2019 2:04 pm

@Big Foot 48. Just wanted to post and say Thank You. Thank you for the new version and for all your work involved in creating this. It is an amazing tool. Happy New Year.

MrDrinkingWater
Posts: 131
Joined: Tue Dec 23, 2014 11:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater » Fri Jan 04, 2019 2:02 pm

In the newest v19.0, has anyone else used the clear entries macro, and then used the load macro without receiving an error message dialog box from your version of Microsoft Excel?

After I start v19.0, clear entries, and use the load macro to import data from an 18.2 RPM file, I get this error: "Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct,"

I'm using Microsoft Excel for Mac, version 16.20

I'll do some further testing out using the load macro of v19.0 to import version 18.2 RPM files.

jkrm
Posts: 82
Joined: Wed Oct 08, 2008 8:20 am

Re: Retiree Portfolio Model

Post by jkrm » Fri Jan 04, 2019 2:21 pm

I’m having the same problem. I suspected the load macro but haven’t had time to experiment to be sure of that. It seems like the computations all work fine though.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Jan 04, 2019 4:11 pm

Sorry you are getting an error message. Using Excel 2013 or Libre Office Calc 6.1, when I clear macros in 19.0 and load from either 18.2 or 17.0 I don't get any error message. If either of you that are seeing the macro want to send me a PM we can arrange to let me test the load models you are using. It's likely due to using the Mac version and an input setting that is new or somehow changed from the older version having some conflict.

You shouldn't be seeing it, but I think it can be ignored if you check all your input settings to the old model.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

scottadelman
Posts: 9
Joined: Fri Mar 13, 2015 12:30 pm

Re: Retiree Portfolio Model

Post by scottadelman » Fri Jan 04, 2019 8:15 pm

I also use a Mac and got the same error when copying my entries over. After spending some time look at them I all, they all seemed correct EXCEPT the entries in the calculator for yearly expenses. Those seemed to have a few missing rows and I need to correct them. Hope that information helps.

MrDrinkingWater
Posts: 131
Joined: Tue Dec 23, 2014 11:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater » Fri Jan 04, 2019 8:16 pm

I'll double-check that my inputs are correct and reasonable.

I don't think the issue is with the load macro since apparently very few other users are having issues with RPM v19.0 .

My input data may have an unusual characteristic (or user entry error!) that does not create an error condition within Excel with RPM v18.2, but it does so far with RPM v19.0.

I did start a new RPM v19.0 file. I zeroed with clear entries macro, and then entered the data from my RPM v18.2 file by hand. I am still getting the same Excel error message once every few minutes while editing. I have mostly been editing the Roth Conversions in Section 10 and editing annual living expense data in Section 5 of the Setup page. I have a suspicion this Excel error is only occurring a few moments after I switch to the other sheets in the Excel workbook identified as Summary and Results.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Jan 04, 2019 10:36 pm

This "formula reference" problem may be related to the new graphs in 19.0. As a test, I've removed them and the names that had to be created to make them work. For those having the problem, download this version where I have deleted these new names and the graphs that used them, and see if this fixes the problem: 19.0NG: https://www.dropbox.com/s/lln54ga9j1wbt ... .xlsm?dl=0
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by munemaker » Fri Jan 04, 2019 11:11 pm

Hi Bigfoot48

Thank you very much for the new version. Very impressive.

I am having a problem I hope you can help me with. I ran the same input data using Excel and Google Sheets, with identical results including the same problem in both.

Problem is...there are a number of places in the workbook where the Final Balance is referenced, usually in green numbers. The numbers are the same every time they are referenced and appear to be wrong. In some cases, there is an error message under the total that shows the difference between the correct Final Balance and the Final Balance shown in green.

Here are the specifics:

On the DETAILS sheet:
- Cells E8, E11, E14, E17, E18, E19 are in green (suspected wrong) and do not agree with the actual Final Balance cells, which in my case are AN8, AN11, AN14, AN17, AN18, AN19. Cells AU8, AU11, AU14, AU17, AU18, AU19 (also in green) agree with the incorrect values in column E.
- Cells E246, E249, E252, E253, E254, E255, E256, E257 are in green (suspected wrong) with an error in red noted in cell D257 showing the difference between the value in green and the actual Final Balance in AN246, AN249, AN252, AN254, AN255, AN256, AN257. Cells AU246, U249, AU252, AU253, AU254, AU255, AU256, AU257 (also in green) agree with the incorrect values in column E.

The BASE sheet as similar issues to the above.

On the RESULTS sheet:
- Cells C79, D79, E79 show error and amount of error in red. The error is related to the Final Balance as described above.

Any idea what is going on here? Something I am doing wrong? Problem with RPM?

Everything else seems to be working fine.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Jan 04, 2019 11:35 pm

munemaker wrote:
Fri Jan 04, 2019 11:11 pm
On the DETAILS sheet:
- Cells E8, E11, E14, E17, E18, E19 are in green (suspected wrong) and do not agree with the actual Final Balance cells, which in my case are AN8, AN11, AN14, AN17, AN18, AN19. Cells AU8, AU11, AU14, AU17, AU18, AU19 (also in green) agree with the incorrect values in column E.
It may not be a bit confusing, but AN8 is the final year starting balance and E8 is the ending balance (hence the green color). So these will be different, as will the others in lines 8-19.
- Cells E246, E249, E252, E253, E254, E255, E256, E257 are in green (suspected wrong) with an error in red noted in cell D257 showing the difference between the value in green and the actual Final Balance in AN246, AN249, AN252, AN254, AN255, AN256, AN257. Cells AU246, U249, AU252, AU253, AU254, AU255, AU256, AU257 (also in green) agree with the incorrect values in column E.
On the RESULTS sheet:
- Cells C79, D79, E79 show error and amount of error in red. The error is related to the Final Balance as described above.
E246 etc, AN246 etc and AU246 should all agree as those are the ending final balances.

I'm not sure how you are getting the red error which indicates an out-of-balance condition. The Example Data, which contains entries and uses all the different fields, is in balance. You may need to send me a PM and your model for me to understand how that is happening.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by munemaker » Fri Jan 04, 2019 11:55 pm

BigFoot48 wrote:
Fri Jan 04, 2019 11:35 pm
You may need to send me a PM and your model for me to understand how that is happening.
PM sent

MrDrinkingWater
Posts: 131
Joined: Tue Dec 23, 2014 11:30 am

Re: Retiree Portfolio Model

Post by MrDrinkingWater » Sat Jan 05, 2019 12:38 am

I used Retiree Portfolio Model v19.0NG.xlsm. Your fix to remove the new graphs appears to have stopped the formula reference problem from occurring, but in doing so, the fix may have introduced, or at least exposed, a different problem.

Of course, the Section 10, Optional Roth Conversion data, is not imported when loading data from an old RPM file with the load macro. After loading data to Retiree Portfolio Model v19.0NG.xlsm, the Setup page now has an error message reported below section 1: "Alert: Portfolio has negative balance in one or more accounts for 1 years[sic]". I see on the Details page that, in year 39, below the Account Balances data, there was a negative $500 balance in the Roth IRA cell, and a red "Negative" text printed below the column. Even after putting some data into Section 10 to have a positive balance for Roth IRA in year 39, the Alert error remains below Section 1 of the Setup page, and the red "Negative" text remains below the year 39 column of the Account balances data of the Details page. There are not any negative numbers above in any of the balances of the Account Balance data of the Details page.

I hope this test run and my observations are useful to you. Good night. See you tomorrow.
BigFoot48 wrote:
Fri Jan 04, 2019 10:36 pm
This "formula reference" problem may be related to the new graphs in 19.0. As a test, I've removed them and the names that had to be created to make them work. For those having the problem, download this version where I have deleted these new names and the graphs that used them, and see if this fixes the problem: 19.0NG: https://www.dropbox.com/s/lln54ga9j1wbt ... .xlsm?dl=0

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

Re: Retiree Portfolio Model

Post by munemaker » Sun Jan 06, 2019 9:32 pm

BigFoot48 wrote:
Fri Jan 04, 2019 11:35 pm
munemaker wrote:
Fri Jan 04, 2019 11:11 pm
On the DETAILS sheet:
- Cells E8, E11, E14, E17, E18, E19 are in green (suspected wrong) and do not agree with the actual Final Balance cells, which in my case are AN8, AN11, AN14, AN17, AN18, AN19. Cells AU8, AU11, AU14, AU17, AU18, AU19 (also in green) agree with the incorrect values in column E.
It may not be a bit confusing, but AN8 is the final year starting balance and E8 is the ending balance (hence the green color). So these will be different, as will the others in lines 8-19.
- Cells E246, E249, E252, E253, E254, E255, E256, E257 are in green (suspected wrong) with an error in red noted in cell D257 showing the difference between the value in green and the actual Final Balance in AN246, AN249, AN252, AN254, AN255, AN256, AN257. Cells AU246, U249, AU252, AU253, AU254, AU255, AU256, AU257 (also in green) agree with the incorrect values in column E.
On the RESULTS sheet:
- Cells C79, D79, E79 show error and amount of error in red. The error is related to the Final Balance as described above.
E246 etc, AN246 etc and AU246 should all agree as those are the ending final balances.

I'm not sure how you are getting the red error which indicates an out-of-balance condition. The Example Data, which contains entries and uses all the different fields, is in balance. You may need to send me a PM and your model for me to understand how that is happening.
OK, I looked at this again tonight and I found a problem with the version of RPM I downloaded from the link above. I noticed most of the incorrect numbers (in green) on my DETAIL and BASE pages (with my data) agreed exactly with the numbers on the sheets as downloaded with your example data. From there, I noticed there are no formulas in cells E249 through E257 on both the DETAIL and BASE pages. I copied the formula from Cell E246 into cells E249 through E257 (on both pages) and that seems to have corrected the problem.

There also may (or may not) be a minor issue in cells AU8 through AU19 on both the BASE and DETAIL page. The column header implies the text color black or green indicates whether the base case or final year is being displayed...and as a result, I was expecting when I turned "USE ROTH CONVERSIONS" on and off (on the SETUP page), that would result in the colors being toggled on cells AU8 through AU19 of the BASE and DETAIL page. Maybe that's not what you intended, but that was my interpretation.

Would like to hear your comments on the above.

And again, thanks for making this great tool available to us. I know of nothing like it.

EDIT:
Some additional items:
On BASE page: Missing forumlas in AW249 through AW257.Formula in AW246 needs copied to AW249 through AW257.

On the RESULTS page, cells E346 and E350 have the same formula and hence yield the same numbers. Shouldn't they be different? Same question with E347 and E351.

On RESULTS page, cell E234 is labeled as "Taxable account balances at start of year" but I think cell E354 is actually the Taxable Account Balance at end of year.

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

Re: Retiree Portfolio Model

Post by munemaker » Mon Jan 07, 2019 9:37 am

munemaker wrote:
Sun Jan 06, 2019 9:32 pm
BigFoot48 wrote:
Fri Jan 04, 2019 11:35 pm
munemaker wrote:
Fri Jan 04, 2019 11:11 pm
On the DETAILS sheet:
- Cells E8, E11, E14, E17, E18, E19 are in green (suspected wrong) and do not agree with the actual Final Balance cells, which in my case are AN8, AN11, AN14, AN17, AN18, AN19. Cells AU8, AU11, AU14, AU17, AU18, AU19 (also in green) agree with the incorrect values in column E.
It may not be a bit confusing, but AN8 is the final year starting balance and E8 is the ending balance (hence the green color). So these will be different, as will the others in lines 8-19.
- Cells E246, E249, E252, E253, E254, E255, E256, E257 are in green (suspected wrong) with an error in red noted in cell D257 showing the difference between the value in green and the actual Final Balance in AN246, AN249, AN252, AN254, AN255, AN256, AN257. Cells AU246, U249, AU252, AU253, AU254, AU255, AU256, AU257 (also in green) agree with the incorrect values in column E.
On the RESULTS sheet:
- Cells C79, D79, E79 show error and amount of error in red. The error is related to the Final Balance as described above.
E246 etc, AN246 etc and AU246 should all agree as those are the ending final balances.

I'm not sure how you are getting the red error which indicates an out-of-balance condition. The Example Data, which contains entries and uses all the different fields, is in balance. You may need to send me a PM and your model for me to understand how that is happening.
OK, I looked at this again tonight and I found a problem with the version of RPM I downloaded from the link above. I noticed most of the incorrect numbers (in green) on my DETAIL and BASE pages (with my data) agreed exactly with the numbers on the sheets as downloaded with your example data. From there, I noticed there are no formulas in cells E249 through E257 on both the DETAIL and BASE pages. I copied the formula from Cell E246 into cells E249 through E257 (on both pages) and that seems to have corrected the problem.

There also may (or may not) be a minor issue in cells AU8 through AU19 on both the BASE and DETAIL page. The column header implies the text color black or green indicates whether the base case or final year is being displayed...and as a result, I was expecting when I turned "USE ROTH CONVERSIONS" on and off (on the SETUP page), that would result in the colors being toggled on cells AU8 through AU19 of the BASE and DETAIL page. Maybe that's not what you intended, but that was my interpretation.

Would like to hear your comments on the above.

And again, thanks for making this great tool available to us. I know of nothing like it.

EDIT:
Some additional items:
On BASE page: Missing forumlas in AW249 through AW257.Formula in AW246 needs copied to AW249 through AW257.

On the RESULTS page, cells E346 and E350 have the same formula and hence yield the same numbers. Shouldn't they be different? Same question with E347 and E351.

On RESULTS page, cell E234 is labeled as "Taxable account balances at start of year" but I think cell E354 is actually the Taxable Account Balance at end of year.
Bigfoot48 worked with me to resolve my issue, and it was MY issue. The file apparently was corrupted during download from Dropbox. Very strange. Anyway, everything is working perfectly now.

Thanks to Bigfoot48.

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

Re: Retiree Portfolio Model

Post by Zephavest » Tue Jan 08, 2019 5:27 pm

Retiree Portfolio Model User Review:

For all you prospective RPM users and part time users, I thought it may help to post my experiences using RPM for the last five years. The first couple of years I just dabbled around the edges but then a little before 60, getting the retirement itchy finger I got more serious. While there is a learning curve to use all the powerful features it is well worth the perseverance to program it to your own personal needs. Once I understood most of what it was telling me and I felt it looked like a doable retirement plan I still discussed my plans with my 401K administrator and a Vanguard Advisor. When all three aligned I pulled the trigger and retired early.

Now moving beyond the planning stage to the execution stage I'm further amazed by the power of RPM. While I keep separate spreadsheets for my budget and investments I still find the insight RPM gives me invaluable. It helped me to make the decision to setup yearly Roth Conversions, especially in light of the current lowered tax brackets and helps me hold off starting Social Security to as close to age 70 as I can manage. When I run a what if, such as pulling money from this account vs that account RPM gives me new insights with each new scenario and sheds light on different angles I may not have considered before. It is a really powerful tool so be prepared to have time to study it, integrate it into your own personal plans and be amazed at the insight it will bring. I've used the Fidelity retirement planner, Maxifiplanner.com and Quickensso.com, none of them gave me the comprehensive insight the Retiree Portfolio Model tool provides.

I'm immensely grateful to the author BigFoot48 and the untold hours he must spend to keep it updated for the Bogleheads community.

Fair Winds and Following Seas, Zephavest

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jan 09, 2019 9:32 am

Many thanks to Zephavest for his insightful review of RPM.

The model has evolved significantly since its introduction in 2012 as a simple Roth Conversion tool. Over the years it has had invaluable contributions from dozens of Bogleheads, with many suggestions for new features that have been implemented. These have made it a much better retiree planning tool. There has also been calculation and logic errors reported, the correction of which has made it's calculation of the users' reliably accurate. Without all of this help RPM would be a shadow of its current self. Thanks to all of you that have made it the valuable planning tool it is today.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

scottadelman
Posts: 9
Joined: Fri Mar 13, 2015 12:30 pm

Re: Retiree Portfolio Model

Post by scottadelman » Wed Jan 09, 2019 9:54 am

Sorry if this is a naive question. Are the dollar amounts that are modeled in this tool based on future dollars or represented in today's dollars? If my plan shows that I will have $1M in my retirement accounts in twenty years is that saying the actual value will be $1M at that point in time, or just equivalent power of $1M in today's dollars? And how does the Present Value discount rate (set in General Settings) change these calculations? I have tried to change the percentage in this field but do not see any alterations to any calculations or prediction.

Thanks in advance for any help.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jan 09, 2019 10:12 am

scottadelman wrote:
Wed Jan 09, 2019 9:54 am
Sorry if this is a naive question. Are the dollar amounts that are modeled in this tool based on future dollars or represented in today's dollars? If my plan shows that I will have $1M in my retirement accounts in twenty years is that saying the actual value will be $1M at that point in time, or just equivalent power of $1M in today's dollars? And how does the Present Value discount rate (set in General Settings) change these calculations? I have tried to change the percentage in this field but do not see any alterations to any calculations or prediction.

Thanks in advance for any help.
If nominal rates are used for earnings, rather than real rates that exclude inflation, and an inflation factor is entered for expenses, then the results are inflated amounts. The Present Value factor is used in a few places to show the discounted cash flow of a few results that have alternative modeling choices, such as Social Security earnings on the Results page.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

computerdoc
Posts: 7
Joined: Tue Dec 27, 2016 6:02 pm

Re: Retiree Portfolio Model

Post by computerdoc » Sat Jan 12, 2019 9:56 am

Where do I enter my VA disability benefit in the model? It is not taxable at the federal or local levels, and is indexed for inflation.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Jan 12, 2019 10:07 am

computerdoc wrote:
Sat Jan 12, 2019 9:56 am
Where do I enter my VA disability benefit in the model? It is not taxable at the federal or local levels, and is indexed for inflation.
Enter it as an Other Income item then use the AGI user adjustment in Section 8 to remove the income from taxes. This requires entering the inflated amounts (as negative numbers) for all the years you will be receiving it on the Details page. The adjustment will also be included in state AGI.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by munemaker » Mon Jan 14, 2019 10:17 am

Just want to report there seems like a minor error on the "Balances by Account at Year End" graph on the RESULTS sheet.

For my situation, the graph shows for colors: red, orange, yellow and green. However, there are only 3 labels: Blue = Taxable, Green = IRA, Red = Inherited IRA. The Blue and Green are correctly labeled. The Orange should actually be labeled as "Inherited IRA" (which is incorrectly labeled as Red). Red should be labeled as Roth "IRA Conversions."

Not a big deal, but I thought I would point it out.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Jan 14, 2019 10:57 am

munemaker wrote:
Mon Jan 14, 2019 10:17 am
Just want to report there seems like a minor error on the "Balances by Account at Year End" graph on the RESULTS sheet.

For my situation, the graph shows for colors: red, orange, yellow and green. However, there are only 3 labels: Blue = Taxable, Green = IRA, Red = Inherited IRA. The Blue and Green are correctly labeled. The Orange should actually be labeled as "Inherited IRA" (which is incorrectly labeled as Red). Red should be labeled as Roth "IRA Conversions."

Not a big deal, but I thought I would point it out.
Thank you for this report. The label size is indeed too small to show all the items and will be expanded. I checked and all the labels are correctly showing the data from the Details page and the colors in the labels and the graph bars match and are correct. Inherited IRA is red, Roth is orange and Roth Conv a dark red. The Sources of Earnings graph next to it shows all the accounts and the colors can be verified there.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by munemaker » Mon Jan 14, 2019 11:31 am

BigFoot48 wrote:
Mon Jan 14, 2019 10:57 am
munemaker wrote:
Mon Jan 14, 2019 10:17 am
Just want to report there seems like a minor error on the "Balances by Account at Year End" graph on the RESULTS sheet.

For my situation, the graph shows for colors: red, orange, yellow and green. However, there are only 3 labels: Blue = Taxable, Green = IRA, Red = Inherited IRA. The Blue and Green are correctly labeled. The Orange should actually be labeled as "Inherited IRA" (which is incorrectly labeled as Red). Red should be labeled as Roth "IRA Conversions."

Not a big deal, but I thought I would point it out.
Thank you for this report. The label size is indeed too small to show all the items and will be expanded. I checked and all the labels are correctly showing the data from the Details page and the colors in the labels and the graph bars match and are correct. Inherited IRA is red, Roth is orange and Roth Conv a dark red. The Sources of Earnings graph next to it shows all the accounts and the colors can be verified there.
OK - thanks for checking it out.

jaytrain52
Posts: 2
Joined: Tue Jan 15, 2019 11:57 am

Re: Retiree Portfolio Model

Post by jaytrain52 » Tue Jan 15, 2019 12:00 pm

It appears that state taxes are not being calculated correctly when using state tax brackets. In particular, taxes are not being calculated for the 2 highest tax brackets. Am I wrong or is there an issue? Thanks.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 15, 2019 12:30 pm

jaytrain52 wrote:
Tue Jan 15, 2019 12:00 pm
It appears that state taxes are not being calculated correctly when using state tax brackets. In particular, taxes are not being calculated for the 2 highest tax brackets. Am I wrong or is there an issue? Thanks.
Are you using version 19.0, because that was fixed last year and appears to be working in 19.0. Do keep in mind that brackets are increased by the tax escalation factor each year.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

jaytrain52
Posts: 2
Joined: Tue Jan 15, 2019 11:57 am

Re: Retiree Portfolio Model

Post by jaytrain52 » Tue Jan 15, 2019 2:31 pm

Regarding the state tax calculation issue, I am using version 19. To double-check, I did a clean download today and see that the formulas on the "Details" sheet for rows 219 and 220 appear to be incorrect. Also, cell F218 appears incorrect. Thanks.

stvyreb
Posts: 93
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jan 15, 2019 4:15 pm

Hello, thx for the RPM SS, however, I find it rather complex how to start or if it is even intended for an early retiree?

eg I am in my mid 50s, I have 2 parents IIRA RMDs every year, in Summary Section 1

it says "Inherited IRA at age 77" then the blue box I have entered 55 my age , It looks like this section can't be used for IIRA unless one is > 77 years old when they get the IIRA ?

Error: Inherited IRA RMD age not +1 year > inherited age




is there perhaps a longer written step-by-step how to use the SS, other than the 1st tab ??


regards

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 15, 2019 5:27 pm

jaytrain52 wrote:
Tue Jan 15, 2019 2:31 pm
Regarding the state tax calculation issue, I am using version 19. To double-check, I did a clean download today and see that the formulas on the "Details" sheet for rows 219 and 220 appear to be incorrect. Also, cell F218 appears incorrect. Thanks.
PM sent

UPDATE: jaytrain52 was using an old version of Apache OpenOffice to run RPM. This program, either when opening the model or saving it, corrupted the state tax calculation by replacing the 2nd and 3rd bracket formulas with the 1st bracket's. Possibly other corruptions happened too. Very unusual. The current version will be tested.
Last edited by BigFoot48 on Thu Jan 17, 2019 7:09 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jan 15, 2019 5:44 pm

stvyreb wrote:
Tue Jan 15, 2019 4:15 pm
Hello, thx for the RPM SS, however, I find it rather complex how to start or if it is even intended for an early retiree?

eg I am in my mid 50s, I have 2 parents IIRA RMDs every year, in Summary Section 1

it says "Inherited IRA at age 77" then the blue box I have entered 55 my age , It looks like this section can't be used for IIRA unless one is > 77 years old when they get the IIRA ?

Error: Inherited IRA RMD age not +1 year > inherited age

is there perhaps a longer written step-by-step how to use the SS, other than the 1st tab ??
Go to Section 7: Special Events and there are places for two inheritances. Enter your inherited IRAs here with their current balances and your current age, replacing the 77 and 72 used in the Example data. In Section 1 enter your current age +1. Unfortunately this may not result in a correct RMD calculation as the model is not programmed to model the RMD for an existing inherited IRA. [Edited from original comment.]

All the instructions for using the model are in cell comments on the Setup page. They describe how each input setting is used and what data should be entered.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

stvyreb
Posts: 93
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jan 16, 2019 12:31 am

are you aware of anyone I might hire/consult with that uses this tool

I am afraid the 'cell comments' on the summary page, are not allowing me to decypher all of what should go where for my situation,

have you ever considered maybe a youtube tutorial of some sort ?

I can enter some of the information, but I can't see how it all is suppose to tie together , nor fit what goes where with the terms being used , I do see something about the ACA in there, but there is no where to enter the subsidy for example, and any roth conversion I would do now, would greater impact my subsidy , etc etc


I am getting various negative amount errors, but don't see what is causing it, nor what it's even referenced is "negative" sigh

Alert! Portfolio has negative beginning yr balances in one or more accounts for 38 years
Alert! Base case has negative balances for 38 years


no idea

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

Re: Retiree Portfolio Model

Post by Zephavest » Wed Jan 16, 2019 6:51 am

stvyreb wrote:
Wed Jan 16, 2019 12:31 am
I am getting various negative amount errors, but don't see what is causing it, nor what it's even referenced is "negative" sigh

Alert! Portfolio has negative beginning yr balances in one or more accounts for 38 years
Alert! Base case has negative balances for 38 years
On your Setup tab, look towards the top of the page, make sure your settings are:

Minimum taxable balance $$$
Use auto withdrawals? y

I only use Auto Withdrawals to avoid balances going negative, simplifies the tool use. But other reasons for negative balances are you run out of money, or your expenses exceed your assets and income.

Hang in there, keep plugging, the tool does have a high learning curve but you can do it and benefit from the insight you gain into your own personal finances. You really don't want to outsource this.

Post Reply