Retiree Portfolio Model

jacksprat · Post by **jacksprat** » Wed Apr 05, 2017 6:03 pm

It's obvious that a lot of work has gone into this. Thank you Bigfoot48

For those that are using the results of this model, how long did it take you to feel comfortable with the results ?

I'm in no way being critical , nor am I questioning the accuracy of the model -- I'm saying that with all the input(s) - and output - I don't feel confident with my results... While the instructions and notes do help - up to a point- there are a lot of ways I think I could have input inaccurate data or be interpreting the results incorrectly and never be the wiser.

Suggestion: maybe a 'simple' and 'advanced' mode would help ?

On second thought, if a user can't master the 'advanced' mode, then perhaps they are not a candidate for attempting to be self directed in retirement planning . Any thoughts appreciated.

WildBill · Post by **WildBill** » Wed Apr 05, 2017 9:16 pm

Howdy

I used it and it is an excellent tool. Senor Bigfoot has done a great piece of work.

I used it enough on various scenarios and cross checked with other applications and methods sufficiently to be very confident in the results. It is superb for scenario analysis.

Having said that, the chief source of uncertainty is of course your own input variables - growth rates for asset classes, and inflation rates and longevity etc.

I do not see that a beginners and advanced model split to be of much value. You can run simple scenarios with by minimizing the range of inputs.

My 2 centavos

Happy scenario planning

W B

perrywinkle · Post by **perrywinkle** » Wed Apr 19, 2017 7:03 pm

Superb work and very useful. Thank you.

In doing a lot of cross checking I believe I have found a bug. On both the Full Case and Base Case, Line 194 is supposed to calculate the Federal Tax Exempt Income which is subject to State Tax. The formula refers to cell F$157, which is the non-taxable earnings in taxable accounts vs. F$156 which is the Tax-exempt investment earnings. I found this as I have a large amount of muni / tax-exempt income which was not showing up in the state AGI and hence was not taxed. Perhaps I have not understood the intent of the items on lines 156 and 157, but when I edited the formula the numbers were as I expected, so that's what I am using.

Peter Foley · Post by **Peter Foley** » Wed Apr 19, 2017 11:13 pm

jacksprat

I had to run at least a half dozen scenarios to gain comfort with the output. I ran similar scenarios in the RPM and i-orp. While the two calculators are different and have different purposes, RPM is definitely superior when it comes to larger scale Roth conversions. By larger scale I mean conversions which, if done per the i-orp calculator would have resulted in more taxes paid and a lower net worth at the 20 year mark. I used the RPM model for my Roth conversions this year and expect to do so next year - next year likely being my last year for Roth conversions.

I think the i-orp output is a bit clearer but perhaps that is because it is more limited. I did study the outputs of the i-orp tab in Bigfoot's model.

BigFoot48 · Post by **BigFoot48** » Thu Apr 20, 2017 9:49 am

perrywinkle wrote:In doing a lot of cross checking I believe I have found a bug.

You did indeed! In the example data this resulted in about of $2,000 different ending portfolio balance as the tax-exempt asset earning were close to the estimate of non-taxed amounts included in annual earnings from the taxable account, but as you noticed if you have a significant tax-exempt amount the error becomes obvious.

Thanks for reporting this. The model has been updated but it may not be worth the effort for most users to update unless they have a significant tax-exempt portfolio balance.

Speaking of which, a very smart Boglehead has been working on a macro that transfers all the setup entries from an older version to a new version, eliminating this tedious effort. It works like a charm and it will be included in the next release, whenever that happens.

Zephavest · Post by **Zephavest** » Thu Apr 20, 2017 6:20 pm

BigFoot48 wrote:
perrywinkle wrote: Speaking of which, a very smart Boglehead has been working on a macro that transfers all the setup entries from an older version to a new version, eliminating this tedious effort. It works like a charm and it will be included in the next release, whenever that happens.

This is great to hear, THANK YOU to the "very smart Boglehead" who is helping on this and of course thank you to BigFoot for his continued excellent work. I can't wait to see the release that will include the new transfer macro.

GeoGibb24 · Post by **GeoGibb24** » Mon May 15, 2017 9:57 am

