Spread sheets for Personal Financial Planning

Financial Independence/Retire Early -- Learn How!
Post Reply
therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Spread sheets for Personal Financial Planning

Post by therealchips » Mon Sep 22, 2003 10:40 am

Have any of you developed spread sheets for your personal financial planning? Do you think you might? I have had a lot of use from mine and I'd be happy to discuss the matter here with any of you.

Here are some of the inputs to my spread sheet:

Current total value of all tax-deferred accounts
Current total value of all taxable accounts
The basis or acquisition cost in the taxable accounts
A line for each year of my age, and for that year, predictions for the rate of return on my investments and for an inflation rate
For each year, my expected corporate pension (started already, but with no COLA) and social security (starting soon and including COLA)
Break-points and income tax rates for the various income tax brackets, indexed for inflation

There are other inputs, but I'll leave that for later.

Here are some of the outputs from my spread sheet:

The withdrawal amount that stabilizes the retirement stash as nearly as possible
For each year, the percent of the retirement stash withdrawn for living expenses and income taxes
Updated (predicted) values for the taxable and tax-deferred accounts each year
Estimates of income taxes for each year
Updated values for the basis in the taxable account each year
Gee-whiz graphs that show the history and the predictions for growth in the taxable and tax-deferred accounts, both inflation-corrected and in current dollars.
Graphs that show the relative importance of various income streams (pension, Social Security, dividends in the taxable accounts, realized capital gains, withdrawals from the tax-deferred accounts) and the allocation of those streams over the years to three uses: living expenses, income tax, and repairing the damage that inflation does to the retirement stash (i. e., "savings for retirement during retirement").

From previous experience at TMF, I suspect this is not a topic of burning interest. That's too bad. It would at least provide a diversion and maybe some insights. I learned, for example, that the income taxes on my mandatory IRA withdrawals are a major and inescapable feature of my future financial life. More than that, I developed estimates of just how large those taxes will be and what I might do in preparation. I have taken a smaller standard of living in my fifties and sixties specifically to "save up" for paying those taxes in my seventies and later while stabilizing my after-tax after-inflation standard of living. (Afterthought: The income taxes on IRA withdrawals are "inescapable" unless I give them to charity or otherwise rack up massive deductions.)
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar » Mon Sep 22, 2003 11:51 am

Have any of you developed spread sheets for your personal financial planning? Do you think you might?


I have not yet. But I definitely want/need to. We use Microsoft Money in managing some accounts, but it is insufficient for the analysis I want to do.

It seems like you have developed some very sophisticated stuff. :shock:

I probably need to start with a detailed budget spreadsheet. :oops: My wife and I have tackled the problem sporadically, but still need to work on it.
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus

MacDuff
* Rookie
Posts: 31
Joined: Wed Jun 18, 2003 8:20 pm
Location: Centralia, WA

Re: Spread sheets for Personal Financial Planning

Post by MacDuff » Mon Sep 22, 2003 1:05 pm

therealchips wrote: Have any of you developed spread sheets for your personal financial planning? Do you think you might? I have had a lot of use from mine and I'd be happy to discuss the matter here with any of you.


This would be very interesting to me. I like Excel, and although I am only medium expert in its use, I did my fairly complex taxes with it for several years when I was too frustrated with Turbo-You-Know to continue with it. I also use a Gordon equation spreadsheet to help me look at some Closed End Funds, etc.

Your use sounds very helpful. For one thing, it may help one to decide when to start taking Social Security. For another, I wonder if sometimes it might not be worthwhile to take advantage of low market prices (when available) to convert some Regular IRA assets to Roth assets. It occurred to me that an excellent use of a Roth might be to house TIPs. Only way I can think of to avoid being taxed on inflationary gains, a condition that raises my annoyance level considerably.

Do you have your spreadsheet-less data of course- available somewhere for download?

Mac

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Post by therealchips » Mon Sep 22, 2003 2:41 pm

Hi, Mac,

