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

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Jan 01, 2015 8:57 am

A new version of the Retiree Portfolio Model is now available for download. Version 15.0 includes many
improvements and all users should upgrade. It calculates and displays results much like the previous version but the Setup page format has been improved and instructions and cell help comments have been reviewed and improved with the goal of making it easier to use for those using it for the first time.

The model can be downloaded from Dropbox via this link: Replaced by a later version

This was version updated Jan 5 to fix a Restrict Application glitch as discussed in a Jan 5 2014 post, and a Sheet Protection setting that didn't allow the sheet formatting macros to work without un-protecting the sheet. It was updated Jan 10 to fix a SS end date error in the Base case. If any of these may affect you, re-download it. Note: The actual spousal benefit amount using the Restricted Application option may not be correct in this version depending on ages entered.

Here are some of the changes included in this version:
> Added macros. Macros are clickable buttons that perform various housekeeping tasks, such as clearing all setup data, copying and pasting results for saving, navigating, and showing or hiding gridlines, headers and rows. The model can be used without activating macros.
> Add Restricted Application strategy to SS benefits calculation. Also insured largest SS benefit is used by default after a spouse's passing. Fixed issue with File & Suspend that might have affected the calculation for different aged spouses.
> Tied the end of SS benefits due to a spouse passing, and the change to the single tax rate, to the Age section entry to eliminate the need to multiple post this entry.
> Example case data has been expanded to use every user input item to increase the possibly of revealing programming errors. A macro is now available to clear nearly all input items for a clean start.
> 2015 tax rates and factors are used. Changed default tax escalation rate to 2.5% from 2.7% to better reflect recent historic and average since the last tax rate change in 2003.
> Added to the Setup page a beta test of a Cash Flow diagram.
> Example data story moved to cell comments in far left section of green section title labels.
> Documented that annual percent changes in retirement income and IRA withdrawals can either increase or decrease the result.
> Added areas on the right side of the Setup page for the user to make notes.
> Added print areas to each page that allows printing of the primary data on the page. Adjust sizing etc. for your own printer.
> Complete review and update of Setup page cell help comments. Improved error messages. Example story moved to left-most cell in each section's title bar.

Please let me know of any problems, issues or suggestions for changes or improvements. For example, BH keith simpson, a new user, has made a number of suggestions that might benefit other new users by making the model more user-friendly, and some of these, like the Cash Flow diagram, have been incorporated into this version with likely more in a future update.

Tip: Open both the previous version and the new version and use the Excel "Compare Side by Side" feature to more easily transfer old Setup entries to new version. (Use the erase macro to start with a clean slate!)

New cash flow diagram (considered a beta test):

Image

Current views: 39,498.
Last edited by BigFoot48 on Mon Jul 17, 2017 5:06 pm, edited 12 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby sandramjet » Sun Jan 04, 2015 1:59 am

So far, I must say, I think you have outdone yourself! Love the new version!

I started playing with the RA options on the SS section, and I'm not sure if I am not understanding the approach correctly or not. Here's my situation I was trying to model:
1) I plan on deferring my benefits to age 70
2) Spouse plans to file for her benefits at age 62
3) At age 66, I would do RA for half of her benefit until I reach age 70
4) At age 70, I would take my benefits, and spouse switches to my spousal benefit. (which is greater than her benefit)

Can I model that? When I set this up how I thought it should be, I see that it looks like everything works up to age 70. At 70, the spreadsheet shows my benefit level, but my spouse still is getting just her benefit, rather than my spousal benefit.

Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sun Jan 04, 2015 10:18 am

sandramjet wrote:Can I model that? When I set this up how I thought it should be, I see that it looks like everything works up to age 70. At 70, the spreadsheet shows my benefit level, but my spouse still is getting just her benefit, rather than my spousal benefit.

You can now if you download it again. Thanks for reporting this. Modeling SS benefits is probably the most difficult logic challenge in the model with all the possible options and combinations. So I think this patch will work, but users should verify, as you did, their SS benefits to insure they are starting and, if applicable, changing in the correct years and at the correct amounts. Note the spousal portion of the benefits is not yet showing on the Setup page in the Spousal section but is included in the total benefits amount.

OOPS: As I said this is difficult. The spousal benefit may not be the correct amount for the RA case. It may take some time to solve.

I also fixed a page protection setting that didn't allow the show rows or borders without unprotecting the sheet.

I am not releasing these fixes as a new version. Users not using Restricted Application or wanting to see hidden rows or borders without a little extra work (unprotect sheet) do not need to download 15.0 again.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

cdbma
Posts: 17
Joined: Fri Feb 28, 2014 3:40 pm

Re: Retiree Portfolio Model

Postby cdbma » Sun Jan 04, 2015 12:51 pm

Holy Smokes! Does this thing kick butt, or what? The author has outdone himself...again.

