Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
DebiT
Posts: 994
Joined: Sat Dec 28, 2013 12:45 pm

Re: Retiree Portfolio Model

Post by DebiT »

BigFoot48 wrote: Fri Jan 08, 2021 3:12 pm 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.
Thank you for your prompt reply. I'll check and make sure the additional benefits method is the same.

I have just now worked with it enough to find the Roth conversion section, the filling the tax brackets next to that , etc. This is amazing. It is going to be so helpful to me in my situation of so much in retirement accounts, and now being a single tax payer.

Thank you for providing this. It is a literal God send.
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.
Pamina2
Posts: 1
Joined: Sat Jan 09, 2021 4:45 pm

Re: Retiree Portfolio Model

Post by Pamina2 »

Thank you BigFoot48 for such a great tool.

I have been playing around with it for a few weeks now in order to replace my old Excel worksheet.

The tool is very easy to follow, however I am having trouble with the Table II override. I am 18 years younger than my husband. I have him as the owner and me as the spouse in the model. When I do the override and add the factors per the instructions in the Tax Tables tab, the RMD calculation is picking up my age instead of his. The IRA Owner Age column is showing my age with the factor that corresponds to this age.

The Spouse Age Column starts with Spouse age:55 with Table II factor: 30.7. When the table shows my husband's age: 73 it has a factor of 15.2 which is the one the model is using. The IRA Owner Column is showing the spouse age. I tried to replicate it below.

Why isn't the model showing my husband's age next to the 30.7 factor? It is not clear to me what I am doing wrong.

Thanks so much for your help!!


Owner Age :73 Spouse Age:55 Table II Factor: 30.7 IRA Owner Age:55

Owner Age :91 Spouse Age:73 Table II Factor: 15.2 IRA Owner Age:73
Last edited by Pamina2 on Sat Jan 09, 2021 5:37 pm, edited 1 time in total.
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

I think I see an issue with the way RMDs are calculated on a stretch inherited IRA in v 21.0. On row 521 of the Detail sheet, F521 correctly looks up the 1st year RMD, but the subsequent years are not following the table, they just have 1 subtracted from the previous year. So in my case, the 1st year has 24.4 (correct), but the second year is 23.4, then 22.4, 21.4, etc., instead of following Table 1.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Thu Jan 14, 2021 8:49 pm I think I see an issue with the way RMDs are calculated on a stretch inherited IRA in v 21.0. On row 521 of the Detail sheet, F521 correctly looks up the 1st year RMD, but the subsequent years are not following the table, they just have 1 subtracted from the previous year. So in my case, the 1st year has 24.4 (correct), but the second year is 23.4, then 22.4, 21.4, etc., instead of following Table 1.
I believe the model is calculating it correctly:
As a non-spouse beneficiary, you must directly roll over the inherited assets to an Inherited IRA in your own name and use your own age and the IRS Single Life Expectancy Table for calculating the first year RMD. For each year after, you would subtract one year from the initial life expectancy factor.
https://www.fidelity.com/building-savin ... ed-ira-rmd
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Pamina2 wrote: Sat Jan 09, 2021 5:33 pm The Spouse Age Column starts with Spouse age:55 with Table II factor: 30.7. When the table shows my husband's age: 73 it has a factor of 15.2 which is the one the model is using. The IRA Owner Column is showing the spouse age. I tried to replicate it below.

Why isn't the model showing my husband's age next to the 30.7 factor? It is not clear to me what I am doing wrong.
Glad you're finding the model useful. I will investigate this issue, but likely a model issue and not something you have done.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

Hi,
I'm trying to determine the optimum amount which I could make each year for Roth conversion. In the RPM, with a given income (AGI/Roth Conversion), I would like to calculate the ACA Premium Tax Credits (PTC) for family of 4 with 2 dependents. Are there formulas which allow me to do that? I'm aware of online calculators such as https://www.kff.org/interactive/subsidy ... ator-2020/ but they require manual entries each time I change the the AGI/Roth Conversion amount.

If such formulas exist I could use them to automatically adjust my expense in the Details tab.
Thanks.
GG
User avatar
FiveK
Posts: 15693
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Fri Jan 22, 2021 8:37 pm Hi,
I'm trying to determine the optimum amount which I could make each year for Roth conversion. In the RPM, with a given income (AGI/Roth Conversion), I would like to calculate the ACA Premium Tax Credits (PTC) for family of 4 with 2 dependents. Are there formulas which allow me to do that? I'm aware of online calculators such as https://www.kff.org/interactive/subsidy ... ator-2020/ but they require manual entries each time I change the the AGI/Roth Conversion amount.
If you can settle for a single year's answer, using "last year's" commercial tax software might be best for something like that. It won't be exact but should get you in the ballpark.

Another option, perhaps more suited for "optimization" (with all the caveats about how things can change during a year), is the personal finance toolbox spreadsheet. See the Roth IRA conversion wiki for some examples.

