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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Nov 23, 2016 11:07 am

gponym wrote:Thanks for making and sharing this great tool, BigFoot. I just got v16.3 on 11/20 from the official site.

I noticed something curious while setting up my situation: The Roth 2 withdrawal appears to replicate the Roth 1 withdrawal in the Base Case worksheet.

Thank you for this detailed problem report. I had indeed failed to properly set the references for the recently added Roth 2 account. I have now fixed that and a related use of the new account in the Return Rates and Allocation section.

The version 16.3 has been updated and those with a second existing Roth IRA should update or wait for the Jan 1 2017 version.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

gponym
Posts: 3
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Postby gponym » Thu Nov 24, 2016 12:12 am

That's nice, thanks for fixing so soon. And glad you could make sense of my post, it wasn't the picture of clarity.

Reading between the lines, it sounds like no great harm will come from halving the Roth 1 withdrawal starting amount in the early November v16.3. Assuming that, I'll wait to update. In the meanwhile I hope to stumble on an easy way to transfer settings from one version Setup page to another.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Nov 25, 2016 9:01 am

gponym wrote:That's nice, thanks for fixing so soon. And glad you could make sense of my post, it wasn't the picture of clarity.

Reading between the lines, it sounds like no great harm will come from halving the Roth 1 withdrawal starting amount in the early November v16.3. Assuming that, I'll wait to update. In the meanwhile I hope to stumble on an easy way to transfer settings from one version Setup page to another.

Your post did a great job of pinpointing the problem and helping me quickly fix it. I think, based on your explanation, that you could just set Roth 1 withdrawal at half and get reasonably close results.

The easiest way to transfer input data from one model to a newer one is to use the Excel "Compare Side by Side" feature that is in the Window menu item. Open the new model, clear the input items using the macro or manually to avoid confusion, then open your existing model. Set the view on both models to the same position, then activate the Side by Side feature. You can then scroll down the Setup page and enter the values in the new model while viewing your existing entries in the old model. It's a bit tedious but makes it pretty easy. That said, I only update my personal model once a year in January, unless a significant problem is found which is thankfully rare these days, thanks to all you Beta testers. :wink:
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

gponym
Posts: 3
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Postby gponym » Fri Nov 25, 2016 5:21 pm

Thanks for the tip about filling out Setup for a new model.

I have found a state tax anomaly. Still using early Nov v16.3. I saw it in Full Case in normally hidden State Brackets row 204. I did not play around to see if any other rows show anomalous behavior.

Here are values over 5 years for rows that seem to capture the weirdness:

Code: Select all

ROW DESCRIPTION             2019  G  2020 H  2021 I  2022 J  2023  K
201  State Taxable Income    27,000  25,600  26,000  25,600   36,900
203  5.25% bracket to 24680   1,300   1,400   1,400   1,300    1,500
204  7.05% bracket to 81080   4,100   4,200   1,800   1,800    4,500
206  State taxes by bracket   5,400   5,600   3,200   3,100    6,000


The figures look strange in row 204, columns G-J. Other values scan fine. What I wonder is, how can a minor change in State taxable income lead to such a major change in row 204?

(State income tax figures from rows 191-200 echo the small differences in row 201 between columns H and I.)

I use the "bracket" method for State tax. It does not change in the period shown (it changes in 2037).

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Nov 26, 2016 8:21 am

gponym wrote:I have found a state tax anomaly. Still using early Nov v16.3. I saw it in Full Case in normally hidden State Brackets row 204. I did not play around to see if any other rows show anomalous behavior.

That is very odd. I've reviewed the calculations and don't see any problem and the example data doesn't show this type of behavior. I've sent you a PM asking for your model to examine.

Update: My Example data didn't reveal the problem because it used the full amount of the state second tax bracket, rather than just a portion which would have revealed the calculation error.
Last edited by BigFoot48 on Sat Nov 26, 2016 6:02 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Nov 26, 2016 6:00 pm

The current version 16.3 has been updated as a result of the state tax bracket problem reported by gponym. Many thanks to him for assisting me in resolving this. The impact on the ending portfolio value for the Example data was only $2,000, or 0.01%, so I'm just updating the current version. I recommend users wait for the 2017 to update, especially those not using the state bracket method of estimating state taxes.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

mjf55
Posts: 53
Joined: Sun Oct 23, 2016 11:46 am

Re: Retiree Portfolio Model

Postby mjf55 » Sat Dec 03, 2016 3:36 am

Hi, First off, I am very impressed with the Retiree Portfolio Model. It has taken me several hours to learn to use it. I still do not fully understand it all, but I am working on it. So THANK YOU for the enormous effort you have put into this.

I am using the latest version, 16.3, downloaded 11/26 at 6pm eastern time. I am using Excel 2013 and that MAY come into play.
I have discovered what I think is a problem, in the state income tax area, but this time using percent ( not brackets). I see what I think an error. ( Of course it could be a seat - keyboard interface problem :D )
1 - Even though I include a standard deduction, the tax applied is against the State AGI, Not State Taxable income.
In the Full Case tab, in cell F208 ( just showing first year, but it happens to all ) the formula is

Code: Select all

=ROUND(IF(AND(State_Move_Age<=F$4,State_Move_Age<>0),+F$195*State_Rate2,+F$195*State_Rate)/Round_To,0)*Round_To*IF(Exclude_Taxes="y",0,1)

Note that the tax is calculated by referring to cell F$195 (State AGI) ( in bothe the TRUE and FALSE evaluation of the IF) but I believe it should be F$201(State Taxable Income). If I change it to F$201, then the tax is what I expect.

EDIT - Thought there was a second issue, but I must be late and it was my problem

Hope you can help. I can set up a single bracket to workaround both issues.

Well Good Luck, and Thanks again for a great tool.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Dec 03, 2016 9:23 am

mjf55 wrote:Hi, First off, I am very impressed with the Retiree Portfolio Model. It has taken me several hours to learn to use it. I still do not fully understand it all, but I am working on it. So THANK YOU for the enormous effort you have put into this.

Thank you. It has been a bunch of work, but I found all the existing models lacking in detail and transparency and I didn't want to trust my personal future to their predictions, thus RPM was born when I needed to model Roth Conversions impact. (Version 1.0 is quite amusing simple when I view it now. If anyone wants to see: https://www.dropbox.com/s/inlw8lvpg7tge ... 0.xls?dl=0 )

The simple state income tax calculation is working as designed. This method was a way to approximate state taxes in the initial years of the model as a simple percent of Federal AGI. The cell comment explains it:

Two methods are available for forecasting state income tax for your current state and a future state if a move is planned.

Simple: A percentage of state Adjusted Gross Income is used to approximate your state income tax. State AGI is Federal AGI adjusted for how SS benefits and Pension 1 income are taxed in the state.

Detailed: Tax rates and brackets (limited to three) along with personal exemptions and standard deductions amounts are used to calculate income taxes. These are applied to the state AGI amount.


As you have figured out, just use the bracket method with the same percent for all brackets.

As for the learning curve, I am trying to simplify things a bit to ease the process. As an old colleague use to say, it's a bit of a "dogs breakfast" right now. Life can be complex, but I'll do my best to make entering and understanding the financial portion of it a little easier.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

mjf55
Posts: 53
Joined: Sun Oct 23, 2016 11:46 am

Re: Retiree Portfolio Model

Postby mjf55 » Sat Dec 03, 2016 11:37 am

Thanks for the response BigFoot48. As usual, it time to RTFM; Read The Fine Manual. As far as learning curve, well that just par of the fun. :sharebeer

User avatar
Felix Mulier
Posts: 1
Joined: Thu Dec 01, 2016 9:55 am

Re: Retiree Portfolio Model

Postby Felix Mulier » Wed Dec 07, 2016 6:08 pm

Thank you for this great model! I'm fairly new to Portfolio Planning, but your tool was most helpful (once I got over the feeling of drinking from a fire hose!) :wink:
I generally avoid temptation unless I can't resist it. | | Felix

Zephavest
Posts: 13
Joined: Thu Jan 10, 2013 9:09 am

Re: Retiree Portfolio Model

Postby Zephavest » Fri Dec 09, 2016 10:49 am

Hi Bigfoot,

I figured out how to avoid any state tax issues, we are moving to Texas when I retire next year, where there is 0% income taxes, but they still get you on your property tax, oh well.

