Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Post Reply
RandyVT
Posts: 1
Joined: Tue Jan 03, 2012 9:36 am

Re: Retiree Portfolio Model

Post by RandyVT »

Alas, I am a Mac OS X user. If there is someone on the forum who can point me to an Excel 2011 Version 14.5.9 (151119) compatible version of this Workbook, I'd love to dive in. Or, if someone has driven down the road with Numbers, I'll try that. But Windows . . . nope, I accept that I can't learn everything and own every kind of gadget in this life.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

The model has been tested and can be run with Excel 2003-2013, LibreOffice Calc, Kingsoft Spreadsheet*, Excel Online* and Google Sheets*. LibreOffice runs the macros, the others* may not. I see Libre has an OS version so you might try that. (Macros are not necessary to use the model, but make some tasks easier.)

The 2016 version will be coming out soon. No major changes but a few new features and a few fixes.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

A new version of the Retiree Portfolio Model model is available for download. I recommend all users download it as it contains some minor calculation corrections.

Version 16.0 can be downloaded from Dropbox via this link: replaced by v16.2

Changes in 16.0 include:
>>Updated tax rates and brackets to 2016 amounts.
>>Modified cell and range names and macros to not conflict with Excel 2007+ column labels.
>>Fixed calculation of taxable account earnings to include in current year special event cash flow (home sale, etc). This increased taxable income in the Example data by about 1%.
>>Added Federal tax-exempt investment allocation choice and related earnings calculation. Amount taxable in your state can be entered.
>>Changes to and new internet links have been generating a false Windows warning. (This warning is a common Excel problem in recent years.) Solution: revised URL addresses will be shown, but will not be active links.
>>Corrected the formula for total SPIA income that didn't include income from IRA sources.
>>Added a report on of the number of years in which Medicare premium may increase due to high income (MAGI) above the threshold and the number of years in which ACA subsidies may not be available prior to age 65 due to MAGI exceeding 400% of the Federal poverty level.
>>Updated instruction, cell notes and formatting.

Please report any problems or suggestions. A happy and prosperous new year to all.
Last edited by BigFoot48 on Fri Jul 22, 2016 8:54 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
gts1952
Posts: 6
Joined: Thu Jan 28, 2016 9:00 pm

Re: Retiree Portfolio Model

Post by gts1952 »

I must be missing something, I entered our ages (64 & 59) on the setup page lines 24 & 25. I get an error when trying to enter the correct starting ages for our pensions in cells G83 thru G86 (65/60/65/65). The error message says "The starting age must be greater than or equal to your age". These starting ages are greater than our current age.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

gts1952 wrote:I must be missing something, I entered our ages (64 & 59) on the setup page lines 24 & 25. I get an error when trying to enter the correct starting ages for our pensions in cells G83 thru G86 (65/60/65/65). The error message says "The starting age must be greater than or equal to your age". These starting ages are greater than our current age.
Thanks for reporting this. Although I could not recreate it, I did find an error in the validation formula but it was not related to the problem you found. Please note that the ages entered in the income section, G83-G86, should all be based on "Your Age". So they should be entered as 65/66/65/65.

I am correcting the error to these validation warnings but will not issue a formal update for it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
ken100
Posts: 1
Joined: Sat Mar 19, 2016 3:43 pm

Re: Retiree Portfolio Model

Post by ken100 »

Hi BigFoot48. Just began setting up the model and appreciate all the work you have done. It's fantastic. I am having a problem with the File and Suspend Social security option. My wife and I are 67. Last year in June at 66 I filed and suspended until 70. My wife applied and started to receive 50% of my FRA benefit in August of last year. When I specify File and Suspend in RPM it starts her benefits in the year we become 70 instead of showing that she is will receive benefits for the 4 years before that. What am I doing wrong? Thanks.

ken100
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

It appears the model has a problem with including existing benefits under the File & Suspend strategy if past age 66. About the only thing I can suggest to get it to calculate correctly is to set your ages at 66 for now. I plan to revise the SS section in the next release for the recent law changes and I will address your situation then.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Dasnyc
Posts: 100
Joined: Wed Oct 16, 2013 6:51 pm

Re: Retiree Portfolio Model

Post by Dasnyc »

First, thank you so much for all your work to make this such a robust tool, and for sharing it with the rest of us.

Although my finances are fairly simple, and don't make use of many of the features, I can appreciate all the work that it must have taken to add and test all the scenarios that are being covered.

