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.

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.

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.

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.

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.

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.

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.

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.

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.

Consequently, we got results in the following cells also.

Equivalently, do the same for Wants and Savings.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

Simply, it inserts a chart of the Actual Percentage on the 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.

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.

Download Practice Workbook

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

50-30-20 Budget Spreadsheet.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Barry Jun 26, 2023 at 1:14 AM

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

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

What Is the 50-30-20 Budget?

Why Is the 50-30-20 Budget Important?

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

Step 01: Calculate Monthly Income

Step 02: Determine Ideal 50-30-20 Division

Step 03: Compute Expenses in 3 Different Categories

Step 04: Compare Actual Expenses with the Ideal Budget

Step 05: Determine Surplus or Shortage

Step 06: Insert Chart to Visualize Easily

Free Template: Ready to Use

Conclusion

Related Articles

Leave a reply Cancel reply