How to Create a 50-30-20 Budget Spreadsheet in Excel

A budget is the basis of your financial stability. Making a 50-30-20 monthly personal budget is a smart habit to get into when managing your money well. It is useful to keep track of various revenue sources and spending categories. It is beneficial to cut out all unnecessary spending by creating a personal monthly budget at the beginning of the month. Here, we will take you through 6 easy and quick steps for creating a 50-30-20 budget spreadsheet in Excel.


Download Practice Workbook

You may download the following Excel workbook for a better understanding and to practice yourself.


What Is the 50-30-20 Budget?

The 50-30-20 budget is a fantastic basic budgeting method that offers advice on how much to set aside each month for certain expenses. Elizabeth Warren popularized it in her book All Your Worth: The Ultimate Lifetime Money Plan. A simple guideline that makes it easy for you to take charge of your finances is the 50/30/20 budget scheme. Here, you spend 50% of your income on the most important expenses (e.g. food, rent, utility bills). 30% of the budget is allocated to desirable items. The remaining 20% is then used for savings or debt repayment.


Why Is the 50-30-20 Budget Important?

At first, we’ll talk about why the budget is important. Because it always reminds us to spend less than our earnings. Taking charge of your finances is a life insurance policy against unforeseen events and a career booster.
Now, let’s come to the matter of why the 50-30-20 budget is important. Because this budget scheme is ideal for people who prefer simplicity. This spending plan is suitable for families, young professionals, and college students equally. It is ideal for those who are new to budgeting. Also, you can set a goal for spending on each category at the start of the month.


6 Steps to Create a 50-30-20 Budget Spreadsheet in Excel

Creating a 50-30-20 budget in Excel could seem challenging at first, especially if you don’t use the application frequently. No matter how simple or complex the budgeting is, this step-by-step article will show you how to create a 50-30-20 budget in an Excel spreadsheet that can be easily customized to meet your needs. Excel includes a huge selection of budgeting templates. But in this article, we will create a 50-30-20 budget spreadsheet in Excel from scratch. So, let’s follow the steps to making a 50-30-20 monthly budget spreadsheet.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


Step 01: Calculate Monthly Income

At the very beginning, we’ve to make an income section where the total monthly income will be mentioned. It’s simple and easy. Just follow along.

📌 Steps:

  • At first, we created a column for sources of income in the B5:B8 range. In this case, the income sources are mainly office salary, overtime pay, additional income, and others.
  • Then, we inserted a new column for the amount under Column C.

Calculate Monthly Income

  • After that, we calculated the Total Budget from the incomes.
  • To do this, select cell C9 and enter the following formula.
=SUM(C5:C8)

Here, C5:C8 represents the range of amounts for several types of income sources.

  • Then, press ENTER.

Calculate Monthly Income in 50 30 20 Budget Spreadsheet in Excel

Read More: How to Do Budgeting and Forecasting in Excel (2 Suitable Ways)


Step 02: Determine Ideal 50-30-20 Division

As we stated before, for a 50-30-20 budget, 50% of the total budget goes to needs. 30% for wants and another 20% is used for savings. Now, we’ll determine the amount of each of these 3 parts. Let’s see it in action.

📌 Steps:

  • Firstly, go to cell F5 and write down the following formula in the Formula Bar.
=C9*0.5

Since the need is 50% of the total income, we multiplied the Total Budget in cell C9 by 0.5.

  • Then, press the ENTER key.

Determine Ideal 50-30-20 Division

  • Similarly, work out the ideal amount of Wants and Savings.
  • Here, take the multiplier as 0.3 to calculate the amount of Wants.
  • Additionally, take 0.2 for Savings.

Determine Ideal 50 30 20 Division in Budget Spreadsheet in Excel

Read More: How to Make a Household Budget in Excel (2 Smart Ways)


Step 03: Compute Expenses in 3 Different Categories

In this section, we’ll calculate the Needs, Wants, and Savings separately. We’ll determine them from various expense sectors. Instead of looking around, let’s see the following steps.

📌 Steps:

  • First of all, make a table for Needs expenses.
  • Then, in Column B, write the numerous areas of expenses. Like, here we’ve used Housing Rent, Food, etc. which are basic needs.

Compute Expenses in 3 Different Categories

  • Secondly, select cell C18 and paste the formula below.
=SUM(C12:C17)

Here, we used the SUM function, to sum up the total expenses for our needs.

  • After that, hit ENTER.

Calculating Total Needs in 50 30 20 Budget Spreadsheet in Excel

  • At this time, we wish to figure out the % of Needs for each sector. Like, what percentage of the total needs, we spent on Food?
  • To do this, go to cell D12 and paste the following formula.
=C12/$C$18
  • Then, press ENTER.

Calculating % of Needs in 50 30 20 Budget Spreadsheet in Excel

Note: Don’t forget to change the format of cells in D12:D17 to Percentage.

  • Now, bring the cursor to the bottom-right corner of cell D12. Instantly, it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool. Then. double-click on it to copy the formula in the rest of the cells.

Using the Fill Handle Tool

  • Consequently, we got results in the following cells also.

Calculating % of Needs in 50 30 20 Budget Spreadsheet in Excel

Compute Expenses in 3 Different Categories of 50 30 20 Budget Excel Spreadsheet

