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
Hi mate,
awesome money management work book thanks.
Just wondering if its possible to make it work for the financial year especially as we are almost ready to start a new one.
Also it doesnt really handle savings all that well in the report as it counts savings as an expense and does not calculate your savings in your budget overview final net position.
One row also requires you allocate savings for holiday for example, this amount is debited from your overall position, and then further down where your allocating funds to holiday expenses, this also debits your net position, almost as if your paying twice.
Be awesome to see some updates with fixes for these. Cheers
Craig
Hi Craig, The file is indeed set up to treat payments to specific savings goals as expenses – a common budgeting approach. The balances in the savings accounts would not be tracked by the money manager. If you don’t want to do it that way, that is up to you. You could create accounts for savings as explained in the Help file like you would accounts for separate checking and credit accounts. Then, transfers to savings would be entered as transfers and not counted as expenses until the money was actually spent. I’ll look at it in more detail to see if anything needs to be fixed.
Hi,
The money manager work book is fantastic but I’m in the same position as Craig who asked the question about it being able to be used for a financial year (1 July 2013 to 30 June 2014). Is there something that can be done to make it work this way?
Thanks
Colleen
@Colleen. Yes, though I should update the template to make it easier to work with financial years. For now … 1) Change the “Month:” label to “Budget Column:” and change the labels in the Budget worksheet from “Jan, Feb, …” to “Jul, Aug, …” (2) Change the formula for the End: date in the report worksheet to =EOMONTH(date_begin,0), and (3) remove the Year-To-Date option after making sure it is NOT checked.
Hi there. Thanks for the quick reply.
I’m still having some issues and am needing some more help!
I’m not quite sure what you mean by the first part 1) Change the “Month:” label to “Budget Column:” – What part of the worksheet are you talking about?
I have made the changes to the month labels (Jul – Jun) and made the formula change to the End Date. It seems to be bringing the transaction information across (eg Jul being month 7 and the amounts associated with this from the transaction sheet). However, the budget amounts coming across are still related to Jan-Dec- for eg the same report for month 7 (which should be July) is bringing the budget amount over for Jan. I’m presuming this is because I haven’t figured out how to do the first part of step 1…..
With regards to the last step (3), does this mean that because we work with a financial year (July-June) we cant get year to date figures?
Your help is appreciated!
Colleen
@Colleen, I’ve updated the main download to allow you to enter the start of the financial year (and the year-to-date checkbox will be related to the start of the financial year). If you want to update your existing file instead of starting over with the new download, you can just make cells A4:D5 in your file the same as the new version. Then, update the month labels in the Budget worksheet as well.
Hi John,
Just wanted to say thanks for updating the spread sheet for the financial year July-June. The template is awesome and perfect for our needs!
Thanks again.
Colleen
Hi,
Do you intend to make a Google docs version? Please also make a form to fill the transaction so that we can fill it on the go. For example, access to the online form from my smartphone and add transaction. I think it will be awesome.
@Ly … I will consider this. Thank you for the suggestion.
Greetings! I have been using this spreadsheet for almost two years now, and I LOVE it! I have just discovered a problem that I have no idea how to fix. I am getting a #REF error on the “Report” tab (Row 46, “Gifts”). Cell C46 has the initial error, which cascades to C46 and then row 49, where the totals are. There is a blue arrow pointing from cell A46, where the word “Gifts” appears, to cell C46, and there is also a dashed line from cell A44 (“Charitable Donations”). The dashed line is attached to a little box that looks like a baby spreadsheet. I have looked at the spreadsheet in formula view, and everything appears to be consistent. I have added and removed many lines in this tab, but this is the only line where this error appears. I have no idea what to do to fix this. I’ve looked on Excel help sites, but they all suggest that I fix the cell references. The cell references are already correct, as far as I can tell (I have screen captures to prove it). If anybody has any ideas, or can point me at an online reference, I would be most grateful!
Peace
MaryB
@MaryB … A #REF error typically means that a cell, row, or column was deleted that some other cell was pointing to. The blue arrows you mention might either be a result of a circular reference or the arrows that appear when you use the Formulas > Trace Precedents/Dependents commands. Check the Transactions worksheet, specifically the hidden column J. It’s possible that the original error is actually in columns J:L of the Transactions worksheet. People often forget that when adding new rows to the Transactions worksheet that all formulas need to be copied down (including the formulas in the hidden columns). If that doesn’t work, send me a copy of your file via email.
Hallelujah! That was exactly it (which is weird, as I always copy entire rows when adding to the spreadsheet, not just individual cells, but anyway…). Thank you!!!!
I have Excel 2002. Can I load and use your Credit Card Debt reduction template. Perhaps only some simple problems would occur??
I really like the look and completeness of the 40 debt capacity template. I am a private individual and not a financial planner.
Thank you for letting me know.
If I can use the template on Excel 2002 I will order the template
@Ken, If the free version (for 10 creditors) works for you, then the extended version should as well.
I am enjoying the use of the Money Manager workbook and have helped my son who just graduated from college with this workbook.
I noticed that when I choose to close the Excel workbook with the X close button on the top right corner the grid lines turn on (which have been turned off) and only one page in the entire workbook closes and the workbook doesn’t close. Seems quite odd. If you want the file, which has some editing performed I can email it to you.
I am using Excel 2010.
I see now that there are two places to turn off Gridlines. I was turning them off in the “View” ribbon and didn’t know that they were also turned on in the “Page Layout” ribbon. Problem solved.
Also, the 5 tabs (sheets) kept opening up when opening the file. After closing each one and saving again it opened properly the next time. Problem solved.
I made a few customizations and grammar editing in your help sheet and added a few suggestions on pages that I spent more time with than I cared to when trying to comprehend the use of some features. I preferred to add some by way of the text box method. I am not an accountant by any means and had to think, think a bit too much.
Also, since my son may not have known how to begin allocating budget items I created some percentages that would get him in the ballpark. This way he would see a suggested goal to shoot for. I listed the percentages in column B next to the major categories of the categories sheet and in the % row title cell of the Budget tab (sheet). Then when he makes adjustments he knows to subtract from a category if he has added to another category because it all must equal 100%.
This really is a very well thought out workbook. You have done a marvelous job. As far as I am concerned the Money Manager covers all that is needed for a typical home budget. I won’t need the personal or budget planner sheets anymore.
Hi, I just downloaded the Money Manager, as I see that the yearly budget sheet is very similar to previous templates I have used. I seem to have a problem though. I downloaded the .ods version, for use with OpenOffice, and upon opening a brand new download, I see that on the Transactions page, all the BALANCE fields simply say #VALUE . Apparently there’s an error somewhere with the cell references, but I don’t know enough about how this template is designed to attempt to fix it. Is there anyone else with this problem, or is there a fix in the works?
Brian again. I figured it out, apparently there is supposed to be one empty row in between the title row (row 4) and the first transaction row to make the formulas work, but in your download that hidden row is missing so the formulas show errors. I realized it by looking at the sample section on the help sheet and comparing it, and noticed the difference in row numbers. I added an empty row and made it hidden, and now it works perfectly.
@Brian, Thanks for pointing that out. I’ll fix the download.
First off I want to thank you for this workbook. This is an amazing tool for crazy people like myself who want to track every penny spent! I hope this question wasn’t asked early in the comment and I missed it but is there an easy way to create a budget for 2015 using 2014 actuals? I know I can change the report and go month by month and category by category to pull out each actual and then put back into the budget sheet for the next year but I was hoping there was an easier and faster way.
Thank you again for this workbook it helps so much!
@Brice, That is a good question. The current template does not make that easy, but here is how I would do it (using the money-manager.xlsx file for Excel 2010 or Excel 2013):
1) Make a copy of the Budget worksheet and name it Actual or CashFlow or ProfitLoss
2) Add two rows under row 10 labeled Date Begin and Date End. These will be the dates that our new formulas will use when doing conditional sums.
3) Create a conditional sum formula to add all Deposits and subtract all Payments in the Transactions table for a particular category that are within the date range: Here is the formula =SUMIFS(Transactions!$I:$I, Transactions!$F:$F, Actual!$A15, Transactions!$B:$B, “>=”&B$11, Transactions!$B:$B, “<="&B$12) - SUMIFS(Transactions!$H:$H, Transactions!$F:$F,Actual!$A15,Transactions!$B:$B, ">=”&B$11, Transactions!$B:$B, “<="&B$12) 4) Use Copy and Paste Special > Values to transfer values from the new worksheet to the Budget worksheet … one major budget category at a time (so that you don’t overwrite the subtotals).
If you’d like me to send you a copy of the workbook I modified, I can do that (email me). Meanwhile, I’ll think about whether this could be included in a new update.
@Jon, Sorry for the delayed response I was not expecting such a quick answer from you. I tried to make the changes to the workbook but could not get the formula to work the correct way. I sent you an email requesting a copy of the template you created.
Thank you again for all of your help.
Jon. Just looked at this program and interested to see you used Quicken, as I did for my accounts when working, in a Church Partnership with six Churches and for my own personal use when I retired. Unfortunately, upgrading to Windows 7, meant that Quicken wouldn’t transfer and up to now, I haven’t found anything suitable, now only for personal use.
A first glance at your website suggests that I might find it useful, though perhaps with more modules etc., than I would need for more limited use.
I have downloaded it into Open Office and it looks to be working correctly, but do you have any comment as to whether there will be too much other material causing complications for a Home Budget and Accounts situation?
Kind regards
Graham C
@Graham … “too much other material causing complications for a Home Budget and Accounts situation” … I’m not sure what you mean. The money management template doesn’t handle taxes, investments, etc. So, Quicken is of course still superior in capability than a simple spreadsheet template.
Can you help me transfer from the original money manager to the updated version?
@Anne, Hello Anne. I’m sorry, but I don’t have time to do that. You could request a quote from an Excel consultant.
Hi, I’ve been using this great spreadsheet for a number of years now. One thing I would love is an “Opening Balance” column prior to the “January” column on the Budget spreadsheet. The main reason being for categories that take longer than one year to accrue, or expenses that only happen every other year or less frequently.
@Paolo, Inserting a column before column B in the Budget worksheet will mess up quite a few things, but for now you could just add a column to the right. Not an ideal location for an “Opening Balance” but it’s an option.
Hello again Jon,
I’m still using this great tool – it has seriously changed my life.
You were of course correct with your previous comment – more trouble than it was worth. But, I did make another, less dramatic change. We aren’t perfect, are we? As such, ending up with the actual account balance (Transactions!, column O) matching the Projected End Balance (Budget!) is somewhat unlikely. So I inserted a line below Projected that I call Corrected Actual. I just plug in the end-of-month balance from the transaction sheet here. I changed the formula for the Projected end balance by adding an if statement, i.e. if Corrected > 0, use it in the formula, otherwise use Projected. This correction flows through the remaining Projected Balances and gives a better picture.
The Money Management SS looks good and interesting; however, when I downloaded it what I got was the instruction SS. How can I download the sheet itself?
Thank you.
Bob Brandt
@Bob, The Help worksheet is just the first worksheet in the workbook. Check out the other tabs.
Hello,
I just came across your site and tools. I’m excited and I’m all set up to go; following the instructions you’ve provided and watching the demo. I’ve also gone through the comments on this page. I didn’t see anyone ask about entering old transactions into the transactions page. It being May and really wanting to capture what and how I’ve been spending; I’m entering in old transactions, and because of the starting balance its starting to look off. Please advise on if I should start this process in May and move forward? Or is there a way to enter information from the previous months?
Thank you
Lori
@Lori, If you are entering old transactions, you should enter a beginning balance for whatever date you plan to start with. For example, if you wanted to start with January, then you could look at a bank/card statement from January to determine what the balance was at that time. If you don’t have a bank statement to refer to, you can just calculate what the beginning balance would have been. For example, if the current balance in your account is 1000, and you start entering old transactions (with a beginning balance of zero at first), and the ending balance in the spreadsheet after you enter the past transactions happens to be 400, then your beginning balance can be changed to 600 to make the ending balance equal 1000.
Thank you,
I started with my beginning balance for May was working my way backwards entering transactions. I’ve only gotten through April but did not want to proceed if I risk throwing off the report spreadsheet. Will entering in the old transitions this way mess up anything?
@Lori, the spreadsheet is capable of handling multiple years of past transactions. The report worksheet is based on dates, so I don’t see a problem with entering past transactions. See my previous comment regarding what beginning balance to use.
Jon,
I am new to money manager. I have filled out 6 month of transactions and made a budget. This all worked nicely. Thank you!
Why does the ‘yearly income and expense report’ show no numbers at all (after all these entries)? I have put in a beginning balance. Arn’t the numbers of the transaction and the income transfered automatically?
@ML … My guess is that you need to update the “For Year Beginning” field at the top of the report.
Dear Jon,
Thank you for your reply. Unfortunately this is not the cause because I had set the date originally and reseting it now did not help either.
Do you think transfering all information which I have entered into a new down load form will help or will I only copy the mistake and bring it along into a new form?
ML
@ML … I can’t tell from your description what is going on, and whether it is just a simple fix or not. Copying your data into another worksheet may lead to more errors if you don’t do it correctly. You’d need to email me a copy of your file if you require additional help.
Good day Jon,
I hope you still check back here periodically. I recently started using the Money Manager Template, and although the initial entries can be extremely time-consuming, eventually it gives a great overview.
However, try as I might, I have been unable to figure out how to incorporate the Yearly Income & Expense Report, or see where the information is meant to be drawn from, I even downloaded the original template again to check that I did not miss any formulas when I was customising my own.
Please help, it would be great to have this overview report.
@Thabo, The reports get their information from the Transactions worksheet. I’d recommend watching the demo video if you haven’t already done so. The only way I could probably diagnose a problem with your spreadsheet is if you send it via email. See the contact page for the website.
hi,
I’m using this template for more than 3-4 years. Its very nice. Is there any template handling acounts with different currencies?
@sojy, Currency symbols can be changed by editing the number format in whatever cell uses a currency format. See the support page to see how to do this.
Hi Jon, Thanks for your response. I was actually looking for something like, you have a base currency(MXN for instance) and your transactions are in multiple currencies(INR, USD etc), so in the summary, the individual rates are calculated according to the day and is displayed in the base currency(MXN in this example). :)
@sojy … I’m afraid I don’t know how to do that. It ought to be possible somehow, but I’m guessing you’d have to link to a data source that provided the daily currency data. You may be able to find something via Google that talks about how to do currency conversions with Excel (though I haven’t looked).
Hello, I’ve been using this template for at least three years and it is THE BEST! I’ve recently had an annoying problem though – quite frequently when I am entering transactions, Excel crashes. It’s only when I’m using this spreadsheet, not any other Excel file. I thought it might be my sometimes-problematic internet connection (I was keeping this file in my Dropbox folder) so I moved it to a location on my hard drive – same problem. Thought it might be some weird interaction between Chrome and the file (I’m usually looking at my bank website when I’ve got the Excel file open), but it happens when Chrome is closed. I haven’t uninstalled the no-longer-active McAfee that is on my computer, but that may be the next step. I’m at just under 2,000 lines in the transactions page, so it’s not very large compared to previous years. Using Win7 Home Premium and Office 2016. I’ve looked at all the transactions with the formulas revealed, and nothing stands out. Is there some way to check for hidden corruptions? I’m saving the file after every keystroke, practically, but it’s still hard to use in those circumstances. Thank you for any suggestions!!!
Peace
MaryB
@MaryB … after years of using the same Excel file frequently, it doesn’t surprise me that the file may have become corrupted. Excel files can become corrupted for many unknown reasons and figuring out what is causing the problem is difficult even for experts. There are tools for sale that claim the ability to repair corrupt Excel files, but I have never tried any of these tools. I’d recommend starting with a new copy of the file (or an older backup copy of your file that is not corrupted). You should be able to use copy and Paste Special > Values to transfer data from your current workbook to the new one. Using Paste Values is a pretty safe way to transfer the information without transferring whatever is causing problems in the file.
– If the problem occurs when you insert a new row (or copy and insert the copied row), then you might try checking the conditional formatting rules. One thing that I have found that can cause problems in Excel files is having a very large number of conditional formatting rules, and that may occur if you have been copying and inserting copied rows for many years … resulting in hundreds of duplicate rules in your Transactions worksheet. So, go to Home > Conditional Formatting > Manage Rules and choose “This Worksheet” from the dropdown. If you see a large number of rules (like over 100 or 1000) then that may be the problem. The original set of rules (5 in the .xlsx file) will probably be at the very bottom of the list of rules. You could download a new copy of the file to see what the original set of rules looks like. Then delete all the duplicate rules in your file and update the Applies To field for the original set of rules.
In the future, to avoid creating copies of conditional formatting rules, instead of copying an existing row and inserting the copied row: First, insert a blank row (right-click on a row and select Insert Row) and then press Ctrl+d to copy the row immediately above the inserted row. Or, add a bunch of blank rows at the bottom of the table (and copy the formulas down). Then, to reuse common transactions, when copy/pasting, copy only the input data (not the formulas) and use Paste Special > Values.
I’ve just looked, and indeed I do have quite a large number of conditional formatting rules. I’ll try your fix and see what happens. Thank you so much!!!
Greetings! I purchased the pack that contained the newest version of the Money Management Template (*love* the separate worksheet with the account list!), and started fresh with 2017. I thought I was off to a good start, but this file is beginning to crash almost as frequently as my 2016 file did. I do now use the insert-blank-row-then-Ctrl-D to put in a new row, and I check the conditional formatting regularly – still at the original list.
I keep a separate sheet with common transactions for the middle and end of the month – I copy the input data only (stopping at the credit/debit columns, not straying into the balances etc) from that sheet into rows in the Transactions sheet. Do you think I should stop copying from the separate worksheet and simply copy from the previous rows in the Transactions sheet? I love using this spreadsheet but yesterday it took me four tries to enter one transaction and it’s kind of making me crazy.
Thank you for any insight or thoughts that may occur!
(I’m using Win7 Home Premium with Office 2016; at one point earlier in this process I uninstalled/reinstalled Excel, which didn’t seem to have any effect.)
@MaryB … I think that Excel 2016 crashes a LOT more than 2013 or 2010. That’s been my experience so far regardless of what spreadsheet I’m working on. It appears that Excel 2016 often has issues with conditional formatting. When the conditional formatting is removed, that sometimes fixes the problem. Most of the conditional formatting in the money management template is for convenience, so you’d probably be safe removing it (Home > Conditional Formatting > Manage Rules … then choose “This Worksheet” from the drop-down).
Copying/Pasting should be fine. I’d recommend using Paste Special > Values so that you only paste values instead of formatting.
Actual amount column in REPORT is not capturing after entering any transaction in TRANSACTION module. only #Name? is reflecting in that column/cell. May be problem of formula. Pls help
@Krish, You’ll need to mention what version of Excel you are using and what other changes you have made to the file. Also, you’ll need to send me a copy of your file because it takes too long to try to guess at what the problem might be otherwise. See the email address on my contact page.
Today I uploaded a new version that includes a Goals worksheet for tracking the balance of savings and expense funds. You can still use virtual sub-accounts if you want to, but now the Goals worksheet gives you way to track funds separately from the Transactions worksheet.
Hello. Jon! I just found your template online! I had been using and having several difficulties with it and wanting to start a different program!
So I believe I did download your program.
However when I began putting transactions in the spreadsheet, I had a few questions. I looked through some of the questions in the blog, and heard people referring to the “help worksheets” or even the “workbook”. I’m wondering if there is some additional info that I could download, that maybe I missed?? If you could direct me to how to download it, I would really appreciate it. A second question is that I was very eager to have the transaction history part up and running so I worked on that first. I’m wondering if we should start with the budget first — what’s the best way? Thank you very much. Sally Akehi
@Sally. The money management spreadsheet includes multiple worksheet tabs. One of these is a detailed help worksheet. When you first open the file, the help worksheet should be the first worksheet that opens. It’s fine to put Transactions in before budget info because then you can use the Report and YearlyReport worksheets to help you figure out what your budget could/should be.
Hi Jon. I’ve just started using your template and like it a lot. I personalized the template with my own categories and found that it’s much easier to update them if the Report and Yearly Report tabs have their categories linked to the categories in the Budget tab. That way you only have to change the category names on the Budget and Categories tabs. Hope this helps someone!
@Marie. Thanks for the suggestion. Perhaps I’ll make a change like that in the next version.
Great tool. I started using this October 1, 2016, my year is ending at the end of this month. How do I carry this over to more months or another year so I can keep it going?
@Brian, You can save a backup for the previous year and then in the new file for next year, clear the input data out of the register and enter new carry-over balances for each of your accounts in the register.
I love this workbook but I have a few items I am trying to modify to meet my needs and need some insight: 1) Report tab, my period doesn’t always begin and end repetitively due to pay dates adjusting for when the 26th of the month falls on a weekend day…but the formulae all look at begin and end date…how do I change that? In my simpler version I just assign a period # for reference when using pivot tables on my Transactions log tab. Thoughts?
2) I am having trouble with Accruals and Reversals and how to show them so they are not in conflict on the Report tab. I have Accruals under To Savings…yet they may reverse out within the same period…..thoughts on how best to handle that?
3) I would like to see what has cleared versus pending for the items in the Report tab, how could I do that?
Many thanks,
L. G. Elmore
@Larry … (2) See the sections in the help worksheet titled “Budgeting for Known Future Expenses – Without Transfers Between Accounts” and “Tracking the Balance of Savings and Expense Funds”
(1) and (3) are not simple answers – might need to request a quote from an Excel consultant to help you with that.