Comprehensive Guide to Tracking Your Finances

I’ve got a few questions for you about your finances… go ahead and tally up the questions you can respond to. What investment returns would you need if you wanted to retire today? What dividend stocks have posted consistent returns annually? How much did you spend last month on food? How much did you earn in dividends? At current rates, how long do you have to wait to retire? If you’re laid off, how long will you last without a job? What percent of your income have you saved each month? Is your savings rate better than last year? If you want to travel, what type of hotel costs the same as staying at home? Exactly how much do you have saved in checking accounts, safety deposits, investment accounts, your 401k? What were your big expenses two months ago?

A few years ago I couldn’t answer any of that. Now, it’s a button-click away. So I figure it’s about time for a comprehensive summary of my method for tracking all my retirement and financial independence variables. First I’ll go through some arguments why you should use this method for tracking data, then I’ll go through how to set it up for yourself and use it. Finally, before we get started, a big shout-out to Akratic on the ERE forum. Although I’ve made changes, updated, and re-arranged, I’d estimate that 75% of the spreadsheet is based on his original version.

What Does This Data-Tracking Method Give Me?

Before 2011, my biggest financial problem wasn’t income, or student debt, or anything like that. It was that I had no idea what I was spending, earning, or how my finances impacted my future! This is bad from a pure empirical perspective, but it felt even worse psychologically. I was unmooored – unconfident in my finances and unsure of my future. Sure I had some savings in the bank, but what if I was laid off? When I tried to cut costs, was I really making progress? How could I best reduce expenses if I didn’t know what I spent on?

Those are reasons for tracking your finances. Why use this sheet?

It gives you access to a ton of your data, in a way that makes sense for you, which you can configure how you want. Also it’s free. All those questions above – you’ll get answers to them. I’ve tested this sheet for two years, and I think other people have used derived versions of the same sheet. So I’d say it’s well-polished and has proven its usefulness.

Why use a spreadsheet at all?

There are other options for saving all your financial information. You could use something like Quicken, or you could try Mint.com or other websites.

I’m not going to pretend that a spreadsheet is more automated than either of those two solutions — it’s not. But the total amount of time you can expect to spend is probably a few hours of setup and adjusting to perfection, and then maybe 2-5 minutes entering data each day, and an hour reviewing once per month. I think this is well worth it for any data geek (or anyone who wants to carefully analyze their finances).

I prefer the lac of automation, anyway: it’s difficult for me to gloss over expenses. It forces me to review all my account balances monthly to make sure they’re sensible. It reminds me to pay bills which can’t be automated.

Finally, Quicken and Mint are not built for people like you or me who are considering retirement or financial independence. The data that we most want concerns time to retirement, dividend stocks, and various ways of viewing this information. The worst thing is inputting all your data into a system and then finding that it won’t tell you what you want. With a spreadsheet, that data is always available, just a formula away. Not to mention, it’s a whole lot easier to re-arrange data in a spreadsheet than in proprietary financial software.

One last thing – although this spreadsheet is manual, that means it’s one less safety risk for you. No providing bank account info to external parties.

You’ve convinced me! How can I get started?

The first thing to do is to go to the google docs page which contains the spreadsheet.

Upon opening, you’ll see a row of tabs at the bottom. At the top is standard file info; there are controls for display and a function bar (this is useful if you want to write your own formulas). Under “File,” select “Make a Copy,” put in your new filename, and you should be ready start entering your own data and exploring the spreadsheet. See below for information on all the features.

I’ve filled in this spreadsheet with fake data, so don’t try too much to match the stated expenses to income or other fields. For your own version, you can zero everything out and just replace the data for January.

Summary Tab

The first, summary, tab is intuitively titled “Summary”. This is your dashboard for monthly info. Total expenses, investment returns, net worth, time to retirement, target savings rates, Your Money or Your Life crossover points, and much more is presented here.