Is there any write-up on how to use the RPM?

smitcat · Post by **smitcat** » Mon May 15, 2017 10:44 am

"Is there any write-up on how to use the RPM?"

I hate to admit - but I agree with you.
I have this model filled out about 75% but there are some inputs I am unsure what it is exactly asking for.
Knowing that garbage in = garbage out I am hesitant to complete it.
So being too ignorant to know has me stumped....

BigFoot48 · Post by **BigFoot48** » Mon May 15, 2017 11:04 am

I'm sorry about the difficulty in using the model and it's one of my goals to improve that. In the meantime, please note that every input factor has a cell description in the title or entry explaining it, and every section heading, such as Portfolio Balances, has a description. There's also a description of how a typical retiree uses the model at the far left cell of each section heading.

On the Readme page there's a section on how to use the model:

Quick Start Instructions and Model Overview

1. Enter the values for your personal data, portfolio amounts, and estimates for earnings rates, inflation and other factors. Be sure to replace all the Example data with your own, or use the macro button to automatically clear the example data. Hover over cells with red triangles to see comments, instructions and help.

more..

Example of instructions on Setup page - Taxable Account Beginning Balance:

Enter your current or forecast starting balance for your taxable accounts. Include brokerage, savings, credit union, etc. accounts.

Withdrawals are not subject to income tax.

ubermax · Post by **ubermax** » Mon May 15, 2017 4:00 pm

BigFoot48 , you created this great tool and have apparently been tweaking/updating it for 5 years now , wow ; just curious do you have a succession plan or is it the intent/desire that users will download the spreadsheet and do their own maintenance ??? or maybe this is an enjoyable enduring hobby for you ??

BigFoot48 · Post by **BigFoot48** » Mon May 15, 2017 4:23 pm

Thanks ubermax. I intend to continue to maintain and tweak the model as a retirement hobby for the foreseeable future, but I may follow Simba some day and the model may be adopted by a new manager or drift off into oblivion.

Now, back to my new hobby, genealogy research!

sandramjet · Post by **sandramjet** » Tue Jun 06, 2017 3:50 pm

I just noticed something by accident, and wonder if it is by design or not...

I created a model and did not bother to put an "ending" age in for one of the two people. Interestingly, that means that the "age for switching to single" tax model was shown as "0", and more surprising (at least to me) was that it appears that as a result all years are taxed at the single rate.

Just curious as to whether this is by design or inadvertent? It is not a big deal in the sense that obviously one or the other should have an ending age at some point; just surprised that there was no note about this.

thanks again for a great tool!

BigFoot48 · Post by **BigFoot48** » Tue Jun 06, 2017 4:58 pm

sandramjet wrote:I just noticed something by accident, and wonder if it is by design or not...

thanks again for a great tool!

Thanks. Just an oversight, or was it on purpose to force the user to insert a year? Either way, I'll fix it somehow in the next release.

BigFoot48 · Post by **BigFoot48** » Wed Jun 07, 2017 9:42 am

sandramjet wrote:I created a model and did not bother to put an "ending" age in for one of the two people. Interestingly, that means that the "age for switching to single" tax model was shown as "0", and more surprising (at least to me) was that it appears that as a result all years are taxed at the single rate.

I have fixed this error and the model has been updated. The default value of "0" in the "year single rate starts" field if no "End Age" was entered for either person was forcing the use of the single rate factors for all years. (Changing this default value in the "year single rate starts" formula at E175 to blank ("") fixed it should anyone want to just fix their model.)

Users filing jointly who do not enter an early End Age for one of the persons should update to the revised version. If you use an End Age, such as used in the example data, the previous model is calculating taxes correctly.

Many thanks to sandramjet for finding and reporting this problem.

LeeMKE · Post by **LeeMKE** » Thu Jun 08, 2017 5:35 pm

I'm unable to find the source of my error.

In setup, no matter what value I enter, the Ending Balances for Taxable is a negative number (up to -$500,000, but never less than -$450,000)
Setup / 2. Portfolio Balances / Taxable