Or BigFoot48 could include all the nuances of the tax code in each year's tax calculation column. And yes, I realize that's likely an outlandish suggestion. ;) The multiple year "pretty good" tax calculation done in RPM is pretty darn good indeed.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Fri Jan 22, 2021 8:37 pm Hi,
I'm trying to determine the optimum amount which I could make each year for Roth conversion. In the RPM, with a given income (AGI/Roth Conversion), I would like to calculate the ACA Premium Tax Credits (PTC) for family of 4 with 2 dependents. Are there formulas which allow me to do that? I'm aware of online calculators such as https://www.kff.org/interactive/subsidy ... ator-2020/ but they require manual entries each time I change the the AGI/Roth Conversion amount.

If such formulas exist I could use them to automatically adjust my expense in the Details tab.
Thanks.
GG
There's no formula within RPM that can do that or be added to do it, so following FiveK's suggestions might provide an estimate that could be manually entered in the Federal AGI manual yearly adjustment field on the Detail page. But precisely adding such a factor when all the other calculations are based on hundreds of broad estimates over many years might not be worth the effort.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

FiveK wrote: Fri Jan 22, 2021 9:41 pm
Another option, perhaps more suited for "optimization" (with all the caveats about how things can change during a year), is the personal finance toolbox spreadsheet. See the Roth IRA conversion wiki for some examples.

Or BigFoot48 could include all the nuances of the tax code in each year's tax calculation column. And yes, I realize that's likely an outlandish suggestion. ;) The multiple year "pretty good" tax calculation done in RPM is pretty darn good indeed.
BigFoot48 wrote: Sat Jan 23, 2021 6:31 am There's no formula within RPM that can do that or be added to do it, so following FiveK's suggestions might provide an estimate that could be manually entered in the Federal AGI manual yearly adjustment field on the Detail page. But precisely adding such a factor when all the other calculations are based on hundreds of broad estimates over many years might not be worth the effort.
Hi BigFoot and FiveK,
Thanks for your suggestions. I really appreciate your support for this amazing tool.
I would like to point out that I'm NOT asking for additional features to the RPM, which is just fine the way it is. I just wonder if any of you know how to calculate the PTC-there must be some formulas behind those online calculators. If I know those formulas, I could create a my own tab in the RPM tool and generate the PTC amounts based on my AGI and then subtract that from my expense. Something like this:

AGI (Details tab) -> PTC Formulae (my own tab) -> Expense Adjustments (Details tab)

Another option would be, go to one of those online ACA calculators, generate a table of income vs. PTC, plot out the table, and do a linear fits to obtain empirical (not sure if this is the right word) equations.
Thanks.
User avatar
FiveK
Posts: 15693
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Sat Jan 23, 2021 9:44 amI just wonder if any of you know how to calculate the PTC-there must be some formulas behind those online calculators. If I know those formulas, I could create a my own tab in the RPM tool and generate the PTC amounts based on my AGI and then subtract that from my expense. Something like this:

AGI (Details tab) -> PTC Formulae (my own tab) -> Expense Adjustments (Details tab)

Another option would be, go to one of those online ACA calculators, generate a table of income vs. PTC, plot out the table, and do a linear fits to obtain empirical (not sure if this is the right word) equations.
Thanks.
The calculations for the PTC are in the toolbox spreadsheet. Although, it is set up to handle the various family sizes, location (48 contiguous vs. Hawaii or Alaska), different SLCSP numbers, etc., that come into play when one files Form 8962.

You might either start with Form 8962 and build your own from scratch, or start with the generic toolbox equations and pare them down to your situation, etc. It would take a little time but seems doable.
sandramjet
Posts: 441
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

Just wondering if the Monte Carlo variant of RPM has been updated to the current version?
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

sandramjet wrote: Mon Jan 25, 2021 11:23 pm Just wondering if the Monte Carlo variant of RPM has been updated to the current version?
Thanks for asking.

I updated it for my own use but was waiting for some indication that others were still interested in it. I didn't know if anyone was using it or not, or if they thought the results were credible or not (I do).

I updated it before this change was made:
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.
Bear with me while I make a new version that includes the most recent update, and then I will post a link to it.

-B
ByThePond
Posts: 348
Joined: Thu Dec 31, 2015 10:21 am

RPM and Roth conversion question

Post by ByThePond »

[Post moved into here, see below. --admin LadyGeek]

In the RPM spreadsheet, when one models Roth conversions,the taxes are automatically assumed to be paid out of a taxable account, which is appropriately debited.

Does anyone know what happens when one's taxable account is depleted (or if one never existed)?

The spreadsheet shows a warning that an account has a negative balance, and that balance is shown in the conversion worksheet area, but I can't figure out if the taxes are assumed to be coming out of another source, or if the negative acct balance should be taken out of the final, bottom line overall portfolio balance.

In other words, does the portfolio end balance in Results Summary already reflect that negative account balance? It seems so, based on the colored charts in Results.

Thanks.
sandramjet
Posts: 441
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

Barsoom wrote: Tue Jan 26, 2021 2:01 am I updated it for my own use but was waiting for some indication that others were still interested in it. I didn't know if anyone was using it or not, or if they thought the results were credible or not (I do).
Yes, I am still interested and trying it out... just haven't had as much time as I wish to play with it.
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

All:

I have updated the Retiree Portfolio Model Monte Carlo variant for 2021. You can download it at the link below.

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

Changes in this version:
  1. Updated to the 2021 version of the Retiree Portfolio Model.
  2. Added Monte Carlo of inflation to the tax tables.
  3. Changed the function of the scenario scroll buttons to advance by probability instead of scenario sequence number.
  4. Added comments to the Monte Carlo tab to explain how to directly select a scenario for analysis.
As usual, please direct questions to me.

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

Re: Retiree Portfolio Model

Post by LadyGeek »

ByThePond - I moved your question in the the RPM support thread. This thread is in the Personal Finance (Not Investing) forum (retirement planning).

(Thanks to the member who reported the post.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: RPM and Roth conversion question

Post by BigFoot48 »

ByThePond wrote: Tue Jan 26, 2021 7:16 am [Post moved into here, see below. --admin LadyGeek]

In the RPM spreadsheet, when one models Roth conversions,the taxes are automatically assumed to be paid out of a taxable account, which is appropriately debited.

Does anyone know what happens when one's taxable account is depleted (or if one never existed)?

The spreadsheet shows a warning that an account has a negative balance, and that balance is shown in the conversion worksheet area, but I can't figure out if the taxes are assumed to be coming out of another source, or if the negative acct balance should be taken out of the final, bottom line overall portfolio balance.

In other words, does the portfolio end balance in Results Summary already reflect that negative account balance? It seems so, based on the colored charts in Results. And thus a Taxable account must exist, even if it's a checking account in reality.

Thanks.
All expenses are paid out of the Taxable account, and while the model allows a negative balance, for accurate modeling the Taxable account should always have a positive balance by increasing income and/or decreasing expenses.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
ByThePond
Posts: 348
Joined: Thu Dec 31, 2015 10:21 am

Re: RPM and Roth conversion question

Post by ByThePond »

BigFoot48 wrote: Tue Jan 26, 2021 4:24 pm
All expenses are paid out of the Taxable account, and while the model allows a negative balance, for accurate modeling the Taxable account should always have a positive balance by increasing income and/or decreasing expenses.
Thank You.
I imagine this could be made more accurate by increasing income as a series of withdrawals from the Roth account, which probably reflects my reality anyway.
My concern was to verify that the negative balance was already accounted for in the Results Summary. I now see in the Portfolio Cash Flow Diagram that this is so.
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

deleted
Last edited by goGators on Sun Jan 31, 2021 1:59 pm, edited 1 time in total.
User avatar
FiveK
Posts: 15693
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

Appears the yellow line is the nominal bracket rate. The actual marginal tax rate is likely different.

The marginal rate effect of premium tax credits can look very different, depending on whether one has received credits during the year or not.
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

FiveK wrote: Wed Jan 27, 2021 12:24 pm Appears the yellow line is the nominal bracket rate. The actual marginal tax rate is likely different.
Hmm, I used the RPM (v21.0) formula in Details!F204 to generate the yellow line. The row was labeled "Marginal rate". Is "nominal bracket rate" the same as "Effective Tax Rate"?
The marginal rate effect of premium tax credits can look very different, depending on whether one has received credits during the year or not.
I'm totally lost....Could you give an example or a link? Thank you.
User avatar
FiveK
Posts: 15693
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Wed Jan 27, 2021 8:38 pm
FiveK wrote: Wed Jan 27, 2021 12:24 pm Appears the yellow line is the nominal bracket rate. The actual marginal tax rate is likely different.
Hmm, I used the RPM (v21.0) formula in Details!F204 to generate the yellow line. The row was labeled "Marginal rate". Is "nominal bracket rate" the same as "Effective Tax Rate"?
Row 204 does a search over cells D195:D201 to find the highest tax bracket with non-zero income.

Row 203 is one version of effective tax rate: (federal income tax) divided by (taxable income). OK, not exactly taxable income - but for Roth conversion decisions one should ignore effective tax rate anyway so let's not belabor this.

See the wiki article linked above for the distinction between marginal tax rate vs. tax bracket. Taxation of Social Security benefits - Bogleheads also has some examples.
The marginal rate effect of premium tax credits can look very different, depending on whether one has received credits during the year or not.
I'm totally lost....Could you give an example or a link? Thank you.
Enter your filing status, age(s), other income, SLCSP annual cost, and your actual plan monthly premiums in the toolbox spreadsheet. Then see the difference in the toolbox marginal rate chart between entering an Advance Premium Tax Credit in cell B115 (use a negative number) vs. leaving that cell blank.

If you don't see any difference, then...?

Does that help? If so, great. If not, what is the least understandable?
MileKing
Posts: 74
Joined: Thu Mar 28, 2019 12:14 pm
Location: Reno, NV

Re: Retiree Portfolio Model

Post by MileKing »

goGators wrote: Wed Jan 27, 2021 7:59 am
FiveK wrote: Sat Jan 23, 2021 11:25 am The calculations for the PTC are in the toolbox spreadsheet. Although, it is set up to handle the various family sizes, location (48 contiguous vs. Hawaii or Alaska), different SLCSP numbers, etc., that come into play when one files Form 8962.

You might either start with Form 8962 and build your own from scratch, or start with the generic toolbox equations and pare them down to your situation, etc. It would take a little time but seems doable.
Using the toolbox + Form 8962 + the RPM as suggested by FiveK, I managed to generate this plot inside the RPM for my situation (MJF + 2 kids + un-subsidized premium ~ $16K)
Image
Thanks for the plot. Trying to understand it, but not quite sure how to interpret or what conclusions to draw. Is the cost % the tax rate? The cost line is always above the ACA line. Does that mean (in your situation) it never pays to do a Roth conversion because the ACA subsidy always exceeds the benefit of the conversion? Thanks for any guidance you can offer in understanding the plot.
goGators
Posts: 97
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

MileKing wrote: Sun Jan 31, 2021 11:09 am Thanks for the plot. Trying to understand it, but not quite sure how to interpret or what conclusions to draw. Is the cost % the tax rate? The cost line is always above the ACA line. Does that mean (in your situation) it never pays to do a Roth conversion because the ACA subsidy always exceeds the benefit of the conversion? Thanks for any guidance you can offer in understanding the plot.
Thanks for your interest in the plot. Unfortunately, the numbers used to generate this plot are incorrect-I will try to delete that post.
You should use the personal finance toolbox spreadsheet as suggested by FiveK to get better results. S/he also pointed out to use a blog by The Finance Buff (aka Harry Sit, aka tfb on this forum) Tax Calculator With ACA Health Insurance Subsidy as an example on how to use that tool.

To avoid hijacking this thread, please PM me if you still have questions.
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

I have a usage question/observation to ask/make.

I was reading a thread about sequence of return risk regarding paying off one's mortgage and decided to test it with my Monte Carlo variant. While reading the thread, a point was made that mortgage payments are contractually fixed throughout the mortgage (unless it's an ARM), while living expenses rise with inflation.

This got me thinking about the proper use of the Retiree Portfolio Model to account for this. This isn't to say that the model is wrong, but it is misleading in the examples that are provided.

In the 5. Expenses section of the Setup tab, the annual Living Expenses and Inflation rate are entered. To the right of this is the "calculator: list of expenses" which is not used to enter the input, but is handy to calculate a total of expenses to be transferred manually to the Living Expenses cell.

You will notice that the first entry in the list of expenses calculator is "Mortgage, rent." This implies that a mortgage should be included in the Living Expenses cell, but this would be incorrect because fixed mortgages (the most likely) don't grow with inflation.

To address this, I have to enter the fixed mortgage in the "User expense adjustment" in the Details tab, because these fields are not naturally inflated. In my case, this resulted in an additional $230,000 in my final portfolio value ($263,000 in the Monte Carlo analysis).

So, the question is this:
  1. Should the example be changed to take "mortgage" out of the "list of expenses" calculator with an explanation of how to enter fixed mortgages?
  2. Should the Expenses section be changed to handle mortgages separately so they don't inflate with the rest of the expenses?
  3. Do nothing?
Thoughts?

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

Re: Retiree Portfolio Model

Post by BigFoot48 »

Barsoom wrote: Fri Feb 05, 2021 4:50 pm
  1. Should the example be changed to take "mortgage" out of the "list of expenses" calculator with an explanation of how to enter fixed mortgages?
  2. Should the Expenses section be changed to handle mortgages separately so they don't inflate with the rest of the expenses?
  3. Do nothing?
Mortgagse should perhaps be taken from the calculator list, or your method of using the optional expense line be explained for users. Since mortgages for retirees may have an ending data the use of the optional line would be a more precise way of including the expense. Or, include it in the expense number but reduce to escalation factor to effectively exclude it from inflation.

I suspect most retirees don't have mortgages so its unique fixed and ending nature was never considered in modeling the expense forecast, but I will consider that as a future improvement.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Iconicus
Posts: 60
Joined: Thu Oct 29, 2020 8:19 pm

Re: Retiree Portfolio Model

Post by Iconicus »

I find that when I run this excellent model (thank you very much for it) with my particulars, I have Roth1 withdrawals from what should be empty Roth1 account. This can be demonstrated/recreated with the least amount of changes to a virgin copy of the spreadsheet by doing the following:

1. make Roth1 contribution = 1,000 starting at 62 and ending at 62 (for 1 year on year 1)
2. make Roth1 withdrawl1 = 30,000 starting at 63 (no end date)
3. make Roth1 withdrawl2 = 0
4. show boarders & lines and hidden rows of the Details sheet
5. look at row 121, Roth1 withdrawals.

The Roth1 withdrawals are 30k for two years, 26,300 in year 3 instead of 29,500 (!), then 900, and then 100 until the end. Starting in year 6 the Roth1 balance is 1,100 and never decreases.

Where is the $100 coming from each year starting at year 6?
How do I get the balance of Roth1 to go to zero?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Iconicus wrote: Sun Feb 07, 2021 2:57 pm Where is the $100 coming from each year starting at year 6?
How do I get the balance of Roth1 to go to zero?
Nice bug find! Use of withdrawals with no end year should result in an automatic end when the account reaches zero. In this case it wasn't going to zero because the section of the model that calculates automatic withdrawals, if that option is selected, is also used to come up with the remaining balance each year. But the earnings for the year are added in even if the ending balance before earnings is zero, such as your example created. So it was an endless rounding off of earnings of $100 added to be the beginning balance which created another $100 in earnings and so on.

I've got the fix and will upload it by tomorrow, if I can. Thanks for the report.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

An update to RPM, 21.1, is now available. This fixes a problem with existing Roth IRAs withdrawals where an ending year is not used. Users not using this feature do not need to upgrade. Download here: https://www.dropbox.com/s/s4i85jch4nc4d ... .xlsm?dl=0
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

Matching maintenance release with the Monte Carlo variant.

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

-B
nereocystis
Posts: 4
Joined: Thu Apr 19, 2018 8:29 pm

Re: Retiree Portfolio Model

Post by nereocystis »

Has anyone had success using Monte Carlo with LibreOffice?

I am using version 6.1.5.2

The blue and yellow A23 and A1 in the Monte Carlo tab both show "#N/A"
B26 shows 1, but B27 onward also show "#N/A"

When I set "Use Monte Carlo growth rates" to "y", then I get lots of errors, with "E:522" in Setup, E13
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

nereocystis wrote: Sat Feb 20, 2021 3:08 pm Has anyone had success using Monte Carlo with LibreOffice?

I am using version 6.1.5.2

The blue and yellow A23 and A1 in the Monte Carlo tab both show "#N/A"
B26 shows 1, but B27 onward also show "#N/A"

When I set "Use Monte Carlo growth rates" to "y", then I get lots of errors, with "E:522" in Setup, E13
I don't know about the Monte Carlo in LibraOffice since I use Excel.

The #N/A is because no prior Monte Carlo was run. It should fill with values after a successful run.

-B
nereocystis
Posts: 4
Joined: Thu Apr 19, 2018 8:29 pm

Re: Retiree Portfolio Model

Post by nereocystis »

Barsoom wrote: Sun Feb 21, 2021 1:39 pm
nereocystis wrote: Sat Feb 20, 2021 3:08 pm Has anyone had success using Monte Carlo with LibreOffice?

I am using version 6.1.5.2

The blue and yellow A23 and A1 in the Monte Carlo tab both show "#N/A"
B26 shows 1, but B27 onward also show "#N/A"

When I set "Use Monte Carlo growth rates" to "y", then I get lots of errors, with "E:522" in Setup, E13
I don't know about the Monte Carlo in LibraOffice since I use Excel.

The #N/A is because no prior Monte Carlo was run. It should fill with values after a successful run.

-B
Thanks.
I set "Use Monte Carlo growth rates?" to "y", hit F9, and still get problems.
I have turned off AutoCalculate. I see "Error: Circular reference" in D3, E3, etc.
D3 is "=Quick_Start", E3 is "=MULTIPLE.OPERATIONS($D3,$A$1,E$2)"
And tried it again. error went away in E3, though stayed in D3
Hit right arrow and F9, and circular reference comes back again
Barsoom
Posts: 720
Joined: Thu Dec 06, 2018 8:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

nereocystis wrote: Sun Feb 21, 2021 7:13 pm Thanks.
I set "Use Monte Carlo growth rates?" to "y", hit F9, and still get problems.
I have turned off AutoCalculate. I see "Error: Circular reference" in D3, E3, etc.
D3 is "=Quick_Start", E3 is "=MULTIPLE.OPERATIONS($D3,$A$1,E$2)"
And tried it again. error went away in E3, though stayed in D3
Hit right arrow and F9, and circular reference comes back again
I had to figure out what tab you were referring to, but I got it.

D3 in the 'Monte Carlo' tab is the reference to the Final Portfolio Value from the Setup tab.

E3, F3... are calculated from Excel Data Tables. Apparently, LibreOffice is doing something different using a function called "Multiple.Operations." It looks like this is not a perfect port of the Data Table functionality.

Data Tables are a super-fast way for Excel to rapidly calculate multiple iterations of a spreadsheet by changing one or two values and building a matrix of results. It looks like LibreOffice is not replicating this functionality in the same way as Excel, and is producing interim errors that Excel does not.

-B
Jtschrock
Posts: 6
Joined: Sat Sep 23, 2017 9:49 am

Re: Retiree Portfolio Model

Post by Jtschrock »

Can someone tell me what the model does with the RMD distributions? Are they automatically reinvested, or is that not possible within the spreadsheet? Thanks!
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Jtschrock wrote: Thu Mar 11, 2021 6:26 pm Can someone tell me what the model does with the RMD distributions? Are they automatically reinvested, or is that not possible within the spreadsheet? Thanks!
RMDs are subtracted from the IRA account and added to the Taxable account, earning the return rates assigned to the Taxable account.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

Question about the returns used in the Inherited Portfolio Impact calculation, Results!G180-G183. The return rates used reference the account return basis only, Setup!E81-E86. I was using the asset class return basis in the rest of the sheet (so maybe should have been Setup!J81-J86) so the returns used for the inherited portfolio weren't consistent with those I used elsewhere. Is that a bug or am I doing something wrong?
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Sun Mar 14, 2021 12:32 pm Question about the returns used in the Inherited Portfolio Impact calculation, Results!G180-G183. The return rates used reference the account return basis only, Setup!E81-E86. I was using the asset class return basis in the rest of the sheet (so maybe should have been Setup!J81-J86) so the returns used for the inherited portfolio weren't consistent with those I used elsewhere. Is that a bug or am I doing something wrong?
That is more of a "feature" for that quick calculation of earnings in the final year of the model. I will fix it but suggest setting the otherwise unused account return factors to mimic your asset class rates in the meantime.

Done, see below...
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

A minor update is available that corrects a calculation of forecast earnings in the year after the final model year in the "Inherited Portfolio Impact" section on the Results page. This impacts only users using this analysis. Download: https://www.dropbox.com/s/su97cf2wmah57 ... .xlsm?dl=0

This changes the earnings calculation for final year+1 on Results page "Inherited Portfolio Impact" section to be Setup page's "Rates Being Used". Thanks to BH Exchme for reporting this.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

BigFoot48 wrote: Sun Mar 14, 2021 1:15 pm A minor update is available that corrects a calculation of forecast earnings in the year after the final model year in the "Inherited Portfolio Impact" section on the Results page. This impacts only users using this analysis. Download: https://www.dropbox.com/s/su97cf2wmah57 ... .xlsm?dl=0

This changes the earnings calculation for final year+1 on Results page "Inherited Portfolio Impact" section to be Setup page's "Rates Being Used". Thanks to BH Exchme for reporting this.
Thanks!
retiredmamma
Posts: 2
Joined: Thu Mar 25, 2021 2:31 pm

Re: Retiree Portfolio Model

Post by retiredmamma »

Thanks for sharing this. Working through it now. I'm sure I'm missing it - but is there a place to forecast retiring early (49 yo) and using taxable accounts for expenses until typical retirement age and then transitioning (depending on amount left in the taxable accounts) to retirement accounts? Thanks and sorry if this is an ignorant question.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

retiredmamma wrote: Thu Mar 25, 2021 2:41 pm Thanks for sharing this. Working through it now. I'm sure I'm missing it - but is there a place to forecast retiring early (49 yo) and using taxable accounts for expenses until typical retirement age and then transitioning (depending on amount left in the taxable accounts) to retirement accounts? Thanks and sorry if this is an ignorant question.
Study the instructions and example data a bit more and you'll find that that's how it works. Expenses come out of the taxable account which is refilled with pre and post-retirement earnings and transfers from IRAs etc., the "retirement accounts".
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
retiredmamma
Posts: 2
Joined: Thu Mar 25, 2021 2:31 pm

Re: Retiree Portfolio Model

Post by retiredmamma »

Thanks. I’ll do that. I didn’t see where I could modify for early retirement (eg 50) but clearly I must have missed it. Thanks
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

I'm confused about the tax treatment at end of life and had a couple questions/suggestions.

1.In cells Results!L180 and M180 for taxable account values at the end of life, the value is calculated by reducing it by the long term capital gains rate. I thought the capital gains tax basis would be reset for the heirs?

2.The tax rate applied to both with and without conversions is the same number, set in Results!n179, though it may actually be very different. Could we get separate input fields for the tax rate to the heirs for the cases of with and without the Roth conversions?

