Join Early Retirement Today
Reply
 
Thread Tools Display Modes
My Lessons Learned and why everyone should build a custom model with Excel
Old 07-02-2021, 10:27 AM   #1
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
My Lessons Learned and why everyone should build a custom model with Excel

I know first post so take it for what it is worth. I also apologize in advance for any spelling errors

Lesson learned will actually come from how well my model tracks to reality in the future. This is about how I learned to create and update my model to hopefully cover the basis and help me make decisions ahead of time.

First off I am not a Tax expert, Financial expert and make no claims to the validity of what I am posting below. My goal here is to share the lessons learned I have from developing my own model over several years and illustrate its all about finding a balance based on your unique set of variables. You can do all this from an Excel spreadsheet fully understanding your choices rather than paying a financial planner to do this wondering if they considered everything they should

I have been modeling my retirement for a while now. Continually updating my excel spreadsheet as I saw things I didn’t consider. I am not going to share my spreadsheet as its pretty custom to my situation as yours should be. I will be retiring the end of the year

The biggest decision besides when to retire for me is what to do about my IRA for Roth Conversions and when to take Social Security

My goal is to maintain our current lifestyle and fortunately I have a pension that starts this month at 60 that helps that. That makes the choice as to when to take social security more flexible. I am down to 67 or 70

Planning Expenses:
• Before I created the detailed spreadsheet I reviewed 4 years of expenses.
• I then removed Mortgage (house paid off), 401K contributions, Health Insurance and taxes
• I then added in the Health Insurance cost I have from my employer for retiree Insurance. Just under $20K, very expensive. I wont qualify for any ACA subsidies
• II then added in other items like adding to the travel budget
• I then used 2021 Tax rates to estimate 2022 taxes
• I took the 4 years of adjusted expenses and added in Taxes and Health insurance and have a range of expenses for our current lifestyle.
• One of the years was much higher and went back in and removed wedding costs for my daughter that I hadn’t caught the first time.
• This resulted in a range that was +/- 10% from the average and so used these for planning
• I rounded up and down to provide 4 expense ranges that were equally separated but bounded the range. NOT The Graphs at the bottom
• I added these to the spread sheet column and escalated every year for inflation. I used 2%, but most others would likely do 3%. Its what you feel comfortable with reviewing what you spend money on
• Then I looked forward and how things would modify my expenses, Medicare at 65 will reduce Health care by $14K per what I have found, maybe more and I don’t expect to be doing all my same hobbies and traveling as much after 85, so slightly reduced income in that year
• This resulted in a 40 year estimation of my expenses over 4 expense rates. I have a goal rate (Expense per year) to work to, but wanted to make sure I had margin

Taxes:
• It’s hard to know if Tax Rates will change, I assumed they would not
• Tax Brackets due change. They go up 1 – 1.5% a year based on my quick estimation
• I created 4 columns for the 10, 12, 22 and 24% tax brackets and increased each year by 1%
• This was a later addition to my spreadsheet and because these brackets change the amount to convert to Roth staying in one bracket changes. Also, the impacts of RMD’s change based on where the tax brackets land. Again everyone’s situation is different that is why you need to develop and customize to your situation
• My state is a flat tax so I just modeled that in the expense

Return on Investment:
• This is the area that provides the greatest variance and one you have little control over in reality
• I see some like a 6% return. I have averaged over 12% the last 15 years, but I intend to go more conservative
• This is what I picked and you likely will pick something else. I modeled 1% (CD Rates), 2.5% and 3.9%. 3.9% was what I saw as a conservative long term rate that several “experts” that was a safe bet. I did not cover negative returns, which is a risk. I will just need to have enough in cash to work through a downturn
• I also did a stress test at 0% and verified my money would last but at a slightly reduced income until 95
• To be extra conservative, I did not consider selling my house. Again your situation will be different