Read More: How to Create a Personal Budget in Excel (With Easy Steps)


Similar Readings


Step 04: Compare Actual Expenses with the Ideal Budget

In this part, we’ll analyze the difference between our Ideal Budget and Actual Expenses. So, without further delay, let’s dive in!

📌 Steps:

  • Initially, select cell G5 and put in the following formula.
=C18

Here, C18 serves as the cell reference for Total Needs. Actually, we’re fetching this value from cell C18 to cell G5.

  • As usual, press ENTER.

Compare Actual Expenses with the Ideal Budget

  • Similarly, fetch the values of the Total of Wants and Savings from cells C28 and C35 to cells G6 and G7 respectively.

In the beginning, we calculated an ideal amount of Needs, Wants, and Savings. But, after bearing all expenses for the month, obviously, the percentage of these 3 components should be different. Here, we’ll testify about this matter.

  • So, move to cell H5 and put down the following formula.
=G5/$C$9

Basically, we’re calculating the Actual Percentage of Needs to the Total Budget.

  • As always, hit ENTER.

Calculating Actual Percentage in 50 30 20 Budget Spreadsheet in Excel

From the picture above, we can clearly notice that the Actual Percentage of Needs is 41.93%. But, in the beginning, we took it as 50%. So, here, the person spent less than the ideal amount. The same happened for other components also. They differ from the ideal amount also.

Read More: How to Create Actual Vs Budget Variance Reports in Excel


Step 05: Determine Surplus or Shortage

At the beginning of the month, an individual can make his budget. At the end of the month, after completing all the tasks, he may have some extra money or some shortfall. Here, we’ll determine this. So let’s explore this step by step.

📌 Steps:

  • Primarily, select cell G9 and enter the following formula.
=SUM(G5:G7)

Fundamentally, we have added all the costs here.

  • Then, tap ENTER.

Determine Surplus or Shortage

  • After that, go to the following cell G10, and paste the formula below.
=C9-G9

In this formula, we are subtracting the Total Expense from the Total Income.

  • Correspondingly, hit ENTER.

  • Following this, go to cell F10 and enter the following formula.
=IF(G10>0,"Surplus","Deficit")

Here, the IF function inserts a logical test. If the value in cell G10 becomes greater than 0, then it will show a text sting Surplus in cell F10. Otherwise, we’ll see a text Deficit in cell F10.

  • As usual, press ENTER.

Applying IF Formula in 50 30 20 Budget Spreadsheet in Excel

Additionally, you can apply Conditional Formatting in cell F10. From the above picture, you can see a Green fill color in cell F10. When it exhibits Surplus, it will be Green. And, it will look Red while displaying the text Deficit inside the cell.

Applying Conditional Formatting

Read More: How to Calculate Remaining Budget Using Formula in Excel


Step 06: Insert Chart to Visualize Easily

We can insert a graph to visualize the 50-30-20 budget. A common technique for showing data linkages graphically is to insert a chart. Charts are used to communicate large or complex amounts of data. And those take up less space while being completely expressed in the text. So, let’s see the process in detail.

📌 Steps:

  • Firstly, jump to the Insert tab.
  • Secondly, click on the Insert Pie or Doughnut Chart drop-down icon.
  • Thirdly, select the Doughnut chart from the available options.

Insert Chart to Visualize Easily

Immediately, it adds a blank chart on the sheet.

  • Now, right-click anywhere on the chart area.

Instantly, the context menu pops up.

Suddenly, the Select Data Source dialog box appears before us.

  • Here, select the Add button under the Legend Entries (Series) section.

Using Select Data Source Dialog Box

Immediately, it brings up the Edit Series input box.

  • In the Series name box, give the reference of cell H4 which is the Actual Percentage.
  • And, in the Series values box, give the reference of the H5:H7 range.
  • Lastly, click OK.

Editing Series of Doughnut Chart

  • Now, click on the Edit button under the Horizontal (Category) Axis Labels section.

Setting up Horizontal Axis Labels

Instantly, it opens the Axis Labels input box.

  • In this place, give the reference of the E5:E7 range in the Axis label range box.
  • Ultimately, click OK.

Selecting Axis Labels

  • Finally, click OK on the Select Data Source dialog box.

Simply, it inserts a chart of the Actual Percentage on the spreadsheet.

Insert Chart to Visualize 50 30 20 Budget in Excel Spreadsheet

Similarly, you can introduce charts for various analyses. Here, we’ve added charts for visualizing the percentage of different components of Needs, Wants, and Savings.

Insert Chart to Visualize 50 30 20 Budget in Excel Spreadsheet

Insert Chart to Visualize 50 30 20 Budget in Excel Spreadsheet

Read More: How to Make a Budget Pie Chart in Excel (with Easy Steps)


Free Template: Ready to Use

In this workbook, we have added an extra sheet concluding a free 50-30-20 monthly budget template. You can use this in your daily life. Also, you may change and edit it according to your needs. You just have to fill up the blue-colored cells. Other calculations will be done automatically.

Free 50 30 20 Excel Spreadsheet Budget Template


Conclusion

This article provides easy and brief solutions to create a 50-30-20 budget spreadsheet in Excel. Don’t forget to download the Template file. Thank you for reading this article. Hopefully, this would be beneficial for you. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo