Retiree Portfolio Model

[Zephavest]

 - Do your different scenarios show clear result differences?

Yes they do and even if the percentage is small, that is just fine with me. If you can keep your scenarios always having a positive balance at the end it is a good plan, regardless of percentage remaining. I want to assure we or my surving spouse do not run out of money.

 - How big a difference do you consider significant? 1%, 10% or ??

See answer number 1.

 - Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?

You are correct, the big picture should contain many more factors then just the ending balance. In one of my early plans I would start my pension at age 63 and Social Security at age 68. I did this in light of aligning my Roth conversions to max out the 12%/15% bracket. In a later plan I noticed at age 71 my RMD along with my Social Security and Pension were fully supporting our living expenses, which also had the unintended consequence of minimizing the amount of Roth conversions I could do while keeping within the 12%/15% tax bracket. In studying a solution for this I realized if I delayed starting my pension from 63 until 65 and delayed starting my social security from 68 to 70 I could increase the amount of my Roth conversions between age 62 and 70, before the RMD kicks in, which also helped reduce the RMD amount and the associated taxes. Further as part of this step I realized it is better for us to live off of our existing Roth accounts during these intervening years in order to maximize IRA distributions and keep them maxed at the 12% bracket.

Another deciding factor was that by maxing out the Roth conversions I was minimizing the remaining IRA balance and reducing the inheritance taxes our daughter would have to pay on the inherited IRA. This plan has been working will for several years now and has the added benefit when the just passed "SecureAct" hit that causes the loss of the inherited stretch IRA's, it turns out my plan in minimizing the inherited IRA balance is spot on and my daughter still will be better off with reduced IRA taxes, even though she has draw down the inherited Roth within ten years.

Regardless of my annual cash flow needs, pre-Pension and pre-Social Security, I max out my Roth Conversions as it allows my to pay the lowest marginal tax rate. Then I withdraw annual income, as needed, from the Roth account. As far as cash needed to pay for the Roth conversion taxes for the first two years of retirement it came from the taxable account, but as that was spent down I now pay for it out the Roth Distributions I take for annual income. This is contrary to what some use as the "wise tale rule of thumb" that you can only do Roth Conversions if you have separate cash to pay the taxes. That it a misconception, If I did not do a Roth Conversion, and just took IRA Distributions for annual income, where is the cash coming from to pay the taxes on that? You can see I'm paying the exact same taxes on the money, regardless whether the 1099-R says "IRA Distribution" or "Roth Conversion", same balance, same taxes due.

[smitcat]

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

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

I'm curious what others have found in terms of:
- Do your different scenarios show clear result differences?
Yes - very clear
- How big a difference do you consider significant? 1%, 10% or ??
Some are over $400K of "spendable" dollars over the full cycle
- Other than just ending balance differences, what other metrics do you use to evaluate the scenarios?
We see no merit in measuring just ending balances - we only find value in measuring 'spendable' dollars after tax between us and our heirs for the full cycle.

[BigFoot48]

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.

Excellent bug find and report! There was indeed a programming error in the Federal Tax Exempt income inclusion in state AGI which resulted in the future state factor being used for the current state if the future state year was blank. The Example data which uses a future state didn't reveal this, and likely most users don't have Federal exempt income, so it hasn't been noticed.

Version 20.0c that fixes this is now available. Only users that have Federal Tax Exempt income may need to upgrade. If no future state year is used then having the same "Tax-exempt investment income taxable in state" in the future state setting will also eliminate the problem. Link: https://www.dropbox.com/s/vrz9xd69c0rxk ... .xlsm?dl=0

I want to keep the model as simple as possible so the three state brackets should be used as best they can for states with more brackets.

Thanks for reporting this state tax calculation problem.

[Delphic]

Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.

[sandramjet]

Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.

While I have no opinion on the VLOOKUP/HLOOKUP issues, I certainly disagree about the base case not being of any value. I look at the differences in various sections between current and base for lots of things. And roth/non roth is not the only thing I am looking at. Looking at SS differences, comparing various adjustments in the detail/base cases (like modifying AGI, Expenses, etc... ) I would be disappointed if the base disappeared.

[Zephavest]

Two things make it hard to mod (and maintain and develop) RPM.