Roth Conversions:
• At a high level, converting all the IRA into a Roth sounded great. I would eliminate the need for RMDs and always be in the 12% tax bracket
• In my situation, the only time this worked out to be the best option is if the ROI was 1% or lower
• That is because your ROI growing your savings is almost nil and it ends up being down to expenses
• At higher ROIs, doing the mass conversion up front lowered the amount and time my money would last
• Doing no conversions didn’t work either as the RMDs drove me to a higher tax bracket
• I modeled two types of Roth conversions: MIN: capped to the 22% bracket, MAX: Capped to the 24% tax bracket. I so no reason to model to the 32% bracket
• This why having the tax brackets escalate each year by 1% is important as it changes the thresholds for MIN, MAX or however you want to do it
• For Min, I modeled to the 22% bracket until RMDs started. I will reevaluate if I should continue them after RMDs start. The RMDs of course reduce the amount to convert to get to the 22% bracket. Being 12 years from RMD’s I figured this was good enough and my model shows the answer is different at 72 based on the ROI I actually achieve
• For Max, I did conversions to the 22% bracket until they conversion was complete resulting in no RMDs, but a significantly reduce investment balance to gain returns on
• For Max, I had a significantly reduced tax bill lowering expenses, but had a major smaller investment base to pull it from
• For Min, I had a lower tax bill than no conversions, a small RMD that fit within my expenses. Meaning I did not have to have more income than I needed
• For no conversions, the RMD drove me to have more income then I needed with the pension and social security and as a result a higher tax bil


Social Security:
• I came down to two points after running through models early on, 67 and 70
• Early models clearly showed SS at 70 is the right answer
• When you factor in Roth conversions, ROI and RMDs, in some situations 67 is actually better until about age 95 (Changes based on variables)
• I used my pension, RMDs and 85% of social security to define the taxable portion of my income along as anything else I may or may not need will now come from the ROTH
• In some cases in my situation, the Pension, RMDs and SS at 70 drove my taxable income above what I needed where as taking it at 67 would not. In these situations; Pension, RMDs and SS @67 required almost nothing from my Roth which is able to sit and grow creating a larger investment balance
• This sweet spot all changes based on your IRA to Roth situation, Resultant RMDs, having a pension and ROI

Conclusion:
It all comes down to finding the right balance for your situation. Using FireCALC is great to do a sanity check, but it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you

Having your own model allows you to go in and update for progress and make any modifications. I expect the answer for 67 to 70 may change based on actual performance and expenses and I know I don’t have to make that decision for a few years.

Based on my situation and models, I have decided to do min Roth conversions to the 22% bracket until age 72. At that time I can always make the decision to continue them on top of RMDs. Or, if things significantly change with a higher ROI than I expect, I may decide to switch to the max conversions (24% bracket)

I am no tax or financial expert. Just an engineer who is use to modeling all kinds of scenarios. Please don’t take anything I said her as gospel as to what you should do. My entire point in sharing this si everyone’s situation is different and using Excel to model your unique situation will help YOU find the right balance.

The bottom 3 graphs are of my situation to illustrate how things vary. For the 1% ROI you can see that at age 85, SS at 70 and Max Roth conversion is the best option because investment isn't growing so lower Tax expense is the better outcome for funds lasting longer

the 2.5% graph shows that the ROI is significant enough to outweigh the benefit of the lower tax of the full conversions. It shows SS at 70 overtakes SS at 67 at about age 86. No Roth conversions is the worst option as RMDs drive income above what is need

The 3.9% Graph shows the min ROTH option is the better solution as it has more base to grow with the higher ROI. Also the Higher ROI moves the date SS@70 overtakes SS@67 to about 93 years old. This shows even higher rates will move that later

Again these graphs are MY SITUATION. The answers will be different for you and to just illustrate why you should develop your own model and tweak it to evaluate multiple scenarios to find the sweet spot