Your two suggested uses of the spread sheet are both valuable. I have exercised mine for both those questions. For me, one answer was to start Social Security at age 65 and 4 months, rather than any time sooner. That result comes as no surprise though, because I was looking at living to age 95. The other question was converting some IRA assets to Roth. My spread sheet says that converting any amount would reduce my "final" estate except maybe for small conversions this year and next to take advantage of my low tax bracket prior to the beginning of social security. I won't know for sure until I see what my year-end distributions are from my mutual funds. Even that small conversion was not advantageous to me until this latest revision in the income tax law.

The worst thing about my spread sheet is its reliance on two input parameters -- market return and inflation -- for each year of retirement. (These are easily varied between runs of the spread sheet for sensitivy analysis. ) I take those as fixed values, without using actual historical data or Monte Carlo with mean and variation in those returns derived from the historical data. To remedy this defect in part, I have also included the "acid test" data that Ataloss told me about. Under my present withdrawal plan, I would first get poorer and then quite a bit richer if the market repeats those acidic results and I keep my standard of living constant. In that sense, my present plan is even more pessimistic than the acid test.

Ah, on the subject of a sanitized or de-personalized version of my spreadsheet. I did that once at TMF a few years back and accidentally left in some mildly personal data. Give me some time to do it again, and more completely this time. Then I can email you a copy if you first email me an address. The other approach -- putting it up somewhere public so that anyone can download it -- is beyond my present technical skill. You are welcome to it if I can sanitize it.

In any case, my spread sheet would not serve any more purpose for you than being a disposable prototype, or maybe something to mine for ideas to put into your own spread sheet. One of the best ideas in it, I think, is keeping track of all future numbers in both current and inflation-corrected values. When both a present in a table or graph, the reader does not have to wonder whether the numbers are before or after inflation corrections. Another good idea is having the spread sheet estimating income taxes (for years out into the future, even though the law will change in unpredictable ways). Most financial planning software asks the user to input an average or marginal income tax rate which, I think, should be an output from the software, not an input. The graphs are informative, too. The very best idea in my spread sheet is its ability to make use of Solver, the built in optimizer of Excel, to determine how much I can spend after income taxes and inflation repair while more or less stabilizing the purchasing power of the retirement capital.

The truth is :oops: that my spread sheet is tailored to my own situation and has some obsolete, unused columns, so I am not proud to show it off in public. Being retired, I haven't felt like polishing it up for more general use, that being too much of a distraction from my heavy-duty loafing. :lol: Let me see what I can do . . .

I'm going to quit now before someone counts the number of words in this post . . . :)
Last edited by therealchips on Mon Sep 22, 2003 6:43 pm, edited 1 time in total.
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

User avatar
FMO
* Rookie
Posts: 30
Joined: Mon Nov 25, 2002 4:00 am

Re: Spread sheets for Personal Financial Planning

Post by FMO » Mon Sep 22, 2003 2:42 pm

therealchips wrote: Have any of you developed spread sheets for your personal financial planning? Do you think you might? I have had a lot of use from mine and I'd be happy to discuss the matter here with any of you.


I have developed several special purpose spreadsheets but the one I use the most is very comprehensive attempt to project as far as my anticipated retirement date. It accommodates all expected sources of income, all anticipated expenses and the value of all assets, to derive a fairly good projection of cash flows and net worth for the medium term. It adjusts for anticipated returns, taxes and inflation. At some point I plan on developing an extension to this effort to address the post-retirement period. Right now, I am loath to put forth the effort because there are simply too many large unknowns related to the status of my real estate holdings, including my personal residence and my retirement location. Perhaps some rainy weekend soon . . .
FMO

"The mark of a successful man is spending an entire day on the bank of a river without feeling guilty about it."

User avatar
FMO
* Rookie
Posts: 30
Joined: Mon Nov 25, 2002 4:00 am

Post by FMO » Mon Sep 22, 2003 2:51 pm

therealchips wrote: Hi, Mac,


Ah, on the subject of a sanitized or de-personalized version of my spreadsheet. I did that once at TMF a few years back and accidentally left in some mildly personal data. Give me some time to do it again, and more completely this time. Then I can email you a copy if you first email me an address. The other approach -- putting it up somewhere public so that anyone can download it -- is beyond my present technical skill. You are welcome to it if I can sanitize it.


I would be interested in seeing the result of your efforts as well. In developing several of my own spreadsheets, I have shamelessly borrowed upon the work of others on occassion to improve my own results. When you are ready, I will be happy to post it publicly for you.
FMO

"The mark of a successful man is spending an entire day on the bank of a river without feeling guilty about it."

peteyperson
**** Heavy Hitter
Posts: 525
Joined: Tue Nov 26, 2002 6:46 am

Post by peteyperson » Mon Sep 22, 2003 2:58 pm

FMO,

Don't you mean that following your theft (borrowed) ideas and methodologies from other wizard spreadsheet creators you'll be happy to help publish new better ideas so you can steal (borrow) those too?

:lol:

<cough> Did I just say that?!

Petey
FMO wrote:
therealchips wrote: Hi, Mac,


Ah, on the subject of a sanitized or de-personalized version of my spreadsheet. I did that once at TMF a few years back and accidentally left in some mildly personal data. Give me some time to do it again, and more completely this time. Then I can email you a copy if you first email me an address. The other approach -- putting it up somewhere public so that anyone can download it -- is beyond my present technical skill. You are welcome to it if I can sanitize it.


I would be interested in seeing the result of your efforts as well. In developing several of my own spreadsheets, I have shamelessly borrowed upon the work of others on occassion to improve my own results. When you are ready, I will be happy to post it publicly for you.

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Post by therealchips » Mon Sep 22, 2003 3:01 pm

Hi, FMO,

Thanks for the comment. I know you like spread sheets as much as I do. Your most-used spread sheet addresses only the accumulation phase while mine addresses only the retirement phase. If we had one that covers both, we could address questions such as this: How should I distribute my savings between a classic IRA, a Roth IRA, and taxable investments during accumulation in order to be as well off as possible during retirement, not just the day I retire? That question needs to consider real estate options, too. It would provide a basis for objective evaluation of the standard advice: put as much money into your 401k as it takes to get every penny of the company's matching funds. I did that, but I'm not sure it was the best thing I could have done with that money. I don't kick myself about it though because I could not have anticipated that the federal income tax rate on capital gains and dividends would drop to 15% and I was not sure I would move to a state with no income tax. (The formerly golden state of California taxes capital gains distributions from mutual funds as ordinary income at a rate of 9.3%. Shudder.)
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

User avatar
FMO
* Rookie
Posts: 30
Joined: Mon Nov 25, 2002 4:00 am

Post by FMO » Mon Sep 22, 2003 3:32 pm

peteyperson wrote: FMO,

Don't you mean that following your theft (borrowed) ideas and methodologies from other wizard spreadsheet creators you'll be happy to help publish new better ideas so you can steal (borrow) those too?

:lol:

<cough> Did I just say that?!

Petey



I would consider it a mutually beneficial exchange. :wink:

Actually, I have freely post many of my real estate spreadsheets for others to use. I recently received an inquiry from a real estate-related radio show in Florida that wanted to market some of my work. I plan on politlely turned them down since anything I produce, I consider a labor of love only. Of course any ideas I gain from others are obtained by permision and only for the personal use of myself and friends.
FMO

"The mark of a successful man is spending an entire day on the bank of a river without feeling guilty about it."

User avatar
FMO
* Rookie
Posts: 30
Joined: Mon Nov 25, 2002 4:00 am

Post by FMO » Mon Sep 22, 2003 3:44 pm

therealchips wrote: Hi, FMO,

