08-14-2007, 11:46 AM #1 Dryer sheet wannabe   Join Date: Jul 2007 Posts: 24 For those with retirement spreadsheets... I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated! __________________ __________________
 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! Join Early-Retirment.org For Free - Click Here
 08-14-2007, 03:38 PM #2 Recycles dryer sheets   Join Date: Aug 2007 Location: Midwest Posts: 109 Most spreadsheets are comprised of basically simple calculations. I've set one up for myself that models what my savings should be at any age in the future (to within a month) given assumptions about current savings rates. It also estimates my Social Security benefit and taxes and estimates a withdrawal rate based on my age at the time to arrive at an estimated retirement income. All these figures are in current dollars, with the expectation that my investments and benefits at least keep up with inflation. Basically just some addition with a bit of multiplication thrown in. Try your hand at some simpler spreadsheets to start out. Make an amortization table without using Excel's built-in functions for that -- make it using addition and multiplication. What I've ended up with is something that someone else would have a learning curve to understand. But it's something I've built and therefore understand what the output means -- unlike the average retirement calculator on the web. If you can do what I regard as basic tasks, which includes entering formulas and then copying them, then you should be able to make your own. Start out with what you can do, use the Help to see if it's possible to do something else you want to do. Never had any spreadsheet training, this method has worked for me. YMMV. --------------------------------------------------- Engineering My Finances Personal Blog __________________ __________________
 08-14-2007, 06:21 PM #3 Thinks s/he gets paid by the post   Join Date: Aug 2007 Posts: 1,224 Basic tasks is all you need. Like EMF said build yourself some tables. For me I'm accumulating so I have a spreadsheet that predicts my accumulation based on conservative estimates of return for my portfolio and new \$ I plan investing monthly. Over time as I learn and better understand what I'm doing I add new features or information. Most recently I was able to determine our annual basic living expense and can calculate out what that will look like over time with inflation and changes that are predictable. I haven't figured out yet the decumulation phase so that is a blank on my spreadsheet - and the way the market is going it's not a priority right now. My next project is to track my ROI. DD __________________
 08-15-2007, 12:39 AM #4 Full time employment: Posting here.   Join Date: Jul 2007 Posts: 678 Agree with the other posters. What I do is quite simple: List all assets List all liabilities Estimate how much we can save each year Assume a normal rate of return (highly speculative) on existing assets Project what assets/liabilities will be in 1, 2, 3, or 10 and 15 years (I don't adjust for inflation--instead assuming a lower rate of return, but that's just me. There are different opinions about it) You can also make budgets based on your actual spending (credit card statement helps), and estimate what your ER needs will be. Given the 4% SWR, you should be able to estimate what you need to retire on IN TODAY'S DOLLARS. Check to see what year that number will fall on. Again this method is very crude and speculative. It works for me. __________________
 08-15-2007, 12:58 AM #5 Full time employment: Posting here.   Join Date: Dec 2006 Posts: 509 My version is probably one of the more simplistic here... since I do the "ghetto budget." I look at how much we typically spend, how much income we bring in, and then set up the difference to go on auto-deposit into combination of 401k and Vanguard Index funds. Then I have everything go into a single web-based view, and every so often I look and go, "Yup, the numbers are going up." (well other than the last week LOL) __________________
 08-15-2007, 08:23 AM #6 Recycles dryer sheets   Join Date: Mar 2007 Posts: 319 I downloaded a budget worksheet from Fidelity and use it to track monthly expenses. For my AL I created my own spreadsheet with separate worksheets for Mutual funds/ 401k/ cash equivalents I update these monthly and each worksheet feeds an allocation worksheet that summarizes +/- \$ per asset - telling me what to sell and buy. This is very simple and meets my need. I also created a worksheet where I insert a cash amount that I want to re-allocate, like a tax refund, and it tells me where to put the \$. Good luck - worst case if your just not into all this allocating is to get a life-style fund(Vanguard) which keeps your allocation intact. Remember you pay for that in two way's, one for the primary fund mgt, and two for not being able to upgrade your funds to Admiral class significantly reducing your fees. __________________ Just Trekking thru!
