FI Calculations - The Uncontrolled Variables

[Karl_H]

I am setting up a spreadsheet to calculate income, expenses, and savings for the rest of my life.  The goal of this spreadsheet is to determine the earliest date that I could stop working and maintain my current lifestyle, without outliving my savings. 

I would like to get your input on the key assumptions that you have made for "significant" uncontrolled variables, including (but not limited to):

• CPI Inflation Rates
• Wage Inflation Rates (for Engineers)
• Bank Interest Rates
• Bond Market Performance
• Stock Market Dividends and Capital Gains
• Income Tax Rates
• Income From Social Security

I am currently 27 years old, and I expect to live another 60 years.  With my current P50 assumptions, I expect to no longer require employment income by age 35.  If I were to significantly cut expenses and change my lifestyle, I could be financially independent by age 30.  However, these predictions are highly dependent on forces beyond my control (listed above) and future lifestyle choices. 

To avoid biasing the responses, I will withold my own assumptions until others have responded :)[/list]

[arebelspy]

Unless you are doing a monte carlo sim, excel modeling like this is mostly useless, as it is too uniform.  Sequence of withdrawals is huge, and just assuming a flat rate ignores all that.

Use something like cfiresim.com to actually model your situation, using historical data (and firecalc.com - the old, worse version of it - to read their explanation of how a calculator like that works).

Example: stock market returns are 8% (ish), yet the SWR is about 4% due to sequence of withdrawals and periods of low returns/high inflation.

Hope I didn't burst your bubble.  :)

Spreadsheets are fun (I think I personally have about 7 related to FI, including one that I've had ongoing for about 3-4 and is on version 12 - and I only increment the version number at major changes), but for what you're trying to do, not the best method.  Seriously, check out cfiresim.com

[Kira]

To be honest I think you are trying to control too much in a wildly uncontrollable world.. it seems the best advice on the forum points towards setting your lifestyle and investments up in such a way that they are affected as little as possible by things like this. And some of these items on your list will specifically not apply to you if you're living MMM style!

• CPI Inflation Rates

- if you don't purchase, eat or drive like a typical American this will not really apply to you

• Wage Inflation Rates (for Engineers)

- this is so different between firms as to be useless, only the raise system at the place YOU work matters, and you can curate your career aggressively so you're always doing better than average anyway

• Bank Interest Rates

- this could affect you but control by the Fed Reserve and their thoughts on how it affects the market could change a lot over the next 60 years!

• Bond Market Performance

- could be an issue if bonds are a significant part of your investment strategy, but of course you can always change strategies throughout your life!

• Stock Market Dividends and Capital Gains

- again depends entirely on the choices you make if you are doing stock picking.. historical returns will not help you much

• Income Tax Rates

- live on less money, so you need to make less money, and you pay lower taxes. That seems more important than trying to predict the completely unpredictable

• Income From Social Security

- Most MMMers seem to believe either it won't be there or we'll get maybe half of what we should. Predict based on your level of doubt here.

If I may generalize.. your time is probably better spent thinking about how to cut expenses, make more money, and figuring out what kind of investments you're comfortable with. You cannot predict the future, all you can do is set yourself up that none of these changes will matter that much to you. (Unless you are a doomsday prepper. Then we cannot help you.) Most of us assume the US is going to keep ticking along in some fashion or another for the rest of our lives. This is why using the historical data sets reassures most people since the US has already gone through some pretty terrible years, and if by the model you can get through those years, you'd probably be fine in today's financial disasters.

I can definitely 100% tell you are an engineer and I am an anthropology major here to tell you that building a better model will not make the world more predictable. You are way better off spending your time working on your own situation than trying to simulate the unsimulatable.

[arebelspy]

In other words, excel is very precise.

But don't confuse precision with accuracy.

:)

[SnackDog]

This is an important exercise, in my view, as it can be very instructive. I recommend setting this up with a summary screen where you vary key inputs and instantly see how they affect graphs of future account balances, income and spending. The online tools are all too slow Nd awkward for proper analysis.

