Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
DSBH
Posts: 98
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

stvyreb wrote: Mon Oct 26, 2020 4:05 pm for "$100k income" are you putting that in Pension section?
Yes.
for case #1 shouldn't E273/277 be $197,500 in order to get "100%" for details ?
but, for your example you want them to be 0% because it's all unrealized capital gains, no distributions?

sorry doesn't make sense, it must be you mean for all 197,500 to be taxable? ( I noticed that expenses somehow change the $1000k x 5% calculations, so it's not $200k
Right [Begin - edit for clarification] "Earning from Taxable Account" is not 200K unless expenses equal 100K = pension income of 100K.
"Earning From Taxable Account" calculated as = [Taxable Acct Total Value + (Taxable Acct Total Value - net expenses)]/2 * 5% in this example.

So in case 1 zero "Earning from Taxable ACCT" is taxable because 100% of "Earning from Taxable ACCT" is due to share price increase, zero from distribution.

Or, 100% of "Earning from Taxable ACCT" (200K) is subtracted from total "earning+income" (200K + 100K), or in other words 0% of "Earning from Taxable ACCT" (200K) is taxable. [End - edit for clarification]
Last edited by DSBH on Fri Oct 30, 2020 11:49 am, edited 1 time in total.
John C. Bogle: "Never confuse genius with luck and a bull market".
Jogger
Posts: 28
Joined: Tue Apr 28, 2020 9:45 pm

Re: Retiree Portfolio Model

Post by Jogger »

:P :moneybag
DSBH wrote: Mon Oct 26, 2020 4:22 pm
stvyreb wrote: Mon Oct 26, 2020 4:05 pm for "$100k income" are you putting that in Pension section?
Yes.
for case #1 shouldn't E273/277 be $197,500 in order to get "100%" for details ?
but, for your example you want them to be 0% because it's all unrealized capital gains, no distributions?

sorry doesn't make sense, it must be you mean for all 197,500 to be taxable? ( I noticed that expenses somehow change the $1000k x 5% calculations, so it's not $200k
Right taxable earning is not 200K unless expenses equal 100K = pension income of 100K.
Earning calculated as = [Taxable + (Taxable - net expenses)]/2 * 5% in this example.

So in case 1 there is ZERO taxable earning because 100% of taxable earning is due to share price increase, zero distribution.

100% of earning is subtracted from total earning, or in other words 0% of earning is taxable.
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Version 20.3 is available as of Nov 1. This adds the ability to adjust (0%-100%) the amount of IRA withdrawals to include in state taxable income. (Thanks to LadyGeek for suggesting and helping with this improvement.) Also a tutorial for new RPM users created by Boglehead MrDrinkingWater has been added to the Readme page. Most users will not benefit from updating, especially this close to a 2021 version.

Download: https://www.dropbox.com/s/i744oepjpbuq3 ... .xlsm?dl=0 (Clear entry macro problem fixed about 5:00 EST. Revised version linked.)
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
User avatar
LadyGeek
Site Admin
Posts: 67287
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

I'm now using the latest Version 20.3 (with the macro fix). All OK, but I wanted to point out a few observations when moving from 20.2 to 20.3:

In the Setup sheet near the top ( Setup, Input Factors And Data), the comments have been updated. Specifically, "load entries" (N2) and Row 9 comments (Results Summary and copy).

In the Setup sheet, Section 8. Income taxes:

- "tax modeling option" has moved to the right of "itemized deductions" and is now called "tax factors escalation".

- Group titles "State Income Adjustments" and "Bracket method Factors" have been formatted for clarity.

- The "new" feature is in Row 289, "IRA withdrawals (less QCD) to include ".

=============================
Suggestion for the "load entries" comment in Setup N2: Add that the setup notes are not copied ("enter notes in these areas" K10:P21 and "notes" H52:L57) .
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: 67287
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

There's an error in the Social Security historical COLA table. In 1983, Social Security switched COLA from July to January. The row is offset by 1 starting in 1983. 2012 has an incorrect entry.

