Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

I have been testing inclusion of the I-ORP four spending strategies (Reality Retirement Planning, Changing Consumption, The Lifecycle of Spending, Age Banding) in my Monte Carlo variant of the Retiree Portfolio Model.

I will publish this when Bigfoot48 releases his update.

-B
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

sfm3 wrote: Mon Jul 20, 2020 8:40 am 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
You indeed identified a problem with the modeling of the Single Premium Immediate Annuity using a traditional or Roth IRA as the funding source. This has been tracked down an a new version 20.2b is available for download: https://www.dropbox.com/s/ud5ifjqq4mh1m ... .xlsm?dl=0

Thanks for the alert.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

All:

I updated the Monte Carlo variant to the Retiree Portfolio Model with the recent bug fix.

Also, a new feature was added: I-ORP Spending Patterns. In the Monte Carlo Setup tab, you can override the Setup Living Expenses and use one of the four alternative spending models, using the Setup Living Expenses as the starting point.

You can get the latest version 20.2b.MC.2 from Dropbox here: https://www.dropbox.com/s/6d06s7ge3r6fp ... .xlsm?dl=0

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

-B
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

There's nothing like using real data. I've entered my info into version 20.2b and still have a ways to go in terms of figuring out my situation.

The "don't convert" Roth conversion analysis in the Results sheet makes all the time I'm spending on this spreadsheet worthwhile. I still have to understand why (and be sure I'm doing this right), but the fact that a clear, simple, answer is available is very helpful.

Unfortunately, I'm posting my opinions early because I believe there is an error in the PA state income tax analysis. (If there is no error, please let me know what I did wrong.)

PA is very tax-friendly for retirees. No pension, 401(k), IRA, nor Social Security is ever taxed. When my IRA RMD distribution kicked in, the income showed as taxable. It should be 0.

State rates and factors:

- percent method, 3.07%
- SS benefits taxable in your state - n
- Pension 1 income included in taxable income - 0%
- Tax-exempt investment income taxable in state : 100%

I attempted to fix it by assuming the Social Security state tax status follows IRA distributions (don't tax). In the Details and Base sheets, I inserted a new Row 210 as:

Code: Select all

B210: IRA withdrawals and RMD to exclude in state  

C210:AS:210:

=ROUND(IF(AND(State_Move_Age<=F$4,State_Move_Age<>0),IF(SS_Tax_State2="n",SUM(F175:F178),0),IF(SS_Tax_State="n",SUM(F175:F178),0))/Round_To,0)*Round_To
This will show the IRA distributions. Then, I subtracted that from the State AGI:

Code: Select all

B213: State AGI

C213:AS213:
=+F207+F208+F209-F210+F211+F212
I also copied the formulas in Columns E, AU and AV from the prior rows. (Also in Base sheet AY210:CL210).

I don't have the confidence to say this is a complete fix. My intent is to confirm this is a problem (or not). However, I have a penchant for debugging spreadsheets and wanted to offer a solution.

Update 7/26/20: A further correction is mentioned in this post.

Update 8/13/2020: Row 210 in the Details and Base sheets was later modified to include QCD distributions, which are not taxable in PA. See this post (Jul 24, 2020) which changed the range from SUM(F175:F178) to SUM(F175:F179).
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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Thu Jul 23, 2020 3:15 pm Unfortunately, I'm posting my opinions early because I believe there is an error in the PA state income tax analysis. (If there is no error, please let me know what I did wrong.)

PA is very tax-friendly for retirees. No pension, 401(k), IRA, nor Social Security is ever taxed. When my IRA RMD distribution kicked in, the income showed as taxable. It should be 0.
Not exactly an error, more like a missing feature. :wink: I made the state income tax options fairly simple, as it would be very difficult to accommodate and keep up to date all 50 states' tax rules. I was hoping it would generally provide a reasonable result most most users and the lack of questions about it over the years indicate perhaps it did.

Your solution looks perfect, and I think it may be beneficial to include it in the next update. Note that the State Income Tax User Adjustment area on the Details page could also be used to adjust for any income that should be excluded or included.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

If you say my solution is OK, I can wait until the next update. I'll take a look at the State Income Tax User Adjustment area on the Details page for some possible tweaks.

My planning situation changed drastically this year - mortgage paid off, becoming a widow, retiring. Along with getting close to the Social Security minimum age, then Medicare, the spreadsheet is a huge help for providing insight on my path forward.

Once I have some confidence understanding why my numbers are going in a certain direction, I'll start running different scenarios and give the spreadsheet a good workout.

I also noticed that the Social Security estimate for age 70 differs from my "official" number supplied by the Social Security Administration. I used Excel goal seek to find the COLA which matches the age 70 number: 0.3%. I'll use your spreadsheet as an estimate.

==============
Taxes: Setup, Row 261

For 2020, I'll be filing as MFJ with no spouse. In 2021, I'll file as single. This may be another situation which is not addressed in the spreadsheet.

Since I have no spouse, I've removed all the data setup entries which applied to a spouse. I didn't want to enter anything for a spouse, as I don't know what else uses it.

I used the single rate override for 2021 and am getting an error as "Error: No spouse age, use 's'." I'll assume this is OK and doesn't affect anything else.

When NASA launches a rocket, part of their countdown is "No unexpected errors, continuing." This is an expected error, so I'll keep going. If there's a better way to do this, let me know.
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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Thu Jul 23, 2020 6:34 pm Taxes: Setup, Row 261

For 2020, I'll be filing as MFJ with no spouse. In 2021, I'll file as single. This may be another situation which is not addressed in the spreadsheet.

Since I have no spouse, I've removed all the data setup entries which applied to a spouse. I didn't want to enter anything for a spouse, as I don't know what else uses it.

I used the single rate override for 2021 and am getting an error as "Error: No spouse age, use 's'." I'll assume this is OK and doesn't affect anything else.

When NASA launches a rocket, part of their countdown is "No unexpected errors, continuing." This is an expected error, so I'll keep going. If there's a better way to do this, let me know.
That is just an alert message to help make sure users are using the correct filing status. If selected, the model will do your first year as MFJ and then if you put your age+1 in as the change year for filing Single it will switch to the single calculation. The spouse age, entered or missing, has no impact on the calculation. You can put in a spouse age to make the error message go away, otherwise just ignore it.

Continue the countdown.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Continuing. I now see 'single rate' highlighted in the Details sheet Row 206, exactly where I expected to be.

Why I made that comment:

In order to accommodate just about any scenario you can think of, the spreadsheet needs to be flexible. This means that it's complicated with a lot of moving parts.

Providing data confirmation such as: 'using', 'not using', 'single rate', 'escalation', 'state1 % state2 %', 'using percent', 'not included', 'rate change', etc. in every location where a user is looking at the data makes all the difference in the world. It takes the spreadsheet from 'unwieldy and unusable' to 'straight-forward and easy to follow'.

When I help debug spreadsheets, one of my main efforts is not on the formulas, but the documentation. Document everything as if you've never seen the spreadsheet before and are new to investing. It's a lot of work, but the effort is paid back multiple times over when you see the spreadsheet helps others as intended.

I'm using the spreadsheet not only because it does what I need, but that it's well documented and I can easily understand what it does. If something goes awry, I know what's going on and can track down what's needed.
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.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

LadyGeek wrote: Thu Jul 23, 2020 6:34 pm I used Excel goal seek to find the COLA which matches the age 70 number: 0.3%.
Let me know if you're interested in stepping up to use Solver with Monte Carlo to optimize things. There is an open-source non-linear solver that is much better than the Excel built-in ones.

What's surprising me with my portfolio is that Solver is recommending a much lower equity allocation than I was expecting. I'm optimizing for highest living expense that doesn't run out of money, and it's putting most of the portfolio into bonds or money market. The living expenses is what I expected, just not the AA that gets there.

This may be because:
  1. I have a "simple" portfolio that is 90% tIRA and 10% after-tax.
  2. The "historical" probabilities that I'm using for stocks causes the extreme highs/lows to drive the optimizer away from stocks and into "safer" investments. This may be okay because my goal is highest living expense, not highest portfolio for heirs when I die.
I've been running my own tests of the optimizer to see if raising the risk level to 5% (running out of money 50 times out of 1,000) puts more investment into equities. I'm not done yet.

I've also been playing with the spending patterns. It's definitely true that I can increase my early retirement spending by "paying" for it out of later reduced spending. I tried having the optimizer search for a spending date of change, but it didn't find any. The same was true with reallocating my portfolio - the optimizer didn't think that changing my AA mid-way through retirement made a difference. Again, probably because of my simple portfolio.

-B
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I'll think about it, but I need a little more time to understand how my situation is shaking out.

My objective is to minimize taxes, maximize return, with no concern about leaving anything for heirs. Your objective of 'highest living expense that doesn't run out of money' is something to think about.

One thing I look at is the overall level of accuracy. We often recommend asset allocations to step no finer than the nearest 5%. Striving for more precision is a lot more work for little added benefit.

Using a more robust solver might be interesting from an academic perspective, however.

To me, a 'simple' portfolio (Lazy portfolio) is a black box with all of the market dynamics contained within (representative sampling of market performance, a.k.a. index fund). You get the total market approach using the 'simple' model. Attempting to break it down further will get into Slice and dice aspects which are known to deviate from the total market approach.

I'm certainly not a modeling expert here, especially with finance, and could be wrong. However, that's how I see the model.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

I'm not trying to push it. It's just that many times people fit their model to the perfect situation. Back-testing it against history (like with FIRECalc) is a good first step to see how the portfolio would have held up during the 100 or so known past retirement periods. The Monte Carlo goes the next step to test the portfolio against an unknow future (1,000 randomly generated 40-year annual varying growth) to see how the portfolio holds up to unknown market conditions.

One can set the annual spending and AA, and Monte Carlo (without Solver) will show how many times the portfolio ran out of money.

Or one can set the spending, and have Solver adjust the AA to maximize the portfolio without running out of money.

Or one can have solver adjust the spending and the AA to maximize the spending without running out of money. Doing this actually optimizes the taxes, too, because increasing spending increases withdrawals from tax-deferred accounts, which increases taxes.

Anyway, it's just another tool that's out there. I'm sorry if I piggy-backed on your post to suggest it. Maybe others who read this might get an idea of how the variant tool might help.

-B
User avatar
FiveK
Posts: 15742
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

Barsoom wrote: Thu Jul 23, 2020 8:41 pm There is an open-source non-linear solver that is much better than the Excel built-in ones.
The one mentioned in Optimized Roth Conversion Model (https://opensolver.org/) or different?
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

FiveK wrote: Thu Jul 23, 2020 10:10 pm
Barsoom wrote: Thu Jul 23, 2020 8:41 pm There is an open-source non-linear solver that is much better than the Excel built-in ones.
The one mentioned in Optimized Roth Conversion Model (https://opensolver.org/) or different?
That's the one. There are about a dozen included in the package. The non-linear one is called NOMAD. You can get it from the "advanced" installation package.

The best part about it is that it operates from within the built-in Excel Solver framework. You can use basic Solver to load and save the Solver models to the spreadsheet, and then switch to Opensolver/Nomad to change the constraints and run the optimization.

If you build a Solver model that you like in Opensolver, just switch to Solver and use the Load/Save option to have Solver save the model as a list of cells in your spreadsheet. Then you can open a new spreadsheet, and have Solver load the range of cells from the other spreadsheet, and then save it to the current spreadsheet. Then Opensolver can work on the copied model without have to recreate it again.

-B
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

Barsoom wrote: Thu Jul 23, 2020 9:31 pm Anyway, it's just another tool that's out there. I'm sorry if I piggy-backed on your post to suggest it. Maybe others who read this might get an idea of how the variant tool might help.
It's never a problem to piggy-back on a post - you never know what might result from the discussion. I see a possibility for a new project.

Do you think there would be enough interest to take what you have now and create a separate thread focused on the optimizer? Since you've forked BigFoot48's spreadsheet, discussions in this thread (theory and support) might get mixed with discussions for BigFoot's version.

We could link to the thread from Retiree Portfolio Model (and the first post in this thread). We could also create a dedicated wiki page.

FYI - The wiki supports a number of open-source projects. For example: Using open source software for portfolio analysis
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.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

LadyGeek wrote: Fri Jul 24, 2020 7:35 am Do you think there would be enough interest to take what you have now and create a separate thread focused on the optimizer? Since you've forked BigFoot48's spreadsheet, discussions in this thread (theory and support) might get mixed with discussions for BigFoot's version.
I think directionally it would be nice to end there, but I don't think it's ready for it yet.

When I first approached Bigfoot about doing this, I said it would be a beta test of a standalone variant to his spreadsheet that I would maintain separately until/unless he felt comfortable enough with it to incorporate into the base spreadsheet. At some future point, we'd decide whether to keep it standalone, incorporate it if the "code" is stable, or drop it altogether if there is no BH community interest in it.

To date, I've received zero feedback on it, good, bad, or otherwise, so I can't tell if it's worthy of a separate discussion thread or a wiki entry. I've made some explanatory comments in this thread, evangelized the base RPM spreadsheet in other newbie threads, but otherwise stayed away from discussing optimization until I got some feedback from users saying the results felt credible.

I am working on a case study to post on another thread (VPW), but I'm currently cautious about rubbing elbows with the Big Boys. I guess I'll have to if I want serious critique of this.

-B
motiv8ed
Posts: 120
Joined: Fri Jul 03, 2020 9:37 pm

Re: Retiree Portfolio Model

Post by motiv8ed »

BigFoot48 - Thanks for creating and sharing such a terrific tool!

I just wanted to confirm what I think is true: there's no way to model a totally tax-free income stream. The hints and instructions in Section "4. Income" all say that all four streams (Pensions 1 & 2 and Other 1 & 2) are subjected to Federal taxes (not worrying about state tax in this case). But there's no way to designate one as completely tax-free, either in this section or any other, right?

I'm sure it won't make a huge difference in the model's final result, but I'm wondering if anyone uses a workaround for this situation.

If not, I'll just consider it a bit of insurance that my expenses would be slightly overstated and if the plan works in the model, then there's a slightly higher probability it'll work in real life.



Motiv8ed
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Hello BigFoot48,

Thank you for sharing such a fantastic tool. I am new to this forum, have been studying it for several days, trying to use a portion of its vast capability to evaluate my ROTH conversion considerations, and am not sure how to proceed with maintaining my portfolio AA goal.

Does the RPM perform annual rebalancing on a portfolio basis? From what I read in this thread - viewtopic.php?t=285584 - it appears that the RPM performs annual rebalancing for each of the 3 accounts - Taxable, T-IRA, Roth - individually but not across accounts for the total portfolio. However I noticed that in the section 3 of the "Setup" tab - rows 99 and 112 - there are notes saying "Memo: allocation targets" as well as entry fields immediately below the "Portfolio asset allocation" row, so perhaps I haven't figured out how to use it.

Thank you for your help,
DSBH
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

motiv8ed wrote: Fri Jul 24, 2020 3:25 pm BigFoot48 - Thanks for creating and sharing such a terrific tool!

I just wanted to confirm what I think is true: there's no way to model a totally tax-free income stream. The hints and instructions in Section "4. Income" all say that all four streams (Pensions 1 & 2 and Other 1 & 2) are subjected to Federal taxes (not worrying about state tax in this case). But there's no way to designate one as completely tax-free, either in this section or any other, right?

I'm sure it won't make a huge difference in the model's final result, but I'm wondering if anyone uses a workaround for this situation.
On the Details page, line 181, there is a "User input income, credit and adjustments" line for Federal AGI. Use this to reduce taxable income, either by entering negative amounts in each year, or enter a formula in each cell referencing the line that has the income to be excluded, like -F70 in the first year to exclude all Other Income from Fed taxes, then -G70 in the next year and so on.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
motiv8ed
Posts: 120
Joined: Fri Jul 03, 2020 9:37 pm

Re: Retiree Portfolio Model

Post by motiv8ed »

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

DSBH wrote: Fri Jul 24, 2020 4:12 pm
Does the RPM perform annual rebalancing on a portfolio basis? From what I read in this thread - viewtopic.php?t=285584 - it appears that the RPM performs annual rebalancing for each of the 3 accounts - Taxable, T-IRA, Roth - individually but not across accounts for the total portfolio. However I noticed that in the section 3 of the "Setup" tab - rows 99 and 112 - there are notes saying "Memo: allocation targets" as well as entry fields immediately below the "Portfolio asset allocation" row, so perhaps I haven't figured out how to use it.
Yes, rebalancing is automatic. In the Return Rate and Allocation section an initial average return rate for each account is determined using the initial balances in each account and an expected return rate, or the more detailed average using the asset classes in each account. These rates are applied to each year so it is effectively assuming each class is re-allocated thus achieving the average return rate desired/predicted.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

^^^ That's a good point. You aren't rebalancing like one would do for a real portfolio account, but assuming a fixed asset allocation every year (or change in a later year) is, in fact, an assumption that your account maintains a constant asset allocation and is always rebalanced. Consider making a note of that in the spreadsheet.
LadyGeek wrote: Thu Jul 23, 2020 3:15 pm ...PA is very tax-friendly for retirees. No pension, 401(k), IRA, nor Social Security is ever taxed. When my IRA RMD distribution kicked in, the income showed as taxable. It should be 0.

...I attempted to fix it by assuming the Social Security state tax status follows IRA distributions (don't tax). In the Details and Base sheets, I inserted a new Row 210 as:

Code: Select all

B210: IRA withdrawals and RMD to exclude in state  

C210:AS:210:

=ROUND(IF(AND(State_Move_Age<=F$4,State_Move_Age<>0),IF(SS_Tax_State2="n",SUM(F175:F179),0),IF(SS_Tax_State="n",SUM(F175:F178),0))/Round_To,0)*Round_To
This will show the IRA distributions. Then, I subtracted that from the State AGI:

Code: Select all

B213: State AGI

C213:AS213:
=+F207+F208+F209-F210+F211+F212
I also copied the formulas in Columns E, AU and AV from the prior rows. (Also in Base sheet AY210:CL210).
Digging in a little deeper, QCDs (Qualified charitable distributions) are looking very good right now. Since the spreadsheet has this built-in, I entered a few numbers and saw my state taxes go negative. :shock:

QCDs are handled differently for each state and may need a new variable. See: QCDs at the State Level | Ed Slott and Company, LLC In PA, QCDs are considered an IRA distribution and are not taxed. My fix for PA has changed to add Row 179 to the exclusion.

Code: Select all

B210: IRA withdrawals and RMD to exclude in state  

C210:AS:210:

=ROUND(IF(AND(State_Move_Age<=F$4,State_Move_Age<>0),IF(SS_Tax_State2="n",SUM(F175:F179),0),IF(SS_Tax_State="n",SUM(F175:F179),0))/Round_To,0)*Round_To
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.
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Thank you, I have more homework now !
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

BigFoot48 wrote: Fri Jul 24, 2020 5:29 pm
DSBH wrote: Fri Jul 24, 2020 4:12 pm
Does the RPM perform annual rebalancing on a portfolio basis? From what I read in this thread - viewtopic.php?t=285584 - it appears that the RPM performs annual rebalancing for each of the 3 accounts - Taxable, T-IRA, Roth - individually but not across accounts for the total portfolio. However I noticed that in the section 3 of the "Setup" tab - rows 99 and 112 - there are notes saying "Memo: allocation targets" as well as entry fields immediately below the "Portfolio asset allocation" row, so perhaps I haven't figured out how to use it.
Yes, rebalancing is automatic. In the Return Rate and Allocation section an initial average return rate for each account is determined using the initial balances in each account and an expected return rate, or the more detailed average using the asset classes in each account. These rates are applied to each year so it is effectively assuming each class is re-allocated thus achieving the average return rate desired/predicted.
Hello BigFoot48,

I went back and set up a simplistic case using the more detailed average annual return rates by class, and the account asset allocation as follows:

$500,000 in Taxable, $400,000 in T-IRA, $100,000 in ROTH - $1M in total
100% in stock for Taxable, 100% in bond for T-IRA, 100% in stock for ROTH - so 60% stock, 40% bond for the whole portfolio
5% average annual return for stock, 1% average annual return for bond
Zero other income, zero expenses, $10K/year ROTH conversion amount
and I'd like to keep my asset allocation target of 60% stock / 40% bond for the total portfolio every year for my 34-yr study period (62-95).

Every year RPM calculates the returns on each account using 5% for Taxable and Roth and 1% for T-IRA, performs ROTH conversion / RMD / Federal Income Tax when applicable, and in the end empties out T-IRA and returns 100% stock in Taxable and Roth. It appears that RPM does annual rebalancing on an account by account basis but does not keep the 60/40 AA target for the total portfolio on an annual basis by changing the asset allocation for individual account every year (e.g. taxable first year has 100% stock but should have 98% stock and 2% bond in the second year and so forth).

One way I think that I can keep my 60/40 AA target for the portfolio on an annual basis is to specify 60/40 as AA for each of the individual accounts, or some variations of AA for each account coupled with the ability to change AA at a future age. I think this approach would allow me to significantly take advantage of RPM vast capability.

Thank you and please advise if I miss, or misstate anything.

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

DSBH wrote: Sat Jul 25, 2020 12:57 pm One way I think that I can keep my 60/40 AA target for the portfolio on an annual basis is to specify 60/40 as AA for each of the individual accounts, or some variations of AA for each account coupled with the ability to change AA at a future age. I think this approach would allow me to significantly take advantage of RPM vast capability.

Thank you and please advise if I miss, or misstate anything.

DSBH
Nice observation. Yes, I think with the change in account balances due to withdrawls/etc. the use of the same allocation for each account would be required for the model to keep the overall allocation fixed. This will result in a slight? miscalculation of each accounts yearly returns vs. reality, but might result in a better overall return calculation, recognizing of course that real returns are going to be significantly different than the modeled returns anyway.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I'm getting comfortable with the spreadsheet and am starting to really look at my situation in detail. However, I don't understand the Roth conversion results. I thought the goal was to minimize taxes, but I'm getting conflicting results and don't know what it's telling me.

The 2nd column is the percent difference between "Roth" and "No Roth" conversion. Better to convert is (+ %), worse to convert is (-%).

Code: Select all

Portfolio summary	               Percent       Conclusion
Portfolio balance at age 96 - ----	-2.8%        don't convert
Years portfolio is larger than other	-100.0%	
		
Key portfolio elements		
Earnings from growth, dividends, interest -7.7%      don't convert
Federal and state taxes                   6.6%       convert
		
Impact on RMD of Roth conversions		
IRA 1 Required Minimum Distribution       15.5%      convert
Federal income tax on RMD                 18.8%      convert
My interpretation is that the final portfolio balance says "don't convert", but federal and state taxes say "convert" for both the portfolio as a whole and on RMDs.

I will be deferring Social Security until 70, nothing but a pension until then. My RMD will kick in at 72.

Another objective is to have an estate value = 0 when death = 1. That can certainly change, but it's what I'm planning for now. QCDs in-lieu of Roth conversions are a much better approach. In addition to fed and state taxes, IRMAA is significantly reduced.

Can someone provide an explanation of the conversion guidance? I'll be changing the numbers around, but found that I don't have an understanding of what the guidance actually means. What should I be looking at - taxes or final balance?

=====================
Suggestion:

There's a missing data confirmation entry in "Setup 4. Income" for SPIA. I was wondering why my numbers didn't look right and then found that the SPIA entry was being used. The directions are clear to enter 0 for the purchase price, but only if you hover over the cell note.

Consider inserting in Setup!E148:

Code: Select all

=IF(SPIA_Amt>0,"< using","not using")
Format the cell the same as Setup!E137.
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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

LadyGeek wrote: Sat Jul 25, 2020 7:01 pm The 2nd column is the percent difference between "Roth" and "No Roth" conversion. Better to convert is (+ %), worse to convert is (-%).

Code: Select all

Portfolio summary	               Percent       Conclusion
Portfolio balance at age 96 - ----	-2.8%        don't convert
Years portfolio is larger than other	-100.0%	
		
Key portfolio elements		
Earnings from growth, dividends, interest -7.7%      don't convert
Federal and state taxes                   6.6%       convert
		
Impact on RMD of Roth conversions		
IRA 1 Required Minimum Distribution       15.5%      convert
Federal income tax on RMD                 18.8%      convert
My interpretation is that the final portfolio balance says "don't convert", but federal and state taxes say "convert" for both the portfolio as a whole and on RMDs.

Can someone provide an explanation of the conversion guidance? I'll be changing the numbers around, but found that I don't have an understanding of what the guidance actually means. What should I be looking at - taxes or final balance?
LadyGeek
The convert/don't convert guidance is just a very simple way to look at difference factors when doing a Roth conversion and offer a suggestion on what it might mean. For example, significantly lower Federal income taxes on RMDs when comparing the two alternatives might mean "convert" would be a benefit. The user needs to decide which are the most important factors for their situation and what the aggregate guidance is indicating and if it should be followed.

I'll add that data confirmation message.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

I have heir(s) so intend to compare the "final" balances between 2 cases (once I figured out how to do it properly):

1. Base case where I model through the year when the beneficiary has to take the last distribution, based on many comments from BH bsteiner. I will assume that the beneficiary will quit her good job, and take out over 10 years roughly in similar annual amount from the T-IRA account. For example if I pass away at 107, the beneficiary will take distribution every year until my 117 birthday - when the RMD table runs out :happy

2. Case with Roth conversion where the selected annual amount will zero out the T-IRA when the beneficiary will inherit all the accounts, say when I pass away at 107 like in the previous example. I may play with the model to see which annual conversion amount would results in the highest "final" balance - of course subject to all the assumptions.
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

BigFoot48 wrote: Sat Jul 25, 2020 7:50 pm LadyGeek
The convert/don't convert guidance is just a very simple way to look at difference factors when doing a Roth conversion and offer a suggestion on what it might mean. For example, significantly lower Federal income taxes on RMDs when comparing the two alternatives might mean "convert" would be a benefit. The user needs to decide which are the most important factors for their situation and what the aggregate guidance is indicating and if it should be followed.

I'll add that data confirmation message.
Thanks, that helped. I was reading too much into those recommendations and thought that you looked at them in aggregate. Now that I understand it's a different recommendation based on different objectives, the guidance make sense.

I should mention that having all of the Roth conversion data entry, withdrawals, account balances, and tax brackets in one spot on the Setup page makes this analysis very easy to do. Since I don't have to worry about the underlying mechanics, I can focus on the task at-hand.

"Easy" doesn't mean that I'm doing this quickly. I still need to carefully check the results and adjust accordingly, e.g. QCDs and IRMAA surcharges.

================
Separately, Roth conversions were discussed during a recent local Bogleheads online chapter meeting. An experienced member suggested that you should not convert all of your traditional IRA to Roth.

Why? Because you want to reserve some of your IRA for long-term care. Nursing homes are qualified medical expenses. The income from drawing down an IRA for medical expenses will be fully deductible once you go over the 10% AGI threshold. (I had started down this path for my late husband, so it's on my mind.)

You can't do that with a Roth IRA. The member recommend leaving a few 100k (no more than 500k) in your traditional IRA for long-term care.

This suggests setting a floor (minimum value) for your traditional IRA. I don't know if this would be a worthwhile change to your spreadsheet, but I wanted to mention it.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
LadyGeek
Site Admin
Posts: 95694
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I think I've got this figured out. I can't overstate how important it is to have an independent way to verify your results.

2020 has a ton of events that impact my financial planning. For Roth conversions, it's important to get this right. I modeled my 2020 situation in my 2019 tax software and input this as "Other income" for 2020 with tax filing as MFJ. Next year will have my pension and tax filing as single.

Then, I went to the Roth conversions. First, I verified that the base case 2020 taxes aligned with my tax software. Accounting for 2019 vs. 2020 deduction differences, the federal taxes aligned nearly dead-on. State tax was a different matter, as I had to add a user adjustment in the state Details and Base rows. (The Base adjustment needed a manual entry due to the Roth conversion, so I broke the formula in Base!F212.) With taxes verified, I now have confidence in the spreadsheet.

(I also took a quick look at IRMAA in the Tax Tables sheet. I'm not an expert, but I could see the 2-year sliding window in action and where surcharges were kicking in.)

It was very easy to enter my Roth conversions and view the tax bracket changes side-by side (and taxes paid - scroll down). I converted enough to hit 95% of the 12% bracket. I didn't see any advantage to go the next higher bracket (22%).

I am very impressed with the amount of insight offered by this spreadsheet. :thumbsup

For example, I was able to see that the amount of headroom for a 2020 Roth Conversion was much less than I thought. Why? Even though my MFJ status gives me more tax space, working a partial year took most of that away. I could also see that taking Social Security also reduces my available Roth conversion space.

I'm also modeling QCDs when RMDs start kicking in. It's good to see possibilities that I can plan for. Things change, but knowing my options is very helpful.

==============
I have one more correction for excluding IRA distributions from state income tax (mentioned in this post). In the Setup sheet, add row 467:

Code: Select all

A467: x

B467: IRA withdrawals and RMD to exclude in state  

C267:AR267: =IF($C$435="Full",Details!F210,Base!F210)
Row 470, modify the formula to subtract the exclusion:

Code: Select all

E470:AR470: =+E464+E465+E466-E467+E468+E469
Delete blank row 488 so it doesn't mess up your storage macros. (As documented in row 486.)

I also saw that inserting row 467 inserted a blank row used by "Lookup row" for Age and Year in Columns AX and BA. I extended the formulas so they wouldn't be blank (and run in series order without skipping a number).
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.
RFMROCKS
Posts: 2
Joined: Sat Aug 01, 2020 11:39 am

Re: Retiree Portfolio Model

Post by RFMROCKS »

Hi, great tool! I was wondering if there is a simple way to add additional income sources that will be paid intermittently in the future. The tool currently only allows 4 inputs; I may have up to 10-12? Thanks for the help.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

RFMROCKS wrote: Sat Aug 01, 2020 12:01 pm Hi, great tool! I was wondering if there is a simple way to add additional income sources that will be paid intermittently in the future. The tool currently only allows 4 inputs; I may have up to 10-12? Thanks for the help.
Glad you like. Unfortunately there's no other way to easily add income sources. I recommend combining the income sources as best you can into the four provided.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

Hello Bigfoot48,

While running some unrealistic scenarios just to familiarize myself with the tool I think I might have discovered a small bug.

In the "Setup" sheet, section 1 "Age and Year Factors" when I enter 40 into the "Total_Years" (cell E40) AND enter either (1) "Your_End_Input" (cell F41) equal to (or higher than) "Your_Age_End" (cell G41) or (2) "Spouse_End_Input" (cell F42) equal to (or higher than) "Spouse_Age_End" (cell G42) - the variable "Single_Rate_Start" (cell E263) in section 8 "Income Taxes" returns a value of 3, which sets the Filing Status in the "Tax Tables" sheet to Single (should be Married) for all years.

As a temporary fix I made 2 changes: (1) add one more year of data that the "Single_Rate_Start" refers to by copying from Setup!AR435:AR438 to Setup!AS435:AS438 and (2) change the "Single_Rate_Start" formula from "...BE..." to "...AS..." - in 4 places.

Please advise if I miss, or misstate anything. Thanks!
DSBH
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

DSBH wrote: Sat Aug 01, 2020 1:24 pm Hello Bigfoot48,

While running some unrealistic scenarios just to familiarize myself with the tool I think I might have discovered a small bug.
Yes, the model wasn't designed to have the last year also be used as the death year of one person so as to trigger the single rate tax filing. I believe your fix would work, however I prefer not to have a year 41, so I will add a warning on the use of the final modeling year as the year a person dies as there is little modeling value in having the last year as a single filing year.

But, I will consider your solution. Good work finding the "bug" and providing a solution.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
sandramjet
Posts: 442
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

BigFoot48 wrote: Sat Aug 01, 2020 12:31 pm
RFMROCKS wrote: Sat Aug 01, 2020 12:01 pm Hi, great tool! I was wondering if there is a simple way to add additional income sources that will be paid intermittently in the future. The tool currently only allows 4 inputs; I may have up to 10-12? Thanks for the help.
Glad you like. Unfortunately there's no other way to easily add income sources. I recommend combining the income sources as best you can into the four provided.
While you can't do it directly on the setup page, I have used the "User expense Adjustments" on the details page to enter income/expenses (-/+) in the future. It seems to work fine for me there.
RFMROCKS
Posts: 2
Joined: Sat Aug 01, 2020 11:39 am

Re: Retiree Portfolio Model

Post by RFMROCKS »

Thanks! I will give it try...
Diynofees
Posts: 5
Joined: Wed Jul 05, 2017 9:31 pm
Location: Boston

Re: Retiree Portfolio Model

Post by Diynofees »

Hi Bigfoot48...continuing to enjoy the heck out of your great spreadsheet tool, running and refining my own models! (And also very grateful to have this
available...)

If you're ever in "looking for enhancements" mode, I think it would be useful to have a separate escalation factor input built in to the tax table page for the state tax calculations and projections. In my case, our state doesn't escalate the personal exemptions and standard deduction on a regular (annual) inflation basis like the feds do; hence state tax projections in future modelling years can be a bit off.

Again, thanks for all your continuing work in improving and updating the RPM.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

FYI to users of the Monte Carlo variant...

I overhauled the data used by the Monte Carlo variant. I wasn't comfortable with the data generation scheme that I was using, and I think I've come up with a much better way to generate future scenarios.

Download link: https://www.dropbox.com/s/kgcu7rexv8cnf ... .xlsm?dl=0

The current approach wasn't as data-based as I'd have liked, even though it was based on reliable data sources. The problems were:
  1. Inadequate basis for correlating stocks to bonds to money market.
  2. Real (inflation-adjusted) values were disconnected from the inflation model, thereby generating optimistic future cases.
The new scheme will be as follows:
  • Inflation will become a Monte Carlo variable, just like the stock, bond, and MM growth rates. Currently, inflation is fixed throughout the 40-year retirement period.
  • Stock growth will now be based on the Total Stock Fund from Vanguard (source Simba Back-test spreadsheet, annual). Was S&P 500 from the Shiller spreadsheet (monthly).
  • Bond growth will now be based on the Total Bond Fund from Vanguard (source Simba Back-test spreadsheet, annual). Was 10-year treasuries from FRED (monthly).
  • Money Market growth will now be based on T-Bills (source Simba Back-Test spreadsheet, annual). Was no source (-1% from bond growth).
  • Based on analyzing the market data from the Simba back-testing spreadsheet (thanks Siamond), the data suggests a much more credible approach:
    1. Annual inflation rate is -90% correlated to annual T-Bill rates.
    2. T-Bill rate is 85% correlated to Total Bond Market rate.
    3. Total Bond Market rate is 15% correlated to Total Stock Market rate.
    4. The top 5% and bottom 5% of data will be dropped as outliers, and the remaining 90% will be used.
    5. Inflation will be generated first. Since 1950, there is (on average) a 75% correlation between inflation and the next year's inflation, in 40-year sets (1:n-1 correlated to 2:n).
      • However, generating a 40-year inflation set with 75% correlation creates too erratic a dataset, eating up most of a retiree's portfolio in loss of purchasing power.
      • Therefore, I'm using a 99% correlation, which smooths out the inflation dataset to a more reasonable spread. This is still user-settable.
      • This does create a narrowed swing in inflation over 40 years. Experiment with this to see if you find an inflation setting that you like.
      • The inflation model can be turned off by setting Start with current inflation rate to "y" and setting the inflation correlation to 100%.
    6. After inflation is created, the T-Bill dataset will be created and correlated to inflation.
    7. Then the Total Bond Market dataset will be created and correlated to the T-Bill dataset.
    8. Finally, the Total Stock Market dataset will be created and correlated to the Total Bond Market.
Monte Carlo will do this 1,000 times and chart the results of the portfolio.

Advanced Solver analysis will allow the user to change Setup variables (e.g., asset allocations, ages of actions, living expenses, contributions and withdrawals) between Montel Carlo runs to asses the impacts of the changes on the objective variable. See detailed instructions in the Monte Carlo Setup tab if you are interested in this feature.

-B
Last edited by Barsoom on Sun Aug 09, 2020 9:58 pm, edited 2 times in total.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Diynofees wrote: Sun Aug 09, 2020 1:55 pm If you're ever in "looking for enhancements" mode, I think it would be useful to have a separate escalation factor input built in to the tax table page for the state tax calculations and projections. In my case, our state doesn't escalate the personal exemptions and standard deduction on a regular (annual) inflation basis like the feds do; hence state tax projections in future modelling years can be a bit off.
Good suggestion. I'll look at implementing it for 2021 - at the latest.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

BigFoot48 wrote: Sat Jul 25, 2020 1:35 pm
DSBH wrote: Sat Jul 25, 2020 12:57 pm One way I think that I can keep my 60/40 AA target for the portfolio on an annual basis is to specify 60/40 as AA for each of the individual accounts, or some variations of AA for each account coupled with the ability to change AA at a future age. I think this approach would allow me to significantly take advantage of RPM vast capability.

Thank you and please advise if I miss, or misstate anything.

DSBH
Nice observation. Yes, I think with the change in account balances due to withdrawls/etc. the use of the same allocation for each account would be required for the model to keep the overall allocation fixed. This will result in a slight? miscalculation of each accounts yearly returns vs. reality, but might result in a better overall return calculation, recognizing of course that real returns are going to be significantly different than the modeled returns anyway.
Hello BigFoot48,

So I took the liberty to add some logic in RPM to optionally rebalance individual accounts in order to maintain the portfolio AA target, used a simplistic case to compare the Portfolio AA method versus the Equal Location (same AA for each account) method, and concluded that the Equal Location method as existed would likely work for my purpose. I will describe my "brute force/research grade/hard coded" logic addition in a separate post but wanted to report my findings first - assuming that my logic addition works correctly - as shown below.

This was the hypothetical, simplistic case that I set up:

MFJ, both 62
$500,000 in Taxable, $400,000 in T-IRA, $100,000 in ROTH - $1M in total
A) 60% stock / 40% bond in each account for the Equal Location method.
B) 60% stock, 40% bond for the whole portfolio with DESIRED 100% in stock for Taxable, 100% in bond for T-IRA, 100% in stock for ROTH for the Portfolio AA method
6% average annual return for stock, 1.5% average annual return for bond
SS - both 36,000 at FRA, start age for DW 62, 70 for DH - 1% COLA
72,000 annual expenses, 1% inflation
No federal tax change, no state tax
40-yr study period (62-101) - just to see all the numbers.
Case with No ROTH conversion + Case with ROTH conversion to zero out T-IRA after 30 years.

The picture below summarizes the "60/40 Equal Location Base Case":

Image

The picture below summarizes the comparison between the "60/40 Equal location Base Case with ROTH conversion to empty out T-IRA in 30 years" and the "60/40 Equal Location Base case" - no significant surprise here: due to ROTH conversion the first case ended up with (1) higher ending ROTH account valuation, (2) lower RMD because of zeroing out T-IRA, and (3) lower taxable account ending value because of addition of lower RMD amounts compared to the Base Case. Notable: the total portfolio ending value for the Roth Conversion case after 30 years is only 2.1% higher than the Base Case in this scenario, easily within margin of modeling error - interesting case of swapping lower step-up basis in taxable account vs higher tax free ROTH account with ROTH conversion.

Image

The picture below summarizes the comparison between the "60/40 Portfolio_AA" and the "60/40 Equal Location" bases, both with ROTH conversion to empty out T-IRA in 30 years. The ending values for the Portfolio_AA case total are around 2% higher than the Equal Location case after 20 years, easily within margin of modeling error. No significant surprise here again, the Portfolio_AA taxable account valuation is higher than the Equal Location's because of higher stock %, offset by the Portfolio_AA Roth account lower valuation due to the higher bond percentage for ROTH in the later years to maintain the desired portfolio AA. Note the necessary annual rebalancing, and the ending 100% bond for the ROTH account which idea a lot of folks are not necessarily comfortable with.

Image

With my logic change I assign stock to taxable before ROTH as suggested in the wiki (I think), and I might try to move ROTH in front of taxable to meet the portfolio desired AA and take another look. I also tested different larger portfolio sizes and got similar results.
[Begin edit #1 - add following ext/pic]
The picture below summarizes the comparison between the "60/40 Portfolio_AA" with ROTH conversion to empty out T-IRA in 30 years; in the first case stock was assigned to ROTH first then Taxable then T-IRA; in the second case stock was assigned to ROTH first then taxable then T-IRA (case previously shown above). The ending values for the Stock-in-ROTH-First case total are around 16% higher than the Stock-in-taxable-First case after 20 years. It appears that in the first case [Begin edit #2]the ROTH and ROTH Conversion accounts valuation grew much faster than the Taxable account in the second case - because of higher % stock assigned to ROTH first. [End edit #2]

Image

I will dig deeper into the stock-in-ROTH-first case but as of now [End edit #1] I think that specifying 60/40 as AA for each of the individual accounts will likely be good enough for me to start putting in my own data and start taking advantage of RPM, and I thank you again for sharing not just such an awesome tool but also your time to help others BHs such as myself.

Best,
DSBH
dougie
Posts: 1
Joined: Wed Aug 26, 2020 8:33 pm

Re: Retiree Portfolio Model

Post by dougie »

BigFoot48 wrote: Tue May 29, 2018 11:33 am
pdbaaxk wrote: Tue May 29, 2018 9:06 am Does anyone know if it is possible for the model to use IRA2 for Roth conversions once IRA1 completely converted or doesn't have enough funds for desired conversion?
I'm afraid not. A solution is to add to IRA1 the extra amount desired, reducing IRA2 balance. Or just completely combine them for this purpose.
It''s been a couple years since this question and response. Has anyone implemented a full workaround for this yet? In my case, the difference in ages between my spouse and me, and the number of years we have to do conversions due to the 2019 SECURE Act, make it compelling to draw down IRA1 then partially draw down IRA2. This is what ORP is recommending. Combining the two into IRA1 in RPM really messes up the RMD calculations, throwing everything off.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

dougie wrote: Wed Aug 26, 2020 8:41 pm It''s been a couple years since this question and response. Has anyone implemented a full workaround for this yet? In my case, the difference in ages between my spouse and me, and the number of years we have to do conversions due to the 2019 SECURE Act, make it compelling to draw down IRA1 then partially draw down IRA2. This is what ORP is recommending. Combining the two into IRA1 in RPM really messes up the RMD calculations, throwing everything off.
I don't believe there has been much discussion of this over the years. However, I believe you should be able to adjust the beginning balances in your two separately owned IRAs such that after making the total conversions out of IRA1, the balances subject to RMD in each will be approximately correct as if the conversions had been made from both.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
DSBH
Posts: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

dougie wrote: Wed Aug 26, 2020 8:41 pm It''s been a couple years since this question and response. Has anyone implemented a full workaround for this yet? In my case, the difference in ages between my spouse and me, and the number of years we have to do conversions due to the 2019 SECURE Act, make it compelling to draw down IRA1 then partially draw down IRA2. This is what ORP is recommending. Combining the two into IRA1 in RPM really messes up the RMD calculations, throwing everything off.
I tried this workaround and it appears to give a decent approximation:

1. Do not enter IRA2 amount (e.g. $100,000) in section 2-Portfolio Balances,
2. Estimate what IRA2 amount may grow to when you/she want(s) to start Roth conversion (e.g. $200,000 in 12 years),
3. Enter that future IRA2 amount (e.g. $200,000) into section 6-IRA Contributions and Withdrawals as IRA1 contribution (cell E199), 0% as Yearly Change (F199), Start Age and End Age both equal to your/her age at that time (e.g. 12 years from now).

The RMDs will be off so you just have to play with the numbers.
nereocystis
Posts: 4
Joined: Thu Apr 19, 2018 8:29 pm

Re: Retiree Portfolio Model

Post by nereocystis »

I am getting errors in v20.2 using LibreOffice Calc.
I'm not sure where to look for solving the error.
It would be great to get rid of the error messages. Here's a description of what I see. I suspect that this isn't enough info, but I'm not sure what to add.

First the good news.
No negative final balances
No negative yearly balances

No the bad news.
In the Results tab, under Portfolio Activity by Account
Below Ending balance
Error (357) (357)

Under
Portfolio Activity by Account Comparison
I have the same error messages

Under Base tab
Total shows Error -357 under Earnings & Escalation Rates column
but under Totals column, there is still a plausible looking total
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

nereocystis wrote: Fri Aug 28, 2020 3:33 pm I am getting errors in v20.2 using LibreOffice Calc.
That's a warning message that there's a difference between the Detail page amounts and the summary. This error should not happen in normal use. PM sent.

UPDATE: The $357 amount shown in this message came from the amounts entered on the Detail page in the User Expense Adjustment line. These amounts are not currently automatically rounded to the Setup page Rounding setting. This will either be corrected to automatically round these amounts or a warning message added that the user must enter rounded amounts.
Last edited by BigFoot48 on Thu Sep 03, 2020 3:42 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

Hello,
I'm sure it's very simple, but is there any explanation for the 'cash flow diagram' or a link to some tutorial?

I'm returning to the RPM, nice to see the added comments, still quite daunting for a non spreadsheet/money modeller, :)
looking
Posts: 709
Joined: Thu Mar 01, 2007 6:14 pm
Location: morgan hill ,ca

Re: Retiree Portfolio Model

Post by looking »

gts1952 wrote: Thu Jan 28, 2016 9:11 pm I must be missing something, I entered our ages (64 & 59) on the setup page lines 24 & 25. I get an error when trying to enter the correct starting ages for our pensions in cells G83 thru G86 (65/60/65/65). The error message says "The starting age must be greater than or equal to your age". These starting ages are greater than our current age.
i'm a bit confused where is then model
nereocystis
Posts: 4
Joined: Thu Apr 19, 2018 8:29 pm

Re: Retiree Portfolio Model

Post by nereocystis »

thanks for helping me with this issue. Quick summary for the future. I used rounding, but my User expense adjustments were not rounded. By rounding to 1, the error went away. I could have rounded my user expense adjustments instead.
BigFoot48 wrote: Fri Aug 28, 2020 3:57 pm
nereocystis wrote: Fri Aug 28, 2020 3:33 pm I am getting errors in v20.2 using LibreOffice Calc.
That's a warning message that there's a difference between the Detail page amounts and the summary. This error should not happen in normal use. PM sent.

UPDATE: The $395 amount shown in this message came from the amounts entered on the Detail page in the User Expense Adjustment line. These amounts are not currently automatically rounded to the Setup page Rounding setting. This will either be corrected to automatically round these amounts or a warning message added that the user must enter rounded amounts.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Version 20.2c is available as of Sept 3. This fixes a rounding problem with user input amounts on the Details page in Expenses, Fed and State AGI adjustments and Foreign Income. Users not using these optional items or already inputting rounded amounts, e.g. $1,400 vs. $1,435 with Rounding set to $100 need not update. Download via: https://www.dropbox.com/s/nidfnhyhix96m ... .xlsm?dl=0

Reminder: the latest version is always available via Post #1.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
stvyreb
Posts: 159
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb »

strange in Libre Office 6.4.6.2 some of the version 19 macros work, but not in the newer v20.x
in LO 7.x when I click on the macro the cell just gets selected .... am I supposed to change it from a protected sheet or don't see an option to stop it from being selected vs. macro starting

so much easier to use when they do, I assume they don't have to be enabled with a default setup, as I can't see anything else to change.
Last edited by stvyreb on Fri Sep 04, 2020 5:49 pm, edited 1 time in total.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

stvyreb wrote: Fri Sep 04, 2020 2:02 pm strange in Libre Office 6.4.6.2 some of the version 19 macros work, but not in the newer v20.x

so much easier to use when they do, I assume they don't have to be enabled with a default setup, as I can't see anything else to change.
Both versions came from the same 2013 version of Excel. I know of no changes to the macro function but Microsoft may have made a change.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Post Reply