Budget Planner Excel Spreadsheet

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.

The Budget Planner Excel Spreadsheet is free (see T&Cs). If you find it useful, please make a donation.

Download link: budget_planner.xls
(54858 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:


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:
– transfer money from one account to another using Internet Banking
– draw cash at an ATM
– make a payment on your credit card
– make a home-loan payment
– make a payment on any loan
– make an investment
– buy a house
– buy a car
– buy shares

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.

An expense is any transaction that decreases your net worth.

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.

An income is any transaction that increases your net worth.

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.

If you find the Budget Planner Excel Spreadsheet useful, please make a donation.

You may not sell the Budget Planner Excel Spreadsheet.


, , , , , , , , ,

 

~ i may be wrong ~ report evil adverts ~
  1. Rainer

    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!

  2. Niki

    Thanks for making this freely available. Really appreciate it.

  3. Mack jackson

    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.

  4. Yosemite

    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!

  5. Nicole

    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.

  6. Francois Viljoen

    @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?

  7. mike

    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.

  8. Francois Viljoen

    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.

  9. mike

    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

  10. mike

    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

  11. Francois Viljoen

    @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.

  12. Heelesh

    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

  13. Francois Viljoen

    @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

  14. mike

    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.

  15. Holly

    Is there any way I can add additional rows for expenses?

  16. Francois Viljoen

    @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.

  17. Sam Gordon

    I love it thanx

  18. Kim-sung Jie

    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.

  19. Nadine

    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?

  20. Francois Viljoen

    @Nadine

    Look at how the “credit-card” account is used in the examples that are included in the spreadsheet.

  21. Nadine

    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?

  22. Francois Viljoen

    @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.

  23. Lynette van Dyk

    This is a great budget spreadsheet. I have been using it since 2010 and it has worked wonders for me.

    Thank You

  24. Francois Viljoen

    @Lynette

    Thank-you the feedback.

  25. Graham Trout

    hey please help me. i have no idea how this works. do you have a tutorial or something to help me? thank you Graham

  26. Shashank

    Hi Francois,

    Thanks a lot for sharing this amazing budget planner free of cost.
    This is my first budget planner , I am really enjoying it … Thanks a ton $$$

  27. nilesh

    nice one

  28. PANNIER

    sir i am from india i want the date format as follows dd-mm-yyyy so kindly help me how to change the date format in your budget planner xls sheet

  29. Francois Viljoen

    Unfortunately it is not possible.

  30. Tasja

    Thank you so much for sharing it free…

  31. Sameh

    Thanks f0r sharing
    I have a car. To setup i credit it from Equity:me to long-term – vehicle ?
    Why it is long term ?

  32. Francois Viljoen

    Cars are usually kept for a long period of time – more than a year in most cases – and you cant use your car as payment to buy something. I.e. it is a long term asset and it is not a cash equivalent.

    http://liberta.co.za/what-is-an-asset/#fixed_assets

  33. manz

    Hi Francois,

    I liked your work on the excel sheet. I am no longer able to add any more rows in any of the income, transfer or expense sheets. I think its protected, it asks for a password when i try to un-protect it. please help…..

  34. Francois Viljoen

    Hi manz

    The budget spreadsheet has A LOT of formulas and calculations on it that links the cells together. I protected all of the things that can break the spreadsheet after getting so many queries from people who accidentally modified these formulas.

    If you run out of space to add transactions it is not as simple as adding new rows… you have to update all the formulas as well.

    To prevent running out of space, I close off the accounts on the spreadsheet and start with a fresh one every year. If you run out of space before the end of a year, it’s probably time for you to upgrade to a database linked accounting package.

    Kind regards,

    Francois

  35. Candice

    Hi Francois!

    I have entered my balances into the ”Admin” tab of the spreadsheet. Let’s say for example the balance on my account is R5000 and the balance on my husband’s account is -R200 (he has an overdraft by the way)

    I’m not sure if I’m correct but under ”Admin” I debited my husband’s cheque account with -R200 and credited equity:me and debited my cheque account with R5000 and credited equity:me

    So my question is, why does it reflect as -4800 in the equity accounts under the ”Account Balances” tab? :/ I’m confused, because should it not reflect that we are R4800 in the green?

    Maybe I’m not understanding something or entering in the wrong why?

  36. Francois Viljoen

    Hi Candice

    If your husband’s account is in overdraft by R200, you should credit his account by R200, and debit your owner’s equity account by the same amount.

    If your account is “in the green” by R5000, you should debit your account by R5000, and debit your owner’s equity account by the same amount.

    If you want some examples, check the entries from a fresh download of a spreadsheet.

    Kind regards,
    Francois

  37. Candice

    Francois, Aha!, I see where I went wrong, did it the other way round.

    Also here you said: If your account is “in the green” by R5000, you should debit your account by R5000, and debit your owner’s equity account by the same amount.
    Question: Did you perhaps mean debit my account by R5000 and CREDIT owner’s equity, as there is no option to debit both?

    Another question: I actually do not understand what Owner’s equity is in the ”Account Balances” spreadsheet. I do not understand why that amount does not get less when I add expenses. Please help me understand this better, it seems like that Equity Account stays the same as the figures one added at the ”Admin” spreadsheet when one started using the spreadsheet. I have understanding of basic bookkeeping, maybe I just do not know what it all means. Could you please explain.

    Thank you for a phenominal spreadsheet!

  38. Mohammed Osman

    Hi Francois,

    I liked your work on the excel sheet. I am not able to add any more rows in any of the income, transfer or expense sheets. I think its protected, it asks for a password when i try to un-protect it. please send me the password – please please

  39. Mohammed Osman

    Hi Francois,

    I liked your work on the excel sheet. I am not able to add any more rows in any of the income, transfer or expense sheets. I think its protected, it asks for a password when i try to un-protect it. please help….. send me the password to un protect sheet

  40. Candice

    Hi Francois,

    Would you please be so kind as to answer the question I posted on May 4th, 2014 at 01:26. Sorry to be an annoyance…eek!
    I just really love this budget spreadsheet so much and I have stopped using it, until I can get more clarity on the questions.

    Thank you for your help.

    Candice

  41. Kotie Joubert

    Hi Francois

    I am a bookkeeper and are starting recently to help entrepreneurs with there start-up costs and doing small businesses books.

    Can I combine there total income expenses assets and liabilities and be able to split it between the actual expenses for the company and actual expenses for personal use?

    Most entrepreneurs that are self-employed and started there own business only needs something small like this just to keep track of all and to be able to manage there money better and gave a good overview on there personal and business expenses and income.

    Please give me some indications on how I would be able to use your spreadsheet and how I should categorize it so it gets split up as I have indicated?

    Your input in this is highly appreciated.

    Thank You
    Regards
    Kotie

  42. France

    Hi François,

    I was wondering if I set up a transfer between my car loan account and checking account, do I have to put my car payment in my expenses as well?

    Great stuff, I had bee looking for a very complete spreadsheet like yours, I absolutely love it.

    Is there a Paypal account link, I could only find the Bitcoin account, and I do not have a Bitcoin account.

  43. Francois Viljoen

    @France:

    No, your transfer is exactly that: a transfer. It belongs in the transfer sheet.

    The expenses that you incur on your car is things like depreciation and the interest on your car loan.

    As for Bitcoin, if you’re not using it yet, then you’re missing out. Check it out. It will be worth your while: http://liberta.co.za/blog/bitcoin

  44. France

    Thank you François for your answer and sorry for my slow response. My time has been consumed by two house moves. I think I undertstand what you mean, if I put in in transfers and expenses at the same time, it would equal a double entry for the same item and my checking account balances will be out of whack.

    As far as Bitcoin goes, the status of its legality in Canada is a little unclear at this time so I would prefer not go that route. But I would gladly make a donation to your favourite charity.

  45. Candice

    Would this spreadsheet be useful to use for a small business?

Add a Comment