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
munemaker
Posts: 4173
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Tue Dec 17, 2019 8:28 am

Bigfoot:

You released an update on 01/01/2019. Is any such update planned for 01/01/2020?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Dec 17, 2019 8:47 am

munemaker wrote:
Tue Dec 17, 2019 8:28 am
Bigfoot:

You released an update on 01/01/2019. Is any such update planned for 01/01/2020?
Yes, unless there's an asteroid strike (or something worse!) before then, I have 20.0 about ready to go for a 1/1/20 release. No major changes this year just 2020 factors updates and minor enhancements.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Mando19
Posts: 29
Joined: Tue Nov 20, 2018 7:38 am

Re: Retiree Portfolio Model

Post by Mando19 » Thu Dec 19, 2019 2:02 pm

Dividend rate question

I am using RPM to analyze the impact of Roth conversions to reduce my annual RMD and taxes, for a given expense budget.

I am having a challenge getting the approximately correct AGI and tax rate. Since I harvest my dividends, is there a way to set my dividend/capital gains percent for equities and bonds (taxable investment) and see this as income? I tried to use the ‘User input income…Adjustments’ (Details worksheet), but don’t think that uses the proper tax rate. Thanks,

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Dec 19, 2019 4:39 pm

Mando19 wrote:
Thu Dec 19, 2019 2:02 pm
Dividend rate question

I am using RPM to analyze the impact of Roth conversions to reduce my annual RMD and taxes, for a given expense budget.

I am having a challenge getting the approximately correct AGI and tax rate. Since I harvest my dividends, is there a way to set my dividend/capital gains percent for equities and bonds (taxable investment) and see this as income? I tried to use the ‘User input income…Adjustments’ (Details worksheet), but don’t think that uses the proper tax rate. Thanks,
The model taxes all the annual growth in the taxable account as ordinary income. To reduce this for the reality that not all the growth is distributed and some of the distributions are capital gains and dividends taxed at a different rate, the model has the Taxable Account Adjustment in the 8 - Income Taxes on the Setup page. Use this adjustment to get closer to what your net actual taxes are in the first year. It's a bit crude but a necessary adjustment for multi-year modeling purposes. Hopefully some users will respond with other advice.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by munemaker » Thu Dec 19, 2019 6:47 pm

BigFoot48 wrote:
Tue Dec 17, 2019 8:47 am
munemaker wrote:
Tue Dec 17, 2019 8:28 am
Bigfoot:

You released an update on 01/01/2019. Is any such update planned for 01/01/2020?
Yes, unless there's an asteroid strike (or something worse!) before then, I have 20.0 about ready to go for a 1/1/20 release. No major changes this year just 2020 factors updates and minor enhancements.
Fantastic! Thanks

Mando19
Posts: 29
Joined: Tue Nov 20, 2018 7:38 am

Re: Retiree Portfolio Model

Post by Mando19 » Fri Dec 20, 2019 6:33 am

BigFoot48 wrote:
Thu Dec 19, 2019 4:39 pm
Mando19 wrote:
Thu Dec 19, 2019 2:02 pm
Dividend rate question

I am using RPM to analyze the impact of Roth conversions to reduce my annual RMD and taxes, for a given expense budget.

I am having a challenge getting the approximately correct AGI and tax rate. Since I harvest my dividends, is there a way to set my dividend/capital gains percent for equities and bonds (taxable investment) and see this as income? I tried to use the ‘User input income…Adjustments’ (Details worksheet), but don’t think that uses the proper tax rate. Thanks,
The model taxes all the annual growth in the taxable account as ordinary income. To reduce this for the reality that not all the growth is distributed and some of the distributions are capital gains and dividends taxed at a different rate, the model has the Taxable Account Adjustment in the 8 - Income Taxes on the Setup page. Use this adjustment to get closer to what your net actual taxes are in the first year. It's a bit crude but a necessary adjustment for multi-year modeling purposes. Hopefully some users will respond with other advice.
The Taxable Account Adjustment in the 8 - Income Taxes, is what I needed.
Thank you.

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

Re: Retiree Portfolio Model

Post by Zephavest » Fri Dec 20, 2019 8:45 am

BigFoot48 wrote:
Tue Dec 17, 2019 8:47 am
Yes, unless there's an asteroid strike (or something worse!) before then, I have 20.0 about ready to go for a 1/1/20 release. No major changes this year just 2020 factors updates and minor enhancements.
Hi BigFoot,

Will you be including updates from the SECURE act? Mainly the change to RMD starting age, changing from 70 1/2 to 72?

This of course assumes the bill gets signed as expected and the ink dries.

Thank you

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Dec 20, 2019 9:22 am

Zephavest wrote:
Fri Dec 20, 2019 8:45 am
Will you be including updates from the SECURE act? Mainly the change to RMD starting age, changing from 70 1/2 to 72?

This of course assumes the bill gets signed as expected and the ink dries.

Thank you
The provisions of the act that might impact the model will not be in the 20.0 version. However, the raising of the RMD age to 72 is already in the model as any age can be used to start RMDs. The proposal to limit inherited IRA RMDs to 10 years will require a change. A quick review indicates that may be the only impact requiring a change but we'll need to wait for the final bill to confirm.

UPDATE: In light of the signing of this bill, I will be including in 202.0 the new age 72 RMD age (actually, its already in the model so this ones easy) and the 10 year inherited IRA distribution provision, if the factors are released by Jan 1.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

dowse
Posts: 443
Joined: Tue Sep 10, 2013 3:10 pm

Re: Retiree Portfolio Model

Post by dowse » Sun Dec 22, 2019 11:17 am

I've been trying to figure out how to get RPM to model what I'm currently doing with my taxable account. I am making monthly withdrawals which approximately equal dividend and interest distributions to supplement my income. The model seems to want to reinvest interest and dividends. Anyone have any thoughts on how to model this?

dowse
Posts: 443
Joined: Tue Sep 10, 2013 3:10 pm

How to get RPM to model Taxable Withdrawals?

Post by dowse » Tue Dec 24, 2019 2:28 pm

[Post moved into here, see below. --admin LadyGeek]

I posted this question in the main RPM thread, but got no response, so I'm reposting it as a new topic.

I've been trying to figure out how to get RPM to model what I'm currently doing with my taxable account. I am making monthly withdrawals which approximately equal dividend and interest distributions to supplement my income. The model seems to want to reinvest interest and dividends. Anyone have any thoughts on how to model this?

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

Re: Retiree Portfolio Model

Post by LadyGeek » Tue Dec 24, 2019 5:19 pm

^^^ dowse - I moved your stand-alone post back into the support thread, which is in the Personal Finance (Not Investing) forum. Consider we're at the start of a major holiday. Please allow additional time for our members to respond.
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.

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

Re: Retiree Portfolio Model

Post by MrDrinkingWater » Tue Dec 24, 2019 5:54 pm

I've been trying to figure out how to get RPM to model what I'm currently doing with my taxable account. I am making monthly withdrawals which approximately equal dividend and interest distributions to supplement my income. The model seems to want to reinvest interest and dividends. Anyone have any thoughts on how to model this?
If you want to decrease your taxable account balance, you can increase your expenses (in Section 5 Expenses), and you will see an effect in your Taxable Ending Balance (in Section 2 Portfolio Balances), among other places in other spreadsheets and graphs throughout the workbook.

There isn't a taxable account data section in Section 6, where the withdrawals are for other parts of your portfolio. Everything in RPM flows into the taxable account and your expenses are deducted from the taxable account.

I hope this helps. Maybe BigFoot48 is on vacation or taking Christmas Eve off.

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

Re: How to get RPM to model Taxable Withdrawals?

Post by BigFoot48 » Tue Dec 24, 2019 6:24 pm

dowse wrote:
Tue Dec 24, 2019 2:28 pm
I've been trying to figure out how to get RPM to model what I'm currently doing with my taxable account. I am making monthly withdrawals which approximately equal dividend and interest distributions to supplement my income. The model seems to want to reinvest interest and dividends. Anyone have any thoughts on how to model this?
Sorry I missed your question, I've been working on the 2020 version lately and celebrating the season.

Earnings in the taxable account are calculated on the average of beginning balance and the ending balance after withdrawals but before earnings. Earnings are then added to the net to determine the end of year balance. Details of how earnings are calculated are on about 446 on the Details page.

Thanks to MrDrinkingWater for jumping in here. I appreciate the help. Especially in December.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

dowse
Posts: 443
Joined: Tue Sep 10, 2013 3:10 pm

Re: Retiree Portfolio Model

Post by dowse » Tue Dec 24, 2019 7:45 pm

Thank you BigFoot48 and MrDrinkingWater for your responses during a very busy time. I should be able to adjust my model accordingly now. Seasons Greetings to you both.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Dec 31, 2019 7:56 am

Version 20.0 of the Retiree Portfolio Model model is available as of December 31, 2019 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/jmsvp7g3rxv8i ... .xlsm?dl=0
Note: updated at 6PM EST for macro error. Download again if necessary.

Updates included in 20.0:
Updated Federal tax factors, Medicare rates, escalation factors, etc to 2020 amounts or adjusted averages thru 2019.
Added 10 year IRA distribution option and 72 RMD age per the 2020 SECURE Act. One 10 year schedule provided in 20.0.
Added option to included estimated additional Medicare surcharges (IRMAA) in calculated yearly expenses.
Reformatted Setup page to reduce clutter and added selected comments from cells to assist new users.
Added to the Setup page a Clear Entries macro for clearing of any manually entered entries on the Details page for Expenses, Federal AGI adjustments, state AGI adjustments and MAGI foreign income adjustments. NOTE: Copying data for these fields from previous models via the Copy Entries macro is NOT provided.
Updated IRA RMD cell comments to reflect new age 72 start year per SECURE.
Added macro to all pages to export all cell comments on that page to a Word file.
Updated i-ORP comparison page. Not for general user use. Misc. minor text/macro corrections. Models are in general agreement.

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have. 256,528 views
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

