Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
User avatar
Topic Author
BigFoot48
Posts: 2705
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue May 28, 2019 8:13 am

FutureEyee:
1. Rounding is user select-able. Forecasts 30 years out can show amounts to the dollar if someone thinks that adds to accuracy or understanding. The reality is that after carefully entering all the balances, factors and choices, virtually every calculation and result will be wrong from what the real-world actual results, so take it all with a grain-of-salt.
2. It's not a Bogleheads model, it's mine, enhanced by dozens of BH members, that the forum allows me to make available to those interested. I think averaging the changes in an account during the year to calculate returns makes it more accurate.
3. Current year estimated taxes are deducted from the current year Taxable account, which is added to the other accounts to arrive at the current year ending balance. That appears to be similar to what your model does.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

FutureEye
Posts: 9
Joined: Tue May 28, 2019 6:40 am

Re: Retiree Portfolio Model

Post by FutureEye » Wed May 29, 2019 8:03 am

BigFoot48:
Thanks for making & sharing the Retiree Portfolio Model. It is truly a well thought out spread sheet.

I'm trying to establish a 5yr + or - 20% accuracy (or should i say confidence) & 30yr ROM accuracy. Part of my estimating methodology is to error on the high side of expenses & low side of returns with the intent of having more confidence that I'll have enough money. I'm going to your averaging to see what the difference is in my spreadsheet. I use Format Rounding, not the Round Function because of cumulative nature of projecting out 30yrs. I agree it makes no difference to a ROM estimate.

Today the primary purpose of my research is to determine optimum IRA to Roth conversion profile. Thanks again.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed May 29, 2019 10:18 am

FutureEye wrote:
Wed May 29, 2019 8:03 am
I use Format Rounding, not the Round Function because of cumulative nature of projecting out 30yrs. I agree it makes no difference to a ROM estimate.

Today the primary purpose of my research is to determine optimum IRA to Roth conversion profile. Thanks again.
I hope the model proves useful in your effort.

Rounding of calculations does impact the results. Using the Example data in v19.1a at different rounding settings results in these ending portfolio balances:
$1 $2,697,094
$100 $2,702,400 (default setting)
$1,000 $2,389,000

The use of rounding was done to make reviewing results a little easier to do. At the $100 setting the impact is very small and I believe the user can rely on the calculation accuracy at the $100 setting. Thanks for your comment.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Bongleur
Posts: 2252
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Sat Jun 01, 2019 7:53 pm

I need to wrap my head around how to interpret nominal numbers instead of "real" numbers that equal "today."
I guess to be precise, the RPM shows "inflated" numbers, since it is conservative by using constant inflators.

Bigfoot48 (quoted below) gave a laundry list of inflators, is that all of them? And which are user-selectible by default (as opposed to digging into the black box, as for SS and tax bracket inflators). He didn't mention the COLA for Pensions. SPIA has a COLA. I've tried to find all the user inputs that have inflators, but I can't be sure that I've not missed something.

In the RESULTS tab:
rows 10-20 show the cash flow year by year.
Column F (start age) is "today" so that is the only "real" value.
The following years are all "inflated."

The "final" account balance (E19) is positive. So the SWR succeeded, I didn't go broke before the end. But I don't know by how much in non-inflated dollars, which is all I understand. Except that I can divide the final balance by the amount I spent in the final year, and know that I have so many years of spending remaining (not counting further inflation). So that's a reasonable measure of "how well did it succeed."

I don't know how hard it would be to duplicate rows 10-20 after applying a "deflator" to get back to Starting Year Real Money. Its just the way I am accustomed to thinking about this. So I have less confidence making comparisons of "nominal" numbers.

The RPM allows variable spending and even large one time expenses. So the graphical displays in Row 22 can visualize that. But I still have a desire to see "how close to failure" a large expense might push the result.

If they were in terms of % of starting value instead of nominal dollars, you could sometimes more easily judge changes. For example, Taxable SS Benefits as % would show The Hump, right?

Since the RPM does not take into account real-life variance in returns, it cannot show a Series Of Bad Returns. Therefore I'd like to have some way of looking at every year in "start year money" to determine if the year after some major change "looks bad." Then I would want to reduce earlier withdrawals. For instance, the year after death of a spouse (with major income reduction from pension & SS).

***********

viewtopic.php?f=2&t=97352&start=650
Re: Retiree Portfolio Model
Post by BigFoot48 » Sat Apr 20, 2019 9:01 am
>
Admiral wrote:
Can someone point me to where the expected rates of inflation are entered in RPM for investments?

I see that there is a field to adjust inflation in Section 5. Income. Does that number also apply to the entire model? In other words are the percentages in Sec 3 Return Rates and Allocations real rates, after the % in sec 5 is applied, or nominal? Or is there some internal inflation calculation that cannot be adjusted?

