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.
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.
Comments
Jon,
I have verified that the formulas did copy down and my question is (Is the information suppose to populate in the Budget tab sheet?) I see that it populates in the Report but not the budget tab sheet? Just wondering if it would.
@Jay … I see. No, the information in the Transactions worksheet does not show up in the Budget tab, as the Budget tab is where you manually enter the budget values for each month. The Report worksheet pulls information from both the Budget and the Transactions worksheets, so that you can compare your monthly budget and expense amounts.
I really like this spreadsheet! Thank you very much for your work Sir!
I was looking for a way to create a “Home” screen to show each accounts last balance. What I have come up with is copying blank rows on the transaction sheet down to two-thousand, I’m only using about 500 rows from 3 months of record, and selected each account to show its current balance. Then I created a new sheet labeled home and copied the account list from the Help sheet to it and created a simple =Transactions.M2000, =Transactions.M2001, etc to show their current balances next to their labels.
I was thinking of maybe having a way to have a drop down menu to select the accounts, like on the transaction sheet and it uses the last entry for the account. That way if I ever reach two thousand transactions next year I won’t have to worry about changing the formulas, etc. I would appreciate your help but if what I did makes sense and is the simplest then just let me know. Thanks.
@Craig … See the following page for information about creating drop-down lists: https://www.vertex42.com/ExcelTips/drop-down-list.html
Hi Jon,
My life has been made so much easier since stumbling across your website a few months ago.
With regards to the size of the drop down box lists on the transactions page – can I make it so I can see more of the categories in the box or am I limited to 8 category lines. I know that if I could see more at once, it would save me the time having to scroll through all the categories every time. Can I do this with data validation or do I need to change it to a combo box. How can I do this without stuffing up all your good work. Thanks for all your help and your templates.
@Brooke: Sorry for the delayed reply. Unfortunately, the # of lines in a data validation drop-down box in Excel is a limitation of Excel and not something that can be changed.
Hi Jon
I am in the UK and have been a long time user of Quicken but changed to a new laptop recently and as I am into retirement, my finances have simplified somewhat so I am delighted to come upon your money management templates. I just started them Jan this year so early days yet but I am very impressed. I use Windows 7 on PC and Excel 2010.
A couple of simple things:
Is it important to enter transactions by dates sequentially or can they be entered in any order as long as they reflect the correct date for the transaction?
If I want them sequentially, is there an easier way other than inserting new rows and copying and pasting the transaction rows into their correct sequential order?
I have red cells appearing randomly in my numbers column. Doesn’t appear to affect operation of the spreadsheet but any significance? and how do I get rid of them?
I also have one pink cell appearing in my R column – significance? and how to get rid of it?
Thanks for any help in advance. And thanks for providing a wonderful tool.
@Larry:
1. If you enter transactions out of order (but use the correct date), the meaning of the Balance columns will be difficult to interpret, but the transactions will still be listed in the correct month in the Report worksheet.
2. You can use Autofilter in Excel to sort the transactions by date AFTER entering them into the table. Select the entire table (including the header row) and go to Data > Filter. Using Autofilter is actually quite useful because you use it to show just the transactions for a single account if you are working on reconciling.
3. The Balance column uses conditional formatting to change the color to red if the value is less than 0. Don’t let your balance go below 0. :-)
4. See Step 4 in the help worksheet – a pink-highlighted cell would mean that the Category you’ve entered is not found in the Categories worksheet.
Jon,
I was looking to ditch Quicken – too complicated and time-consuming, especially if you use the automatic payments / loans. I was looking for a simpler way when I came across your site. I’m happy to say that I’ve been using the Money Management template for 3 months now and couldn’t be happier – it was exactly what I was looking for and then some. I had thought about putting together my own spreadsheet, but I would have never come with one so comprehensive.
I set-up a “personal allowance” category that I use for discretionary purposes and created a “sub-budget” worksheet, linked to the main budget, that allows me to track the details without cluttering up the main reporting and categories sheets.
I’m actually having fun with this. Great stuff – thanks!!
I just started to use the worksheets and they seem to be a great substitute for mint/quicken. I would love to integrate my student loans. I would just include loans as accounts and transfer money to them, but you would not be able to capture the interest component of the payment. I guess you could transfer the money from checking to the loan and separately enter an interest as a payment within a loan account, if you knew the exact amount. Any ideas on a simpler way? How would that show up in the report?
You are correct. You could create an account for your loan, but the main reason to add an account is so that you can record expenses paid from that account via the Transactions worksheet (like you would with a credit card). For a student loan, if you are simply interested in tracking the reduction in principal, it would probably be simpler to use the Amortization Schedule template along with the money manager workbook. The amortization schedule would also help you determine the allocation to principal and interest expense (if you can’t get that from a monthly loan statement).
To track interest and principal as separate expenses in the budget report, create another expense category titled “Student Loan Interest” or just “Interest Expense” to go under the “Student Loan” category already included in Obligations and record each loan payment as a split transaction.
This is sooo sweet!! It looks very, very similar to one I created and works very similar as well! When I first saw this I got happy and thought, “I’m on the right track since I seem to be thinking similar to Jon!” LOL. However, yours is cleaner and more user friendly than mine, so I’m ditching mine and using yours :). I love it! I’d be happy to share mine with you for you to take a look at…
Quick question… on mine I had a formula to use “cash” as an ‘account’ that way it didn’t change the running balance. I created a cash account on yours as well and just added whatever I paid cash for to both the deposit and payment columns so it nets to zero. Is this a good way to do it in your opinion or did you build something else in it?
Thanks for your time and your awesome contributions!!
If you add $25 to both the Payment and Deposit columns, then the amounts won’t show up in the report because they net to zero. I’m not sure why you want to have it net to zero. If you’re tracking your cash, then you probably should be listing ATM withdrawals as transfers to Cash and treating the Cash account like other accounts. Cash payments can be one of the leaky faucets of budgeting.
Perfect! I have tried quite a lot personal finance softwares, but nothing is as simple and as easy to use as this. The ideas in the sheet are genius, everything neat, simple and straightforward. I have modified it to my need and translated all into Vietnamese for a more friendly interface to me. together with your debt snow ball calculator and saving calculator templates, now I can totally control my finance.
Thank you so much!
This looks like a great tool. And I’m a complete novice. So I need to be able to track things weekly rather than monthly because of how my income flucuates and comes from a variety of sources. So how could I (or can you) make this so that I can do weekly input and then have it convert to monthly and annually? Or can you direct me to a template that already works on a weekly basis? Thanks.
The transactions can be entered whenever you want. If you don’t need to create a weekly budget, but just want to input transactions weekly, that is already how the spreadsheet works. If you wanted to create and compare to a weekly budget, that is different. I have a weekly budget version that I could share, but if you wanted BOTH a weekly budget report AND a monthly budget report, that is not something I’ve created yet (though it would not be too difficult to set up).
I am having a problem (not with your spreadsheet) on how to handle a budget. I have a separate escrow account under savings that gets a monthly contribution. When I pay the actual tax from this account i would like to see that on the reports but it looks like I am paying twice in the budget sheet. An suggestions?
The Report worksheet in the money management template includes transactions where the Category matches a category in the report. If you are just transferring money from one account to the other and don’t want that transfer to show up in the budget report, either leave the Category blank, or enter “[Transfer]”. There are examples in the Help worksheet to look at.
I was frustrated with the template until I turned the macro security to low.
It has worked well for me since then. Maybe you discuss that somewhere, but I didn’t see it.
Hope the template works well for me now. It will work better for me to monitor my finances.
K Swarts
@Keith … it’s a bit strange that the macro security had anything to do with it, because the file doesn’t use or contain and macros. Still, glad it’s working better for you.
i am having one problem regarding personal budget. when i enter my salary income at the income head then it will reflect on the expenses of month before my name and if i have not entered it then my income shows zero. guide me what i am doing wrong.
@narendra … I’d need you to send me a screenshot of the problem, with more details. I’m afraid I can’t quite follow what you are saying. See the about page (link at the bottom of the web page) for my contact email.
I’ve been using this spreadsheet for six months now, it’s BRILLIANT, but I have this question – what is the best way to handle the changing year? Should I go with balances verified online on 12/31, or the latest statement for each account, or … ? I’m probably making this harder than it needs to be but I’ve been wondering about this for the past two weeks. Thanks for any suggestions, and THANK YOU for such a wonderful spreadsheet!!
@Mary … balancing your accounts (i.e. reconciling) is done via the Transactions worksheet and is independent of the Report worksheet. So, you should still be able to use your normal statement balances for reconciling regardless of the change in year. I’d recommend saving a backup copy of the file for 2012 and then update your yearly budget for 2013.
One thing I’m considering for the new year is to break down the opening balance cell on the budget sheet by creating opening balance cells for every budget category, and then totaling them in the original cell.
Jon,
I’ve been using money manager for 3 months now and I’m loving it!. Thanks for making it available. As the year comes to end, I’m going through the template trying to figure out how to start the budgeting for the new year. Do I erase the current year’s budget? or create a new tab? I’d like to keep the past information for my records. Thanks a lot for your help!
Martha
Hi Jon
Like Mary, I too, have enjoyed using your spreadsheet for the past 12 months but would like to clarify something on changing over to a new year.
I can see the point about making a back up copy and updating your budget but do you just keep adding the next year’s transactions to the transactions sheet? It starts to get pretty long when you are trying to reorder dates and see accounts separately. Or do you just repeat the process when you first started by creating another worksheet for the new year and using closing balances from statements of accounts at end of previous year? What would you recommend?
Thanks
Larry
How to proceed with creating a new budget for the near year has been a common question. First, definitely save a backup copy for 2012 (naming the backup file money_manager_2012.xlsx for example). Then, in your current copy of the file, go through and update the Budget worksheet for 2013. Whether to remove older Transactions or not is up to you. For example, you could remove older reconciled transactions and then include a “Carry-over Balance” entry for each account to reflect the latest reconciled statement balance (similar to what you did when starting from scratch).
Greetings! I came up with a solution that is (so far, as far as I can tell…) working for me. Most of my accounts have statement dates of 12/31/12, so I’ve been able to reconcile them already. I have a new copy of the spreadsheet, for 2013, with the opening balance of each of my accounts in that transaction worksheet linked to the final balance in the 2012 transactions worksheet. Once everything has cleared (two checks that haven’t been deposited in TWO MONTHS!! Isn’t that annoying?) then I can go back to the opening balances in the 2013 spreadsheet and paste the final values in over the formulas. This is allowing me to keep track of cleared balances in two accounts that don’t have statements that match the calendar month. Once again, THANK YOU for this wonderful tool !!
I’m having issues working between OpenOffice and Excel.
Categories match fine using OpenOffice, but highlight as not matched when opened using Excel. Assuming this is a macro issue.
I would like to be able to open in both programs.
Any thoughts?
@Phil … There are no macros in the file. Highlighting works via conditional formatting. You could remove the conditional formatting conditions if you are confident that your categories do in fact match. As the template was designed using Excel, I’m guessing the problem has to do with editing in OpenOffice. If you could send me your file I could see if I could detect the problem (you’d have to contact me using the email on the contact page).
I keep getting a “File is corrupt and cannot opened” error after I down load the file? Any suggestions? I am running Excel 2010. Thanks for any suggestions.
@Dan, For whatever reason, that sometimes happens when downloading spreadsheets. I can’t do much about that, but I’ve re-uploaded the file in case there was some problem with the file on the server.
Hi Jon:
I love your spreadsheet and have spent the last couple of months entering categories and transactions. I just completed the Budget worksheet and am having problems getting the values to carry over to the Report. If I do one month at a time it works fine. But if I try to use the YTD feature, I get value errors in all budget categories. The YTD transactions are ok either way. I can’t figure out what I did wrong. Can you help? Thanks!
@Michelle, I’d probably need to look at your copy of the spreadsheet to diagnose the problem. You can email me.
I have discovered a “#REF!” error on the report tab (cell C74, category “Groceries”) of my 2013 money manager spreadsheet. The error was not on the 2012 spreadsheet that I used. When I try to use the error-checking tools, I am referred to “Transactions!F:F” and “Transactions!J:J” neither of which changed when I changed from 2012 to 2013. The formula below the cell with the error is exactly identical, save for the row number being changed by one, and it shows no error. I am completely baffled. I’ve searched for solutions online and it’s been suggested that deleting lines in the transactions sheet could cause the problem, but the formula in Report!C74 refers to entire columns, not any single cell, so I don’t understand how that could be an issue. (I do insert lots of lines for new transactions, but I am very careful about copying and pasting, so I don’t *think* that’s the issue either).
Very grateful for any suggestions as to a fix.
I think this money manager will do all that I could ever want…if I knew where to get started. Is there a video for beginners? I understand better if I see it.
@Lare … yes, there is a video demo on the download page. Also, a detailed Help worksheet within the file itself.
Thanks for creating/sharing your spreadsheets!
I have read through the support page and haven’t found specifics addressing my questions. Sorry if I missed it.
I’d like to post transactions for 6-10 checking accounts – view each account/those transactions by themselves and print reports – cumulative (or selected checking accounts) totals, also by category assigned (total) and be able to sub-total several categories broken down into sub-categories. I don’t download from bank accounts or print checks, I just manually post all income & expenses by account, assign to category & sub-category if I need it in a report, then customize the reports to print. I do not need the budgeting features so can I just ignore those? Or will it screw up my category reports elsewhere. Thanks. Joy
@Joy, The Money Management Template includes all transactions for all accounts in the Transactions worksheet. There is a Help worksheet that explains how the template works. Using the Filter feature in Excel, you can choose to display only transactions for a single account. You could also create a Pivot Table from the Transactions worksheet if you are comfortable doing that type of thing with Excel. That would allow you to subtotal just a specific set of sub-categories. I can’t say whether the template will do everything you want it to or not, but you are welcome to try.
Sorry. Forgot to say I’m using excel 2007, PC, windows 8, working with the money management template. Also, sorry, but I can’t find your email address/link anywhere that works – using IE 10, found several websites that aren’t compatible with IE 10. Thanks.