One is Base Case which is a huge lead weight since everything has to be repeated there. And it is no value since one or two mouse clicks goes from Roth to non-Roth = Base. Prob seemed like a nice bell & whistle but should be torn out and forgotten.

Another is using VLOOKUP and HLOOKUP instead of Index / Match. Terrible practice which means any change in the lookup areas breaks many long hairy formulas.

With things like this the model is very hard to change or add stuff such as Roth for second IRA. Want to see this live on so take out things that make it tough to work inside it.

Hi Delphic,

Welcome to the Bogleheads forum and RPM since this is your first post maybe you did not look at the history or read the entire thread? Countless other users have found the selfless contributions from BigFoot48 invaluable in that he has provided his personal model to the community. Not only did he develop it but he has continuously maintained it since 2012. He has added a number of new features at users suggestions when they may help the community at large. As an Excel user myself for over 30 years I know there are multiple ways to achieve the desired outcome within Excel. Like any design decisions, the creator, designer, engineer, etc. makes the best use of the tool as he/she sees fit. It is good to review some of the key information in the first post of this thread:

The model I'm providing is my personal model that I use periodically, entering my own portfolio, estimate of earnings, expenses and anticipated changes we think will happen, to look at the conversion decision. (I've got another five years until RMD and the related taxes kick in and any conversion benefit goes away for good.)  In addition to the conversion analysis, and since the model includes Social Security benefits as an income source, I did add one feature that I can't use - a way to quantify the "when should I start Social Security benefits" decision. (We started at 62.)  That feature also quantifies the SS decision process, and could be useful to many.

So, as my personal model, use it at your own risk.  It may help you or it may not.  Your situation may be more complex and the model be of only limited value.  Your data may cause it to make bad calculations.  It may indicate a conversion advantage because you used a 12% earnings rate.  Lots of things can be indicated from the data entered, so use it as a start in your conversion decision, or SS benefits decision, and when in doubt, create a model! (Above updated Dec 2013)

So when you find RPM no longer meets your personal needs or you need some additional information outside of RPM, take BigFoot's advice and "create a model!" of your own.That is what I did. After years of using RPM, modifying it as I wished for my own personal needs, there came a day when I wanted some additional information to meet my own personal needs so I created my own side model for those things. Did I have to carefully study the formula's to understand them, sure, but I learned a lot along the way.

Please try to start with compliments and not complaints, telling the author his design decisions are terrible would indicate that this may not be the tool for you.

If a user is having too much difficulty with it, I suggest the excellent ORP model, which is very easy to use and provides nearly identical results in calculating future portfolio results. https://www.i-orp.com/ZERO/index.html

[gts1952]

I do not understand the function of line 171 in the "Details" tab, and what inputs are being used to generate the number. In my case it generates a negative number that effectively offsets most of my (fully) taxable investment income. In an unmodified 20c version, it offsets $4000 of the $9600 taxable account earnings. The line description reads "Less other non-taxable earnings in taxable". On the same line there's a cell with a percentage value in it. The "Detective" tool did not yield any useful insights.

[BigFoot48]

I do not understand the function of line 171 in the "Details" tab, and what inputs are being used to generate the number. In my case it generates a negative number that effectively offsets most of my (fully) taxable investment income. In an unmodified 20c version, it offsets $4000 of the $9600 taxable account earnings. The line description reads "Less other non-taxable earnings in taxable". On the same line there's a cell with a percentage value in it. The "Detective" tool did not yield any useful insights.

See the "taxable account adjustment" in the 8. Income Taxes section on the Setup page for the source and explanation of that adjustment to gross taxable earnings.

[Joe3zz]

Thank you Bigfoot for your work on this tool!

Hoping someone can help/clarify. I entered our data in the current version of the Retiree Portfolio Model. My wife and I are essentially retired and have a 50/50 portfolio., age 63, no social security yet.

Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?

[sandramjet]

There is an input for "optional base case adjustment" on lines 219-224 of the setup page... However, I'm not quite sure I understand how to use it properly.

The note says:
Optional Base Case Adjustment: Making Roth conversions may require additional IRA withdrawals to fund Federal taxes. Use these optional adjustments to reduce the withdrawals in the comparison (base) case for these extra withdrawals. This will provide a more accurate comparison in evaluating the benefit of Roth conversions as those higher withdrawals are not needed if conversions are not being made.
This is a rarely used feature.

But it is not clear to me what the adjustment amount should be. I'm assuming that if you want to convert say 10K from traditional to roth, but will incur a $2k tax hit that must also come out of the traditional, then you would input a -2k value for that parameter since you wouldn't be pulling out as much if you have the tax money come from somewhere else. Is this a correct interpretation, or do I have the sign wrong (ie should use +2k since you would end up with 2k more staying in the traditional) .... or have I completely missed the boat on when/how to use this?

A related question is when you say this is a "rarely used feature" is that comment because people generally have $ for taxes elsewhere? In my case, I have exhausted all of my taxable accounts and have only traditional and Roth accounts to pull from.

Thanks!

[RangeleyLake]

Thank you Bigfoot for your work on this tool!



Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?

HI Joe3zz
I have been wondering the same thing. I did come across on the setup page section 8 - Taxable Account Adjustment on lines 275-276 that I think answers this questions. I have just started using this area. This might be the option we are both looking for.
I am still struggling as to what numbers to plug in there but I think this works. I am still not totally comfortable with that little section. (I am still playing around with it)

[BigFoot48]

Thank you Bigfoot for your work on this tool!

Hoping someone can help/clarify. I entered our data in the current version of the Retiree Portfolio Model. My wife and I are essentially retired and have a 50/50 portfolio., age 63, no social security yet.

Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?

The Taxable Account Adjustment section in the Setup "8 Income Tax" section includes a method of having distributed dividends and capital gains taxed at the marginal rate.

Use this setting to enter a capital gains amount that will result in an approximate capital gain tax amount being calculated after applying the bracket marginal rate.

[BigFoot48]

There is an input for "optional base case adjustment" on lines 219-224 of the setup page... However, I'm not quite sure I understand how to use it properly.

The note says:
Optional Base Case Adjustment: Making Roth conversions may require additional IRA withdrawals to fund Federal taxes. Use these optional adjustments to reduce the withdrawals in the comparison (base) case for these extra withdrawals. This will provide a more accurate comparison in evaluating the benefit of Roth conversions as those higher withdrawals are not needed if conversions are not being made.
This is a rarely used feature.

But it is not clear to me what the adjustment amount should be. I'm assuming that if you want to convert say 10K from traditional to roth, but will incur a $2k tax hit that must also come out of the traditional, then you would input a -2k value for that parameter since you wouldn't be pulling out as much if you have the tax money come from somewhere else. Is this a correct interpretation, or do I have the sign wrong (ie should use +2k since you would end up with 2k more staying in the traditional) .... or have I completely missed the boat on when/how to use this?

A related question is when you say this is a "rarely used feature" is that comment because people generally have $ for taxes elsewhere? In my case, I have exhausted all of my taxable accounts and have only traditional and Roth accounts to pull from.

Thanks!

I suspected back in 2013 based on threads that most people doing Roth conversions pay the taxes from existing funds, and having to withdraw more to pay taxes was an issue. If additional IRA withdrawals are required to pay the taxes, this adjustment to the Base case was an attempt to negate the impact of those withdrawals so that just the impact of the conversions could be studied.

I don't know if this works or anyone has used it. There's never been a question about it.

[LadyGeek]

I removed an off-topic post. As a reminder, see: General Etiquette

We expect this forum to be a place where people can feel comfortable asking questions and where debates and discussions are conducted in civil tones.

...At all times we must conduct ourselves in a respectful manner to other posters. Attacks on individuals, insults, name calling, trolling, baiting or other attempts to sow dissension are not acceptable.

[Joe3zz]

Thank you both for the referral to section 8. Like RangeleyLake, I will need to figure out what to plug in there, but it is a start.

Thank you Bigfoot for your work on this tool!



Our taxable portfolio is essentially stock mutual funds and our IRAs are bond funds.

If I model our expenditures without withdrawals from our IRAs, it calculates our Federal Taxes as 0 for a couple of years, then calculates them off SS and some other factors when those kick in.

I see that the instructions say "It cannot calculate the taxes on withdrawal of funds containing unrealized capital gains in the taxable account. As a result, taxes will be understated on such gains included in the starting taxable account balance or earned over the years."