Here is an idea for your next January release. Would it be possible to add an item to Section 7. Special Events? The idea is to compare alternate investment ideas, the scenario would be along the following lines. (I understand the "Expenditures" item helps on this but I'm not sure it shows the entire picture.)

In 2017 purchase new home for $400,000 vs purchase new home for $500,000. The comparison would be looking at the what if the user pulls out an extra $100,000 from their portfolio to ""invest"" it into a higher cost home then you would look at the down line impacts to the overall size of the portfolio growth as you age.

The comparison could be along the lines of:

x years for the portfolio investment dollars to grow at x%
vs
x years for the portfolio, less $100,000, investment dollars to grow at x%

But to offset this, you would anticipate the extra $100,000 invested in the home would grow at x%.

Summary, so while a three fund portfolio may grow the $100,000 at 6% vs the Home may only grow at 3%, but if you anticipate a large inheritance left over, why not spend more on yourself to enjoy a nicer home now. This could be used for other what-if ideas, invest in a rental home, etc.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Dec 09, 2016 11:38 am

Zephavest wrote:Here is an idea for your next January release. Would it be possible to add an item to Section 7. Special Events? The idea is to compare alternate investment ideas, the scenario would be along the following lines. (I understand the "Expenditures" item helps on this but I'm not sure it shows the entire picture.)

Thanks for that suggestion. I think it's a very interesting idea, and probably one most people have not considered. However, I'm trying to keep the model as simple as possible (see "fire hose" description above), and think this calculation would have limited users.

I suggest using the existing Asset Sales and Expenditures section to model this by assuming a net home purchase and eventual sale, recording the result, then changing to a different set of entries and comparing that result. Keep in mind that each of the four events provided for here can be either cash inflow or outflow, depending on the sign.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

Zephavest
Posts: 13
Joined: Thu Jan 10, 2013 9:09 am

Re: Retiree Portfolio Model

Postby Zephavest » Fri Dec 09, 2016 11:49 am

BigFoot48 wrote:I suggest using the existing Asset Sales and Expenditures section to model this by assuming a net home purchase and eventual sale, recording the result, then changing to a different set of entries and comparing that result. Keep in mind that each of the four events provided for here can be either cash inflow or outflow, depending on the sign.


Excellent suggestion, thank you, I was not sure how to model this and I had overlooked the sale item as I was focused on the expenditure item.

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

Re: Retiree Portfolio Model

Postby LadyGeek » Sun Dec 11, 2016 2:14 pm

The bottom of every wiki article has a "Help improve this page" form for submitting feedback, no login is required.

We just received this feedback: Feedback post on Retiree Portfolio Model
it was good

Administrative: I added Retiree Portfolio Model to the "Retirement planning" navigation menu and added the menu to the bottom of the page.

I also added the "Retirement planning" category to the page, which will group it with similar pages. This is important, as the Site navigation page is grouped by categories.

The result is that it will be easier for readers to find this article, as well as other relevant articles.
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.

brdubbs01
Posts: 8
Joined: Wed Jul 24, 2013 10:03 am

Re: Retiree Portfolio Model

Postby brdubbs01 » Tue Dec 27, 2016 4:43 pm

I am on a Macbook with no Excel. Only have access to Google sheets online. I hit the link to download RPM hoping for a menu option to download into Google Sheets ut just got Apples Preview program opening the first page description of RPM.

To find instructions, I google searched "RPM and Google sheets" but did not see anything.

Is RPM still compatible with Google sheets? If so, how would I go about downloading it?

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Dec 27, 2016 5:05 pm

brdubbs01 wrote:Is RPM still compatible with Google sheets? If so, how would I go about downloading it?

I don't have Apple so I can't test it, but you should be able to download the file to your computer, then open Sheets online and use the file-import-upload menu to load it into Sheets. It will run there, a bit slow, and with no macro support. If LibreOffice is available for Apple I would recommend trying it as the Calc program runs RPM very well in Windows.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

brdubbs01
Posts: 8
Joined: Wed Jul 24, 2013 10:03 am

Re: Retiree Portfolio Model

Postby brdubbs01 » Tue Dec 27, 2016 5:54 pm

Got it done.

I had to open Google docs online to get the file-import-upload menu to appear.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sun Jan 01, 2017 12:06 pm

A new version of the Retiree Portfolio Model model is available which includes 2017 tax data and numerous minor updates. I recommend all users download it. Minor update June 13.

Version 17.0 can be downloaded from Dropbox via this link: https://www.dropbox.com/s/xzawrbf773a9r ... 0.xls?dl=0 [Updated Jan 12 after fixing macro name conflict problem. Updated Jan 13 to fix Fed Exemption error. Updated Jan 16 to fix Clear Entries error caused by Jan 12 fix. Updated Mar 18 to fix Summary page where too many columns were being hidden by the macro. Updated Apr 20 to fix calculation of state AGI that was adding back wrong Federal tax-exempt/non-taxed amount. Updated Jun 7 fixing "Year Single Rate Starts" 0 error that forced use of the single rate if no "End Age" was entered for either person. Thanks to sandramjet for finding this. Updated June 13 - fixed beginning of year balances for IRAs inherited in first model year to zero. Was doubling up the amounts. Thanks to BH jeffaron for discovering this malfunction.]

A new version of the model which excludes the Base case is also available for those who don't have a need for Roth conversion or SS benefits analyses that the standard model provides. While the hiding of the Base case can be done via macros, those who don't want to use macros or use programs that don't support macros, may find this a good alternative. This provides a simpler version of the model and most retirees post-age 70 will probably find this easier to use.

Version 17.0xb (excluding Base) can be downloaded from Dropbox via this link: https://www.dropbox.com/s/my8uzv1n2hvo4 ... b.xls?dl=0 [Updated as detailed above.]

Changes in 17.0 include:
>Updated tax rates and brackets to 2017 amounts. Set tax factors escalation rate to 2.2% (was 2.5%).
>Added option to have additional personal exemptions. Modified tax factor escalation method resulting in slightly different forecast amounts.
>Added macros to hide little used pages (ORP page now hidden by default), and improved hiding Base case amounts and comparisons.
>Added calculation of Federal income tax impact of Roth conversion on the RMD of the IRA used in the conversion.
>Modified "copy results" macros on Setup page to also copy settings and data to their respective storage area on the Results page.
>Updated format and corrected totals for omitted factors and suppressed zeros to make for easier reading on Summary page
>Fixed calculation of IRA earnings in final year if account is zero. RMD assumed to be on Jan 1 now for earnings calculation.
>Fixed earnings calculation for final year if any account has a balance.

If anyone finds a problem please PM me, or post it.

A happy and prosperous new year to all!
Last edited by BigFoot48 on Tue Jun 13, 2017 10:58 am, edited 8 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

rgs92
Posts: 1099
Joined: Mon Mar 02, 2009 8:00 pm

Re: Retiree Portfolio Model

Postby rgs92 » Sun Jan 01, 2017 12:34 pm

Why is all this work more useful than Firecalc? Do state and property taxes really make that much of a difference?

Zephavest
Posts: 13
Joined: Thu Jan 10, 2013 9:09 am

Re: Retiree Portfolio Model

Postby Zephavest » Sun Jan 01, 2017 7:12 pm

BigFoot48 wrote:A new version of the Retiree Portfolio Model model is available which includes 2017 tax data and numerous minor updates. I recommend all users download it.

A happy and prosperous new year to all!


HI Bigfoot,

Happy New year to you as well, thank you so much for all your continued hard work on RPM. I look forward to setting up the new version 17.

Zephavest
Posts: 13
Joined: Thu Jan 10, 2013 9:09 am

Re: Retiree Portfolio Model

Postby Zephavest » Fri Jan 06, 2017 9:13 am

rgs92 wrote:Why is all this work more useful than Firecalc? Do state and property taxes really make that much of a difference?

I've not tried Firecalc but to answer your question about state and property taxes, I'll give you two examples:

California Personal Income Tax Rate (my current state):
https://www.tax-brackets.org/californiataxtable
Take 10% as the average, this is a significant percent of the portfolio not to be accounted for.

Texas Property Tax Rate (my retirement state):
http://www.davedowns.com/dallas-property-tax.php
The North Dallas area we are moving to has a property tax rate of 2.5% to 3.1%. This is based on the value of your home, for me the out of pocket taxes is about equivalent to what we are paying in personal income tax in California.

So you can see, for my example, RPM lets me calculate the impact of these expenses, which allows me to see a clearer picture of my overall portfolio health.

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

Re: Retiree Portfolio Model

Postby jkrm » Tue Jan 10, 2017 10:49 am

Hi, Bigfoot. Happy New Year and thank you again for this great spreadsheet model.

I just downloaded and started to use version 17. Excel keeps popping up a box with this message: "The name, QCD1, either conflicts with a valid range reference or is invalid for Excel. The name has been replaced with _QCD1." There is a second sentence about updating manually any references in VBA code, then two buttons labelled "OK" and "OK to all". I have clicked both of the buttons many times, but the message keeps popping up every few minutes. I am using Excel 2010 and saved the workbook as a .xlsm file, in case that might be the problem.

Also a suggestion. Instead of special events happening in single years, maybe you could add a capability for an event to start in a particular year and repeat every year thereafter, up to a given end year. And be able to indicate an independent inflation rate for that event. (I am thinking about healthcare costs!)

Thanks again for all of your work on this.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Jan 10, 2017 11:17 am

jkrm wrote:I just downloaded and started to use version 17. Excel keeps popping up a box with this message: "The name, QCD1, either conflicts with a valid range reference or is invalid for Excel. The name has been replaced with _QCD1." There is a second sentence about updating manually any references in VBA code, then two buttons labelled "OK" and "OK to all". I have clicked both of the buttons many times, but the message keeps popping up every few minutes. I am using Excel 2010 and saved the workbook as a .xlsm file, in case that might be the problem.

Also a suggestion. Instead of special events happening in single years, maybe you could add a capability for an event to start in a particular year and repeat every year thereafter, up to a given end year. And be able to indicate an independent inflation rate for that event. (I am thinking about healthcare costs!)

This is apparently a not uncommon problem in using a model developed in Excel 2003 in a later version, and it appears to result from the number of columns and their labels going from 256 columns A-IV in 2003 to 16384 columns A-XFD in Excel 2007 and newer. The QCD is being seen as a column by Excel and it gets confused. I will see if I can find this and fix it but until then you might want to turn off macros to use the model.

Edit: Found it! It's the label for the just added Qualified Charitable Distribution use-selection cell name.

I do provide for an increase in expenses at a selectable age but with the same inflation rate, so this can be used to model future healthcare or assisted living costs. I will see if this can be improved without complicating the model too much.

Thanks!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Jan 11, 2017 8:56 am

jkrm wrote:I am using Excel 2010 and saved the workbook as a .xlsm file, in case that might be the problem.

Please download this fixed version and see if you still get that error. Thanks. See below.
Last edited by BigFoot48 on Wed Jan 11, 2017 4:42 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

hollerg
Posts: 1
Joined: Wed Jan 11, 2017 3:21 am

Re: Retiree Portfolio Model

Postby hollerg » Wed Jan 11, 2017 2:22 pm

So working through the first time and finding this a great since I can see what is being done :sharebeer .

In testing the 17v "fix", there is still a substitution being done by Excel for QCD1 when saved to enable macros in Excel 2013. A "find" for QCD1, for the whole worksheet shows that the Results tab at D693 has a formula +QCD1. It gets renamed, despite it no longer being a range name. I suppose it needs the updated Name?

As a check I used the Name Manager, filtered for "names with errors", which found that name No_negative_final_balances refers to an invalid location on the Setup tab. I am not experienced enough to know if this is a significant problem

Lastly do the formulas in H93 and H94 need to be swapped, so that the end age is in the correct place?

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

Re: Retiree Portfolio Model

Postby jkrm » Wed Jan 11, 2017 3:55 pm

BigFoot48 wrote:
jkrm wrote:I am using Excel 2010 and saved the workbook as a .xlsm file, in case that might be the problem.

Please download this fixed version and see if you still get that error. Thanks. https://www.dropbox.com/s/d6s25h9llk2dr ... x.xls?dl=0


Not getting the error with this version. But strangely, I am not getting it with the original one anymore, either! I am also getting slightly different final balances, which is driving me a little bit crazy, but I suspect I've just transcribed some input values incorrectly. I'll look at it again tomorrow and probably see it right off.

EDIT: I just tried saving in Excel 2010 as an xlsm file, and I did get the same error message as before. I'd forgotten that the error didn't show up till I saved in that format. Sorry.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Jan 11, 2017 4:40 pm

hollerg wrote:So working through the first time and finding this a great since I can see what is being done :sharebeer .

In testing the 17v "fix", there is still a substitution being done by Excel for QCD1 when saved to enable macros in Excel 2013. A "find" for QCD1, for the whole worksheet shows that the Results tab at D693 has a formula +QCD1. It gets renamed, despite it no longer being a range name. I suppose it needs the updated Name?

As a check I used the Name Manager, filtered for "names with errors", which found that name No_negative_final_balances refers to an invalid location on the Setup tab. I am not experienced enough to know if this is a significant problem

Lastly do the formulas in H93 and H94 need to be swapped, so that the end age is in the correct place?

Thanks for testing it and finding those lingering name problems. I have fixed them and would like another test at everyone's convenience. Here's the link: removed as the fix was successful

I loaded it in Excel 2013 and saved to an xlsm file type and did not get any errors.

The SS ending age at H93 and H94 are correct, even though they seen counter-intuitive. Please PM with how your SS benefits are planned as there may be a situation I have not anticipated that is causing the problem you have. Thanks.

Model note on the SS ending age:
The age of the person whose benefits will be ending based on the end age entered in the Ages Factors section and the benefit amounts.

For example, if the You person dies at 85 and has the larger benefit, that age will be shown here in the Spouse's benefit line, indicating those benefits will end. If they have smaller benefits, such as spousal, then the end age will be their age, as their benefits will end, rather than being transferred to the surviving spouse.

A person getting spousal benefits, rather than their own higher self benefits, typically assume their spouse's benefits upon that person's death. So the last year of either benefit is the other person's age.
Last edited by BigFoot48 on Thu Jan 12, 2017 1:06 pm, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby jkrm » Thu Jan 12, 2017 8:07 am

I just tried the second fix - no issues. Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Jan 12, 2017 8:57 am

jkrm wrote:I just tried the second fix - no issues. Thanks!

Great! The 17.0 and 17.0xb models have been updated with fixed macro name. Anyone using Excel 2007 and newer may want to upgrade to avoid this Excel warning message: "The name, QCD1, either conflicts with a valid range reference or is invalid for Excel. The name has been replaced with _QCD1." However, if a user is not getting the name conflict warning then there's no reason to upgrade.

Thanks for the help and hollerg too!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

gypsydavey
Posts: 5
Joined: Wed Nov 02, 2016 10:56 am

Re: Retiree Portfolio Model

Postby gypsydavey » Fri Jan 13, 2017 9:38 am

Just getting up to speed with this spreadsheet. Nice work.

I have a question about the exemption amount displayed under the Federal Taxable Income calculations. Despite setting filing status to "married", the line item for exemptions and deductions seems to include an amount based on a single exemption (4050).

As I said, I'm just getting up to speed so there may be some other setting that affects this that I don't understand yet. Also using LibreOffice calc so not sure if that's a factor.

Thanks

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Jan 13, 2017 10:15 am

gypsydavey wrote:I have a question about the exemption amount displayed under the Federal Taxable Income calculations. Despite setting filing status to "married", the line item for exemptions and deductions seems to include an amount based on a single exemption (4050).

Thanks! I do see this occur when there is no early death year entered for either person. I will fix this for a future release but in the meantime just enter an End Age for either person in the "1. Ages and Year Factors" section. Using the last age of either person will also prevent this error if you don't want to model the early death of a spouse.

Fixed - model updated Jan 13.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

gypsydavey
Posts: 5
Joined: Wed Nov 02, 2016 10:56 am

Re: Retiree Portfolio Model

Postby gypsydavey » Fri Jan 13, 2017 6:00 pm

Your workaround worked!

I'll download your fixed version and verify later.

Thanks

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

Re: Retiree Portfolio Model

Postby LadyGeek » Sun Jan 15, 2017 3:03 pm

New member cgrinst1 has a question about future date Roth conversions. I'm not sure if it's about the spreadsheet or something else, see: Setting Withdrawals from Converted Roth in Retiree Portfolio Model
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.

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

Re: Retiree Portfolio Model

Postby sandramjet » Mon Jan 16, 2017 12:00 am

I was trying to model some SS scenarios and not sure I'm doing it correctly.
Here's what I want to model:
My benefit to start at Age 70
My spouse starts benefit at age 62, continues to my age 70
At age 70, spouse switches to my spousal benefit
At age 85, spousal benefit switches to survivor benefit.

If I select "automatic spouse benefit" as "y" it (correctly) gives an error message about spousal benefit starting before mine, although it looks to me like it fills in the spousal amount early anyway.
If I select "n" for that, it uses my spouses benefit correctly, but never switches to the spousal benefits. It does switch to survivor benefit at the end age *85), however.

Am I doing something incorrectly, or is this something that isn't modeled by just the inputs on the setup page? In that case, I presume I could model it manually by changing the results on the full/base case pages explicitly?

Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon Jan 16, 2017 8:55 am

sandramjet wrote:Am I doing something incorrectly, or is this something that isn't modeled by just the inputs on the setup page? In that case, I presume I could model it manually by changing the results on the full/base case pages explicitly?

Thanks!
While there's likely some situations the model can't handle, I believe it will work for yours. Just put your spouse's early benefits in the Additional Benefits section. And manually changing the results is always a good solution if the model is not handling the situation properly. Just find the appropriate yearly results line on the case(s) page, unprotect the page, and replace the formulas with your own amounts.

I think this example is the situation you want to model and this should work:
Image
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby sandramjet » Mon Jan 16, 2017 11:28 am

THanks, that should do it. Once again, I'm impressed by how much this tool does!

I found one more issue, again not sure if this is my problem... I decided to download the latest version. Did that, but then saved it as an xlsm file in Excel 2010. When I then tried to run the "clear" macro on that, I got an error:

Microsoft Visual Basic
Run-time error ‘1004’:
The cell or chart that you are trying to change is protected and
therefore read-only.
To modify a protected cell or chart, first remove protection using the
Unprotect Sheet command (Review tab, Changes group). You may be
prompted for a password.
End Debug Help

I then tried downloading and saving in original format, not xlsm. This time I got this error when I tried to use the clear macro:
Run-time error ‘1004’:
The specified dimension is not valid for the current chart type.
End Debug Help

Is this a known issue, a new bug, or (most likely) a user error?

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon Jan 16, 2017 12:12 pm

sandramjet wrote:I found one more issue, again not sure if this is my problem... I decided to download the latest version. Did that, but then saved it as an xlsm file in Excel 2010. When I then tried to run the "clear" macro on that, I got an error:

Oops, sorry about that! When I fixed the label for the QCD1 cell to avoid a problem with current versions of Excel I forgot that cell reference was also used in the Clear Entries macro. All fixed now.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

User avatar
Peter Foley
Posts: 3883
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Re: Retiree Portfolio Model

Postby Peter Foley » Tue Jan 17, 2017 4:50 pm

BigFoot 48

Something to consider, although it may not be feasible . . . .

I've posted on a few forum threads regarding my use and opinion of various calculators. With regard to free calculators I have found, only the Retiree Portfolio Model and ORP provide multi-year guidance on Roth conversions. I find yours to be the more flexible and more complete. There is a learning curve for both of them and I have run multiple scenarios on both of them.

I wonder if the one aspect or ORP that I prefer, their table layout reports, could be replicated on additional tabs of the Retiree Portfolio Model?

The reports I find most useful in ORP are the asset balance report, and income by federal tax bracket. The asset balance report could be simplified and aggregated into a net worth report. Versions of these are created within your spreadsheet, but to me seem less intuitive than the ORP reports.

Food for thought . . .

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Jan 17, 2017 5:34 pm

Peter Foley wrote:The reports I find most useful in ORP are the asset balance report, and income by federal tax bracket. The asset balance report could be simplified and aggregated into a net worth report. Versions of these are created within your spreadsheet, but to me seem less intuitive than the ORP reports.

Food for thought . . .

Thanks for those suggestions. I am concerned about the amount of data the model generates and how to present it in the most understandable and user-friendly manner. The Summary page was originally set up to emulate how ORP presents some of its data, and I will study what you are suggesting and see how that might be added to ether this page or a new tab.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

chuckb84
Posts: 133
Joined: Wed Oct 21, 2015 10:41 am

Re: Retiree Portfolio Model

Postby chuckb84 » Mon Jan 23, 2017 10:55 am

I just started using this and am still figuring things out. One puzzler is that the case I set up has almost no money in the taxable account and yet the model somehow keeps putting money into that in future years. Why? Is there a way to turn that off via the setup? The SS seems to flow money from the IRA into taxable? Why? If that isn't it, where are the funds coming from?

Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon Jan 23, 2017 11:21 am

chuckb84 wrote:I just started using this and am still figuring things out. One puzzler is that the case I set up has almost no money in the taxable account and yet the model somehow keeps putting money into that in future years. Why? Is there a way to turn that off via the setup? The SS seems to flow money from the IRA into taxable? Why? If that isn't it, where are the funds coming from?

Thanks!

Withdrawals from IRAs, income from SS and salaries, earnings from the taxable account balance, and a few other things, all flow into the taxable account. You can view everything going into taxable on the case pages in the "Earnings and Income" and "Account Changes" sections, and at line 419: Taxable Account, which details all the changes going on in the taxable account.

There's no way to change that.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

chuckb84
Posts: 133
Joined: Wed Oct 21, 2015 10:41 am

Re: Retiree Portfolio Model

Postby chuckb84 » Mon Jan 23, 2017 12:40 pm

BigFoot48 wrote:
chuckb84 wrote:I just started using this and am still figuring things out. One puzzler is that the case I set up has almost no money in the taxable account and yet the model somehow keeps putting money into that in future years. Why? Is there a way to turn that off via the setup? The SS seems to flow money from the IRA into taxable? Why? If that isn't it, where are the funds coming from?

Thanks!

Withdrawals from IRAs, income from SS and salaries, earnings from the taxable account balance, and a few other things, all flow into the taxable account. You can view everything going into taxable on the case pages in the "Earnings and Income" and "Account Changes" sections, and at line 419: Taxable Account, which details all the changes going on in the taxable account.

There's no way to change that.


Thanks for that. However, I don't get the logic. "Withdrawals from IRAs, income from SS and salaries, earnings from the taxable account balance" are what I'm living on to finance expenditures, so why does the SS put them into the taxable account? There's something fundamental here that I'm not getting...

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

Re: Retiree Portfolio Model

Postby sandramjet » Mon Jan 23, 2017 12:52 pm

I think you are thinking that the "taxable account" is what you hold at a brokerage as an investment. Don't think of the "taxable" account as just an "investment" account. Remember that as far as the IRS is concerned, your checking account is also a "taxable account". So all of the money that is available for paying expenses flows into that account, and that account has the taxes calculated on it.

TVBogle
Posts: 14
Joined: Wed Jun 17, 2015 9:20 am

Re: Retiree Portfolio Model

Postby TVBogle » Thu Feb 02, 2017 1:15 pm

Hi BigFoot,
First, I would like to thank you very much for adding the "Additional Exemptions" option in v17.
Second, while manually copying my inputs from v16.3 to v17, I wondered if there was a better way to save time and minimize errors.
Is it possible to have a macro to export (write) the inputs to a text file and a 2nd macro to import(read) that text file into your spreadsheet?

Like this : Inputs of OLD Spreadsheet > Export Macro > Text File > Import Macro > Inputs of NEW Spreadsheet

I'm not an excel expert so please excuse my ignorance.
Again, many thanks for this wonderful tool.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Feb 02, 2017 2:13 pm

TVBogle wrote:Is it possible to have a macro to export (write) the inputs to a text file and a 2nd macro to import(read) that text file into your spreadsheet?

Thanks! I've thought about doing that as the input data can be stored (user action) on the Results page. It may be possible to copy that data and import it, but it might be a detailed programming and maintenance problem. I'll look into it some more. In the meantime, use the Excel "Compare Side by Side with" feature (under Window menu item) to make copying existing entries into a new model a little bit easier.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

cgrinst1
Posts: 3
Joined: Sun Jan 15, 2017 2:31 pm

Retiree Portfolio Model

Postby cgrinst1 » Sat Mar 18, 2017 1:06 pm

Thanks for creating this model! Here's my question: In the summary page, I find that certain numbers are replaced by ``###". It is not the case that the numbers being replaced are too long; in my case, most of the numbers being replaced are four digits in length. When I click on a particular ``###", the replaced number does appear at the right in the bottom margin. More curiously, in the column where this replacement occurs, some of the numbers (also four digits long) are NOT replaced; they appear as numbers. The specific column where this occurs is titled ``Roth" and appears under the heading ``Account Balances - Start of Year". In the next column to the right, titled ``Roth Conv", the four-digit numbers all appear; none of them are replaced by ``###".

As a more general question, is there any way to change the default settings for the number of digits allowed in a given column on the summary page?

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Mar 18, 2017 2:45 pm

cgrinst1 wrote:As a more general question, is there any way to change the default settings for the number of digits allowed in a given column on the summary page?

Thanks for reporting that odd result as it revealed another problem with the "hide rows/columns" macro on the Summary page. Because some titles had been formatted with "merger and center", the hide command was hiding more than it should, including the setting that sets the rounding on the Summary page. The model has been updated to fix that. Users should "show" the hidden data to see more summary data, but only need to update if you want a correct view in the "hidden" mode.

As for the ### problem, as usual LadyGeek knows exactly what it is. The columns widths have been set to accommodate the example data, but if you have a large Roth balance it is causing this problem. Unprotect the page, show borders and lines, and increase the width of the column.

I have updated the existing model 17.0 to accommodate larger portfolio amounts. Download again if one or more accounts need more room on the Summary and Results pages.
Last edited by BigFoot48 on Sat Mar 18, 2017 3:44 pm, edited 6 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby LadyGeek » Sat Mar 18, 2017 2:50 pm

When the column widths are too narrow to display the numbers, "###" are displayed instead. Widen the column and the numbers should appear.
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.

sidneyinplanning
Posts: 1
Joined: Mon Nov 14, 2016 9:23 pm

Re: Retiree Portfolio Model

Postby sidneyinplanning » Thu Mar 30, 2017 11:54 pm

Where in the model to set the retirement year? My wife and I are still earning income so there is no need for IRA or 401K distribution till several years later.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Mar 31, 2017 9:27 am

sidneyinplanning wrote:Where in the model to set the retirement year? My wife and I are still earning income so there is no need for IRA or 401K distribution till several years later.

The model is designed for those in or near retirement, with a 40 year limit on the forecast period. As it uses current year tax rates and brackets, using the current year and your current age gives the best near-term calculation of Federal taxes, but is not required. (I'm fairly certain that the calculated taxes for, say 2037, are wrong, but that's the nature of such things.)

If your retirement year is a few years from now, you can enter your current age and working income and expenses, and an end age for your working income - your retirement age. (Note, four pension and other income factors are provided that can be used to model pre-retirement income.) And a "change age and percent" can be entered to model lower retirement expenses. Or, just use your future retirement age and year and accept that taxes will be likely understated or overstated.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser


Return to “Personal Finance (Not Investing)”

Who is online

Users browsing this forum: Clever_Username, DEZ1, gfmels, Nate79, Palatineman, spammagnet, strongboy2005, zakath47 and 65 guests