I've compared my entries with the original file, but don't find where I went wrong.
The original file seems to work correctly.
I've traced back cell by cell to check the embedded formulas, and they match the original file.
I have avoided the macro that erased the sample entries and typed my data into the original file. But I get the same results.

Is this a bug or a feature?

Have you had anyone else with this issue?
Other suggestions?

BigFoot48 · Post by **BigFoot48** » Thu Jun 08, 2017 6:10 pm

LeeMKE wrote:In setup, no matter what value I enter, the Ending Balances for Taxable is a negative number (up to -$500,000, but never less than -$450,000)

The usual reason this happens is that expenditures from taxable exceed the funds going in via IRA/Roth withdrawals. You might also try turning on the "General Settings: Use auto withdrawals" and see if that helps. (I haven't tested that in a long time so...).

Look on the Summary page at the far right column which is "To Taxbl", showing the total funds flowing into taxable each year. Unless you have a very large taxable account, there should be positive amounts in each year showing the funding required to keep taxable positive.

The model requires the user to keep the taxable account balance positive (if not using auto withdrawals) so review what is going into Taxable to make sure it's adequate to cover expenses, taxes and other withdrawals.

LeeMKE · Post by **LeeMKE** » Thu Jun 08, 2017 9:50 pm

The model requires the user to keep the taxable account balance positive (if not using auto withdrawals) so review what is going into Taxable to make sure it's adequate to cover expenses, taxes and other withdrawals.

Yup! That fixed my problem. I never found the"General Settings: Use auto withdrawals" but once I grabbed the withdrawal amount for the first year in the accounts, the spreadsheet worked.

Thanks!

Zephavest · Post by **Zephavest** » Sat Jun 24, 2017 8:13 am

Hello Big Foot,

I just downloaded both versions this morning, I got this error in the 17b version, 17 was fine. This happened when selecting the "Clear" sheet function.

Microsoft Run-Time error '1004'
The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet.
When clicking on Debug, it takes you to:
Selection.ClearContents

I then unprotected the Setup tab and retried the 'clear' function, it seems to work fine.

Is this a bug to be concerned about or just continue on?

Best Regards, Zephavest

BigFoot48 · Post by **BigFoot48** » Sat Jun 24, 2017 8:51 am

Zephavest wrote:I just downloaded both versions this morning, I got this error in the 17b version, 17 was fine. This happened when selecting the "Clear" sheet function.

Is this a bug to be concerned about or just continue on?

Thanks. Just a bug which I just fixed in the "xb" version and have uploaded it. Cells O13-15 on the Setup page needed to be unprotected for the Clear macro to not get hung-up there. Not being unprotected was a byproduct of process that hides the Base case in this special version. I plan to eliminate this version in the next release to help avoid the extra work maintaining two requires. And if it all works out, the next version will be somewhat simplified which will remove all the "hide Base case" complexity.

Zephavest · Post by **Zephavest** » Sat Jun 24, 2017 9:00 am

Hi BigFoot,

Thank you so much for the fast reply as usual. I pulled the trigger in April and retired. I'm just now having time to get back to some serious planning for my retirement cash flow in detail. Yours is the only tool I've found to give me the insight of the details to instill confidence in your own personal plan. We have the move from California to Texas behind us and the new home is under contract, time to start planning the next phase.

Best Regards, Zephavest

Diynofees · Post by **Diynofees** » Fri Jul 07, 2017 4:29 pm

Hi Bigfoot! Been familiarizing myself with your RPM tool since I discovered its existence a little while ago. Great job! ... and a real service to folks out there who want to take a serious look at their retirement financial picture.

I have a question regarding the philosophy behind the social security calculations and display...where one is modeling the earlier death of a spouse (like George in your provided example), why is his social security benefit still being paid out after his death? Shouldn't proper SS benefit amounts only be showing up in Martha's ("Spouse") column after that point? There's also a comment on Setup sheet cell H93 (named "Your_SS_End") that says "If this is blank, these benefits will continue to the end of the modeling period." (It's not really blank in the computer sense, it contains a double-dash "--".) Anyway, I'm uncertain as to what is happening here and why.

Thanks and best regards.

BigFoot48 · Post by **BigFoot48** » Fri Jul 07, 2017 11:26 pm

Diynofees, I will be glad to answer your questions but I'm away from my model for the next week and need it to confirm the answers i have are correct. Stand by!

sandramjet · Post by **sandramjet** » Sat Jul 08, 2017 12:24 am

Diynofees wrote: I have a question regarding the philosophy behind the social security calculations and display...where one is modeling the earlier death of a spouse (like George in your provided example), why is his social security benefit still being paid out after his death?

If you play around with the model I think you'll see that this is the survivors benefit, continuing with the larger of the spouse or your benefit. That is, if your benefits are larger than the spouse, your benefits will show as continuing. If the spouses benefit are larger, then the spouses benefits continue. I think the confusion might be coming from expecting that George's survivor benefit would be displayed in the line corresponding to "Martha", but that is not how it is displayed. You should see that Martha's "spousal" benefit terminates at that same year (as it should, since she is now collecting a survivor benefit, not a spousal benefit). So the net effect is correct.

Diynofees · Post by **Diynofees** » Mon Jul 10, 2017 6:16 am

Thanks Sandramjet...I guess I was being too much of a purist in viewing the output! I see your point exactly now and how Bigfoot has chosen to display Social Security benefits. I think I was also getting a little confused by the some of the cell comments attached to the relevant input cells on the Setup sheet. Thanks again for your clarifications.

sidneyinplanning · Post by **sidneyinplanning** » Fri Sep 15, 2017 4:22 pm

Question to Bigfoot48:
Try to work on the v17.0 model and get to some difficulties:
a) 1 spouse will start in 2017, another will work till 2023 - so there will be 401K contribution at 20% and 3% employer match. How can I enter that stream of income?
b) first year under Ages and Year Factor. 2017 is only for 1 stops working, see a) above. Got error message in typing 2024 in cell E22
c) should the age of y must be younger and for the spouse older? I cannot effectively altered the end age.

Or should this model be used when both couple has retired?

BigFoot48 · Post by **BigFoot48** » Fri Sep 15, 2017 7:39 pm

Sidneyinplanning:
The model is generally designed for retirees, with a provision for including additional working income in the early years.

Entered IRA/401k amounts are assumed to be the balance in the starting year, but for your situation I would set the starting amount such that, including the calculated earnings over the next few years, the model shows a balance in that account in the spouse's retirement year approximating the amount the spouse expects to receive. I don't see a need to include the annual additions to the 401k as its all in a tax-deferred account and the important thing is the balance at retirement.

There can be only one start year for the model calculations. The two ages in the starting year can be anything (more or less) and the "end age" is only used to model impact on the portfolio from the death of a spouse.

Hope that helps your somewhat unique situation.

sidneyinplanning · Post by **sidneyinplanning** » Fri Sep 15, 2017 9:11 pm

Thank you so much. I will enter the expected balance of the 401K as in 2024. Likewise, all balances of 2024 the year 1. Again thanks a lot.

sidneyinplanning · Post by **sidneyinplanning** » Sat Sep 16, 2017 12:39 pm

minor issues in saving the model:

BigFoot48 · Post by **BigFoot48** » Sat Sep 16, 2017 3:46 pm

sidneyinplanning wrote: ↑Sat Sep 16, 2017 12:39 pm minor issues in saving the model:

What version of Excel are you using? This is a false error that Excel sometimes exhibits (other Excel users have reported it on their spreadsheets as a Google search reveals). The validation rules for all those cells listed refer to other cells within the same worksheet, Setup. I would ignore it.

The model was created and is maintained using Excel 2003 and I continue to use the original format xls. The model is tested annually on other Excel versions and other spreadsheet programs and I don't recall seeing this error message, but have seen other weird false ones from time to time.

sidneyinplanning · Post by **sidneyinplanning** » Sat Sep 16, 2017 11:24 pm

Excel 2010. I will ignore such false error.

Per your advice, I am doing a projection of my assets by IRA1, IRA2, Roth 1, Roth 2 and Taxable in 2024. Then I will input the data as in 2017 and input the age as age in 2017+7. In this way, when I get the result, I just see 2017 as 2024.

munemaker · Post by **munemaker** » Tue Nov 07, 2017 10:35 pm

