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
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.
Update 5/16/2017 - Many people have asked about how to track money that is allocated to future expenses or individual savings goals. One method is to create virtual sub-accounts via the Accounts worksheet. This method allows you to use the Transactions worksheet and record transfers just as you would with a real account, except that these transfers and the sub-accounts don't show up on your actual bank statements. Another approach is to use a separate spreadsheet to track individual goals (such as the Account Register or Savings Goal Tracker). Because this is such a common request, I've added a new Goals worksheet into the money-manager.xlsx file that lets you track individual savings goals.
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.
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.
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!?
@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.
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.
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.
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.
We have started using your money manager to keep our household budget. It is a very useful, but the one thing that I am having trouble with is in the report tab, I would like to have the “monthly budget report” engaged and not the “year to date report”. How can I toggle between the two? Thanks much!!
@Randy … There is a checkbox to the right of the Begin/End dates that can be used to toggle between monthly and year-to-date reports. If that isn’t the problem, please contact me via email.
I’m having some issues with the Transactions page. The categories column dropdowns don’t seem to allow me to use any categories other than the ones provided with the sample template. For example, I can’t list a deposit category as anything other than Wages and Tips. None of the other Income categories appear on the drop down menu. Please help. Thanks!
For problems like this, please contact me via email. If you send me a copy of your modified spreadsheet and let me know what version of Excel you are using and more details, I can usually figure out the problem.
I love this and am wondering if you are planning to make available in Google Docs? I would love to be able to access the info from work and from home.
Unfortunately, this is a case where the Google Docs compatibility falls short. Without going into details, there are too many features lacking in Google Docs to convert this template to a Google Docs spreadsheet.
Is there going to be a Google doc Version for this amazing tool?
@Alex … not yet.
I’m trying to determine the best way to record a payment from savings. If I charge the initial purchase to savings and then transfer the money from my savings account to my checking account I believe savings will get charged twice. If I charge the category “baby equipment” then I can transfer the money from savings to checking, but “baby equipment” will have a huge spike skewing the report. Also if I use money from income account “gift received” is it better to charge “gift received” or the category.
@Christopher: The key is to realize that the Category column in the Transactions worksheet determines what amounts are included in the Report – it doesn’t matter what account it came out of. If you enter “[Transfer]” in the Category column (or leave it blank), that amount won’t show up in the budget report.
Ideally, if you had been saving up for a large purchase over time using the Savings as an Expense technique, you would have created a “Baby Equipment Fund” as a sub-category under Savings. Then, any time you contributed to this fund via a transfer from checking to savings (as in the example given in the Help worksheet), it would show up in your budget report as a payment to “Baby Equipment Fund”.
So now, let’s say that you’ve reached your savings goal and are able to buy the baby equipment. You’ve saved $500 and according to your budget, that amount has already been spent (as payments to your “baby equipment fund”). But of course, the money is still in your Savings account. To record the payment, enter the transaction as a Payment from your Savings account, but leave the Category blank, and enter a note in the Memo column like “payment from Baby Equipment Fund”. That way, your Savings balance will remain accurate, but it won’t show up in your report. I use a separate Savings Goal spreadsheet to track the balances in my savings account.
If you lump in your “gift received” money with the rest of your budget, you could use a spending category such as “Discretionary” to record payments made with gift money (that’s the way I do it). However, if you spend the gift money on something for which you had already been budgeting, such as clothing, record the payment using the “Clothing” category. Then, you could use the money you saved (by not having to pay for the clothes with your wages) to make an extra payment to savings or debt reduction. Sometimes it depends on the request of the gift giver, though. If they told you the gift was for X, you should probably spend it on X.
Just downloaded this deal and same problem as someone earlier…actuals not carrying over to the report from the transaction sheet? I downloaded version 1.1 and am using Excel 2003.
@GH: Downloaded as-is, the template should work fine. The template was designed using Excel 2003, so it isn’t a version issue. If you have made any changes to the spreadsheet, I would need you to email me a copy or at least an example of the errors you are seeing. Please email me for further assistance.
Solution for: Actuals not carrying over to the report from the transaction sheet.
After reviewing GH’s spreadsheet, the problem turned out to be that the formulas in columns J-N of the Transactions worksheet had been deleted (which likely happened by accident while erasing the initial example data set).
A word of warning to everyone … while you can delete and edit the transaction data in columns A-I, make sure not to delete the formulas in columns J-O. And, on a related note, make sure that when you add rows to the Transactions worksheets, that you are also copying the formulas in columns J-O. The easiest way to ensure that happens is to always copy existing row(s) and insert the copied row(s). Don’t just insert blank rows.
Iam using Excel 2007 and iam very excited to hear this money management template. Its a great tool to control the money.
So i will suddest that, Everyone should read this post and get benefittd.Thanks..
I have tried 4 times to download your Vertex42 Money Manager
software with no luck. What am I doing wrong? Thanking you kindly: Frank!
[Edit by Admin: problem resolved by opening the spreadsheet via Excel instead of by double-clicking on the file]
My husband and I finally set up out budget w/ Vertex42. I’ve used Excel a bit, esp for work and have used it for budgeting when I only had a checking account, so it was quite simple. My question is regarding the BUDGET worksheet “TO SAVINGS” sections and how this works. We set up our budget so we can set aside certain monies “TO SAVINGS”. How should you record this in the TRANSACTIONS worksheet?
Say that we have 4 deposits from our income source a month and I deposit them to checking…which is where our automatic deposit is set up to go. I don’t necessarily think that I’ll “transfer” that money from our checking account to our savings account. When I make and entry on the transaction sheet, it deducts that amount from the checking account. How do I make sure that the amounts in my accounts are correct while also applying the monies “TO SAVINGS” on a monthly basis?
[Edit by Admin: There is information in the Help worksheet related specifically to making “transfers to savings”. If further information is required, please respond by specifying how your question is different from what the Help worksheet already covers]
As Randy said, i too started using this money management technique for house-holdings.But i face trouble with it in the report tab, I would like to have the “monthly budget report” engaged and not the “year to date report”. What should i do now? Can you give suggestions??
[Edit by Admin: unrelated link removed … there is a checkbox in the Report worksheet for toggling between the monthly vs. YTD report]