You may be overdoing the granularity, however. Start simple with an overall investment performance rate, for example rather than splitting out stocks, bonds and savings.

I use 3.5 for inflation, inflation+3% for investment appreciation, 4% for wage inflation, 4% for real estate appreciation. I use 6% for medical expense inflation. For SS I just use what their online report gives me at age 70. I use 30% income tax and 20% cap gains as we live in a high income tax state.

 I think it is important to use realistic but slightly conservative figures keeping in mind using a conservative value for all parameters will be unrealistically conservative overall. You want to shoot for a p90 not a p99. The reason you need to aim for a p90 is to account for black swan events: medical calamity, law suit, divorce, natural disaster, etc. Almost everyone I know has had one of these by age 60.

I vary all parameters, within reason, but also my retirement age to see how results look. A good tool will help one understand which variables are important.

My Current tool says we are ready and more work just brings larger income later but it is already more than we will spend.  My employer has me in golden handcuffs regarding pension and stock options. Health benefits also jump if I stay until a threshold of years of service and age.  All this converges on the same retirement age for me to max benefits and not bail out until mortgage debts are paid. To be honest, it suggests we maybe should be spending more!

[This_Is_My_Username]

I do similar caluclations for my job, and the short answer is to use our best guess given our current information.  And our best guess will be to use the long term historical average.



CPI Inflation Rates

Use the Federal Government Central Bank target rate.

Wage Inflation Rates (for Engineers)]

use your previous experience.  Assume that you will be promoted when you expect to be.

Bank Interest Rates

For term deposits?  use the historical average.  start with the current rate for 13-14, and slowly increase to the historical average over 2-3 years.

Bond Market Performance

use the historical average.  start with the current rate for 13-14, and slowly increase to the historical average over 2-3 years.


Stock Market Dividends and Capital Gains

use the historical average.  10% in my country. (australia)

Income Tax Rates

use current rates, assuming no changes.

income From Social Security

just make your best guess, because age 65(?) is so far away.  perhaps you should ignore this section because the impact will be minimal.

[Karl_H]

Everyone,

Thanks for your responses!

I wish I had a free Monte Carlo simulator that I could use for this purpose, but alas, I do not. 
cfiresim.com seems to be useful for evaluating extreme cases (target success rates of 90%-99%).  However, cfiresim.com's avoidance of income taxes appears to be a significant short-coming. 

Therefore, I feel that excel is still the most useful tool for modeling P50 cases for decisions with a financial consequence.  I would use cfiresim.com or a Monte Carlo simulator, before making the ultimate decision (retirement). 

Like most responders, I decided to use historical average inflation rates for P50 modeling.  I also assumed that current laws & government polcies would remain constant (and indexed to CPI inflation), with the exception of social security.   

I have attached a an example spreadsheet, for anyone else who would like to create a similar tool.  Please note that I have not put any effort into making it easier for other people to use.  For privacy, I have removed my personal information. 

•CPI Inflation Rates

Assumed that rates would return from current levels to the historical average (4%) in 3 years. 

•Wage Inflation Rates (for Engineers)

Assumed 10% increase next year, based on current year performance assessment, and 5% thereafter.  This is based on my past experience.

•Bank Interest Rates

Assumed that yields would return from current levels to the historical average (3%) within 5 years. 

•Bond Market Performance

Assumed that yields would return from current levels to the historical average (5%) within 5 years.

•Stock Market Dividends and Capital Gains

Dividends = 2.5% yield
Unrealized Capital Gains = CPI Inflation Rate + 2%
Realized Capital Gains = 0.5% of year-end holdings

•Income Tax Rates

Assumed 2013 US Federal Income Tax Rates.  Medicare and other taxes not accounted for.

•Income From Social Security

$7K/yr in today's money, indexed for inflation

[arebelspy]

I wish I had a free Monte Carlo simulator that I could use for this purpose, but alas, I do not. 

Use one of the online ones?

However, cfiresim.com's avoidance of income taxes appears to be a significant short-coming. 