Sorry for the long first post
Attached Images
File Type: jpg 1.JPG (71.6 KB, 316 views)
File Type: jpg 25.JPG (70.9 KB, 266 views)
File Type: jpg 39.JPG (87.0 KB, 260 views)
Romer is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 07-02-2021, 10:41 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Aug 2016
Location: Northern Virginia
Posts: 7,545
hi and welcome.

I do agree that doing your own spreadsheet is the way to go. It also avoids using a planner's "black box". Flipside is you have to be really diligent, but sounds like you are that type, as I am.

I think you can fool yourself into thinking you can plan this out with precision. You may optimize SS if you know when you will pass. Otherwise it is more based on conditions on the ground and and overall understanding of how the numbers work over time.

You make an excellent point on tax brackets, but you probably want to model current tax rates expiring for 2026. That in essence is current law.

You may also want to model a cutback in SS when funds start running short. Some folks do that.

One other advantage of doing your own models is it gets you really familiar with the mechanics and keeps you close to the numbers. You likely will become rather expert on your taxes.

Again, welcome and wish you luck on your models and your retirement.
Montecfo is offline   Reply With Quote
Old 07-02-2021, 10:49 AM   #3
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
Quote:
Originally Posted by Montecfo View Post
hi and welcome.

I do agree that doing your own spreadsheet is the way to go. It also avoids using a planner's "black box". Flipside is you have to be really diligent, but sounds like you are that type, as I am.

I think you can fool yourself into thinking you can plan this out with precision. You may optimize SS if you know when you will pass. Otherwise it is more based on conditions on the ground and and overall understanding of how the numbers work over time.

You make an excellent point on tax brackets, but you probably want to model current tax rates expiring for 2026. That in essence is current law.

You may also want to model a cutback in SS when funds start running short. Some folks do that.

One other advantage of doing your own models is it gets you really familiar with the mechanics and keeps you close to the numbers. You likely will become rather expert on your taxes.

Again, welcome and wish you luck on your models and your retirement.
Thanks for the reply and you are correct. I have no illusions that I have planned this out with precision. I have modeled my income growth over the last 20 years and every year my model was wrong and I performed better.

I see this as a starting point to work from that needs to be maintained, and updated based on changing conditions

I need to monitor my progress along with adjustments to determine if I need to modify anything as I move forward

The only real conclusion I feel I can take from my model is I can afford to retire (Yay) and next year I should do a Roth conversion to the 22% tax bracket. I will have to wait until the end of next year to decide what to do with the year after and won't make a decision on Social Security until I get close to 67

But the thing about the plan is it isnt going to be the way things turn out. It is just a tool to be used pro-actively year to year to manage things going forward. That is another reason to develop your own as it forces you to understand the data and actually be able to modify your plans
Romer is offline   Reply With Quote
Old 07-02-2021, 10:49 AM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
RunningBum's Avatar
 
Join Date: Jun 2007
Posts: 13,202
For expenses, did you factor in large irregular expenses you might not have hit in those last 4 years? Like, replacing a car? Repainting your house or other home maintenance? By 85 you might be in an independent or assisted/memory living situation, which may change your expenses, possibly by a lot.

Return on investment and inflation rates are almost certainly not going to be a straight line, whether high or low. Averaging out to 0% ROI may be a valid worst case for returns, but it looks like you didn't vary inflation. This is where tools like FIRECALC are useful, for showing how you'd do in historical situations.

My calcs have taxes reverting to higher brackets in 2026. This may not happen but I'd rather be conservative with my estimates. I won't be paying that much in fed taxes anyway so it's not a big impact.

I'm not going to get into the Roth conversion or when to take SS decisions. Just use Advanced Search and search for Roth in a thread title and look for threads with high post counts. SS is a little harder because search doesn't return results for 2 character searches. I'm worn out from those previous threads and I'm set with my plans. Will try not to get sucked in by other responses.
RunningBum is offline   Reply With Quote
Old 07-02-2021, 11:01 AM   #5
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
I did run Firecalc and came out at 100%

