Retiree Portfolio Model

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Post by gponym » Sat Feb 09, 2019 7:59 pm

mkedst wrote:
Sat Feb 09, 2019 11:11 am
gponym wrote:
Fri Feb 08, 2019 8:39 pm
Does RPM handle the rather byzantine rules that determine what percent of a taxpayer's Social Security income gets included into taxable income?
Yes, the calculate can be seen on the Details tab, section "SS benefits taxation calculation".
Thanks. I see it and the supporting calculations.

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

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 6:17 am

HOW TO ENTER & DISPLAY REAL INSTEAD OF NOMINAL? UNDERSTANDING THE PRESENT VALUE DISCOUNT RATE SETTING?
BigFoot48 wrote:
Sun Apr 15, 2018 3:09 pm
goGators wrote:
Sun Apr 15, 2018 1:42 pm
I assume RPM account balances are nominal (inflated dollars).
Is there an easy way to display them in real (non-inflated) dollars?
You can set the inflation rate to 0% (Section 5 - Expenses), and
the tax rate/bracket escalator to 0% (Rax Tables page), and
enter your earnings rates at real values (Section 3 - Return Rates).
That should do it - I think.
I think there may be some more items which need to be set to zero... has anyone else looked at this?

OR -- does cell I15 Present Value Discount Rate do this for you? Doesn't all the output turn into Real if this is set to your guess at Real?
I'm confused - is the sheet designed to show output as Real or Nominal?