See: Cost-of-Living Adjustment (COLA) Information for 2021| SSA (Automatic Cost-Of-Living Adjustments received since 1975)

1983 is not listed in the Social Security table. (Also note the announced 2021 COLA of 1.3%.)

The data is in Tax Tables, T341:U387. Here's the corrected data. I didn't know what to use for 1983 (the missing year), so I inserted ??? as a placeholder. Perhaps make it the same as 1982?

Code: Select all

1974	
1975	 8.00 
1976	 6.40 
1977	 5.90 
1978	 6.50 
1979	 9.90 
1980	 14.30 
1981	 11.20 
1982	 7.40 
1983	 ??? 
1984	 3.50 
1985	 3.50 
1986	 3.10 
1987	 1.30 
1988	 4.20 
1989	 4.00 
1990	 4.70 
1991	 5.40 
1992	 3.70 
1993	 3.00 
1994	 2.60 
1995	 2.80 
1996	 2.60 
1997	 2.90 
1998	 2.10 
1999	 1.30 
2000	 2.50 
2001	 3.50 
2002	 2.60 
2003	 1.40 
2004	 2.10 
2005	 2.70 
2006	 4.10 
2007	 3.30 
2008	 2.30 
2009	 5.80 
2010	 -   
2011	 -   
2012	 3.60 
2013	 1.70 
2014	 1.50 
2015	 1.70 
2016	 -   
2017	 0.30 
2018	 2.00 
2019	 2.80 
2020	 1.60 
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.
zxllxz
Posts: 46
Joined: Fri Jun 08, 2018 7:43 am

Re: Retiree Portfolio Model

Post by zxllxz »

I found this table from the SSA, 1983 was 3.5 same as 1984

https://www.ssa.gov/oact/cola/colaseries.html
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

The SS table on the Tax page is just used to calculate 10-year averages for the COLA cell comment field. It's up to the user to enter a COLA for future years modeling. I will fix the offset and update the cell comment numbers.

Fixed. Impact on cell comment:
Annual average COLA as of 2020:
30 years: 2.3% same
20 years: 2.0% was 2.1
10 years: 1.7% same

Thanks for the comments on the new and moved feature and comments. Here's another one I added in the Return Rates section: "Alert: portfolio and account allocations not maintained after first year", an issue I hope to address.

"Suggestion for the "load entries" comment..." done!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
User avatar
LadyGeek
Site Admin
Posts: 67287
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

In the Setup sheet --> Section 8. Income Taxes --> taxable account adjustment (Row 272):

These descriptions confused me:

- Row 276: "Net growth distributed as dividends, CG, etc."
- Row 277: "Distributed as qualified dividends and LTCG"

My taxable account only has savings accounts and a CD. It took a while to understand that the first entry is short-term capital gains distributions, meaning interest and dividends that get taxed at ordinary rates. The second is long-term capital gains at the qualified rates.

Consider revising those descriptions to:

- Row 276: "Short-term gain distributions (interest, etc)."
- Row 277: "Long-term gain distributions (qualified dividends, etc.)."

The cell comment in D276 includes long-term gains for 2018. Consider moving:
Qualified dividends and long-term capital gains, as of 2018, are taxed at 0% for the lowest two tax brackets, 15% for the next three higher brackets, and 20% in the two highest.
to D277 (qualified dividends).

The corresponding description might need clarification. Perhaps from:

- N276: Dividends and STCG: Enter estimate of these included in net yearly growth

To:
- N276: Interest, Dividends, and STCG: Enter estimate of these included in net yearly growth

(The wiki has some background info: Capital gains distribution)
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: 67287
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

In section 9. Optional: Alternative Tax Rates, the comments in Cells A9 and F317 (Change year) use 2025 as an example for the starting year.

Based on current law for the Tax Cuts and Jobs Act, individual tax rates expire after December 31, 2025 and revert to the previous rates. I believe 2026 would be a better number for those comments.