I have really been digging into this spreadsheet. It is very impressive and is tremendously helpful. My complements to the creator.

However, the spreadsheet does have what seems to be a bug:

In the SETUP tab, under "3. Return Rates and Allocation," there is an feature "optional, future asset allocation and start age". The instructions say if you enter a 0 in cell E72 or leave it blank, this feature will not be used. So I did that. I tried it both ways...0 and blank. In both cases, cell G72 says "not using," but when I look at the FULL CASE tab, row 251, I can see that the feature seems to be indeed being used because it shows tax exempt interest, while I have not entered any tax exempt asset values.

As an experiment, I changed cell E72 on the setup page to 101 (my spreadsheet ends at age 98). When I did this, cell G72 says "using" but since it is beyond the end of my spreadsheet, it has no effect on the results. After entering 101, the tax exempt interest values in FULL CASE tab, row 251 all disappeared. That seems to prove that this feature is being used when a 0 or blank are entered in cell E72 of the SETUP tab.

I happened to notice some suspicious results, and it took me a while to find this because row 251 is hidden (by default) on the FULL CASE tab.

I am pretty sure this is a bug. I am hoping some other user(s) can confirm it.

How do I report this?

BigFoot48 · Post by **BigFoot48** » Wed Nov 08, 2017 8:06 am

munemaker wrote: ↑Tue Nov 07, 2017 10:35 pm How do I report this?

You just have. I'll check it out.

The rate change feature appears to be working as designed. The rates being controlled are in lines 22-25 and entering a zero or blank will keep the default rates in use.

However, it does appear the tax-exempt calculation may not be working properly. Checking....

I have found what was causing the optional tax exempt allocation to be used even if the optional allocation option was turned off. The model will be updated today to fix this. If users don't want to update, a quick solution is to make the optional tax exempt allocation the same as the default tax exempt allocation. So if you have 0% tax exempt just set the optional allocation rate to 0% also, even if you don't use the allocation-change option.

This problem appears to only impact the tax-exempt allocation.

Thanks to munemaker for finding, analyzing and reporting this problem. The downloadable model has been updated. (Version 17.0xb, which excluded the base case, has been removed as 18.0 is being redesigned to address the problem of too much data from the two-case model.)

munemaker · Post by **munemaker** » Wed Nov 08, 2017 10:38 am

BigFoot48 wrote: ↑Wed Nov 08, 2017 8:06 am
munemaker wrote: ↑Tue Nov 07, 2017 10:35 pm How do I report this?
You just have. I'll check it out.

The rate change feature appears to be working as designed. The rates being controlled are in lines 22-25 and entering a zero or blank will keep the default rates in use.

However, it does appear the tax-exempt calculation may not be working properly. Checking....

I have found what was causing the optional tax exempt allocation to be used even if the optional allocation option was turned off. The model will be updated today to fix this. If users don't want to update, a quick solution is to make the optional tax exempt allocation the same as the default tax exempt allocation. So if you have 0% tax exempt just set the optional allocation rate to 0% also, even if you don't use the allocation-change option.

This problem appears to only impact the tax-exempt allocation.

Thanks to munemaker for finding, analyzing and reporting this problem. The downloadable model has been updated. (Version 17.0xb, which excluded the base case, has been removed as 18.0 is being redesigned to address the problem of too much data from the two-case model.)

Thanks for responding and taking care of this so quickly!

Again, thank you for developing Retiree Portfolio Model. As a recent retiree, it is the perfect tool for me right now as I evaluate various alternatives going forward. I can only imagine the hundreds of hours you have invested in it.

BigFoot48 · Post by **BigFoot48** » Wed Nov 08, 2017 11:06 am

munemaker wrote: ↑Wed Nov 08, 2017 10:38 am Thanks for responding and taking care of this so quickly!

Again, thank you for developing Retiree Portfolio Model. As a recent retiree, it is the perfect tool for me right now as I evaluate various alternatives going forward. I can only imagine the hundreds of hours you have invested in it.

Thanks very much. It's one of my retirement hobbies, and my personal retirement forecasting tool, so I want it comprehensive and accurate too. Help from users finding problems or offering suggestions is a big help. (I try not to dwell on the number of hours spent. Someday it may just be updating the annual tax rates!)

