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: 740
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: 75
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: 3114
Joined: Tue Feb 20, 2007 9: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; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4: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: 95686
Joined: Sat Dec 20, 2008 4: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: 63
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: 3114
Joined: Tue Feb 20, 2007 9: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; 18-time loser
User avatar
LadyGeek
Site Admin
Posts: 95686
Joined: Sat Dec 20, 2008 4: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: 95686
Joined: Sat Dec 20, 2008 4: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: 95686
Joined: Sat Dec 20, 2008 4: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: 740
Joined: Tue Jul 21, 2020 3:31 pm
Location: Texas

Re: Retiree Portfolio Model

Post by DSBH »

LadyGeek wrote: Tue Nov 03, 2020 9: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: 5039
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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 8: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; 18-time loser
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8: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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Barsoom wrote: Mon Nov 23, 2020 10: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 11:08 am, edited 4 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 9:29 am
Admiral wrote: Mon Nov 23, 2020 8: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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 10: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; 18-time loser
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 11:01 am
Admiral wrote: Mon Nov 23, 2020 10: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: 5039
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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Mon Nov 23, 2020 11:50 am 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; 18-time loser
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Mon Nov 23, 2020 3:57 pm
Admiral wrote: Mon Nov 23, 2020 11:50 am 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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Tue Nov 24, 2020 12: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; 18-time loser
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 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: 75
Joined: Thu Mar 28, 2019 12:14 pm
Location: Reno, NV

Re: Retiree Portfolio Model

Post by MileKing »

Admiral wrote: Tue Nov 24, 2020 12:15 pm
BigFoot48 wrote: Mon Nov 23, 2020 3:57 pm
Admiral wrote: Mon Nov 23, 2020 11:50 am 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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

MileKing wrote: Fri Nov 27, 2020 4: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; 18-time loser
Admiral
Posts: 5039
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral »

BigFoot48 wrote: Fri Nov 27, 2020 5:51 pm
MileKing wrote: Fri Nov 27, 2020 4: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: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Admiral wrote: Sun Nov 29, 2020 7: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; 18-time loser
herbslc
Posts: 1
Joined: Sat Dec 05, 2020 11:59 am

Re: Retiree Portfolio Model

Post by herbslc »

something changed and is not noted in your notes. it's on the Setup tab, cells F278 and G278. not sure if this was a correction or something wiggled in the version. definitely changed my results.

thanks! love the s/s. best model i've found !
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

herbslc wrote: Sat Dec 05, 2020 12:14 pm something changed and is not noted in your notes. it's on the Setup tab, cells F278 and G278. not sure if this was a correction or something wiggled in the version. definitely changed my results.

thanks! love the s/s. best model i've found !
The rates in those cells were made visible in the last minor update, but nothing in the calculations should have changed. The example data results remains the same. Will do some checking. Glad you like it.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Formler
Posts: 1
Joined: Thu Dec 10, 2020 11:01 am

Re: Retiree Portfolio Model

Post by Formler »

Maybe mentioned before eg pg 10. Details State Bracket cell F221 and all of rows 222 & 223: displayed formulas are truncated. No error msg, not sure of results. Using Excel 365.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Formler wrote: Thu Dec 10, 2020 11:12 am Maybe mentioned before eg pg 10. Details State Bracket cell F221 and all of rows 222 & 223: displayed formulas are truncated. No error msg, not sure of results. Using Excel 365.
The formulas are working but are indeed had hard breaks resulting in them being displayed on many lines, requiring scrolling to view the entire formulas. Will fix on 21.0. Note that users do not have to view formulas, just results.
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 »

just curious, Does it make sense to adjust
setup tab 3). "by class" G82, 83, 85 stock, bond, MM rates" till
details tab F184 AGI matches my real world AGI for 2020 ?

my taxable allocations I believe are fairly accurate, but I'm using very low rates 3.0, 1.5, 0.5% respectively for the above

I ask this as it would be nice to have a ballpark on year to year income for use for my ACA health care estimates, even if just as a less precise 2nd source

2)
can someone explain Details Tab, Row 268, it says "tax-exempt interest | line 8b | user input | then its populated with amount in all cells;

it sounds like it is something I inputed somewhere, maybe from somewhere on the setup page?
what are the line numbers it is referring to ? or the others eg line 37 ; line 45& 50,

all my income is from investment for this year, but with the 'tax exempt' section amounts the MAGI doesn't seem accurate, I have just deferred interest savings bonds, not other tax exempts

3)
if I have capital loss carryovers, where might that be inputted, and if some of the mutual funds are international, how does one account for the foreign tax deduction

hope thats not too much for one post
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: Wed Dec 16, 2020 8:33 pm just curious, Does it make sense to adjust setup tab 3). "by class" G82, 83, 85 stock, bond, MM rates" till
details tab F184 AGI matches my real world AGI for 2020 ?

No. Use the rates you expect to achieve on average for the entire forecast period. Focusing on the current year will distort total results, the forecasting of which is the purpose of the model.

2)
can someone explain Details Tab, Row 268, it says "tax-exempt interest | line 8b | user input | then its populated with amount in all cells; it sounds like it is something I inputed somewhere, maybe from somewhere on the setup page?
what are the line numbers it is referring to ? or the others eg line 37 ; line 45& 50,

You apparently have an asset allocation which includes Tax Exempt bonds, entered in Setup page, Return Rates and Allocation. The line numbers refer to the IRS tax form.

3)
if I have capital loss carryovers, where might that be inputted, and if some of the mutual funds are international, how does one account for the foreign tax deduction

The model is not designed to include those tax factors but you might be able to use the Federal AGI User Adjustment and the Exclude Foreign Income manual input entries on the Details page.
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 Dec 05, 2020 12:49 pm
herbslc wrote: Sat Dec 05, 2020 12:14 pm something changed and is not noted in your notes. it's on the Setup tab, cells F278 and G278. not sure if this was a correction or something wiggled in the version. definitely changed my results.

thanks! love the s/s. best model i've found !
The rates in those cells were made visible in the last minor update, but nothing in the calculations should have changed. The example data results remains the same. Will do some checking. Glad you like it.
Hello BigFoot48,

It appears that the version 20.3 formula for cell F278 have changed from:

=IF(F275=0,0,1-ROUND(E276/F275,2))
to
=IF(E275=0,0,1-ROUND(E276/F275,2)).

As a result if the user does not enter any data in cells E273/E274 the whole increase in Taxable Account will be included in the AGI calculation as shown on row 171 of the Details sheet. In the previous version if there is no data in those cells then the whole increase is excluded in the AGI calc.

Is this an intended change? Please advise.

Thanks,
DSBH
John C. Bogle: "Never confuse genius with luck and a bull market".
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 Dec 25, 2020 3:50 pm Hello BigFoot48,

It appears that the version 20.3 formula for cell F278 have changed from:

=IF(F275=0,0,1-ROUND(E276/F275,2))
to
=IF(E275=0,0,1-ROUND(E276/F275,2)).

As a result if the user does not enter any data in cells E273/E274 the whole increase in Taxable Account will be included in the AGI calculation as shown on row 171 of the Details sheet. In the previous version if there is no data in those cells then the whole increase is excluded in the AGI calc.

Is this an intended change? Please advise.

Thanks,
DSBH
I did this as I thought it would be better to use the user supplied estimates of tax account growth and tax exempt earnings rather than whatever amounts show up in the first year. I think this can result in a more accurate estimate of the amount to exclude from taxes. Perhaps a warning to make this estimates should be shown? Or would users prefer to just default to the current year amounts?
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: Fri Dec 25, 2020 3:57 pm
DSBH wrote: Fri Dec 25, 2020 3:50 pm Hello BigFoot48,

It appears that the version 20.3 formula for cell F278 have changed from:

=IF(F275=0,0,1-ROUND(E276/F275,2))
to
=IF(E275=0,0,1-ROUND(E276/F275,2)).

As a result if the user does not enter any data in cells E273/E274 the whole increase in Taxable Account will be included in the AGI calculation as shown on row 171 of the Details sheet. In the previous version if there is no data in those cells then the whole increase is excluded in the AGI calc.

Is this an intended change? Please advise.

Thanks,
DSBH
I did this as I thought it would be better to use the user supplied estimates of tax account growth and tax exempt earnings rather than whatever amounts show up in the first year. I think this can result in a more accurate estimate of the amount to exclude from taxes. Perhaps a warning to make this estimates should be shown? Or would users prefer to just default to the current year amounts?
I think that it is a good idea to have user-supplied estimates, and a warning to make these estimates is definitely beneficial.

Thanks so much for the quick response!
John C. Bogle: "Never confuse genius with luck and a bull market".
RangeleyLake
Posts: 46
Joined: Mon May 20, 2019 7:03 pm

Re: Retiree Portfolio Model

Post by RangeleyLake »

DSBH wrote: Fri Dec 25, 2020 4:12 pm
BigFoot48 wrote: Fri Dec 25, 2020 3:57 pm
DSBH wrote: Fri Dec 25, 2020 3:50 pm Hello BigFoot48,

It appears that the version 20.3 formula for cell F278 have changed from:

=IF(F275=0,0,1-ROUND(E276/F275,2))
to
=IF(E275=0,0,1-ROUND(E276/F275,2)).

As a result if the user does not enter any data in cells E273/E274 the whole increase in Taxable Account will be included in the AGI calculation as shown on row 171 of the Details sheet. In the previous version if there is no data in those cells then the whole increase is excluded in the AGI calc.

Is this an intended change? Please advise.

Thanks,
DSBH
I did this as I thought it would be better to use the user supplied estimates of tax account growth and tax exempt earnings rather than whatever amounts show up in the first year. I think this can result in a more accurate estimate of the amount to exclude from taxes. Perhaps a warning to make this estimates should be shown? Or would users prefer to just default to the current year amounts?
I think that it is a good idea to have user-supplied estimates, and a warning to make these estimates is definitely beneficial.

Thanks so much for the quick response!
I would agree with DSBH. Thank you for the update and your continue support of RPM. Have a save and Happy New Year
RangeleyLake
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Happy New Year and let's hope 2021 is significantly better than 2020. Version 21.0 of RPM is available as of Jan 1. Changes include:
Federal 2021 tax factors
State income tax factors and brackets now have their own escalation rate. No longer uses Federal. Thanks BH Diynofees for suggestion.
Improved Taxable Account Adjustment section to alert user if estimates are not entered.
Maintaining portfolio allocation when using Asset Class Method remains to be implemented due to distractions during the year. Boglehead DSBH sent me a solution a few months ago with a recent update, which which I am studying. While this needs to be fixed, I continue to believe the impact on results is fairly minor as account allocations are maintained.
Download: https://www.dropbox.com/s/bx5ps24mq1gkq ... .xlsm?dl=0

Significant new features and changes can have unforeseen problems so please contact me via PM or post in this thread with any issues you find, or suggestions you may have.
Last edited by BigFoot48 on Sat Jan 02, 2021 2:21 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Skeeter1
Posts: 367
Joined: Sat Jul 06, 2019 1:50 pm

Re: Retiree Portfolio Model

Post by Skeeter1 »

BigFoot, you and your program are Totally awesome.

Since discovering it, the program has been enlightening and fascinating to use. I am a novice Excel user, so it was a slow go for me for a bit, but the program is just so user friendly that if you take your time and continue to tweak and analyze the results you can "catch" your own mistakes and easily correct.

So generous of you to take the time to benefit the community with your continued input and updates. 2021 is a very important year for me since I am 69 and have a few years left to consider additional Roth conversions before my RMD's start when I am 72.

Thank you again and Happy New year to you and the ones that you Love.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Thank you very much Skeeter1. Updating it was a bit more tedious this year with all the external distractions and I have to admit I'm hoping for a year of few suggestions to take a break, but a well-oiled and useful model for retirees remains the goal. Happy New Year to you and all Bogleheads.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
dgSteig
Posts: 4
Joined: Fri Nov 27, 2020 5:33 am

Re: Retiree Portfolio Model

Post by dgSteig »

Just updating to 21.1, and am having a strange problem. Hitting copy-paste first raises an error about overwriting a protected value, though it works to copy the data. Then I find it has changed the Calculation Options to Manual, which prevents any further changes from registering. Unlocking all of the cells in the Results area fixes the problem, not sure why the Calculation Options changed.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

dgSteig wrote: Sat Jan 02, 2021 6:04 am Just updating to 21.1, and am having a strange problem. Hitting copy-paste first raises an error about overwriting a protected value, though it works to copy the data. Then I find it has changed the Calculation Options to Manual, which prevents any further changes from registering. Unlocking all of the cells in the Results area fixes the problem, not sure why the Calculation Options changed.
RPM 21.0 was developed using Excel 2013 and the calculation setting remains at automatic, and I don't believe there are any unprotected areas on the Results page to copy anything to, except for the copy-store results boxes and note areas. They appear to allow copy/paste. Unprotecting the sheets is not recommended and is not supported.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
RangeleyLake
Posts: 46
Joined: Mon May 20, 2019 7:03 pm

Re: Retiree Portfolio Model

Post by RangeleyLake »