Version 18.0 for year 2018 says:
> The discount rate used to determine present value of future cash flows, is reduced to a real rate (after inflation) of 3% to provide a more realistic real-return result.
>
and the popup (which I don't know how to COPY) says "a real rate (after inflation) of 3.0% is used in the Example data. An alternative could be the portfolio forecast average real return rate."

I'm confused because the sheet doesn't explicitly let you enter nominal + real so they can be seen, in each of the items where "change % over time" is a factor.

SOME SETTINGS NEEDED, IN ORDER:
SETUP, MODELING OPTIONS: set Present Value Discount Rate to 0% ???
SETUP Section 3 Return Rates: Enter Real % rates.
SETUP, Section 4 Income: set Pension Change to the COLA.
SETUP, Section 4 Income: set SS Benefits COLA.
SETUP, Section 5 Living Expenses: set Inflation to Real ??? Don't understand how this interacts with PVDR
SETUP, Section 6, IRA Withdrawals -- would you set Yearly Change to equal Inflation if you want to withdraw an equal Real amount each year?

TAX RATE TABLES, set the tax rate/bracket escalator to ???
***

To start trying to understand this spreadsheet, which is phenominally complex, I want to simply ask "if income matches inflation, and spending remains the same (accounting for inflation), how long will it last?

EDIT: I really don't understand what the value of the Present Value Discount Rate does to the output.
I'm guessing that if I think the Real Return Rate is 1% then that is the value I put into the PVDR
Then the Taxable Return of govt bonds is now, say, 3% Nominal. So that goes into the Return Rates,Taxable.
Then 1% goes everywhere that represents a COLA (pension, Social Security... anywhere else?)
But after doing this I expect the graphs to be "flat" for account balance over time, not rising, when I set values for various Income = Expense.
Last edited by Bongleur on Mon Feb 11, 2019 7:35 am, edited 1 time in total.
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: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 6:29 am

Is SETUP, 4 Income, Pensions -- presumed to be a COLA pension? Or do you enter a COLA in "Change" as your guess at inflation?
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: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 7:42 am

It is REALLY bugging me that the Income & Expenses graph is BACKWARDS.
The Income should be blue & the bar graph, the same as the two charts above it.
Not red & line graph.

I'm guessing its because the two datum were entered in the opposite order vis a vis the previous graphs, when creating the graph.
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: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 8:21 am

The pop up windows are too small. They are only "long" enough to show all the lines when viewed at 75% size.
I normally view at %200 size.
Can elevator bars be added to these?
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: 2738
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 11, 2019 8:38 am

Lots of questions, I'll start with the easy one. The Present Value Discount Rate is ONLY used in a very few calculations to show the present value of a result, like Social Security income, so that different alternatives can be compared.

The model is designed to show nominal results. The percent factors provided are designed to adjust the nominal results to each user's choice, but if all of them are set to real values and escalation/inflation at 0%, I suppose it will show real results, but there's no "switch" to do this, so you're on your own.

Graphs should be generally consistent in appearance and I will check the one you noted. I'm not aware of an elevator feature for cell comments. The income factors, which could be a pension, can be annually escalated or reduced by the adjustment factor.

And all users should keep in mind that the model is open-source and users can change any feature or calculation they want to. Just unprotect the sheet and modify it.
Last edited by BigFoot48 on Mon Feb 11, 2019 11:12 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 8:45 am

I think SETTINGS cells I56 & I66 which is Rates Being Used, should not come out to 54%.
I just blew up a 250K taxable start to $40 Million end amount, simply by changing from Account method to Asset Class method.
I56 is 3% using Account, but I66 stays at 56% after I have entered a % above zero for stocks, bonds, etc.

How do I get this thing to reflect a 2% Real return for Stocks, 1% Real for Bonds, and 0% Real for MM ?
I entered 1% in the Present Value Discount Rate and
Average Returns Calculator
Allocation Rates
20.0% 3.0% Stock
70.0% 2.0% Bond
0.0% Tax Exempt
10.0% 0.0% Money Mkt
0.0% Other
100.0% 2.0% Average

Rates Being Used
Beginning Change 2019
Taxable 54.0% 54.0%

class method: asset Money Mkt Calculated
allocation for each account Stocks Bonds Tax Exempt & Other Rates
Taxable 20.0% 70.0% 0.0% 10.0% 54.0%

without hand-adjusting its gibberish; how do I PASTE spreadsheet data & keep the formatting?
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: 2738
Joined: Tue Feb 20, 2007 10:47 am
Location: Arizona

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 11, 2019 9:01 am

Bongleur wrote:
Mon Feb 11, 2019 8:45 am
I think SETTINGS cells I56 & I66 which is Rates Being Used, should not come out to 54%.
The factors for each Asset Class must equal 100%. Check your Return Rates by Asset Class which are used to calculate the Calculated Rates in the Class Method section.

Use the Example data as an example and enter your settings in a similar fashion.
Last edited by BigFoot48 on Mon Feb 11, 2019 11:01 am, edited 2 times in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Zephavest » Mon Feb 11, 2019 10:45 am

Boggedown wrote:
Sat Feb 02, 2019 2:42 pm
However, Microsoft Excel has a powerful optimizing add-in called 'Solver'. It also has 'Goal Seek' but AFAIK that can only handle one variable so won't apply to this case. Solver can theoretically handle large sets of variables, and provides several advanced methods of optimizing a 'results' cell by manipulating such sets of input cells.
Hi Boggedown,

Thanks so much for the heads up on Solver, I've never used it before, I tried it on my personal budget spreadsheet, where I was getting a circular reference, amazing, it did the trick. Then I wrote a macro for it to run on every column/year. Thanks again for sharing the tip.

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

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 11:00 am

BigFoot48 wrote:
Mon Feb 11, 2019 9:01 am
The factors for each Asset Class must equal 100%. I see I don't have a warning if over 100% is entered, as the Money Market factor is designed to set the total to 100%, but perhaps an error message is required.

Use the Example data as an example and enter your settings in a similar fashion.
Yes I noticed that MM is the "remainder." Very opaque, should just have all classes in blue.
My numbers DO add up to 100%.
I think the equation for those cells has a bad input cell somewhere. Can't figure out what its trying to do.
I don't see why it doesn't just copy the answer from the Average Returns Calculator
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
Zephavest
Posts: 82
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Mon Feb 11, 2019 11:01 am

Hello to All RPM Users and to those kicking the tires,

RPM is an Open Source, Professional Grade Finance Modeling Tool, it is Free of Charge and ONLY made possible by the countless, untold hours, spent on it by it's author, BigFoot48. This tool exceeds the capabilities of almost all other Retirement Modeling Tools, free or commercially available. As such, it does take personal knowledge in Finance, Investments and Excel usage to use properly, as one forum member observed, it is intended for Professionals or Serious Hobbyists. It does have a very steep learning curve but that is only because of the power the tool has.

BigFoot has bent over backwards year after year, continually updating the tool, fixing bugs and adding features that meet most users needs and for that I'm very grateful. When you hit a situation that the tool does not address, be creative, you can find workarounds for your own personal situation. For example, I have a tax-free income stream that was hard to model, my solution, I entered it in the SPIA section with 100% exclusion. Being a retired engineer and being use to precision, instead of entering my Social Security as age 66 or 67, I simply prorated my starting rate at age 66.4 and used that, confirming my starting amounts at the projected start year. You will read here of other creative solutions other users have found for their unique situation.

I have learned a lot reading all the posts and seeing how other uses adopted the tool to their needs. For situations that are more personal and are specific questions for the author it may be best to send him a personal private message. For help on how to use Excel, if you are not already and intermediate to advanced user, there are dozens of excellent websites with Excel help. For example, two weeks ago, I wanted to learn VBA, to write a macro for Solver, on my personal budget, I now have two successful macros under my belt, when I got stuck I found excelforum.com very helpful.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 11, 2019 11:03 am

Bongleur wrote:
Mon Feb 11, 2019 11:00 am

Yes I noticed that MM is the "remainder." Very opaque, should just have all classes in blue.
My numbers DO add up to 100%.
I think the equation for those cells has a bad input cell somewhere. Can't figure out what its trying to do.
I don't see why it doesn't just copy the answer from the Average Returns Calculator
I edited my reply: "Check your Return Rates by Asset Class which are used to calculate the Calculated Rates in the Class Method section." Blue is used to indicate a user-supplied value, which the Money Market amount is not in order to make having a 100% total automatic.

The Calculators throughout the model are just that - stand-alone calculators that have no impact on the model calculations.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 11, 2019 11:18 am

Zephavest wrote:
Mon Feb 11, 2019 11:01 am
Hello to All RPM Users and to those kicking the tires,
Many thanks for Zephavest for his description of RPM and advice for new users. All very accurate and useful. While it would be impossible for me to personally help each user, I will try when possible. If a user is having too much difficulty with it, I suggest the excellent ORP model, which is very easy to use and provides nearly identical results in calculating future portfolio results. https://www.i-orp.com/bequest/index.html
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 11:34 am

BigFoot48 wrote:
Mon Feb 11, 2019 8:38 am
Lots of questions, I'll start with the easy one. The Present Value Discount Rate is ONLY used in a very few calculations to show the present value of a result, like Social Security income, so that different alternatives can be compared.

The model is designed to show nominal results. The percent factors provided are designed to adjust the nominal results to each user's choice, but if all of them are set to real values and escalation/inflation at 0%, I suppose it will show real results, but there's no "switch" to do this, so you're on your own.
So I can ignore PVDR... I only found in in Column D of RESULTS. I tried arrows for Dependant Cells, w/ some symbol, probably meaning "see other tab" but I don't know how to figure out WHICH tab.

But Nominal = Real + Inflation. How does the Black Box figure out what each is? Is there a "master" cell to show Inflation used for all calcs?
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: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Mon Feb 11, 2019 11:45 am

A search yields no mention of Sequence of Returns.
Anyone found a Monte Carlo sheet to work on making sure the first 10 years don't kill it?
One with a similar way of inputting data so you can make sure the entries do the same thing.

OR
Maybe something like calculate the first 10 years expenses (above guaranteed income like Pension & SS) and then delete that amount from the portfolio, and let this sheet run for the first 10 years with zero expenses...

Kind of like starting the sheet 10 years before retirement age.

How can one turn TIPS into a line item Asset Class? Since TIPs will have a very different rate of return from other bonds.
Use the Other Income line, and specify starting to use the fund in a specific year... ?
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
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Mon Feb 11, 2019 1:53 pm

I can answer your questions, but you need to make an adjustment in your thinking if you are going to successfully use RPM. RPM is a tool. You need to figure out how to use it. Stating how you think it should work or wish it would work is not going to help you. My advice is to study the documentation before you ask a bunch of questions that would be obvious if you had a basic understanding of finance and Excel, and if you read the documentation. RPM may not be the tool for you. As stated previously, iORP might be more suitable. It too is an excellent tool, and you don't need to understand all the details you are struggling with.
Bongleur wrote:
Mon Feb 11, 2019 11:45 am
A search yields no mention of Sequence of Returns.
Anyone found a Monte Carlo sheet to work on making sure the first 10 years don't kill it?
One with a similar way of inputting data so you can make sure the entries do the same thing.
OR
Maybe something like calculate the first 10 years expenses (above guaranteed income like Pension & SS) and then delete that amount from the portfolio, and let this sheet run for the first 10 years with zero expenses...

Kind of like starting the sheet 10 years before retirement age.
This is not what RPM is about. It doesn't operate this way.
Bongleur wrote:
Mon Feb 11, 2019 11:45 am

How can one turn TIPS into a line item Asset Class? Since TIPs will have a very different rate of return from other bonds.
Use the Other Income line, and specify starting to use the fund in a specific year... ?
Think about it and reason your way through it. Are TIPs closer to equity, bond or money market fund? What would a reasonable expectation be for a rate of return?

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Mon Feb 11, 2019 2:14 pm

Bongleur wrote:
Mon Feb 11, 2019 6:17 am
HOW TO ENTER & DISPLAY REAL INSTEAD OF NOMINAL? UNDERSTANDING THE PRESENT VALUE DISCOUNT RATE SETTING?
BigFoot48 wrote:
Sun Apr 15, 2018 3:09 pm
goGators wrote:
Sun Apr 15, 2018 1:42 pm
I assume RPM account balances are nominal (inflated dollars).
Is there an easy way to display them in real (non-inflated) dollars?
You can set the inflation rate to 0% (Section 5 - Expenses), and
the tax rate/bracket escalator to 0% (Rax Tables page), and
enter your earnings rates at real values (Section 3 - Return Rates).
That should do it - I think.
I think there may be some more items which need to be set to zero... has anyone else looked at this?

SOME SETTINGS NEEDED, IN ORDER:
SETUP, MODELING OPTIONS: set Present Value Discount Rate to 0% ???
SETUP Section 3 Return Rates: Enter Real % rates.
SETUP, Section 4 Income: set Pension Change to the COLA.
SETUP, Section 4 Income: set SS Benefits COLA.
SETUP, Section 5 Living Expenses: set Inflation to Real ??? Don't understand how this interacts with PVDR
SETUP, Section 6, IRA Withdrawals -- would you set Yearly Change to equal Inflation if you want to withdraw an equal Real amount each year?

TAX RATE TABLES, set the tax rate/bracket escalator to ???
***
Here's what I did to use REAL RATES, and it seems to be working.
- Present Value Discount Rate:
On the SETUP page, set cell I15 to 0.9%.
- Pension
On the SETUP page, set cell F93 to 0.0% if the pension is inflation adjusted. If not, set it to -2.1%.
- Social Security COLA
On the SETUP page, set cell E103 to 0.0%.
- Taxes Escalation Rate
On the TAX TABLE page, set cell D10 to 0.0%.

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Mon Feb 11, 2019 2:18 pm

Bongleur wrote:
Mon Feb 11, 2019 7:42 am
It is REALLY bugging me that the Income & Expenses graph is BACKWARDS.
Looking at the big picture, it is a very minor detail. Don't let it bug you.

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Mon Feb 11, 2019 2:23 pm

Bongleur wrote:
Mon Feb 11, 2019 8:45 am
without hand-adjusting its gibberish; how do I PASTE spreadsheet data & keep the formatting?
Not sure what you are referring to. What gibberish? What are you trying to cut/paste? From the calculator? All the items in the blue cells should be entered. Just type them in.

You might try "PASTE SPECIAL" and select "VALUES."
Last edited by munemaker on Mon Feb 11, 2019 2:27 pm, edited 1 time in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Mon Feb 11, 2019 2:27 pm

munemaker wrote:
Mon Feb 11, 2019 2:14 pm
Here's what I did to use REAL RATES, and it seems to be working.
- Present Value Discount Rate:
On the SETUP page, set cell I15 to 0.9%.
- Pension
On the SETUP page, set cell F93 to 0.0% if the pension is inflation adjusted. If not, set it to -2.1%.
- Social Security COLA
On the SETUP page, set cell E103 to 0.0%.
- Taxes Escalation Rate
On the TAX TABLE page, set cell D10 to 0.0%.
A reminder, the Present Value Discount Rate is not used in any model calculations, only in several analyses of the resulting data. So it should be ignored when attempting to use real rates.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Mon Feb 11, 2019 3:35 pm

BigFoot48 wrote:
Mon Feb 11, 2019 2:27 pm
munemaker wrote:
Mon Feb 11, 2019 2:14 pm
Here's what I did to use REAL RATES, and it seems to be working.
- Present Value Discount Rate:
On the SETUP page, set cell I15 to 0.9%.
- Pension
On the SETUP page, set cell F93 to 0.0% if the pension is inflation adjusted. If not, set it to -2.1%.
- Social Security COLA
On the SETUP page, set cell E103 to 0.0%.
- Taxes Escalation Rate
On the TAX TABLE page, set cell D10 to 0.0%.
A reminder, the Present Value Discount Rate is not used in any model calculations, only in several analyses of the resulting data. So it should be ignored when attempting to use real rates.
I realize PVDR is not used in any calculations of outputs, only NPV calculations.

My understanding is that when discounting future income streams such as SS, that the NPV is viewed as being less than the simple sum of the payments discounted for inflation; the reasoning for this being the discount rate used in the NPV calculation needs to account for the return the investor would have realized if he/she had invested the money as a lump sum.

Not sure if I explained that very well. A better explanation is here:
https://www.kitces.com/blog/net-present ... breakeven/

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

Re: Retiree Portfolio Model

Post by Bongleur » Tue Feb 12, 2019 1:05 am

"The model is designed to show nominal results."

It would be good to say that in the introduction so people first view it with the correct interpretation.
Why did you do it that way? What are the benefits of seeing an amount many years hence in inflated Nominal dollars?

So when it says I have a 100,000 dollars left after 40 years, its really just pocket change, because those are 40 year old dollars ?
It would be useful to be able to at least toggle that into Real dollars, to see how much value you are leaving to your heirs.

Another reason to work in Real dollars is that whan you change your expenses or income over time, that is impossible to "see" when the dollar amount in a year is shown as Nominal inflated money.
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: 2276
Joined: Fri Dec 03, 2010 10:36 am

Re: Retiree Portfolio Model

Post by Bongleur » Tue Feb 12, 2019 1:12 am

munemaker wrote:
Mon Feb 11, 2019 2:14 pm
Bongleur wrote:
Mon Feb 11, 2019 6:17 am
HOW TO ENTER & DISPLAY REAL INSTEAD OF NOMINAL? UNDERSTANDING THE PRESENT VALUE DISCOUNT RATE SETTING?
Here's what I did to use REAL RATES, and it seems to be working.
- Present Value Discount Rate:
On the SETUP page, set cell I15 to 0.9%.
- Pension
On the SETUP page, set cell F93 to 0.0% if the pension is inflation adjusted. If not, set it to -2.1%.
- Social Security COLA
On the SETUP page, set cell E103 to 0.0%.
- Taxes Escalation Rate
On the TAX TABLE page, set cell D10 to 0.0%.
So the default for Pension IS COLA ? Nothing says that in the instructions. That cell says "to change annual income" -- how else does a Pension change over time, other than being COLA ??? Why is it titled "Change" instead of "COLA."

And if the Pension is NOT COLA, then setting to -2.1% is because 2.1 is the assumption of inflation that is baked-in to the Sheet, somewhere?
So if I want to use a 1% inflation rate for the COLA, I need to enter 2.1 - 1 = 1.1% ???
This makes NO sense.
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
Zephavest
Posts: 82
Joined: Thu Jan 10, 2013 9:09 am
Location: Texas

Re: Retiree Portfolio Model

Post by Zephavest » Tue Feb 12, 2019 7:09 am

Bongleur wrote:
Tue Feb 12, 2019 1:05 am
It would be good to say that in the introduction so people first view it with the correct interpretation.
Another item to be good to say in the the introduction is the tool assumes people have a good understanding of basic retirement financial concepts. There are many great posts on Bogleheads discussing these along with many great websites such as these I found in a quick Google search.

Your Retirement Plans Can't Ignore Inflation
https://money.usnews.com/money/blogs/th ... -inflation

How Inflation Eats Away at Your Retirement
https://www.investopedia.com/articles/r ... rement.asp

Inflation Impact on Retirement Planning
https://tritonfinancialgroup.com/inflat ... -planning/

Google is your friend, try doing searches such as this one:
understanding inflation effects on retirement budget
https://www.google.com/search?newwindow ... 84&bih=571
BigFoot48 wrote:
Mon Feb 11, 2019 11:18 am
If a user is having too much difficulty with it, I suggest the excellent ORP model, which is very easy to use and provides nearly identical results in calculating future portfolio results. https://www.i-orp.com/bequest/index.html

goGators
Posts: 55
Joined: Thu Apr 04, 2013 10:01 pm

Re: Retiree Portfolio Model

Post by goGators » Tue Feb 12, 2019 7:47 am

Zephavest wrote:
Mon Feb 11, 2019 11:01 am
...
BigFoot has bent over backwards year after year, continually updating the tool, fixing bugs and adding features that meet most users needs and for that I'm very grateful. When you hit a situation that the tool does not address, be creative, you can find workarounds for your own personal situation.
...
+1
I'm another VERY GRATEFUL user. RPM is #1 on my list. It's an amazing tool. Thank you BigFoot for all of your hard work!

smitcat
Posts: 4353
Joined: Mon Nov 07, 2016 10:51 am

Re: Retiree Portfolio Model

Post by smitcat » Tue Feb 12, 2019 8:11 am

goGators wrote:
Tue Feb 12, 2019 7:47 am
Zephavest wrote:
Mon Feb 11, 2019 11:01 am
...
BigFoot has bent over backwards year after year, continually updating the tool, fixing bugs and adding features that meet most users needs and for that I'm very grateful. When you hit a situation that the tool does not address, be creative, you can find workarounds for your own personal situation.
...
+1
I'm another VERY GRATEFUL user. RPM is #1 on my list. It's an amazing tool. Thank you BigFoot for all of your hard work!
+1 we are very thankful for this tool, it has been very vauable for us.
Thank you for your work!

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Tue Feb 12, 2019 8:22 am

smitcat wrote:
Tue Feb 12, 2019 8:11 am
goGators wrote:
Tue Feb 12, 2019 7:47 am
Zephavest wrote:
Mon Feb 11, 2019 11:01 am
...
BigFoot has bent over backwards year after year, continually updating the tool, fixing bugs and adding features that meet most users needs and for that I'm very grateful. When you hit a situation that the tool does not address, be creative, you can find workarounds for your own personal situation.
...
+1
I'm another VERY GRATEFUL user. RPM is #1 on my list. It's an amazing tool. Thank you BigFoot for all of your hard work!
+1 we are very thankful for this tool, it has been very vauable for us.
Thank you for your work!
+1 Nothing else remotely like it. It is my go-to tool for retirement planning and Roth conversions. I am very appreciative for your work and for making this available to us.

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Tue Feb 12, 2019 9:30 am

Bongleur wrote:
Tue Feb 12, 2019 1:12 am
[Why is it titled "Change" instead of "COLA."
You say Tomatoe, I say Tomato
https://www.youtube.com/watch?v=zZ3fjQa5Hls
Bongleur wrote:
Mon Feb 11, 2019 6:17 am
And if the Pension is NOT COLA, then setting to -2.1% is because 2.1 is the assumption of inflation that is baked-in to the Sheet, somewhere?
The inflation rate is not "baked into the Sheet." It is baked into the numbers that you enter into the sheet. For example, if you enter a pension that does not have inflation adjustment, it is not going to maintain its real value year after year. It is going to decline every year due to inflation.

eg. 2.1% inflation, $1000 pension inflation adjusted, nominal values:

2019 - $1,000
2020 - $1,021
2021 - $1,042
etc.

eg. 2.1% inflation, $1,000 pension inflation adjusted, real values:

2019 - $1,000
2020 - $1,000
2021 - $1,000
etc.

eg. 2.1% inflation, $1,000 pension non-inflation adjusted, nominal values:

2019 - $1,000
2020 - $1,000
2021 - $1,000

e.g. 2.1% inflation, $1,000 pension, non-inflation adjusted, real values:

2019 - $1,000
2020 - $979
2021 - $959
etc.
Bongleur wrote:
Mon Feb 11, 2019 6:17 am
So if I want to use a 1% inflation rate for the COLA, I need to enter 2.1 - 1 = 1.1% ???
By "inflation rate for the COLA," I assume you mean the annual change of the pension. If you are working in nominal terms, enter 1.0%. If you are working in real terms, then input as a negative number: -1.1%. It is a negative number because the increase is not keeping up with the 2.1% inflation rate.

Hope this helps.
Last edited by munemaker on Tue Feb 12, 2019 12:32 pm, edited 2 times in total.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Tue Feb 12, 2019 9:59 am

Thanks for all the appreciation of the model! It is my contribution to the Boglehead forum and members, who have done so much to improve my and other forum visitors financial lives for many years. And it wouldn't be where it is today without the valuable suggestions for improvements and corrections from Bogleheads, with 20 of the most significant currently listed on the Readme page (and there are many more).

I also want to thank all of you who jump in to help answer user questions. If I don't reply it's because the answers are exactly right, and I couldn't have said it better.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Zephavest » Tue Feb 12, 2019 10:50 am


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

Re: Retiree Portfolio Model

Post by Bongleur » Thu Feb 21, 2019 8:06 pm

Zephavest wrote:
Tue Feb 12, 2019 7:09 am
Bongleur wrote:
Tue Feb 12, 2019 1:05 am
It would be good to say that in the introduction so people first view it with the correct interpretation.
Another item to be good to say in the the introduction is the tool assumes people have a good understanding of basic retirement financial concepts.
Not germaine to my questions. I don't understand how to enter the scenarios I desire into the sheet, because I don't understand the "point of view" from which it was created. Is inflation adjustment a default, or is not inflation adjusted the default? Author says not ("nominal values")

The questions of specifying inflation rate, real return rate, a COLA on a particular income source, and displaying real vs nominal are different.

Where is the cell where I say "include this inflation rate in every yearly calculation (or not) ?" -- thus choosing Real or Nominal display.
Where is the cell where I say "presume the inflation rate is x% ?" -- thus the value of a zero return item will decrease every year by that rate if REAL is toggled ON.

Where are the cells where I say "presume the return above inflation for investment types a,b,c,d... is z1, z2, z3, z4... ?"
OR perhaps any rate of return I enter is presumed to be (inflation + real return) ???

Where is the toggle for "presume this source of income is inflation adjusted every year -- ie it is a COLA (or not) ?"
I just plain don't understand how to work the cell that's supposed to account for COLA, if it is present.

I need tospend more time messing with the spreadsheet. So far nothing I enter gives me the answers I expect, so I'm not using it right. I need to figure out how to work with just one chunk of it at a time, until I get the test to output the number I know is right.
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: 7854
Joined: Sun Mar 16, 2014 2:43 pm

Re: Retiree Portfolio Model

Post by FiveK » Thu Feb 21, 2019 8:24 pm

Bongleur wrote:
Thu Feb 21, 2019 8:06 pm
I need to figure out how to work with just one chunk of it at a time, until I get the test to output the number I know is right.
Can't speak for BigFoot48, but from analogous software troubleshooting:
- "It doesn't work" requests get low priority
- "Here is my exact input, what I expect for an answer (and why), and what I get for an answer. Please help me understand whether it is my expectation or the software that is incorrect." requests are so refreshing that they often engender quick responses.

gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Post by gponym » Thu Feb 21, 2019 9:11 pm

Has anyone found an easy way to view cell comments that get obscured by graphs (and other things)?

For example, on Setup tab, the comment for "Move Age" cell (H213) in section 8 is obscured by the Marginal Federal Tax Rates graph. This is just one example of many such partly obscured comments which are undecipherable.

I use Excel for Mac version 15.32.

(Tried to post an example using Imgur but fails, will pursue that later.)

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Fri Feb 22, 2019 7:53 am

gponym wrote:
Thu Feb 21, 2019 9:11 pm
Has anyone found an easy way to view cell comments that get obscured by graphs (and other things)?
They are not obscured in the Excel version, thus the positioning of the graphs and sizing of the comments. I don't see a solution to this and don't have a Mac for testing. I would recommend using an alternative program to read those comments or unprotecting the page and deleting or moving the the graphs.

More: I see there is a "Send To Back" setting for graphs. I will set that for all the graphs on the Setup page and it will be part of the next release, 19.1, coming soon. Maybe that will fix the Mac problem.
Last edited by BigFoot48 on Fri Feb 22, 2019 9:31 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

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

Re: Retiree Portfolio Model

Post by Zephavest » Fri Feb 22, 2019 8:18 am

gponym wrote:
Thu Feb 21, 2019 9:11 pm
Has anyone found an easy way to view cell comments that get obscured by graphs (and other things)?
1) Un-protect the sheet you want to view comments in.
2) Click into cell with comment, mouse right click menu, use "Edit Comment" or "Show/Hide Comments" to get them to pop up, drag and resize as you wish.
3) Re-protect sheet