Google "tax cuts jobs act expire december 31, 2025".

Update: Here's one source: Tax Reform Isn’t Done | Tax Foundation
After December 31, 2025, the individual income tax rate schedule is set to revert to the pre-TCJA brackets and rates.
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: 67287
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Retiree Portfolio Model

Post by LadyGeek »

There's a typo in the Setup sheet --> Portfolio Cash Flow Diagram (starting at R6).

The lower-left side arrow "Special Evt" should be "Special Exp" (Special Expenditures).

The text comes from cell "Shape_Special" and is defined in AA65. It tracks to the Summary sheet with one of the columns titled "Special Expend".

Update: I'm not so sure this is a typo. The items come from section 7.0 Special Events, but this section includes QCDs. The cash flow is calculated in the Summary sheet as "Asset Sales" - "Special Expend" - "SPIA Taxbl", so perhaps I don't fully understand what this means.
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: 98
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

LadyGeek wrote: Tue Nov 03, 2020 10:41 am In the Setup sheet --> Section 8. Income Taxes --> taxable account adjustment (Row 272):

These descriptions confused me:

- Row 276: "Net growth distributed as dividends, CG, etc."
- Row 277: "Distributed as qualified dividends and LTCG"

My taxable account only has savings accounts and a CD. It took a while to understand that the first entry is short-term capital gains distributions, meaning interest and dividends that get taxed at ordinary rates. The second is long-term capital gains at the qualified rates.
I think that the cell comment in D276 is correct ("an estimate of how much of the net yearly growth, as shown above, will be distributed for the year as interest, dividends, qualified dividends and short and long term capital gains") but the description in N276 is not correct for cell E276. For instance:

E273 = 200,000 = Annual growth in Taxable Account, 100K from saving accounts/CD interests and 100K from Vanguard Total Stock Market Index
E274 = 0 = no tax-exempt interest

Assuming that the 100K growth from Vanguard Total Stock Market Index came from 20K of qualified dividends/LTCG and 80K of share price increase:

E276 = 120,000 = 100K total interest rom saving accounts and CD + 20K of qualified dividends from Vanguard Total Stock Market Index
E277 = 20,000 = qualified dividends and LTCG

Cells F278 and G278 will show 40% (may need to change cell text color to see them), meaning 40% of the 200,000 growth (or 200,000 - 120,000 = 80,000) will be deducted from the AGI calculation because it's from Vanguard fund share price increase as shown in the Details and Base sheets' row 171, and 20,000 will be deducted from federal tax calculations as shown in the Details and Base sheets' row 192 (see comments in cell B192).
John C. Bogle: "Never confuse genius with luck and a bull market".
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

Question on how to account for pension contributions:

I couldn't figure out why RPM was off by 8-10k on my federal taxes, showing that we were at 35% marginal when we are really at 24%.

One thing I found is that there seems to be no place to account for a federally deductible (that is, pre-tax) retirement contribution that is a pension, not an IRA. In our case this amount is about $9,000 per year. If I simply add this to one of the pre-tax IRAs that are in the model, then the balances of that IRA will increase incorrectly.

The pension contrib is basically its own bucket, and the balance is (in some ways) irrelevant, since we do not account for or plan to take the cash.

Where, either in the set-up page or on the details page, can this be added so it properly reduces our federally taxable income?
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 9:04 am Question on how to account for pension contributions:

I couldn't figure out why RPM was off by 8-10k on my federal taxes, showing that we were at 35% marginal when we are really at 24%.

One thing I found is that there seems to be no place to account for a federally deductible (that is, pre-tax) retirement contribution that is a pension, not an IRA. In our case this amount is about $9,000 per year. If I simply add this to one of the pre-tax IRAs that are in the model, then the balances of that IRA will increase incorrectly.

The pension contrib is basically its own bucket, and the balance is (in some ways) irrelevant, since we do not account for or plan to take the cash.

Where, either in the set-up page or on the details page, can this be added so it properly reduces our federally taxable income?
You can add that to the Adjusted Gross Income calculation - User input income, credit and adjustments line on the Details page to reduce Federal taxable income.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Barsoom
Posts: 347
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

Is it worth adding a separate entry in the Expenses section for mortgage payments since it is a known term expense?

Currently, I include mortgage payments in the Living Expenses entry, but then I go into the Details tab and back out the annualized payment in the "User expense adjustments" line (negative entry) beginning in the year after the mortgage ends through the rest of the planning period.

Should the spreadsheet do this automatically or is it best to just keep it the way I'm doing it?

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

Barsoom wrote: Mon Nov 23, 2020 11:23 am Is it worth adding a separate entry in the Expenses section for mortgage payments since it is a known term expense?

Currently, I include mortgage payments in the Living Expenses entry, but then I go into the Details tab and back out the annualized payment in the "User expense adjustments" line (negative entry) beginning in the year after the mortgage ends through the rest of the planning period.

Should the spreadsheet do this automatically or is it best to just keep it the way I'm doing it?

-B
You'll need to continue to do it your way (or add it IN using the User Adj) as I'm not inclined to add it as a separate expense as most retirees (target market for model) don't have mortgages. But I will consider adding a temporary expense option.

You could also use the existing expense Change Age and Year to drop the mortgage payment.
Last edited by BigFoot48 on Mon Nov 23, 2020 12:08 pm, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 10:29 am
Admiral wrote: Mon Nov 23, 2020 9:04 am Question on how to account for pension contributions:

I couldn't figure out why RPM was off by 8-10k on my federal taxes, showing that we were at 35% marginal when we are really at 24%.

One thing I found is that there seems to be no place to account for a federally deductible (that is, pre-tax) retirement contribution that is a pension, not an IRA. In our case this amount is about $9,000 per year. If I simply add this to one of the pre-tax IRAs that are in the model, then the balances of that IRA will increase incorrectly.

The pension contrib is basically its own bucket, and the balance is (in some ways) irrelevant, since we do not account for or plan to take the cash.

Where, either in the set-up page or on the details page, can this be added so it properly reduces our federally taxable income?
You can add that to the Adjusted Gross Income calculation - User input income, credit and adjustments line on the Details page to reduce Federal taxable income.
Yes I thought for that. The problem is my state and local tax burden (7% total) is based on gross, with no deductions for retirement or pre-tax pension contribs. So If the AGI is reduced, those calculations will be wrong.
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 11:40 am
Yes I thought for that. The problem is my state and local tax burden (7% total) is based on gross, with no deductions for retirement or pre-tax pension contribs. So If the AGI is reduced, those calculations will be wrong.
There's also a User adjustment for state taxes where you could add/deduct that.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 12:01 pm
Admiral wrote: Mon Nov 23, 2020 11:40 am
Yes I thought for that. The problem is my state and local tax burden (7% total) is based on gross, with no deductions for retirement or pre-tax pension contribs. So If the AGI is reduced, those calculations will be wrong.
There's also a User adjustment for state taxes where you could add/deduct that.
Roger that, thank you, I now see that.
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

Somewhat related:

The planner seems to calculate that IRA contributions are deducted from the taxable account (?), as if the money goes there and then comes out. For example, on the "Details" tab, under "Taxable account - IRA contributions" there is an entry that appears as "(38,400)" [F77] which does reflect the total of two IRA contributions made (including employer contribution, see below).

However as we know this is not what happens in real life. Also, $12,000 of this is employer match, which is not reflected in gross income.

How should this be dealt with? Should I "Gross up" my gross income to include the employer contribution? This is also the case for my spouse.
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 12:50 pm Somewhat related:

The planner seems to calculate that IRA contributions are deducted from the taxable account (?), as if the money goes there and then comes out. For example, on the "Details" tab, under "Taxable account - IRA contributions" there is an entry that appears as "(38,400)" [F77] which does reflect the total of two IRA contributions made (including employer contribution, see below).

