How to Make a Personal Monthly Budget in Excel (2 Smart Ways)

We always try to make the best out of our limits. In order to make the best use of our monthly income, it is best to make a personal monthly budget. If you don’t know how to make a personal monthly budget in Excel, you can take help from this article. Here, I will try to explain 2 smart ways to make one.

Watch Video – Make a Personal Monthly Budget in Excel


What Is a Personal Monthly Budget?

Get FREE Advanced Excel Exercises with Solutions!

A personal monthly budget is nothing more than a financial plan that features the incomes and expenses of an individual over a month. The individual tries to consider all the possible sources of income and expenses throughout the month in that budget. It helps a person to manage his finances in the best possible way.


How to Make a Personal Monthly Budget in Excel: 2 Smart Ways

If you want to make a personal monthly budget manually, it won’t be as easy as using a built-in template. Both of the methods are described here in the following section.


1. Manual Procedures to Make a Personal Monthly Budget in Excel

We can create a personal monthly budget based on our necessities. We can add the necessary sources or remove the unnecessary ones in this procedure. The manual procedures for making a personal monthly budget are described in detail in the below section.


Step 1: Fix a Month to Make Personal Budget

In order to create a personal monthly budget, we need first to consider a month of a year as a time period. Here, I have considered April’2023 as a time period.

How to Make a Personal Monthly Budget in Excel


Step 2: Classify Income and Expense Sources

Followingly, consider all the possible income sources in a column (i.e. Income).

Categorizing Income Sources

Also, consider all the possible expense sources. There are some sources of fixed expenses and occasional expenses. You can categorize them based on the expense pattern.

 Classifying Expense Sources to Make a Personal Monthly Budget in Excel


Step 3: Input Projected and Actual Income

Now, take a new worksheet and add all the possible income sources. Add the probable income amounts in the Projected Income column and the exact amount in the Actual Income column.

Inserting Projected and Actual Income Amounts to Make a Personal Monthly Budget in Excel


Step 4: Compare Projected and Actual Incomes

Now, calculate the difference between the projected income and actual income in cell E7, using the following formula.

=D7-C7

Here,
D7 = Actual Income
C7 = Projected Income

Finding Amount Differences between Projected and Actual Incomes

Now, insert the following formula using the SUM function in cell C12 to calculate the total amount of the projected income and hit Enter.

=SUM(C7:C11)

Calculating Total Projected Income Amounts

Use Fill Handle on the right side to AutoFill the total amount of actual income and the total difference between the actual and projected income.

Calculating Total Actual Income and Difference in Amounts


Step 5: Compare Projected and Actual Expenses

After calculating the income, it is time to calculate the expenses. You can follow similar procedures like the calculation of income to calculate the expenses. You can create multiple tables based on the expense categories and add the projected and actual expense amounts in them.

Calculating Expenses to Make a Personal Monthly Budget in Excel


Step 6: Summarize Personal Monthly Budget

Now, create a table to summarize the whole monthly budget with the total amount of projected income, actual income, Projected expenses, actual expenses, and their differences.

Projected Income vs Projected Expenses

Let’s compare the Projected Income and Projected Expenses first.

The projected income is already calculated in the Personal Monthly Budget sheet. Now, insert the following formula and press Enter to have the projected income in cell C7.

='Personal Monthly Budget'!C12

Summarizing Total Projected Incomes

Now, input the following formula in cell D7 and hit Enter to calculate the total projected expenses.

='Personal Monthly Budget'!C22+'Personal Monthly Budget'!C33+'Personal Monthly Budget'!C45+'Personal Monthly Budget'!C54+'Personal Monthly Budget'!C61+'Personal Monthly Budget'!C67

Summarizing Total Projected Expenses to Make a Personal Monthly Budget in Excel

Actual Income vs Actual Expenses

The actual income is already calculated in the Personal Monthly Budget sheet. Just insert the following formula and press Enter to have the actual income in cell C8.

='Personal Monthly Budget'!D12

Summarizing Total Actual Income

To calculate total actual expenses, apply the following formula and press Enter.

='Personal Monthly Budget'!D22+'Personal Monthly Budget'!D33+'Personal Monthly Budget'!D45+'Personal Monthly Budget'!D54+'Personal Monthly Budget'!D61+'Personal Monthly Budget'!D67

 Summarizing Total Actual Expenses


Step 7: Highlight Key Information

Now, calculate the difference between the projected and actual budget by applying the following formula in cell E8.

=C8-D8

Calculating Income and Expense Differences

Also, calculate the difference between projected income and actual income. For this, apply the following formula in cell C9.

=C8-C7

Calculating Prrojected and Actual Income Difference

Finally, use Fill Handle on the right side to AutoFill the difference between projected income & actual expenses in cell D9, and projected balance & actual balance in cell E9.

AutoFill Formula to Find Difference

Read More: How to Create a Personal Budget in Excel


2. Use Built-in Template to Make a Personal Monthly Budget in Excel

The simplest way to make a personal monthly budget in Excel is to use a built-in template. For this, go to the File tab and click on New. Now, write Personal Monthly Budget in the search window and press Enter. Pick a personal monthly budget template based on your preferences.

Command Sequence to insert Personal Monthly Budget Template

Now, click on the Create option.

Click to have Template

We will have a personal monthly budget template. Now, you can add or remove cells based on your income and expenses.

Having Built-in Personal Monthly Budget Template

Read More: How to Make a Household Budget in Excel


Importance of Making a Personal Monthly Budget in Excel

The importance of a personal monthly budget is beyond description. Some of them are described in the following section.

  • A personal monthly budget helps you to gain a clear overview of your income and expenses.
  • It helps you to control your expenses by providing a framework.
  • We can set a financial goal and work towards it.
  • It helps you to allocate funds for savings and investments.
  • If you are in debt, it allows you to reduce unnecessary expenses and save money.

Things to Remember

  • Apply the correct formula to the correct cell.
  • You can separate income and expense in different sheets in case you are having a big dataset.

Frequently Asked Questions

1. Why should I make a personal monthly budget in Excel?

You are in need of a personal monthly budget if you want to manage your finances in a systematic way. It will help you to have a clear view of your income and expenses. It will also help you to make savings and investments.

2. How to Extend a Personal Monthly Budget to Make a Personal Yearly Budget?

In order to make a personal monthly budget to a personal yearly budget, make a monthly budget for every month throughout the year and summarize it with the addition of income and expenses of every month.

3. Are there any Excel templates available for personal monthly budgets?

Yes, there are many useful and simple templates in Excel. Just go to the File tab and click on New. Now, write Personal Monthly Budget in the search window and press Enter. There you will have multiple templates on your personal monthly budget. Pick a personal monthly budget template based on your preferences.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, I have tried to explain how to make a personal monthly budget in Excel in  2 smart ways along with the importance of it. I have also added the problems that you might face and frequently asked questions on this topic. I hope this article will be helpful for you. For any further questions, please comment below.


Related Articles

 

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo