Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
For those with retirement spreadsheets...
Old 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!
__________________

__________________
Cbear1883 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 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.

---------------------------------------------------
__________________

__________________
EngineeringMyFinances is offline   Reply With Quote
Old 08-14-2007, 06:21 PM   #3
Thinks s/he gets paid by the post
DblDoc's Avatar
 
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
__________________
DblDoc is offline   Reply With Quote
Old 08-15-2007, 12:39 AM   #4
Full time employment: Posting here.
GoodSense's Avatar
 
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.
__________________
GoodSense is offline   Reply With Quote
Old 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)
__________________
Peaceful_Warrior is offline   Reply With Quote
Old 08-15-2007, 08:23 AM   #6
Recycles dryer sheets
In-control's Avatar
 
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!
In-control is offline   Reply With Quote
Old 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 View Post
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?
__________________
Sam is offline   Reply With Quote
Old 08-15-2007, 08:41 AM   #8
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
Originally Posted by Peaceful_Warrior View Post
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
W2R is offline   Reply With Quote
Old 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
__________________
Cbear1883 is offline   Reply With Quote
Old 08-15-2007, 09:40 AM   #10
Full time employment: Posting here.
Sandy's Avatar
 
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
Sandy is offline   Reply With Quote
Old 08-15-2007, 10:46 AM   #11
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
Originally Posted by Sandy View Post
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
W2R is offline   Reply With Quote
Old 08-15-2007, 11:56 AM   #12
Thinks s/he gets paid by the post
OAG's Avatar
 
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
OAG is offline   Reply With Quote
Old 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
EngineeringMyFinances is offline   Reply With Quote
Old 08-15-2007, 04:33 PM   #14
Thinks s/he gets paid by the post
walkinwood's Avatar
 
Join Date: Jul 2006
Location: Denver
Posts: 2,675
Quote:
Originally Posted by Cbear1883 View Post
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.
__________________
walkinwood is offline   Reply With Quote
Old 08-15-2007, 04:54 PM   #15
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
Originally Posted by EngineeringMyFinances View Post
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
W2R is offline   Reply With Quote
Old 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
EngineeringMyFinances is offline   Reply With Quote
Old 08-16-2007, 08:46 AM   #17
Thinks s/he gets paid by the post
jIMOh's Avatar
 
Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Quote:
Originally Posted by Cbear1883 View Post
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.
jIMOh is offline   Reply With Quote
Old 08-16-2007, 11:29 AM   #18
Thinks s/he gets paid by the post
BUM's Avatar
 
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.
BUM is offline   Reply With Quote
Old 08-16-2007, 11:34 AM   #19
Administrator
W2R's Avatar
 
Join Date: Jan 2007
Location: New Orleans
Posts: 38,822
Quote:
Originally Posted by BUM View Post
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
W2R is offline   Reply With Quote
Old 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.
__________________

__________________
novaman is offline   Reply With Quote
Reply


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

Advanced Search
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
Tax Policy Promotes "Early" Retirement REWahoo FIRE and Money 19 07-21-2017 03:10 PM
Who pays more income taxes in retirement? LOL! FIRE and Money 19 12-17-2006 04:16 PM
Vanguard Adding More Target Retirement Funds Lusitan FIRE and Money 23 03-21-2006 07:17 PM
Scott Burns: Stay safe on retirement spending PL FIRE and Money 49 12-22-2004 11:59 AM

 

 
All times are GMT -6. The time now is 12:19 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.