However as we know this is not what happens in real life. Also, $12,000 of this is employer match, which is not reflected in gross income.

How should this be dealt with? Should I "Gross up" my gross income to include the employer contribution? This is also the case for my spouse.
I think I would start your model at your retirement age and estimate what your IRA balances will be then.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 4:57 pm
Admiral wrote: Mon Nov 23, 2020 12:50 pm Somewhat related:

The planner seems to calculate that IRA contributions are deducted from the taxable account (?), as if the money goes there and then comes out. For example, on the "Details" tab, under "Taxable account - IRA contributions" there is an entry that appears as "(38,400)" [F77] which does reflect the total of two IRA contributions made (including employer contribution, see below).

However as we know this is not what happens in real life. Also, $12,000 of this is employer match, which is not reflected in gross income.

How should this be dealt with? Should I "Gross up" my gross income to include the employer contribution? This is also the case for my spouse.
I think I would start your model at your retirement age and estimate what your IRA balances will be then.
Thanks. Yes. I could certainly do that. But one of the benefits of RPM, to me at least, is the sheet's ability to calculate future cashflows and balances (not to mention taxes). This is highly relevant to me now, 7-8 years pre-retirement. I'd prefer not to guess at balances nearly a decade hence, but have them calculated.

Does your reply indicate there's no good (easy) way to force the software to accurately calculate pre-tax savings when they are not part of one's gross income? I do understand that having all monies flow into taxable makes sense for those who are already retired and have no pre-tax deductions. But is there a workaround for those who are still working?
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Tue Nov 24, 2020 1:15 pm Thanks. Yes. I could certainly do that. But one of the benefits of RPM, to me at least, is the sheet's ability to calculate future cashflows and balances (not to mention taxes). This is highly relevant to me now, 7-8 years pre-retirement. I'd prefer not to guess at balances nearly a decade hence, but have them calculated.

Does your reply indicate there's no good (easy) way to force the software to accurately calculate pre-tax savings when they are not part of one's gross income? I do understand that having all monies flow into taxable makes sense for those who are already retired and have no pre-tax deductions. But is there a workaround for those who are still working?
Offhand I can't think of a way to accommodate your situation in the model as modeling working years was not part of the original concept. Perhaps another user has come up with a work-around and could offer advice.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Barsoom
Posts: 347
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

FYI to users of the Monte Carlo variant...

I know a major annual release is planned to come out soon, but I wanted to get this out.

I made some major changes to the data used by the Monte Carlo variant. I wasn't comfortable with the future scenarios being generated to test sequence of return risk, so I took another look at the source data I was basing the trends on.

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

The new parameters will be as follows:
  • Inflation will be based on the annual inflation rates from 1953 forward.
  • Stock growth will be based on the Total Stock Fund from Vanguard (source Simba Back-test spreadsheet, annual) from 1953 forward.
  • Bond growth will now be based on the Total Bond Fund from Vanguard (source Simba Back-test spreadsheet, annual) from 1953 forward.
  • Money Market growth will now be based on T-Bills (source Simba Back-Test spreadsheet, annual) from 1953 forward.
  • Based on analyzing the market data from the Simba back-testing spreadsheet (thanks Siamond), the data suggests these correlations:
    1. Annual inflation rate is 71.6% correlated to non-inflation-adjusted annual T-Bill rates.
    2. Inflation-adjusted T-Bill rate is 58.4% correlated to inflation-adjusted Total Bond Market rate from 1953 forward.
    3. Inflation-adjusted Total Bond Market rate is 17.13% correlated to inflation-adjusted Total Stock Market rate from 1953 forward.
    4. Inflation will be generated first. Since 1953, there is (on average) a 76.47% correlation between inflation and the next year's inflation (1:n-1 correlated to 2:n), or 1953:2018 correlated to 1954:2019).
      • The inflation model can be switched between a normal distribution and a percentile distribution (default).
      • You can experiment with this to see if you find an inflation setting that you like better.
      • The inflation model can be turned off by setting Start with current inflation rate to "y" and setting the inflation correlation to 100%.
    5. After inflation is created, the T-Bill dataset will be created and correlated to inflation.
    6. Then the Total Bond Market dataset will be created and correlated to the T-Bill dataset.
    7. 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.