goGators · Post by **goGators** » Wed Nov 22, 2017 11:41 am

Hi BigFoot,
If my FRA age is 67, should the formula in cell R93 = R$98*$U93?
Currently, you have cell R93 = R$97*$U93
Same question for cells R94, R95, R96, R107, R108, R109 and corresponding cells for spouse whose FRA age is also 67.
I'm using V.17-Nov8, 2017
Thank you & Happy Thanksgiving

BigFoot48 · Post by **BigFoot48** » Wed Nov 22, 2017 12:34 pm

goGators wrote: ↑Wed Nov 22, 2017 11:41 am Hi BigFoot,
If my FRA age is 67, should the formula in cell R93 = R$98*$U93?

Possibly, and I will check it out! However, that is a calculator to test benefits and is not used to calculate benefits for the model. The actual model calculator is in the normally hidden area AB98 to AH116 and appears to be working for a 67 FRA.

I am thinking about abandoning the SS calculators now the FRA is changing and just letting users input the benefits they have determined they will receive, much like ORP is now doing. Probably won't happen for version 18.0 though.

[Update] The SS calculator appears to be working for FRA 66 and 67.

Peter Foley · Post by **Peter Foley** » Fri Dec 08, 2017 10:02 am

BigFoot
There is an interesting discussion in this thread that has relevance for your RPM:

viewtopic.php?f=2&t=233796

The basic question is that when running scenarios, should one set all accounts to the same rate of return? Conversely, one could argue that because many investors hold primarily stocks in the Roth accounts and bonds in tax deferred, that setting different rates for these accounts reflects reality.

What would be your recommendation with regard to looking at Roth conversion scenarios with RPM?

BigFoot48 · Post by **BigFoot48** » Fri Dec 08, 2017 10:32 am

Peter Foley wrote: ↑Fri Dec 08, 2017 10:02 am What would be your recommendation with regard to looking at Roth conversion scenarios with RPM?

My recommendation is to set asset allocation and estimated returns in each account to what the user actually holds and expects in them. I think that will result is a better forecast doing conversions as funds are moved from a bond-heavy IRA to a Roth that has more flexibility in its allocation and may have more stock holdings to benefit heirs.

sandramjet · Post by **sandramjet** » Fri Dec 08, 2017 3:10 pm

BigFoot48 wrote: ↑Fri Dec 08, 2017 10:32 am
Peter Foley wrote: ↑Fri Dec 08, 2017 10:02 am What would be your recommendation with regard to looking at Roth conversion scenarios with RPM?
My recommendation is to set asset allocation and estimated returns in each account to what the user actually holds and expects in them. I think that will result is a better forecast doing conversions as funds are moved from a bond-heavy IRA to a Roth that has more flexibility in its allocation and may have more stock holdings to benefit heirs.

I agree with this. There is a fundamental difference in that i-ORP solves an optimization problem so it will inherently leverage any differences in account AA settings, whereas RPM is not doing any optimization, but just a straight calculation.

munemaker · Post by **munemaker** » Tue Dec 26, 2017 12:54 am

BigFoot48:

I am curious about whether you are planning to update RPM to the new tax laws that go into effect in 2018?

Thanks again for providing RPM. It has been a tremendous help to me and it would be awesome if you continue to update it.

Thanks!

BigFoot48 · Post by **BigFoot48** » Tue Dec 26, 2017 8:12 am

munemaker wrote: ↑Tue Dec 26, 2017 12:54 am BigFoot48:

I am curious about whether you are planning to update RPM to the new tax laws that go into effect in 2018?

Thanks again for providing RPM. It has been a tremendous help to me and it would be awesome if you continue to update it.

Thanks!

I'm glad you like this planning tool! Yes, the new rates and brackets are now in it (a thanks to Congress for not adding 5 new brackets which would have made the update a PIA) and I'm planning to release it Jan 1. The new version includes a macro written by a Boglehead member that will transfer nearly all user entries from an earlier version to this one with one click, so it will be easy to instantly see one's results under the new rates.

BigFoot48 · Post by **BigFoot48** » Mon Jan 01, 2018 8:36 am

