8

I have a monthly household budget where I track income and expenses broken down by category. It's just an Excel spreadsheet where I enter the amounts from all receipts into the appropriate category.

It's quite easy to make a mistake when calculating expenses. For example, I might mistype a number from a receipt, or forget about a purchase entirely if I didn't get a receipt (which happens sometimes for a variety of reasons).

So I'd like to verify what appears in my spreadsheet against what's actually in my bank account. Let's say April started with a balance of 0, my income was $1000, and my budget spreadsheet shows that my expenses so far were $900. So I'd like to look at my bank account and see that there is indeed $100 left there.

A long time ago when I only used one debit card, it was quite easy to do. Currently, I use multiple credit cards, and it complicates things. Credit cards have opening/closing dates that usually do not correspond to a calendar month. It may run from April 15 to May 14, for example, so when I'm paying, this covers part of April expenses and part of May expenses. Different cards have different opening/closing dates, so I can't just shift my budgeting period to correspond to the opening/closing dates. Plus, the payment is due several weeks after a closing date. Plus, a purchase I make on April 30th may be reported a few days later and will appear on the May statement.

As a result of all that, the balance of my bank account today has very little to do with the balance in my budgeting spreadsheet.

How do you verify your budget "on paper" against real balance in the bank? I imagine there must be ways to do that because company accountants must have been doing this for centuries.

I don't want to just get rid of the credit cards because they are useful in several ways (cash back, credit history, and so on).

I've heard about tools like Mint.com, Quicken, and YNAB, but it's not entirely clear from their description whether they can do it.

What I need is something that can give me two views of the budget. The "conceptual view" would be something like "in April, you made $1000 and spent $900, so you have $100 more to spend total, of which $50 is in fast food category and $50 is in entertainment category". The "physical view" would be something like "given that your paycheck came on April 15th, and you paid off credit card 1 and didn't yet pay off credit card 2, there should be $643 in your bank account as of today".

A bonus feature is if I could assign each expense to a category of my choice (I've seen some categories that software comes up with automatically based on vendor, and didn't like those).

  • Do you specifically want to verify it against your bank account balance, or do you just want a backup log that will provide a secondary record of each transaction? Software such as Quicken will allow you to see what money you have spent so far, but for things like credit cards, for the reasons you mention, there may still be a lag between when the charge shows up on the credit card and when that money comes out of your bank account. – BrenBarn Apr 28 '15 at 4:28
  • Yes, I'd like to verify it against the bank account balance. I just use credit card statements to have a backup record of transactions. – Eugene Apr 28 '15 at 20:32
  • I see. I think that is going to be difficult for the reasons you described: ultimately your bank balance will reflect payments to different credit cards whose billing periods may not align. We'll see if anyone offers any solutions. My suggestion would be that it might be easier to recalibrate your notion of how/where to "verify" your transaction record. For instance, if you buy groceries for $20 with a credit card, you don't need to wait for that charge to actually be paid from your bank account in order to match it against your budget; you have already spent the money. – BrenBarn Apr 28 '15 at 20:58
  • Have you tried using mint.com? It does most of the things you describe and is mostly automatic. I go in about once every two weeks to do clean up (miscategorized transactions, adjust for split checks at restaurants, enter cash spending etc) – Jay May 3 '15 at 18:30
  • ugh, there are so many corner cases, too! for instance, in February my credit card company credited my amount on the last day of the month, but the payment didn't actually come out of my account until the first day of March, resulting in my net worth calculation for the month (cash - liabilities) being WAY off - and it took me way too long to figure out why! – Michael Apr 5 '17 at 17:57
14

What you are describing sounds a lot like the way we handle our household budget. This is possible, but quite difficult to do with an Excel spreadsheet. It is much easier to do with dedicated budgeting software designed for this purpose.

When choosing personal budgeting software, I've found that the available packages fall in two broad categories:

  1. Some packages take what I would call a proactive approach: You enter in your bank account balances, and assign your money into spending categories. When you deposit your paycheck, you do the same thing: you add this money to your spending categories. Then when you spend money, you assign it to a spending category, and the software keeps track of your category balances. At any time, you can see both your bank balances and your spending category balances. If you need to spend money in a category that doesn't have any more money, you'll need to move money from a different category into that one. This approach is sometimes called the envelope system, because it resembles a digital version of putting your cash into different envelopes with different purposes. A few examples of software in this category are You Need a Budget (YNAB), Mvelopes, and EveryDollar.

  2. Other packages take more of a reactive approach: You don't bother assigning a job to the money already in your bank account. Instead, you just enter your monthly income and put together a spending plan. As you spend money, you assign the transactions to a spending category, and at the end of the month, you can see what you actually spent vs. what your plan was, and try to adjust your next budget accordingly. Software that takes this approach includes Quicken and Mint.com.

I use and recommend the proactive approach, and it sounds from your question like this is the approach that you are looking for. I've used several different budgeting software packages, and my personal recommendation is for YNAB, the software that we currently use. I don't want this post to sound too much like a commercial, but I believe it will do everything you are looking for.

One of the great things about the proactive approach, in my opinion, is how credit card accounts are handled. Since your spending category balances only include real money actually sitting in an account (not projected income for the month), when you spend money out of a category with your credit card, the software deducts the money from the spending category immediately, as it is already spent. The credit card balance goes negative. When the credit card bill comes and you pay it, this is handled in the software as an account transfer from your checking account to your credit card account. The money in the checking account is already set aside for the purpose of paying your credit card bill.

Dedicated budgeting software generally has a reconcile feature that makes verifying your bank statements very easy. You just enter the date of your bank statement and the balance, and then the software shows you a list of the transactions that fall in those dates. You can check each one against the transactions on the statement, editing the ones that aren't right and adding any that are missing from the software. After everything checks out, the software marks the transactions as verified, so you can easily see what has cleared and what hasn't.


Let me give you an example to clarify, in response to your comment. This example is specific to YNAB, but other software using the same approach would work in a similar way.

Let's say that you have a checking account and a credit card account. Your checking account, named CHECKING, has $2,000 in it currently. Your credit card currently has nothing charged on it, because you've just paid your bill and haven't used it yet this billing period. YNAB reports the balance of your credit card account (we'll call this account CREDITCARD), as $0.

Every dollar in CHECKING is assigned to a category. For example, you've got $200 in "groceries", $100 in "fast food", $300 in "rent", $50 in "phone", $500 in "emergency fund", etc. If you add up the balance of all of your categories, you'll get $2,000.

Let's say that you've written a check to the grocery store for $100. When you enter this in YNAB, you tell it the name of the store, the account that you paid with (CHECKING), and the category that the expense belongs to (groceries). The "groceries" category balance will go down from $200 to $100, and the CHECKING account balance will go down from $2,000 to $1,900.

Now, let's say that you've spent $10 on fast food with your credit card. When you enter this in YNAB, you tell it the name of the restaurant, the account that you paid with (CREDITCARD), and the category that the expense belongs to (fast food). YNAB will lower the "fast food" category balance from $100 to $90, and your CREDITCARD account balance will go from $0 to $-10.

At this point, if you add up all the category balances, you'll get $1,890. And if you add up your account balances, you'll also get $1,890, because CHECKING has $1,900 and CREDITCARD has $-10. If you get your checking account bank statement at this time, the account balance of $1,900 should match the statement and you'll see the payment to the grocery store, assuming the check has cleared. And if the credit card bill comes now, you'll see the fast food purchase and the balance of $-10.

When you write a check to pay this credit card bill, you enter this in YNAB as an account transfer of $10 from CHECKING to CREDITCARD. This transfer does not affect any of your category balances; they remain the same. But now your CHECKING account balance is down to $1,890, and CREDITCARD is back to $0.

This works just as well whether you have one checking account and one credit card, or 2 checking accounts, 2 savings accounts, and 3 credit cards. When you want to spend some money, you look at your category balance. If there is money in there, then the money is available to spend somewhere in one of your accounts. Then you pick an account you want to pay with, and, looking at the account balance, if there isn't enough money in that account to pay it, you just need to move some money from another account into that one, or pick a different account. When you pay for an expense with a credit card, the money gets deducted from the category balances immediately, and is no longer available to spend on something else.

  • Just to make sure I understand -- in YNAB software, can I add a $10 expense to category "fast food", to "credit card 1" on "Apr 28", and it will automatically calculate the balance on the card given the opening/closing dates? Or is it simply that when I'm paying the Apr 15-May 14 CC bill on May 30, I already have more than necessary saved up (because I've transferred all expenses up to May 30th already)? – Eugene Apr 28 '15 at 20:39
  • @Eugene I've added a more concrete example to try to explain in detail how it works. Let me know if it is still unclear. – Ben Miller Apr 28 '15 at 21:33
  • @Eugene If you end up trying YNAB and you find yourself with questions on using the software, feel free to come back to this site and ask. – Ben Miller Apr 29 '15 at 17:40
0

My budget spreadsheet is my check register spreadsheet, with one tab per month, and has a separate tab for my single CC.

Importantly, the check register tab "looks forward" to the end of the month, with line entries for pay days and known dates when expenses are due.

I put the "multiple times per month" expense categories "Groceries", "Restaurant" and "Miscellaneous" and the end of month. Each time I buy some groceries, the value in the EOM Grocery cell is reduced by the amount of groceries I buy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.