I am very excited to announce the release of my new Money Management template. It has long been my goal to create a simple, macro-free, money management spreadsheet that provides the same functionality as basic budgeting and money management software. The bare minimum you need from such a tool is the ability to:
- Define budget categories
- Record payments and deposits for your various bank accounts and assign the transactions to one or more budget categories
- Compare your current spending to your budget via a report or dashboard of some kind
Vertex42 Money Manager
Over the years, I’ve created many different types of budget spreadsheets, but none of them included a way to record transactions like you can with Quicken or Microsoft Money. I use a spreadsheet similar to my checkbook register to record my business transactions, but it is too complicated to try to share. Such is the case with most spreadsheets that people create for themselves. If a spreadsheet is so difficult to use and customize that you might as well create your own from scratch, it doesn’t make a good template.
I’ve always used Quicken for my personal finances (and will probably continue to use it), so the real motivation for creating the Vertex42® Money Management Template came from the prompting of multiple customers over the past few months. In my first attempts, I tried using separate worksheets for different accounts, like Quicken, but that didn’t make the spreadsheet simple enough to customize. Recording transactions for multiple accounts (checking, credit cards, savings, etc.) within a single worksheet turned out to be the solution.
I don’t want to be too redundant, so to learn more about the new Vertex42® Money Manager, visit the download page.
Try it out and come back here to leave your comments and suggestions. I will announce future updates and changes in the comments.
Oh, and did I mention this spreadsheet is free?
Enjoy,
Jon
—–
I know this is going to come up, so I’m going to mention it now: The ability to automatically important transactions from your financial institution(s) would be a bonus time saver, but that is not a critical feature and would add a significant amount of complication to the spreadsheet. So please don’t ask for this feature – it’s not going to happen. I purposely avoid using macros and VBA within my templates because I’ve found that causes the number of questions and problems to sky-rocket.
{ 94 comments… read them below or add one }
Next Comments →
I really like the functionality of this spreadsheet. I’m trying to figure out if there is an easy way to run a report for the entire year. Any suggestions?
That depends on what you want the report to show. Make a list of what you’d like to see in the report, and I’ll see what I can do when I get some time. – J
I would love to see a cumulative version of the monthly report, by category. I thought I could re-configure the cell where you input the number for the month on the monthly report template, but I played around with it for about an hour and couldn’t figure it out. Basically, I would like to be able to view my budget and actual spending, for each category, for the entire year. So, if it were a multi-year spreadsheet, I could possibly type in 2010 to see all spending vs. budget for that year. It would be almost a duplicate of the monthly report, but it would give information over an entire year. Thanks!
I can’t get my actuals to show up in the Report worksheet. What am I doing wrong?
To be honest, I don’t understand how the formula provided (example below) is gathering the information from the Transactions worksheet.
Example:
=INDEX(Categories!C:C,MATCH(A23,categories,0))
Please help!
The Report worksheet shows the “Actual” amounts for the dates between the Begin and End dates. Make sure you have transactions with dates between the Begin and End dates shown in the Report worksheet. – J
J.D. Roth of getrichslowly.org provided a nice review of this template. Read his review here.
It was bound to happen … I found a bug. The problem was related to handling transactions such as credit card refunds (where the amount is listed under Deposit instead of Payment).
- Values entered into the Deposit column of the Transactions worksheet would not be accounted for in the Expense categories of the budget report.
- Values entered into the Payment column of the Transactions worksheet would not be accounted for in the Income categories of the budget report.
I just uploaded a fixed version (version 1.0.2), but if you downloaded the Money Manager sometime BEFORE this comment was posted, then you should either download a new copy or follow these steps to fix the bug:
Bug fix for version 1.0.1:
(To check the version #, go to File > Properties > Custom tab, or Office Button > Prepare > Properties > Custom tab)
1. Go to the Transactions worksheet and Unhide columns J-N
2. In the “Payment this Month” column change the formula in cell K6 to =IF(J6,H6-I6,0) and copy it down.
3. In the “Deposit this Month” column, change the formula in cell L6 to =IF(J6,I6-H6,0) and copy it down.
4. Hide columns J-N again.
I will be releasing version 1.1 within the next couple weeks, which will make this obsolete, but I wanted to make this change immediately for those who have already downloaded and started using the spreadsheet.
You can now download version 1.1 which includes a “Year-To-Date” option (just a simple checkbox) in the Report worksheet and various other minor improvements and fixes.
@Ashley – thanks for your suggestion
Yes! Thanks Jon – much appreciated. This solves so many budgeting issues I’ve had over the last year.
Hi – this is a brilliant spreadsheet, and has saved me procrastinating over building my own! One feature that would be very nice is the ability to do Envelope Budgets/Accounts.
I could force an envelope by creating some Envelope Accounts, and ‘transferring’ money into these, but this would leave incorrect Account Balance.
I shall probably tinker and create my own attempt at this, but was wondering if this may be on your to do list!?
Thanks
Tom.
@Tom – At first I thought you meant real envelopes, which could be treated like you say – as separate accounts. But it sounds like you are wanting to track the balance of specific budget categories by essentially creating sub-accounts (i.e. “envelopes”) for your checking or savings accounts. That is actually a great way to track savings goals and money you’re saving for irregular expenses.
It turns out there is a simple way to handle this using the money management spreadsheet. First, recognize that the Transaction History worksheet is a way to track the balance of multiple accounts. You could think of the overall BALANCE as the master account, and the Account Balance column being the balance of the sub-accounts (i.e. your “envelopes”). So, the solution would be to make a copy of the Transaction worksheet, and then make a few changes:
1. Copy the Transactions worksheet (Right-click on the Transactions tab, select “Move or Copy…”, then check the “Create a copy” box) and rename the new one “Envelopes” or “Savings”. You can do whatever you want with this new worksheet without affecting the rest of the money management spreadsheet.
2. Select the cells in the Account column and change the Data Validation from =accounts to =categories (Data > Data Validation > Source:). This will allow you to select your budget categories from a drop-down list. 3. You may want to change the “Account” label to “Envelope”. You could of course remove the data validation altogether.
4. With those cells still selected, remove the conditional formatting (Format > Conditional Formatting > Delete).
5. Remove other columns you might not need, such as Num, R, the hidden columns, and Cleared Balance.
If you create a separate sheet for your Savings account, then the overall balance in your new sheet could be compared against the total Savings balance in the Transactions sheet. The downside to this approach is that you end up recording transactions in a couple of places.
I’m not sure if this approach would be more convenient than using the separate Savings Goal Tracker and Expense Tracker worksheets or not, but it’s certainly an interesting alternative.
Last question – I promise! Now that you made the changes for handling transactions such as credit card refunds (where the amount is listed under Deposit instead of Payment)” – you can’t record a transfer correctly. If you record a transfer from checking to savings, and record it going out of the checking and coming into the savings, for something such as car replacement, it cancels itself out and doesn’t show up on the monthly report. If you just record it once, for instance, leaving the checking account for with the category of “car replacement”, it will show up on the monthly report but won’t increase the balance of the savings account in the transactions tab. Any suggestions? This may not be an easy fix. These spreadsheets truly are brilliant and make my life so much easier!
@Ashley – I could be wrong, but I’m pretty sure the examples currently included in the download show how to correctly record a transfer from checking to savings. It is also discussed in the Help file. I’ve checked this over, and it looks correct. Perhaps it’s not readily apparent that the trick is to list “[Transfer]” as the category on the entry you add for the Deposit into Savings. If you list “Car Replacement” as the category for BOTH the Deposit into Savings AND the Payment out of Checking you are right – it would cancel itself out in the budget report. So, remember to list “[Transfer]” as the category for the Savings entry.
Oh gosh – I should have looked at the help before spending hours trying to figure out what I was doing wrong. Sorry! I understand now.
What is the best way to enter cash? If I enter the withdrawel then it balances, but I lose the category tracking. If I enter each cash payment I likely won’t balance.
@Christopher: I would create a separate “Cash” account if you want to keep track of cash payments. When you make an ATM withdrawal from a checking account, you would record the withdrawal as a “Transfer” from Checking to Cash. There are examples in the Help info about how to record transfers from one account to another.
I think you have a great tool here, and want to thank you for sharing all the work you put into this. The only suggestion I have is that it would be great if for debts such as vehicles credit cards etc., you could enter the balance so you could keep the goal of payin them off in sight.
Thanks again.
hi i would like to know how to change the account categories (ie bobs checking account, change it to cic checking acct) – i have been using my own names they are all show up red but i can’t find where on the categories page to change them.
thanks
cic
Hello cic … account categories can be added/edited in the Help worksheet (see Step 2).
Great tool! So much simpler than anything I’ve ever attempted to create for myself. One issue is coming up for me: When I enter a transaction under the category “Daily Living:Clothing”, it is populating on the Report tab under both “Daily Living:Clothing” and “Children:Clothing”, and so the expense is counted twice. The same happens vice versa: If I categorize a transaction as “Children:Clothing” on the transaction worksheet, it is counted in both Clothing categories on the report. Please advise.
I also appreciate the advice about handling cash. Would have been my next question!
Hi Mandy, you have a “Clothing” category under both Daily Living and Children, but each category name needs to be unique. You could label the second one “Kid Clothing” to make it different from the “Clothing” category under Daily Living.
Hi
It is a great tool. One issue is if I dont use the Year-To-Date option then the graph is not showing the budget.
@Satheesh … I’d need to see your file to figure out why that might be happening and would also need to know what version of Excel you are using. [Contact Me and send as attachment]
i am thinking of using your tool for budgeting…but before i do all the working entering info, i was wondering if there is a way to show individual balances for checking, savings and cc cards. i see on the report page that there is an “acutal” column for cc’s but not a total for checking and savings accounts…
@Mary … Yes, the Transactions worksheet let’s you see the current and cleared balances for each separate account. The Report worksheet shows what you have earned and spent … but not what the current balances are.
Next Comments →