The results are obviously in inflated numbers, so I'm just trying to understand what happens if inflation is higher or lower.
>

BigFoot48:
The model is designed to model real life financial results. Investments don't go up in real terms and expenses don't change in real terms. However, if someone wants to model future years using real rates it could be done but you're on your own in determining how to do that. I have no interest in supporting such a forecast.

Section 3: Return Rates are nominal. If you think stocks will go up 5% this year and in future years enter 5%.
Section 4: Social Security has an estimate of the actual COLA rate used by SS. Note: A model using real rates would have the SS benefits in 2049 the same as 2019. Is that beneficial?
Section 5: Expenses has the COLA inflation rate for expenses. Only this one rate is used for all years in forecasting living expenses to be incurred over the selected model periods.
Section 7: Special Events are entered at the future estimated values. A Future Value Calculator is provided to assist in determining those amounts. NOTE: The model has a number of Calculators on the Setup page. Any amounts shown on them are not used in the model unless the user inputs them somewhere.
Section 8: Income Taxes. Current year income tax brackets, standard deductions are escalsted based on a historic average of the change used by the IRS and this rate is on the Tax Tables page.

Cell comments are provided detailing what every setting does and how it is used. I recommend they all be read when starting to use the model.
***

ADMIRAL:
Thanks for that explanation. My question(s) is how to interpret the results in RPM vs a MC simulator like Flexible Retirement Planner which gives future results in current dollars and applies an expected (i.e. projected) inflation rate (with an input for standard deviation).

So, the question is how to square a future portfolio value in RPM that is $12m in 30 years vs. the same portfolio valued at (for example) $5m in 30 years but given in current dollars.
***

BIGFOOT48:
Yes. I recommend you use iORP which is also a nominal forecaster and agrees closely to RPM results if you want a comparison.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

Bongleur
Posts: 2252
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Sat Jun 01, 2019 7:55 pm

Given that the RPM uses a constant % for investment gains (aka "growth" or "yield") , is it correct to say that it uses the Arithmatic Average Growth Rate (instead of CAGR) ?

Real-world Variance means that there is a substantial chance that the actual growth will fall below the "straight-line" growth used by RPM, right?

SO how do I rationally discount the input for expected arithmatic average growth rate to give a result that is a not-to-improbable, but worse than linear average, series of returns? More defensible than "I think its 5% so I'll just use, uh, say, 4%"

Suppose I use some other analyzers that predict my portfolio Variance is V -- or maybe even a V for Equity and Bonds individually. Does V give me information I can use to discount the straight line percentage? Perhaps by V, or half V (sigma) ?
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

Admiral
Posts: 2047
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Sun Jun 02, 2019 8:31 am

Bongleur wrote:
Sat Jun 01, 2019 7:55 pm
Given that the RPM uses a constant % for investment gains (aka "growth" or "yield") , is it correct to say that it uses the Arithmatic Average Growth Rate (instead of CAGR) ?

Real-world Variance means that there is a substantial chance that the actual growth will fall below the "straight-line" growth used by RPM, right?

SO how do I rationally discount the input for expected arithmatic average growth rate to give a result that is a not-to-improbable, but worse than linear average, series of returns? More defensible than "I think its 5% so I'll just use, uh, say, 4%"

Suppose I use some other analyzers that predict my portfolio Variance is V -- or maybe even a V for Equity and Bonds individually. Does V give me information I can use to discount the straight line percentage? Perhaps by V, or half V (sigma) ?
I recommend you use RPM in conjunction with a MC simulator that uses randomized returns and growth rates. I use Flexible Retirement Planner, but there are others. (FRP shows results in current dollars, but it accounts for inflation.) In general I find that the results in one tool match the results in the other, assuming you use the same inputs. (I agree that looking 35+ years out and seeing I will have $12 million is little odd, but in any case it's impossible to predict that far out with any tool, regardless of how future dollars are displayed.)

One thing you can play around with is the section where the return rates change. RPM uses that more for "near end of life" changes, but you could easily change the presumed rates of return to an earlier year, say, to simulate a crash or a period of depressed returns. (It's the section called "optional, change asset allocation in a future age."

I also recommend you use i-ORP, which has a Monte Carlo function. I see RPM's main value in estimating taxes and modeling Roth conversions. FRP is less good for those kinds of calculations.

User avatar
Zephavest
Posts: 51
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Sun Jun 02, 2019 9:46 am

Another, simpler, solution if you assume long term growth rates are going to be an annualized 7%, just set your return rate so something less to account for the unknowns, such as 5% annualized. You will save a lot of work and a few headaches, 2,500 monte carlo simulations and cross checking to multiple tools may not yield any better results (yes, I've tried multiple tools before I finally arrived at my simplified approach).

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Mon Jun 03, 2019 5:24 pm

so using v19.1 MSO H&S v.2019 when I hover over the red triangles e.g. A9 cell the comments are cutoff on the bottom of the popup, and I see no way to scroll to read them,

is there a setting I'm suppose to engage or disengage or any other way to read the cell comments please?


addendum: so.......it appears they are cut off if one engages the macro to 'show' borders&lines FYI , turned off I can see them fwiw

sandramjet
Posts: 271
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet » Mon Jun 03, 2019 5:58 pm

You can also unprotect the sheet (on Review tab), then click on the one you want, resize it, then re-protect the sheet.

Bongleur
Posts: 2252
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Mon Jun 03, 2019 8:20 pm

Zephavest wrote:
Sun Jun 02, 2019 9:46 am
Another, simpler, solution if you assume long term growth rates are going to be an annualized 7%, just set your return rate so something less to account for the unknowns, such as 5% annualized. You will save a lot of work and a few headaches, 2,500 monte carlo simulations and cross checking to multiple tools may not yield any better results (yes, I've tried multiple tools before I finally arrived at my simplified approach).
But that's just a random guess. If some other tool says my asset allocation outputs a CAGR then there should be some math that will give me the AGR (which is what RPM uses, no?).
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

User avatar
FiveK
Posts: 6916
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK » Mon Jun 03, 2019 8:45 pm

Bongleur wrote:
Mon Jun 03, 2019 8:20 pm
...the AGR (which is what RPM uses, no?).
No.

Admiral
Posts: 2047
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Tue Jun 04, 2019 6:31 am

Bongleur wrote:
Mon Jun 03, 2019 8:20 pm
Zephavest wrote:
Sun Jun 02, 2019 9:46 am
Another, simpler, solution if you assume long term growth rates are going to be an annualized 7%, just set your return rate so something less to account for the unknowns, such as 5% annualized. You will save a lot of work and a few headaches, 2,500 monte carlo simulations and cross checking to multiple tools may not yield any better results (yes, I've tried multiple tools before I finally arrived at my simplified approach).
But that's just a random guess. If some other tool says my asset allocation outputs a CAGR then there should be some math that will give me the AGR (which is what RPM uses, no?).
It's all random. If you're looking for exactitude you're not going to find it. The tools are best used to explore a range of possibilities and outcomes by adjusting the inputs. All it takes is one year of a severe market crash to blow up your (or any) plan. The 4% SWR is as good a plan as any, as it has survived any and all crashes (historically, which of course does not mean it WILL, only that it's likely to.)

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jun 04, 2019 2:32 pm

sandramjet wrote:
Mon Jun 03, 2019 5:58 pm
You can also unprotect the sheet (on Review tab), then click on the one you want, resize it, then re-protect the sheet.
actually that has no effect at all, the choice is review-> unprotect sheet

sadly, *further, now macro 'borders & lines" engaged or disengaged I still am getting the bottom of the popup cell red triangle explanations cutoff

*is there any way to just get a word document with the LONGER popups , even if the cell isn't referenced ??

or some way to cut&paste them myself I prefer to not be forced to use a popup to digest the instructions if possible.

I might do it myself but would be tedious, and there is no way to cut&paste the popups, though said that, there may be a finite number of long and hence cut off popups , but I'm needing all the help I can get from these popups ............

PS: is it possible to click in the formula bar and have it highlight which cells are being used to make various calculations ? , I'm not an excel expert and don't know what =HLOOKUP($P$192,$E$351:$BE$371,4,TRUE) would mean for example

PPS: in 8.Income Taxes just to the right of the graphs, my "Federal Tax income" says 0 , ( P206 ), perhaps that is because P195 less earnings adjustment says (33,300) again the last line of the popup is cutoff, but seems to say the $33k is coming from the ?simple tax calculator ?

I am using 30 years prediction from age 54 to 84 per the graph what look like age 59-62 shows no federal income tax, then again age 67 to 71 , might that be correct ? I guess I'll go look at the 'details' but appreciate any feedback

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 04, 2019 5:49 pm

stvyreb wrote:
Tue Jun 04, 2019 2:32 pm
*is there any way to just get a word document with the LONGER popups , even if the cell isn't referenced ??

Not at this time but I will consider it for 2020.

or some way to cut&paste them myself I prefer to not be forced to use a popup to digest the instructions if possible.

The Zoom setting in the development model using Excel 2013 is set at 75%. A lower page zoom setting will result in the cell comments not being fully displayed. Check that setting (View/Zoom) and see if changing it helps.

PS: is it possible to click in the formula bar and have it highlight which cells are being used to make various calculations ? , I'm not an excel expert and don't know what =HLOOKUP($P$192,$E$351:$BE$371,4,TRUE) would mean for example

Users that want to study the calculations are on their own.

PPS: in 8.Income Taxes just to the right of the graphs, my "Federal Tax income" says 0 , ( P206 ), perhaps that is because P195 less earnings adjustment says (33,300) again the last line of the popup is cutoff, but seems to say the $33k is coming from the ?simple tax calculator ?

The Simple Tax Calculator is a model "Calculator", meaning its results are for information only and are not used in the model. P206 is the Federal Taxable Income for the year in the model entered above in P102.

I am using 30 years prediction from age 54 to 84 per the graph what look like age 59-62 shows no federal income tax, then again age 67 to 71 , might that be correct ? I guess I'll go look at the 'details' but appreciate any feedback

Look at the Summary, Results and Details pages to discover why you have no income taxes in those years.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jun 04, 2019 6:28 pm

*is there any way to just get a word document with the LONGER popups , even if the cell isn't referenced ??

Not at this time but I will consider it for 2020.

.......seems like it may help to have the ability to capture the instructions in order to reference them if one has questions IMO

or some way to cut&paste them myself I prefer to not be forced to use a popup to digest the instructions if possible.

The Zoom setting in the development model using Excel 2013 is set at 75%. A lower page zoom setting will result in the cell comments not being fully displayed. Check that setting (View/Zoom) and see if changing it helps.

............ah ok, that fixes some of them but, for something like cell P188 I have to reduce the scale to 45% and even on a 27" monitor it is not readable FYI , this is MSO 2019

..........ya the more I ask and exam, the more I start to understand, however I really need to be able to cut and paste the popups to be able to process ...... and to be able to see the full popup

......in the case of the fed tax being zero , there is something called "less earnings adjustment" ..........in my case this number is a large negative number which seems to be why my Federal Tax is listed as zero ........ popup says 'it is a factor from the income tax section, maybe its some built-in factor ? or maybe it is large because there is something in column E I've left blank , no idea ......... but my fed tax for 2019 won't be zero :)

..so what should I look at ? the results tab-> income taxes for MAGI says $21,900 for 2020 but yet it says Fed effective rate 0% ; Details tab again for next 7 years has this large "less other non-taxable earnings in taxable" number .......
Last edited by stvyreb on Tue Jun 04, 2019 6:40 pm, edited 2 times in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 04, 2019 6:36 pm

The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
Last edited by BigFoot48 on Tue Jun 04, 2019 6:42 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Admiral
Posts: 2047
Joined: Mon Oct 27, 2014 12:35 pm

Re: Retiree Portfolio Model

Post by Admiral » Tue Jun 04, 2019 6:38 pm

I use RPM on Excel for O365 ( v. 16.25) on a Mac. I have no issues with seeing popups. They are not "cut off." Sometimes one has to scroll a bit, that's it.

On zoom levels under 100, I see some odd cursor behavior, but that's it.

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jun 04, 2019 6:42 pm

BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
thanks but may I ask where I "supplied" it ?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 04, 2019 6:46 pm

stvyreb wrote:
Tue Jun 04, 2019 6:42 pm
BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
thanks but may I ask where I "supplied" it ?
In E205 and E206. These amounts reduce the credit which represents how much of taxable account growth for the year was NOT distributed as taxable earnings.
Last edited by BigFoot48 on Tue Jun 04, 2019 6:57 pm, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jun 04, 2019 6:48 pm

BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
Image

ncdad1
Posts: 4
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Tue Jun 04, 2019 8:07 pm

I started with 19.1 and truly love your spreadsheet. It is useful and elegant.
My question is how do you handle the migration to a new version? I have invested a lot of time in data input and I was wondering what happens in the future when you create a new version?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 04, 2019 8:17 pm

ncdad1 wrote:
Tue Jun 04, 2019 8:07 pm
I started with 19.1 and truly love your spreadsheet. It is useful and elegant.
My question is how do you handle the migration to a new version? I have invested a lot of time in data input and I was wondering what happens in the future when you create a new version?
The original way was to load both the old and new and use the View Side by Side to copy the settings. Then smart BH mjf55 created a macro to do it. The Clear and Load macros are at the top of the Setup page.
Last edited by BigFoot48 on Tue Jun 04, 2019 8:27 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

ncdad1
Posts: 4
Joined: Mon Apr 22, 2019 12:50 pm

Re: Retiree Portfolio Model

Post by ncdad1 » Tue Jun 04, 2019 8:27 pm

Thank you. So, now I will look forward to new versions.

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jun 05, 2019 1:08 pm

stvyreb wrote:
Tue Jun 04, 2019 6:48 pm
BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
Image
60% zoom, "show all comments" MSO 2019 on Win10 (only version available for those who buy MSO now afaik ), and many comments cut off ....... kind of important IMO

one workaround I can think of then, is to try to load other RPMs or formats in G. Sheets or libreoffice.

as usual otherwise mucho gracias

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jun 05, 2019 1:38 pm

BigFoot48 wrote:
Tue Jun 04, 2019 6:46 pm
stvyreb wrote:
Tue Jun 04, 2019 6:42 pm
BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
thanks but may I ask where I "supplied" it ?
In E205 and E206. These amounts reduce the credit which represents how much of taxable account growth for the year was NOT distributed as taxable earnings.
so if one is carrying over capital losses, say $3000/year do they just subtract that from E205 "net growth"

and how do I account for Inherited IRA RMDs in Section 8. or should I be ?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jun 05, 2019 2:25 pm

stvyreb wrote:
Wed Jun 05, 2019 1:38 pm
so if one is carrying over capital losses, say $3000/year do they just subtract that from E205 "net growth"

and how do I account for Inherited IRA RMDs in Section 8. or should I be ?
Capital Losses are not provided for in the model. Inherited IRAs are in Section 7.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jun 05, 2019 3:24 pm

BigFoot48 wrote:
Wed Jun 05, 2019 2:25 pm
stvyreb wrote:
Wed Jun 05, 2019 1:38 pm
so if one is carrying over capital losses, say $3000/year do they just subtract that from E205 "net growth"

and how do I account for Inherited IRA RMDs in Section 8. or should I be ?
Capital Losses are not provided for in the model. Inherited IRAs are in Section 7.
so the Inherited IRA RMD income I receive annually IS accounted for in the tax model/RPM 19.1, despite it not being in Section 8. ?

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jun 05, 2019 3:40 pm

stvyreb wrote:
Wed Jun 05, 2019 3:24 pm
BigFoot48 wrote:
Wed Jun 05, 2019 2:25 pm
stvyreb wrote:
Wed Jun 05, 2019 1:38 pm
so if one is carrying over capital losses, say $3000/year do they just subtract that from E205 "net growth"

and how do I account for Inherited IRA RMDs in Section 8. or should I be ?
Capital Losses are not provided for in the model. Inherited IRAs are in Section 7.
so the Inherited IRA RMD income I receive annually IS accounted for in the tax model/RPM 19.1, despite it not being in Section 8. ?
If the required inherited IRA data is entered in Section 1 (ages and owner) and Section 7 (not 8) (amount and inherit year) the IRA amount and RMD will be in the model and taxable income.
Last edited by BigFoot48 on Wed Jun 05, 2019 4:15 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jun 05, 2019 4:12 pm

Did you mean Section 7 E176 'age when event occurs' not section 8 ?

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Wed Jun 05, 2019 4:13 pm

stvyreb wrote:
Wed Jun 05, 2019 1:08 pm
stvyreb wrote:
Tue Jun 04, 2019 6:48 pm
BigFoot48 wrote:
Tue Jun 04, 2019 6:36 pm
The Taxable Account Earnings Adjustment is in "8. Income Taxes" and explained there, assuming you can read the popups. It is a user-supplied adjustment to taxable account earnings.

You might also try the Show All Comments setting in Review and see if that helps.
Image
60% zoom, "show all comments" MSO 2019 on Win10 (only version available for those who buy MSO now afaik ), and many comments cut off ....... kind of important IMO

one workaround I can think of then, is to try to load other RPMs or formats in G. Sheets or libreoffice.

as usual otherwise mucho gracias
please disregard this, Sandra kindly sent me a PM how to resize comments myself ...

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jun 05, 2019 4:14 pm

stvyreb wrote:
Wed Jun 05, 2019 4:12 pm
Did you mean Section 7 E176 'age when event occurs' not section 8 ?
Yes, 7 Special Events.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

GrowthSeeker
Posts: 505
Joined: Tue May 15, 2018 10:14 pm

Re: Retiree Portfolio Model

Post by GrowthSeeker » Tue Jun 11, 2019 6:58 am

Supreme thanks to BigFoot48 for this awesome tool.
As a 66 yo somewhat recent retiree, one of my goals is to optimize Roth conversions. I've made several spreadsheets to get a handle on this. A year ago I tried using RPM but found the learning curve too steep for my level of patience at the time; and now I'm going for Round 2.
At first I had a million error messages, and I was able to answer some from the wiki and reading and searching within this topic, now with 731 posts. I think I have no errors showing, and I am able to play with different Roth conversion scenarios and see the effect on final balances.

My first question: is there an organized help file, or an optimal way to troubleshoot problems short of reading all 731 posts? If there is, a link to it in the first post would be helpful (maybe it is there and I just missed it).

Now as to what has me perplexed at the moment:
1) none of the buttons, which I assume should run macros, are working for me. If I click one, I am just selecting whatever cell is behind the button.
(note that if I press Alt-F11 and try to look at the RPM macros, all the entries seem to be blank as if I didn't download any macros).
2) almost all of the graphs just show the first 4 years worth of data instead of, say, 30 years. Exceptions are the 'SS Benefits and Expenses', 'Sources of Earnings' and 'Earnings and Expenses' graphs which show all 30 years. However, the "details" and "base" tabs show that all years up to age 95 have data including ending portfolio values which appear to be as expected. It's just that the graphs show only the first four bars of data.

Thanks.
LibreOffice Calc Version: 6.2.3.2 (x64) [Macro Security set to low]
Retiree Portfolio Model v18.1 (I realize this is not the most recent version)
Just because you're paranoid doesn't mean they're NOT out to get you.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 11, 2019 7:47 am

GrowthSeeker wrote:
Tue Jun 11, 2019 6:58 am
My first question: is there an organized help file, or an optimal way to troubleshoot problems short of reading all 731 posts? If there is, a link to it in the first post would be helpful (maybe it is there and I just missed it).

Now as to what has me perplexed at the moment:
1) none of the buttons, which I assume should run macros, are working for me. If I click one, I am just selecting whatever cell is behind the button.
(note that if I press Alt-F11 and try to look at the RPM macros, all the entries seem to be blank as if I didn't download any macros).
2) almost all of the graphs just show the first 4 years worth of data instead of, say, 30 years. Exceptions are the 'SS Benefits and Expenses', 'Sources of Earnings' and 'Earnings and Expenses' graphs which show all 30 years. However, the "details" and "base" tabs show that all years up to age 95 have data including ending portfolio values which appear to be as expected. It's just that the graphs show only the first four bars of data.
I'm sorry you are having these problems with the model. Unfortunately, LibreOffice Calc is not a good alternative of Excel, especially for new users. The cell comments, which contain all of the instructions for using the model, are not visible for the most part in Libre. Without those it will be difficult to effectively use the model.

Macros are also not working in Libre. They help in automating certain things but are not essential in using the model.

There is no help file, as the cell comments fulfill that function. I have been looking at techniques for printing all the cell comments and plan to include that in a future release, but again it may not be available in Libre. I don't recommend reading all the posts in this thread, although many users have been helped by other users over the years and versions.

When loading 19.1 into Libre, all the graphs are working with the example data, so I suspect something in your data may be preventing Libre from showing all the years.

You might want to try Excel Online or WPS Kingsoft Spreadsheet instead of Libre, but none are as good as Excel. Thanks for your comments.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

GrowthSeeker
Posts: 505
Joined: Tue May 15, 2018 10:14 pm

Re: Retiree Portfolio Model

Post by GrowthSeeker » Tue Jun 11, 2019 8:03 am

BigFoot48 wrote:
Tue Jun 11, 2019 7:47 am
GrowthSeeker wrote:
Tue Jun 11, 2019 6:58 am
My first question: is there an organized help file, or an optimal way to troubleshoot problems short of reading all 731 posts? If there is, a link to it in the first post would be helpful (maybe it is there and I just missed it).

Now as to what has me perplexed at the moment:
1) none of the buttons, which I assume should run macros, are working for me. If I click one, I am just selecting whatever cell is behind the button.
(note that if I press Alt-F11 and try to look at the RPM macros, all the entries seem to be blank as if I didn't download any macros).
2) almost all of the graphs just show the first 4 years worth of data instead of, say, 30 years. Exceptions are the 'SS Benefits and Expenses', 'Sources of Earnings' and 'Earnings and Expenses' graphs which show all 30 years. However, the "details" and "base" tabs show that all years up to age 95 have data including ending portfolio values which appear to be as expected. It's just that the graphs show only the first four bars of data.
I'm sorry you are having these problems with the model. Unfortunately, LibreOffice Calc is not a good alternative of Excel, especially for new users. The cell comments, which contain all of the instructions for using the model, are not visible for the most part in Libre. Without those it will be difficult to effectively use the model.

Macros are also not working in Libre. They help in automating certain things but are not essential in using the model.

There is no help file, as the cell comments fulfill that function. I have been looking at techniques for printing all the cell comments and plan to include that in a future release, but again it may not be available in Libre. I don't recommend reading all the posts in this thread, although many users have been helped by other users over the years and versions.

When loading 19.1 into Libre, all the graphs are working with the example data, so I suspect something in your data may be preventing Libre from showing all the years.

You might want to try Excel Online or WPS Kingsoft Spreadsheet instead of Libre, but none are as good as Excel. Thanks for your comments.
Thank you for your rapid response and comments.
Since I posted, and before I saw your response, I downloaded the latest version and: SUCCESS!
The light blue navigation buttons work, at least some of them do, such as "go to top"; and all the graphs show all 30 bars. I can see some comments, such as clicking in a field that requires y/n the "enter or select" dropdown works.
Just because you're paranoid doesn't mean they're NOT out to get you.

User avatar
Zephavest
Posts: 51
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Tue Jun 11, 2019 8:17 am

BigFoot48 wrote:
Tue Jun 11, 2019 7:47 am
You might want to try Excel Online or WPS Kingsoft Spreadsheet instead of Libre, but none are as good as Excel. Thanks for your comments.
rennale wrote:
Mon May 06, 2019 6:26 am
Buy a copy of Excel for Mac for $105.

https://www.amazon.com/dp/B07H4XBM1R
I checked the link above, current price is $129 for both Mac and Windows.

Excel is the first "Investment" you need to make to figure out your investments in RPM.
rennale wrote:
Mon May 06, 2019 6:26 am
Anything else will cause more grief than it's worth....

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 11, 2019 8:29 am

GrowthSeeker wrote:
Tue Jun 11, 2019 8:03 am
Thank you for your rapid response and comments.
Since I posted, and before I saw your response, I downloaded the latest version and: SUCCESS!
The light blue navigation buttons work, at least some of them do, such as "go to top"; and all the graphs show all 30 bars. I can see some comments, such as clicking in a field that requires y/n the "enter or select" dropdown works.
That's good news. I also notice after opening Libre again, after setting the Macro Security setting to low upon the first use, that macros are indeed working. However most cell comments are still not visible. I'll see if i can find any Libre Calc user who solved this issue.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

GrowthSeeker
Posts: 505
Joined: Tue May 15, 2018 10:14 pm

Re: Retiree Portfolio Model

Post by GrowthSeeker » Tue Jun 11, 2019 8:48 am

BigFoot48 wrote:
Tue Jun 11, 2019 8:29 am
GrowthSeeker wrote:
Tue Jun 11, 2019 8:03 am
Thank you for your rapid response and comments.
Since I posted, and before I saw your response, I downloaded the latest version and: SUCCESS!
The light blue navigation buttons work, at least some of them do, such as "go to top"; and all the graphs show all 30 bars. I can see some comments, such as clicking in a field that requires y/n the "enter or select" dropdown works.
That's good news. I also notice after opening Libre again, after setting the Macro Security setting to low upon the first use, that macros are indeed working. However most cell comments are still not visible. I'll see if i can find any Libre Calc user who solved this issue.
OK this is weird: I closed then reopened Libre. I can still see some comments, such as when I hover over cell A9 in Setup and I read about George and Martha. But now, the "go to top" and other navigation buttons no longer work for me, but before, they did.
Just because you're paranoid doesn't mean they're NOT out to get you.

stvyreb
Posts: 92
Joined: Sat Nov 05, 2016 8:57 pm

Re: Retiree Portfolio Model

Post by stvyreb » Tue Jun 11, 2019 1:41 pm

munemaker wrote:
Sun Feb 03, 2019 3:59 pm
munemaker wrote:
Sat Feb 02, 2019 11:46 am

1) If you are taking the savings bond interest as a lump when you redeem the bonds, do this: Enter the principle as Tax Exempt on the SETUP sheet. Then go to the DETAILS sheet, and enter the taxable interest payment on line 179 under whatever year(s) you plan for them to occur. The interest would still show up on your state return though...not sure how to prevent that without changing the state formula in the SS, which is what I would do.
Thinking about this a little more, you can prevent the savings bond interest income from showing up on your state income tax by entering the savings bond interest as a negative number on line 209 on the DETAILS page. That's more elegant than changing the formula as I had suggested earlier.

