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
I really like your spreadsheets. Great way to learn about sumif and some other excel functions. Like you, albeit not for sharing, I’ve built my own excel workbook for personal finances. I actually got frustrated with Intuit’s lack of support for Quicken for the Mac and found some of the interface with Quicken 2005 cumbersome.
The name of the game in personal finance (as in other tools) is the reports. What I find lacking in your sheets (and I’ll actually work on creating them for you) is something with greater flexibility than monthly or YTD. Decision making for personal finances also includes trends. In our case, we basically strike a balance every six months not necessarily aligned to the a year-end calendar. In fact, we summarize on 4/30 and 10/31 for no particular reason. I can’t include a spreadsheet so I can’t show you the report, however, it shows balances by account (which helps us understand changes in sub- and major account categories) and transactions (which helps us understand spending patterns). Cash flow reports include 2 six-month periods while balance reports include 4 individual years (with deltas).
As I mentioned earlier, I’ll work on creating the reports from the transaction spreadsheet in your workbook. When done you’re more than welcome to it.
For what it’s worth, its all about the reports and monthly and YTD reports are good basic reports but not very responsive the actual questions we ask about our personal finances.
Again, great workbook! Got a chance to learn about sumif (I tend to use sumproduct but I think your way is more efficient).
Liv
Hi Jon,
Nice worksheet, thank you for sharing!
While your solution is correct, I think you misunderstood “Mandy on September 25th’s” statement.
Vertex42 Money Manager V1.1.1 by default has 2 “clothing” categories. “Daily Living:Clothing” and “Children:Clothing”. The fix is simple (and a good way to begin learning to customize your categories) but you still might want to put out V1.1.2 to fix this TINY bug.
Thanks again, this is a great way to start figuring out a budget especially for those already familiar with Excel.
-Jon
Thanks for the great tool. I am using the Open Office version and can’t get the auto filters to work on the transactions tab so that I can view one account at a time. (I get the auto filters on there ok…but the only choices in the drop down menu on each column are ALL, TOP 10, or STANDARD FILTER…..and it doesn’t list the different accounts.)
Hi Jon,
I have just downloaded the money-management file and each time I open it, it is crashing excel. I can’t make changes or even input data without having a very long response time. I am using Microsoft Excel for Mac 2008, Version 12.
Is there some setting that needs to be changed? I have saved a copy of the file to my hard drive and changed the file name, I don’t experience this issue with other .xls files.
It looks like a great and helpful tool, I am eager to start using it!
Fiona
@Fiona: Sorry … there’s nothing in the file that should be causing Excel to crash, and it works fine on my Mac in Excel 2008. My only guess is that it has something to do with the fact that the download is a .zip file and must be extracted first.
Jon,
Thank you very much for the very helpful spreadsheet – sincerely appreciated.
Quick question – I was fooling around trying to print the monthly budget report and got the settings out of wack. Now when I try to restore to your original setting, I get an error message stating that the “margins don’t fit the page size”.
Can you help fix this?
Thanks
Jim
@Jim … I’d check out the Excel help (F1) to learn about updating the print settings, setting a print area, changing margins, and scaling. Most likely, you’ve set the margins to 0 or something too small for your printer to handle.
I’m intrigued by your spreadsheet and would very much like to use it. However, I do budgets each week. Can the spreadsheet be tailored to accomodate this, and if so how might I do that without potentially corrupting the underlying formulas?
Creating a Weekly Budget
@Chuck … The weekly version has not been created yet, but it actually isn’t TOO hard to make it work yourself, because the values used to populate the Actual column in the Report worksheet are based on the Begin date and End date. Here is how to make the change:
(a) In the Report worksheet, enter the Begin and End dates manually.
(b) Change the formula for the End Date to =D4+7 for weekly or =D4+14 for biweekly
(c) In the Budget column, remove the formulas that reference the Budget worksheet, and enter the values manually based on your weekly budget.
You might want to reformat the Begin and End date fields as well as the Budget column so that the cells are easily identifiable as inputs instead of formulas.
Note: These changes make the Year, Month, and Year-to-Date options in the Report worksheet obsolete. The yearly Budget worksheet will also no longer be linked to anything. But, you could still use it for reference if you wanted to.
If I get a lot of feedback, I would consider including a weekly version of the money management template on my site. I suppose it would be possible to include two different Report worksheets in the template (one for monthly and the other for weekly), but I have not received many requests for a weekly version, so I’m not sure that’s worth the effort.
Jon,
I want to track spending on a bathroom remodel in our budget. We have the money in our savings account. I thought it would be easy to set up a separate savings account and deduct the money spent from that (savings) account, however, we make all of our purchases on our cc and then pay the cc from our checking account. I want to start with the total and make deductions along the way. This money manager is obviously not set up like this…
Do you have a suggestion on how we would set this up?
Mary
@Mary,
The Help worksheet explains how to list transfers between accounts. The transactions you make with the CC would be where you allocate the expenses to a “bathroom remodel” budget category. The transfers from Checking to CC, and from Savings to Checking, would just be “transfers”. Hope that helps.
Hello Jon,
I love your excel template and it is working great!
But is there some way to maybe implement an extra column on the side of the help page that shows the balances in those accounts? I ask this because I have several accounts that I inserted which adds up to a lot of transactions and it becomes slightly difficult to find the account I want to check balances for.
side note on possible typo?:
On the help tab in step 4, the example said $250 credit card payment but the entries are for $150.
Thanks!
@YC … If you convert the Transactions table into a List, you can then display just the transactions for a particular account. Select the table (including the columns labels and all the rows) and then Go to Data > Filter > AutoFilter. I think you may find that to be the preferable solution. However, you could also do as you are suggesting and display the balance next to the list of accounts in the Help worksheet using a formula like this:
I don’t use standard month formatting, is there a formula I can use to change the dates on that report, or does it have to have the formula even?
I also was not to clear on the transactions page if I start again at the end of each month or its a all year list?
Thank you for this wonderful spreadsheet!!
@Steph: You can see if the following will help: http://vertex42blog.com/money/money-management-template.html/comment-page-2#comment-129
You should not clear out the transactions each month … just keep a running list. You can hide the rows for older entries if you want to.
Just downloaded and started changing sub categories. Wouldn’t it be easier to just reference the cells from the category page to the budget & report page? That is what I did that way they are all going to match. Ex. =+Categories!A13 would go in column “a” row 23 in the Budget page and column “a” row 33 in the Report page.
@Jeff: Yes, it probably would make it easier to set up initially, so I’ll consider making that change. But, if you deleted the row in the categories worksheet, you’d end up with #REF errors in the Budget and Report page without knowing what that category was supposed to be. Also, if you decided you wanted to change “Groceries” to “Grocery” after a few months of adding Transactions, the expenses listed in the Transactions table categorized as “Groceries” would suddenly disappear from the Report worksheet. You would need to make sure that if you made a change to the Categories worksheet, you went through and corrected the Category for all of the past Transactions.
I’m new to this and am a little confused. I’m not sure how to treat my credit cards. Should they be listed in “Obligations” only if I’m not using them anymore and just want to clear the debt? Or should they be in “Obligations” anyway, even if I’m continuing to use them, and pay them down? Also, should I list each type of credit card (eg. VISA, AMEX) I have in the “Accounts” list, or should it be just a generic “credit card”? Thanks!
@Narelle: That’s a good question. If you are just trying to clear old debt, then I’d put that amount in Obligations. Now, let’s assume that you’ve gotten to the point where you are able to pay off the credit cards in full every month. In that case, I’d treat the cards like you would a debit card … where every transaction is tracked and recorded and allocated to specific budget categories just like you would with a checking account. Then, each month, you make a transfer from your checking account to your credit card account to pay the balance. In the meantime, you’ll need to do both: (a) track new charges and pay off the new charges in full and (b) pay down the existing balance and interest. For simplicity, I would stop using credit cards that had a previous balance, and use a debit card instead.
The other approach would be to categorize each CC transaction from this point on (like you would with a debit card), and then when you pay the credit card bill, you would pay the amount of all NEW charges PLUS the interest+principal on the previous balance. The NEW charges will have already been allocated to budget categories, so that portion of the credit card bill would be considered a “transfer”, while the interest+principal portion of your bill would be allocated to the “Obligations” category. This would require making the credit card bill payment a split transaction (part “Transfer” and part “Obligation”). Yikes … that does sound complicated. I’d stick with the first option (use a debt card until you have the balance(s) paid off).
I’m sorry, but I’m a little confused with transfering from my savings account and the proper reporting of it. Here is an example of what I’m trying to do. I have a subcategory of Savings called “Clothing Fund”. I currently have $50 in it and want to transfer it to my Checking account. What I have done is registered my transaction from the Clothing Fund account with [To Checking] as payee and category as [Transfer] with a 50.00 payment. Then my next transaction is Checking account with [From Savings] payee and category as [Transfer] with a 50.00 deposit. After all this, I registers 50 to my checking, but my Clothing Fund is still at 50.00. What am I doing wrong?
@Jason … In the example included in the Help worksheet, listing “College Fund” as the Category means that you are treating the transfer as an expense for budgeting purposes. According to your budget, that money is now gone. Even though it is sitting in your savings account, according to your budget, you’ve already spent the money on college. You might think of it like writing a check and post-dating it for 10 years in the future.
It sounds like you not only have a “Clothing Fund” subcategory under savings but also a savings ACCOUNT named “Clothing Fund”. I don’t understand why your savings account is not reduced by $50 when you transfer money out of it. It sounds like you used the right approach. You can email me for more assistance.
Hi Jon
I have been using the spreadsheet since January to track my spending habits and in hopes to improve my budgeting. I’m not sure if I deleted some info, but the report tab is not pulling in the numbers from the Transactions tab. It seems to have only the transactions that were recorded in January, nothing from February or March. I’ve unchecked the year to date feature and also checked it to see if this changes the data but nothing. I’ve also specified it to look just at March, Year 2011, Month 3, Begin 3/1/11 End 3/31/11.
Any suggestions?
Fiona
@Fiona … This is usually a result of certain formulas in the Transactions table not being copied. Unhide the hidden columns J-L and copy the formulas down as needed. Make sure that when you add new rows into the Transactions table, that you copy an existing row and insert that copied row, instead of just inserting a blank row. This will help ensure that the necessary formulas get copied.
@Di … Yes, you can use any number of accounts. I believe these questions are answered in the Help worksheet. First, make sure that the names for your different accounts are listed in the **Accounts** list contained in the Help worksheet (cells C54:C60). Then, when you add a transaction into the Transactions worksheet, you can select the appropriate account in column A. The Help worksheet contains examples of different types of transactions and how these would be entered into the Transactions worksheet.
Okay, this spread sheet is awesome! I am have one huge problem, my wife now wants to use this via Google Docs so we can share and update it as needed. The problem comes in (I believe) in the upload and conversion to Google. Nothing relates to the other pages, the drop downs are lost but that is easy to retrieve. Does anyone have a fix for this? If i bring it into OpenOffice will it covert correctly to Google Docs? Anyone else run across this?
In a short this would be a GREAT phone app, but it will need to be shareable within a family or group of users.
Thanks,
Shawn
@SDaniels … You are correct … it won’t work in Google Docs and going through OpenOffice won’t help. Perhaps when I get more time, I’ll see if I can make a Google Docs version. Mint.com has a pretty cool iPhone app.
Jon i love this spreadsheet. On the expense summary bar graph on the report worksheet is there any way to change which categories are on that graph? For example, my Dining/eating out category is on eof my biggest problem and i want to see that on the bar graph, but its not currently there.
@Robert … The data used to create the graph is located underneath the graph (move the chart to see the data). The data are just references to other cells in the worksheet. So, if you make changes to major categories, or if you wanted to add specific categories to the graph, you could do so by editing the data table behind the graph. After you move the chart, click on the chart to see what range of cells it is using. Hope that helps. – Jon W.
Hi Jon,
Great template! I am using the worksheet to set up a budget for our soccer team. Our year starts in July and runs through June. Is there a way that I could change the template so that the budget sheet will list the first month as July and have the report sheet calculate the budget correctly? If I change the column headers on the budget sheet, the report sheet still links that column to a January date.
Thanks!
@Vicky … yes. The Month entered into cell B5 in the report worksheet represents the column, so if you change the labels in the Budget worksheet, Month “1” will be whatever is in column B in the Budget worksheet. The only formula that needs to be changed is the Begin date in the Report worksheet. You could either manually enter the date, or use a fancy formula like this: =DATEVALUE(OFFSET(Budget!A10,0,B5)&” 1, “&B4), which assembles a date like “Jul 1, 2011” based on “Jul” being the label you enter in the Budget worksheet. Hope that helps.
Thanks so much, Jon. I used the formula =DATEVALUE(OFFSET(Budget!A10,0,B5)&” 1, “&B4), which does correlate to the first month of our budget year, and it works perfectly.
The end date in the cell below, however, still refers to January. Is there another “offset” function that I can put in place of that formula (=DATE($B$4,month+1,0) so that the end date would also display correctly? I’m a novice at functions…
This worksheet is exactly what we need for our group.
Thank you!
@Vicky … To make the End date be the last day of the month, based on the Begin date: =DATE(YEAR(date_begin),MONTH(date_begin+35),1)-1
Jon, I can’t thank you enough!!! It works perfectly.
I cant enter the date where it says date it does this ######. What Can i do?
“#####” means that you need to widen the column to see what is contained in the cell.
Jon, I tried your spreadsheet and it works wonderfully. I have one question, I could not figure out how to add a new account. I was able to add it to the transaction sheet, but could not figure out which other sheets I should also update. When I added it to the transaction list, it was highlighted in red. Can you help. Thank you.
@Nicolle … the place to add the names for the new accounts is actually in the Help worksheet under Step 2 where it talks about defining your list of accounts. Many people don’t realize that the **Accounts** list in the Help worksheet is the actual list that is supposed to be edited (rather than just an example). So, that’s something I’ll probably change in the next revision.
Has anyone tried using the spreadsheet in Quick Office or Documents to Go for the iPad? I’m assuming the functionality of the spreadsheet might get lost, but I wanted to check to see if anyone has tried before I purchase either of the apps.
@Ashley … Haven’t tried it, but I’m guessing it wouldn’t work very well in those apps.
Hi Jon,
Another question about having the budget run from July 1 through June 30. You helped me change the start/end dates in the report so that month 1 = July. But Is there anything I can do if I want a year-to-date report? It will give me a 6-mo report in December for this year, but then a separate report for Jan-June because it is in a separate calendar year. Is there any way that I can look back at the end of next June and see the entire budget for the year in the report to see where we were off?
Thanks,
Vicky
@Vicky … You’ll need to contact me via email and probably need to send me a copy of the spreadsheet … I don’t recall what we did before, and I don’t think I have a copy of the file that I helped you with (if that’s what I did).
Hi John,
Great spreadsheet! Hopefully it keeps us focused on money management. One question: What is the purpose of the hidden row at the top of the transaction page (row 5). You can’t filter data because of it. When you delete it, the formula’s don’t work for column O. I tried highlighting all the data and then filtering, that doesn’t work either. Please help – I am trying to filter down to either a specific account or category transaction list.
@Louise … The hidden row 5 is there because of the formula used in the Balance column. You are correct that using Autofilter can sometimes mess things up, so I’ve changed the formula to =IF(ISERROR(OFFSET(O5,-1,0,1,1)+I5-H5),I5-H5,OFFSET(O5,-1,0,1,1)+I5-H5) in the newest version.
Hello, I have followed the steps in completing the categories, Transactions, and making sure everything matched in regards to the category names, but I seem to be a little confused? Can you tell me if the information is suppose to populate in the budget sheet once I complete the transactions sheet? If so can you tell me how I can get that to work?
@Jay … the most common reason why information from the Transactions worksheet does not included in the Report worksheet is because the formulas in the hidden columns were not copied down (which is normally caused by inserting blank rows into the Transactions worksheet instead of copying existing rows). Copying existing rows and inserting the copied rows will help ensure that all the formulas in the row will be copied as well.
See step 4-5 in the Help worksheet:
Check Formulas
If you ever have reason to suspect that you forgot to copy formulas, you should UNHIDE
columns J-L and copy the formulas down.