I'm one of those users that reads all the comments on this app. BF48 has done a great job explaining everything, and I've learned so much about SS, and other Fed stuff that I never knew before. However, I find that a good chunk of the comments are cut off - the comment box is too small. I've tried changing my zoom factor, etc. No help. Unfortunately, Excel does not allow comment boxes to scroll. Unprotecting , then resizing is going to be cumbersome. I found some "auto-resize" macros on the web, but I'm worried that I might break something in the app.

Has anyone see this? Any alternate solutions to manually resizing them?

Thanks,

Chris

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sun Jan 04, 2015 1:01 pm

Thanks Chris. I went through and made sure all the cell comment boxes were displayed properly on the Setup page while doing this release, and most of the comments on the other pages, so I'm puzzled at what you are seeing. I assume you are not changing the Excel zoom which should be 80% on the Setup page. Not sure what might make them get cutoff otherwise. What version of Excel etc. are you using?
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

cdbma
Posts: 17
Joined: Fri Feb 28, 2014 3:40 pm

Re: Retiree Portfolio Model

Postby cdbma » Sun Jan 04, 2015 1:13 pm

I'm using 2003. Too cheap to upgrade!

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

Re: Retiree Portfolio Model

Postby LadyGeek » Sun Jan 04, 2015 1:13 pm

cdbma wrote: I found some "auto-resize" macros on the web, but I'm worried that I might break something in the app.

I can't reproduce your comments cut-off problem, but I took a quick look in MS Excel.

If you're worried that adding a macro might break something, then use a unique module name. For example, "cdbmaCommentResizer".

I found a macro that works: How to auto-size comment box to fit its content in Excel?

You can rename VBA modules by opening up the Properties window (F4) and typing the new name in the "(Name) field.

Run the macro by placing your cursor at the start of the "Sub" line (first entry), then Run --> Run Sub/UserForm.

FYI - I'm using Excel 2010 and can't guarantee anything for 2003.
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
BigFoot48
Posts: 2474
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Sun Jan 04, 2015 1:54 pm

LadyGeek wrote:I found a macro that works: How to auto-size comment box to fit its content in Excel?

I tried that one and it appears to work, with the only downside being that it ignores existing line breaks resulting in really long comments. I spend a lot of time fixing comment sizing so I may add that to the embedded macros if I can fix the line problem. Thanks for the suggestion!

I also do development of this model using Excel 2003 as I have yet to find a compelling reason to use a later version, and it continues to work very well with LibreOffice Calc (also a LadyGeek suggestion).
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

cdbma
Posts: 17
Joined: Fri Feb 28, 2014 3:40 pm

Re: Retiree Portfolio Model

Postby cdbma » Sun Jan 04, 2015 1:58 pm

Thanks for the reference. I gave it a try, but the code seems to strip out CRs, so the text is now a long, one-line comment!

If I click on "edit comment," the comment box opens with the edit frame around the entire comment. As soon as I grab the LR corner to drag the box, the text along the bottom is lost. I get it back by resizing the box.

No worries. I'm guessing it's an artifact of 2003. I will resize by hand as I need to.

Thanks again.

Chris

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

Re: Retiree Portfolio Model

Postby LadyGeek » Sun Jan 04, 2015 2:46 pm

As BigFoot48 suggested: LibreOffice Calc - Free Office Suite

The macros which copy selected cells seem to break, but the rest of the are mostly OK. Unless I missed one, none of the macros actually do any calculations.

You could probably just use LibreOffice Calc and be done with it.
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: 136
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Postby sandramjet » Fri Jan 16, 2015 9:17 am

I like the new macro to copy current results to the hold locations on the main page. One thing that would be nice is if it could also copy the current description for the case to the comment area as well.

Thanks for a great tool!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Jan 16, 2015 2:50 pm

sandramjet wrote:I started playing with the RA options on the SS section, and I'm not sure if I am not understanding the approach correctly or not. Here's my situation I was trying to model:
1) I plan on deferring my benefits to age 70
2) Spouse plans to file for her benefits at age 62
3) At age 66, I would do RA for half of her benefit until I reach age 70
4) At age 70, I would take my benefits, and spouse switches to my spousal benefit. (which is greater than her benefit)

Can I model that? When I set this up how I thought it should be, I see that it looks like everything works up to age 70. At 70, the spreadsheet shows my benefit level, but my spouse still is getting just her benefit, rather than my spousal benefit.

While reviewing the model and studying the literature on SS benefits to make sure the model at least attempts to address each SS option and do the calculations properly, I ran across this article that helped fill some gaps in my knowledge. You may want to review your plans, as it may be that #4 can't be done.
Update Jan 18: It appears I misinterpreted the paragraph below, and the benefit method planned above can be done. As to whether I can program it into the model remains to be seen.

If the wife applies for benefits before attaining full retirement age and she is eligible for spousal benefits, then she is deemed to be applying for both her own retirement benefits and spousal benefits. Stated differently, before attaining full retirement age, she cannot apply for spousal benefits only and later switch to her own benefits, or vice versa.
Social Security Strategies for Couples http://www.aaii.com/journal/article/social-security-strategies-for-couples.touch
Last edited by BigFoot48 on Sun Jan 18, 2015 4:30 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Jan 16, 2015 2:53 pm