In our case, the the capital gains taxes on sales in our taxable account are a significant factor to consider. Is there some way to include/calculate some estimate of what they will be, so that the model considers accurate numbers? How are others addressing this when they use the model?

HI Joe3zz
I have been wondering the same thing. I did come across on the setup page section 8 - Taxable Account Adjustment on lines 275-276 that I think answers this questions. I have just started using this area. This might be the option we are both looking for.
I am still struggling as to what numbers to plug in there but I think this works. I am still not totally comfortable with that little section. (I am still playing around with it)

[RangeleyLake]

The Taxable Account Adjustment section in the Setup "8 Income Tax" section includes a method of having distributed dividends and capital gains taxed at the marginal rate.

Use this setting to enter a capital gains amount that will result in an approximate capital gain tax amount being calculated after applying the bracket marginal rate.

Thank you BigFoot48 for confirming what I thought would show LTCG and Qualified Dividends. When I started to play around in this section I stumbled across a couple of "#name?" error message has I was following the flow. It was on the Detail tab and Base tab on cell C175 ("IRA withdrawals" row) and C177 ("Inherited IRA withdrawals" row) the same cell reference for both tabs . This only happens when I select Y on "Use Automatic Withdrawals?" on the set up tab. On these cells it had a formula of =IF(Automate="y",Auto withdrawals included,""). When I change the formula on these cells and added quotation on "Auto withdrawals included" such as =IF(Automate="y","Auto withdrawals included","."), that resolved that error and gave me the message of "Auto withdrawals included" in on all of these cells. I was wondering if these cells was for only information purpose and/or if those cells are being used for any other part of this model which would impact any other results. Thanks again for a model that gives us a complete picture of our financial decision. ( I am not sure if you received a PM from me on this question sometime ago but was not sure if I was successful sent it. If I was successful in sending that PM sorry for the duplicate question.---still learning on how to use this forum.

[BigFoot48]

It was on the Detail tab and Base tab on cell C175 ("IRA withdrawals" row) and C177 ("Inherited IRA withdrawals" row) the same cell reference for both tabs . This only happens when I select Y on "Use Automatic Withdrawals?" on the set up tab. On these cells it had a formula of =IF(Automate="y",Auto withdrawals included,""). When I change the formula on these cells and added quotation on "Auto withdrawals included" such as =IF(Automate="y","Auto withdrawals included","."), that resolved that error and gave me the message of "Auto withdrawals included" in on all of these cells. I was wondering if these cells was for only information purpose and/or if those cells are being used for any other part of this model which would impact any other results.

Thanks for that report. That's just an alert message and has no impact on calculations. I suspect few people use the automated withdrawal feature or un-hide those lines and I seldom test the auto-withdraw option anymore so those errors were not noticed. I will fix it on the current release. No need for users to update.

[armadillo1955]

I've been playing with the spreadsheet for a couple of weeks now. I'm no spreadsheet wizard but I can appreciate a tool like this and the many many hours needed to create, maintain and support it. I'm new to this forum and this is my first post. This feels like a silly question but I have scoured the tool and can't seem to find an easy answer. I have input all my financial data and played with several different "setup" scenarios. I can make sense of how changing different setup values impact the result. The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM. I'm using LibreOffice ver 6.3.5.2. Thanks in advance for any help with what I hope is a simple question.

[BigFoot48]

The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM.

I'm glad you are finding the model useful. The answer is that RPM is not designed to make or offer investment and portfolio withdrawals decisions for the user. It requires the users to apply the knowledge they have on these and many more financial issues, learned here at Bogleheads (highly recommended) and elsewhere, to model their portfolios over the selected period.

Users to need to learn the benefits and impacts of different account withdrawals over future years first, then use RPM to model them. While i-Orp is excellent and I highly recommend it, its modeled withdrawal strategy might not be optimum for some users and having a good education in these matters before using any model will be of great benefit.

[armadillo1955]

The issue I am having is figuring out which of the 3 accounts (Taxable, IRA, Roth) I should be pulling money from in any given year. There is a plethora of information in the Summary, Results and Details tabs but I don't see where the tool spits out for a specific year and based on what you input on the setup tab, this is where you should be pulling money from. What am I missing, this is crystal clear in the I-ORP tool, not so much (for me) in RPM.

I'm glad you are finding the model useful. The answer is that RPM is not designed to make or offer investment and portfolio withdrawals decisions for the user. It requires the users to apply the knowledge they have on these and many more financial issues, learned here at Bogleheads (highly recommended) and elsewhere, to model their portfolios over the selected period.

Users to need to learn the benefits and impacts of different account withdrawals over future years first, then use RPM to model them. While i-Orp is excellent and I highly recommend it, its modeled withdrawal strategy might not be optimum for some users and having a good education in these matters before using any model will be of great benefit.

Thanks for the clarification and advice, much appreciated.

[sonar230]

I am considering retiring early (age 55) and, therefore, would need to sequence the withdrawals such that taxable accounts are drawn down first in early retirement until age 60 instead of the IRA accounts. Is there a way to force the sequence of withdrawals and also have the tool recommend the annual amounts for Roth conversions? The "Use automatic withdrawals" choice doesn't seem to force the I have entered all of my data, but I cannot seems to find a way to sequence the withdrawals. Has anyone figured this out?

Thanks,
sonar230

[BigFoot48]

I am considering retiring early (age 55) and, therefore, would need to sequence the withdrawals such that taxable accounts are drawn down first in early retirement until age 60 instead of the IRA accounts. Is there a way to force the sequence of withdrawals and also have the tool recommend the annual amounts for Roth conversions? The "Use automatic withdrawals" choice doesn't seem to force the I have entered all of my data, but I cannot seems to find a way to sequence the withdrawals. Has anyone figured this out?

Thanks,
sonar230

The model automatically pays the expenses input by the user and calculated taxes out of the taxable account. The taxable account increases based on the annual growth rate entered along with any income and special events (inheritances, asset sales, etc.) the user inputs. The user has to use the methods provided to set the Roth conversion amounts, usually optimized to stay within a lower tax bracket. RPM doesn't recommend conversion amounts.
I don't recommend use of the automatic withdrawal feature as it's just a simply way to use account funds but is not optimized for the best sequence of withdrawals in any year. Good luck in your early retirement!

[sonar230]

Thanks. I will take another look. I really appreciate all the effort you have put into this tool.

Thanks,
sonar230

[BigFoot48]

An updated version of RPM is now available. This update adds an option to set 2020 calculated RMD amounts to zero that may be required in any of the two IRAs or two inherited IRAs. This can be selected via a setting in the Modeling Options section on the Setup page. This is a result of a provision in the Cronos19 legislation.

A related change improves the Taxable Account Adjustment section in 8. Income Taxes to better explain this feature and to calculate the Base case adjustment separately to calculate it correctly if the 2020 RMD is set to zero.

Version 20.1 of the Retiree Portfolio Model model is available as of April 15, 2020 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/h5eozrgkj1y0r ... .xlsm?dl=0

This update is only needed for users subject to RMDs in 2020 who want to see the results of not taking the RMDs. Please note: a test of this using data that has a 2020 RMD revealed that selecting to not take it 1) increased the RMD for 2021 and following years, which is subject to tax at the highest marginal rate, resulting in 2) increased Federal taxes and 3) a reduced portfolio balance at the end of the modeling period. So, if correct, there may be a reason to take some RMD in 2020 after-all. Note, these increased amounts are very small and are mainly an interesting impact of the deferred RMD.