08-15-2007, 08:30 AM   #7
Thinks s/he gets paid by the post

Join Date: Mar 2006
Location: Houston
Posts: 2,155
Quote:
 Originally Posted by Cbear1883 I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated!
I think you will get more specific replies if you tell us what you're trying to accomplish with your spreadsheet. What are you trying to track? What are you trying to project?
__________________

08-15-2007, 08:41 AM   #8

Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
 Originally Posted by Peaceful_Warrior My version is probably one of the more simplistic here...
My Excel retirement workbook, on the other hand, has fourteen well populated worksheets and is terrifyingly complex. I have had it going since 2000 and I keep adding things as I decide they interest me. I love Excel and I keep track of (enter or compute and project) all of my income, budget, pension, social security, TSP (=401K), Roth, days until I retire, best retirement dates and why, and so on.

Then there are pages with steps needed to move after retirement, hypothetical projections of nestegg left at various ages given various assumptions, possible asset allocation strategy with lists of mutual funds I am interested in, computations of income from various sizes of TSP (MetLife) annuity if I got one when I retire, and amortization on my past mortgage. Now that my mortgage is paid off, this page has morphed into my daily checking and savings account balances along with monthly and yearly projections and goals, and what I actually live on (income minus savings/investments) in comparison with my budget.

I could go on (and on, and on...). Essentially my Excel workbook is as personal as can be. I update it every morning with account information that I get online and elsewhere. I wouldn't wish it on my worst enemy, but keeping tabs on things this closely has worked well for me. Despite the unexpected vagaries of life, I am meeting or exceeding all of the financial goals that I have been creating for myself through the years. In my spreadsheets I can see that in black and white, and that is a GREAT feeling.
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851

08-15-2007, 09:27 AM   #9
Dryer sheet wannabe

Join Date: Jul 2007
Posts: 24
Thanks for the replies everyone

Quote:
 I think you will get more specific replies if you tell us what you're trying to accomplish with your spreadsheet. What are you trying to track? What are you trying to project?
Well to be honest I'm not sure...something that I can use to keep track of what I have now, and what I'm going to have as the years drag on. I'd also like to be able to use it as a guide for what I owe for my mortgage, car loans, upcoming large purchases, etc. (I just finished paying off what was left of my younger years that I paid for in plastic, so the cc's are gone!). My goal is to pay everything off by 2013, including the mortgage we took out 3 years ago. We get no tax advantages, as the mortgage is under 70k, and at 6.375, it's not the BEST rate in the world, so I'd rather just pay it off. I also don't have all that much faith in where the market is headed in the next few years. Once our debt is paid off, we'll be able to put some serious cash into our retirement funds.

Something similar to what Want2retirehas described sounds great, yet maybe not "quite" so detailed (only because we're just starting and don't have much detail to imput). I did a search on the forum and found a few that I could probably use for comparison, but you're all so savvy, I thought you would have some good suggestions, and I was right. Sometimes I feel like I'm so far from retirement, that I shouldn't even bother yet with detailed planning. I'm still 28 years from our "anticipated" early retirement date but I guess I figure that the more work I put into it, the earlier that date can be.

Thanks all for your ideas, they'll be put to good use
__________________

 08-15-2007, 09:40 AM #10 Full time employment: Posting here.   Join Date: Dec 2006 Location: Florida Posts: 855 If you need help figuring out how much you need to pay each month to pay off the mortgage quickly, and you don't know how to do that in Excel, use an on-line calculator, like this one Paying Down a Mortgage Calculator - Fiscal Agents Financial Tools or go to this website that has lots of caculators that can do it for you: Hugh's Mortgage and Financial Calculators then all you need to do is chart your monthly expenses (how much you pay/save) and you can get started with a very simple spread sheet that only uses + and - __________________ I would not have anyone adopt my mode of living...but I would have each one be very careful to find out and pursue his own way, and not his father's or his mother's or his neighbor's instead. Thoreau, Walden