sandramjet wrote:I like the new macro to copy current results to the hold locations on the main page. One thing that would be nice is if it could also copy the current description for the case to the comment area as well.

Thanks for a great tool!

Glad you like it. Well, there's limited room there for the model description but I suppose it might be beneficial, and could easily be over-written if desired. I'll put it on the To Do.

Oh second thought, I think most people will use it like in the example, with one long description, and brief alternative case descriptions in the storage area that rarely change, so I think I'll leave it as is.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

LesH8210
Posts: 2
Joined: Sat Jan 31, 2015 1:57 pm

Re: Retiree Portfolio Model

Postby LesH8210 » Tue Feb 03, 2015 6:12 pm

I have used spreadsheets for budgeting since the Tandy micro was created and mine was a homemade copy of that machine (if you could call it that). The Retiree Portfolio is by far the most comprehensive tool I have seen using Excel. For me, the ability to unhide the inner workings opens up a whole new world of Excel programming. Your work is excellent, and many thanks for publishing it.

I do have a question regarding the clear all macro. I am using v15.0 and have built about 20 versions of a plan with this version (so far). I have created all by replacing the settings on the setup sheet with my inputs and deleting or zeroing out all fields that I am not using. I tried the clear all macro but it failed when it reached the end date for the spouse SS field (at least I believe that is the one, it was highlighted in the debug window). So I unprotected the setup page and ran it again. Then I entered the exact input from one of my plans that was previously tested as ok. The macro cleared sheet gave me an ending portfolio value of around $160,000 more than the plans that I built by replacing the inputs on the setup sheet that you provided in v15.0.

So I guess my question is, is it ok to just replace the inputs on the setup page or is the proper way to unprotect the setup page and run the clear all macro?

Again, my sincere appreciation for this fine work. I was deep into trying to come up with a crude tool that is shameful compared to what you have created!

Les H.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 04, 2015 2:01 am

Les - thank you for the compliments on the model and I'm glad you are finding it useful.

I appreciate you reporting this error in the macro that clears most of the input factors. This resulted from a change I made at some point to a couple of cells, making them calculated rather than input, without a corresponding change to the macro that clears or resets to zero input cells.

Users can either input their own values, replacing the sample data values, or use the macro to reset almost all of them to zero or blanks and then input their data. Either method should produce the same results and I don't know why you are seeing a $160,000 difference. I suggest you use the Excel Compare function to compare the Setup pages of the two versions and see if there's a difference somewhere in your input data.

Because of this failure in the macro, I am releasing a new version that includes a fix for this, and some other changes I've made since version 15.0.

Thanks again for the compliments and I'll continue to work to keep the model living up to them!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 04, 2015 2:08 am

A new version of the Retiree Portfolio Model is now available for download. Version 15.1 includes several fixes and minor improvements. Users of 15.0 that are not experiencing any problems as listed in the changes below can possibly ignore this update, but many users may benefit from the fixed Clear Cell fix and other changes.

The model can be downloaded from Dropbox via this link: Replaced by a new version.

Changes (some of these were added to 15.0 in the last few weeks):
> Fixed SS Restricted Application logic to allow spousal benefits at 70. NOTE: This function still under re-development to allow total flexibility.
> Fixed Protect Sheet setting to allow use of sheet format macros without unprotecting the page.
> Fixed spousal benefits ending date which was not consistent nor based on other person's end date.
> Improved SS benefit input error messages. Added start years to spousal and special amounts.
> Fixed Clear Entries function to skip locked calculated cells that were creating a halt to the macro process and an error message.

Now at 43,995 views.
Last edited by BigFoot48 on Mon Oct 05, 2015 8:54 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

LesH8210
Posts: 2
Joined: Sat Jan 31, 2015 1:57 pm

Re: Retiree Portfolio Model

Postby LesH8210 » Wed Feb 04, 2015 8:35 am

Many thanks! The clear macro is working perfectly now and v15.1 vs. v15 output comparison is exactly the same.

Have a great day,
Les

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

Re: Retiree Portfolio Model

Postby sandramjet » Sat Feb 07, 2015 7:22 pm

I was trying to do a comparison between using a "file and suspend" strategy vs "RA" strategy. But if I understand the way the sheet works, it seems I can't do "FS" as the "base" case and the "RA" as the "full" case. Is my understanding correct, or am I just not doing something correctly?

Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Sat Feb 07, 2015 7:28 pm

No, you can do each alone in the two different cases. In fact the example case that comes with the model does exactly that. Please note that both of these do the simple version of these two strategies and more complex benefit modeling, like combining both in a case, will have to await a further release.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Tue Feb 10, 2015 9:04 pm

I just downloaded v 15.1 this afternoon and LibreOffice 4.4.0.3. I haven't started doing anything yet but I see that nothing happens when I press "Clear Entries" or any other button. I'm running on OSx 10.8. Any suggestions? Is anyone running this on a Mac with LibreOffice? Is there some configuring I need to do? I did enable macros. Thanks.