While I believe the model is calculating the use of this feature correctly, there may be a problem I didn't find. Please post here or PM me with any issues.

[FiveK]

Please note: a test of this using data that has a 2020 RMD revealed that selecting to not take it 1) increased the RMD for 2021 and following years, which is subject to tax at the highest marginal rate, resulting in 2) increased Federal taxes and 3) a reduced portfolio balance at the end of the modeling period. So, if correct, there may be a reason to take some RMD in 2020 after-all.

Makes perfect sense if one substitutes "convert some to Roth" for "take some RMD" especially if one pays the conversion tax from taxable. In that case, even if this year's marginal rate is the same as future marginal rates, there can be an advantage due to the reduced tax drag from "moving" the tax amount from taxable to Roth. See Traditional versus Roth - Bogleheads for details.

[sampaine]

For the link to 20.1 I get a 404 error.

[BigFoot48]

For the link to 20.1 I get a 404 error.

Thanks. Link in announcement post above worked but the one in post #1 got truncated. Fixed.

[Speckles]

Thank you for the spreadsheet. It’s fabulous already and you keep making it even better. Your effort is always appreciated!
Specs

[Barsoom]

With permission from BigFoot48, I am announcing a variant to the Retiree Portfolio Model, a Monte Carlo addition to test sequence of return risk on your portfolio. Using built-in Excel capabilities, this variant contains 1,000 generated 40-year stock and bond rate sequences in place of the single rates defined in the Setup tab. It produces a probability distribution of your final portfolio balance, along with the failure rate (percent of trials that ran out of money).