08-15-2007, 10:46 AM   #11

Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
 Originally Posted by Sandy If you need help figuring out how much you need to pay each month to pay off the mortgage quickly, and you don't know how to do that in Excel, use an on-line calculator, like this one Paying Down a Mortgage Calculator - Fiscal Agents Financial Tools or go to this website that has lots of caculators that can do it for you: Hugh's Mortgage and Financial Calculators then all you need to do is chart your monthly expenses (how much you pay/save) and you can get started with a very simple spread sheet that only uses + and -
Absolutely! A lot of the stuff that I have on my spreadsheet, such as social security based on my past and projected income, and how much a TSP annuity would pay, and my amortization page are all available as calculators online. I find the method used by the calculators, program the method into Excel and check in comparison with the calculators. I only do this because it is fun for me (yes, I'm weird). This is yet another reason why I wouldn't wish my worksheet on anybody else! It is very customized.

Well, I suppose that another reason why I include these computations into my Excel spreadsheet would be that once I understand the computations I can try out different scenarios that some calculators can't handle. Plus, in the case of amortization it keeps a record...for example, I sent in an extra \$13,200 on May 1, 2003, \$16,700 on July 1, 2004, \$5000 on December 1, 2004, and so on. Fun for reminiscing and for computing how much I lived on other than the house payments.

I always check the output with what the calculators say, every year or two just to make sure I didn't drift away from the basic computations or that the computational factors used by the online calculators haven't been updated (as in the social security multipliers, etc).
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851

 08-15-2007, 11:56 AM #12 Thinks s/he gets paid by the post   Join Date: Jun 2006 Location: Central, Ohio, USA Posts: 2,598 IMO you may be able to find what you need here Free Excel Spreadsheets. __________________ Vietnam Veteran, CW4 USA, Retired 1979
08-15-2007, 12:14 PM   #13
Recycles dryer sheets

Join Date: Aug 2007
Location: Midwest
Posts: 109
W2R:
Quote:
 My Excel retirement workbook, on the other hand, has fourteen well populated worksheets and is terrifyingly complex
Cbear:
Quote:
 Well to be honest I'm not sure...something that I can use to keep track of what I have now
W2R's description of her spreadsheet sounds about like mine. Rome wasn't built in a day, and neither were our spreadsheets -- at least not mine. I started off simply with a worksheet that kept track of savings goals and what my mortgage balance was doing as I made additional payments.

Then I added worksheets to keep track of my Social Security, retirement projections, per year tax withholdings so I wouldn't get hit with a penalty, etc.
__________________
Engineering My Finances Blog

08-15-2007, 04:33 PM   #14
Thinks s/he gets paid by the post

Join Date: Jul 2006
Location: Denver
Posts: 2,675
Quote:
 Originally Posted by Cbear1883 Thanks for the replies everyone Well to be honest I'm not sure...something that I can use to keep track of what I have now, and what I'm going to have as the years drag on. I'd also like to be able to use it as a guide for what I owe for my mortgage, car loans, upcoming large purchases, etc. (I just finished paying off what was left of my younger years that I paid for in plastic, so the cc's are gone!).

Why not use a money management software package? That will get you started without the complexity of a spreadsheet. Once you know what you want, you could start creating your own spreadsheet. I use Quicken and am very happy with it. I have a friend that uses the Opensource GnuCash and swears by it (its also free).

I use a spreadsheet for things that Quicken doesn't do well.
__________________

08-15-2007, 04:54 PM   #15

Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
 Originally Posted by EngineeringMyFinances W2R's description of her spreadsheet sounds about like mine. Rome wasn't built in a day, and neither were our spreadsheets -- at least not mine. I started off simply with a worksheet that kept track of savings goals and what my mortgage balance was doing as I made additional payments. Then I added worksheets to keep track of my Social Security, retirement projections, per year tax withholdings so I wouldn't get hit with a penalty, etc.
The same is true for me. My spreadsheet has evolved over 7-8 years. I think I started it when I wanted to calculate how much my federal pension would be. That was the first question I had.