(Standard practice on a PC, may be similar on a Mac, If not, Google is your friend:
Excel for Mac how to show comments
https://www.google.com/search?newwindow ... CiWAjt_iZE

gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Post by gponym » Fri Feb 22, 2019 8:50 pm

BigFoot48 wrote:
Fri Feb 22, 2019 7:53 am
gponym wrote:
Thu Feb 21, 2019 9:11 pm
Has anyone found an easy way to view cell comments that get obscured by graphs (and other things)?
They are not obscured in the Excel version, thus the positioning of the graphs and sizing of the comments. I don't see a solution to this and don't have a Mac for testing. I would recommend using an alternative program to read those comments or unprotecting the page and deleting or moving the the graphs.

More: I see there is a "Send To Back" setting for graphs. I will set that for all the graphs on the Setup page and it will be part of the next release, 19.1, coming soon. Maybe that will fix the Mac problem.
Thanks for taking a look, Bigfoot. Problem looks like a tough one, at least in my 2016 Excel for Mac version.

sa: my related reply to Zephavest for the workaround I've adopted

My version has Send Forward and Send Backward options (under Arrange on the Review ribbon) but they are greyed out when I click on a sample graph (same as before: Marg Fed Tax Rate on Setup tab).

Also, right clicking on image yields a context menu but no choices permit sending to back (or front).

Pity that these hide-and-seek comments are not prioritized to display over everything; it seems like that would have been the logical design choice.

gponym
Posts: 9
Joined: Mon Aug 18, 2014 11:37 am

Re: Retiree Portfolio Model

Post by gponym » Fri Feb 22, 2019 8:58 pm

Zephavest wrote:
Fri Feb 22, 2019 8:18 am
gponym wrote:
Thu Feb 21, 2019 9:11 pm
Has anyone found an easy way to view cell comments that get obscured by graphs (and other things)?
1) Un-protect the sheet you want to view comments in.
2) Click into cell with comment, mouse right click menu, use "Edit Comment" or "Show/Hide Comments" to get them to pop up, drag and resize as you wish.
3) Re-protect sheet

