You know that feeling – no money left in the bank, but you are not really sure where it has all gone. Oh, you might be doing a bit of budgeting and have paid off all your regular expenses. What about the rest?

Once I got started on this, I expanded the question: Where do I spend my money? Is there some "GOK" account into which my money is leaking?

Step 1: Download your account statement for the last 1 year

To understand where I am spending my money, I downloaded my bank statement for the last 18 months in MS Excel format. Citibank, where I have my account, does not give an easy-to-use download format, at least in India. If your bank offers a way to download in some other format, such as MS Money, you may find it easier to do this analysis in MS Money itself.

Step 2: Remove inflow transactions

Once I had the Excel set up the way I wanted, I removed the "Credited" column, as my goal was to analyze my expenses. I have a stable job and the inflows are pretty constant. I also removed some superfluous transactions such as cancellations, fuel surcharge reversals etc.

Step 3: Categorize your transactions

I then categorized each transaction based on a pre-defined set of tags. For example, purchases at a music store would go towards Music while purchases at a supermarket would go towards Groceries. Of course, there might have been instances where I bought some music at a supermarket, but I had no way to get that data.

I tend to use cash a lot (primarily because plastic is not so common in India as in the US and Europe), so there were a lot of ATM withdrawals.

This was an eye-opener for me, as I realized could have done anything with those withdrawals. I might have bought groceries, fuel, music or any other expenses. I did not have that level of granular data! As I wanted to get to the end by now (!), I simply clubbed all of them under household expenses.

Step 4: Analyze results

Once the transactions were categorized, I used some formulae to sum the transactions under their respective categories. A pie chart seemed the best way to show the transactions by volume, so I made a pie chart of my top expense categories.

graph1

The first category was the "household expenses" as expected. The second largest category was a one-time mortgage payment. Events, Food and Travel expenses were the next set of expenses.

Step 5: Create Goals and Make a Plan

This is all very well, but what is the use?, you may ask. Based on the data, I am not able to really see the opportunities for cutting the flab. I decided there were a few things I needed to do:

  1. Keep a more granular track of ATM withdrawals. I decided to log each day’s transactions in an Excel and treat the money with me as a sort of cashbox. While I need to be vigilant, I decided to adopt a balanced approach and track major expenses. Minor ones would be a sort of miscellaneous category.
  2. Increase use of electronic transactions in places where a Debit card is accepted. My bank does not charge me on any debit card transactions, so I should start keeping less cash.This would create a more granular transaction list.
  3. Keep a separate track of discretionary expenditure. I will keep the bills when we eat or go out for movies etc.
  4. A stretch goal would be to identify impulse purchases when we go shopping to the supermarket. While we do make a shopping list, we tend to pick some other times which we think might be good to enjoy. I am not probably going to cut those impulse purchases out, but at least know how much I spend on them.

I will redo this exercise 3 months later to see if I can identify areas of improvement. I have also promised myself that any savings I see would go into the Emergency account, which is dangerously lean these days.

Do you track your money using Excel or other methods? Share your comments, tips and tricks with us.

del.icio.us Tags: , ,