Most of this data is aggregated from the other sheets. I manually enter my average investment income from my stock broker, but this is a redundant field you may not want to use. Otherwise you shouldn’t have to adjust anything on this tab. There are some rows with subtly different data. For instance, “Simple Months Saved” assumes you have only cash and spend your current expense level each month. “Months Saved with Interest” uses a standard formula to calculate your interest earned, less the previous month’s expenses, until you run out of money.

However, there’s some things to play around with (helpfully titled “To Play Around With”). First there’s the SWR, which is what you estimate as investment returns. You can see how bull or bear markets could impact your future. Generally this should be kept at 4%, but if you’re conservative, change it to 3% (or experiment with 5%, etc).

Second, there’s a standard of living adjustment. If you currently live in an expensive city and plan on moving to a cheaper city (or a cheaper apartment, etc), adjust this. Alternately, if you plan on moving somewhere more expensive, you can adjust for that as well. The value “100” will reduce your expenses by 100, and trigger a recalculation of all the derived values. “-100” will increase your expenses by 100.

Finally, post-tax income can be used to adjust your savings rate. Maybe you’re 10 years from retirement, but if you get that $5k raise, you’ve just saved 2 years! Or you hate your job and want to move somewhere calmer, it will only cost you 1 year.

Finances Tab

Here is a month-by-month breakdown of your expenses and income by category. Check to see your trends on food expenses, entertainment, clothing, or something else.

Also useful is a savings rate check (and graph). Standard financial advice is to save 10% of your salary. Terrible! See if you can keep it at 50%, 60% or even 90%. The higher the better.

All the data here should be pulled from the other sheets – no need to enter anything at all. Just double-check some of the values.

+FI/-FI Tabs

The next two tabs are data tabs, representing income and expenses (+FI signifying income – increasing financial independence).

Taking a peek inside, there are two internal columns (colored light gray), and then columns for the year, month, category, and total expenses.

The behavior for income and expenses is a little different. Typically I only have 2-4 sources of income in a given month: my paychecks, some dividends, maybe a gift or selling a car or something. So I record all this directly on the +FI tab.

On the other hand, I usually have a ton of individual expenses. I want to store these each day. I do that on the next tab, itemized expenses. -FI should pull the data from there automatically.

As mentioned, I edit the +FI tab directly, adding a new row whenever something hits my bank account.

The -FI tab needs to be edited as well: each month I will copy the lines from the previous month and increment the month (eg, from 1 to 2 when starting February). After this I confirm that the greyed-out lines match up with the new month. Then, I can ignore this tab for another month.

Itemized Expenses tab

This is definitely the most-edited tab. Each day when I spend money, I go in and choose a category for each expense, and write a little note with details.

As with the FI tabs, you should double-check your data, particularly if copying or pasting rows. These expenses should be aggregated automatically in the -FI tab.

Net Worth Tab

Arguably my favorite tab, this is dedicated to showing the change in your net worth over time, breaking it down into separate accounts and debits.

The first column is used if you want to set aside certain accounts for non-retirement purposes – maybe travel or a new car, maybe you treat your 401k differently. “1” indicates something that will be used for retirement. “0” indicates something that is not intended for that.

The next column is just a convenient placeholder, if you feel like storing your account name. All the following columns are basically raw data. I enter this info on the 1st or 2nd of every month. It provides a good break and hopefully shows some progress I might not have felt on a day-by-day basis.

Investments Tab

This final tab is an attempt to track investments. It will pull in data automatically from external sources like Yahoo! Finance or Google Finance.

This is in no way intended to be comprehensive. Mostly I wanted to screen and review stocks based on dividend info, which my broker is sorely lacking, and which is difficult to drill into on most sites.

First there’s some raw data. You should enter your shares and unit cost, everything else on the row should be calculated automatically. PnL stands for “Profit and Loss,” and is the total amount of money you have earned or lost so far (in pure stock price terms). Percent gain is relative to the amount you invested to start with. The market price used for these calculations is pulled from Google Finance automatically.

EPS stands for “Earnings Per Share,” and is the reported earnings for the company over the last year. This is also sourced from Google Finance.

The next set of columns is about dividend info. The data here is actually a manipulated table of each individual dividend payment from Yahoo! Finance and can be a little rough. In particular, if a company changes the dates of its dividends, that is not reflected.

Payout is the amount of earnings that were paid to shareholders as a dividend: a high value here is bad, because it is usually less sustainable (there is leeway for the company to sustain bad quarters without cutting dividends). If I see this at 80% or 90% I will investigate.

Annual Dividend and Annual Income are the total dividend payouts over the last calendar year, and the total income you received from the company over the last calendar year (assuming you owned it for the full year).

Previous Dividend and Previous Income are the same values, but for the year before. The idea here is to check, on a stock-by-stock basis, whether you received a “raise” or “pay cut” for holding this stock.

Finally, there’s a dividend worksheet. I use this for screening. Enter a name and you’ll see the total dividends each year for the past 5 years, the percent increase or decrease in dividends, and finally a plot of price changes over time. I would absolutely double-check this data befor e making a purchasing decision – but I find it useful to start my buying (or selling) process.

End

Hopefully this was useful. If you have any questions, feel free to ask below and I’ll try to answer as best I can. If you use this and have any upgrades or suggestions, I’d be happy to hear those as well! I’ll also try to keep this guide as updated as possible, as things change over time.

Advertisements
Previous Post
Next Post
Leave a comment

6 Comments

  1. thankful visitor

     /  July 23, 2012

    I just discovered the spreadsheet and have not thoroughly understood everything. I guess this feeling will go away after I played around it a little bit 🙂

    Just wanted to say Thank You! for the work and thoughts you put into this sheet and thanks again for releasing this to the public! I will definitely pimp my own spreadsheet and incorporate some of your ideas. Thanks!

    Reply
  2. Debbie M

     /  August 19, 2012

    Thanks for this thorough sharing. I have been pretty happy with my own spreadsheets until I decided to track my spending. I’m not interested in using your sheets wholesale, but then you have too many ideas I want to use for me to be able to just tweak what I have. I’ve decided to completely redo my stuff–I need to get rid of old baggage from copying notes from year to year, and I’m re-evaluating how to split things into the different worksheets to maximize efficiency and usability. Some good ideas I’m taking from you/akratic (don’t remember seeing his version and couldn’t find it):
    * using one column for category names (and one for names + month) and using the sumif function to find sums rather than making a different column for each category and using the sum function to find sums
    * being more thorough about detailing things one one page and summarizing them on another
    * I still can’t figure out how to keep stock information automatically updated from the internet, but that would be cool.

    Extra ideas I’m adding that might interest you:
    * not just a page for stocks, but also ones for bonds, mutual funds, other assets (savings accounts, car, house), and my pension
    * a page for savings where I list out both all the places where the money is and all the things the money is for. If all your stocks are for retirement and all your savings are for emergencies, you don’t need this, of course, but I like to save up for and keep track of things like car repairs, saving for my next car, housing upkeep, housing renovations, health spending, and vacations/computers.
    * I like to keep a running monthly total on the cash flow (+FI and -FI) sheets
    * I keep a list of categories at the top of the cash flow pages so I don’t forget what they are
    * In stocks I have a column for the sector so I can track my diversification

    Reply
    • m741

       /  August 19, 2012

      Hey, thanks for the info. Stock info is compiled if it’s available from google finance (things likeP/E and price are there but other stuff is less reliable. Dividends are from scraping Yahoo! Finance). But I don’t really trust this data enough to try to investigate sectors, etc.

      The running monthly total is already available on the ‘Finances’ page.

      The list of savings and categories is a good idea.

      Reply
      • Debbie M

         /  August 19, 2012

        That’s true about the running total. I think it might be more helpful for me if it’s also right there when I’m adding the data. Also, that way I can compare my monthly totals for the same day of the month in previous months. But I may later find that it’s just messy.

  1. The Lepore Family Finances | James Lepore
  2. lifewithabrain.com » A Financially Independent Millionaire Before 35

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: