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: 2437
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: 2437
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: 2437
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: 2437
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: 46
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: 2437
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: 46
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: 10
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: 2437
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: 10
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: 35150
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: 6
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: 2437
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: 6
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: 2437
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.

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.]

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 Fri Jan 13, 2017 12:45 pm, edited 3 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

rgs92
Posts: 836
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: 10
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: 10
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: 29
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: 2437
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: 2437
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: 29
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: 2437
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: 29
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: 2437
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: 3
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: 2437
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: 3
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: 35150
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: 78
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: 2437
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


Return to “Personal Finance (Not Investing)”

Who is online

Users browsing this forum: beerloin, bowtieman81, cmeretire, Former Usher, Google [Bot], inmymind, JDCarpenter, Justalearner, Kosmo, MrBroMan, oldhobo, onourway, Sbashore, Slime, Stewie, Sweetfo and 109 guests