The 40-year rate sequence model is customizable, allowing you to try different minimum, maximum, and likely value shapes to change the volatility of the return rate sequences. You can also single-step through the scenarios to examine the details of your portfolio under different market conditions.

Instructions are included in the Read Me and Monte Carlo Setup tabs. Just use the Copy feature to import your portfolio from the original Retiree Portfolio Model spreadsheet, change the Formula->Calculation Options to "Automatic except for data tables," set the "Use Monte Carlo growth rates" switch to "y," and press F9 to run the analysis. Run time should be under 30 seconds. Results are displayed on the main Setup tab.

For now, I am treating this as a beta test to gauge Boglehead user sentiment. Please post any comments/questions/concerns to me. If the community feels that this is a positive tool, I will continue to support it when BigFoot48 makes updates.

You can access the variant from this Dropbox link: https://www.dropbox.com/s/a42ju17d7afos ... .xlsm?dl=0

Thank you for your consideration and feedback.

-B

[ncdad1]

Thank you. I love new things to play with. I will let you know how it goes

[Zephavest]

Hi Barsoom, thanks for sharing this with the community, nice job!!!

[ncdad1]

Barsom, I got stuck at the "Copy feature". I am using MAC excel. It comes up with a dialog box. Normally, one can navigate to a file in the directory. I tried putting both the original file and your file in the same download directory and typing the name of the original files but I got an error the program could not find the original file?? Am I support to add directory structure even though both files are in the same download directory?

[Barsoom]

Barsom, I got stuck at the "Copy feature". I am using MAC excel. It comes up with a dialog box. Normally, one can navigate to a file in the directory. I tried putting both the original file and your file in the same download directory and typing the name of the original files but I got an error the program could not find the original file?? Am I support to add directory structure even though both files are in the same download directory?

Did that work before, copying from an original to original spreadsheet?

I didn't change any macros or do anything to original functionality, so I'm perplexed at why my changes would affect this. I have Windows, so I can't help with Mac questions.

Some top-of-mind suggestions... I don't think the .xlsm (macro-enabled) filetype really matters if you still enable macros after opening the spreadsheet. Can you try changing the format to .xlsb or .xlsx and see if that changes things? Do this by saving as... and changing the filetype in the dropdown (don't just change the file name).

If the original to original works but the original to my version does not, I'd say that for now, can you manually enter the data?

-B

[BigFoot48]

BHs, Barsoom did an outstanding job adding a Monte Carlo simulator to the current version of RPM. Anyone wanting to see how this forecasts their portfolio results should check it out. As of now this will be a Beta/Alternative version that he will support. By leaving the Excel model open there have been many user-provided contributions over the years and his illustrates the value of doing this. Enjoy!

[ncdad1]

Barsom

I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of
"~/Downloads/Retiree Portfolio Model v20.0.xlsm"

each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.

[Barsoom]

Barsom

I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of
"~/Downloads/Retiree Portfolio Model v20.0.xlsm"

each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.

Does it display a file explorer? Can't you navigate to the proper folder from within the file explorer?

-B

[BigFoot48]

I started with v20 so I have not had to clear and load the data into a new version yet so this is a good test for me.

I am working on various deviations of "~/Downloads/Retiree Portfolio Model v20.0.xlsm"
each time taking and adding the directory and extension to see if the copy function can find my original file. No luck yet.

There's been no reports of the macro copy settings feature not working. Have you tried it between 20.0 and 20.1? I don't have Mac Excel to test it but that may be the reason. If you can't get it to work just revert to the manual copy method.