I am having a small problem with the ORP tab. Not sure what I have done wrong. All appears correct, except on the far right where RPM and ORP values are compared. The RPM values are correct, but the ORP value are off. '
The values in the '11_Social Security' ORP column are wrong
The correct Social Security Values appear in the '12_Pension' ORP column.
Likewise, my pension values are appearing in the '13_Earnings' ORP column.

Thanks for any help you can provide
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Dasnyc wrote:I am having a small problem with the ORP tab. Not sure what I have done wrong. All appears correct, except on the far right where RPM and ORP values are compared. The RPM values are correct, but the ORP value are off. '
The values in the '11_Social Security' ORP column are wrong
The correct Social Security Values appear in the '12_Pension' ORP column.
Likewise, my pension values are appearing in the '13_Earnings' ORP column.
Thanks for pointing this out. ORP changed the results schedule of values without notifying me, :wink: so the reference formulas were off by a column. I have fixed this and anyone that wants the corrected version can download it again. Version number remains the same.

One of the burdens of a "robust" tool!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

The bottom of every wiki page has a reader feedback form where anyone can post comments (" Help improve this page" - no login required).

I'm passing along this feedback which I posted in the page's Discussion tab:
I was looking for a suggested portfolio for someone retired
I'm not sure if this is someone looking for help with their own portfolio, or someone wanting an example for the spreadsheet.

If it's the former, a question of this type depends on an individual's situation and they should ask this question in the forum.

(This is another aspect of being a wiki editor - How to handle reader feedback and decide if the suggestion is appropriate.)

=================
I also modified the page to clarify where to go for support and download instructions: Retiree Portfolio Model
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
sampaine
Posts: 11
Joined: Wed May 11, 2016 11:00 am

Re: Retiree Portfolio Model

Post by sampaine »

Love this spreadsheet!
Under Other Income: I have entered my age above (67), but when I enter the start age for some other income (85) I get an error message that start age must be greater than my age. It is, so I don't understand why I get the error message.
Please help!
Thanks!
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sampaine wrote:Love this spreadsheet!
Under Other Income: I have entered my age above (67), but when I enter the start age for some other income (85) I get an error message that start age must be greater than my age. It is, so I don't understand why I get the error message.
Please help!
Thanks!
The warning message in cell B87 applies to both Pensions 1 and Other Income 1 ages. Make sure both of these ages are equal to or greater than the "Your Age" value in cell E24. PM me if you still have a problem.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

The bottom of every wiki contains a form which allows readers to provide feedback ("Help improve this page"), no login is needed. We just got this Feedback post on Retiree Portfolio Model:
i don't see where the link is for the spreadsheet talked about on this page. Maybe put it at the top?
I revised the page: Retiree Portfolio Model

BigFoot48 - If you don't like the wording, just change it. (Any editor is welcome to edit the page.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote:I revised the page: Retiree Portfolio Model BigFoot48 - If you don't like the wording, just change it. (Any editor is welcome to edit the page.)
LadyGeek, thanks for making that improvement to the Wiki page as it will now make locating the model link and asking questions in this thread much easier.

I'm working on a update to remove the two Social Security claiming options cancelled by Congress last December and it should be out soon.

80,000 views of this thread
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

A new version of the model, 16.2 - Jul 22, 2016, is now available - update: replaced by 16.3 on Oct 16, 2016.

Note: A problem in the "Clear Entries" macro was reported by a user (thanks Puck!) and fixed on July 23 AM and the model updated. Download again if you intend to use this feature.

This update was mainly done to remove the terminated Social Security strategies of Restricted Application and File and Suspend. To provide SS benefits flexibility in the model, I've added a feature for adding additional benefits for those currently using those strategies or grandfathered in, or for those that might be getting benefits from a previous marriage or for other reasons. This had been a request of some users.

Also added due to a suggestion/idea of a user is a way for IRA owners with a spouse younger than ten years to input IRS Table II data and have the model correctly calculate the RMD amounts. Please consider this a beta feature!

