Need a simple Payroll System for your firm? Try our Payroll Calculator

Creating Personal and Family Budget in Microsoft Excel

Creating Personal and Family Budget in Excel

Creating a personal budget should be simple enough so that a second grader, who knows addition, subtraction, multiplication, and division, can understand it. It doesn’t have to be a jungle of incomprehensible figures.

Creation of any budget consists of three simple steps; step 1 is to determine your income,
step 2 is to outline all of the expenses that includes all subscriptions, utility and other bills and at last all day-to-day costs, which leads us to the final step 3 in which you should determine categories where you can reduce your expenses and begin saving.

Using a one-person example, here’s a simple 3 step guide that can get you going, but before we go any further and for the sake of having a good example right before your eyes of everything that we are about to explain in this article. I recommend downloading either Personal Budget Spreadsheet or Personal Monthly Budget, both of this templates can help you to understand the basic principals of budgeting and even help you to get started in no time at all.
Person X, with no one to support but himself and his massive credit card debts. He wants to clean up the mess he has gotten himself into without losing the essentials.

Determine your Income

The first thing he does is calculates precisely how much money he makes every month. In his case, he makes about $20,000 (after taxes) yearly. He has no other income from any other source. By dividing his annual income by 12, he gets his monthly salary: $1,666.66. He also uses the same principle when calculating costs (expenses) he has had over a yearly period, just so that everything works on a monthly basis.

Personal Budget Income

Outlining Expenses

To make it all just a little easier to comprehend, I have decided to split incurred expenses that you might have in two subcategories, necessary expenses and non-essential. So let’s get back to our example.

Calculating Essential Expenses

The person X decides to make a list of those things that he needs to survive every month. In doing so, he comes up with the following categories:

1. Rent/Mortgage payment
2. Grocery
3. Clothing
4. Utility – heat, electricity, water
5. Car maintenance – gas, insurance

Some of the items are already a pre-set monthly expense such as rent or mortgage or car insurance for example, but most of everything else fluctuates monthly. However, the fluctuations are minor therefore Person X decides to note down every expense for anything he bought (even the small items) for a month or two to get a clear idea of how much he spends in all the categories above.
He also digs up some of his old bills for a couple of months for things like heating, electricity, and water and calculates the average but adds a little to cover times when it may be more than usual like in the winter time for instance. Now he has all figures to fill in these categories.
The spreadsheet that you might have downloaded already contains a comprehensive list of all categories and items within them, of which we have talked about above in this article, but it is entirely up to you to decide, which category out of all is essential for your specific circumstances and which is not.

Personal Budget Expenses

Now, for the sake of argument Person X knows that every month, he must set aside $1100 for all essentials. He doesn’t even have to use the credit card, since he knows this is how much he will need to survive, without getting into debt.

Determine and Calculate Non-Essential Expenses

Person X lists the non-essentials-things that he spends on that aren’t as necessary:

1. recreation, this includes holidays, trips to restaurants, video rental or some other purchases.
2. gifts, souvenirs, etc.
3. long-distance calls

He thinks about his latest trips to the restaurant and decides to start eating more at home, perhaps picking up some cooking skills by trial and error, to cut down his restaurant eating costs, or doing some coupon shopping to reduce the bill, or getting better vacation deals, etc.

For gifts, he decides to allocate small amounts every month so when necessary, there will be enough for it. He also gets a better deal and reduces his long-distance calls bill by paying a flat rate for a certain number of hours every month. He also finds a credit card with the offer not to charge interest for 12 months on any balance transfers, which reduces the amount he has to pay every month to clear the balance of his credit card and helps to pay it all faster.

So the total amount of all the non-essentials our Person X has to spend on every month is approximately $175.00. Of course, paying balances off on the credit card is not necessarily is non-essential, but I have only brought it here for the sake of an example to show you what can actually be done to reduce your monthly spending.
The total of all essentials and non-essentials so far is now $1275.00.

Debt Elimination, Charity, and Savings

Since Person X is serious about giving to charity, eliminating his debts and wants to have some savings as well, he decides that these are categories that have to be included as well. After fiddling around a bit, he decides to allocate $30.00 for charitable donations and put the rest of what he has got left towards savings.

Personal Budget Savings

Some Things to Remember

1. Numbers are flexible. Experiment with getting cheaper options for things, even for essentials. If, for instance your rent is very high, consider moving. If you can, reduce your clothing bill to only what you really, really need. Be on the lookout for ways, especially, that you can increase the proportions of money you put in for debts, savings and charity.
2. Don’t spend unless you have it (so you can avoid interest and unnecessary borrowing).
2. Write down every expenditure. Keep receipts of all purchases.
3. Deal with averages.
4. Plan for extras
5. Plan for emergencies

2 comments… add one
  • couple of quick question:

    -can catagories be customized?
    -are expenses able to be downloaded from online bank and credit card companies?
    -is there anyway my expenses are uploaded online for sharing without my knowledge?

    • Let me answer all of your questions in the same order:

      – All categories can be customized, provided that you have a basic knowledge of Microsoft Excel.
      – Unfortunately no. You can use your bank statement, but you will need to enter your expenses manually.
      – Absolutely not, unless you have shared this file online or uploaded to the “Shared with Everyone” directory on Microsoft OneDrive.


Leave a Comment

We are thrilled you have chosen to leave a comment on Spreadsheet123 blog. Please keep in mind that all comments are moderated and manually approved. Please Do NOT use keywords in the name field because they won’t be linked to anything and do not include promotional links. The name you add will be displayed along with your comment. Your Email and IP stored with your comment won’t be shared publicly but are used for spam preventive purpose. For more information, please see our Privacy Policy.
Let’s try to have a personal and meaningful conversation.

This site uses Akismet to reduce spam. Learn how your comment data is processed.