Good day BigFoot48
Thank you for the latest version of RPM. This tool just gets better every year. Thanks for your hard work on this.
I came across a few items that I receive an error message on the Set Up tab.
• The “clear” entry button on cell O1 came up with Run time error ‘1004’. “The cell or chart that you are trying to change is protected and therefore read-only. To modify….” I think it is looking at the Result Summary section and the Storage section on the set up page. It is not clearing those areas.
• The “clear” entry button on cell L496 at the Storage section, I am seeing a similar problem as above and the same error message.
• The “copy and paste” button at Result Summary section and the Storage section, the same problem as above and the same or similar error message.

Is this a problem because I have an older version of Excel? FYI, when I unprotected the sheet I did not have any problem.
Thanks again on your continue support and improvement of this tool that we used.
RangeleyLake
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

RangeleyLake wrote: Sat Jan 02, 2021 1:50 pm Good day BigFoot48
Thank you for the latest version of RPM. This tool just gets better every year. Thanks for your hard work on this.
I came across a few items that I receive an error message on the Set Up tab.
Thanks for that report. This has happened before when I work on the model in non-protected mode, testing everything, then forget to unprotect cells that change causing the error. WIll fix.

UPDATE: All fixed. Anyone who downloaded prior to this post time should download again.

Thanks RangeleyLake!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1335
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

Amazing tool. I've made a lot of Excel sheets over the years, but this is really top notch.

One thing I can't quite figure out is how to best handle an I inherited an IRA (stretch IRA) that I inherited. Seems like it has to be a future event? In section 7 (Setup!E243), I can enter the amount, but the age must be this year (not the past). So RMDs don't start until next year, correct if I inherited this year. But since I inherited some time ago, I actually have to take RMDs this year. I realize that doing anything automatically may violate a lot of the data structure, so I looked for a manual way to this and it looks like I can get pretty close by entering this year's estimated RMD in Setup!E216. Is that the best way to handle it or did I miss an easier way?
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

You should be able to put that IRA in Age and Years Factors, IRA RMD Ages, with the current year balance in the Special Events section, and have it take an RMD in the current year. Your other solution would work to show income/taxes but not account for the IRA balances.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Minor fix today to the optional income tax calculator's taxable SS income calculation (missing taxable pension income) Thanks to BH Diynofees for reporting and providing solution.

No need for most users to update.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1335
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

BigFoot48 wrote: Sat Jan 02, 2021 5:45 pm You should be able to put that IRA in Age and Years Factors, IRA RMD Ages, with the current year balance in the Special Events section, and have it take an RMD in the current year. Your other solution would work to show income/taxes but not account for the IRA balances.
Thank you- appreciate the feedback.
DebiT
Posts: 995
Joined: Sat Dec 28, 2013 12:45 pm

Re: Retiree Portfolio Model

Post by DebiT »

I have tried searching on this, but unfortunately I do not understand the answers.

I am a new user of RPM, and am trying to input my information. I am a recent widow, age 63, collecting survivor benefits on my husband's account. I will collect my own at age 70.

My first confusion is that the program seems to want me to put in the PIA for each of us, and I can no longer see that amount online, and don't have it anywhere else. My actual survivor benefit is $27504, and at age 70 I will get an additional $14406 for a total of $41910 as of now. How do I input this? An image of this spreadsheet section would be very helpful

Also, do I use spouse info or not, in the ages section? This is part of my confusion.

Finally, my financial plan has me dying at age 100. It seems the RPM doesn't want to allow this, and ends at 89. That won't work for my family longetivity. Is there a way to do this?

Thank you so much. I sense that this spreadsheet is going to be very helpful, esp because I have to calculate Roth conversions, but I'm stuck at the very beginning and feeling so frustrated.

EDIT: I think I solved both issues. For longevity, I just made the plan last 37 years, with no end date

For the SS issue, I kept a spouse, gave us both the same in section 1. In section 4, I started with my SS received now, and added an additional amount in the spouse section, starting age 70.

So now, my remaining question is, how to enter it correctly as instructed using the PIA amounts, which I don't have?

Thanks so much.
Age 66, life turned upside down 3/2/19, thanking God for what I've learned from this group. AA 40/60 for now, possibly changing at age 70.
User avatar
Topic Author
BigFoot48
Posts: 3114
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Hope you find the model useful. I can see the instructions in the cell comment may not be totally clear, but if a person is projecting future SS benefits they enter the PIA amounts, but if benefits have started you enter that amount, use your current age, and set the setting for "benefits started" to y. For your spousal benefit, you could also enter them in the "additional benefits" section with start age 70, but if what you did is working go with that.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Post Reply