I added sheets gradually over the years, and I modify it as new questions arise. That is pretty much every day. For example, recently I added projections of what my TSP (401K) will be if the market continues to yield what it has this year, only indefinitely, and compare with my prior projections. The market probably won't continue to tank, but it will be cheery if/when those numbers begin to rise.
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851

08-15-2007, 10:01 PM   #16
Recycles dryer sheets

Join Date: Aug 2007
Location: Midwest
Posts: 109
Quote:
 Why not use a money management software package? That will get you started without the complexity of a spreadsheet.
I've tried out both Money and Quicken, and didn't find them to my liking. Maybe I didn't give them enough of a chance.

When I was younger, I tracked all spending in detail. But after awhile of doing that, good spending habits were developed, or at least good enough spending habits that I don't need to track spending in detail. I set general savings targets to make sure I don't stray too far out of line. May not work for most, but works for me.

With respect to web calculators for retirement planning, most of them don't let you know their assumptions. Same thing with financial software. With my own spreadsheet, I know what the assumptions are.

Maybe FIREcalc's a bit different, and through discussion on this forum I could understand better how it works. For now, I'm using a very conservative estimate for withdrawal. Perhaps with a bit more study I'll feel more comfortable with an earlier retirement as I get closer. But since I'm in the accumulation phase, it doesn't matter.
__________________
Engineering My Finances Blog

08-16-2007, 08:46 AM   #17
Thinks s/he gets paid by the post

Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Quote:
 Originally Posted by Cbear1883 I'd like to start one, but I wouldn't know where to begin. Anyone have suggestions for one they'd be willing to link me to? I'm not exactly Excel savvy, though I can accomplish basic tasks. Any help would be appreciated!
I have one workbook which has close to 20 sheets of various calculations.

page 1 is a list of all accounts, web addresses, login ids and password codes (not the actual passwords, but a code to help me remember it).

page 2 is a list of tax tables (from fairmark) and how to calculate income taxes in USA.

page 3 is an estimate of what it takes to retire based on given investment returns (average) over long term.

Input income
Input current age
Input retirement age

output is age to retire with a given return %. There is a column for 12% returns, 11%, 10%, 9%, 8%, 7% and 6%.

I have this printed and look at it often. When others post "here is what I have saved and this is my age", I look at this chart to see where they are. This chart is the reason why I know I need to be aggressive for retirement (9% returns barely achieves goal right now). 10% achieves goal with room to spare.

I have another "draw down" spread sheet for how the amount saved on previous sheet would get drawn down. An ammortization schedule of draw down.

I have a sheet for the ammortization schedule on our current mortgage.
I have a sheet for the ammortization schedule on our second mortgage.
I have a sheet on CDs, when they mature and their amounts
I have a sheet on compound interest calculations

and I am sure I created more, and just did not remember what they were for (I rarely delete anything).
__________________
Light travels faster than sound. That is why some people appear bright until you hear them speak. One person's stupidity is another person's job security.

 08-16-2007, 11:29 AM #18 Thinks s/he gets paid by the post   Join Date: Feb 2004 Location: Mid Hudson Valley Posts: 1,778 I keep 2 excel spreadsheets. Both fairly simple. The first is a balance sheet style that lists assets, liabilities and ultimately net worth. The second simply lists my investment account's total for each month and overlays that number with the DJIA on the same graph. Over time I like the way my portfolio is less riveted to the DOW. I say keep it simple. Make up your own sheet. You'll understand it better. __________________ In a panamax down by the river.
08-16-2007, 11:34 AM   #19

Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
 Originally Posted by BUM The second simply lists my investment account's total for each month and overlays that number with the DJIA on the same graph.
What a cool idea! I've GOT to try that. Thanks!
__________________
Already we are boldly launched upon the deep; but soon we shall be lost in its unshored, harbourless immensities.

- - H. Melville, 1851

 08-18-2007, 10:04 AM #20 Recycles dryer sheets   Join Date: May 2007 Posts: 371 My version of quicken premier 2007 has a great retirement calculator built right into it. And it is really easy to work with because as long as you keep everything up to date in Quicken it simply pulls that data and runs the calculations. __________________ __________________

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