Manage my Finances

March 17, 2012 3:36 PM

Does anyone else manage their finances in excel? How exactly do you do it? I would like to manage my (pretty straight forward) personal finances. Alternatives to excel welcome.

Please dont offer me something US based because that wont be much use to me! But i'm open to alternatives. If anyone does it in excel and has a template it would be fantastic if you could show me!

posted by bruinbruin to Work & Money (19 answers total)

When you say manage what do you mean? Do a budget, track every penny you spend and analyse out?
posted by koahiatamadl at 3:40 PM on March 17, 2012

Response by poster: I mean track every penny in and out and in the best case scenario also analyse and see trends and charts over time for things like transportation costs or food costs.
posted by bruinbruin at 3:42 PM on March 17, 2012

Alternatives to excel welcome.

I use Gnucash, which is free and good.

It uses double entry bookkeeping, which (if you're not already familiar with it) may take a bit to get used to, but once you've wrapped your head around its basic idea, it starts coming pretty naturally, in my experience.
posted by Flunkie at 3:46 PM on March 17, 2012 [3 favorites]

I would start by asking yourself what the output of the analysis needs to be. Is this something just for you or are you planning to share it with an accountant/tax inspector, etc? One of the major reasons why people use bespoke financial tools other than Excel is that these better at getting you to a state where you can spit out results in the format that such people expect for your particular country (I have just spent a few days getting to grips with specialist accountancy software for my French business: I could have used Excel but I would have had to spend more time studying exactly what output documents where needed and how they should be laid out).

If you do want to use Excel then consider starting with some of the template documents that Microsoft make available - your problem will be link these together intelligently however.
posted by rongorongo at 3:57 PM on March 17, 2012

I use Pear Budget (the spreadsheet version), from MeFi member Alt F4. I have added another sheet to the spreadsheet to track the balance of my various savings categories - I put aside a certain amount each month for utilities, car expenses etc, so I have a little sheet to show what my current savings balance for each of those.
posted by AnnaRat at 4:00 PM on March 17, 2012

I use excel for all of my personal finance bookkeeping. My set up is a single Excel file with the following tabs:

1. Monthly income/expense totals
(I downloaded a template from Microsoft's template page. They are USD based, but it works just as well with other currencies. I ignore the dollar signs and just enter everything directly in yen.)

2. Rundown of daily expenses
(Simple spread sheet with the date, and an item by item list of what I spent money on that day. I keep my receipts and just log everything in at the end of the day. There is also a column for "needless expenses," where I can count how much money was unnecessarily spent on stuff like eating out when I could have cooked at home, etc.)

**The above tabs are kept monthly (payday-to-payday). I start a new sheet every payday.**

3. Bill tracker
(A list of all of my bills with the total amount due, due date (also put into my Mac calendar), and $difference and %difference with previous month (useful for keeping tabs on my electricity and gas usage.)

4. Loan repayment tracker
(List with due and payment dates, amount paid, and loan balance. The due date is also linked to my Mac calendar).

5. Savings log
(Keeps track of how much I intend to save each month and how much actually gets saved. I plan ahead for 12 months and include how much of the saved $ I intend to use for planned large purchases.)

My system is simple and surely could be improved, but it has done wonders for my personal finance management. The daily input of expenses is the most important part for me. I want to see the lowest number possible each day, so it makes me think carefully about how I use my money and whether a purchases is really necessary (or excessive).
posted by Kevtaro at 4:30 PM on March 17, 2012 [2 favorites]

I use Excel. I have a spreadsheet where I keep track of
1)payments out (one for the beginning of the month, one for the end)
this includes, rent, utilities, savings, insurance, food, etc. and incidentals related to my cars
2) money in
3) money left

It also allows me to track bill paydowns, how much I have left to pay. Right now I am completely out of debt.

I keep a tally of how much is going out, how much is coming in, how much I have left.

It is pretty simple. I do not keep track of daily expenses, but I pretty much know what I spend each day and never need to keep track.
posted by wandering_not_lost at 4:41 PM on March 17, 2012

I use Mint to track spending by category, then I transfer the monthly totals to a spreadsheet. It's real simple. Down column A is my various spending categories (mortgage, groceries, etc) and across the top the first column is my budget and then there is a column for each month. I calculate averages at 6 mo and 12 mo.
posted by COD at 5:47 PM on March 17, 2012

Check your memail!
posted by peanut_mcgillicuty at 6:15 PM on March 17, 2012

I use Excel for my own finance, but it is not much more than a check register. One sheet for each month, one row for each bill that I pay, and over time I've added columns to track the running balance on various accounts. (In addition to tracking loan balances, I tend to pay my bills in round numbers for simplicity. If a bill is $78.54, I'll pay $80 one month and $75 another month.) The final column is the check-register portion, where I track the money flowing in and out.

I tried switching to gnucash, but I just don't want or need to track every penny or every transaction. It seemed far too complicated. Which is good if that's what you want.
posted by gjc at 7:17 PM on March 17, 2012

Mint.com is totally US based and therefore a terrible answer to the question asked.

Before I lived in the US, I used Money Manager, not sure how well supported it is these days but is great if you are able to enter each transaction you make. You categorise each transaction when you answer it and then can call up a report of 'money per category' for previous months. It's all manual, doesn't pull info down from your bank or anything.
posted by jacalata at 2:14 AM on March 18, 2012

I am very happy with You Need A Budget. It is tailored to a particular way of money management (essentially the envelope system) which you need to learn in order to use it effectively. It started out as an Excel spreadsheet, so even if you don't like it, it may give you some ideas.
posted by sesquipedalian at 3:23 AM on March 18, 2012 [1 favorite]

If you have an iPad or iPhone (or a Mac, soon, maybe), have a look at MoneyWiz. It's the first personal finance app that I've actually been able to use.
posted by Skyanth at 3:46 AM on March 18, 2012

I also use the spreadsheet version of Pearbudget (for 7 years now). Which country you are in isn't an issue since it just takes whatever inputs that you give it.
posted by nolnacs at 5:45 AM on March 18, 2012

I wouldn't advocate using spreadsheets because you can easily bump into things you'd like to do that get needlessly difficult.

When I used to do this kind of thing I used Gnucash. It's not pretty, and it can seem intimidating and complicated, but it is powerful, flexible and free.

Other people I know also use Quicken. There's not a UK specific version any more, but you can either use an old UK version or get the US version and just ignore that the currency is stated as dollars.(As long as you're only tracking and analyzing your income and spending, rather than trying to interface to your bank, do tax calculations etc, it doesn't matter that it's not UK specific.)

If you're happy to push your financial data to a website - which I'm not - moneydashboard is a UK site, somewhat like Mint, which might be worth a look.
posted by philipy at 8:59 AM on March 18, 2012

I think I am currently doing exactly what you want to do in Excel.

I keep it all in a single spreadsheet and track every penny in and out and have done for the last 8 years or so. I started doing it when I started a PhD having worked for a few years and was suddenly much poorer and had to be very careful about spending.

There is an individual sheet per month which tracks largely spending out and unexpected income in (i.e. little windfalls, rather than my actual salary which is a regular thing). I try to pay for everything by card, and track those + the cash withdrawals. I track cash in a separate column which should match up to the total cash withdrawals, but I allow myself some leeway so I'm not too fussed about the cash column absolutely matching (I only allow myself to withdraw a fixed amount of cash each month in any case).

The columns in the monthly sheets are date, type, description, cost, way paid (i.e. which of my cards, cheque, etc.), a separate cash column after that and notes.

I have a visual basic macro that I run on this at the end of the year which summarises all my spending monthly over the year by category and additional macros that create graphs for me.

I have separate sheets in the Workbook that are my budget, salary, tax and mortgage calculations.

I have 2 bank accounts each with a current and savings account.

Account 1 receives my salary and I use it to pay all regular outgoings (mortgage, utilities, insurance etc go as direct debits). I use the savings account on account 1 to keep enough money to cover the annual direct debits (e.g. my flat management charge) so that I don't go overdrawn when they are paid (again by direct debit). I calculate in advance how much I need to keep in here.

I use Current Account 2 for all my discretionary spending, groceries etc. I pay myself a fixed amount each month from the Account 1 for this spending, and it is essentially this spending that I track in the Excel sheet. I also have an easy access savings account attached to account 2 in which I keep 3 × my monthly outgoings saved, just in case so I don't get penalised for dipping into long term savings.

This system, combined with my Excel sheet mean that I am always completely on top of my finances. I now earn enough that I no longer need to track every penny as I did when I set this up, but the system works so fluidly for me that I just keep doing it.

I've created a template to show you the basic structure (I have far more budget sheet and tracking sheets than there are here, but this is illustrative). Here's a link to the template. The (very simple) summarising macro is in the template.
posted by inbetweener at 11:01 AM on March 18, 2012

My wife and I did track every penny for about three months, mostly so we could get a handle on where we were spending money.

There's not a lot to it: save receipts, set up categories that make sense for you, and be rigorous about getting the receipts into the spreadsheet. It took us maybe 5 minutes a night, if that.