Changes in 16.2 include:
>> Removed cancelled SS benefit strategies File and Suspend and Restricted Application. Added an "Additional Benefits" option to allow entry of these or other benefits.
>> Added an option to select the IRS Table II - Joint Life for calculating RMD if the spouse is more than 10 years younger. Requires user to input data in table.
>> Removed the choice to include an annuity purchase in the Base case. This was an unnecessary complication for this common and minor event.
>> Fixed validation checking for Income by Source - Your Age. (added to 16.0 Jan 29)
>> Fixed ORP comparison schedule for ORP results table change (removed Taxable) (added to 16.0 Mar 19)
>> Fixed some verification and error messages. Updated instructions and cell comments. Updated ORP page for recent ORP changes.

Please report any problems or suggestions.
Last edited by BigFoot48 on Sun Oct 16, 2016 4:53 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Unkie
Posts: 1
Joined: Mon Aug 01, 2016 11:37 pm

Retiree Portfolio Model

Post by Unkie »

Good Evening Dr. Bigfoot et al, ;-)
Are you aware of anyone who offers a q/c or technical peer review consulting service for this model (fee or not)?
My brother and I are having a great time trying to employ this correctly in parallel with our own systems, but since new to this model, we're still naturally skeptical that we are using it correctly....so some review and advise would likely be a benefit.
tnx! Unkie
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Unkie wrote:...so some review and advise would likely be a benefit.
The only reviews available are those posted by users in this thread, and they are mostly limited to either they find it useful or they found a problem. I recommend users that want to test it should start with limited entries, verify that the model is calculating and manipulating those entries correctly, then add more complexity and verify results at that point, and so on and so on. Features that make this easier to do is a switch to turn off all income tax calculations, the ability to set inflation and earnings rates at 0%, and rounding of results at $1.

The model can be tested against the ORP Retirement Calculator and a page is now provided to help in comparing the two models. ORP automates many of the decisions that RPM requires the user to make, so the RPM model's entries must be set such that RPM's results generally match those determined by ORP. ORP calculations of earnings on balances and taxes are different from RPM's, so some differences will occur that can never be completely reconciled. In comparisons I've made over the years with simple cases, the two models have reasonably close results. (ORP's creator has included RPM in his list of Retirement Planning Calculators: http://www.i-orp.com/others.html )

I'm glad you're finding the model useful and please do report your conclusion.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Hello Bigfoot,

I would like to join the chorus in thanking you for an absolutely amazing piece of work in Excel!!! I'm a long time Excel user myself and find the things you have been able to do in Excel take it to a whole new level. After using multiple tools, including my own Excel spreadsheet, it was your Retiree Portfolio Model that gave my wife and I the confidence that we will be fine to retire within the next year, five years ahead of schedule. You helped me better visualize different Social Security start year scenarios and you introduced me to the power of Roth Conversations to minimize Social Security RMDs and taxes.

I want to say a GREAT BIG THANK YOU for all all your tireless work!!!

Best Regards, Zephavest
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

And thank you Zephavest for those kind comments. I just hope you used both your own models and many other sources in making your early-retirement decision, as we must always remember this wisdom: "the data is not a future reality, just a mathematical construct". Best of luck!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

BigFoot48 wrote:I just hope you used both your own models and many other sources in making your early-retirement decision, as we must always remember this wisdom: "the data is not a future reality, just a mathematical construct". Best of luck!
Hi Bigfoot,

Excellent advice, thank you, I completely understand as I've agonized over numerous approaches over the last five years.These have included multiple Excel scenarios, using the Fidelity 401K Retirement Planner and then having an appointment with their Financial Analyst, which basically just confirmed I used the model correctly and then they tried to sell me a roll over annuity. Then I had several appointments with the Vanguard Financial Analyst, again the bottom line was my percent draw down is between 1% and 2% so that provides for a good outlook regardless of the market gyrations.

I have a question on your new version 16.2, I'm switching over from version 16.0 and I found one item for you to look at please. On the Setup tab in cell E13, "In Use alternative SS benefits?" the yes/no selection drop down box is missing. When you have a chance could you look at that please?

Thank you, Zephavest
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Zephavest wrote: I have a question on your new version 16.2, I'm switching over from version 16.0 and I found one item for you to look at please. On the Setup tab in cell E13, "In Use alternative SS benefits?" the yes/no selection drop down box is missing. When you have a chance could you look at that please?
Fixed! Thanks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Peter Foley
Posts: 5525
Joined: Fri Nov 23, 2007 9:34 am
Location: Lake Wobegon

Re: Retiree Portfolio Model

Post by Peter Foley »

After a couple year's hiatus and a stab a I-ORP I went back to your speadsheet. There is a lot to like.

One thing I would like is for the option to run the program to age 90 rather than 95. Running to age 95 tends to favor tax free conversions because of the additional time for compounding. Also RMD's after age 90 distort the tax picture.

My modeling of Roth conversions shows I come out ahead by doing so. But, only at ages 93 to 95. The tax situation for my heirs is likely to be much different than my own. :wink:
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

You can chose however many years you would like to model, from 1 to 40. So just pick the number that ends your model at age 90. (I have never run a 1 year test model - user beware - the results may be a bit odd.)
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Peter Foley
Posts: 5525
Joined: Fri Nov 23, 2007 9:34 am
Location: Lake Wobegon

Re: Retiree Portfolio Model

Post by Peter Foley »

Thanks. I changed the setting from 30 to 25 and the results changed accordingly. This, of course, prompted me to model a few different options.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

BigFoot48 - Whenever you get to it, I found a few minor nits in version 16.2 "Interim".

-- Readme!B40 has a broken link: Vanguard Economic and Investment Outlook - Jan 2014 returns "The article you requested cannot be found."

The Vanguard search engine does not allow sorting by date (231 search results for "economic outlook"), so there's no guarantee you will get the latest info.

As a compromise, perhaps the top-level page would be helpful: Vanguard - Research and Commentary If not, consider removing the link.

-- In Readme!B41, the title should be clarified from "Portfolio Solutions’ 30-Year Market Forecast" to "Portfolio Solutions 30-Year Market Forecast for 2015" (add the date)

-- In Readme!E38:G40 (range), I recommend combining the text and links into a single hyperlink.

-- In Readme!G44, there's no hyperlink for Excel 2013. Perhaps this isn't needed and the section title could be changed from "These spreadsheets will run RPM:" to "Alternatives to Microsoft Excel:" ?

-- In Readme!E50, change the Bogleheads.org hyperlink from http://www.bogleheads.org/ to https://www.bogleheads.org/ (http: goes to https:). It doesn't matter, as the protocol is automatically changed to https:// . But, I'm a site admin and want to be sure the link is correct. :)

--In Readme!B59, the link to "Click here for the current model link at the Bogleheads' forum. Comments, suggestions and error reports welcome!" should be https://www.bogleheads.org/forum/viewtopic.php?p=1405885#p1405885. Currently, it's using http:// and highlighting "Roth". (Again, the link works...)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Thanks LadyGeek! I will fix those in the next few weeks.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Sorry, one more:

In Readme!E26, the text box on top of this cell ("go to top") has an unassigned macro. "Cannot run the macro "Retiree Portfolio Model v16.2xls'!Top'. I found one that's close in Module 6, but it should go to a1:a1 instead of a8:a8.

I'm using MS Excel 2016.

If I have any more comments, I'll keep a list and then post when appropriate.

Update: Saving in MS Excel 2016 format and checking backwards compatibility, the spreadsheet is complaining about broken data validation rules in the "Setup" worksheet. I'm not sure what can be done (if anything should be done...), but I can look at this later.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

All of the link and macro issues reported by LadyGeek and some more have been fixed in the current version 16.2. I'm glad to see that the clickable link issue that forced me to show links rather than make them clickable has gone away.

The broken data validation message is likely the false positive that has been discussed and cursed a lot over the years. There are a number of suggested solutions that a Google search will reveal. This Microsoft one may work: https://support.microsoft.com/en-us/kb/2757267

I plan to continue to use the ancient XLS file type but opening and then saving the file using XLSX will remove all macros if some users prefer not to have them in the model. XLSM will preserve them.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

^^^ Thanks! I've updated my local copy. I should explain why I've suddenly started critiquing a spreadsheet that's been available since 2012.

The simple reason is that I changed employers a few months ago. I never looked at my old employer's pension plan details until I left the company. Pensions don't happen until you hit 65, so I never dug into the details.

To my surprise, I qualify for full "early" pension benefits at 60. :shock: I'll hit that milestone in few years. :annoyed My husband retired late last year and the reality of my own retirement is fast approaching. I need to plan this for real. Now.

The spreadsheet is an eye-opener. I just enter my retirement events and see the impacts in the Summary and Results worksheets. The automatic withdrawal feature is nice. 8-) I find it enlightening to see how all of these events are kicking in over time. Why did my expenses suddenly drop - mortgage paid off. Why did my income go up - RMDs kick in.