My expense level is high maintaining my current lifestyle with margin. My selected expense level has $30K of margin. My situation is different and I am comfortable with what I had

Those are all good things people will consider. I commented on the tax brackets and decided to not change them and also on the Inflation value I used

I also was not trying to suck you into another thread. I am glad you have a plan and path forward. My lessons learned are intended for those who do not and maybe what I have done will help them

I did read the roth conversion threads. Some good info in there, but it all depends on your situation
Romer is offline   Reply With Quote
Old 07-02-2021, 11:03 AM   #6
Recycles dryer sheets
 
Join Date: Feb 2010
Posts: 429
Hi Romer and Montecfo. I know the basics of how to use Excel, but I am not an expert user. Can you tell me what variables you use in your columns and rows, and the appropriate equations, for the Excel spreadsheets that make up your custom models? Thanks.
nico08 is offline   Reply With Quote
Old 07-02-2021, 11:26 AM   #7
Moderator Emeritus
aja8888's Avatar
 
Join Date: Apr 2011
Location: Conroe, Texas
Posts: 18,645
I'll be 78 in a couple of months and I agree setting goals and planning is a good exercise, but in the end, "Nobody Knows Nuthin"....LOL!

I've had more unplanned surprises along the way....but glad I had a "contingency plan"!
__________________
*********Go Astros!*********
aja8888 is offline   Reply With Quote
Old 07-02-2021, 11:27 AM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Midpack's Avatar
 
Join Date: Jan 2008
Location: NC
Posts: 21,204
Whatever you need to be comfortable with your plan. I’m also an ex-Engineer and I’ve used spreadsheets daily since long before Excel. Building elaborate spreadsheets can be a good exercise (was/is for me), if only to convince the user that retirement planning is an axe, not a scalpel - and an elaborate spreadsheet won’t change that. No matter how much you put into it there are so many unknowns acting that you can’t account for (e.g. SORR/returns, inflation, HC inflation, tax rates, longevity, geopolitics, unexpected expenses, etc.) over a period of 30 years +/-!

There’s a good reason FIRECALC results are so variable, and it’s not because “it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you.” I still maintain detailed spreadsheets on spending/budget, net worth and short term tax planning, but I’ve come to believe running something like FIRECALC every 5 years or so, with considerable safety factors and a plan B/C/D, is as good a predictor as anything. FIRECALC does allow for quite a few inputs, though not taxes understandably. YMMV

__________________
No one agrees with other people's opinions; they merely agree with their own opinions -- expressed by somebody else. Sydney Tremayne
Retired Jun 2011 at age 57

Target AA: 50% equity funds / 45% bonds / 5% cash
Target WR: Approx 1.5% Approx 20% SI (secure income, SS only)
Midpack is online now   Reply With Quote
Old 07-02-2021, 11:41 AM   #9
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
Quote:
Originally Posted by nico08 View Post
Hi Romer and Montecfo. I know the basics of how to use Excel, but I am not an expert user. Can you tell me what variables you use in your columns and rows, and the appropriate equations, for the Excel spreadsheets that make up your custom models? Thanks.
Probably the one that took me the longest time to get right was to calculate the tax in an equation that worked every year
You can change these based on the potential; for them to increase in 2026 based on current law

=IF(K7-D7<0,(0),(K7-D7))*0.22+(C7*0.1)+((IF(V7>D7,D7,K7)-C7)*0.12)+((K7-20000)*0.0465)

K7= My calculation column for Taxable income
C7= 22% Bracket escalated every year by 1%
D7= 12% Bracket escalated every year by 1%
E7=24% Bracket escalated every year by 1%
F7 = 22% Bracket escalated every year by 1% (I know its out of order)
The last part is for Colorado as they take off the first 20K and then have a flat rate of 4.65%

The c7 times .1 is to cover the base 10% tax before the 12% bracket starts