Thanks for the comment. I know you like spread sheets as much as I do. Your most-used spread sheet addresses only the accumulation phase while mine addresses only the retirement phase. If we had one that covers both, we could address questions such as this: How should I distribute my savings between a classic IRA, a Roth IRA, and taxable investments during accumulation in order to be as well off as possible during retirement, not just the day I retire? That question needs to consider real estate options, too. It would provide a basis for objective evaluation of the standard advice: put as much money into your 401k as it takes to get every penny of the company's matching funds. I did that, but I'm not sure it was the best thing I could have done with that money. I don't kick myself about it though because I could not have anticipated that the federal income tax rate on capital gains and dividends would drop to 15% and I was not sure I would move to a state with no income tax. (The formerly golden state of California taxes capital gains distributions from mutual funds as ordinary income at a rate of 9.3%. Shudder.)


I know this may sound goofy, but I so enjoy designing a good useful spreadsheet it reminds me of the old saying something to the effect that the chase is more fun than the capture. It's like real estate. I love looking for properties, evaluating their investment potential and negotiating the final transactions. The actual ownership, as productive as it may be, I consider boring. The prospect of designing a good post-retirement spreadsheet holds forth the golden promise of being a thoroughly engaging, entertaining and instructive exercise. One that I am not to big of a hurry to begin, for once begun it will occupy me nonstop until something close to a final product is achieved. Then comes the somewhat less exciting prospect of maintaining it in a high state of usefullness.

I too am interested in evaluating the issues you raise regarding the optimal management of tax-deferred accounts. Right now I am making notes, considering the design possibilities and otherwise milking the creative process of structuring my next "better than sliced bread" spreadsheet. :D
FMO

"The mark of a successful man is spending an entire day on the bank of a river without feeling guilty about it."

User avatar
karma
** Regular
Posts: 65
Joined: Thu Jul 31, 2003 1:16 am

Post by karma » Mon Sep 22, 2003 4:06 pm

You guys scare me. I love spreadsheetology, but you all are way above my league. It is amazing, though, how impressed people can be by a fairly simple spreadsheet. It's sort of like preaching FIRE. People's eyes glaze over if you mention saving and living below your means. They also glaze over if you mention the SUMIF function (my favorite).

Now I'll have to go work on my spreadsheets to bring them up to NFB par.

karma

Kramer
* Rookie
Posts: 18
Joined: Mon Aug 25, 2003 3:53 pm

Post by Kramer » Mon Sep 22, 2003 7:04 pm

Hi Chips, Kramer here, I'm a big fan, and am honored to reply to your post.

I use some standard type spreadsheets that I find invaluable:

Holdings -- (eg, net worth) divided by asset class for my slice and dice
portfolio. It compares my current asset allocation against my asset
allocation plan. It also tallies up info about asset location (taxable vs.
non-tax), growth/blend vs. value, small vs. large, foreign vs. US,
bonds vs. equities. I always consult this spreadsheet before
investing new money. It also tallies up my portfolio's weighted
expense ratio (0.26% right now).

Retire Expenses -- Retirement budget by expense category basically.
I will use the toal projected monthly expenses along with a projected
withdrawl percentage to get a handle on how much I need to retire.
It can be depressing sometimes! Also, in the same spreadsheet, I
will do this for different scenarios -- for instance, a budget for retiring
in a foreign country, etc. I also estimate my retirement tax rate and
include that as an expense.

Net Worth Projections -- Basically projects my current savings rate and
projected returns rate into the future. I modified a REHP spreadsheet
for this one.

Current Budget -- A few times I have recorded everything I have spent
for a couple of months, and I put this into a spreadsheet by expense
category.

And I agree with FMO's comments that nursing your spreadsheets into
completeness is really a joy!

Kramer
Last edited by Kramer on Mon Sep 22, 2003 11:40 pm, edited 1 time in total.

Kramer
* Rookie
Posts: 18
Joined: Mon Aug 25, 2003 3:53 pm

Post by Kramer » Mon Sep 22, 2003 7:39 pm

I recently purchased MATLAB, a modeling tool for engineers and mathematicians that I used in school. I use this for a graduate class in signal processing that I am taking. Anyway, MATLAB is an incredibly useful tool. Think of it as something like Excel on steroids, and with much better performance and flexibility.

