Budget Planner Excel Spreadsheet
The Budget Planner Excel Spreadsheet is a powerful accounting and budgeting spreadsheet that works equally well with Microsoft Excel and OpenOffice.org‘s Calc.
You can use it to draw up a budget, keep track of your income, expenses and transfers between accounts, and see how your actual income and expenses compare to the amounts you budgeted.
Download link: budget_planner.xls
(28483 downloads since 2009-05-03)
If you want to manage, document and plan all your finances – your:
- income,
- expenses,
- account transfers,
- loans and other liabilities,
- investments and other assets, and
- your budget
- in one place, then this budget planner spreadsheet is for you.
Instructions: Quick Guide
The budget planner spreadsheet has several tab-sheets, each with a different function.
To help you get started, the spreadsheet already has some example accounts and transactions filled in. Feel free to modify, edit, or remove them as you wish.
1. Use the following sheets to setup and administer your accounts:
- Setup: Specify your accounts, income and expense categories.
- Admin: Set your initial account balances and other account admin.
2. Use the following sheets to record transactions:
- Income: Record all your income.
- Expenses: Record all your expenses.
- Transfers: Record all your transfers from one account to another, e.g. when you draw money, make a bond or other loan payment, etc.
3. Use the following sheets to keep a budget:
- Budget: Setup a budget and track how your actual income and expenses compare with your budgeted income and expenses.
4. Use the following sheets to display information:
- Account Balances: Check your account balances on any date.
- Balance Sheet: See your net worth and balance sheet on a specific date.
- Income Statement: See your income statement between any two dates.
Instructions: Detailed Guide
The following table contains detailed instructions for how to use each sheet in the budget planner spreadsheet.
| Description | |
|---|---|
| Setup |
This is where you specify your accounts, and your income and expense categories. Everything starts with this sheet. Specify at least one equity account. You need an equity account for each owner of the entity. If you’re using the spreadsheet as your personal budget, you are the entity, and thus, you are the only owner. Next, specify all your current (short-term) assets and liabilities, like your current and credit card accounts, any clothing or other store accounts you may have, etc. Then, specify all your long-term assets and liabilities, e.g any property and vehicles you own, your investments, your bond, your vehicle loans, etc. Next, specify all your sources of income. Finally, specify all your expense categories. |
| Admin |
Use this sheet to set your initial account balances (opening balances) and perform account admin (e.g. corrections). To open an account for an asset, debit the asset’s account that you specified in the Setup tab-sheet, and credit the appropriate equity account. To open an account for a liability (loan), credit the liability’s account that you specified in the Setup tab-sheet, and debit the appropriate equity account. In general, you should only ever need to use this sheet when opening or finalizing your accounts. For any transactions, you should use the Transfers, Expenses and Income tab-sheets. |
| Transfers |
Use this sheet to record all transfers between accounts. Here are a few examples of transfers. When you: Any transaction that increases one current or long-term account, and decreases another, is a transfer. Transfers are not expenses; they do not effect your net worth. |
| Expenses |
Use this sheet to record all your expenses. For each expense, enter a description, the date on which the expense was incurred, the expense category and the account from which the expense was paid. |
| Income |
Use this sheet to record all your income. For each income, enter a description, the date on which the income was received, the income category and the account into which the income was received. |
| Account Balances |
Use this sheet to display your account balances, on a specific date. Enter the date for which you wish to see the account balances in the provided field (you can enter ‘=now()’ to see the account balances for the current date). |
| Budget |
Use this sheet create a budget for a specified period of time, and track how your actual income and expenses compare to your budgeted income and expenses. Enter the from- and to- date for the budget in the fields provided. Then, enter the amounts you think you will receive for each income category, and the amounts you think you will spend for each expense category, during the specified time period. The budget will automatically display summaries of the actual expenses and income and the net profit/loss that were made between the two dates, and how these amounts differ from the amounts you budgeted. Hint: If you want to create multiple budgets (e.g. one for each month), right click on the tab-sheet’s tab, and click on ‘Move or Copy’. Tick the box that says ‘Create a copy’, select the tab where you want the new budget to be positioned, and click on ‘OK’. |
| Balance Sheet |
Use this sheet to display a balance sheet, on a specific date. Enter the date for which you wish to see a balance sheet in the provided field (you can enter ‘=now()’ to see the balance sheet for the current date). |
| Income Statement |
Use this sheet to display an income statement, for a specified period of time. Enter the from- and to- date in the fields provided, to see summaries of all the expenses and income, and the net profit/loss that were made between the two dates. |
History of the Budget Tracker Excel Spreadsheet
I created the first version of the spreadsheet in 2004, to use as my own budgeting tool. I made several improvements over the years, but the version you can download here is still based on the original.
This is probably the closest a spreadsheet can get to being a full blown accounting system.
I’ve used it to coach people to manage their finances, to keep the books of a small business, to manage the expenses of a flat I shared with friends and I’m still using it as my personal accounting and budgeting tool to this day.
Because I’ve been using it for such a long time, I don’t expect any bugs to remain, but if you find any, please let me know.
License & Terms of Use
The Budget Planner Excel Spreadsheet is completely free. You may use, copy, distribute and share it freely, as long as all copyright notices, logos and links remain embedded in the spreadsheet. You may not sell the spreadsheet.
May 4th, 2009 at 03:02 am
This is a great tool for people to use to manage their finances. The thing I like most about this spreadsheet is that it is not only an expense tracker, but also a “portfolio manager”. If I wasn’t already using MS Money, I would definitely use this spreadsheet because it gives me pretty much all the functionality/flexibility that an individual/small business needs. Your explanation on the web page is excellent, and should get most people started without too many headaches.
I also like the cosmetic changes you’ve made without reducing functionality or adding complexity. Great work!
June 29th, 2009 at 04:08 pm
Thanks for making this freely available. Really appreciate it.
July 22nd, 2009 at 07:41 am
hanks for sharing such great post, it will surly help many people. Budgeting never means to cut off the expenses and compromising with the needs and requirements, it just means a systematic and planned financial life.
August 20th, 2009 at 01:35 am
Thank you so much for sharing this! I looked all over the web for a spreadsheet that would work with multiple accounts and could not find one until I came across this page. It does exactly what I want it to do. Great work!
January 19th, 2011 at 10:00 am
Hi Francois
I downloaded the file but for some reason it says it is corrupt when I try to open it. Please will you look into this for me?
Many thanks.
January 19th, 2011 at 11:29 pm
@Nicole
Very strange. I can download and open the file without problems.
But it’s the second time this month someone struggled to open the ZIP file.
What version of Windows are you using?
February 1st, 2011 at 10:32 am
Hi Francois
i’m struggling to populate the “admin Page” with my data . seems this worksheet is password protected against editing beyond the confines of the current data structure. if i go “tools”, “protection” and click “unprotect” – i am prompted for a password. strangely i have no problem populating the “setup” page. have tried re-downloading the zip file and unzipping from scratch – so don’t think that it is a corrupted file. frustrating ‘cos i can see how powerful the spreadsheet is. any ideas? wanna use it for educating my clients.
February 1st, 2011 at 02:04 pm
Hi Mike
If you’re getting errors like that then you are trying to enter data in the wrong way.
Some of the cells are protected because they contain formulas that should not be modified, or else it will break the spreadsheet.
To enter data in the admin tabsheet, type in the description of the admin entry, the date and the amount, and then select the affected accounts from the drop-down lists.
February 1st, 2011 at 03:50 pm
thanks francois
all ok now. maybe i was getting carried away with cutting and pasting from other open instances of excel. sorry for taking your time. one tip to prevent the dreaded: “.xls file is locked for editing” is not have more than one copy of excel open at any one time. if you get that error – delete your “temporary files”, delete your system’s cache and re-boot.
thanks again
February 4th, 2011 at 12:48 pm
hi francois
last question. in your budget planner data i notice that there is a monthly expense of R5 000 interest on long-term liability of house loan. there’s also a transfer of R7000 for “bond payment”. the effect is a R2000 reduction in the long-term liability of house loan. is this because in the R7000 transfer, R5000 is interest (an expense) and the R2000 is the capital amount reduced? sorry to sound stupid but i don’t have a bond.
thanks fracois
February 4th, 2011 at 03:52 pm
@Mike
You explained it perfectly.
The interest of R 5,000 that accrued during that month is an expense: it lowers the entity’s net-worth).
The bond payment of R 7,000 is a transfer: it only transfers money from one account to another, it does not affect the entity’s net worth at all.
The end result is that the outstanding capital amount on the home loan decreased by R 2,000 (R 7,000 – R 5,000).
Most loan accounts can be treated this way.
February 8th, 2011 at 07:54 pm
Hi Mike,
I have downloaded the file, but cannot open the zip file as the error received states the file is corrupt. I am using Win 7 as the O/S.
Any suggestions?
Tx
February 9th, 2011 at 06:13 pm
@Heelesh (and anyone else who has problems downloading the ZIP file)
I figured out the problem: it is an Internet Explorer bug.
I downloaded the ZIP file on a Windows 7 machine, using Internet Explorer 8. When I tried to open the ZIP file, I got the following error:
“The Compressed (zipped) Folder ‘C:\Users\francois\Desktop\budget_planner.xls.zip’ is invalid”.
However, when downloaded the same file using FireFox, I could open the downloaded file without any problems. I then also tried using Opera to download the file, and once again, I could open it without any problems.
So, if you’re experiencing this problem, please try downloading and using a different browser.
And then, while you’re at it, please stop using Internet Explorer altogether.
You won’t be disappointed.
I’m not going into the details, but Internet Explorer sucks. For more reasons than you can imagine. If you don’t believe me, ask a few web developers what they think about it. And then prepare to be told in very graphic language just how vile that disgusting piece of software is. (There I go again…)
So, to get back to the point, just use a half decent browser instead. I beg you please.
Here are three good ones for you to choose from:
- FireFox: http://www.getfirefox.com/
- Opera: http://www.opera.com/
- Google Chrome: http://www.google.com/chrome
February 10th, 2011 at 05:49 pm
hi francois
why don’t you put an unzipped version of budget planner on your website. it’s only 3.79mb and will overcome the download problems.
if a user doesn’t have unzip software they will probably not want to by the software for just unzipping the budget planner.
an unzipped version. simple.
May 6th, 2011 at 12:46 am
Is there any way I can add additional rows for expenses?
May 6th, 2011 at 07:13 am
@Mike
I know that in the USA, Europe, Australia and many Asian counties bandwidth is plenty and Internet access is very cheap.
However, here in South Africa and many other African counries, Internet access is still slow and exhorbitantly expensive for many people, so I’d rather stick to the zip file.
Also, Internet Explorer is a really major cause of headaches for many developers. The problem is that Microsoft refuses to stick to web standards that everyone else accepts. If it could be calculated, I’m sure you would find that the collective time developers have wasted to ‘get it to work on IE’, it would come to thousands of years.
Well I’m not wasting any more of my own time. And I’m happy to make any contribution I can to rid the world of Internet Explorer.
@Holly
Unfortunately not. To add extra rows for expenses (or incomes, or accounts) is complicated. All the formulas, on all the sheets have to be updated.
When I start to run out of rows and I really feel the need to record my expenses in that amount of detail I group them into one category on the main spreadsheet, and use a different spreadsheet to split that group of expenses and track them individually.
E.g. last year I trained for and competed in offroad enduro motorcycle races. There are a lot of expenses that goes with the sport!
To track it properly, on my main budget spreadsheet I only had a single expense called ‘enduro-racing’, where I recorded all the expenses together.
Then on a 2nd budget spreadsheet that I used only to record my racing expenses, I created expense categories like ‘bike-maintenance’, ‘track-fees’, ‘membership-fees’, ‘fuel’, ‘riding-kit’, ‘suppliments’, etc.
Since my racing budget spreadsheet does not contain any personal info it is also usefull to be able to give it to friends who are thinking about taking up the sport, so they can see how much to budget for it.
June 25th, 2011 at 10:19 am
I love it thanx
October 2nd, 2011 at 09:10 am
Hey Francois, wow, this is such a great and useful sheet. I will be using it to do my budgeting and analysing my networth. Excellent work and thanks for sharing it.
January 27th, 2012 at 02:35 pm
Hi,
This is a great spreadsheet – Ill be using it for personal use, but I would like to know.
If I have an overdraft, how and where would that be captured?
January 27th, 2012 at 02:34 pm
@Nadine
Look at how the “credit-card” account is used in the examples that are included in the spreadsheet.
January 27th, 2012 at 03:18 pm
Thanks for the quick response Francois!
So would that then be captured under Setup at current accounts?
Admin: Debit Equity: Me (Nadine) and Credit: Overdraft
Transfers: e.g INTO – Cash, From Overdraft
Basically Im going to capture my current account as the overdraft account correct?
January 27th, 2012 at 04:42 pm
@Nadine
Yup, that sounds right. You can still call it “cheque” or whatever you wish in the “Setup” section.
If you currently have an overdraft balance, then you will use the “Admin” section to open the account in the same way in which the “credit-card” account is opened in the example.
From there on your transactions look exactly the same.