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

Get FREE Advanced Excel Exercises with Solutions!

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.


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?

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 in the personal monthly budget. 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 Prepare a Sales Budget with Example in Excel


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 Create Budget and Expense Tracker in Excel


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

  • Equivalently, do the same for Wants and Savings.

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

Read More: How to Create a Budget with Irregular Income in Excel


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.

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: Create Retirement Budget Worksheet 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.

  • Then, choose Select Data from the menu.

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

  • Straightaway, add Data Labels to the chart from the Chart Elements options.

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 Create Renovation Budget in Excel


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


Download Practice Workbook

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


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 will be beneficial for you. Please let us know in the comment section if you have any queries or suggestions.


Related Articles


<< Go Back to Budget Template | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

2 Comments
  1. Thank you very much, this is excellent and I have now created this budget. I would like to create this for every month, so can I simply use the Sheet – Move/Copy function to copy this to 12 sheets for every month?

    • Reply Avatar photo
      Shahriar Abrar Rafid Jun 26, 2023 at 12:18 PM

      Hi BARYY,
      Thanks for your appreciation. I feel very happy that my tutorial helped you. Now, get back to your query.
      Yes, you can simply Copy the worksheet. Assume, we named the worksheet “Jan” for the month of January.
      Right-click on the sheet name tab and select Move or Copy from the context menu.

      In the Move or Copy dialog box, select move to end and check the box of the Create a copy option. Then, click OK.

      But, this procedure is lengthy and time-consuming. Because you have to repeat this process 11 times for the remaining 11 months. Also, you have to rename the sheets according to the month’s name. Instead, you can use a simple VBA macro to do it in a click.
      In the Visual Basic Editor, click Insert >> Module.

      Paste the following code into the module and click on the Run button.

      Sub Create_Monthly_Sheets()
      Dim monthNames As Variant
      Dim i As Integer
      
      monthNames = Array("Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
      
      Application.ScreenUpdating = False
      
      ' Copy the "Jan" sheet and rename it for each month
      For i = LBound(monthNames) To UBound(monthNames)
          Sheets("Jan").Copy After:=Sheets(Sheets.Count)
          With ActiveSheet
              .Name = monthNames(i)
          End With
      Next i
      
      Application.ScreenUpdating = True
      
      ' Activate the "Jan" sheet
      Sheets("Jan").Activate
      End Sub

      See the result with your own eyes.

      Again, thanks for your query. Your interest in learning is what motivates us to create better content.

      Regards,
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo