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: 329
Joined: Sat Dec 28, 2013 1:45 pm

Re: Retiree Portfolio Model

Post by DebiT »

BigFoot48 wrote: Fri Jan 08, 2021 4: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 63, life turned upside down 3/2/19, thanking God for what I've learned from this group
Pamina2
Posts: 1
Joined: Sat Jan 09, 2021 5: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 6:37 pm, edited 1 time in total.
Exchme
Posts: 227
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: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Exchme wrote: Thu Jan 14, 2021 9: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; 15-time loser
User avatar
Topic Author
BigFoot48
Posts: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Pamina2 wrote: Sat Jan 09, 2021 6: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; 15-time loser
goGators
Posts: 71
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: 11009
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Fri Jan 22, 2021 9: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: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

goGators wrote: Fri Jan 22, 2021 9: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; 15-time loser
goGators
Posts: 71
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

FiveK wrote: Fri Jan 22, 2021 10: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 7: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: 11009
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Sat Jan 23, 2021 10: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: 317
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: 426
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

sandramjet wrote: Tue Jan 26, 2021 12:23 am 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: 259
Joined: Thu Dec 31, 2015 11: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: 317
Joined: Thu Oct 23, 2014 11:28 pm

Re: Retiree Portfolio Model

Post by sandramjet »

Barsoom wrote: Tue Jan 26, 2021 3: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: 426
Joined: Thu Dec 06, 2018 9: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: 70110
Joined: Sat Dec 20, 2008 5: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: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: RPM and Roth conversion question

Post by BigFoot48 »

ByThePond wrote: Tue Jan 26, 2021 8: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; 15-time loser
ByThePond
Posts: 259
Joined: Thu Dec 31, 2015 11:21 am

Re: RPM and Roth conversion question

Post by ByThePond »

BigFoot48 wrote: Tue Jan 26, 2021 5: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: 71
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 2:59 pm, edited 1 time in total.
User avatar
FiveK
Posts: 11009
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: 71
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

FiveK wrote: Wed Jan 27, 2021 1: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: 11009
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK »

goGators wrote: Wed Jan 27, 2021 9:38 pm
FiveK wrote: Wed Jan 27, 2021 1: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: 17
Joined: Thu Mar 28, 2019 12:14 pm
Location: Reno, NV

Re: Retiree Portfolio Model

Post by MileKing »

goGators wrote: Wed Jan 27, 2021 8:59 am
FiveK wrote: Sat Jan 23, 2021 12:25 pm 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: 71
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators »

MileKing wrote: Sun Jan 31, 2021 12:09 pm 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: 426
Joined: Thu Dec 06, 2018 9: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: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Barsoom wrote: Fri Feb 05, 2021 5: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; 15-time loser
Iconicus
Posts: 12
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: 2862
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 »

Iconicus wrote: Sun Feb 07, 2021 3: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; 15-time loser
User avatar
Topic Author
BigFoot48
Posts: 2862
Joined: Tue Feb 20, 2007 10: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; 15-time loser
Barsoom
Posts: 426
Joined: Thu Dec 06, 2018 9: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: 426
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

nereocystis wrote: Sat Feb 20, 2021 4: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 2:39 pm
nereocystis wrote: Sat Feb 20, 2021 4: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: 426
Joined: Thu Dec 06, 2018 9:40 am

Re: Retiree Portfolio Model

Post by Barsoom »

nereocystis wrote: Sun Feb 21, 2021 8: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
Post Reply