Anyway, last evening I wrote a MATLAB program that basically does the same thing as Dory36's web site for safe withdrawl rates, except that it uses all monte carlo calculations based on parameters that I input, instead of historical returns (which many of us believe are higher than future returns). I wrote the program and was able to duplicate all the results on Bill Bernstein's efficient frontier site, in his series on the Retirement Calculator from Hell. This took only 90 lines of MATLAB code.

The program also turns average returns into annual returns. Dr. Bernstein explains this in his article -- an average return of 7.7% with Standard Deviation of 12, is really an annualized return of 7.0%. I calculate the annualized return from the average return using a separate monte carlo technique, although there may be a closed form technique to do it. Maybe Gummy will have a suggestion on that one -- I was cranking this out pretty fast and I'll have to get back to that one later.

Inputs (all in constant real dollars):
Stock Average return
Stock return standard deviation
Bond Average return
Bond return standard deviation
Years of withdrawls
Withdrawl rate
Stock portion of portfolio
Bond portion of portfolio
Years until you receive SS/pension
Amount of pension when it comes
Starting amount at retirement
Number of monte carlo runs (I can do about 50K/second on my P4)


Outputs:
Annualized return stocks
Annualized return bonds
% survival rate
I'll be adding lots here, especially plots over ranges of parameters,
plots of terminal portfolio amounts, etc.


I just started this one yesterday, so naturally I will be adding lots more to this simulation in the future. At some point, if folks were interested, I could post some simulation results.

If anyone has advice about additions, please let me know. For instance, adding some correlation between stock and bond returns, modeling a more sophisticated slice and dice portfolio, I'd like to add in possible part time income during retirement, etc.

My first surpising finding is how high the percentage of bonds should be for a very safe portfolio. If one is predicting low returns for equities going forward, and little or no decrease in volatility, and your withdrawl rate is low, a lot of bonds makes a lot of sense.

Kramer

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Post by therealchips » Tue Sep 23, 2003 8:48 am

Hi, Kramer. Thanks for the kind words. I'm glad you find my posts of some interest or value.

Your spreadsheets cover another of the holes in my spreadsheet, as FMOs' address the real estate issues that I neglect. Since I'm not in the accumulation phase and don't do slice and dice, I don't have as much information to keep track of as you do. Your situation is of more general interest here than mine. Your material on projecting retirement expenses is valuable too, although I do it the other way. I make my expenditures fit the available money. That's easier to do since my assets are large relative to my consumer appetites. I have seen sites on the net that help people identify and quantify various expenses in retirement. That's probably another kind of information worthy on inclusion in our FAQ here. I'll look for it again. I can understand that the exercise can be depressing, but it is worthwhile and may actually promote peace of mind in contemplating retirement.

I'll respond to your other post in a while. You have good ideas there too.
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Some Graphical Output from Retirement Planning Spreadsheets

Post by therealchips » Sat Oct 11, 2003 10:42 am

FMO wrote
Right now I am making notes, considering the design possibilities and otherwise milking the creative process of structuring my next "better than sliced bread" spreadsheet.


When I started to take the personal information out of my spread sheet, it fell apart and many of the more interesting calculations disappeared. I'll think about it some more. Meanwhile, here are descriptions of some of the graphic outputs from my that spread sheet for your consideration.

The first graph has the title "Total, Sheltered and Unsheltered Capital in 1993 Dollars." I picked 1993 as the starting year because that is the year I quit working. The graph covers ages from 53 to 95, with the first ten years being historical data and the rest being projected data. The vertical axis is dollars. The title removes any doubt about whether inflation corrections are included. They are. This graph shows that my tax-sheltered accounts, my IRAs, increase in value until mandatory withdrawals start at age 70½. It also shows that the total is fairly flat, rising a bit for the next nine years, being flat for six years after that, and then declining slightly for fifteen years. One variant of this graph is to pick a budget that stabilizes the graph as nearly as possible, but only to age 85 instead of age 95, to see how the capital would decline if I made that more realistic assumption about my life expectancy but made another ten years of withdrawals.