[ncdad1]

Bansom and Bigfoot

I tried saving the file from xlsm to xlsx and that did it.

[Rajsx]

I downloaded The Retiree Portfolio Model, it asks me to buy Microsoft 365 (Office).

Bigfoot, Is there a way to try RPM without buying the Office ?

Thanks

[BigFoot48]

I downloaded The Retiree Portfolio Model, it asks me to buy Microsoft 365 (Office).

Bigfoot, Is there a way to try RPM without buying the Office ?

Thanks

The best free alternative is LibraOffice Calc. The list of alternatives tested is on the Readme page.

[BigFoot48]

Version 20.2 of the Retiree Portfolio Model model is available as of June 22, 2020 and can be downloaded from Dropbox via this link: https://www.dropbox.com/s/rsfm9vbb2yl4f ... .xlsm?dl=0

This is a maintenance update, improving the functioning by replacing VLOOKUP and HLOOKUP functions with Index/Match, the current standard. This will benefit future versions and perhaps other programmers modifying the model to test other ideas.

There is NO NEED to update to this version for any normal user.

Many thanks to BH Barsoom for doing the programming to update these functions. His Monte Carlo version will benefit from these changes as will future versions of RPM.

July 19: minor correction in IRMAA display of applicable years. Year four was not being included. Thanks to BH diy60 for noticing this, finding missing year 4 formula, and reporting it. No need to update for most users.

[Barsoom]

Here is a link to the corresponding Monte Carlo variant of the Retiree Portfolio Model version 20.2.

https://www.dropbox.com/s/5f7qjlkukm945 ... .xlsm?dl=0

The only new feature is the inclusion of a second SOLVER model for advanced users. This model will seek to find the highest disposable income (living expenses) based on altering the asset allocations within the desired risk level (number of acceptable scenarios that run out of money).

As usual, please direct questions or comments on the Monte Carlo variant to me.

-B

[Barsoom]

All:

I updated the Monte Carlo variant to the Retiree Portfolio Model.

I added the Money Market/Cash return to the Monte Carlo variables. Previously, it was a fixed amount that was used for all 40 years. This variable is 100% correlated to the Bond rate, but the user can adjust the minimum, likely, and maximum range, as well as the probability function (BetaPert, Triangle).

You can get the latest version from Dropbox here: https://www.dropbox.com/s/7pwht1t9rfbl0 ... .xlsm?dl=0

As always, please direct questions, comments, etc., to me.

-B

[rottenscott]

I'm attempting to use RPM for someone currently employed (i.e. still earning wages) so perhaps this is outside the intended scope of the model --certainly not a retiree but maybe a pre-retiree? Anyways, since the model has a LOT to offer I'm hoping I can still use it and would appreciate any insight into the following issues. I'm using v20.1 of the model with LibreOffice.

1. Handling tIRA contributions from wages

It appears the model does not deduct tIRA contributions from taxable income so from this I presume wages entered as "Other income" would need to exclude this amount (i.e. correlate more to W2 Box 1). However, since the model does pay for such contributions from the taxable account I somehow need to replace this income. My only thought was to reduce Expenses by the contribution amount and also keep the IRA "Yearly Change" percentage the same as the Expense "Inflation" percentage.

2. Default taxes on earnings in taxable account

In looking at possible ways to add tax-exempt income I stumbled across "taxable account adjustment" in section 8. It appears by default (i.e. no adjustments entered) all the earnings are excluded from income -- Taxable_Tax_Rate gets set to 100% and so on line #171 on the Details tab the full earnings amounts is subtracted. This contradicts what I thought I had read in previous posts that "The model taxes all the annual growth in the taxable account as ordinary income". Maybe that comment was prior to the introduction of the adjustments?

Thanks

[BigFoot48]

I'm attempting to use RPM for someone currently employed (i.e. still earning wages) so perhaps this is outside the intended scope of the model
1. Handling tIRA contributions from wages
2. Default taxes on earnings in taxable account

Yes, the model was designed for retirees and the contribution to IRAs from wages was not modeled. Your solution may work - keeping the net amount of cash flow and taxes about right. You could also use the "User input income, credit and adjustments" on the Details page, line 181, to reduce taxable income during the contribution years. Or you could just accept that taxes are overstated somewhat during those employment years and your portfolio is slightly understated as a result.