HiHo
Posts: 10
Joined: Fri Jan 03, 2020 7:58 pm

Re: Retiree Portfolio Model

Post by HiHo » Sat Jan 18, 2020 3:01 am

Thank you so much posting the RPM. A very powerful tool.

I am still learning , but I have a couple of questions.

1. Why isn't there Roth 2 contribution in the model ? ( It has only Roth 1 contribution in Setup tab, section 6, Line 201 ) There is Roth 2 in section 2 Portfolio Balances, but I couldn't find it in section 6. My wife and I are both contributing Roth, but I can't enter my wife's contribution.

2. Also, I don't see Roth conversion from IRA 2. Is there a way to do it ?

thanks.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Jan 18, 2020 7:27 am

HiHo wrote:
Sat Jan 18, 2020 3:01 am
Thank you so much posting the RPM. A very powerful tool.
1. Why isn't there Roth 2 contribution in the model ? ( It has only Roth 1 contribution in Setup tab, section 6, Line 201 ) There is Roth 2 in section 2 Portfolio Balances, but I couldn't find it in section 6. My wife and I are both contributing Roth, but I can't enter my wife's contribution.
2. Also, I don't see Roth conversion from IRA 2. Is there a way to do it ?
I'm glad you like it. The features you see are designed to meet the needs of most of the users and are what are available in the current version. I recommend combining accounts as necessary to use the existing accounts, contribution and conversion settings.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

HiHo
Posts: 10
Joined: Fri Jan 03, 2020 7:58 pm

Re: Retiree Portfolio Model

Post by HiHo » Sun Jan 19, 2020 12:06 am

BigFoot48 wrote:
Sat Jan 18, 2020 7:27 am
HiHo wrote:
Sat Jan 18, 2020 3:01 am
Thank you so much posting the RPM. A very powerful tool.
1. Why isn't there Roth 2 contribution in the model ? ( It has only Roth 1 contribution in Setup tab, section 6, Line 201 ) There is Roth 2 in section 2 Portfolio Balances, but I couldn't find it in section 6. My wife and I are both contributing Roth, but I can't enter my wife's contribution.
2. Also, I don't see Roth conversion from IRA 2. Is there a way to do it ?
I'm glad you like it. The features you see are designed to meet the needs of most of the users and are what are available in the current version. I recommend combining accounts as necessary to use the existing accounts, contribution and conversion settings.
Thank you. I will try what you suggested.

By the way, when I re-read my post, it looks abrupt and demanding an answer. Sorry , I was in a hurry. I didn't mean to be rude or impolite.

Thank you again for enlightening me with your wisdom.
Last edited by HiHo on Sun Jan 19, 2020 12:22 pm, edited 1 time in total.

User avatar
2pedals
Posts: 1184
Joined: Wed Dec 31, 2014 12:31 pm

Re: Retiree Portfolio Model

Post by 2pedals » Sun Jan 19, 2020 12:17 am

BigFoot48, I am also very thankful. RPM is a very powerful spreadsheet. I have been using it for a few years now to help me understand tax brackets and Roth conversions in early retirement.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Jan 19, 2020 2:22 pm

Version 20.0a of the Retiree Portfolio Model model is available as of January 19, 2019 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/nac4so2tivv88 ... .xlsm?dl=0

Updating from 20.0 is optional, as it only impacts those who are using the new 20.0 feature of including IRMAA surcharges in annual expenses. Including them resulted in overstating the annual total expenses inflation amount during and following the years of any IRMAA surcharges. In the Example data total Expenses were overstated by 0.9%. Since the surcharges are independently calculated they have their own inflation adjustment and do not impact the inflation adjustment applied to living expenses and should not be included in that calculation.

Many thanks to BH sandramjet who noticed the higher inflation amounts, reported it to me, then as a bonus provided the revised Excel calculation to correct it!
Last edited by BigFoot48 on Sun Jan 19, 2020 3:33 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Admiral
Posts: 2920
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sun Jan 19, 2020 2:58 pm

BigFoot48 wrote:
Sun Jan 19, 2020 2:22 pm
Version 20.0a of the Retiree Portfolio Model model is available as of January 19, 2019 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/nac4so2tivv88 ... .xlsm?dl=0

Updating from 20.0 is optional, as it only impacts those who are using the new 20.0 feature of including IRMAA surcharges in annual expenses. Including them resulted in overstating the annual total expenses inflation amount during and following the years of any IRMAA surcharges. Since the surcharges are independently calculated they have their own inflation adjustment and do not impact the inflation adjustment applied to living expenses.

Many thanks to BH sandramjet who noticed the higher inflation amounts, reported it to me, then as a bonus provided the revised Excel calculation to correct it!
Thanks for updating! What's the update procedure from v. 19.1? Does all the personal data need to be re-entered if downloading this version or is there an import function?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Jan 19, 2020 3:28 pm

