Spreadsheets

[Helen]

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

 

[malakito]

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

 

[Nords]

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...)

 

[wildcat]

Just curious does everyone recommend Quicken? I have used Excel (bone dry version of tracking expenses) but I imagine there are better packages.

 

[Newell]

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.

 

[Nords]

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.

 

[MJ]

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

 

[kat]

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

 

[cute fuzzy bunny]

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

 

[grumpy]

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

 

[HFWR]

My spreadsheet has six columns: one for each powerball number...

 

[Sheryl]

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.

 

[JDARNELL]

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

 

[sailor]

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.

 

[Newell]

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.

 

[Nords]

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.

 

[maddythebeagle]

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.

 

[Niko]

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

 

[ats5g]

Barry,

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

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

Federal Employees + Military people have the Thrift Savings Plan.

- Alec

 

[Niko]

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

 

[dandan14]

I'm really impressed with the level of spreadsheet sophistication here. I've used excel to track networth and such for years, but not quite as detailed as what some of you have listed.

Whats the possibility of getting some of these spreadsheets posted to the board? I would love to take a look at some of these!!

Thanks.

 

[SteveR]

I use Money....I know I know, Microsoft sucks but I have been using it for too long to even think about moving data to another program.

I track just about everything...I have no life I have to admit but with my DW working crazy hours it keeps me off the streets and out of trouble and gives me DATA from which to make better guesses.

I have also created a number of spreadsheets in Excel to run more detailed What If's about expenses, future raises, stock options, 72(t) distributions, IRA income-distributions and to keep track of my DVDs and CDs.

Is anal retentive spelled with a hypen? :

 

[TromboneAl]

Here's a spreadsheet that I use a lot:

 http://members.cox.net/macymusic/GroceryPriceList.xls

Others:

Propane price
Propane usage
Health insurance plan comparisons (scenario testing)
Annual expenses
True cost of college (e.g. subtract driving costs, etc.)
Determine when to take SS
Calculate cost to heat a room with space heater
Evaluate Roth conversion
Evaluate whether Rottentomatoes.com reviews are accurate for us

For the last one, I plot my rating of a movie (0-100) against the rottentomatoes.com rating of that movie.  The result was that there was not a very good correlation:

 

[justin]

Is anal retentive spelled with a hypen? :

Not sure. Hyphen is spelled with an "h" though.

Al,

It looks like you and rottentomatoes are using different rating scales. You appear to rate from 0-100. From your plot, rottentomatoes generally rates from 60-100 it seems. What is the r squared on that plot? 0.1??

 

[SteveR]

Justin,

I was not sure who would take the bait but I guess you win the prize as the most anal-retentive one here so far. Yes, I do know that hyphen does have an h in it. Glad to see there are folks here with nothing better to do than spell check others' posts.

Keep up the good work.