MinnTexan
Posts: 1
Joined: Wed Feb 11, 2015 8:50 am

Re: Retiree Portfolio Model

Postby MinnTexan » Wed Feb 11, 2015 9:04 am

Is the withdrawal rate calculation correct? I think it needs another parenthesis.

Currently v15.1 is
=IF(G23>0;-(+G18+G20+G21)/(G14+G23)/2;0)

For an 80k withdrawal on a 1M average account value it gives 2.1% instead of 8.1%

Should be
=IF(G23>0;-(+G18+G20+G21)/((G14+G23)/2);0)

Thanks for all the work on the spreadsheet. Has been very helpful in educating myself with different scenarios. Much more comprehensive than my own attempts.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 11, 2015 9:16 am

deniseB wrote:I just downloaded v 15.1 this afternoon and LibreOffice 4.4.0.3. I haven't started doing anything yet but I see that nothing happens when I press "Clear Entries" or any other button. I'm running on OSx 10.8. Any suggestions? Is anyone running this on a Mac with LibreOffice? Is there some configuring I need to do? I did enable macros. Thanks.

Some macros will not work in Windows LibreOffice Calc, including the last part of the Clear Entries macro and the Copy-Paste macros, as the Calc macro program apparently needs a different way to select a range of data than Excel uses. Selecting one of these macros will result in an error and the macro editor likely popping up. If this happens, just close the editor and don't use that macro. [This will be fixed in the next release.] Other than that problem, the Windows version of LibeOffice Calc 4.4.0.3 can be used to run the RPM model with only some minor formatting issues.

As for the Apple O/S, a forum member recently tested RPM with Apple Numbers and there were numerous errors preventing the model from running, so it's not compatible with Numbers. From your report it also appears none of the macros will run using LibreOffice Calc on the Apple, so I recommend not using the macros which are not required to otherwise use the model. Please let me know if input or calculation errors occur when using the model, but as the Apple O/S is not being supported I will not be able to address them.
Last edited by BigFoot48 on Wed Feb 11, 2015 1:46 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 11, 2015 1:44 pm

MinnTexan wrote:Is the withdrawal rate calculation correct? I think it needs another parenthesis.

And you are right! Good catch. This calculation is shown on the Results page in the summary of each case at the top. However I realized this factor was mis-labeled as it's not intended to be the withdrawal rate but rather the net expenditures rate, so I also fixed the calculation to be net of SS benefits and other received income and the related graph. Version 15.1 has been updated if anyone wants the fixed version.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Wed Feb 11, 2015 2:30 pm

BigFoot48 - Ah, I didn't realize it wasn't supported on Apple.

I used v 14.3 last year and it appeared to be working, so maybe I'll go back to that. I wouldn't know if the calculations were correct, though. Do you know whether there were any calculation problems with that verison on Macs?

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 11, 2015 2:40 pm

deniseB wrote:BigFoot48 - Ah, I didn't realize it wasn't supported on Apple.

I used v 14.3 last year and it appeared to be working, so maybe I'll go back to that. I wouldn't know if the calculations were correct, though. Do you know whether there were any calculation problems with that verison on Macs?

There have been significant improvements since then and if 14.3 ran on your Apple using LibreOffice then 15.1 should also, with the exception that the macros may not work and should be ignored. Try it and let us know if it's working, with the easiest way being to check the Setup page Quick Results calculated amounts for the example case and see if the numbers agree with the saved results directly below it. If they're the same then the model is, more or less, working as it should.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Wed Feb 11, 2015 3:23 pm

I was using OpenOffice last year. I tried LibreOffice because OpenOffice wasn't working now. Maybe I need to make some configuration changes. I don't know either of these products. I used to know Excel but that was years ago.

The cell that has Social Security benefit amount with a value of "30000" will only let me change it to "y" or "n".

When I press Clear Entries and Go To Top nothing happens, with or without macros.

Any ideas why?

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Wed Feb 11, 2015 3:48 pm

Clear Entries is a macro and you should open the file again and don't click on that just to eliminate it from the process. I have no idea why that SS value field has been apparently changed to a y/n field. Don't understand how that could happen.

Go to the first post and download version 14.93 and see if that works for you.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Wed Feb 11, 2015 10:07 pm

Thanks, will do

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Thu Feb 12, 2015 10:11 pm

BigFoot48, v4.93 seems to be working better for me, but I did run into one snag, similar to the one I mentioned yesterday - in the RMD start age field, where it defaults to 71, I need to change it to 70 and it will only accept "y" or "n".

Thanks, Denise

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Feb 13, 2015 2:05 am

deniseB wrote:BigFoot48, v4.93 seems to be working better for me, but I did run into one snag, similar to the one I mentioned yesterday - in the RMD start age field, where it defaults to 71, I need to change it to 70 and it will only accept "y" or "n".