There's lots to do here, but this spreadsheet has me thinking. Thanks for keeping this updated. :thumbsup
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I found that it was best to start things off by running the "Clear entries" macro in the Setup sheet (Setup!N1). Otherwise, there's a chance that the example info will mix with your own data.

(I also unprotect all the sheets and show the border and lines, but that's me.)

Version control -

It was confusing to see "Version 16.2 - Jul 22, 2016" for a September 17, 2016 update. Even though this is an "interim" release, the date should reflect the changes.

I suggest changing Readme!E1 from "Version 16.2 - Jul 22, 2016" to "Version 16.2 - Sep 17, 2016"

For the next release, a few minor typos:

Setup Section 1:
- F3 comments have a typo "Enter the age the person may pass away (assumes Dec 31)."

Setup Section 3:
- D154 should be "Inherited IRA", currently is "IRA".

Summary:
- Y1 "Rounding" - I don't think this is the right terminology. It's not rounding per se, but displaying the tables in the desired units. I recommend changing "Rounding" to "Display Units". Also, the comment from "Rounding of these tables." to "Display table in these dollar units."
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

This thread is now in the Personal Finance (Not Investing) forum (retirement planning). Sorry for the delay, I should have moved this quite a while ago.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
TimeRunner
Posts: 1938
Joined: Sat Dec 29, 2012 8:23 pm
Location: Beach-side, CA

Re: Retiree Portfolio Model

Post by TimeRunner »

LadyGeek - all good suggestions and catches, thank you for your diligence. When I use the sheet, I *have to* unprotect it and widen the columns, otherwise the pop-up notes can't be read because they are blocked out by graphs and such. No big deal and happy the sheets can be unprotected.
One cannot enlighten the unconscious. | "All I need are some tasty waves, a cool buzz, and I'm fine." -Jeff Spicoli
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

^^^Thanks. I did see that initially, but couldn't reproduce the problem. I still see occasional truncated pop-up notes (comments) even after unprotecting the sheets. Using the original download without modifications is still inconsistent. (Using MS Excel 2016.)

BigFoot48 - Add this to the list. There's a chance for human input error, so I'll mention it now:

In the Setup sheet, section "8. Income Taxes" (Income, Tax and Cash Flow calculator), Cell L161 notes "Select case to view.", but there's no drop-down list.

The user needs to enter "Full" exactly as shown or the condition will revert to the "Base" case.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Thanks LadyGeek for all the corrections and suggestions and I concur on their benefit. I have corrected or modified all of them that you have reported through your last post. As for cell comments being blocked by graphs, I'm unaware of any with that problem but will adjust any that are reported, or that are unreadable due to the size setting.

Keep 'em coming!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

In Full Case, comments for cells F4 and F5, "age" is not shown in "Your age" and "Spouse age" (better as "Spouse's age").

====================
First, I appreciate the way you've hidden the underlying details to focus on what's important. I also appreciate how you've documented where the hidden details are and provide an easy macro to make them visible.

For some reason, my Social Security income seemed to be ramping up far too fast. It just didn't seem "real" to me. Tracing backwards (and un-hiding the information as I went), I eventually landed on the Social Security COLA assumption (Setup!E93).

The "default" assumption is 2.5%. Reviewing your data in Tax Tables (Other Data Tables - row 269), I can see how this number was derived. My gut feel was to drop the COLA closer to 1.0%, resulting in levels that seemed more appropriate for my purpose (conservative, looking 10+ years out).

(Your documentation is clear. I'm not requesting you change the average, as it's my responsibility to understand what this stuff means.)

Since I was on this sheet, I double-checked your data. From Cost-of-Living Adjustment (COLA) Information, the numbers match. Next, I went after the CPI-U (X277:X318) and got stuck. I couldn't find a reference for the CPI-U data. Did I miss it? Social Security uses CPI-W:
The purpose of the COLA is to ensure that the purchasing power of Social Security and Supplemental Security Income (SSI) benefits is not eroded by inflation. It is based on the percentage increase in the Consumer Price Index for Urban Wage Earners and Clerical Workers (CPI-W) from the third quarter of the last year a COLA was determined to the third quarter of the current year. If there is no increase, there can be no COLA.
I went to the source (Consumer Price Index (CPI), U.S. Bureau of Labor Statistics) and eventually landed at Consumer Price Index - Urban Wage Earners and Clerical Workers (Series: CWUR0000SA0). Getting the right data is a bit tricky, as you need to click on "More formatting options" to get the 12-Month Percent Change and start from 1974.

I was able to align the results with most of the years since 1974. The numbers seem to be taken from the December time frame, but they start deviating from the BLS numbers in 2003. I'm not sure, but based on the above description, I think the 3rd quarter numbers are what the SSA is using.

Rather than go down this rabbit hole, I found a link in the wiki which leads to the SSA's CPI for Urban Wage Earners and Clerical Workers. I think this is where you want to be, as the intent is to understand COLA as it impacts your SSA income. One could then follow the link to the Cost-Of-Living Adjustment (COLA) top-level page and research it to your heart's content.

May I suggest replacing the CPI-U numbers with links to the Social Security Administration's CPI-W site?
Useful Information
- CPI for Urban Wage Earners and Clerical Workers (CPI-W)
- Cost-Of-Living Adjustment (COLA) (Summary)

Or, just remove the CPI-U column as it's probably at a lower level of detail than needed for the spreadsheet.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek, the 2.5% Social Security COLA I've been using in the example data comes from the average over the past 27 years, as you found out. This is also the rate the ORP model has been using but he recently reduced it and the inflation rate to the Federal Reserve target of 2% in his new "simple input" model.

I agree the 2.5% is quite aggressive considering recent history. Over the past 10 years, for example, the average has been 1.8%. I will consider reducing that default rate in the next numbered update.

The CPI-U numbers you found were just to get a source of inflation data and aren't used in the model except to provide data for the Inflation entry cell comment, but I will add the links you found to better document the sources of the data. Thanks for your evaluation of these factors.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
blgaarder
Posts: 67
Joined: Wed Mar 11, 2015 4:33 pm

Re: Retiree Portfolio Model

Post by blgaarder »

Would it make sense to add a qualified charitable distribution parameter?

That would reduce AGI and possibly taxes.

Thanks
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

blgaarder wrote:Would it make sense to add a qualified charitable distribution parameter?

That would reduce AGI and possibly taxes.

Thanks
I've been thinking about doing that as I see a lot of discussion about it and it's now more or less a permanent tax feature according to what I've read. I've got an update about ready to release in a week and I'll see about adding that to it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95466
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

The tax feature is permanent, details are in the wiki: Qualified charitable distributions
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

A new version of the model, 16.3 - Oct 16, 2016 (updated Nov 26), is now available and can be downloaded from Dropbox via this link: Replaced by 17.0

Changes include:
Nov 26: fixed an error in the middle State tax bracket calculation. Impacted Example data ending portfolio balance by $2,000 or 0.01% so not considered major. Does not impact those using average state tax rate method, rather than bracket.
Nov 23: fixed calculations of existing Roth 2 withdrawals and related references.
Nov 2: fixed "Hide Base case" macro. Download again if you want to use this feature.
Added choice of Social Security Full Retirement Age 67 to calculations for those born 1960 and later.
Added second Roth IRA account for beginning portfolio.
Added Qualified Charitable Distributions donations from IRAs.
Added option to exclude the Base case from being calculated to simplify the amount of data displayed for those just using the Full case. Also added macro option to hide most Base case data.
Added macro option to hide ORP page/tab.

Reduced default inflation and SS COLA rates to 2.0% from 2.5% based on review of recent 10-20 year averages.

Fixed the state standard deduction and exemption to use current state data if no future state move date is entered.
Fixed checking for adequate balance for taxable account SPIA purchase in Full case to current year starting balance (was prior year starting balance).

Reviewed, modified and added comments and formatting. Consolidated links to Internet information and provided new "Useful Info" macro button access.
Moved RPM summary for ORP comparison to ORP page. Jul 26
Added and modified error messages to better monitor ages used. Out of range entries may make the model fail to calculate. Aug 21
Corrected or removed bad links on Readme page. Combined links with titles previously not working. Minor editing. Sept 16-17

Significant new features usually come with problems, so please post or contact me via PM with any issues.
Last edited by BigFoot48 on Sun Jan 01, 2017 11:12 am, edited 7 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Hi BigFoot,

Thank you for the great new features and improvements in v16.3. I wanted to let you know of an anomaly I found. I downloaded and went to setup v16.3 Oct 16, 2016. I'm using Excel 2010. I used the macros to hide the ORP tab and Clear Entries, they both worked fine. I then tried the hide Base case and received the following error (you get the error with the Exclude Base case set to either Y or N):

Pop-up box:
Microsoft Visual Basic
"Run-time error '1004':"
"Unable to set the Hidden property of the Range class"

I then, clicked on "Debug" in pop-up
Results:
Module 24 (Code):
(highlighted in yellow):
Selection.EntireRow.Hidden = True

Any recommendations or do you think this is just a bug in Excel 2010?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Zephavest wrote:I used the macros to hide the ORP tab and Clear Entries, they both worked fine. I then tried the hide Base case and received the following error (you get the error with the Exclude Base case set to either Y or N):

Any recommendations or do you think this is just a bug in Excel 2010?
No, a programming error. All fixed now, download again if you want to use this feature. I found that protecting the pages, which is the last thing I do after working on the model, without enabling formatting changes while protected was causing the macro to fail. Thanks for the alert.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Wow, that was fast, thank you so much, working fine now.
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Hi BigFoot,

I found another feature for you, on the Full Case, Base Case and Comparison tabs, when using the Detail Rows (Show/Hide) function. I'm getting the same Pop-up box:
Microsoft Visual Basic
"Run-time error '1004':"
"Unable to set the Hidden property of the Range class"

Maybe this is similar to the "protecting the pages" issue you previously fixed?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Yes, it was the same protection issue, which I have now fixed on all pages. Sorry about that. If you don't want to download again, just un-protect each sheet to allow those macros to work. Or, un-protect and then re-protect and check the boxes for format cells, columns and rows.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Zephavest
Posts: 95
Joined: Thu Jan 10, 2013 8:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest »

Great, thank you for the workaround and the fast fix.
TVBogle
Posts: 29
Joined: Wed Jun 17, 2015 9:20 am

Re: Retiree Portfolio Model

Post by TVBogle »

Hi BigFoot,
For federal income tax, how do I change the number of exemptions to include dependents (row # 172 on the Full Case/Base Case page)? Also, it seems that this number is always 1 regardless what I choose for Filing status (row # 174 on the Setup page). I'm using v16.3 with Excel2003.
Many thanks for this wonderful tool.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

TVBogle wrote:For federal income tax, how do I change the number of exemptions to include dependents (row # 172 on the Full Case/Base Case page)? Also, it seems that this number is always 1 regardless what I choose for Filing status (row # 174 on the Setup page).
Glad you like the model!

The Filing Status (Married or Single) entry on the Setup page will select either 2 or 1 exemptions that are indeed shown for each year on line 172 on the Case pages. I'm not sure why you're not seeing a 2 there when "m" is entered as the filing status, as it appears to be working when I do it. Send me a PM if you are unable to get it working for more assistance.

As for entering more exemptions for dependents, I have not included that feature as the model is aimed at retirees but understand this might be needed for some, and will plan on adding the feature to the 2017 version that is usually out in early January.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
TVBogle
Posts: 29
Joined: Wed Jun 17, 2015 9:20 am

Re: Retiree Portfolio Model

Post by TVBogle »

I might have found the cause of my problem.
It looks like that I need to specify a value for "End Age" in the "Ages and Year Factors " section. :oops:
Thank you and I'm looking forward to the future releases.
gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Post by gponym »

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.

My setup is this: In Setup worksheet, start Roth 1 withdrawal at, say, Start Age 90, End Age 99. Set Annual to 7000 and Change at 20%. These values show up fine in row 111 of Base Case (row is usually hidden).

Set Roth 2 in Setup worksheet as follows: Annual 0 (that's right, zero). Change 0%. Start Age 99. End Age blank.

The values for Roth 1 withdrawals shown in row 111 of Base Case start faithfully at age 90 and end at age 99. Value goes from 7000 to 36100. All looks as it should be.

BUT the values for Roth 2 withdrawals shown in row 112 of Base Case exactly mimic the values in row 111, starting with 7000 at age 90 and ending with 36100 at age 99. The Full Case behaves just like the Base Case.

This appears to be a simple bug but I'm brand new, so maybe there is another explanation. I stared at the formulas in rows 111 and 112 but gave up before deciphering their logic.

I am working around this by halving the Annual starting value for Roth 1 withdrawals in Setup. I don't know what side effects this may have, so I'd like not to have to do that.
Post Reply