The second graph is the data in the first one but without inflation corrections. It makes the effects of inflation visible and, maybe, dramatic. I can easily revise my assumptions for investment return and inflation. What I cannot yet do is model the volatility of the market except in one special sense. I have also run my spreadsheet for the "worst case" data on market returns and inflation as suggested by ataloss. Even with withdrawals fixed in purchashing power, the capital survives for thirty years and in fact grows.

The third graph is titled "Budget in 1993 and Current Dollars", showing one line for each, with dollars on the vertical axis and age on the horizontal axis. The budget here is an after-tax figure, since I allow for income taxes separately. The point is not to allow my increased income taxes that come with mandatory IRA withdrawals to reduce either my after-tax standard of living or to reduce the total retirement stash any more than is necessary. Again, this graph represents history for the first ten years (since I am ten years into retirement) and projections for subsequent years.

The fourth graph has the title "Income taxes, current dollars and inflation adjusted". It shows clearly that my taxes jump when my social security income starts, jump sharply when my IRA withdrawals start, and increase somewhat even from there. As I have said, many of the retirement calculators I have looked at ask for average income tax rate as an input from the user. I think the retirement plan should itself calculate that number and recognize that it is probably not constant over an extended period of retirement. As before, this graph has dollars on the vertical axis, years on the horizontal axis, and ten years of data from my personal history followed by thirty-odd years of projected data. All this supports calculations to address questions like "When should I start my social security?" and "Should I make any IRA withdrawals before I have to?" Sometimes I set aside mathematical results for psychological reasons, but not in the cases of those two questions. My calculations say that it would not benefit me over the term of my planning to start social security early or to take any IRA withdrawals when their size is optional in my sixties. YMMV. Again, I object to the retirement planning calculators that do not budget separately for income taxes and living expenses. I do not intend to let variabilty of income taxes have any great effect on my after-tax standard of living.

Whatshisname has advocated using up tax-deferred assets first, ahead of taxable accounts, to avoid the large income taxes on IRAs that otherwise result. The value of the tax deferral is so great to me, as confirmed by the spreadsheet calculations, that I intend to let the IRA assets ride untouched as long as it is legal. If the death tax does not stay dead after it dies in 2009 (?), my IRA assets will probably go to a tax-exempt foundation rather than become subject to both inheritance tax and income tax.

More in a while. My fingers are tired. Your eyes are tired.
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

User avatar
FMO
* Rookie
Posts: 30
Joined: Mon Nov 25, 2002 4:00 am

Post by FMO » Sat Oct 11, 2003 12:17 pm

Thanks Chips for sharing your approach. I understand what you mean when you say that it is difficult to sanitize a financial spreadsheet. Most of the usefulness of such an exercise is derived from being able to incorporate specifics of your personal situation. That is why commercial software rarely yields satisfactory results. I have been working on my own spreadsheet. I will share the salient features of my approach as follows.

The spreadsheet begins where my accumulation phase spreadsheet leaves off at my planned retirement in 2009. I have set aside a worksheet for tabular data that will be used in many of the calculations. This would include an area for specifying assumptions, a table of projected income tax rates and mandatory IRA withdrawal rates. The main assumptions are inflation rate, health inflation rate, anticipated rate of return on cash equivalents/brokerage accounts, rate of taxable dividends, and dividend and cash flow sweeps. These inputs were selected as most important since I plan to live entirely off my pension, making withdraws from my investments only when mandatory or when required to pay taxes or provide a cash flow buffer. Dividend sweep is money transferred from taxable accounts and made available to pay expenses. The cash flow sweep is the percentage of monthly cash flow which is reinvested.

The graphical outputs consist of three graphs portraying net worth, cash flow, and total taxes in both future and constant 2010 dollars.

What I find is that with about a 25% dividend sweep, my constant dollar net worth increases each year, constant dollar cash flow increases when mandatory distributions start and remains fairly level throughout the balance of the entire period. Constant dollar taxes increase in every year. I simulated converting my regular IRA to a Roth incrementally. I found that it did not increase my before-tax net worth for a period of about thirty years. After-tax would be a different story. I am still wrestling with the best way to evaluate this.
FMO

"The mark of a successful man is spending an entire day on the bank of a river without feeling guilty about it."

User avatar
ben
*** Veteran
Posts: 231
Joined: Mon Feb 17, 2003 4:00 am
Location: The world is not enough

Post by ben » Sun Oct 12, 2003 7:22 am

Let me see what I have... A net worth overview spreadsheet incl. all accounts and some simple future estimate calculations of net worth, a budget spread sheet w. several scenarios depending mainly on location, a specific investment spreadsheet with all broker based stocks etc. and a target portfolio spreadsheet w. analysis of e.g. expense ratios/USD vs other currencies split, value vs growth split Etc.

The net worth spreadsheet is my motivator - it includes a very simple estm. about when I hit my target $1mill net worth being my financial independance target as well as a USD with drawal amount estimate.

The budget spreadsheet covers 3 different budgets (satisfactory/good/great lifestyle) and also some cost of living analysis for a couple of potential FIRE-base countries.

The target portfolio spreadsheet also includes new investment ideas/back tests Etc.

Since all my accounts are held tax free I do not dabble much w. taxes (for now :D) and since my time horizon for the FI target is fairly short I have not used inflation adjustment there either. Coorporate pension and public pension is about 30 years out in the future so no spreadsheet for that yet either.... lazy me :wink:
Normal; to put on clothes bought for work, go to work in car bought to get to work needed to pay for the clothes, the car and the home left empty all day in order to afford to live in it...

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Post by therealchips » Sun Oct 12, 2003 9:01 am

Thanks, FMO.
The spreadsheet begins where my accumulation phase spreadsheet leaves off at my planned retirement in 2009.
My spreadsheet addresses only the distribution phase. I never developed one for the accumulation phase. Someone may write a spreadsheet that covers both the accumulation and distribution phases. That would allow analysis of questions like "What is the optimal distribution of my investments between Roth IRAs, classic IRAs or 401 (k)'s that can be rolled over into an IRA, and my taxable accounts during accumulation, considering income taxes in both phases, possible employer match of 401(k) contributions, and other factors?" and "How much should I save for retirement (considering possible pay raises, pensions and COLA's, and certain taxes and inflation) so that my standard of living is about the same in both phases?" Ideally, the "how much" would be a specified amount for each year of the plan, rather than a specified percentage of income. I would not be surprised to see that such an analysis would show that people are wise to incur debt for their educations, as they frequently do, if this qualifies them for well-paying jobs.

You have more fine-grain representation of your income streams than I do. That is another weakness in my approach; it harms the accuracy of my income tax predictions. Looking at any particular investment, the variable income streams are interest or dividends, and realized and unrealized capital gains. I just make summary estimates for all my taxable accounts taken together. (My current estimate is that 35% of all gains in those accounts will be unrealized.) I would think that detailed modelling in this area would be thrown off by unpredictable market performance, not to mention changes in the tax law, but I might add it to my plan one day.
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

therealchips
*** Veteran
Posts: 174
Joined: Sat Jan 04, 2003 4:00 am
Location: Henderson, Nevada, USA

Further thought on my first graphical output.

Post by therealchips » Sun Oct 12, 2003 1:40 pm

The first graph has the title "Total, Sheltered and Unsheltered Capital in 1993 Dollars."


If I were doing this spreadsheet over for more general use, I'd probably break up the stash into at least three sections: Tax-deferred (i.e., classic IRA or 401(k)), Not taxable (i.e., Roth IRA), and Taxable. I used only two categories -- Tax Sheltered or Not. By Sheltered, I meant my classic IRA; I've never had a Roth IRA but such IRA's are worth including. A line for equity in real estate is possible too, considering that its tax treatment keeps it from fitting naturally into any of the other three categories I listed.
He who has lived obscurely and quietly has lived well. [Latin: Bene qui latuit, bene vixit.]

Chips

Post Reply