Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Spreadsheets
Old 06-27-2005, 09:20 AM   #1
Thinks s/he gets paid by the post
Helen's Avatar
 
Join Date: Oct 2004
Location: Portland
Posts: 1,339
Spreadsheets

Hi All,

I've been seeing the word spreedsheets pop up on various posts.

I have two. My favorite is my eight year plan to ER. I track everything I am saving on an annual basis by the account type (ie - 401k, Roth, aftertax etc). I also have estimated salary increases (Federal employee) so I can determine where and how much to increment my savings. I then have a running total of my increase in savings - one in the form of raw contributions and the other with a 5% return.

My second spreadsheet is an annual budget that shows how much per pay period is deducted from my paycheck and which account it is deposited in.

What type of information does everyone track on their spreadsheets ?

-helen
__________________

__________________
Helen 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!

Re: Spreadsheets
Old 06-27-2005, 11:13 AM   #2
Recycles dryer sheets
 
Join Date: Jan 2004
Posts: 159
Re: Spreadsheets

I just have one spreadsheet called "finances.xls", but it has lots of worksheets. Here are some of the things I keep track of in mine:

Assumptions (which are inputs to a lot of the other worksheets)
Annual raise %
Annual increase in college tuition %
Annual inflation rate %
401(k) contribution %
Kids' college fund contribution amounts
etc.
Net Worth
Columns = as of a particular date
Rows = one per asset or liability
Comparative data going back 11 years
Spending last six months
From Quicken
By category
Life insurance spreadsheet for me
Life insurance spreadsheet for my wife
Retirement/college expenses projection worksheet
Investment expenses as a percentage of invested assets (currently hovering under 15 bp)
MBA cash flow worksheet

Plus some graphs of the above.

malakito
__________________

__________________
malakito is offline   Reply With Quote
Re: Spreadsheets
Old 06-27-2005, 11:23 AM   #3
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Re: Spreadsheets

I track our lifetime earnings.

I think the idea came from TMND but I don't remember. The idea was that eventually your net worth should exceed your lifetime (total) salary. (It worked, but it took almost up until ER.) I also track what % we pay in taxes-- % of total income, % of taxable income, % of net worth. That used to be a bigger deal before 1986 tax reform and the 2003 tax reductions so I guess I keep it up out of habit.

I also track all the money that we've paid into SS & Medicare. In another 17 years (when I start getting it back) I'll see how long it takes me to beat the returns on a low-cost small-cap stock index ETF. It'll be interesting to see if I live that long. Hey, you gotta have goals in your life.

I track the College Board's annual college-cost surveys and update our spreadsheet yearly to see if we're keeping up (not even close). Our kid will probably be able to patch together the financing for Harvard or Cal Tech... but I think that there are much better (coincidentally much cheaper) schools. So tracking the college savings is mainly an exercise in thinking through our strategy and planning the eventual shift from stocks into bonds/cash.

I'll be tracking the performance of our mortgage money that's invested in the S&P600/Barra Small-cap Value ETF. So far so good but it'll be interesting to see how the numbers turn out in the next 30 years (we're refinancing yet again next month).

I also track the payback on our photovoltaic system. It's not as simple as totaling up the expenses and then deducting the savings on our utility bills-- oh, no, that's just too easy for a nuclear-trained INTJ engineer. This takes into account the opportunity cost of a decent 6% after-tax return on a low-cost ETF as well as the rollover of tax credits spread out over three years. When I enter that month's KWhr data the bottom line gives the # years of payback. It's 25 years so far but we need to add another 2 KW of PV panels to really make this practical. We're still bottom-fishing for a bargain price.

Fidelity lists their IPOs all the way back to 1996 and I spreadsheeted the performance of the most recent 20 through last April. I guess there's a reason that Fidelity doesn't provide this data. When you exclude Google (didn't need Fidelity for that!) the IPO class's performance has been negative, which makes me much more selective about the crap they're flogging from Lehman. (I gave up when I got to Jan 2004. E-mail me if anyone wants to complete the exercise and I'll send you the rudimentary spreadsheet. Some of these IPOs may no longer be among the living...)

__________________
*
*

The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.
Nords is offline   Reply With Quote
Re: Spreadsheets
Old 06-27-2005, 12:25 PM   #4
Thinks s/he gets paid by the post
wildcat's Avatar
 
Join Date: Feb 2005
Location: Lou-evil
Posts: 2,025
Re: Spreadsheets

Just curious does everyone recommend Quicken? I have used Excel (bone dry version of tracking expenses) but I imagine there are better packages.
__________________
"These walls are kind of funny. First you hate 'em, then you get used to 'em. Enough time passes, gets so you depend on them"
wildcat is offline   Reply With Quote
Re: Spreadsheets
Old 06-27-2005, 01:57 PM   #5
Recycles dryer sheets
 