The =IF(K7-D7<0,(0),(K7-D7)) is pretty useful. Essentially IF K7-D7 is negative then it will use 0, otherwise it uses the equation. The negative values were messing me up
Romer is offline   Reply With Quote
Old 07-02-2021, 11:44 AM   #10
Moderator
rodi's Avatar
 
Join Date: Apr 2012
Location: San Diego
Posts: 14,169
The OP has done a great job of figuring out spending and modelling it for retirement.

I used a variety of calculators... Including a fairly complicated spreadsheet that I created for various "what if" scenarios.

One of the things I did was similar to the OP - Take my gross pay, subtract out the things that wouldn't apply in retirement (401k contributions, employer health care premiums, SS and medicare contributions) Add in the things that would be new in retirement (healthcare premiums on the open market, bigger travel budget). That gave me my working budget (SWAG) for retirement planning. I then used some other calculators that detail spending to see if I came up with similar numbers. After a few iterations of figuring out what I missed, I had the same ballpark number from all sources as to what I needed to spend in retirement.

Here are the calculators I used.

Firecalc is good for showing how market variations, asset allocation, and longevity can impact your plan.

Quicken lifetime planner is good for figuring out typical, but lumpy expense (college tuition, wedding expenses for kids, etc.) but it's deterministic... you choose the rate of return and the rate of inflation... So not real world with variations. But it's great for making you think about your expenses.

Fidelity Retirement Planner can be very detailed - you can break out expenses that inflate at a higher rate than everything else (healthcare, college, for example). You can be as detailed as you want on expected expenses. Or you can just input a number and have the same inflation applied to the total and hope your number was correct.

I found the usefulness to be in stress testing my plan... making sure I'd thought of most things, and had a plan that accounted for market changes, spending changes, etc.
__________________
Retired June 2014. No longer an enginerd - now I'm just a nerd.
micro pensions 6%, rental income 20%
rodi is offline   Reply With Quote
Old 07-02-2021, 12:01 PM   #11
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
Quote:
Originally Posted by Midpack View Post
Whatever you need to be comfortable with your plan. I’m also an ex-Engineer and I’ve used spreadsheets daily since long before Excel. Building elaborate spreadsheets can be a good exercise (was/is for me), if only to convince the user that retirement planning is an axe, not a scalpel - and an elaborate spreadsheet won’t change that. No matter how much you put into it there are so many unknowns acting that you can’t account for (e.g. SORR/returns, inflation, HC inflation, tax rates, longevity, geopolitics, unexpected expenses, etc.) over a period of 30 years +/-!

There’s a good reason FIRECALC results are so variable, and it’s not because “it doesn’t allow you to fully model your unique situation, tweaking individual variables to find the best balance and sweet spot for you.” I still maintain detailed spreadsheets on spending/budget, net worth and short term tax planning, but I’ve come to believe running something like FIRECALC every 5 years or so, with considerable safety factors and a plan B/C/D, is as good a predictor as anything. FIRECALC does allow for quite a few inputs, though not taxes understandably. YMMV

I agree completely

Firecalc did not allow me to model taxes or adjust expenses base on my situation like change in Health care costs from 60-65 or a reduction based on taxes or RMD values and playing with my Roth conversion to see impacts


Firecalc is a good tool to use
Romer is offline   Reply With Quote
Old 07-02-2021, 01:12 PM   #12
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Midpack's Avatar
 
Join Date: Jan 2008
Location: NC
Posts: 21,204
Quote:
Originally Posted by Romer View Post
Firecalc did not allow me to model taxes or adjust expenses base on my situation like change in Health care costs from 60-65 or a reduction based on taxes or RMD values and playing with my Roth conversion to see impacts.
FIRECALC doesn’t do taxes because no one knows how they’ll play out over the next 30 years. Users just need to treat taxes as another expense, including RMD and/or Roth conversion impacts. That’s a much easier DIY spreadsheet exercise.