A new version of the Retiree Portfolio Model model is now available. This includes the new 2018 tax rates and brackets, along with changes to the formatting and numerous improvements and updates. I recommend all users download it.

Version 18.0 can be downloaded from Dropbox via this link: https://www.dropbox.com/s/ud2r1418hwuev ... .xlsm?dl=0

Updates:
May 14: Extra Federal exemption age checking bug fixed. Beginning with 2018 tax year its not used. No need to D/L.
Jan 21: Minor formatting, labeling and a graph addition. Model date updated to Jan 21. (download only if Summary page shows ### in some columns)
Jan 18: Corrected cell notes that said SS benefits needed Medicare deducted to calc correct taxable amount. A BH pointed out that Gross = Net for this calculation. (no download needed)
Jan 13: Fixed Clear Entries macro to work in LibreOffice Calc. (download only if you use LibreOffice)
Jan 11: Setup page Cash Flow Chart was missing (forever?) the Special Events item. Added. (download only if you use this chart)
Jan 8: The IRA 2 withdrawals that have a starting and ending year had an error (using the "base" case years) that didn't allow a year limitation. Fixed. (no download needed as this error had self-corrected in v18.0)
Jan 2: Displayed marginal rate fixed, PM reported formula default fixed (no impact on results) and Roth conversion comparison graph added to Setup and Results pages per request. (download for accurate marginal tax rate display)

This version has formatting changes to shift the focus from a Roth conversion comparison model to the full-featured retiree portfolio forecast model it has evolved to over the years. The instant Roth conversion and alternative Social Security benefits comparison tools are still there but now have a more minor role. Future changes will focus on enhancing the presentation and analysis of portfolio results for the 90% of users who will never do a Roth conversion.

The portfolio results using the same example data used in the 17.0 version are identical in 18.0, when taxes are excluded. The example data for 2018 has been changed, increasing intemized deductions to above the new much higher standard deduction amount and doing Roth conversions up to the top of the new 12% bracket.

A significant time-saver has been added this year thanks to the contribution of Boglehead member mjf55. He provided a macro that will copy all the setup data from an earlier model to this one in about one second. Note that because of model changes, some data, mainly Roth conversions, will not be copied from 17.0 or earlier models. Also any new setup fields, such as the changed IRA withdrawals for the Base case (few, if anyone, use this), will not be populated with data as it didn't previously exist. Many thanks to him for this improvement.

Another change is that the model is now provided in the Excel 2007 xlms format. This will allow future enhancements using newer Excel features (although there are none used in this version). It can still be opened by Excel 2003 but a "data validation" error may be shown. I believe that is a false-positive and no data is lost if opened in 2003. The "m" in the extension indicates the model contains macros. As always, macros do not need to be used or activated to use the model, they just make it easier to do certain things like loading previous model entries and jumping around the model.

Changes in version 18.0 include:
> Updated tax rates, brackets, personal exemption, and standard deductions to 2018 amounts from the recent tax law change.
> Added a macro that will copy nearly all Setup page factors from a previous RPM model to the current one. Many thanks to BH mjf55 for this big time-saver!
> Added labels for all macros describing what they do and showing which macro is being used.
> Made many formatting changes to focus on the main default (full) case that includes all options. The comparison base case which excludes Roth conversions, and the comparison data have been assigned to a more minor presence in the model.
> Fixed QCD expenditures to correctly show impact on the taxable account balance. If you use it, this fix impacts taxable account earnings and increases taxes.
> The discount rate used to determine present value of future cash flows, is reduced to a real rate (after inflation) of 3% to provide a more realistic real-return result.
> Changed from Excel 2003 file type xls to Excel 2007-2016 type xlms.

Significant new features usually come with problems so please contact me via PM or post in this thread with any issues. A Happy, Healthy and Prosperous New Year to all!
132,500 views

sandramjet · Post by **sandramjet** » Tue Jan 02, 2018 12:32 am

Does the tax model include the reversion to the current brackets in ten years back to what they are today?

BigFoot48 · Post by **BigFoot48** » Tue Jan 02, 2018 1:38 am

sandramjet wrote: ↑Tue Jan 02, 2018 12:32 am Does the tax model include the reversion to the current brackets in ten years back to what they are today?

No, but the optional tax rate change feature in Setup Section 9 could be used to change the rates (but not brackets, deductions and exemptions) in any future year if you want to test a reversion.

sandramjet · Post by **sandramjet** » Tue Jan 02, 2018 2:39 am

I quickly tried out this version, and haven't checked everything carefully yet. But when I look at the "Results" tab, the chart titled "Marginal Rates" seems to show the old rates (25, 28 percent, etc).

Also, maybe it is just because I have to get used to the new look, but I kind of miss the summary chart that used to be on the setup page with the "base" and "full" case portfolio value vs years. As I struggle to decide whether to do Roth conversions, it was a nice graphical way to show the relative differences. Is that chart still available on other tabs? ( I may have just missed it...)

Thanks as always for all your efforts!

BigFoot48 · Post by **BigFoot48** » Tue Jan 02, 2018 8:46 am

sandramjet wrote: ↑Tue Jan 02, 2018 2:39 am I quickly tried out this version, and haven't checked everything carefully yet. But when I look at the "Results" tab, the chart titled "Marginal Rates" seems to show the old rates (25, 28 percent, etc).

Thank you for reporting this finding! The marginal rate calculation for the Base case is indeed using the old rates, although the tax calculations are using the new rates. This was due to an extra tax table I had inadvertently had the Base case marginal calculation reference to, rather than the current rates.

So this is a display problem, not affecting the portfolio calculations. Nevertheless, it will be fixed today. Those doing Roth conversions or SS benefit comparisons will want to download later today.

I removed some charts to simplify things and the one you liked apparently was one. I will see if I can put that back somewhere since you found this rate display error!

UPDATE Jan 2: Displayed marginal rate fixed, PM reported formula default fixed (no impact on results) and Roth conversion comparison graph added to Setup and Results pages.

kd2008 · Post by **kd2008** » Mon Jan 08, 2018 5:16 pm

Thank you very much for this excellent tool! Wonderful indeed!

I am trying to model a scenario where one spouse is retired and the other is still working with contributions to IRA and 401k etc. Is it possible to model this?

I was able to input working spouse income in the pension 2 but the tool assumes all the savings go to taxable account. Is there a way to include them in the IRAs instead?

Thanks a bunch!

BigFoot48 · Post by **BigFoot48** » Mon Jan 08, 2018 6:35 pm

kd2008 wrote: ↑Mon Jan 08, 2018 5:16 pm I am trying to model a scenario where one spouse is retired and the other is still working with contributions to IRA and 401k etc. Is it possible to model this?

Unfortunately there's no way to model that in the current version, although I see how it might be useful for near-retirees. I would suggest forecasting the future IRA amount and entering a current year starting amount such that with the yearly earnings the future year "beginning" IRA amount is approximately what you expect to have.

On a side note, while looking at your issue I noticed the IRA2 withdrawals years had an error (it was using the Base case withdrawal years). This error had been around for many versions. Maybe no one uses it, or don't limit withdrawals to a few years? The error was bypassed in version 18.0 by other changes but I fixed it anyway.

goGators · Post by **goGators** » Thu Jan 11, 2018 10:01 am

I need a little help here. I cannot execute a macro in version 18.0. My mouse pointer does not change to a hand when I hover over a macro.
I'm using LibreOffice 5.3.7.2 and OS is win10x64. I have no problem with v17.0. Any idea? Thanks.

BigFoot48 · Post by **BigFoot48** » Thu Jan 11, 2018 10:58 am

goGators wrote: ↑Thu Jan 11, 2018 10:01 am I need a little help here. I cannot execute a macro in version 18.0. My mouse pointer does not change to a hand when I hover over a macro.
I'm using LibreOffice 5.3.7.2 and OS is win10x64. I have no problem with v17.0. Any idea? Thanks.

I tested it in my copy of LibreOffice 5.3.7.2, win10x64 and the macros work (a few, like Clear, may not work due to the different code that LibreOffice uses). Check the setting at Tools > Options > Security > Macros Security. Mine is set to "Low".