Join Date: Nov 2003
Posts: 69
Send a message via AIM to Newell
Re: Spreadsheets

Wildcat,
Personally, I would highly recommend Quicken, but not to the exclusion of spreadsheets. There are too many things that are unique to each individual that makes it impractical or impossible to build them all into a general purpose financial package.

I also believe that there is a level of comfort in using personally constructed spreadsheets because there is necessarily a very good understanding of all of the assumptions built into whatever it is that you're analyzing.

On the other hand, Quicken does a fantastic job of keeping track of every $1 of income and expense that I have and a fairly good job of maintaining info on my investments.
__________________
Newell is offline   Reply With Quote
Re: Spreadsheets
Old 06-27-2005, 02:08 PM   #6
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Re: Spreadsheets

Quote:
Originally Posted by wildcat
Just curious does everyone recommend Quicken?* I have used Excel (bone dry version of tracking expenses) but I imagine there are better packages.
I've been using Quicken for over a decade (since the DOS days).

Don't get me wrong-- the program sucks at a number of things, especially Intuit's customer support and their Microsoft-like insistence on changing data formats to force the rest of the world to pay for upgrades. Quicken will do 95% of everything quite well (even invisibly) while its 5% flaws-- especially downloads-- will drive you absolutely crazy with frustration. And that's even before you contact tech support. (OK, TH, take a solo here!)

On the positive side, it's a much better database than an Excel or Access app. I haven't maintained a paper checkbook since the very early '90s. Quicken will track and remember everything you do and it won't make (hardly any) coding mistakes. It's been a lifesaver when spouse says "When did we do that?" or "When did we last buy one of those things?" It'll organize your spending and force you to approach budgeting in a more responsible manner while reinforcing your motivation with cool rising graphs. It also makes nice printouts & pie charts for those "Where did all the money go?!?" discussions.

Another "bright spot" about Intuit, I think, is their refund policy. They'll refund just about any complaint within the first 60 days. About a third of my Quicken or TurboTax purchases have pissed me off so badly that I've girded for battle and phoned in a refund request ready to go ballistic-- and every time it was met politely & quickly. I guess they make enough money from initial sales that they just don't have to care about repeat customers.

Quicken vs Money is one of those eternal reciprocated diatribes. Both programs have their ardent supporters and even more indignant critics, which tells me that it's probably just a personal preference. Try them both and decide which you like better.
__________________
*
*

The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.
Nords is offline   Reply With Quote
Re: Spreadsheets
Old 06-27-2005, 04:46 PM   #7
Thinks s/he gets paid by the post
 
Join Date: Mar 2004
Posts: 2,083
Re: Spreadsheets

Over the last 2 years, I had fun developing a multiple worksheet spreadsheet that tracks my financial information. I also linked them to minimize input entry.

These are the main sheets that I developed.
1) dashboard. shows current and forcasted end of year assets. This feeds #5
2) detail monthly expenses for current and forcasted 3 years. This feeds #4 and #5
3) banking. This feeds #1
4) 40 year detail budget with yearly inflation factors. This feeds #2 and #5
5) 40 year asset tracking and income requirement.
6) stock holdings. Cut and Pasted from Yahoo. This feeds #1

I have a few "what if" yearly flags that alter my financial forcasting if I sell my house, if I get my company pension, or if collect SS at 62 or 66.

MJ
__________________
I look to the present moment because that's where I live my life.
MJ is offline   Reply With Quote
Re: Spreadsheets
Old 06-28-2005, 08:20 AM   #8
Full time employment: Posting here.
 
Join Date: Apr 2005
Posts: 577
Re: Spreadsheets

My spreadsheet substitute consists of:

1. print out of accounts at Vanguard, with linked accounts.
2. copy of FireCalc results page that I settled on after tinkering.
3. 2 lined 5 x 7 inch pages. One has about 8 points to remember about $ not included in RE savings, or about things to do. The other has best guess short list of life expenses, necessary and chosen.

I folded it up and keep it in the wooden box my Dad gave me when I was a kid, on a shelf in the dining room. I'm trying to train myself to forget about it and go live my life. When it's on the computer I look at it waaaaay too much.

kate
__________________
kat is offline   Reply With Quote
Re: Spreadsheets
Old 06-29-2005, 02:33 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
cute fuzzy bunny's Avatar
 
Join Date: Dec 2003
Location: Losing my whump
Posts: 22,697
Re: Spreadsheets

Quote:
Originally Posted by Nords
And that's even before you contact tech support. (OK, TH, take a solo here!)
They have that? I thought they just had an email address that went to the circular file...hmm...
__________________
Be fearful when others are greedy, and greedy when others are fearful. Just another form of "buy low, sell high" for those who have trouble with things. This rule is not universal. Do not buy a 1973 Pinto because everyone else is afraid of it.
cute fuzzy bunny is offline   Reply With Quote
Re: Spreadsheets
Old 06-29-2005, 04:10 PM   #10
Thinks s/he gets paid by the post
grumpy's Avatar
 