Why not count the amount of income tax you expect to pay in your spending?

[Nords]

•CPI Inflation Rates
Assumed that rates would return from current levels to the historical average (4%) in 3 years. 
•Wage Inflation Rates (for Engineers)
Assumed 10% increase next year, based on current year performance assessment, and 5% thereafter.  This is based on my past experience.
•Bank Interest Rates
Assumed that yields would return from current levels to the historical average (3%) within 5 years. 
•Bond Market Performance
Assumed that yields would return from current levels to the historical average (5%) within 5 years.
•Stock Market Dividends and Capital Gains
Dividends = 2.5% yield
Unrealized Capital Gains = CPI Inflation Rate + 2%
Realized Capital Gains = 0.5% of year-end holdings

You live in a really nice world, and I'd like to live there too, but what relevance does this have to the future?

For example, Dimson & Marsh' "Triumph of the Optimists" claims that annual inflation over 20th century has been about 3%.  Inflation over the last 40 years has been about 5%.  Inflation last year was lower than either of those other two numbers.  Which number are you calling "historical"?  More importantly, how do you determine what inflation's going to be for the rest of your life?  Are you willing to adjust that assumption for factors like the gold standard, two world wars, the Bretton Woods agreement, a petroleum recession, accounting changes, modern financial engineering, globalization, and computerized trading markets?  And what CPI is that fabled government using:  CPI-W, CPI-U, or chained CPI?  The Wal-Mart effect has impacted CPI over the last 20 years by about 15%-- does that need to be accounted for too?

The reason posters are recommending calculators is so that you can run thousands of simulations of those uncontrollable variables.  Then you want to examine the situations where the variables have all turned against you so that you can figure out what you'd have to correct in that situation, and by how much.

cFIRESim is avoiding estimating taxes as much as it's avoiding estimating your electric bill-- everyone has a different one, even when everyone is subject to the same laws.  For example you could program federal & state & local tax rates into the software, but I pay very low federal taxes and no state taxes.  And what about property taxes or vehicle registration taxes-- should we program all taxes into calculators?  So I'd rather add my taxes to my other budget categories and not have the simulator choose it for me.

Pick all the numbers you want, but a decade from now you'll be starting all over again at picking new numbers.  Maybe someday you and arebelspy will build the ultimate spreadsheet, but you could also use today's tools to design your life instead of starting all over again at designing your own tools.  Perhaps a more productive use of your analysis would be designing a portfolio asset allocation and a couple of different spending levels which would handle a wide variance of the above factors, and then see how sensitive your assets and your plans are to the swings of those factors.

If I was in your position (which I was) then I'd be more focused on estimating my spending and on having a low-cost diversified portfolio of index funds that's not absolutely destroyed by a decade of any one of the above factors.  But when I was in your position (in 1987) then I also tried to design a better spreadsheet.  The benefit of the design exercise was that it helped me convince myself that I was wasting my time.

Todd Tresidder says it better than I do:
http://financialmentor.com/retirement-planning/early-retirement/smart-alternative/8543
http://financialmentor.com/free-articles/retirement-planning/how-much-to-retire/why-retirement-calculators-cant-be-trusted

[SnackDog]

The huge advantage of doing it all in Excel is that you can customize it and control it all to your situation. It is not daunting at all. Just whack years in the columns and start adding rows for income and outflow as well as assets. I then add a summary sheet where I can adjust all the variables and see the results instantly on several graphs of future spending, income, taxes, net worth, etc. The ability to play with the variables and see the results from the model is very instructive. You are correct that the model is wrong, but the behavior is not.  One can begin to bracket future realities.  All these questions on the forum about investing, home purchase, etc can be answered instantly in terms of net worth at any future date.

Inflation in the past century has had entire decades as high as 9% and as low as 2%.  Inflation with economic growth is no problem. Stagflation, however, will ruin us all. If the stock market flat lines while inflation goes on a tear, you are ruined, particularly without hard assets like real estate or commodities.