I believe the comment of taxable account earnings is indeed a left-over from before the taxable earnings adjustment was added. The adjustment is used to better approximate taxable account taxable earnings by deducting an estimated of non-distributed earnings.

[rottenscott]

Thanks for the response. I think I took the long way around to the realization I should use my existing spreadsheets to project portfolio growth during the working/investing phase and then plug those future balances into RPM to deal with all the things it handles so well during retirement.

[RangeleyLake]

Hi Bigfoot

Thanks for the continue support of RPM. It is an invaluable tool with lots useful information, which we will use to manage some of our financial decision now and in the future.
There was a few items I came across lately while I was using RPM (this was the first time I noticed these items).

1) On the Set Up tab in the storage area, the copy macro button (Cell E497) and the clear macro button (cell L495) are looking at the wrong cell data. It seems to be looking at data a lot earlier and ending a lot earlier. – I have noticed that it could affect your results if you use either of these two macro on the latest version-

2) On the Tax Table tab, should cell F83 reference cell E81 and cell F84 reference cell E80? It looks like this area is referencing ACA MAGI.

3) On the Base Tab cell b20, I had an alert on the base tab that was no true. It was saying “ Negative balance in one or more years….” . When I look at the formula in this cell it, part of the formula states- =If(Negative_full>0….) should this be- =if (negative_base>0…..)

4) On thought, not sure if this is worth the time or would be useful for all users, is it possible on the Results summary on the Set up tab the cell E15 could reference both federal and state tax. I was thinking of referencing cell E145 from the detail page. Of course the description on cell d15 would change to Total Federal and State taxes.

Thanks again for the many hours you have spent on this model (for the time building and maintaining the model and answering all of those questions). There is a lot of useful information on the model and it give us a more complete picture of our finances.
Rangely Lake

[sfm3]

Hi Bigfoot,

Thanks for providing such a well thought out retirement planning tool. I'm a new user of RPM and am trying to model annuities. It appears that there is an error if I choose IRA as the source as the "Portfolio Activity by Account" is significantly less than the balance in the "Portfolio and Yearly Balances" section on the results tab. If I just select taxable account instead, there is no error. Any thoughts on what I might be setting up incorrectly? I am using $1 for the initial amount assuming that the annuity has already been started.

Thanks,
SFM3

[BigFoot48]

1) On the Set Up tab in the storage area, the copy macro button (Cell E497) and the clear macro button (cell L495) are looking at the wrong cell data.
The copy macro has been getting out of range for a long time but is likely never used but is now fixed, along with the clear macro which may be frequently used. Also noted the latest release didn't have the stored settings for different test cases so fixed that.

2) On the Tax Table tab, should cell F83 reference cell E81 and cell F84 reference cell E80? It looks like this area is referencing ACA MAGI.
This is an important correction for those using the Medicare IRMAA estimate (note, this is an informational estimate and is not used in the model calculations.)

3) On the Base Tab cell b20, I had an alert on the base tab that was no true. It was saying “ Negative balance in one or more years….” . When I look at the formula in this cell it, part of the formula states- =If(Negative_full>0….) should this be- =if (negative_base>0…..)
Oops! Fixed.

4) On thought, not sure if this is worth the time or would be useful for all users, is it possible on the Results summary on the Set up tab the cell E15 could reference both federal and state tax. I was thinking of referencing cell E145 from the detail page. Of course the description on cell d15 would change to Total Federal and State taxes.
Something to think about but nearly everyone has Federal and state taxes may change when people move so I prefer to leave it Federal only but other user comments on this are welcome.

Rangely Lake, thanks very much for finding these problems and reporting them. Efforts like yours help make the RPM better, benefiting all users. I have noted my corrections above and will be providing an updated model 20.2a today.

[LadyGeek]

(2 posts up) sfm3, Welcome!

bigfoot48 - With my recent retirement, I just downloaded the Retiree Portfolio Model and will be using it "for real" this time (instead of just testing to see if it works). Modeling a Roth conversion is my top priority. I also need to look Medicare IRMAA, so the forthcoming bug fix is much appreciated.