And under the Spending Model tab “FIREcalc supporters can make yearly spending revisions throughout the duration of their plan” - so you have lots of flexibility to model spending any way you’d like (pic below). There’s no minimum donation for supporters.

There are many features in FIRECALC that users don’t realize are there, or don’t understand how they can be used to model all sorts of situations. The authors were incredibly clever, not fully appreciated by most.

Again I learned a lot building complex spreadsheets, so it wasn’t time wasted by any means, I learned a lot. But without something to model SORR variation, I didn’t see any spreadsheet as useful for probabilities - which is the crux of planning. Whatever happens, we all know there won’t be any straight line returns, inflation, taxes, etc.

I still have the spreadsheet I did in 2009 that modeled everything (while still at my peak Excel skills), with tons of input variables, I spent months developing it. Looking back there aren’t any built in errors, but it wasn’t even close in predicting our path to 2021. I opened it a few days ago, and just laughed at how naive I was to believe I could be more precise. Look again at the FIRECALC results charts, that’s the unknown variation you’re trying to tame…
Attached Images
File Type: jpg EA1E2812-670A-4696-B607-2D1C40660001.jpg (324.9 KB, 147 views)
__________________
No one agrees with other people's opinions; they merely agree with their own opinions -- expressed by somebody else. Sydney Tremayne
Retired Jun 2011 at age 57

Target AA: 50% equity funds / 45% bonds / 5% cash
Target WR: Approx 1.5% Approx 20% SI (secure income, SS only)
Midpack is online now   Reply With Quote
Old 07-02-2021, 02:00 PM   #13
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
Thanks for highlighting the spending by year. I missed that one.

I am a car enthusiast. Why I am telling you this? I am a long time member and moderator for a Toyota Land Cruiser Forum. That is valuable resource and when I started really helped me get going. I was able to diagnose problems and incorporate modifications leveraging the lessons from others. As I grew more experienced, I gave back to the forum creating lots of how to's like a thread with instructions and pictures on how to rebuild a front axle. Learning and giving back is the beauty of these forums.

I have been researching and laying out my retirement plans for a long time. Lots of sources. I didnt find one place that meet my needs. Much of what I included in the first post came from iterations as I matured my model over several years.

I found this web site last month and found it to be a great resource.

My intent/objective with this thread was not to say everyone should do it this way, but to share what I learned along the path as maybe it will help someone who is just starting to dig in.

I did conclude that having a custom model forces you to better understand the interplay of all the factors and enables easier customization to your scenario. As mentioned, these need to be augmented with Firecalc or other options for a stress check

The internet is a 2 dimensional forum making communication of intent and meaning harder to gleam. I just wanted to state my objective with starting this thread which is likely of little value to those who already have models or sources of data they are happy with

This discussion has been valuable to me and opened up other ideas and thoughts to consider

Have a great 4th of July
Romer is offline   Reply With Quote
Old 07-02-2021, 02:27 PM   #14
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
OldShooter's Avatar
 
Join Date: Mar 2017
Location: City
Posts: 10,336
Comments from someone who has done about a jillion models.

Quote:
Originally Posted by Romer View Post
... =IF(K7-D7<0,(0),(K7-D7))*0.22+(C7*0.1)+((IF(V7>D7,D7,K7)-C7)*0.12)+((K7-20000)*0.0465) ...
Embedding parameters like 20000 and 0.0465 leads at best to inconvenience and at worst to errors. The parameter values are essentially invisible to someone using the sheet, thus subject to typos or worse, and they are difficult to find and update when things change. You will be better off putting all the parameters into a section of cells with labels, then referencing the parameter cell when you need to use the value. Then everything is very visible and easy to change. My rule is: Never embed a parameter in an equation; always use a cell reference or use table lookups if necessary.
__________________
Ignoramus et ignorabimus
OldShooter is offline   Reply With Quote
Old 07-02-2021, 04:04 PM   #15
Recycles dryer sheets
 