Using the pre-loaded case study in the spreadsheet...
Example Data: $70k expenses before tax. SS at 70 & 66. 23% SS reduction in 2035. $200k inherited. Sell house at 84. Wife widowed at 85. Roth conversions will leave $1.2M to heirs.
The base case portfolio in 2049 is $2,728,900. Roth conversions are turned off. Here are some findings from running the Monte Carlo sequence of return risk analysis:
  1. The weighted average portfolio in 2049 is $1,464,513.
  2. Monte Carlo analysis predicts an 11.9% probability of running out of money.
    • Worst-case scenario runs out of money in year 21.
    • The 2.5% risk case runs out of money in year 27.
    • The 5.0% risk case runs out of money in year 28.
    • The 7.5% risk case runs out of money in year 29.
    • The 11.9% risk case (last case that runs out of money) is in year 30.
    • The first successful case that does not run out of money has a portfolio in 2049 of $700.
  3. The worst-case scenario is a retirement into a prolonged period of high inflation with decent market returns. Inflated living expenses are not made up with earnings.
  4. The weighted-average case (54.9% case) is average inflation with average market conditions. From years 9 to 16, earnings exceed living expenses, resulting in a 0% withdrawal rate.
As usual, please post any comments, questions, concerns as a reply to this post.

-B
MileKing
Posts: 12
Joined: Thu Mar 28, 2019 12:14 pm
Location: Reno, NV

Re: Retiree Portfolio Model

Post by MileKing »

Admiral wrote: Tue Nov 24, 2020 1:15 pm
BigFoot48 wrote: Mon Nov 23, 2020 4:57 pm
Admiral wrote: Mon Nov 23, 2020 12:50 pm Somewhat related:

The planner seems to calculate that IRA contributions are deducted from the taxable account (?), as if the money goes there and then comes out. For example, on the "Details" tab, under "Taxable account - IRA contributions" there is an entry that appears as "(38,400)" [F77] which does reflect the total of two IRA contributions made (including employer contribution, see below).

However as we know this is not what happens in real life. Also, $12,000 of this is employer match, which is not reflected in gross income.

How should this be dealt with? Should I "Gross up" my gross income to include the employer contribution? This is also the case for my spouse.
I think I would start your model at your retirement age and estimate what your IRA balances will be then.
Thanks. Yes. I could certainly do that. But one of the benefits of RPM, to me at least, is the sheet's ability to calculate future cashflows and balances (not to mention taxes). This is highly relevant to me now, 7-8 years pre-retirement. I'd prefer not to guess at balances nearly a decade hence, but have them calculated.

Does your reply indicate there's no good (easy) way to force the software to accurately calculate pre-tax savings when they are not part of one's gross income? I do understand that having all monies flow into taxable makes sense for those who are already retired and have no pre-tax deductions. But is there a workaround for those who are still working?
RPM is a great tool and sometimes their are limitations one needs to workaround for their own specific situation. In your particular case, the only potential workaround I can see is manually modifying the calculation for end of year balance for the IRA account in the year of the contribution and the start of year balance in the next year. This would need be done in both the Details and Base tabs. So if you (and/or your employer) contributed $10,000 to the IRA account, you could put () around the existing formula and then enter a "+10000" following the formula. You would need to do this for each year where you have an IRA contribution that is coming from your pay. In the limited testing I've done using this approach, the consistency and integrity of the spreadsheet calculations and the user data seem to be maintained. I would not recommend doing this unless you are very familiar with RPM and its operation, and Excel formulas. Perhaps Bigfoot48 can weigh in on whether this approach causes unforeseen issues.
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

