Monday, August 20, 2012

Retirement Planning Excel Sheet

I apologize for the small image and the late post but I have a small treat today.  I've uploaded a link in this post below if you are interested in downloading it and playing around with the spreadsheet. This is a simple little excel model I created to project one way of how to plan for retirement.  I'll quickly talk about how to think about this tool and how to use it.

First of all, this tool is not meant to accurately represent your income and expenses every year until and after retirement.  It is only used as a guideline to see how some assumptions you might have about your income and expenses will play out over the long run.  I view this thing as more of a little toy that you can use to test different assumptions and how much you need to plan on saving to meet your potential needs.  Plug some numbers, look at the results, and make the adjustments that you would think better represent the future.  Then, plan accordingly and make sure you don't overspend year to year.  You can even keep the general budget plan this thing pops up and try to follow it yourself after making your tweaks.

So what does this tool do and how do you use it?  It essentially takes in numbers you feed it and shows you what those numbers will look like over time.  You give it the starting values for pre-tax income, a bonus, investment balance, and expenses and some assumed constant growth rates for each of these over time.  You also give an average tax rate to apply over the course of the model.  You can already see lots of problems with how this model becomes disjointed from what will really happen.  Will your food expenses really inflate year after year at 5% or whatever percent you plug in for the next 50 years?  Probably not.  But that is what the model will do.  You will also give a retirement year which will set all income and bonus numbers to 0 for that year and afterward as well as a maximum limit to your income and bonus.  This is because if you increase your income year after year at 5% or 8% or 10%, you will be making millions when you are 40 or 50 which is unlikely.  By setting a cap, if the amount reaches above the cap, the next year will just be flat until the retirement year.  You can see in the numbers that I have already, with an income cap of $200,000 that after 2028, income flat lines at $188,427 because it would go over the limit.

You should treat the investment balance as your savings year after year.  It takes your starting investment balance which you input and add to it your net income (total income - total expenses) year after year.  It also assumes you grow this income at whatever growth rate you input.  You should put a rate reflective of your investments which will depend on your asset allocation.  If you put most of this in cash, you will want to expect a rate of around 2-4%.  If you have a substantial portion in stocks, you can probably expect something around 8-10% year after year.  A blend of stocks, bonds, and cash will probably result in some rate in between those ranges.

For the expense growth rates, part of it should be how much you expect inflation to affect those subgroups.  Overall, inflation has been about 3% from 1900 to 2000.  The other part of your expense growth rates should be how much more you will be spending year over year.  If you know that you spend most of your money on food and that you will continue to use marginal increase in income on food, you may want to add a percent to your food growth.  If you know your raise will be going to movies and parties, you may want to add a percent to personal.  I would put your expense growth rates somewhere between 1-5% depending on how much you think prices will increase over time and how much you think you will be spending extra on that subgroup.  However, even growing modestly at 1% can result in the model spitting out a $200,000 expense on rent each year in retirement so look over the numbers and change them where necessary.

Unfortunately, this tool does not model in some life cycle events like purchasing a home or going to graduate school or having kids.  If you have student debt, you can model that in through a negative starting investment balance.  Make the necessary changes to the years where you expect something unusual to happen.

After getting the final numbers, you can see when you will go in the red.  Look at what year your investment balance turns negative.  That is the year you will run out of money and will need to cut back more on expenses or find some other means of earning income.  You can try to change the retirement year to see how much of an effect that has by retiring earlier or later on your savings.

Will this model give you all the answers to how you should think about retirement and budgeting, probably not.  But use it as a guideline to frame how you think about your spending and earnings.  You have much more control over your expenses in the short term to manage if prices get too high.  If you are a college student like me and think about how much you should budget each year for these categories, you will be a step ahead of everyone else.

No comments:

Post a Comment