Join Date: Jul 2004
Posts: 1,321
Re: Spreadsheets

My multisheet workbook is stuctured as follows:

Sheet 1: Summary of investments by category - Equities, bonds, cash, retirement accounts

Sheet 2: Retirement account details showing each account and fund with percentages by category

Sheet 3: Individual stock summary showing # or shares, average price, current price, original purchase cost, value of shares sold, cash dividends, current value, net return, effective div. yield, annual dividend, % of holdings

Sheet 4: Prices - of each stock and fund, downloaded once a week as CSV file from WSJ online (updates all prices throughout all sheets)

Sheet 5: Portfolio allocation - Large cap, mid cap, small cap, international, Specialty, Fixed Income, Cash. Also calculates allocation percentages with pensions treated as fixed income

Sheets 6 thru 40: One sheet for each individual stock, fund or account showing each transaction (buy, sell, dividend, etc.)

I spend about an hour a week maintaining the wookbook. I keep a printout of each sheet in a three ring binder. For any transactions that take place, I mark them on the printout and update the sheets once a week and print any pages that have changed. This binder has all the account numbers, phone numbers, id's and passwords needed to maintain all accounts.

This workbook format has evolved over the 10 years that I have maintained it. It is tailored to my specific needs and interests. I find it to be indispensible. Therefore, each time I update it I also save it to a removabe medium for backup.

At the end of each calendar year I save printouts of sheets 1 thru 5 in the binder. I use these to update a graph of portfolio growth by category.

Grumpy
__________________
...you can check out any time you like, but you can never leave...
grumpy is offline   Reply With Quote
Re: Spreadsheets
Old 06-29-2005, 05:46 PM   #11
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Re: Spreadsheets

My spreadsheet has six columns: one for each powerball number... :P
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Re: Spreadsheets
Old 06-29-2005, 11:04 PM   #12
Thinks s/he gets paid by the post
Sheryl's Avatar
 
Join Date: Apr 2004
Posts: 1,459
Re: Spreadsheets

Wow.* I'm an amatuer...

I have two - one is where I track expenses - my own creation that probably would have been better done on Quicken, but I like to do it in Excel.* I have five years worth of summary - how much I earned, spent and invested each month.* Two years of data in much more detail - every expense catagorized.* Mostly I like to be albe to see trends and averages, I'm currently working on using this data to develop a realistic retirement budget.

I also have a spreadsheet of all my investments, broken down by account type, and then cross referenced to show asset allocation.* Updating this puppy and seeing the number grow is my favorite activity these days.

Last night I created a spreadsheet of FIRECalc runs.* *I changed one variable at a time and tracked the effect of each change on the overall success of the scenario....* Very interesting.

One REALLY wierd thing:* after reading a couple comments here lately about how you really don't WANT to spend so much in later retirment years, I ran some scenarios where I reduced my projected spending in year 30* (Used a 50 year time span).* I accidently used a positive number first - meaning that spending would increase by $25,000 instead of decrease.* It made VIRTUALLY NO DIFFERENCE!

I thought something must be wrong but after looking at the detail reports I finally concluded that with the data being used for the various years, by year 30 either the portfolio was dead and it didn't matter, or it was so huge it didn't matter - there didn't happen to be any years where that decrease late in the game effected the eventual success or failure of the portfolio.

Weird.
__________________
Sheryl is offline   Reply With Quote
Re: Spreadsheets
Old 07-01-2005, 06:16 PM   #13
Full time employment: Posting here.
 
Join Date: Sep 2002
Posts: 538
Re: Spreadsheets

I too am a Quicken fan.* I think Quicken has a cultic following.* It's nothing magic if you think about it but it does allow people to organize their complete financial picture.* Of course junk in/ junk out.*

Many folks have heard my comments about that I would be glad when I stopped entering stuff in quicken and went to zero based budgeting.* It does take a lot of time.* But until I retire I will keep plugging away.*

JDW
__________________
JDARNELL is offline   Reply With Quote
Re: Spreadsheets
Old 07-01-2005, 09:46 PM   #14
Full time employment: Posting here.
sailor's Avatar
 
Join Date: May 2005
Location: Atlanta suburbs
Posts: 881
Re: Spreadsheets