Join Date: Jan 2011
Location: Hilton Head Island
Posts: 325
I have done some detailed spreadsheets on when to take SSA benefits and learned that the ROI forecast had a significant impact...larger ROI's suggested to take SSA sooner. I assumed smaller ROI and therefore took SSA at age 70...now SSA covers most of our necessities. IRA's can handle the joys of retirement and the chunky expenses.

I had a hard time dealing with inflation, so I did everything in TODAY'S Dollars. I know SSA benefits will increase with inflation, as will most expenses. My mortgage (I decided to keep it at less than 3% interest), would not change, and my health care expenses would increase greater than expenses, so they may offset each other. My retiree healthcare has annual out of pocket maximums, so I have some protection there as well.

I assume ROI minus inflation would be about 3%...both numbers are out of my control, and I can adjust the 3% if I want to be more or less conservative.

To calculate my annual spending, I assume I will run out of funds at age 105, and then solve (Goal Seek) for how much I can spend each year. Our actual spending is always much below that number, but it gives me a max which is helpful. On RMD's, I will do Roth conversions to keep RMD's below this max spend...since I only want to eliminate an RMD's impacting my annual withdrawal (I hope that make sense).

I know I could get into a lot more detail, but there are too many variables that we cannot control...I just do not want to run out of funds while alive and I do not want to be a burden to my children.
levindb is online now   Reply With Quote
Old 07-02-2021, 05:45 PM   #16
Thinks s/he gets paid by the post
Out of Steam's Avatar
 
Join Date: Mar 2017
Posts: 1,636
Quote:
Originally Posted by OldShooter View Post
Comments from someone who has done about a jillion models.

Embedding parameters like 20000 and 0.0465 leads at best to inconvenience and at worst to errors. The parameter values are essentially invisible to someone using the sheet, thus subject to typos or worse, and they are difficult to find and update when things change. You will be better off putting all the parameters into a section of cells with labels, then referencing the parameter cell when you need to use the value. Then everything is very visible and easy to change. My rule is: Never embed a parameter in an equation; always use a cell reference or use table lookups if necessary.
Your rule is best practice for MS Excel spreadsheets.

I maintained a pension estimator spreadsheet for at least 5 years before I retired. Such parameters had to be repeatedly changed, and were best left visible in cells.

While I don't have a lifelong spreadsheet model for retirement income, I do have a cash flow model that extends through the expected arrival of our first Social Security payment in late 2022. I plan to extend it through the arrival of the second stream of payments in 2025.

After that, I expect to be able to meet expenses with no more than a 2% draw on retirement savings, though I'm perfectly willing to take 3% for more disposable income or savings outside of retirement accounts.
Out of Steam is offline   Reply With Quote
Old 07-02-2021, 05:46 PM   #17
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
jollystomper's Avatar
 
Join Date: Apr 2012
Posts: 6,132
I did something similar before I retired with Excel.

I modeled my expenses, looking back at 5 and 10 year averages (thank you, Quicken) to come up with a ballpark for my categories. Then adjusted by taking out expenses I no longer had (e.g college costs) and adding in additional expenses (e.g. additional car, travel, future needed repairs/improvements). This because my "target" retirement expenses that I used in various models (FIRECalc, Megacorp retirement planning tools. etc.).

I then did a five year projection, adjusted for inflation, of my overall expenses to determine the required cash flow. I used 3 and five 5 year projections because (a) that is the most I would consider having in cash to cover expenses, without the need to touch equities, and (b) 3-5 years from retirement would be the earliest I would look at taking SS (this was also something I modeled to project the yearly amounts and breakeven points for DW and I). This gave me an estimate of the cash I would build to have during retirement.

I did not look at Roth conversions before retirement, as our income made us ineligible for them. But once retired and becoming eligible, I built a model to project out the yearly impact of Roth conversions based on our expected income.