3.Is there a reason the program only shows the 1st year benefit to heirs in Setup!i337? Seems like multiplying by the RMD factor entered in Results!n181 might be better. As it is, seems like users may simply add the first year benefit and their own benefit and miss the real size of the effect of Roth conversions on their heirs (I know I did). The program's actions are explained in the embedded notes, so just a suggestion of a way to help reduce user error.

4.Why the use of the single life expectancy table in Results!n181? The initial data entry is 14.8, which doesn't force withdrawals in 10 years. Maybe default that to a number a little smaller than 10 so people that don't get into the details will have a reasonable answer. Or if you want to get fancy, I think the maximum number could be calculated from the returns and displayed for the user to decide.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Fri Mar 26, 2021 1:53 pm I'm confused about the tax treatment at end of life and had a couple questions/suggestions.

1.In cells Results!L180 and M180 for taxable account values at the end of life, the value is calculated by reducing it by the long term capital gains rate. I thought the capital gains tax basis would be reset for the heirs?

The taxable account balance is not being reduced by the LTCG rate but rather the user's estimate of how much of taxable account earnings are capital gains as set in the Setup page "taxable account adjustments" section.

2.The tax rate applied to both with and without conversions is the same number, set in Results!n179, though it may actually be very different. Could we get separate input fields for the tax rate to the heirs for the cases of with and without the Roth conversions?

Taxes with and without conversions are separately calculated on the Details (included conversions if selected) and Base (no conversions) pages using the appropriate current marginal rates for each.

3.Is there a reason the program only shows the 1st year benefit to heirs in Setup!i337? Seems like multiplying by the RMD factor entered in Results!n181 might be better. As it is, seems like users may simply add the first year benefit and their own benefit and miss the real size of the effect of Roth conversions on their heirs (I know I did). The program's actions are explained in the embedded notes, so just a suggestion of a way to help reduce user error.

Occasional portfolio ending balances are analyzed in simple ways to show how heirs may benefit, but the model is not designed to showed extensive heir benefits. Users are on their own in using this data.

4.Why the use of the single life expectancy table in Results!n181? The initial data entry is 14.8, which doesn't force withdrawals in 10 years. Maybe default that to a number a little smaller than 10 so people that don't get into the details will have a reasonable answer. Or if you want to get fancy, I think the maximum number could be calculated from the returns and displayed for the user to decide.

The 14.8 factor representing age 85 was chosen assuming a spouse was inheriting it. The cell note could be improved to provide more guidance on the use of this. Again, this section is just a quick look at ending results and should be used with a "grain of salt" as forecasting 30-40 out is a fools errand to begin with.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Exchme
Posts: 1323
Joined: Sun Sep 06, 2020 3:00 pm

Re: Retiree Portfolio Model

Post by Exchme »

BigFoot48 wrote: Fri Mar 26, 2021 3:52 pm
Exchme wrote: Fri Mar 26, 2021 1:53 pm I'm confused about the tax treatment at end of life and had a couple questions/suggestions.

1.In cells Results!L180 and M180 for taxable account values at the end of life, the value is calculated by reducing it by the long term capital gains rate. I thought the capital gains tax basis would be reset for the heirs?

The taxable account balance is not being reduced by the LTCG rate but rather the user's estimate of how much of taxable account earnings are capital gains as set in the Setup page "taxable account adjustments" section.

2.The tax rate applied to both with and without conversions is the same number, set in Results!n179, though it may actually be very different. Could we get separate input fields for the tax rate to the heirs for the cases of with and without the Roth conversions?

Taxes with and without conversions are separately calculated on the Details (included conversions if selected) and Base (no conversions) pages using the appropriate current marginal rates for each.

3.Is there a reason the program only shows the 1st year benefit to heirs in Setup!i337? Seems like multiplying by the RMD factor entered in Results!n181 might be better. As it is, seems like users may simply add the first year benefit and their own benefit and miss the real size of the effect of Roth conversions on their heirs (I know I did). The program's actions are explained in the embedded notes, so just a suggestion of a way to help reduce user error.

Occasional portfolio ending balances are analyzed in simple ways to show how heirs may benefit, but the model is not designed to showed extensive heir benefits. Users are on their own in using this data.

4.Why the use of the single life expectancy table in Results!n181? The initial data entry is 14.8, which doesn't force withdrawals in 10 years. Maybe default that to a number a little smaller than 10 so people that don't get into the details will have a reasonable answer. Or if you want to get fancy, I think the maximum number could be calculated from the returns and displayed for the user to decide.

The 14.8 factor representing age 85 was chosen assuming a spouse was inheriting it. The cell note could be improved to provide more guidance on the use of this. Again, this section is just a quick look at ending results and should be used with a "grain of salt" as forecasting 30-40 out is a fools errand to begin with.