Admiral wrote:
Sun Jan 19, 2020 2:58 pm

Thanks for updating! What's the update procedure from v. 19.1? Does all the personal data need to be re-entered if downloading this version or is there an import function?
Use the Clear and Copy macros at the top of the Setup page to Clear almost all the input entries (inflation and some other factors are left) and then Copy the entries from an earlier version. Or, open in a second window the earlier version and use the View Side by Side feature to copy the entries being sure to replace all the Example data entries.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by LadyGeek » Sun Jan 19, 2020 4:47 pm

Are there any known issues with LibreOffice Calc? I have version 6.3.3.2.0+.The Readme sheet suggests that most macros work.
LibreOffice Calc 6.3.2.2 (x64) - cell comments visible, graphs show (axis labels may not), most macros work
I'm unable to select any cells in the Setup sheet, even after un-protecting the sheet (uncheck "Protect" in the sheet properties).

I can select cells in the other sheets when I remove the protection. Perhaps it's just the Setup sheet, or a macro that runs in that sheet?

I can use Microsoft Excel 2019 if I need to, but wanted to double-check with you first.
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: 2808
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Jan 19, 2020 6:33 pm

LadyGeek wrote:
Sun Jan 19, 2020 4:47 pm
Are there any known issues with LibreOffice Calc? I have version 6.3.3.2.0+.The Readme sheet suggests that most macros work.
LibreOffice Calc 6.3.2.2 (x64) - cell comments visible, graphs show (axis labels may not), most macros work
I'm unable to select any cells in the Setup sheet, even after un-protecting the sheet (uncheck "Protect" in the sheet properties).

I can select cells in the other sheets when I remove the protection. Perhaps it's just the Setup sheet, or a macro that runs in that sheet?

I can use Microsoft Excel 2019 if I need to, but wanted to double-check with you first.
There should be few if any problems with LibeOffice Calc (except as noted on Readme). I just opened 20.0a with 6.3.2.2 (x64) and was able to edit the entries on Setup. I did nothing special when I installed LibreOffice but am running in Edit mode (Edit menu item). All user entry cells are unprotected by default. There are no automatic running macros. I have no idea why you are having a trouble with it unless its the Edit mode thingee.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

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

Re: Retiree Portfolio Model

Post by LadyGeek » Sun Jan 19, 2020 7:07 pm

Thanks. Knowing that it works for you (and what's enabled) helps. It might be a Linux thingee, as that's what I'm running now. I can't select cells with the mouse, and moving the selection via keyboard is extremely slow. It seems that everything is running in slow-motion - but just for this sheet. I have no problems with any other sheet.

I'll keep looking, and will let you know if I find anything.
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.

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Wed Jan 22, 2020 8:45 pm

Good Evening BigFoot

I was trying to use the latest RPM version (V20.0A) and I was having some issues.
(I downloaded it on 1/22/2020 in the evening.

When I do a "save as" on the version (I rename it so I can play without breaking the original download) on this version, there is good majority of cells in all the tabs that have the following comment #NAME? . This also seems to happen randomly when I run macros to load entries. I have not run into this problem with previous versions.

Can you help me figure out what is going on. Is it me or something else

I am currently using Excel 2007 on the latest version of windows 10.

Thank you
RangeleyLake

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Jan 23, 2020 1:28 am

RangeleyLake wrote:
Wed Jan 22, 2020 8:45 pm
I was trying to use the latest RPM version (V20.0A) and I was having some issues.
(I downloaded it on 1/22/2020 in the evening.

When I do a "save as" on the version (I rename it so I can play without breaking the original download) on this version, there is good majority of cells in all the tabs that have the following comment #NAME? . This also seems to happen randomly when I run macros to load entries. I have not run into this problem with previous versions.

Can you help me figure out what is going on. Is it me or something else

I am currently using Excel 2007 on the latest version of windows 10.
That's interesting. I'm not seeing it and can't reproduce it. The #NAME error would indicate a corrupted input value or formula somewhere. Do you see error on the downloaded copy before renaming? If you use the Clear entries macro does it happen? Have you downloaded it again? I hate to be judgemental but I think it's you. :wink:
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Thu Jan 23, 2020 7:40 am

Thanks for the quick reply.
To answer a couple of your questions, I did download a copy again ( I use direct download as I have been doing), I do not see this error message on the download copy before renaming the file in the save as function.

After the file is downloaded I can rename the file, I can make a copy of the file but I am unable to rename the file when I do "save as" in the dialog box ( I usually only add a word or letter and keep the rest of the file name as is).

I do see the following message when I am go the dropbox,
".xlsm files are supported but something went wrong.
Retiree Portfolio Model v20.0a.xlsm · 1.91 MB"
If I remember correctly, there a was discussion that this was a non-issue.

I was able to use the clear entries and and load entries buttons successfully. Everything works great as long as I do not use the "save as" function.

If I see something that I am doing, I will give you an update
Thanks again for this tool.
RangeleyLake

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

Re: Retiree Portfolio Model

Post by Zephavest » Thu Jan 23, 2020 8:26 am

RangeleyLake wrote:
Thu Jan 23, 2020 7:40 am
If I see something that I am doing, I will give you an update
RL, here is something to try, download another fresh copy of the file, do not open it, do not do a save as, just select it and then do a copy and paste. Windows will make a copy of the file with "-copy" at the end of the file name. Then try opening this "-copy" file and see if you are getting the name error? (later you can always rename the file instead of doing a save as).

You can also do a test with a fresh downloaded copy, just open it, the original file, and delete it when you are done. You can always download another copy, and another...

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Thu Jan 23, 2020 12:04 pm

Zephavest wrote:
Thu Jan 23, 2020 8:26 am
RangeleyLake wrote:
Thu Jan 23, 2020 7:40 am
If I see something that I am doing, I will give you an update
RL, here is something to try, download another fresh copy of the file, do not open it, do not do a save as, just select it and then do a copy and paste. Windows will make a copy of the file with "-copy" at the end of the file name. Then try opening this "-copy" file and see if you are getting the name error? (later you can always rename the file instead of doing a save as).

Thank you Zephavest for your idea. That works and I am not having the name error message.. Do you know why I cannot do a Save As and change the name in that dialog box. I have done this in previous version.

Also what I found interesting, it seems that after I do your recommendation and input my data through the macro buttons (Clear entries and Load entries) , I am not getting the Error message when I do a save as to do a name change.
Thank you
RangeleyLake

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

Re: Retiree Portfolio Model

Post by Zephavest » Sat Jan 25, 2020 7:08 am

RangeleyLake wrote:
Thu Jan 23, 2020 12:04 pm
Do you know why I cannot do a Save As and change the name in that dialog box. I have done this in previous version.
The problem rang a bell for me on one of my previous reported problems:
Zephavest wrote:
Thu Sep 06, 2018 10:38 am
I successfully crashed both versions. So next I suspected something was wrong with my Excel/Office or Windows 10. Since I ran RPM v17.xlsm in June 2017 with the same version of Excel/Office I guessed my Office Installation had become corrupted, perhaps by one of the Windows 10 updates. I ran a full online repair of the Office Installation, and then did another cold boot. I had Success on all files, single and side by side compare, so everything is backup and running fine.
So what I recommend is you go into the Windows app manager and run the "full online repair of the Office Installation" that will be listed when you select repair of your Office Installation. Since your running Excel 2007 if I remember correctly these were standalone installations so just do the same thing for Excel from the Windows app manger. In this case it may just say do a Repair, not Full Online repair. If that is not successful then uninstall and reinstall your Excel program.

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Sat Jan 25, 2020 7:41 am

The problem rang a bell for me on one of my previous reported problems:
Zephavest wrote:
Thu Sep 06, 2018 10:38 am
I successfully crashed both versions. So next I suspected something was wrong with my Excel/Office or Windows 10. Since I ran RPM v17.xlsm in June 2017 with the same version of Excel/Office I guessed my Office Installation had become corrupted, perhaps by one of the Windows 10 updates. I ran a full online repair of the Office Installation, and then did another cold boot. I had Success on all files, single and side by side compare, so everything is backup and running fine.
So what I recommend is you go into the Windows app manager and run the "full online repair of the Office Installation" that will be listed when you select repair of your Office Installation. Since your running Excel 2007 if I remember correctly these were standalone installations so just do the same thing for Excel from the Windows app manger. In this case it may just say do a Repair, not Full Online repair. If that is not successful then uninstall and reinstall your Excel program.
Thank you Zephavest for your recommendation. I will poke around and give that a try. If it makes any difference, I have the desktop version of "Microsoft Office Home and Student Edition 2007" (I still have the Installation CD). Will update you once I have a chance to play around with it.
RangeleyLake

MildlyEccentric
Posts: 92
Joined: Mon Mar 10, 2014 9:28 am

Re: Retiree Portfolio Model

Post by MildlyEccentric » Tue Jan 28, 2020 2:10 pm

I also experienced the same problem as RangelyLake. I took the original downloaded file and created a new working copy by doing a "save as." The new copy immediately exhibited errors (#NAME showing up all over as well as other anomalies). The original copy of the spreadsheet does not exhibit errors. Performing a file copy and paste to create a working copy results in a file without errors. If I rename the copy/pasted file, it continues to work. If I do a "save as" on the working copy, the errors return on the new file. I ran "Office Diagnostics" on the installation, but the problem remained. I'm using Office 2007 and RPM v20.0a.

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Tue Jan 28, 2020 6:51 pm

RangeleyLake wrote:
Sat Jan 25, 2020 7:41 am
The problem rang a bell for me on one of my previous reported problems:
Zephavest wrote:
Thu Sep 06, 2018 10:38 am
I successfully crashed both versions. So next I suspected something was wrong with my Excel/Office or Windows 10. Since I ran RPM v17.xlsm in June 2017 with the same version of Excel/Office I guessed my Office Installation had become corrupted, perhaps by one of the Windows 10 updates. I ran a full online repair of the Office Installation, and then did another cold boot. I had Success on all files, single and side by side compare, so everything is backup and running fine.
So what I recommend is you go into the Windows app manager and run the "full online repair of the Office Installation" that will be listed when you select repair of your Office Installation. Since your running Excel 2007 if I remember correctly these were standalone installations so just do the same thing for Excel from the Windows app manger. In this case it may just say do a Repair, not Full Online repair. If that is not successful then uninstall and reinstall your Excel program.
Thank you Zephavest for your recommendation. I will poke around and give that a try. If it makes any difference, I have the desktop version of "Microsoft Office Home and Student Edition 2007" (I still have the Installation CD). Will update you once I have a chance to play around with it.
Hi Zephavest, repairing the Office product did not help. You did mention to do a reinstall but I will hold off on that because I can still successfully use RPM with you other recommendation. Thanks again

I also would like to thank Bigfoot48 again for a wonderful tool that is full of useful information. It has helped us tremendously with planning our financial future.
RangeleyLake

MildlyEccentric
Posts: 92
Joined: Mon Mar 10, 2014 9:28 am

Re: Retiree Portfolio Model

Post by MildlyEccentric » Tue Jan 28, 2020 8:31 pm

I'm still experiencing an anomaly with RPM and Excel 2007. I can download a copy of the spreadsheet which starts with no errors. I experimented a bit and found I can enter data into many of the cells on the Setup tab without a problem, however, if I enter data into the "Your_age" cell (change the default value of 62 to 67, for example), something goes wrong and the errors return. Interestingly, if I click the "undo" button, the original cell value is restored, but the errors remain. I can open open a fresh copy of the spreadsheet and make a single entry into the "Your_age" cell and create the errors. I have Macros disabled.

A sampling of some of the errors I see if I change the "Your_age" cell are below:

#NAME?
Full: Negative for 39 years
#NAME?
Base: Negative for 39 years
#NAME?
Portfolio: negative balances for 39 years
Alert! Portfolio has negative beginning balance in one or more accounts for 39 years

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Tue Jan 28, 2020 9:33 pm

MildlyEccentric wrote:
Tue Jan 28, 2020 8:31 pm
I'm still experiencing an anomaly with RPM and Excel 2007. I can download a copy of the spreadsheet which starts with no errors. I experimented a bit and found I can enter data into many of the cells on the Setup tab without a problem, however, if I enter data into the "Your_age" cell (change the default value of 62 to 67, for example), something goes wrong and the errors return. Interestingly, if I click the "undo" button, the original cell value is restored, but the errors remain. I can open open a fresh copy of the spreadsheet and make a single entry into the "Your_age" cell and create the errors. I have Macros disabled.

A sampling of some of the errors I see if I change the "Your_age" cell are below:

#NAME?
Full: Negative for 39 years
#NAME?
Base: Negative for 39 years
#NAME?
Portfolio: negative balances for 39 years
Alert! Portfolio has negative beginning balance in one or more accounts for 39 years
This is also happening if you change the "Spouse_age" cell
RangeleyLake

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

Re: Retiree Portfolio Model

Post by Zephavest » Wed Jan 29, 2020 5:33 am

Can you try to enable Macros and see how it behaves. There are no security concerns running macros in RPM, the file has been used by many users and it is safe.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jan 29, 2020 7:21 am

I am unable to replicate or determine what is causing the #NAME error. I am using Excel 2013 and the xlsm format is suppose to be supported by Excel 2007. No significant programming changes were made between the 2019 and 2020 versions. Users having this error may want to test the 2019 version which is still available for download: https://www.dropbox.com/s/9qrmgww1di1xk ... .xlsm?dl=0

Excel has a Open And Repair option which I have run on 20.0a and while it says repairs were made the repair list is empty. But users having the #NAME error may want to try that. This "repaired" version is now the downloadable version but only users having this issue need to download it as a test.

I don't see how running the macro-enabled version would solve the issue but it's certainly worth trying. The macros are not used unless a user clicks on one of the run buttons.

If none of these ideas work I suggest using LibreOffice Calc.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

RangeleyLake
Posts: 13
Joined: Mon May 20, 2019 7:03 pm
Location: Maine

Re: Retiree Portfolio Model

Post by RangeleyLake » Wed Jan 29, 2020 1:18 pm

Zephavest wrote:
Wed Jan 29, 2020 5:33 am
Can you try to enable Macros and see how it behaves. There are no security concerns running macros in RPM, the file has been used by many users and it is safe.

FYI
I tried enable macros and was I still giving me the same error.
I tried enable macros and also used the clear entries button. After this was done, there was no error when I tried to do a Save As or do a age change. From there on,the only error message I encounter are the usual warnings because of the data that was inputted

On a side note..We have been talking about getting the newest version of office or Liber office. Just not sure when we will be switching...It just a matter of time...

THanks again for your time Zephavest and BigFoot..It is greatly appreciated. :happy
RangeleyLake

ByThePond
Posts: 206
Joined: Thu Dec 31, 2015 11:21 am

Re: Retiree Portfolio Model

Post by ByThePond » Wed Jan 29, 2020 6:15 pm

Bigfoot, I too have found problems with changing the "spouse age" cell. When I update by 1 year, the end of portfolio balance drops by about $1.4 MM, for no reason that I can understand. There are also a couple of red alerts about negative balances that show up, not unexpectedly with the lower balances. My workaround is to leave the spouse age what it was previously, and accept a little more uncertainty in the end results, hoping maybe for eventual resolution.
Again, many thanks for this wonderful tool.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jan 29, 2020 6:27 pm

ByThePond wrote:
Wed Jan 29, 2020 6:15 pm
Bigfoot, I too have found problems with changing the "spouse age" cell. When I update by 1 year, the end of portfolio balance drops by about $1.4 MM, for no reason that I can understand. There are also a couple of red alerts about negative balances that show up, not unexpectedly with the lower balances. My workaround is to leave the spouse age what it was previously, and accept a little more uncertainty in the end results, hoping maybe for eventual resolution.
Again, many thanks for this wonderful tool.
Glad you like it! I know there are a whole lot of numbers to look at in the model and it can be overwhelming, but that's what you'll have to do to see why this is happening. Check all the factors that are spouse related, like Social Security, the age they may get their spouse's higher SS, working income, owned IRA RMDs and inheritances. Those may change with a change in their age resulting in less income and impact ending balances. The reason is in there - somewhere!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

ByThePond
Posts: 206
Joined: Thu Dec 31, 2015 11:21 am

Re: Retiree Portfolio Model

Post by ByThePond » Wed Jan 29, 2020 6:50 pm

BigFoot48 wrote:
Wed Jan 29, 2020 6:27 pm
Check all the factors that are spouse related, like Social Security, the age they may get their spouse's higher SS, working income, owned IRA RMDs and inheritances. Those may change with a change in their age resulting in less income and impact ending balances. The reason is in there - somewhere!
That was it! Had to update the spouse's current age cell as well as spouse's start age for SS to the same current age. I thought I'd checked that.
Thanks.

MildlyEccentric
Posts: 92
Joined: Mon Mar 10, 2014 9:28 am

Re: Retiree Portfolio Model

Post by MildlyEccentric » Sat Feb 01, 2020 12:57 am

I have a theory on the #NAME and other errors that occur when using RPM 20.0a with the 2007 version of Excel. I think the problem is with the use of the IFNA function in the "Details" tab of the spreadsheet. I believe IFNA was introduced in the 2010 version of Excel and is not supported in the 2007 version. Perhaps the IFERROR function which was introduced in 2007 could be used instead.

Thanks for developing such a useful tool.

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

Re: Retiree Portfolio Model

Post by Zephavest » Sat Feb 01, 2020 7:37 am

MildlyEccentric wrote:
Sat Feb 01, 2020 12:57 am
Perhaps the IFERROR function which was introduced in 2007 could be used instead.
Bigfoot is overly generous with the thousands of hours he has put into developing and maintaining RPM for the Bogleheads community at large. It is not fair to ask Bigfoot to do such as massive change, assuming it would even work, without countless more hours of testing. The reality is Microsoft took Excel 2007 to End of Life status in 2017, as in no more support from Microsoft. The money you save in rounding errors in RPM will let you purchase the latest version of Excel.

Excel 2007 End of Life - 2017
https://news.thewindowsclub.com/end-of- ... 007-90097/

Office Office 365 - $69.99 - annual subscription
Classic Office Home & Student 2019 - $149.99 - one time purchase
https://products.office.com/en-us/compa ... :primaryr1

Excel 2019 - $139.99 - one time purchase
https://www.microsoft.com/en-us/p/excel ... verviewtab

Then there is always the free alternative:
BigFoot48 wrote:
Wed Jan 29, 2020 7:21 am
If none of these ideas work I suggest using LibreOffice Calc.

MildlyEccentric
Posts: 92
Joined: Mon Mar 10, 2014 9:28 am

Re: Retiree Portfolio Model

Post by MildlyEccentric » Sat Feb 01, 2020 8:51 am

Zephavest wrote:
Sat Feb 01, 2020 7:37 am
MildlyEccentric wrote:
Sat Feb 01, 2020 12:57 am
Perhaps the IFERROR function which was introduced in 2007 could be used instead.
Bigfoot is overly generous with the thousands of hours he has put into developing and maintaining RPM for the Bogleheads community at large. It is not fair to ask Bigfoot to do such as massive change, assuming it would even work, without countless more hours of testing. The reality is Microsoft took Excel 2007 to End of Life status in 2017, as in no more support from Microsoft. The money you save in rounding errors in RPM will let you purchase the latest version of Excel.

Excel 2007 End of Life - 2017
https://news.thewindowsclub.com/end-of- ... 007-90097/

Office Office 365 - $69.99 - annual subscription
Classic Office Home & Student 2019 - $149.99 - one time purchase
https://products.office.com/en-us/compa ... :primaryr1

Excel 2019 - $139.99 - one time purchase
https://www.microsoft.com/en-us/p/excel ... verviewtab

Then there is always the free alternative:
BigFoot48 wrote:
Wed Jan 29, 2020 7:21 am
If none of these ideas work I suggest using LibreOffice Calc.
I'm sorry if I gave the impression that I was lobbying for BigFoot48 to spend extra effort on my behalf. I actually own a current copy of Excel in addition to the older version, so I have an easy solution. I was simply sharing what I found regarding an issue that appeared to be of interest to some users of the spreadsheet. I imagine also that Bigfoot48 is likely to be curious as to the origin of the the reported problem. Maintaining compatibility with older versions of software is nice if you can manage it, but sometimes it just gets to be too much trouble. I made the effort to track down the source of the problem with Excel 2007, what others do with the information is their call.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Feb 01, 2020 9:25 am

MildlyEccentric wrote:
Sat Feb 01, 2020 8:51 am
I'm sorry if I gave the impression that I was lobbying for BigFoot48 to spend extra effort on my behalf. I actually own a current copy of Excel in addition to the older version, so I have an easy solution. I was simply sharing what I found regarding an issue that appeared to be of interest to some users of the spreadsheet. I imagine also that Bigfoot48 is likely to be curious as to the origin of the the reported problem. Maintaining compatibility with older versions of software is nice if you can manage it, but sometimes it just gets to be too much trouble. I made the effort to track down the source of the problem with Excel 2007, what others do with the information is their call.
And I appreciate your effort and notification of what caused this problem. The IFNA function is indeed new to this version, and was what a Google search indicated was required to fix a formula problem. Good to know that IFERROR may be a good substitute and I'll consider using that in the future.
It would be nice if the model could be forever backward compatible but at some point an Excel upgrade may be required. I know I wish Excel 2003, which I still use for other models, still worked for RPM but MS has moved on.
Thanks for helping out!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

Horsefly
Posts: 589
Joined: Sat Oct 24, 2015 8:13 am
Location: Colorado, mostly

Re: Retiree Portfolio Model

Post by Horsefly » Sat Feb 01, 2020 1:23 pm

Count me as one who got 100's of cells showing "#NAME" errors on MS Office 2007.

I moved over to Libre Office 6.2, and so far all seems fine.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sat Feb 01, 2020 5:15 pm

I decided to make the quick fix from IFNA to IFERROR as it was only used in two rows of formulas on the Details page. The revised model is 20.0b and any user of Excel 2007 or 2010 can download it here: https://www.dropbox.com/s/133ho7hx7tlif ... .xlsm?dl=0 No need for other users to download.

Thanks for the help of MildlyEccentric in identifying the source of the error for users of versions prior to 2013. However, this type of problem may occur again as new functions are introduced by Microsoft which have limited backwards compatibility, so it may be a good idea to stay somewhat current in Excel version, at least to 2013 which I am currently using.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser

gts1952
Posts: 5
Joined: Thu Jan 28, 2016 10:00 pm

Re: Retiree Portfolio Model

Post by gts1952 » Sat Feb 01, 2020 8:48 pm

I'm running the model using LibreOffice. I've been playing with the "20a" version for a week or so, and noticed that my NYS income tax had gone to $0. I figured out that this happened when I changed the state move age to zero from the initially populated value of 84. If I change it to an age value of 69 or greater, the approximate proper income tax value comes up. I downloaded the "20b" version just now, did no value changes other than the future state tax move age. If I change the move age to zero, the state tax value in 1st year increases from $1100 to $1900. I just noticed that the age should be blank or populated not zero, however when I change it from age 84 to a blank, the first year tax goes from 1100 to 900. It looks like there may be something inconsistent in the formulas, or possibly it's application based. As a side note, there are 8 income tax brackets in NYS. I can possibly fix this myself, suggesting it as part of a future update.

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

Re: Retiree Portfolio Model

Post by sandramjet » Sun Feb 02, 2020 12:24 am

I have been using this tool for a few years now, and it is a great tool (Thank you Bigfoot48)!

My problem is not in executing the model but in interpreting the results :?

As I evaluate the results of different simulations, I find that the results often come down to very small differences over 30+ years. For example, when I look at whether to do Roth conversions or not, I find the final portfolio values vary by less than two percent. Given that many of the inputs you have to make (like estimated returns, expenses, inflation, etc, etc.) I figure that small a difference means it really doesn't matter which choice I make.

In some ways, it is reassuring to suggest that I'll be OK regardless of choice, but at the same time it doesn't really help me make a choice on what to do :confused

I'm curious what others have found in terms of:
- Do your different scenarios show clear result differences?
- How big a difference do you consider significant? 1%, 10% or ??
- Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?

Thanks for any thoughts

Post Reply