Quote:
Originally Posted by Tomcat98
Many folks have heard my comments about that I would be glad when I stopped entering stuff in quicken and went to zero based budgeting. It does take a lot of time. But until I retire I will keep plugging away.
Another Quicken fan here - why does it take a lot of time?
Can't you download all your transactions?
I have over 20 accounts in Quicken and except for 2 (which are rarely updated anyway) the rest have automatic daily downloads.
In the morning before the breakfast I review the downloaded transactions and if necessary categorize them. Doesn't take longer than 3 minutes per day for few daily transactions I have.
__________________
sailor is offline   Reply With Quote
Re: Spreadsheets
Old 07-01-2005, 10:08 PM   #15
Recycles dryer sheets
 
Join Date: Nov 2003
Posts: 69
Send a message via AIM to Newell
Re: Spreadsheets

I guess I consider myself a Quicken addict. I have spent many, many hours trying to enter historical data so as to have a record of every check written, every paycheck deposited, every bill paid, every investment made and every credit card transaction - 1st for 10 years, then 20, and ultimately back to 1964 when I was discharged from the Marine Corps.

It's interesting to reminisce about "stuff" that was purchased over the years that seemed so important at the time, and so unimportant in hindsight.
__________________
Newell is offline   Reply With Quote
Re: Spreadsheets
Old 07-02-2005, 01:01 PM   #16
Moderator Emeritus
Nords's Avatar
 
Join Date: Dec 2002
Location: Oahu
Posts: 26,616
Re: Spreadsheets

Quote:
Originally Posted by oldbykur
I guess I consider myself a Quicken addict. I have spent many, many hours trying to enter historical data so as to have a record of every check written, every paycheck deposited, every bill paid, every investment made and every credit card transaction - 1st for 10 years, then 20, and ultimately back to 1964 when I was discharged from the Marine Corps.

It's interesting to reminisce about "stuff" that was purchased over the years that seemed so important at the time, and so unimportant in hindsight.
Welcome to the club, oldbykur. That's one of the 12 steps, but I don't have any motivation to change either.

Our kid is redecorating her room and we're getting ready to buy new furniture. (She's pointed out that she's had to put up with the current furniture for over two-thirds of her life.) Spouse asked this morning "How much did we pay for that stuff?" and the answer took less than 60 seconds.

I'm trying to decide if Quicken history can be used to calculate a personal CPI. I'm surprised that Intuit hasn't turned that into a button and charged us to click it...

TH, in anticipation of your rant on Quicken's "download" tactics, I'll just say that Quickbooks is even more of a ripoff.
__________________
*
*

The book written on E-R.org, "The Military Guide to Financial Independence and Retirement", on sale now! For more info see "About Me" in my profile.
I don't spend much time here anymore, so please send me a PM. Thanks.
Nords is offline   Reply With Quote
Re: Spreadsheets
Old 07-02-2005, 03:33 PM   #17
Thinks s/he gets paid by the post
maddythebeagle's Avatar
 
Join Date: Jun 2005
Posts: 2,450
Re: Spreadsheets

Do federal employees have a 457k (deferred comp.) like state and local grunts?

I use a spreadsheet to track my dividend reinvestment plans and net worth and projected investment values over time. I am a cheapo and dont want to buy any other software as well as not wanting to waste my time leaning it....KISS is my moto.
__________________
- Hurry! to the cliffs of insanity!
maddythebeagle is offline   Reply With Quote
Re: Spreadsheets
Old 07-10-2005, 07:29 PM   #18
Recycles dryer sheets
 
Join Date: May 2005
Posts: 192
Re: Spreadsheets

Does anyone know a good place to get good spreadsheet templates for this sort of thing? I'd like to have slick excel worksheets tracking my general finances, but it always seems so daunting to start one on my own (and I'm not all that good with computers). I think that if I had a good one to begin with, it would be easier to modify it fit my needs, rather than try and create one from scratch. Thanks!

Barry
__________________

Niko is offline   Reply With Quote
Re: Spreadsheets
Old 07-10-2005, 07:38 PM   #19
Full time employment: Posting here.
 
Join Date: Oct 2003
Posts: 961
Re: Spreadsheets

Barry,

Nathan Gutman has some interesting spreadsheets, including a bugdet one I believe.

Quote:
Originally Posted by maddythebeagle
Do federal employees have a 457k (deferred comp.) like state and local grunts?
Federal Employees + Military people have the Thrift Savings Plan.

- Alec
__________________
ats5g is offline   Reply With Quote
Re: Spreadsheets
Old 07-10-2005, 07:43 PM   #20
Recycles dryer sheets
 
Join Date: May 2005
Posts: 192
Re: Spreadsheets

Thanks, Alec! I just took a quick look at it. His site seems to have some of the things I'm looking for. I'll give it a more thorough readthrough later tonight. Barry
__________________

__________________

Niko 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
Google Spreadsheets Rich_by_the_Bay Other topics 30 08-03-2006 09:09 PM
Google Spreadsheets mickj Other topics 1 08-02-2006 12:42 PM

 

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