Sorry, I don't think I communicated well. (Edit - followup question on item 1 deleted, the program is calculating the taxes for the heir for the first year using the same methodology used for the user's lifetime, perfectly reasonable)

On Item 2, in the Tax_Est field Results!N179, used to calculate Heir_benefit, I was hoping to be able to have different input fields for the heir tax rate with conversions vs. without.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Fri Mar 26, 2021 4:18 pm Sorry, I don't think I communicated well. (Edit - followup question on item 1 deleted, the program is calculating the taxes for the heir for the first year using the same methodology used for the user's lifetime, perfectly reasonable)

On Item 2, in the Tax_Est field Results!N179, used to calculate Heir_benefit, I was hoping to be able to have different input fields for the heir tax rate with conversions vs. without.
This simple look at end-of-forecast results needs to remain simple. If desired, a different marginal rate can be used to determine the taxes for the conversion results and the taxes and net amount entered in the provided text box.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Madisonriver
Posts: 37
Joined: Sat Mar 27, 2021 9:58 am

Re: Retiree Portfolio Model

Post by Madisonriver »

Background: I have used RPM for approximately 3 years leading up to retirement this past year. First post on the forum. I am fluent in Visual Basic and database programming and want to offer my admiration for Bigfoot's excellent work on RPM - I am amazed at the programming within RPM - sincere thanks and appreciation of your understanding of spreadsheet's and personal finance.

My question(s) to follow. We are now fully retired mid 2020 and started Roth conversion's in 2021. I have run into a situation that at present am unable to figure out a work around. My issue maybe that RPM is designed as a forecasting tool and not a real time tool. Or I am overlooking something.

Now that I have started Roth conversions, I am updating Section 2 "Portfolio balances" and Section 10 "Optional: Roth Conversions" specifically conversions and withdrawals for the current year (I am using the "entered" method (E:329)).
1. My roth conversions are transferred into an previously funded Roth IRA (E:67) - and not the "Roth Conv" account used in the programming. Initially I did not see an issue but this morning realized that when I update the current Roth IRA balance in E:67 - AND the year to date Roth conversions (C:344) I realized that I the current total Roth Balances are incorrect. In trying to troubleshoot my dilemma, I believe that there is no method to update the balance of the "Roth Conv" even if I actually used a separate Roth account for the conversions?

The work around I believe should be to substract my Roth conversions from the account balance of the Roth IRA (E:67) - or stop updating account balances altogether ? I must be overlooking something.

Thanks in advance - and I hope this makes sense. I am hopeful that other RPM users in retirement performing Roth conversions can suggest or share what they are doing to keep the RPM data current.
User avatar
Topic Author
BigFoot48
Posts: 3113
Joined: Tue Feb 20, 2007 9:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Madisonriver wrote: Sat Mar 27, 2021 10:33 am My question(s) to follow. We are now fully retired mid 2020 and started Roth conversion's in 2021. I have run into a situation that at present am unable to figure out a work around. My issue maybe that RPM is designed as a forecasting tool and not a real time tool. Or I am overlooking something.

Now that I have started Roth conversions, I am updating Section 2 "Portfolio balances" and Section 10 "Optional: Roth Conversions" specifically conversions and withdrawals for the current year (I am using the "entered" method (E:329)).
1. My roth conversions are transferred into an previously funded Roth IRA (E:67) - and not the "Roth Conv" account used in the programming. Initially I did not see an issue but this morning realized that when I update the current Roth IRA balance in E:67 - AND the year to date Roth conversions (C:344) I realized that I the current total Roth Balances are incorrect. In trying to troubleshoot my dilemma, I believe that there is no method to update the balance of the "Roth Conv" even if I actually used a separate Roth account for the conversions?

The work around I believe should be to substract my Roth conversions from the account balance of the Roth IRA (E:67) - or stop updating account balances altogether ? I must be overlooking something.

Thanks in advance - and I hope this makes sense. I am hopeful that other RPM users in retirement performing Roth conversions can suggest or share what they are doing to keep the RPM data current.
RPM is a forecasting tool with prior year ending balances as the portfolio's starting point. The Roth Conversion IRA does not have a prior year beginning balance but one of the other two Roth accounts could be used to contain the results of prior conversions, I suppose, then combined in another worksheet to track the totals. Good luck!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 18-time loser
Madisonriver
Posts: 37
Joined: Sat Mar 27, 2021 9:58 am

Re: Retiree Portfolio Model

Post by Madisonriver »

BigFoot48 wrote: Sat Mar 27, 2021 1:21 pm RPM is a forecasting tool with prior year ending balances as the portfolio's starting point. The Roth Conversion IRA does not have a prior year beginning balance but one of the other two Roth accounts could be used to contain the results of prior conversions, I suppose, then combined in another worksheet to track the totals. Good luck!
[ quote fixed by admin LadyGeek]

Thank you very much, makes sense to me. And to reiterate - fantastic work!
Post Reply