Denise, I am unable to test the model on an Apple system but it would appear for some reason the Libre software and O/S are not handling the model properly at that RMD cell, which is just a common number input cell. I would recommend you try Excel Online which works very well, except for not supporting macros and not displaying text boxes, such as used on the Readme page. https://onedrive.live.com/
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Fri Feb 13, 2015 8:07 pm

I hate to tell you this, but even Excel Online is giving me 'y'/'n' popups in numeric fields. If this really isn't happening to anyone else then I'm totally mystified.

I guess I'll see if I can make do with v14.93 and work around the RMD start age.

deniseB
Posts: 22
Joined: Sat Aug 18, 2012 7:51 pm

Re: Retiree Portfolio Model

Postby deniseB » Fri Feb 13, 2015 9:12 pm

On second thought, I think I do know what happened - the spreadsheet must have gotten messed up when I opened it on the Mac, then I copied the same one up to Excel Online. But I think I'm okay. I figured out how to get rid of the selection lists so I can enter numbers. I just hope nothing else is messed up because I won't know if the calculations are correct or not. Anyway, I'm working with v15 now.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Fri Feb 13, 2015 9:29 pm

That sounds like what happened. You may want to download a new copy and start all over, avoiding use of the macros. Hope you find it useful when it finally works as intended!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sengsational
Posts: 19
Joined: Sat Feb 05, 2011 8:48 pm

Dipping a Toe In - a cookbook to loading an i-orp model

Postby sengsational » Mon Apr 27, 2015 2:11 pm

I will admit that I've tried before, never quite got a level of comfort with the model, and gave-up. I thought I would try again, and while doing so, had an idea that might widen the audience for the model: a set of step-by-step instructions for loading a model from i-orp.

The good news with a model like this is that it does so much. The bad news is that it can be overwhelming at the start. I consider myself to be a decent spreadsheet jockey and I've fiddled around with quite a few retirement models, but I'm pretty much slammed when I try to get going with my data in this sheet. Since i-orp has so many fewer inputs, it's easier to get started. i-orp doesn't have the precision, of course, but it spits out a workable model output, and it also includes all inputs if you scroll-down on the results page.

If I were more comfortable with this sheet, I probably could map the i-orp inputs to the RPM inputs. I realize that there might be some things that won't map, and the cookbook could start off by saying, run the i-orp model, but don't use X or Y. Then every field could be mapped (or ignored, if it doesn't make a difference). I would not expect the results from the two models to be exactly the same, but it would be a great starting point, I think. And from that starting point, the power of the RPM, "what if" analysis, could be leveraged with a lot of confidence.

Below are all of the inputs that would have to be mapped. Dr. BF48 is probably too busy with the model, but maybe someone who's an experienced jockey could undertake such an effort.

Code: Select all

 Current Age of Retiree
 Current Age of Spouse
 Beginning Tax-Deferred Account Balance
 Spouses Beginning Tax-Deferred Account B
 Contribution to Tax-Deferred Account
 Spouses Contribution to Tax-Deferred Acc
 Roth IRA account Balance
 Spouses Roth IRA account Balance
 Contribution to Roth IRA Account
 Spouses Contribution to Roth IRA Account
 After-Tax Investment Balance
 Contribution to Savings Account
 Minimum IRA Balance
 Minimum Roth Balance
 Minimum Taxable Account Balance
 Illiquid Asset Value.
 Cost of Illiquid Asset, for tax purposes
 Unpaid Principle Balance
 Year to sell illiquid asset.
 Illiquid Asset Value (home).
 Cost of Illiquid Asset, for tax purposes
 Unpaid Principle Balance (home).
 Year to sell illiquid asset (home).
 Reverse Mortgage: Life time income plan
 Reverse Mortgage: Lump Sum Distribution
 Age to Begin Reverse Mortgage
 Social Security Income.
 Spouses Social Security Income.
 Age to Begin Social Security.
 Spouses Age to begin Social Security.
 Retiree receiving disability benefits
 Spouse receiving disability benefits
 Pension, Adjusted For Inflation.
 Spouses Pension, Adjusted For Inflation.
 Pension, NOT Adjusted For Inflation.
 Spouses Pension - NO Inflation Adjustmen
 Age to start Pension.
 Spouses Age to start Pension.
 Pension Survivors Benefit %.
 Spouses Survivor Benefit %.
 Earned Income.
 Spouses Earned Income.
 Age to end Earned Income.
 Spouses Age to end Earned Income.
 %  in tax deferred stocks at retirement
 %  in tax deferred stocks at plan end
 %  in Roth stocks at retirement
 %  in Roth stocks at plan end
 %  in taxable stocks at retirement
 %  in taxable stocks at plan end
 % return on fixed income investments
 % return on stocks
 Inflation rate for income
 Spending inflation rate

 Anticipated Retirement Age
 Spouses Anticipated Retirement Age
 Retiree Life Expectency
 Spouse Life Expectency
 Desired Estate Size
 Enable IRA to Roth IRA Conversions.
 Realistic Retirement Planning
 Obamacare Cliff Taxable Income Cap
 After-Tax Account % Anticipated Tax Rate
 Current Federal Tax Bracket
 Current State Tax Bracket
 State Income Tax Standard Deduct & Exemp
 State Personal Income Tax Rate.
 Exclude Soc Sec benefits from State Tax.
 Pension Exclusion From State Taxes
 Desired Spending Level
 Monte Carlo Risk Assessment

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

Re: Dipping a Toe In - a cookbook to loading an i-orp model

Postby BigFoot48 » Mon Apr 27, 2015 4:21 pm

sengsational wrote:I will admit that I've tried before, never quite got a level of comfort with the model, and gave-up. I thought I would try again, and while doing so, had an idea that might widen the audience for the model: a set of step-by-step instructions for loading a model from i-orp.

Interesting suggestion. The Retiree Portfolio Model (RPM) is tested against ORP at least once a year. This test, using a limited number of input factors, shows that the two models have very similar results. (The two models do calculate annual earnings and taxes a bit different.)

The test result, along with the factors used, is shown at the bottom of the Results page below the yellow line (currently line 692). The mapping of the ORP factors used in RPM is also shown here, providing a partial help to those using both models should they stumble across this section.

ORP is an excellent place to start modeling one's portfolio, earnings and expenditures. Then if one wants additional input flexibility and the ability to compare instantly two different scenarios, they can utilize RPM. I do recognize that this model can be a bit overwhelming with all the options provided. (Version 1.0 was really simple!)

I will take your suggestion under advisement, but if anyone wants to work on it please do and PM me with any questions or results. Thanks for your interest.

51,000 views
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

sengsational
Posts: 19
Joined: Sat Feb 05, 2011 8:48 pm

Re: Dipping a Toe In - a cookbook to loading an i-orp model

Postby sengsational » Tue Apr 28, 2015 5:30 pm

BigFoot48 wrote:The test result, along with the factors used, is shown at the bottom of the Results page below the yellow line (currently line 692). The mapping of the ORP factors used in RPM is also shown here, providing a partial help to those using both models should they stumble across this section.
I didn't stumble upon that bit of work. Thanks for the guidance. That's pretty much has everything I was looking for (the mapping between i-orp and your model is done). I think this is the gateway drug to full RPM addiction.

sengsational
Posts: 19
Joined: Sat Feb 05, 2011 8:48 pm

i-orp Comparison

Postby sengsational » Wed Apr 29, 2015 3:19 pm

I spent quite a while trying to "import" the test model from i-orp to RPM. First I ran the macro to clear out the starting data. I've got it so you can paste-in the i-orp parameters (bottom of the results tab), and those should go to the right input fields in RPM. I manually figured the spend less taxes from the i-orp output and I made a spot to add that. The next step, done manually, is to key-in the Roth conversions from the i-orp page into the RPM setup page. That should do it...the models should be similar, but I'm just not getting a similar result. It looks like it's doing some kind of Roth conversion spread over a lot more years than I've keyed-in. I've added a tab that has the output from i-orp, but I just can't figure out how to compare the two models. I've uploaded the sheet with the test data in it, in case someone smarter than me about this stuff wants to check it out. https://drive.google.com/file/d/0B5wbKf ... sp=sharing

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

Re: i-orp Comparison

Postby BigFoot48 » Fri May 01, 2015 3:32 pm

sengsational wrote:That should do it...the models should be similar, but I'm just not getting a similar result. It looks like it's doing some kind of Roth conversion spread over a lot more years than I've keyed-in. I've added a tab that has the output from i-orp, but I just can't figure out how to compare the two models.

The final portfolio balance of the data set entered into your ORP and RPM models are very similar: ORP is at $5,184,000 and RPM $5,145,600 (Full case, which includes Roth conversions). The Roth conversions and withdrawals generated automatically by ORP were entered correctly in RPM. Although there are additional differences that need correcting, e.g. married tax status.

Because yearly account earnings are calculated differently in the models, as is Federal income tax and possibly other factors, all of these impacting taxes and RMD, the accounts will have somewhat different balances each year when the models are compared. However, the fact that the final balances are very similar is an indication that RPM is doing the math correctly (as is ORP!).

Update May 4: I am doing additional work to better reconcile a simple ORP model to RPM, using the same input factors and closest ORP-generated values. I am seeing in some cases a difference of -10% to +10% between the two in the final account balances, and would like to know how exactly that is occurring.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

pankuchb
Posts: 4
Joined: Wed Oct 30, 2013 8:55 am

Re: Retiree Portfolio Model

Postby pankuchb » Tue Sep 01, 2015 11:19 am

Hi,

I enjoy using your latest spreadsheet model. When I put in a future growth rate for stocks the spreadsheet seems to assume that it is all taxable while in most cases it is really not taxable since it is not being sold, but the value does grow. The calculated taxes appear to be much higher than I am actually paying. Any suggestions?

Thanks,
Brian
Searcher

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Sep 01, 2015 12:00 pm

pankuchb wrote:I enjoy using your latest spreadsheet model. When I put in a future growth rate for stocks the spreadsheet seems to assume that it is all taxable while in most cases it is really not taxable since it is not being sold, but the value does grow. The calculated taxes appear to be much higher than I am actually paying. Any suggestions?

I'm glad you are finding it useful. In section "8. Income Taxes" there is an adjustment to reduce the amount of calculated total taxable account earnings to an amount that more closely approximates taxable earnings. The cell comments includes instructions on how to use this feature:

Enter a percent that approximates what portion of earnings in the taxable account should not be taxed at marginal rates. This will reduce the amount of taxable account earnings subject to Federal and state income taxes.

For example, qualifying dividends and long term capital gains have a lower tax rate, such as 0% or 15%, than other taxable income for most taxpayers. Tax exempt mutual funds and municipal bonds have a 0% rate. And increases in the Net Asset Value may not be subject to tax. In this model all of these are included in taxable income in the year earned. The factor is used to approximate how much should be excluded from the tax calculation.


I have been taking the summer off from working on the model but hope some day to improve the tax calculation for capital gains, and maybe someday finish the comparison to how ORP calculates portfolio results.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby LadyGeek » Tue Sep 15, 2015 6:06 pm

FYI - PapaGeek has created a spreadsheet for showing the effects of taxation on Social Security. I thought it would be relevant companion to the Retiree Portfolio model spreadsheet. See: Spreadsheet to show how the taxable SS benefits will affect you
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
BigFoot48
Posts: 2474
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Sep 15, 2015 6:29 pm

I also think the Social Security spreadsheet PapaGreek has done is an excellent contribution to understanding the impact of taxes on SS. It's encouraged me to flag the 46% marginal rate in this model, and I will also add a link to PapaGreek's thread it in the next update of RPM. Update: can't get links to not generate a Windows error message in the latest release running under Windows 10.
Last edited by BigFoot48 on Mon Oct 05, 2015 8:57 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon Oct 05, 2015 8:49 am

A new version (15.2) of the Retiree Portfolio Model is now available for download from Dropbox via this link: Replaced by a newer version

Note: Using Excel 2010 may result in an error message and disabling of macros. Under investigation.
Changes in 15.2 include:
Interim fix Nov 2: Fixed macros on Results page, setup factors and results copying, to work with LibreCalc.
Improved state income tax calculation. Added option to use tax brackets, std deductions, exemptions and ability to exclude pension income from state tax.
Created ORP comparison page. Moved existing worksheet, improved analysis and comparison, and provided instructions.
Changed withdrawal rate to net expenditure rate on Results page lines 24 and 37.
Removed text boxes to allow viewing of text in Excel Online program.
Reversed sign on Inherit/Expend column entries on Summary page so that expenses show as a positive number, inflows negative.
Changed signs on Summary page additions and deductions to be consistent with case signs.
Appears to work properly with LibreOffice Calc 5.0.2.2

The two major changes were at the suggestion of users. The forecasting of state income tax can now be done using tax brackets (limited to 3), Federal itemized or state standard deductions, and state exemptions. The option to use a simple percentage of Federal taxes is still available.

I have been comparing RPM results to the premier retirement forecasting tool, Optimal Retirement Planner (ORP) for several years. I decided to move those worksheets to a page/tab and enhance them for other users. Note: use of this is entirely optional and I can only offer limited advice on how the sophisticated ORP model works and why it has different results from RPM. That said, if one has a simple model and match the selections made in ORP in RPM then a reasonable comparison can be made.

I tried to include links to Tax Foundation pages on state taxes but new internet links generated a Windows error message, perhaps related to upgrading the O/S to Windows 10. The existing links to various websites continue to work.

Finally, James Welch who created ORP has done a whitepaper on Roth Conversions, the original focus of this model. It is available at the bottom of the page here: http://www.i-orp.com/ One portion of his conclusion: "Given an aversion to using savings to prepay taxes in a higher tax bracket early in retirement, the potential reduction in the size of the mid-plan estate, the timing issue, and the reduction in disposable income, the financial benefits of doing partial IRA to Roth IRA conversions may not warrant the extra paperwork. "

Please let me know of any problems with the new version as extensive changes often generate unforeseen events. (60,007 views)
Last edited by BigFoot48 on Sun Jan 03, 2016 12:57 pm, edited 6 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

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

Re: Retiree Portfolio Model

Postby sandramjet » Tue Oct 06, 2015 1:39 am

I'm looking forward to trying it out. One thing I noticed is that after I downloaded it from dropbox and tried to save it in excel 2010, i get an error message that says:
"The name, ORP1, conflicts with a valid range reference or is invalid for Excel. The name has been replaced with _ORP1.
You may still need to manually update any reference to this name used in VBA code or as text arguments in functions. You must close and reopen the workbook before these changes take effect"

Do you know of any references that I would need to update, or why it is giving that error?

Thanks!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Tue Oct 06, 2015 8:01 am

Thank you for reporting that error. ORP1 is the macro that copies the model summary data for use in comparing to the ORP data. I did not test this update in Excel 2010 but will do so and see if the ranges used in that macro are causing this error message.

Update 1: I can confirm that 15.2 is not opening properly in Excel 2010 with macros being reported as "corrupt". I'm also seeing this in the earlier versions 15.1 and a 14 version. Excel is reporting the macros as corrupt and are deleting them. I don't know what has changed that is making this occur but Windows 10 is on my list due to another new error I encountered which had Excel 2003 reporting new internet linked-cells as errors.

Until I can resolve this I suggest users of 2010 just use the model without the macros, use 2003 or use Libre office. They make copying data and using the model a little easier but are not necessary for the operation of the calculations except for the new ORP comparison which 98% of users will likely never use. The investigation will continue...

Update 2: I found some cells that the ORP macros were copying to were protected causing an error when the page protection was turned on. I fixed those and some other misc macro references I noticed. This did not fix the 2010 problem. LibreOffice 15 runs version 15.2 including use of macros so that is a good alternative to Excel 2010.

Update 3: When I open RPM v14.93, which has no macros, in Office Excel 2010 I get the same series of error messages: "macros are corrupt".."deleting all macros"... So I don't know what's going on and all my computers are running Win10 so I can't test the hunch that it's the source of this problem. Lots of people get these errors pre-Win10 as a Google search reveals. Research continues...
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

SittingOnTheFence
Posts: 172
Joined: Sun Sep 27, 2015 5:30 pm

Re: Retiree Portfolio Model

Postby SittingOnTheFence » Thu Oct 08, 2015 12:15 am

Just discovered this spreadsheet. I'm running libcalc Version: 4.1.6.2 Build ID: 410m0(Build:2) which is a linux build, I have no idea if it is the same build for Windows. I'm using v15.2 downloaded from DropBox link and have macros enabled (the clear entry macro works :D )

I note some formatting issues, I don't know if this is due to converting .xls to .ods nor if it has anything to do with the linux version I'm running.

In the comment below I use the word 'tab' but don't know what to properly call it. It's a colored & anchored box w/ arrow on end and text inside.

Some of the anchored 'tabs' in the cash flow diagram are not in the right place. For example, "withdrawls $37" looks like it belongs around cell W30 but on my spreadsheet I found it at A28..E28 covering parts of text in row 28. If you had not had a copy of this cash flow diagram on the wiki I would have had a difficult time figuring out what and where it was supposed to be. There were several other 'tabs' in the diagram that were misplaced and had to be moved to match the diagram on the wiki.

Maybe this is because I'm using linux and saved the spreadsheet as native .ods or maybe there is a formatted error. I'm barely scratching the surface of data entry.......

Thanks for this great work.

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Thu Oct 08, 2015 8:23 am

I'm glad you like the model. The problem you are seeing appears to be likely a result of saving it to the ods format. The alignment of that flow chart is perfect using LibreCalc and the xls format on Windows, but saving it to ods format and reloading results in the problem you are seeing. The "Clear" macro is my favorite too.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 12-time loser

SittingOnTheFence
Posts: 172
Joined: Sun Sep 27, 2015 5:30 pm

Re: Retiree Portfolio Model

Postby SittingOnTheFence » Thu Oct 08, 2015 3:02 pm

Thanks, I just tried it in .xls and didn't find the problem I mentioned.
I'll continue using xls file format

User avatar
Just sayin...
Posts: 158
Joined: Tue Oct 09, 2007 10:12 am

Re: Retiree Portfolio Model

Postby Just sayin... » Mon Nov 02, 2015 9:43 pm

Well, I downloaded the latest RPM spreadsheet and tried to open it in Excel (2016) on my Mac (10.11.1). After I got the following pop-up, I quit and decided to seek help before going any further:

Image

I can dig up a Windows laptop from work and bring it home tomorrow, but I prefer to keep my personal stuff private. Is there any way to make things work on the Mac, or am I forced to run Win / Excel?

Thanks!

Update: I ran the spreadsheet on a lab machine I have access to, using Remote Desktop Connection (Win 10, Excel 2013). Less than elegant, but it works!

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

Re: Retiree Portfolio Model

Postby BigFoot48 » Mon Nov 02, 2015 11:32 pm

Others have also had problems running it on a Mac so I would recommend some Windows/Excel/Libre combination. The macros are really very simple and I don't know why an error message like you posted are occurring in some versions of Excel, but I will continue to try and resolve it. Just remember the model calculations will function without using of any of the macros, so if they are causing an issue just disable/ignore them, if possible.

These are likely the reason for the error message: "Office 2010 for Windows and Office 2011 for Mac have new features and an updated (larger) version of Visual Basic for Applications. There are many reserved words in Office, and some words that didn't used to be reserved words are now reserved." and "Microsoft increased the number of available columns in a spreadsheet to over 16,000 in recent Excel versions. This means that cells with some letters and numbers become in conflict with actual cells with that reference."
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: aorin, badbreath, chevca, egoldstein, fabdog, Katietsu, KlangFool, littlebird, nolesrule, omega, rjbraun, Sandi_k, scotgirl100, spn32, Sweetfo and 120 guests