(Standard practice on a PC, may be similar on a Mac, If not, Google is your friend:
Excel for Mac how to show comments
https://www.google.com/search?newwindow ... CiWAjt_iZE
Thanks, your suggestions helped. There is more than one way to get to readability in Excel for Mac. Working on the Setup tab, I decided to move the offending graphs to a place which does not interfere with comments. It's one-and-done, other methods seemed to require more futzing--eg, applying "Show" to a comment works, but seems like after that it is alway on display, and still hides behind the graph: I still have to click on it each occasion I want to read it to move it "forward" of an image. Moving comments around also works on a Mac, pretty slick and I may use it on other tabs where space is tighter, but it seemed like more work on setup where I had to move about a half dozen graphs.

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

Re: Retiree Portfolio Model

Post by jmk » Sun Feb 24, 2019 1:54 pm

Where does RPM allow one to list ongoing Roth contributions akin to the lines for IRA contributions? My apologies if this has already been answered, I couldn't find answer by searching.

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Feb 24, 2019 3:05 pm

jmk wrote:
Sun Feb 24, 2019 1:54 pm
Where does RPM allow one to list ongoing Roth contributions akin to the lines for IRA contributions? My apologies if this has already been answered, I couldn't find answer by searching.
There's no provision for additional Roth contributions, but I believe you could use the one of the Roth 1 withdrawal amounts using negative numbers to add funds to that Roth. I have not tested it.

Update: Tested this method and it currently doesn't work if no end age is entered, but does appear to work if an end age is entered. I will try to fix the end-age glitch in version 19.1, coming soon.

UPDATE #2: I decided it would be easier and better to just add a Roth 1 contribution option.
Last edited by BigFoot48 on Mon Feb 25, 2019 8:49 am, edited 1 time in total.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Retiredron
Posts: 49
Joined: Mon Mar 27, 2017 9:43 pm

Re: Retiree Portfolio Model

Post by Retiredron » Sun Feb 24, 2019 7:38 pm

BigFoot48,

The RPM is a great tool and I'm learning how use if more effectively week by week. Thanks so much for making it available to us.

I have a question about how to enter a tIRA balance that has a 16% after-tax basis. Section 2 asks for the current tIRA balance. Is there a way to adjust this balance entry to account for the after-tax basis in the tIRA? One thought I had was to just reduce the tIRA amount entered into Section 2 by 16% and add the 16% to my existing Roth balance entry. However, I'm not sure if that approach would be correct. I don't see anywhere in the tool where you can enter tIRA after-tax basis information but maybe I am just not seeing that!

Thanks in advance for any guidance offered!

Ron

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Sun Feb 24, 2019 10:03 pm

Retiredron wrote:
Sun Feb 24, 2019 7:38 pm
I have a question about how to enter a tIRA balance that has a 16% after-tax basis. Section 2 asks for the current tIRA balance. Is there a way to adjust this balance entry to account for the after-tax basis in the tIRA? One thought I had was to just reduce the tIRA amount entered into Section 2 by 16% and add the 16% to my existing Roth balance entry. However, I'm not sure if that approach would be correct. I don't see anywhere in the tool where you can enter tIRA after-tax basis information but maybe I am just not seeing that!
Ron, glad you like it. Unfortunately the model does not provide for after-tax contributions to IRAs. Your idea of using a Roth for the after-tax contributions is a good alternative, with the only downside that the RMD for the IRA is then understated. So you would need to show withdrawals from the Roth for those years to keep the amount of funds flowing into the taxable account about right.

Another alternative is just to include the full amount and realize that taxes will be over-stated during the RMD years by a relatively small amount. You could adjust taxes using the manual AGI adjustment line in the Details page.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

Retiredron
Posts: 49
Joined: Mon Mar 27, 2017 9:43 pm

Re: Retiree Portfolio Model

Post by Retiredron » Mon Feb 25, 2019 7:29 am

Thanks so much for the speedy reply!

jmk
Posts: 523
Joined: Tue Nov 01, 2011 7:48 pm

Re: Retiree Portfolio Model

Post by jmk » Tue Feb 26, 2019 3:55 pm

BigFoot48 wrote:
Sun Feb 24, 2019 3:05 pm
UPDATE #2: I decided it would be easier and better to just add a Roth 1 contribution option.
This sounds good, thanks!

In the "notes" we should be clear to indicate for users that this is independent of the Roth Conversions later in the spreadsheet.

rts58
Posts: 41
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 » Thu Feb 28, 2019 1:46 am

I think there may be an error in the calculation of state taxable income for pensions. I live in MA and noticed that my pension income in the future was not being included in state taxable income. Here is the formula for cell j207:

Code: Select all

=ROUND(IF(AND(State_Move_Age>J$4,State_Move_Age<>0),Pen_Tax_State*J$66,Pen_Tax_State2*J$66)/Round_To,0)*Round_To
Basically it is saying if I'm above my state move age and it's different from zero, then use my pension tax state value, if not use my second pension tax state.

It's late for me, but I think that is backwards. If I'm above the move age then I should be taxed in the new state. The formula should be:

Code: Select all

=ROUND(IF(AND(State_Move_Age>J$4,State_Move_Age<>0),Pen_Tax_State2*J$66,Pen_Tax_State*J$66)/Round_To,0)*Round_To
Hope I've posted this to the right place. Love this model.

Thanks

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Feb 28, 2019 7:23 am

rts58 wrote:
Thu Feb 28, 2019 1:46 am
I think there may be an error in the calculation of state taxable income for pensions. I live in MA and noticed that my pension income in the future was not being included in state taxable income. Here is the formula for cell j207:

Code: Select all

=ROUND(IF(AND(State_Move_Age>J$4,State_Move_Age<>0),Pen_Tax_State*J$66,Pen_Tax_State2*J$66)/Round_To,0)*Round_To
Basically it is saying if I'm above my state move age and it's different from zero, then use my pension tax state value, if not use my second pension tax state.
Actually it's saying if your state move age is in the future, use the current state taxable pension rate, and once the state move age is no longer greater than your age that year, start using the new state's pension taxable rate. The example data shows this correctly happening. Glad you like it!
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

rts58
Posts: 41
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 » Thu Feb 28, 2019 11:06 am

BigFoot48 wrote:
Thu Feb 28, 2019 7:23 am
Actually it's saying if your state move age is in the future, use the current state taxable pension rate, and once the state move age is no longer greater than your age that year, start using the new state's pension taxable rate.
I see I was looking at the move age comparison wrong. However, I still think the formula is incorrect. In the example data if you change the State_Move_Age and Pen_Tax_State2 to zero, you'll see it no longer calculates a value for Pension 1 income taxable in state. If I add a move age like 99, then it calculates the value before the move age properly.

The issue seems to be that Excel values the result of AND(FALSE,FALSE) as FALSE. If I change the formula as follows it seems to work correctly:

Code: Select all

=ROUND(IF(OR(J$4<State_Move_Age,State_Move_Age=0),Pen_Tax_State*J$66,Pen_Tax_State2*J$66)/Round_To,0)*Round_To

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

Re: Retiree Portfolio Model

Post by BigFoot48 » Thu Feb 28, 2019 11:42 am

rts58 wrote:
Thu Feb 28, 2019 11:06 am
I see I was looking at the move age comparison wrong. However, I still think the formula is incorrect. In the example data if you change the State_Move_Age and Pen_Tax_State2 to zero, you'll see it no longer calculates a value for Pension 1 income taxable in state. If I add a move age like 99, then it calculates the value before the move age properly.
I have reviewed your finding and agree! Thank you very much. The correction will be included in 19.1 to be released tomorrow.
Retired | Two-time in top-10 in Bogleheads S&P500 contest; 13-time loser

rts58
Posts: 41
Joined: Sat Jul 16, 2016 10:35 am

Re: Retiree Portfolio Model

Post by rts58 » Thu Feb 28, 2019 1:10 pm

BigFoot48 wrote:
Thu Feb 28, 2019 11:42 am
I have reviewed your finding and agree! Thank you very much. The correction will be included in 19.1 to be released tomorrow.
No, thank you, this worksheet is sooo helpful. BTW, I made the change and saw my Base plan was much better than the Roth Conv plan. Then realized I had to make the change to the Base tab as well as Detail!

Thanks again

RoxieII
Posts: 62
Joined: Thu Dec 18, 2014 9:40 am

Re: Retiree Portfolio Model

Post by RoxieII » Thu Feb 28, 2019 1:29 pm

This is a wonderful tool. :beer

User avatar
munemaker
Posts: 4144
Joined: Sat Jan 18, 2014 6:14 pm

Re: Retiree Portfolio Model

Post by munemaker » Thu Feb 28, 2019 9:01 pm

rts58 wrote:
Thu Feb 28, 2019 1:10 pm
BTW, I made the change and saw my Base plan was much better than the Roth Conv plan. Then realized I had to make the change to the Base tab as well as Detail!
Can you please explain further? If you change the inputs, don't they change both the base and detail tabs? What did you change that affected the base plan but not the Roth conversion plan?

Post Reply