MileKing wrote: Fri Nov 27, 2020 5:55 pm RPM is a great tool and sometimes their are limitations one needs to workaround for their own specific situation. In your particular case, the only potential workaround I can see is manually modifying the calculation for end of year balance for the IRA account in the year of the contribution and the start of year balance in the next year. This would need be done in both the Details and Base tabs. So if you (and/or your employer) contributed $10,000 to the IRA account, you could put () around the existing formula and then enter a "+10000" following the formula. You would need to do this for each year where you have an IRA contribution that is coming from your pay. In the limited testing I've done using this approach, the consistency and integrity of the spreadsheet calculations and the user data seem to be maintained. I would not recommend doing this unless you are very familiar with RPM and its operation, and Excel formulas. Perhaps Bigfoot48 can weigh in on whether this approach causes unforeseen issues.
That may accomplish what Admiral needs, however I would try something a little different. Unprotect the Details page and add any yearly company contributions to IRA 1 Contributions line 87 and/or IRA2 Contributions line 98. Add amounts to the end of the existing formula, e.g. +20,000 for each year. Base case will use these amounts so no need to edit it.

This will insure the added amounts are included in the years earnings calculation, I believe. Good luck!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Admiral
Posts: 3090
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Fri Nov 27, 2020 6:51 pm
MileKing wrote: Fri Nov 27, 2020 5:55 pm RPM is a great tool and sometimes their are limitations one needs to workaround for their own specific situation. In your particular case, the only potential workaround I can see is manually modifying the calculation for end of year balance for the IRA account in the year of the contribution and the start of year balance in the next year. This would need be done in both the Details and Base tabs. So if you (and/or your employer) contributed $10,000 to the IRA account, you could put () around the existing formula and then enter a "+10000" following the formula. You would need to do this for each year where you have an IRA contribution that is coming from your pay. In the limited testing I've done using this approach, the consistency and integrity of the spreadsheet calculations and the user data seem to be maintained. I would not recommend doing this unless you are very familiar with RPM and its operation, and Excel formulas. Perhaps Bigfoot48 can weigh in on whether this approach causes unforeseen issues.
That may accomplish what Admiral needs, however I would try something a little different. Unprotect the Details page and add any yearly company contributions to IRA 1 Contributions line 87 and/or IRA2 Contributions line 98. Add amounts to the end of the existing formula, e.g. +20,000 for each year. Base case will use these amounts so no need to edit it.

This will insure the added amounts are included in the years earnings calculation, I believe. Good luck!
Thanks. I tried this and it does calculate correctly. The issue, however, is RPM is still drawing down the taxable account, so the account goes negative, when in reality it doesn't...because those contributions are going into the IRA from gross pay, not from taxable (or BEFORE gross pay, in the case of the employer contribution.)

For example, without the added employer contribution, using $28,000 as the IRA 1 contribution, the taxable account goes negative in 2027. But when we start adding in the extra $12,000 per year after the formula in Line 87, RPM sees that as more money coming out, so the "negative" balance starts to move forward. If I add the extra $12k in 2019, the taxable account then goes negative in 2023, etc.
User avatar
Topic Author
BigFoot48
Posts: 2837
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Sun Nov 29, 2020 8:38 am Thanks. I tried this and it does calculate correctly. The issue, however, is RPM is still drawing down the taxable account, so the account goes negative, when in reality it doesn't...because those contributions are going into the IRA from gross pay, not from taxable (or BEFORE gross pay, in the case of the employer contribution.)

For example, without the added employer contribution, using $28,000 as the IRA 1 contribution, the taxable account goes negative in 2027. But when we start adding in the extra $12,000 per year after the formula in Line 87, RPM sees that as more money coming out, so the "negative" balance starts to move forward. If I add the extra $12k in 2019, the taxable account then goes negative in 2023, etc.
The use of RPM for working years forecasting is not supported so any workarounds may not function properly. I recommend using i-ORP.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 14-time loser
Post Reply