Other things I modeled where health insurance costs (ACA vs. Megacorp retiree subsidized/non-subsidized, with and without COBRA), federal and state taxes, etc.

In retirement I still have a "main" Excel workbook for both tracking/modeling some of these items. It currently contains about a dozen tabs (some are just constants for aid calculations, like tax tables).

In sum, my errors have been on the good side so far... overestimating expenses and underestimating income. Hey, I'm not perfect at this .
__________________
FIREd date: June 26, 2018 - "This Happy Feeling, Going Round and Round!" (GQ)
jollystomper is offline   Reply With Quote
Old 07-02-2021, 06:22 PM   #18
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Mar 2016
Posts: 8,968
I don't have a clue as to building a custom model with excel. I don't even know what excel is.

Oh, I know, it's the little one on the right -
RobbieB is offline   Reply With Quote
Old 07-04-2021, 07:03 PM   #19
Recycles dryer sheets
Romer's Avatar
 
Join Date: Jun 2021
Location: Centennial
Posts: 415
And I thought I had a plan

This site has already helped me significantly

I bought the recommended Jane Bryant Quinn book on how to make your retirement last and read the section on spousal social security benefits. First I was confused because I thought it was talking about death benefits and that I thought was 100%

I reread it and searched this site and learned something I had no idea about

Here I thought it was my benefits plus my wifes benefits. Hers calculate out to 25% of mine based on her work history

The spouse benefit taken after FRA will add $10K in income that I wasn't expecting and I am not use to good surprises from the government

Per a recommendation I saw here I went to https://opensocialsecurity.com/

It provided the following strategy
  • your spouse files for his/her retirement benefit to begin 9/2026, at age 65 and 5 months.
  • You file for your retirement benefit to begin 6/2031, at age 70 and 0 months.
  • Your spouse files for his/her spousal benefit to begin 6/2031, at age 70 and 2 month

I will have to research that more because from what I read I thought she should wait until FRA at 67 so she gets the full 50% when I file

BUT a new lesson learned
__________________
FIRE'd March 2022
Romer is offline   Reply With Quote
Old 07-04-2021, 09:53 PM   #20
Thinks s/he gets paid by the post
martyp's Avatar
 
Join Date: Sep 2010
Location: Thailand countryside, Sisaket province
Posts: 1,331
i built my own spreadsheet to decide whether I could retire or not. I was never one to track my expenses because my year to year expenses were the same year to year and I always had savings to cover any unexpected expense. What I learned by building the spreadsheet was that there were 3 categories of expenses:

1. Expenses that occurred monthly: mortgage, phone, internet, groceries, gas, savings, etc.
2. Expenses that were regular but intermittent: these were mostly various insurance premiums but also vehicle registration and taxes.
3. Expenses that are unexpected (though planned for): A new roof, a new washing machine or water heater, etc. I looked at these expenses as a few occurances over a period of say, three years.

I started tracking my expenses in detail in the few months before I left work and then continued for two more years at which point I wasn't learning anything new. I built the spreadsheet out for 30 years and could include when I paid off my mortgage and when I started Social Security. I could also play what-if by adjusting various interested rates and pension cost of living adjustments. In reality though I don't have confidence in what the world is going to look like more than 5 years out. (Just consider the last 2 years!). I estimate a lifespan from my family health history and mine. Pick a number, any number.
__________________
Happy, Wild, and Free
martyp is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lessons Learned from Urban Meyer ronocnikral Young Dreamers 14 12-10-2010 01:47 PM
Anyone worked with an Architect to build custom house? Amethyst Other topics 14 03-04-2010 06:31 PM
Lessons learned from the debacle brewer12345 FIRE and Money 120 08-05-2009 04:19 PM
Lessons Learned popowich Young Dreamers 24 07-14-2008 11:16 AM

» Quick Links

 
All times are GMT -6. The time now is 03:02 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2024, vBulletin Solutions, Inc.