Use Excel for Your Finances

Have you ever thought about getting a better overview of your finances? This article shows you a simplified version of an 'accounting' for non-businesses, which will enable you to get an overview not only on where you are financially, but also to see where you are going, and why.

Steps

Calculating your net worth

  1. Decide on your level of detail; for example, considering everything worth more than 1% of your yearly income is a starting point.
  2. Build an Conduct a Home Inventory in the first column. An inventory is a list of all the assets you own that are worth anything. To keep it simple, you should only consider assets that are of relevance; so list cars, houses, motorbikes, bank accounts, CDs, 401-ks, etc.; but skip the skirts in your closet, the books in your shelves, or the old TV.
  3. Estimate the current value for each item, and put it in the second column. Be sure to be realistic; even though you paid 1299 $ for that plasma TV, what could you seriously get if you sold it now? Most household articles loose 50% right when you take them out of the shop. On the other hand, cars lose about 50% every three years, and houses only a small percentage if at all. Of course, bank accounts and cash is worth its current total.
  4. Add to the list all your Take Control of Your Debts, loans, mortgages, credit cards, and other open payments of relevant size. Spend some time to make the list complete; don't leave out things because they are 'kind of special' or 'everyone has them', or 'they will be soon gone'. For everything you owe, put the amount in the value column as a negative number.
  5. Build the total of all estimates. This is your net worth.
  6. If you would like it more detailed, go back and add more lines for smaller things, of both groups (owned and owed).

Net worth development - simplified analysis

  1. Repeat the net worth calculation after a month, or a year. Copy the sheet you used, and start adapting it. Consider the list of items, and correct the worth where necessary, for example, deduct 1% for a car per month, and especially update the fast-changing things, like bank account and cash. Remember also your loans might have decreased, if you paid something off.
  2. Compare the new total with the previous one. Did it increase, or did it decrease? If it decreases, you might be living beyond your means. If it increases, you are accumulating wealth, which is generally a good thing.
  3. Divide the difference by your income for that period, and consider the resulting percentage - this is called the savings quota. Typical US customers have a low savings quota, around 0% or even negative. In many European countries, a savings quota of 15-20% is considered normal.
  4. Don't interpret too much into the first number. All your little estimation errors accumulate, and the result may just not be exact enough. Keep repeating it after another month or year. If you see a consistent direction, and you are sure you are not forgetting something relevant, take it more serious.
    • It is your decision what savings quota you want to target at, but with 0% you will have nothing when you retire, which does not sound to attractive. On the other hand, many people would consider saving 20% of their income as too much. You should try to stay safely away from 0%, and of course on the positive side.

Having a personal accounting

the next level of detail would be to analyze what is flowing where, so you know where your money is going. To establish this, a bit more work is needed.

  1. In the inventory from above, put a number or code onto each line. This will be your chart of accounts. You can use any coding you like, for example numbering them 001, 002, etc.; or using letters, like c01, c02 for credit card, b01, b02 for bank accounts, or whatever.
  2. Add further lines (accounts) to the inventory, which describe the different types of things you spend money on. Be as detailed or rough as you want to know it later; start rather small, and if you value the results, get more detailed. Typical lines would be 'food & drinks', 'gas & other car related', 'house related'. Simply come back here and add more lines when you find you missed something.
  3. Assign codes to these lines too. Don't duplicate codes already used.
  4. Start a new sheet, which will contain the log. Each transaction in your finances will be recorded in this sheet as one line. Create columns for 'date', 'description', 'amount', 'from account', and 'to account'.
  5. Now the real work starts - add a line for each transaction. This is an ongoing process, not a one-day activity. Every evening, or latest every weekend, add the lines for all transactions. For example, if you get cash from your checking account, you would add a line like: '3/8/09 | took out cash | 200.00 $ | 100 | 120', where 100 and 120 would be the account numbers you assigned to 'bank' and 'cash'. If you shop in the supermarket, the line might be '3/9/09 | supermarket | 69.50 $ | 120 | 410', where 120 is again the account number for 'cash', and 410 is the code for 'food'.
    • Start calculating. Once you have some lines collected, you can make Excel do the math: for each account number, add with a formula all changes that affect it together. Whenever the account number is in the 'from' column, subtract the amount; if it is in the 'to' column, add it. For example, with the two example lines from above, the cash account would have a total of +200.00 -69.50 = +130.50, plus whatever the cash was before. You will need some more complex excel techniques for this, it is not trivial. There are many ways to do it in Excel, one option is a matrix formula, which might look something like "=SUM(IF('Log'!$E=$C2;1;IF('Log'!$D=$C2;-1;0))*'Log'$C)" - this means compare the values in column E and D of the log to the current lines code, and if they match, add the amount from column C, multiplied by 1 or -1.
    • The result will show the new totals for all your accounts in the inventory, and in the second part of that sheet, the cost for each line; for example the line 'food' would show the total of 69.50 $, which is what you spent for it.
    • If you use this process, you do not need to make a new inventory every period - it gets calculated automatically. You should check it for correctness, though; any differences mean that you forgot lines in the log.
    • You will be able to see your net worth changes all the time but be careful to analyze them only after some longer period. Obviously, the day you get your paycheck, your net worth jumps up, but the next days you are only spending, so it goes down again. Compare it only for corresponding days, like each time before your paycheck comes.

Tips

  1. Don't over-detail in your first try. You might end up being frustrated. Basically, if you go to a level of detail like a business has to do (where every cent needs to be recorded), it would be an hour of work a day.
  2. On the other extreme, don't oversimplify. If you enter only ten lines a month, you cannot expect to get any new insights out of it.
  3. It might be worth to do a very simple version to get used to the concepts.
  4. You can also buy dedicated software for this, but it costs money. Excel can clearly do it, but you have to invest a bit more time.
  5. If you want more help, and you know a CPA, they will probably recognize the concepts right away, and be able to help you.
  6. If you want to have it a bit more professional, sort your inventory by the accessibility of the values, from low to high. This means, that a house would come first, as it is the most effort and time to liquidate, then probably cars, your TV, the bank account, and finally the cash. The same sorting applies to the owed part - first the mortgage, then the car loan, credit cards, and last the twenty bucks you need to give your colleague back for your lunch.
  7. Once you get more advanced and experienced, you can start adding 'planned' lines to your log - things you know are going to happen, like the next mortgage payment, the next check, an upcoming vacation, etc. That way, you can predict your future financial situation much better (and react early).

Warnings

  1. Do not expect useful results with ten minutes of work. It will take you some hours before the quality and level of detail is good enough to give you useful information.
  2. Be mindful on where you store the Excel sheet; it will contain a considerable amount of information about your financial situation that you might not want public (not even to your friends maybe).
  3. As always with computers, save often, make backups. There is nothing more frustrating than losing hours of work by some stupid error or blue screen.

Things You'll Need

  1. File MS Excel Records when You Add Them (any version), or any other capable spread sheet application

Related Articles

Retrieved from "https://kipkis.com/index.php?title=Use_Excel_for_Your_Finances&oldid=276476"