For the deferred EE, I Savings Bonds interest taxable at age 61-66 ( I am soon to be 54 )

Would it make sense to , then,
1) include both the Issue Price (principal) and (deferred) interest in E40 Taxable.
2) include only the Savings Bond Issue Price (principal) portion as G58 tax exempt , and the deferred interest as G59 MM/other
a) although, the I bond "interest" I believe may include inflation adjustments to the principal , though not parsed out from the overall interest


The Deferred Interest is not actually *new "income" for the purposes of Details Stub -> Row 181 ; seems to me it just a taxable event as there is no new assets

..........perhaps I *can use Details (#181) but then I should then *not include the interest portion anywhere in the
Setup stub-> 2. Porfolio Balances ?

II
Since this is not state taxable income whatever I enter on Details (181) , I should enter as a negative number on Details #211 ??

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Jun 11, 2019 5:05 pm

stvyreb wrote:
Tue Jun 11, 2019 1:41 pm
Since this is not state taxable income whatever I enter on Details (181) , I should enter as a negative number on Details #211 ??
This is the cell comment for "user input" in State Income Tax, line 211:
Enter yearly amounts to adjust the model's AGI calculation for factors not included in the model.
Positive +: increase AGI
Negative -: reduce AGI
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Bongleur
Posts: 2252
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Wed Jun 12, 2019 1:42 am

BigFoot48 wrote:
Tue Jun 11, 2019 7:47 am
There is no help file, as the cell comments fulfill that function. I have been looking at techniques for printing all the cell comments and plan to include that in a future release, but again it may not be available in Libre.
Is it possible to write a script that will look at every cell, and if the cell contains a comment, to copy the comment to a specified sheet, along with the comment cell ID in an adjacent column? So it would create a long listing having 2 columns, which could be printed out or looked at.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Wed Jun 12, 2019 7:25 am

Bongleur wrote:
Wed Jun 12, 2019 1:42 am

Is it possible to write a script that will look at every cell, and if the cell contains a comment, to copy the comment to a specified sheet, along with the comment cell ID in an adjacent column? So it would create a long listing having 2 columns, which could be printed out or looked at.
Possibly. Here is the easiest solution I've found. It creates a list of all comments found in the selected print area, such as the area of the Setup page containing input items. The output can be directed to a PDF file. The input items section can be deleted or skipped over, leaving a 32 page long list of all the comments.

1. In Excel go to the Page Layout tab and find the Page Setup section (just below the Print Area icon).
2. Click on the bottom-right expand arrow icon to get the full Page Setup window appear.
3. On the Page Setup window click on the Sheet tab, then click on the Comments down arrow and select the "At end of sheet" option from the drop-down list.
4. Click the Print button.
https://www.ablebits.com/office-addins- ... nts-excel/
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

GrowthSeeker
Posts: 505
Joined: Tue May 15, 2018 10:14 pm

Re: Retiree Portfolio Model

Post by GrowthSeeker » Wed Jun 12, 2019 10:45 am

BigFoot48 wrote:
Tue Jun 11, 2019 7:47 am
There is no help file, as the cell comments fulfill that function. I have been looking at techniques for printing all the cell comments and plan to include that in a future release, but again it may not be available in Libre.
At least in LibreOffice:
Or just: View | Comments and all the comment boxes appear for whatever tab you are viewing. This is then a toggle. Comment boxes can be moved out of the way by clicking and dragging. This has to be repeated for each tab. How you set this option is "remembered" if you save the file and reopen.
The Comments option is greyed out for me in Tabs: Summary, Results, Details, and Base (I assume they have no comments).
Just because you're paranoid doesn't mean they're NOT out to get you.

tman9999
Posts: 31
Joined: Tue May 20, 2008 1:06 pm

Re: Retiree Portfolio Model

Post by tman9999 » Thu Jun 13, 2019 3:37 pm

Hello there - just found this model after playing around with I-ORP for awhile, and I'm really liking it. Thank you!

What attracted me to RPM in the first place was the Roth Conversion benefit estimator. My wife and I both have large tIRA accounts, and small RothIRA accounts; and we're about a year away from retiring.

I-ORP gave us an aggressive Roth conversion schedule aimed at lifetime tax minimization. When I tried to enter those yearly conversions into RPM I got red A's next to several years, indicating I had exceeded the available Roth balance. Then I realized RPM is only looking at my ROTH balance, not both of our ROTHs. I've entered current balances for both of us in tIRA and ROTH IRA, but I don't see anywhere to tell RPM to convert both of ours to ROTHs.

Is this an available option? Or should I just combine both our tIRA balances into tIRA 1 under section 2?

Thanks for any info.

(apologies if this question has already been addressed in this thread - I did a search on "conversion" and got 108 hits!).

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Jun 13, 2019 4:45 pm

Good question. You need to combine the IRAs into IRA 1, which is the only one used for conversions.

The account balances entry titles have cell notes that include:
IRA 1: Note: All Roth conversions come out of this IRA.
IRA 2: Note: No Roth conversions are made from this IRA.
Last edited by BigFoot48 on Thu Jun 13, 2019 10:29 pm, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

tman9999
Posts: 31
Joined: Tue May 20, 2008 1:06 pm

Re: Retiree Portfolio Model

Post by tman9999 » Thu Jun 13, 2019 9:46 pm

Thanks!

Post Reply