We used Numbers (Apple's spreadsheet program), not Excel, but same idea. This was before we had smartphones; if I were doing it today, I'd want to be able to log expenses incurred when out and about in my phone, and have that sync to something on my desktop. I'm sure there are lots of ways to approach that.

The way we set it up was:
Separate worksheet for each month.
Column heads were Date | By | Description | Category ("by" is the person who incurred the expense). If I were doing it again, I might not bother with separate monthly worksheets. It's easy enough to get monthly totals.
posted by adamrice at 11:07 AM on March 18, 2012

I use Google Docs. I was using a percentage-based system for awhile (35% to home, 25% to life, 15% to savings, 25% to debt or goal) but since my boyfriend and I joined forces, that hasn't been practical. What we did was have three columns: joint expenses, his expenses, my expenses. So, it looks roughly like this:

JOINT
Rent XXX
Insurance XXX
Utilities XXX
etc.
(total) YYY

ME

Income XXX

Expenses
(joint) =YYY/2
(RRSP) XXX
(bus pass) XXX
(cell phone) XXX
etc.
(total) ZZZ

Surplus =XXX-ZZZ

Then the same for him.

If you set it up right, you'll get a running total as you go. For instance, once I pay my share of the joint expenses, I am left with, say, $500 a month. So then I put in my fixed personal expenses (RRSP, bus pass, prescriptions and so on) and then the surplus box will tell me how much I have left for fun money. And for him, likewise.

I find that now that my finances are a little more complex, the percentages are less useful. Who cares if I am spending 20% or 21% on something if I still have money left over? Both he and I are frugal people who are not big spenders. We make enough money. So we plug in our incomes, then the fixed expenses, and we see what's left.

For tracking, I have most of it set up automatically. My RRSP transfers automatically from my account. We transfer money into the joint account twice a month and then withdraw cash which we put into jars. Groceries and entertainment spending comes out of those respective jars, cash-only. He withdraws once a month from the joint account to pay himself back for car expenses, and other than that, if either of us wants to track something, we can.

For my personal spending (fun money) I just use a simple column. I track purchases there and use an autosum to total the column. If it's under what I budgeted, that's all I care about.

If you want me to send you the spreadsheet, memail me with a proper email address and I can send it to you.
posted by JoannaC at 2:12 PM on March 18, 2012

I use Excel and EEBA. I was always annoyed at how various programs (Microsoft Money, Mint, others I can't recall) weren't easily usable in certain ways, weren't able to do what I wanted, or had more than I wanted. My system is a little complex and might be more than you're looking for but it covers everything pretty well.

An important starting point is that I budget monthly and the money that I make, say, this month, is not used until next month. I sit down on roughly the 1st of each month, go over my spending from the previous month, and then budget out my expenses for the upcoming month. After that I use the EEBA mobile application to keep track of expenses on a daily basis (whenever I buy something I record it).

Throughout the month I am recording my transactions in EEBA in different "envelopes" or categories such as gas, groceries, etc. At the 1st of a new month I cross-check the bank transactions and EEBA transactions from the previous month and adjust everything in EEBA as necessary (for example, if I forgot to record something I'll add it in to EEBA). After that EEBA lets me see how much I spent in each category and put it all in my spreadsheet, which I'll introduce next.

The main columns of my Excel sheet are "Future Budget", "To Date", and "Income". Sheets that I use but you might not want to use are "Savings" and a sheet with all of my downloaded EEBA transactions I use to crosscheck my recorded EEBA transactions with what the bank says I did. In the "To Date" column is where I'd put the expenses by category that I just determined from EEBA. In this sheet I also have how much I budgeted for that month so I can easily compare, and I have a few formulas doing averages and whatnot for me.

After I'm done with the retrospective stuff I budget for the upcoming month. I essentially determine how much I have in my checking account and in cash and subtract from that any debts I owe (credit card charges I owe from the previous month, any checks I've written that haven't been cashed yet, etc). I'll also add anything that I expect to receive soon (maybe my friend owes me $100 and I'm getting this tomorrow). I take this "money on hand" number (this should roughly match my previous month's income, which I recoreded on the "Income" sheet) and plug it into my "Future Budget" sheet where I have my expected expenses for the next 4-6 months laid out with some simple formulas that allow me to adjust expense categories to make everything balance. This is rough, but it gives me a good guide. I'm paying more attention to the upcoming couple of months than anything. After I figure out what my budget will be for the upcoming month (usually pretty similar each month aside from larger expenses for trips or big ticket items) I set my limits in EEBA, pay off my credit card, and don't open the sheet for another month.

The "Savings" sheet I tie into "Future Budget" to see how long I can live off of savings (which is always important to know when being a poor student). There are some formulas tying everything together.

Anyway, as others upthread, let me know if you want a copy of my actual sheet.
posted by Defenestrator at 2:55 AM on March 21, 2012